帶你玩玩轉 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 的研討會。查詢調優是一個複雜的話題，每次我就索引和直方圖作報告時，我都會找到新的可改進點。但是每次報告反饋也表明很多軟體界中的人並不精通索引，並且時常使用錯誤。我想直方圖大概由於出現時間較短，還沒有出現像索引這種使用錯誤的情況。

原文來自：