2. 解析與優(yōu)化
服務(wù)器收到客戶端傳來的請求之后,還需要經(jīng)過查詢緩存、詞法語法解析和預(yù)處理、查詢優(yōu)化的處理。
2.1 查詢緩存
如果我們兩次都執(zhí)行同一條查詢指令,第二次的響應(yīng)時(shí)間會(huì)不會(huì)比第一次的響應(yīng)時(shí)間短一些?
之前使用過Redis緩存工具的讀者應(yīng)該會(huì)有這個(gè)很自然的想法,MySQL收到查詢請求之后應(yīng)該先到緩存中查看一下,看一下之前是不是執(zhí)行過這條指令。如果緩存命中,則直接返回結(jié)果;否則重新進(jìn)行查詢,然后加入緩存。
MySQL確實(shí)內(nèi)部自帶了一個(gè)緩存模塊。
現(xiàn)在有一張500W行且沒有添加索引的數(shù)據(jù)表,我執(zhí)行以下命令兩次,第二次會(huì)不會(huì)變得很快?
SELECT * FROM t_user WHERE user_name = '蟬沐風(fēng)'
并不會(huì)!說明緩存沒有生效,為什么?MySQL默認(rèn)是關(guān)閉自身的緩存功能的,查看一下query_cache_type
變量設(shè)置。
mysql> show variables like 'query_cache_type';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_type | OFF |
+------------------------------+---------+
默認(rèn)關(guān)閉就意味著不推薦,MySQL為什么不推薦用戶使用自己的緩存功能呢?
- MySQL自帶的緩存系統(tǒng)應(yīng)用場景非常有限,它要求SQL語句必須一模一樣,多一個(gè)空格,變一個(gè)大小寫都被認(rèn)為是兩條不同的SQL語句
- 緩存失效非常頻繁。只要一個(gè)表的數(shù)據(jù)有任何修改,針對該表的所有緩存都會(huì)失效。對于更新頻繁的數(shù)據(jù)表而言,緩存命中率非常低!
所以緩存的功能還是交給專業(yè)的ORM框架(比如MyBatis默認(rèn)開啟一級緩存)或者獨(dú)立的緩存服務(wù)Redis更加適合。
MySQL8.0已經(jīng)徹底移除了緩存功能
2.2 解析器 & 預(yù)處理器(Parser & Preprocessor)
現(xiàn)在跳過緩存這一步了,接下來需要做什么了?
如果我隨便在客戶端終端里輸入一個(gè)字符串chanmufeng
,服務(wù)器返回了一個(gè)1064的錯(cuò)誤
mysql> chanmufeng;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'chanmufeng' at line 1
服務(wù)器是怎么判斷出我的輸入是錯(cuò)誤的呢?這就是MySQL的Parser解析器的作用了,它主要包含兩步,分別是詞法解析和語法分析。
2.2.1 詞法解析
以下面的SQL語句為例
SELECT * FROM t_user WHERE user_name = '蟬沐風(fēng)' AND age > 3;
分析器先會(huì)做“詞法分析”,就是把一條完整的SQL語句打碎成一個(gè)個(gè)單詞,比如一條簡單的SQL語句,會(huì)打碎成8個(gè)符號(hào),每個(gè)符號(hào)是什么類型,從哪里開始到哪里結(jié)束。
MySQL 從你輸入的SELECT
這個(gè)關(guān)鍵字識(shí)別出來,這是一個(gè)查詢語句。它也要把字符串t_user
識(shí) 別成“表名 t_user”,把字符串user_name
識(shí)別成“列 user_name"。
2.2.2 語法分析
做完詞法解析,接下來需要做語法分析了。
根據(jù)詞法分析的結(jié)果,語法分析器會(huì)根據(jù)語法規(guī)則,判斷你輸入的這個(gè) SQL 語句是否滿足 MySQL 語法,比如單引號(hào)是否閉合,關(guān)鍵詞拼寫是否正確等。
解析器會(huì)根據(jù)SQL語句生成一個(gè)數(shù)據(jù)結(jié)構(gòu),這個(gè)數(shù)據(jù)結(jié)構(gòu)我們成為解析樹。
我故意拼錯(cuò)了SELECT
關(guān)鍵字,MySQL報(bào)了語法錯(cuò)誤,就是在語法分析這一步。
mysql> ELECT * FROM t_user WHERE user_name = '蟬沐風(fēng)' AND age > 3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELECT * FROM t_user WHERE user_name = '蟬沐風(fēng)'' at line 1
詞法語法分析是一個(gè)非?;A(chǔ)的功能,Java 的編譯器、百度搜索引擎如果要識(shí)別語句,必須也要有詞法語法分析功能。
任何數(shù)據(jù)庫的中間件,要解析 SQL完成路由功能,也必須要有詞法和語法分析功能,比如 Mycat,Sharding-JDBC(用到了Druid Parser)等都是如此。在市面上也有很多的開源的詞法解析的工具,比如 LEX,Yacc等。
2.2.3 預(yù)處理器
如果我們寫了一條語法和詞法都沒有問題的SQL,但是字段名和表名卻不存在,這個(gè)錯(cuò)誤是在哪一個(gè)階段爆出的呢?
詞法解析和語法分析是無法知道數(shù)據(jù)庫里有什么表,有哪些字段的。要知道這些信息還需要解析階段的另一個(gè)工具——預(yù)處理器。
它會(huì)檢查生成的解析樹,解決解析器無法解析的語義。比如,它會(huì)檢查表和列名是否存在,檢查名字和別名,保證沒有歧義。預(yù)處理之后得到一個(gè)新的解析樹。
本質(zhì)上,解析和預(yù)處理是一個(gè)編譯過程,涉及到詞法解析、語法和語義分析,更多細(xì)節(jié)我們不會(huì)探究,感興趣的讀者可以看一下編譯原理方面的書籍。
2.3 查詢優(yōu)化器(Optimizer)與查詢執(zhí)行計(jì)劃
到了這一步,MySQL終于知道我們想查詢的表和列以及相應(yīng)的搜索條件了,是不是可以直接進(jìn)行查詢了?
還不行。MySQL作者擔(dān)心我們寫的SQL太垃圾,所以有設(shè)計(jì)出一個(gè)叫做查詢優(yōu)化器的東東,輔助我們提高查詢效率。
2.3.1 什么是查詢優(yōu)化器?
一條 SQL語句是不是只有一種執(zhí)行方式?或者說數(shù)據(jù)庫最終執(zhí)行的 SQL是不是就是我們發(fā)送的 SQL?
不是。一條 SQL 語句是可以有很多種執(zhí)行方式的,最終返回相同的結(jié)果,他們是等價(jià)的。
舉一個(gè)非常簡單的例子,比如你執(zhí)行下面這樣的語句:
SELECT * FROM t1, t2 WHERE t1.id = 10 AND t2.id = 20
- 既可以先從表 t1 里面取出 id=10 的記錄,再根據(jù) id 值關(guān)聯(lián)到表 t2,再判斷 t2 里面 id 的值是否等于 20。
- 也可以先從表 t2 里面取出 id=20 的記錄,再根據(jù) id 值關(guān)聯(lián)到表 t1,再判斷 t1 里面 id 的值是否等于 10。
這兩種執(zhí)行方法的邏輯結(jié)果是一樣的,但是執(zhí)行的效率會(huì)有不同,如果有這么多種執(zhí)行方式,這些執(zhí)行方式怎么得到的?最終選擇哪一種去執(zhí)行?根據(jù)什么判斷標(biāo)準(zhǔn)去選擇?
這個(gè)就是 MySQL的查詢優(yōu)化器的模塊(Optimizer)的工作。
查詢優(yōu)化器的目的就是根據(jù)解析樹生成不同的執(zhí)行計(jì)劃(Execution Plan),然后選擇一種最優(yōu)的執(zhí)行計(jì)劃,MySQL 里面使用的是基于開銷(cost)的優(yōu)化器,哪種執(zhí)行計(jì)劃開銷最小,就用哪種。
2.3.2 優(yōu)化器究竟做了什么?
舉兩個(gè)簡單的例子∶
- 當(dāng)我們對多張表進(jìn)行關(guān)聯(lián)查詢的時(shí)候,以哪個(gè)表的數(shù)據(jù)作為基準(zhǔn)表。
- 有多個(gè)索引可以使用的時(shí)候,選擇哪個(gè)索引。
實(shí)際上,對于每一種數(shù)據(jù)庫來說,優(yōu)化器的模塊都是必不可少的,他們通過復(fù)雜的算法實(shí)現(xiàn)盡可能優(yōu)化查詢效率。
往細(xì)節(jié)上說,查詢優(yōu)化器主要做了下面幾方面的優(yōu)化:
- 子查詢優(yōu)化
- 等價(jià)謂詞重寫
- 條件化簡
- 外連接消除
- 嵌套連接消除
- 連接消除
- 語義優(yōu)化
本文不會(huì)對優(yōu)化的細(xì)節(jié)展開講解,大家先對MySQL的整體架構(gòu)有所了解就可以了,具體細(xì)節(jié)之后單獨(dú)開篇介紹
但是優(yōu)化器也不是萬能的,如果SQL語句寫得實(shí)在太垃圾,再牛的優(yōu)化器也救不了你了。因此大家在編寫SQL語句的時(shí)候還是要有意識(shí)地進(jìn)行優(yōu)化。
2.3.3 執(zhí)行計(jì)劃
優(yōu)化完之后,得到一個(gè)什么東西呢?優(yōu)化器最終會(huì)把解析樹變成一個(gè)查詢執(zhí)行計(jì)劃。
查詢執(zhí)行計(jì)劃展示了接下來執(zhí)行查詢的具體方式,比如多張表關(guān)聯(lián)查詢,先查詢哪張表,在執(zhí)行查詢的時(shí)候有多個(gè)索引可以使用,實(shí)際上該使用哪些索引。
MySQL提供了一個(gè)查看執(zhí)行計(jì)劃的工具。我們在 SQL語句前面加上 EXPLAIN
就可以看到執(zhí)行計(jì)劃的信息。
mysql> EXPLAIN SELECT * FROM t_user WHERE user_name = '';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
如果要得到更加詳細(xì)的信息,還可以用FORMAT=JSON
,或者開啟optimizer trace
。
mysql> EXPLAIN FORMAT=JSON SELECT * FROM t_user WHERE user_name = '';
文本不會(huì)帶大家詳細(xì)了解執(zhí)行計(jì)劃的每一個(gè)參數(shù),內(nèi)容很龐雜,大家先對MySQL的整體架構(gòu)有所了解就可以了,具體細(xì)節(jié)之后單獨(dú)開篇介紹
-
服務(wù)器
+關(guān)注
關(guān)注
12文章
8873瀏覽量
84973 -
TCP
+關(guān)注
關(guān)注
8文章
1337瀏覽量
78873 -
MySQL
+關(guān)注
關(guān)注
1文章
794瀏覽量
26359
發(fā)布評論請先 登錄
相關(guān)推薦
評論