1、MySQL 查詢(xún)優(yōu)化器與 SQL 調(diào)試 （一）

要想寫(xiě)出更好的 SQL，一些基礎(chǔ)概念和 SQL 調(diào)試是必不可少的。下面我們來(lái)看下查詢(xún)優(yōu)化器給我們做了哪些優(yōu)化，執(zhí)行器真正執(zhí)行的 SQL 語(yǔ)句是什么。

首先了解一些基礎(chǔ)的概念。

1.1、MySQL 客戶(hù)端與服務(wù)端的通信協(xié)議

MySQL 客戶(hù)端與服務(wù)端的通信協(xié)議為 “半雙工”，也就是在任何一個(gè)時(shí)刻，只能由服務(wù)端發(fā)送數(shù)據(jù)到客戶(hù)端，或者反之，兩個(gè)動(dòng)作不能同時(shí)發(fā)生。這就導(dǎo)致了沒(méi)法進(jìn)行流量控制，一旦一端開(kāi)始發(fā)送消息，另外一端要完整接收消息才能響應(yīng)對(duì)方。如果發(fā)送的數(shù)據(jù)太長(zhǎng)，MySQL 會(huì)拒絕接收更多的數(shù)據(jù)直接拋出異常，這時(shí)候可以通過(guò)設(shè)置 ‘max_allowed_packet’ 來(lái)調(diào)節(jié)可以發(fā)送的數(shù)據(jù)量大小，單位是 Byte。

而 MySQL 客戶(hù)端接收服務(wù)端發(fā)送的數(shù)據(jù)可能由多個(gè)數(shù)據(jù)包組成。在接收數(shù)據(jù)的過(guò)程看起來(lái)像是客戶(hù)端主動(dòng)去拉數(shù)據(jù)，但實(shí)際上是服務(wù)端主動(dòng)去推送數(shù)據(jù)到客戶(hù)端，客戶(hù)端無(wú)法主動(dòng)的讓服務(wù)端停止下來(lái)，只有當(dāng)所有數(shù)據(jù)發(fā)送給客戶(hù)端后才會(huì)釋放這條查詢(xún)所占用的資源。

1.2、查詢(xún) MySQL 服務(wù)端所有連接線(xiàn)程狀態(tài)

對(duì)于客戶(hù)端和服務(wù)端的通信都會(huì)通過(guò)線(xiàn)程去操作，而每個(gè)線(xiàn)程都會(huì)有屬于它的狀態(tài)，我們可以使用 show processlist 去查看相應(yīng)線(xiàn)程的連接信息。

• Id

連接標(biāo)識(shí)符。這個(gè)值來(lái)自于 INFORMATION_SCHEMA 數(shù)據(jù)庫(kù)的 PROCESSLIST 表的 ID 值，通過(guò) CONNECTION_ID() 函數(shù)返回。

• User

連接的用戶(hù)（用戶(hù)名）。system user 的值是指服務(wù)器生成的非客戶(hù)端線(xiàn)程，用于處理內(nèi)部的任務(wù)。比如：延遲行處理程序線(xiàn)程或副本主機(jī)上使用的 I/O 或 SQL 線(xiàn)程。對(duì)于 system user， Host列中未指定主機(jī)。unauthenticated user 指的是與客戶(hù)端已經(jīng)連接但是還未身份認(rèn)證的線(xiàn)程。event_scheduler指的是監(jiān)控調(diào)度事件的線(xiàn)程。

• Host

主機(jī)名（連接地址）（system user 沒(méi)有主機(jī)的除外）。以 TCP/IP 連接的主機(jī)名通過(guò)host_name:client_port格式去顯示更容易知道客戶(hù)端正在做什么。

• db

線(xiàn)程默認(rèn)的數(shù)據(jù)庫(kù)，沒(méi)有選擇則顯示 NULL。

• Command

客戶(hù)端執(zhí)行命令的類(lèi)型，如果是 Sleep 則表示是空閑狀態(tài)。

更多參數(shù)值請(qǐng)查閱官方文檔：https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html

• Time

線(xiàn)程處于當(dāng)前狀態(tài)的時(shí)間（以秒為單位）。對(duì)于 replica 線(xiàn)程，該值是最后一次同步事件到 replica host 之間的毫秒數(shù)。

• State

動(dòng)作、事件或者狀態(tài)。大多數(shù)狀態(tài)流轉(zhuǎn)的速度是非?？斓模绻€(xiàn)程一直停留在一個(gè)狀態(tài)，那么應(yīng)該檢查一下當(dāng)前狀態(tài)是否出現(xiàn)什么問(wèn)題了。

更多狀態(tài)值請(qǐng)查詢(xún)官方文檔：https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

• Info

線(xiàn)程正在執(zhí)行的語(yǔ)句，如果沒(méi)有執(zhí)行任何語(yǔ)句的話(huà)就是 NULL。

show processlist 只能查看 info 當(dāng)中的前 100 個(gè)字符，如果想要查看完整的字符，請(qǐng)使用 show full processlist。

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

MySQL 使用基于成本的優(yōu)化器，它將嘗試預(yù)測(cè)一個(gè)查詢(xún)使用某種執(zhí)行計(jì)劃時(shí)的成本，并選擇其中成本最小的一個(gè)。

如何查詢(xún) SQL 的執(zhí)行成本呢？

我們可以使用 explain + FORMAT=JSON 的方式查看具體的執(zhí)行計(jì)劃：

explain FORMAT=JSON select * from alarm_record


{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "alarm_record",
      "access_type": "ALL",
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "2K"
      },
      "used_columns": [
        "id",
        "product_id",
		"..."
      ]
    }
  }
}

其中 query_cost 就是總的查詢(xún)成本，大概要讀 0.35 個(gè)數(shù)據(jù)頁(yè)，而數(shù)據(jù)頁(yè)的默認(rèn)大小是 16 KB。

而導(dǎo)致 MySQL 選擇錯(cuò)誤的執(zhí)行計(jì)劃可能有以下原因（不一樣全）：

• 統(tǒng)計(jì)信息不準(zhǔn)確。

MySQL 依賴(lài)存儲(chǔ)引擎提供的統(tǒng)計(jì)信息來(lái)估計(jì)成本，但是有的引擎給出的統(tǒng)計(jì)成本不一定準(zhǔn)確，可能誤差會(huì)比較大。

• 執(zhí)行計(jì)劃中的成本不等同于實(shí)際的執(zhí)行成本。
• MySQL 中的最優(yōu)可能不是我們想象中的速度最快。

因?yàn)?MySQL 只是基于其成本模型選擇的最優(yōu)執(zhí)行計(jì)劃，有時(shí)候選擇的不一定是最快的。

• MySQL 從不考慮其他并發(fā)執(zhí)行的查詢(xún)，這可能會(huì)影響到當(dāng)前的查詢(xún)速度。
• MySQL 也不是任何時(shí)候都是基于成本的優(yōu)化。
• MySQL 不會(huì)考慮不受其控制的操作成本。

1.4.1、查詢(xún)優(yōu)化器做了哪些優(yōu)化

有以下部分優(yōu)化內(nèi)容：

• 重新定義關(guān)聯(lián)表順序

MySQL 會(huì)根據(jù)相關(guān)的嵌套循環(huán)算法找到掃描行數(shù)更少的表去重新定義關(guān)聯(lián)順序。但是有時(shí)候優(yōu)化器給出的不是最佳的關(guān)聯(lián)順序，如果有超過(guò) n 個(gè)表的關(guān)聯(lián)，優(yōu)化器需要檢查 n 的階乘種關(guān)聯(lián)順序，當(dāng)這個(gè)變得非常大的時(shí)候，優(yōu)化器不可能去檢查每一種的關(guān)聯(lián)成本，這時(shí)候優(yōu)化器會(huì)選擇 “貪婪” 搜索的方式查找 “最優(yōu)” 的關(guān)聯(lián)順序。

當(dāng)關(guān)聯(lián)的表超過(guò) optimizer_search_depth 配置的時(shí)候，就會(huì)選擇 “貪婪” 的搜索模式了（show VARIABLES like 'optimizer_search_depth'）。

• 將外連接轉(zhuǎn)換成內(nèi)連接
• 使用登記變換規(guī)則

MySQL 會(huì)合并比較、移除一些恒成立和恒不成立的判斷。

• 優(yōu)化 count()、min()和max()

想要找到某一列的最小值，只需要查詢(xún)對(duì)應(yīng) B-Tree 索引的最左端的記錄，優(yōu)化器在優(yōu)化的時(shí)候會(huì)將這個(gè)表達(dá)式當(dāng)成一個(gè)常數(shù)對(duì)待。如果 MySQL 使用了這種類(lèi)型的優(yōu)化，在 explain 中可以看到 “select tables optimized away”。

• 預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式
• 覆蓋索引掃描
• 子查詢(xún)優(yōu)化

MySQL 某些情況可以將子查詢(xún)轉(zhuǎn)換成一種效率更高的形式，從而減少多個(gè)查詢(xún)多次對(duì)數(shù)據(jù)進(jìn)行訪(fǎng)問(wèn)。

• 提前終止查詢(xún)

在發(fā)現(xiàn)已經(jīng)滿(mǎn)足查詢(xún)需求的時(shí)候，MySQL 總能立即終止查詢(xún)。

• 等值傳播
• 列表 in() 的比較

MySQL 會(huì)將 in 列表中的數(shù)據(jù)先進(jìn)行排序，然后通過(guò)二分查找來(lái)確定列表中的值是否滿(mǎn)足條件，對(duì)于 in 列表中有大量取值的時(shí)候，MySQL 的處理速度會(huì)更快。

參考文檔

• 高性能 MySQL 第三版
• MySQL 8.0 官方文檔