[[418410]]

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

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

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

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

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

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

查詢的復(fù)雜性可能會(huì)影響優(yōu)化。優(yōu)化器希望提供可用的最低成本查詢方式。連接五個(gè)不同的表就意味著有 5 的階乘（即 120）種可能的連接組合。代碼中內(nèi)置了啟發(fā)式方法，以嘗試對(duì)所有可能的選項(xiàng)進(jìn)行快捷評(píng)估。MySQL 每次看到查詢時(shí)都希望生成一個(gè)新的查詢計(jì)劃，而其他數(shù)據(jù)庫(kù)（例如 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í)際情況并不相符。沒(méi)有更多有效信息的情況下，查詢優(yōu)化器會(huì)假設(shè)列中的所有數(shù)據(jù)均勻分布在所有行中。沒(méi)有其他選擇依據(jù)時(shí)，它會(huì)默認(rèn)選擇兩個(gè)可能索引中較小的一個(gè)。雖然基于成本的優(yōu)化器模型可以制定出很多好的決策，但最終查詢計(jì)劃并不是最佳方案的情況也是有可能的。

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

查詢計(jì)劃是指優(yōu)化器基于查詢語(yǔ)句產(chǎn)生的，提供給服務(wù)器執(zhí)行的計(jì)劃內(nèi)容。查看查詢計(jì)劃的方法是在查詢語(yǔ)句前加上 EXPLAIN 關(guān)鍵字。例如，以下查詢要從城市表（city）和相應(yīng)的國(guó)家表（country）中獲得城市名稱（和所屬國(guó)家名稱），城市表和國(guó)家表通過(guò)國(guó)家唯一代碼連接。本例中僅查詢了英國(guó)的字母順序前五名的城市： 

SELECT city.name AS 'City',  
               country.name AS 'Country'  
FROM city  
JOIN country ON (city.countrycode = country.code)  
WHERE country.code = 'GBR'  
LIMIT 5; 

在查詢語(yǔ)句前加上 EXPLAIN 可以看到優(yōu)化器生成的查詢計(jì)劃。跳過(guò)除輸出末尾之外的所有內(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 子句從在國(guó)家表中查找變成了在城市表中查找。優(yōu)化器認(rèn)為這兩個(gè)改動(dòng)會(huì)提供比原始查詢更快的結(jié)果。

索引

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

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

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

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

直方圖

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

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

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

ANALYZE TABLE t UPDATE HISTOGRAM ON c1 WITH 10 BUCKETS; 

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

選擇索引還是直方圖？

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

結(jié)語(yǔ)

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