如老手一般玩轉 MySQL 查詢
許多人將數據庫查詢語句的調優視作哈利波特小說中某種神秘的“黑魔法”;使用錯誤的咒語，數據就會從寶貴的資源變成一堆糊狀物。
實際上，對關系數據庫系統的查詢調優是一項簡單的工程，其遵循的規則或啟發式方法很容易理解。查詢優化器會翻譯你發送給 MySQL 實例的查詢指令，然后將這些啟發式方法和優化器已知的數據信息結合使用，確定獲取所請求數據的最佳方式。再讀一下后面這半句：“優化器已知的數據信息。”查詢優化器需要對數據所在位置的猜測越少(即已知信息越多)，它就可以越好地制定交付數據的計劃。
為了讓優化器更好地了解數據，你可以考慮使用索引和直方圖。正確使用索引和直方圖可以大大提高數據庫查詢的速度。這就像如果你按照食譜做菜，就可以得到你喜歡吃的東西;但是假如你隨意在該食譜中添加材料，最終得到的東西可能就不那么盡如人意了。
基于成本的優化器
大多數現代關系型數據庫使用基于成本的優化器cost-based optimizer來確定如何從數據庫中檢索數據。該成本方案是基于盡可能減少非常耗費資源的磁盤讀取過程。數據庫服務器內的查詢優化器代碼會在得到數據時對這些數據的獲取進行統計，并構建一個獲取數據的歷史模型。
但歷史數據是可能會過時的。這就好像你去商店買你最喜歡的零食，然后突然發現零食漲價或者商店關門了。服務器的優化進程可能會根據舊信息做出錯誤的假設，進而制定出低效的查詢計劃。
查詢的復雜性可能會影響優化。優化器希望提供可用的最低成本查詢方式。連接五個不同的表就意味著有 5 的階乘(即 120)種可能的連接組合。代碼中內置了啟發式方法，以嘗試對所有可能的選項進行快捷評估。MySQL 每次看到查詢時都希望生成一個新的查詢計劃，而其他數據庫(例如 Oracle)則可以鎖定查詢計劃。這就是向優化器提供有關數據的詳細信息至關重要的原因。要想獲得穩定的性能，在制定查詢計劃時為查詢優化器提供最新信息確實很有效。
此外，優化器中內置的規則可能與數據的實際情況并不相符。沒有更多有效信息的情況下，查詢優化器會假設列中的所有數據均勻分布在所有行中。沒有其他選擇依據時，它會默認選擇兩個可能索引中較小的一個。雖然基于成本的優化器模型可以制定出很多好的決策，但最終查詢計劃并不是最佳方案的情況也是有可能的。
查詢計劃是什么?
查詢計劃query plan是指優化器基于查詢語句產生的，提供給服務器執行的計劃內容。查看查詢計劃的方法是在查詢語句前加上 EXPLAIN 關鍵字。例如，以下查詢要從城市表(city)和相應的國家表(country)中獲得城市名稱(和所屬國家名稱)，城市表和國家表通過國家唯一代碼連接。本例中僅查詢了英國的字母順序前五名的城市：
- SELECT city.name AS 'City',
- country.name AS 'Country'
- FROM city
- JOIN country ON (city.countrycode = country.code)
- WHERE country.code = 'GBR'
- LIMIT 5;
在查詢語句前加上 EXPLAIN 可以看到優化器生成的查詢計劃。跳過除輸出末尾之外的所有內容，可以看到優化后的查詢：
- SELECT `world`.`city`.`Name` AS `City`,
- 'United Kingdom' AS `Country`
- FROM `world`.`city`
- JOIN `world`.`country`
- WHERE (`world`.`city`.`CountryCode` = 'GBR')
- LIMIT 5;
看下比較大的幾個變化， country.name as 'Country' 改成了 'United Kingdom' AS 'Country'，WHERE 子句從在國家表中查找變成了在城市表中查找。優化器認為這兩個改動會提供比原始查詢更快的結果。
索引
在 MySQL 世界中，你會聽到索引或鍵的概念。不過，索引是由鍵組成的，鍵是一種識別記錄的方式，并且大概率是唯一的。如果將列設計為鍵，優化器可以搜索這些鍵的列表以找到所需的記錄，而無需讀取整個表。如果沒有索引，服務器必須從第一列的第一行開始讀取每一行數據。如果該列是作為唯一索引創建的，則服務器可以直接讀取該行數據并忽略其余數據。索引的值(也稱為基數)唯一性越強越好。請記住，我們在尋找更快獲取數據的方法。
MySQL 默認的 InnoDB 存儲引擎希望你的表有一個主鍵，并按照該鍵將你的數據存儲在 B+ 樹中。“不可見列”是 MySQL 最近添加的功能，除非在查詢中明確指明該不可見列，否則不會返回該列數據。例如，SELECT * FROM foo; 就不會返回任何不可見列。這個功能提供了一種向舊表添加主鍵的方法，且無需為了包含該新列而重寫所有查詢語句。
更復雜的是，有多種類型的索引，例如函數索引、空間索引和復合索引。甚至在某些情況下，你還可以創建這樣一個索引：該索引可以為查詢提供所有請求的信息，從而無需再去訪問數據表。
本文不會詳細講解各種索引類型，你只需將索引看作指向要查詢的數據記錄的快捷方式。你可以在一個或多個列或這些列的一部分上創建索引。我的醫師系統就可以通過我姓氏的前三個字母和出生日期來查找我的記錄。使用多列時要注意首選唯一性最強的字段，然后是第二強的字段，依此類推。“年-月-日”的索引可用于“年-月-日”、“年-月”和“年”搜索，但不適用于“日”、“月-日”或“年-日”搜索。考慮這些因素有助于你圍繞如何使用數據這一出發點來設計索引。
直方圖
直方圖就是數據的分布形式。如果你將人名按其姓氏的字母順序排序，就可以對姓氏以字母 A 到 F 開頭的人放到一個“邏輯桶”中，然后將 G 到 J 開頭的放到另一個中，依此類推。優化器會假定數據在列內均勻分布，但實際使用時多數情況并不是均勻的。
MySQL 提供兩種類型的直方圖：所有數據在桶中平均分配的等高型，以及單個值在單個桶中的等寬型。最多可以設置 1,024 個存儲桶。數據存儲桶數量的選擇取決于許多因素，包括去重后的數值量、數據傾斜度以及需要的結果準確度。如果桶的數量超過某個閾值，桶機制帶來的收益就會開始遞減。
以下命令將在表 t 的列 c1 上創建 10 個桶的直方圖：
- ANALYZE TABLE t UPDATE HISTOGRAM ON c1 WITH 10 BUCKETS;
想象一下你在售賣小號、中號和大號襪子，每種尺寸的襪子都放在單獨的儲物箱中。如果你想找某個尺寸的襪子，就可以直接去對應尺寸的箱子里找。MySQL 自從三年前發布 MySQL 8.0 以來就有了直方圖功能，但該功能卻并沒有像索引那樣廣為人知。與索引不同，使用直方圖插入、更新或刪除記錄都不會產生額外開銷。而如果更新索引，就必須更新 ANALYZE TABLE 命令。當數據變動不大并且頻繁更改數據會降低效率時，直方圖是一種很好的方法。
選擇索引還是直方圖?
對需要直接訪問的且具備唯一性的數據項目使用索引。雖然修改、刪除和插入操作會產生額外開銷，但如果數據架構正確，索引就可以方便你快速訪問。對不經常更新的數據則建議使用直方圖，例如過去十幾年的季度結果。
結語
本文源于最近在 Open Source 101 會議 上的一次報告。報告的演示文稿源自 PHP UK Conferenc 的研討會。查詢調優是一個復雜的話題，每次我就索引和直方圖作報告時，我都會找到新的可改進點。但是每次報告反饋也表明很多軟件界中的人并不精通索引，并且時常使用錯誤。我想直方圖大概由于出現時間較短，還沒有出現像索引這種使用錯誤的情況。
