一條SQL查詢語(yǔ)句是怎么去執(zhí)行的？（中）

2. 解析與優(yōu)化

服務(wù)器收到客戶端傳來(lái)的請(qǐng)求之后，還需要經(jīng)過(guò)查詢緩存、詞法語(yǔ)法解析和預(yù)處理、查詢優(yōu)化的處理。

2.1 查詢緩存

如果我們兩次都執(zhí)行同一條查詢指令，第二次的響應(yīng)時(shí)間會(huì)不會(huì)比第一次的響應(yīng)時(shí)間短一些？

之前使用過(guò)Redis緩存工具的讀者應(yīng)該會(huì)有這個(gè)很自然的想法，MySQL收到查詢請(qǐng)求之后應(yīng)該先到緩存中查看一下，看一下之前是不是執(zhí)行過(guò)這條指令。如果緩存命中，則直接返回結(jié)果；否則重新進(jìn)行查詢，然后加入緩存。

MySQL確實(shí)內(nèi)部自帶了一個(gè)緩存模塊。

現(xiàn)在有一張500W行且沒(méi)有添加索引的數(shù)據(jù)表，我執(zhí)行以下命令兩次，第二次會(huì)不會(huì)變得很快？

SELECT * FROM t_user WHERE user_name = '蟬沐風(fēng)'

并不會(huì)！說(shuō)明緩存沒(méi)有生效，為什么？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為什么不推薦用戶使用自己的緩存功能呢？

1. MySQL自帶的緩存系統(tǒng)應(yīng)用場(chǎng)景非常有限，它要求SQL語(yǔ)句必須一模一樣，多一個(gè)空格，變一個(gè)大小寫都被認(rèn)為是兩條不同的SQL語(yǔ)句
2. 緩存失效非常頻繁。只要一個(gè)表的數(shù)據(jù)有任何修改，針對(duì)該表的所有緩存都會(huì)失效。對(duì)于更新頻繁的數(shù)據(jù)表而言，緩存命中率非常低！

所以緩存的功能還是交給專業(yè)的ORM框架（比如MyBatis默認(rèn)開(kāi)啟一級(jí)緩存）或者獨(dú)立的緩存服務(wù)Redis更加適合。

MySQL8.0已經(jīng)徹底移除了緩存功能

2.2 解析器 & 預(yù)處理器（Parser & Preprocessor）

現(xiàn)在跳過(guò)緩存這一步了，接下來(lái)需要做什么了？

如果我隨便在客戶端終端里輸入一個(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解析器的作用了，它主要包含兩步，分別是詞法解析和語(yǔ)法分析。

2.2.1 詞法解析

以下面的SQL語(yǔ)句為例

SELECT * FROM t_user WHERE user_name = '蟬沐風(fēng)' AND age > 3;

分析器先會(huì)做“詞法分析”，就是把一條完整的SQL語(yǔ)句打碎成一個(gè)個(gè)單詞，比如一條簡(jiǎn)單的SQL語(yǔ)句，會(huì)打碎成8個(gè)符號(hào)，每個(gè)符號(hào)是什么類型，從哪里開(kāi)始到哪里結(jié)束。

MySQL 從你輸入的SELECT這個(gè)關(guān)鍵字識(shí)別出來(lái)，這是一個(gè)查詢語(yǔ)句。它也要把字符串t_user識(shí) 別成“表名 t_user”，把字符串user_name識(shí)別成“列 user_name"。

2.2.2 語(yǔ)法分析

做完詞法解析，接下來(lái)需要做語(yǔ)法分析了。

根據(jù)詞法分析的結(jié)果，語(yǔ)法分析器會(huì)根據(jù)語(yǔ)法規(guī)則，判斷你輸入的這個(gè) SQL 語(yǔ)句是否滿足 MySQL 語(yǔ)法，比如單引號(hào)是否閉合，關(guān)鍵詞拼寫是否正確等。

解析器會(huì)根據(jù)SQL語(yǔ)句生成一個(gè)數(shù)據(jù)結(jié)構(gòu)，這個(gè)數(shù)據(jù)結(jié)構(gòu)我們成為解析樹(shù)。

我故意拼錯(cuò)了SELECT關(guān)鍵字，MySQL報(bào)了語(yǔ)法錯(cuò)誤，就是在語(yǔ)法分析這一步。

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

詞法語(yǔ)法分析是一個(gè)非?；A(chǔ)的功能，Java 的編譯器、百度搜索引擎如果要識(shí)別語(yǔ)句，必須也要有詞法語(yǔ)法分析功能。

任何數(shù)據(jù)庫(kù)的中間件，要解析 SQL完成路由功能，也必須要有詞法和語(yǔ)法分析功能，比如 Mycat，Sharding-JDBC（用到了Druid Parser）等都是如此。在市面上也有很多的開(kāi)源的詞法解析的工具，比如 LEX，Yacc等。

2.2.3 預(yù)處理器

如果我們寫了一條語(yǔ)法和詞法都沒(méi)有問(wèn)題的SQL，但是字段名和表名卻不存在，這個(gè)錯(cuò)誤是在哪一個(gè)階段爆出的呢？

詞法解析和語(yǔ)法分析是無(wú)法知道數(shù)據(jù)庫(kù)里有什么表，有哪些字段的。要知道這些信息還需要解析階段的另一個(gè)工具——預(yù)處理器。

它會(huì)檢查生成的解析樹(shù)，解決解析器無(wú)法解析的語(yǔ)義。比如，它會(huì)檢查表和列名是否存在，檢查名字和別名，保證沒(méi)有歧義。預(yù)處理之后得到一個(gè)新的解析樹(shù)。

本質(zhì)上，解析和預(yù)處理是一個(gè)編譯過(guò)程，涉及到詞法解析、語(yǔ)法和語(yǔ)義分析，更多細(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語(yǔ)句是不是只有一種執(zhí)行方式？或者說(shuō)數(shù)據(jù)庫(kù)最終執(zhí)行的 SQL是不是就是我們發(fā)送的 SQL?

不是。一條 SQL 語(yǔ)句是可以有很多種執(zhí)行方式的，最終返回相同的結(jié)果，他們是等價(jià)的。

舉一個(gè)非常簡(jiǎn)單的例子，比如你執(zhí)行下面這樣的語(yǔ)句：

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ù)解析樹(shù)生成不同的執(zhí)行計(jì)劃（Execution Plan），然后選擇一種最優(yōu)的執(zhí)行計(jì)劃，MySQL 里面使用的是基于開(kāi)銷（cost）的優(yōu)化器，哪種執(zhí)行計(jì)劃開(kāi)銷最小，就用哪種。

2.3.2 優(yōu)化器究竟做了什么？

舉兩個(gè)簡(jiǎn)單的例子∶

1. 當(dāng)我們對(duì)多張表進(jìn)行關(guān)聯(lián)查詢的時(shí)候，以哪個(gè)表的數(shù)據(jù)作為基準(zhǔn)表。
2. 有多個(gè)索引可以使用的時(shí)候，選擇哪個(gè)索引。

實(shí)際上，對(duì)于每一種數(shù)據(jù)庫(kù)來(lái)說(shuō)，優(yōu)化器的模塊都是必不可少的，他們通過(guò)復(fù)雜的算法實(shí)現(xiàn)盡可能優(yōu)化查詢效率。

往細(xì)節(jié)上說(shuō)，查詢優(yōu)化器主要做了下面幾方面的優(yōu)化：

• 子查詢優(yōu)化
• 等價(jià)謂詞重寫
• 條件化簡(jiǎn)
• 外連接消除
• 嵌套連接消除
• 連接消除
• 語(yǔ)義優(yōu)化

本文不會(huì)對(duì)優(yōu)化的細(xì)節(jié)展開(kāi)講解，大家先對(duì)MySQL的整體架構(gòu)有所了解就可以了，具體細(xì)節(jié)之后單獨(dú)開(kāi)篇介紹

但是優(yōu)化器也不是萬(wàn)能的，如果SQL語(yǔ)句寫得實(shí)在太垃圾，再牛的優(yōu)化器也救不了你了。因此大家在編寫SQL語(yǔ)句的時(shí)候還是要有意識(shí)地進(jìn)行優(yōu)化。

2.3.3 執(zhí)行計(jì)劃

優(yōu)化完之后，得到一個(gè)什么東西呢？?jī)?yōu)化器最終會(huì)把解析樹(shù)變成一個(gè)查詢執(zhí)行計(jì)劃。

查詢執(zhí)行計(jì)劃展示了接下來(lái)執(zhí)行查詢的具體方式，比如多張表關(guān)聯(lián)查詢，先查詢哪張表，在執(zhí)行查詢的時(shí)候有多個(gè)索引可以使用，實(shí)際上該使用哪些索引。

MySQL提供了一個(gè)查看執(zhí)行計(jì)劃的工具。我們?cè)?SQL語(yǔ)句前面加上 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，或者開(kāi)啟optimizer trace。

mysql> EXPLAIN FORMAT=JSON SELECT * FROM t_user WHERE user_name = '';

文本不會(huì)帶大家詳細(xì)了解執(zhí)行計(jì)劃的每一個(gè)參數(shù)，內(nèi)容很龐雜，大家先對(duì)MySQL的整體架構(gòu)有所了解就可以了，具體細(xì)節(jié)之后單獨(dú)開(kāi)篇介紹