1、MySQL 查詢優(yōu)化器與 SQL 調(diào)試 （一）
要想寫出更好的 SQL，一些基礎(chǔ)概念和 SQL 調(diào)試是必不可少的。下面我們來看下查詢優(yōu)化器給我們做了哪些優(yōu)化，執(zhí)行器真正執(zhí)行的 SQL 語句是什么。
首先了解一些基礎(chǔ)的概念。
1.1、MySQL 客戶端與服務(wù)端的通信協(xié)議
MySQL 客戶端與服務(wù)端的通信協(xié)議為 “半雙工”，也就是在任何一個時刻，只能由服務(wù)端發(fā)送數(shù)據(jù)到客戶端，或者反之，兩個動作不能同時發(fā)生。這就導致了沒法進行流量控制，一旦一端開始發(fā)送消息，另外一端要完整接收消息才能響應(yīng)對方。如果發(fā)送的數(shù)據(jù)太長，MySQL 會拒絕接收更多的數(shù)據(jù)直接拋出異常，這時候可以通過設(shè)置 ‘max_allowed_packet’ 來調(diào)節(jié)可以發(fā)送的數(shù)據(jù)量大小，單位是 Byte。
而 MySQL 客戶端接收服務(wù)端發(fā)送的數(shù)據(jù)可能由多個數(shù)據(jù)包組成。在接收數(shù)據(jù)的過程看起來像是客戶端主動去拉數(shù)據(jù)，但實際上是服務(wù)端主動去推送數(shù)據(jù)到客戶端，客戶端無法主動的讓服務(wù)端停止下來，只有當所有數(shù)據(jù)發(fā)送給客戶端后才會釋放這條查詢所占用的資源。
1.2、查詢 MySQL 服務(wù)端所有連接線程狀態(tài)
對于客戶端和服務(wù)端的通信都會通過線程去操作，而每個線程都會有屬于它的狀態(tài)，我們可以使用 show processlist 去查看相應(yīng)線程的連接信息。
- Id
連接標識符。這個值來自于 INFORMATION_SCHEMA 數(shù)據(jù)庫的 PROCESSLIST 表的 ID 值，通過 CONNECTION_ID() 函數(shù)返回。
- User
連接的用戶（用戶名）。system user 的值是指服務(wù)器生成的非客戶端線程，用于處理內(nèi)部的任務(wù)。比如：延遲行處理程序線程或副本主機上使用的 I/O 或 SQL 線程。對于 system user， Host列中未指定主機。unauthenticated user 指的是與客戶端已經(jīng)連接但是還未身份認證的線程。event_scheduler指的是監(jiān)控調(diào)度事件的線程。
- Host
主機名（連接地址）（system user 沒有主機的除外）。以 TCP/IP 連接的主機名通過host_name:client_port格式去顯示更容易知道客戶端正在做什么。
- db
線程默認的數(shù)據(jù)庫，沒有選擇則顯示 NULL。
- Command
客戶端執(zhí)行命令的類型，如果是 Sleep 則表示是空閑狀態(tài)。
更多參數(shù)值請查閱官方文檔：https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html
- Time
線程處于當前狀態(tài)的時間（以秒為單位）。對于 replica 線程，該值是最后一次同步事件到 replica host 之間的毫秒數(shù)。
- State
動作、事件或者狀態(tài)。大多數(shù)狀態(tài)流轉(zhuǎn)的速度是非常快的，如果線程一直停留在一個狀態(tài)，那么應(yīng)該檢查一下當前狀態(tài)是否出現(xiàn)什么問題了。
更多狀態(tài)值請查詢官方文檔：https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
- Info
線程正在執(zhí)行的語句，如果沒有執(zhí)行任何語句的話就是 NULL。
show processlist 只能查看 info 當中的前 100 個字符，如果想要查看完整的字符，請使用 show full processlist。
1.4、查詢優(yōu)化器
MySQL 使用基于成本的優(yōu)化器，它將嘗試預(yù)測一個查詢使用某種執(zhí)行計劃時的成本，并選擇其中成本最小的一個。
如何查詢 SQL 的執(zhí)行成本呢？
我們可以使用 explain + FORMAT=JSON 的方式查看具體的執(zhí)行計劃：
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 就是總的查詢成本，大概要讀 0.35 個數(shù)據(jù)頁，而數(shù)據(jù)頁的默認大小是 16 KB。
而導致 MySQL 選擇錯誤的執(zhí)行計劃可能有以下原因（不一樣全）：
- 統(tǒng)計信息不準確。
MySQL 依賴存儲引擎提供的統(tǒng)計信息來估計成本，但是有的引擎給出的統(tǒng)計成本不一定準確，可能誤差會比較大。
- 執(zhí)行計劃中的成本不等同于實際的執(zhí)行成本。
- MySQL 中的最優(yōu)可能不是我們想象中的速度最快。
因為 MySQL 只是基于其成本模型選擇的最優(yōu)執(zhí)行計劃，有時候選擇的不一定是最快的。
- MySQL 從不考慮其他并發(fā)執(zhí)行的查詢，這可能會影響到當前的查詢速度。
- MySQL 也不是任何時候都是基于成本的優(yōu)化。
- MySQL 不會考慮不受其控制的操作成本。
1.4.1、查詢優(yōu)化器做了哪些優(yōu)化
有以下部分優(yōu)化內(nèi)容：
- 重新定義關(guān)聯(lián)表順序
MySQL 會根據(jù)相關(guān)的嵌套循環(huán)算法找到掃描行數(shù)更少的表去重新定義關(guān)聯(lián)順序。但是有時候優(yōu)化器給出的不是最佳的關(guān)聯(lián)順序，如果有超過 n 個表的關(guān)聯(lián)，優(yōu)化器需要檢查 n 的階乘種關(guān)聯(lián)順序，當這個變得非常大的時候，優(yōu)化器不可能去檢查每一種的關(guān)聯(lián)成本，這時候優(yōu)化器會選擇 “貪婪” 搜索的方式查找 “最優(yōu)” 的關(guān)聯(lián)順序。
當關(guān)聯(lián)的表超過 optimizer_search_depth 配置的時候，就會選擇 “貪婪” 的搜索模式了（show VARIABLES like 'optimizer_search_depth'）。
- 將外連接轉(zhuǎn)換成內(nèi)連接
- 使用登記變換規(guī)則
MySQL 會合并比較、移除一些恒成立和恒不成立的判斷。
- 優(yōu)化 count()、min()和max()
想要找到某一列的最小值，只需要查詢對應(yīng) B-Tree 索引的最左端的記錄，優(yōu)化器在優(yōu)化的時候會將這個表達式當成一個常數(shù)對待。如果 MySQL 使用了這種類型的優(yōu)化，在 explain 中可以看到 “select tables optimized away”。
- 預(yù)估并轉(zhuǎn)化為常數(shù)表達式
- 覆蓋索引掃描
- 子查詢優(yōu)化
MySQL 某些情況可以將子查詢轉(zhuǎn)換成一種效率更高的形式，從而減少多個查詢多次對數(shù)據(jù)進行訪問。
- 提前終止查詢
在發(fā)現(xiàn)已經(jīng)滿足查詢需求的時候，MySQL 總能立即終止查詢。
- 等值傳播
- 列表 in() 的比較
MySQL 會將 in 列表中的數(shù)據(jù)先進行排序，然后通過二分查找來確定列表中的值是否滿足條件，對于 in 列表中有大量取值的時候，MySQL 的處理速度會更快。
參考文檔
- 高性能 MySQL 第三版
- MySQL 8.0 官方文檔
