如果想深入地學(xué)習(xí) MySQL ，那么應(yīng)該從宏觀(guān)的架構(gòu)上面著手，這一篇我們學(xué)習(xí) MySQL 查詢(xún)語(yǔ)句執(zhí)行的流程，希望對(duì)大家有所幫助！

本篇文章 MySQL 版本為 8.0.18

架構(gòu)圖

解析器

解析器的作用是對(duì)客戶(hù)端傳來(lái)的 SQL 語(yǔ)句進(jìn)行以下工作：

• 語(yǔ)法解析：檢查 SQL 語(yǔ)句的語(yǔ)法，括號(hào)、引號(hào)是否閉合等
• 詞法解析：把 SQL 語(yǔ)句中的關(guān)鍵詞、表名、字段名拆分成一個(gè)個(gè)節(jié)點(diǎn)，最終得到一顆解析樹(shù)

預(yù)處理器

解析器主要是檢查語(yǔ)法詞法方面，但是如果語(yǔ)法詞法都正確，但是表、字段是不存在的，那么這段 SQL 語(yǔ)句也是無(wú)法正確執(zhí)行的。

所以預(yù)處理器的作用是：語(yǔ)義解析，判斷解析樹(shù)的語(yǔ)義是否正確，表、字段這些是否存在，預(yù)處理后會(huì)得到一顆新的解析樹(shù)。

查詢(xún)優(yōu)化器

查詢(xún)優(yōu)化器結(jié)構(gòu)

在 MySQL 中一條 SQL 語(yǔ)句的執(zhí)行方式有多種，雖然最終都會(huì)得到相同的結(jié)果，但是存在開(kāi)銷(xiāo)上的差異，具體選擇哪一種執(zhí)行方式是由查詢(xún)優(yōu)化器來(lái)決定的。比如說(shuō)：

• 表中有多個(gè)索引可以選擇，具體選擇哪一個(gè)索引
• 當(dāng)我們對(duì)多張表進(jìn)行關(guān)聯(lián)查詢(xún)時(shí)，以哪一張表的數(shù)據(jù)為基準(zhǔn)表

查詢(xún)優(yōu)化器是基于開(kāi)銷(xiāo)（cost）的優(yōu)化器，它的工作原理是根據(jù)解析樹(shù)生成的多種執(zhí)行計(jì)劃，會(huì)評(píng)估各種執(zhí)行方式所需的開(kāi)銷(xiāo)（cost），最終會(huì)得到一個(gè)開(kāi)銷(xiāo)最小的執(zhí)行計(jì)劃作為最終方案。

但是這個(gè)開(kāi)銷(xiāo)最小的執(zhí)行方式不一定是最優(yōu)的執(zhí)行方式，比如本該使用索引，卻進(jìn)行了全表掃描等。雖然查詢(xún)優(yōu)化器中有《優(yōu)化》兩個(gè)字，但是這個(gè)優(yōu)化并不是萬(wàn)能的，很多時(shí)候更加需要考慮 SQL 語(yǔ)句書(shū)寫(xiě)得是否合理。

邏輯查詢(xún)優(yōu)化

邏輯查詢(xún)優(yōu)化主要負(fù)責(zé)進(jìn)行一些關(guān)系代數(shù)對(duì) SQL 語(yǔ)句進(jìn)行優(yōu)化，從而使 SQL 語(yǔ)句執(zhí)行效率更高

邏輯查詢(xún)優(yōu)化我們可以使用幾個(gè)案例來(lái)簡(jiǎn)單理解

• 子查詢(xún)合并

  合并前

  SELECT * FROM t1 WHERE a1<10 AND (   EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR   EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2) );

  登錄后復(fù)制

  合并后

  SELECT * FROM t1 WHERE a1<10 AND (   EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND (t2.b2=1 OR t2.b2=2) );

  登錄后復(fù)制

  把多個(gè)子查詢(xún)通過(guò)合并查詢(xún)條件而合并查詢(xún)，把多次連接操作減少為單次表掃描和單次連接

• 等價(jià)謂詞重寫(xiě)

  像我們熟悉的 like 模糊查詢(xún)，% 寫(xiě)在條件后面才會(huì)進(jìn)行索引范圍查詢(xún)，其實(shí)這是查詢(xún)優(yōu)化器的功勞

  假設(shè)使用的條件都是有建立索引的，重寫(xiě)前

  SELECT * FROM USERINFO WHERE name LIKE 'Abc%';

  登錄后復(fù)制

  重寫(xiě)后

  SELECT * FROM USERINFO WHERE name >= 'Abc' AND name < 'Abd';

  登錄后復(fù)制

  這就是為什么能進(jìn)行索引范圍查詢(xún)的答案

• 條件簡(jiǎn)化

  條件簡(jiǎn)化也是利用一些等式、代數(shù)關(guān)系來(lái)實(shí)現(xiàn)簡(jiǎn)化

  • 去除表達(dá)式中的冗余括號(hào)，減少語(yǔ)法分析時(shí)產(chǎn)生的AND和OR 樹(shù)的層 次，比如 ((a AND b) AND (c AND d)) 簡(jiǎn)化為 a AND b AND c AND d
  • 常量傳遞，比如 col1 = col2 AND col2 = 3 簡(jiǎn)化為 col1 = 3 AND col2 = 3
  • 表達(dá)式計(jì)算，對(duì)于一些可直接求解的表達(dá)式會(huì)轉(zhuǎn)換為最終的計(jì)算結(jié)果，比如 col1 = 1+2 簡(jiǎn)化為 col1 = 3

物理查詢(xún)優(yōu)化

物理查詢(xún)優(yōu)化主要做的工作是根據(jù) SQL 語(yǔ)句分別對(duì)多種執(zhí)行計(jì)劃進(jìn)行開(kāi)銷(xiāo)的評(píng)估

物理查詢(xún)優(yōu)化主要解決以下幾個(gè)問(wèn)題：

• 單表掃描中采用哪種方式是開(kāi)銷(xiāo)最小的（掃描索引+回表 or 全表掃描）

• 存在表連接的時(shí)候使用哪種連接方式是開(kāi)銷(xiāo)最小的

簡(jiǎn)單了解一下代價(jià)評(píng)估，代價(jià)評(píng)估是基于 CPU 代價(jià)和 IO 代價(jià)兩個(gè)維度的

上述參數(shù)說(shuō)明如下：

• a_page_IO_time， 一個(gè)數(shù)據(jù)頁(yè)加載的IO耗時(shí)
• N_page，數(shù)據(jù)頁(yè)數(shù)量
• N_tuple，元組數(shù)（元組理解為一行數(shù)據(jù)）
• a_tuple_CPU_time，一個(gè)元組從數(shù)據(jù)頁(yè)中解析的CPU耗時(shí)
• C_index，索引的IO耗時(shí)
• N_page_index，索引頁(yè)數(shù)量

關(guān)于索引成本計(jì)算可以參考這篇文章：MySQL查詢(xún)?yōu)槭裁催x擇使用這個(gè)索引？——基于MySQL 8.0.22索引成本計(jì)算

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

執(zhí)行計(jì)劃是查詢(xún)優(yōu)化器的產(chǎn)物，最終會(huì)交給存儲(chǔ)引擎進(jìn)行執(zhí)行。執(zhí)行計(jì)劃可以幫助我們得知 MySQL 會(huì)怎么執(zhí)行這條 SQL 語(yǔ)句。

使用 explain 關(guān)鍵字查看 SQL 語(yǔ)句的執(zhí)行計(jì)劃，可以得到以下信息：

• id：嵌套查詢(xún)中查詢(xún)的執(zhí)行順序
• possible_keys：本次查詢(xún)可能用到的索引
• Key：實(shí)際用到的索引
• rows：得到結(jié)果大概要檢索多少行數(shù)據(jù)
• select_type多表之間的連接類(lèi)型
• extra：額外的信息，是否有索引覆蓋、索引下推等

存儲(chǔ)引擎

MySQL 服務(wù)端規(guī)定了數(shù)據(jù)如何存儲(chǔ)、如何提取、如何更新的規(guī)范，這個(gè)規(guī)范由存儲(chǔ)引擎來(lái)實(shí)現(xiàn)，不同的存儲(chǔ)引擎的實(shí)現(xiàn)方式不同，所以不同的存儲(chǔ)引擎會(huì)呈現(xiàn)其獨(dú)特的功能和特點(diǎn)。其中最常用的存儲(chǔ)引擎是 InnoDB 和 MyISAM

簡(jiǎn)單說(shuō)說(shuō)這兩款存儲(chǔ)引擎的特點(diǎn)

InnoDB：

• 支持外鍵、事務(wù)，保證了數(shù)據(jù)的完整性和一致性
• 支持更細(xì)的鎖粒度，對(duì)鎖的控制更好，讀寫(xiě)效率更高

MyISAM

• 不支持事務(wù)，只支持行鎖，適合數(shù)據(jù)只讀的場(chǎng)景

存儲(chǔ)引擎方面暫時(shí)先不展開(kāi)，會(huì)在其他文章繼續(xù)穿插他們的對(duì)比，以及會(huì)詳細(xì)分析 InnoDB 更新數(shù)據(jù)的流程

總結(jié)

從前，只知道在客戶(hù)端軟件上寫(xiě)下 SQL 語(yǔ)句，點(diǎn)擊執(zhí)行，拿到數(shù)據(jù)

到現(xiàn)在終于了解到一條查詢(xún)語(yǔ)句傳入 MySQL 服務(wù)端后需要經(jīng)歷這一系列的操作

• 解析器根據(jù)這條 SQL 語(yǔ)句的語(yǔ)法、詞法進(jìn)行檢查，如果沒(méi)有錯(cuò)誤的話(huà)會(huì)按關(guān)鍵詞拆分成一個(gè)個(gè)節(jié)點(diǎn)，最終形成一棵解析樹(shù)

• 預(yù)處理器會(huì)檢查 SQL 語(yǔ)句的語(yǔ)義，檢查 SQL 語(yǔ)句是否有歧義、字段等是否存在，形成一棵新的解析樹(shù)

• 查詢(xún)優(yōu)化器拿到這個(gè)解析樹(shù)生成的各種執(zhí)行計(jì)劃，經(jīng)過(guò)邏輯查詢(xún)優(yōu)化、物理查詢(xún)優(yōu)化后得到一個(gè)開(kāi)銷(xiāo)最小的執(zhí)行計(jì)劃

• 執(zhí)行引擎拿到這份執(zhí)行計(jì)劃調(diào)用存儲(chǔ)引擎的接口

• 存儲(chǔ)引擎根據(jù)執(zhí)行計(jì)劃進(jìn)行數(shù)據(jù)查詢(xún)，查詢(xún)會(huì)查詢(xún)調(diào)用操作系統(tǒng)中文件系統(tǒng)的一些接口，完成數(shù)據(jù)查詢(xún)，最后返回給客戶(hù)端

【