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

via: https://opensource.com/article/21/5/mysql-query-tuning

作者：Dave Stokes 選題：lujun9972 譯者：unigeorge 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive