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