許多人將數(shù)據(jù)庫查詢語句的調(diào)優(yōu)視作哈利波特小說中某種神秘的“黑魔法”;使用錯(cuò)誤的咒語，數(shù)據(jù)就會從寶貴的資源變成一堆糊狀物。

實(shí)際上，對關(guān)系數(shù)據(jù)庫系統(tǒng)的查詢調(diào)優(yōu)是一項(xiàng)簡單的工程，其遵循的規(guī)則或啟發(fā)式方法很容易理解。查詢優(yōu)化器會翻譯你發(fā)送給 MySQL 實(shí)例的查詢指令，然后將這些啟發(fā)式方法和優(yōu)化器已知的數(shù)據(jù)信息結(jié)合使用，確定獲取所請求數(shù)據(jù)的最佳方式。再讀一下后面這半句：“優(yōu)化器已知的數(shù)據(jù)信息。”查詢優(yōu)化器需要對數(shù)據(jù)所在位置的猜測越少(即已知信息越多)，它就可以越好地制定交付數(shù)據(jù)的計(jì)劃。

[[417685]]

為了讓優(yōu)化器更好地了解數(shù)據(jù)，你可以考慮使用索引和直方圖。正確使用索引和直方圖可以大大提高數(shù)據(jù)庫查詢的速度。這就像如果你按照食譜做菜，就可以得到你喜歡吃的東西;但是假如你隨意在該食譜中添加材料，最終得到的東西可能就不那么盡如人意了。

基于成本的優(yōu)化器

大多數(shù)現(xiàn)代關(guān)系型數(shù)據(jù)庫使用基于成本的優(yōu)化器cost-based optimizer來確定如何從數(shù)據(jù)庫中檢索數(shù)據(jù)。該成本方案是基于盡可能減少非常耗費(fèi)資源的磁盤讀取過程。數(shù)據(jù)庫服務(wù)器內(nèi)的查詢優(yōu)化器代碼會在得到數(shù)據(jù)時(shí)對這些數(shù)據(jù)的獲取進(jìn)行統(tǒng)計(jì)，并構(gòu)建一個(gè)獲取數(shù)據(jù)的歷史模型。

但歷史數(shù)據(jù)是可能會過時(shí)的。這就好像你去商店買你最喜歡的零食，然后突然發(fā)現(xiàn)零食漲價(jià)或者商店關(guān)門了。服務(wù)器的優(yōu)化進(jìn)程可能會根據(jù)舊信息做出錯(cuò)誤的假設(shè)，進(jìn)而制定出低效的查詢計(jì)劃。

查詢的復(fù)雜性可能會影響優(yōu)化。優(yōu)化器希望提供可用的最低成本查詢方式。連接五個(gè)不同的表就意味著有 5 的階乘(即 120)種可能的連接組合。代碼中內(nèi)置了啟發(fā)式方法，以嘗試對所有可能的選項(xiàng)進(jìn)行快捷評估。MySQL 每次看到查詢時(shí)都希望生成一個(gè)新的查詢計(jì)劃，而其他數(shù)據(jù)庫(例如 Oracle)則可以鎖定查詢計(jì)劃。這就是向優(yōu)化器提供有關(guān)數(shù)據(jù)的詳細(xì)信息至關(guān)重要的原因。要想獲得穩(wěn)定的性能，在制定查詢計(jì)劃時(shí)為查詢優(yōu)化器提供最新信息確實(shí)很有效。

此外，優(yōu)化器中內(nèi)置的規(guī)則可能與數(shù)據(jù)的實(shí)際情況并不相符。沒有更多有效信息的情況下，查詢優(yōu)化器會假設(shè)列中的所有數(shù)據(jù)均勻分布在所有行中。沒有其他選擇依據(jù)時(shí)，它會默認(rèn)選擇兩個(gè)可能索引中較小的一個(gè)。雖然基于成本的優(yōu)化器模型可以制定出很多好的決策，但最終查詢計(jì)劃并不是最佳方案的情況也是有可能的。

查詢計(jì)劃是什么?

查詢計(jì)劃query plan是指優(yōu)化器基于查詢語句產(chǎn)生的，提供給服務(wù)器執(zhí)行的計(jì)劃內(nèi)容。查看查詢計(jì)劃的方法是在查詢語句前加上 EXPLAIN 關(guān)鍵字。例如，以下查詢要從城市表(city)和相應(yīng)的國家表(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 可以看到優(yōu)化器生成的查詢計(jì)劃。跳過除輸出末尾之外的所有內(nèi)容，可以看到優(yōu)化后的查詢：

SELECT `world`.`city`.`Name` AS `City`, 
                'United Kingdom' AS `Country` 
FROM `world`.`city` 
JOIN `world`.`country` 
WHERE (`world`.`city`.`CountryCode` = 'GBR') 
LIMIT 5; 

看下比較大的幾個(gè)變化， country.name as 'Country' 改成了 'United Kingdom' AS 'Country'，WHERE 子句從在國家表中查找變成了在城市表中查找。優(yōu)化器認(rèn)為這兩個(gè)改動會提供比原始查詢更快的結(jié)果。

索引

在 MySQL 世界中，你會聽到索引或鍵的概念。不過，索引是由鍵組成的，鍵是一種識別記錄的方式，并且大概率是唯一的。如果將列設(shè)計(jì)為鍵，優(yōu)化器可以搜索這些鍵的列表以找到所需的記錄，而無需讀取整個(gè)表。如果沒有索引，服務(wù)器必須從第一列的第一行開始讀取每一行數(shù)據(jù)。如果該列是作為唯一索引創(chuàng)建的，則服務(wù)器可以直接讀取該行數(shù)據(jù)并忽略其余數(shù)據(jù)。索引的值(也稱為基數(shù))唯一性越強(qiáng)越好。請記住，我們在尋找更快獲取數(shù)據(jù)的方法。

MySQL 默認(rèn)的 InnoDB 存儲引擎希望你的表有一個(gè)主鍵，并按照該鍵將你的數(shù)據(jù)存儲在 B+ 樹中。“不可見列”是 MySQL 最近添加的功能，除非在查詢中明確指明該不可見列，否則不會返回該列數(shù)據(jù)。例如，SELECT * FROM foo; 就不會返回任何不可見列。這個(gè)功能提供了一種向舊表添加主鍵的方法，且無需為了包含該新列而重寫所有查詢語句。

更復(fù)雜的是，有多種類型的索引，例如函數(shù)索引、空間索引和復(fù)合索引。甚至在某些情況下，你還可以創(chuàng)建這樣一個(gè)索引：該索引可以為查詢提供所有請求的信息，從而無需再去訪問數(shù)據(jù)表。

本文不會詳細(xì)講解各種索引類型，你只需將索引看作指向要查詢的數(shù)據(jù)記錄的快捷方式。你可以在一個(gè)或多個(gè)列或這些列的一部分上創(chuàng)建索引。我的醫(yī)師系統(tǒng)就可以通過我姓氏的前三個(gè)字母和出生日期來查找我的記錄。使用多列時(shí)要注意首選唯一性最強(qiáng)的字段，然后是第二強(qiáng)的字段，依此類推。“年-月-日”的索引可用于“年-月-日”、“年-月”和“年”搜索，但不適用于“日”、“月-日”或“年-日”搜索?？紤]這些因素有助于你圍繞如何使用數(shù)據(jù)這一出發(fā)點(diǎn)來設(shè)計(jì)索引。

直方圖

直方圖就是數(shù)據(jù)的分布形式。如果你將人名按其姓氏的字母順序排序，就可以對姓氏以字母 A 到 F 開頭的人放到一個(gè)“邏輯桶”中，然后將 G 到 J 開頭的放到另一個(gè)中，依此類推。優(yōu)化器會假定數(shù)據(jù)在列內(nèi)均勻分布，但實(shí)際使用時(shí)多數(shù)情況并不是均勻的。

MySQL 提供兩種類型的直方圖：所有數(shù)據(jù)在桶中平均分配的等高型，以及單個(gè)值在單個(gè)桶中的等寬型。最多可以設(shè)置 1,024 個(gè)存儲桶。數(shù)據(jù)存儲桶數(shù)量的選擇取決于許多因素，包括去重后的數(shù)值量、數(shù)據(jù)傾斜度以及需要的結(jié)果準(zhǔn)確度。如果桶的數(shù)量超過某個(gè)閾值，桶機(jī)制帶來的收益就會開始遞減。

以下命令將在表 t 的列 c1 上創(chuàng)建 10 個(gè)桶的直方圖：

ANALYZE TABLE t UPDATE HISTOGRAM ON c1 WITH 10 BUCKETS; 

想象一下你在售賣小號、中號和大號襪子，每種尺寸的襪子都放在單獨(dú)的儲物箱中。如果你想找某個(gè)尺寸的襪子，就可以直接去對應(yīng)尺寸的箱子里找。MySQL 自從三年前發(fā)布 MySQL 8.0 以來就有了直方圖功能，但該功能卻并沒有像索引那樣廣為人知。與索引不同，使用直方圖插入、更新或刪除記錄都不會產(chǎn)生額外開銷。而如果更新索引，就必須更新 ANALYZE TABLE 命令。當(dāng)數(shù)據(jù)變動不大并且頻繁更改數(shù)據(jù)會降低效率時(shí)，直方圖是一種很好的方法。

選擇索引還是直方圖?

對需要直接訪問的且具備唯一性的數(shù)據(jù)項(xiàng)目使用索引。雖然修改、刪除和插入操作會產(chǎn)生額外開銷，但如果數(shù)據(jù)架構(gòu)正確，索引就可以方便你快速訪問。對不經(jīng)常更新的數(shù)據(jù)則建議使用直方圖，例如過去十幾年的季度結(jié)果。

結(jié)語

本文源于最近在 Open Source 101 會議 上的一次報(bào)告。報(bào)告的演示文稿源自 PHP UK Conferenc 的研討會。查詢調(diào)優(yōu)是一個(gè)復(fù)雜的話題，每次我就索引和直方圖作報(bào)告時(shí)，我都會找到新的可改進(jìn)點(diǎn)。但是每次報(bào)告反饋也表明很多軟件界中的人并不精通索引，并且時(shí)常使用錯(cuò)誤。我想直方圖大概由于出現(xiàn)時(shí)間較短，還沒有出現(xiàn)像索引這種使用錯(cuò)誤的情況。