本文是對(duì)這篇文章How the MySQL Optimizer Calculates the Cost of a Query (Doc ID 1327497.1)^[1]的翻譯，翻譯如有不當(dāng)?shù)牡胤?，敬?qǐng)諒解，請(qǐng)尊重原創(chuàng)和翻譯勞動(dòng)成果，轉(zhuǎn)載的時(shí)候請(qǐng)注明出處。謝謝！

適用于：

MySQL 4.0 及后續(xù)更高的版本

本文檔中的內(nèi)容適用于任何平臺(tái)。

目標(biāo)

了解 MySQL 優(yōu)化器如何計(jì)算SQL語(yǔ)句的查詢成本/代價(jià)以及如何分析EXPLAIN 語(yǔ)句的輸出。

解決方案

MySQL優(yōu)化器使用成本模型(cost model)，其中查詢計(jì)劃的總體成本由各種操作(operation)的成本總體決定。確定成本的主要方法是使用存儲(chǔ)提供的統(tǒng)計(jì)數(shù)據(jù)并使用所謂的成本常量（另請(qǐng)參閱下面的“對(duì)數(shù)據(jù)庫(kù)成本模型進(jìn)行更改”部分）。例如，這些統(tǒng)計(jì)數(shù)據(jù)是索引基數(shù)值（即索引中值的唯一性的度量）和每個(gè)表中的總行數(shù)。由于統(tǒng)計(jì)數(shù)據(jù)的不精確性，因?yàn)榻y(tǒng)計(jì)數(shù)據(jù)可能已經(jīng)過時(shí)，或者它是使用近似方法來計(jì)算獲取的統(tǒng)計(jì)數(shù)據(jù)（對(duì)于InnoDB數(shù)據(jù)庫(kù)尤其如此，見下文），并且值的分布未知，那么優(yōu)化器只能執(zhí)行查詢時(shí)提供查詢結(jié)果集的行數(shù)的估計(jì)值。該預(yù)估值在某些情況下非常準(zhǔn)確，但在某些情況下則不太準(zhǔn)確。 實(shí)際的實(shí)現(xiàn)方式比上面的描述更復(fù)雜，確切的細(xì)節(jié)也可能取決于查詢的類型。另請(qǐng)參閱參考手冊(cè)中的優(yōu)化器成本模型，了解 MySQL 5.7 及更高版本中優(yōu)化器成本模型的討論。 在 MySQL 5.6 及后續(xù)更高版本中，當(dāng)我們?cè)谠u(píng)估優(yōu)化器如何執(zhí)行查詢語(yǔ)句時(shí)，優(yōu)化器跟蹤功能(optimizer trace?feature)可用于深入了解優(yōu)化器的決策過程。更多詳細(xì)信息，請(qǐng)參閱注釋 2241524.1^[2] 。

更改數(shù)據(jù)庫(kù)的成本模型

在 MySQL 5.7 以及后續(xù)版本中，優(yōu)化器模型中使用的成本常量(cost constants)由數(shù)據(jù)庫(kù)用來進(jìn)行成本估算。如果需要的話，可以更改此數(shù)據(jù)庫(kù)的配置。

警告：更改數(shù)據(jù)庫(kù)的成本模型被認(rèn)為是高級(jí)數(shù)據(jù)庫(kù)管理。如果您進(jìn)行更改以驗(yàn)證其行為是否符合預(yù)期時(shí)，請(qǐng)務(wù)必小心，并在部署到生產(chǎn)環(huán)境之前進(jìn)行完全徹底充分的測(cè)試。

從 MySQL 5.7.17 開始，數(shù)據(jù)庫(kù)成本模型中有兩個(gè)可以調(diào)整的變量。數(shù)據(jù)存儲(chǔ)在mysql.engine_cost表中，默認(rèn)值為：

mysql>?SELECT?*?FROM?mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+
|?engine_name?|?device_type?|?cost_name??????????????|?cost_value?|?last_update?????????|?comment?|
+-------------+-------------+------------------------+------------+---------------------+---------+
|?default?????|???????????0?|?io_block_read_cost?????|???????NULL?|?2017-01-13?15:21:46?|?NULL????|
|?default?????|???????????0?|?memory_block_read_cost?|???????NULL?|?2017-01-13?15:21:46?|?NULL????|
+-------------+-------------+------------------------+------------+---------------------+---------+
2?rows?in?set?(0.00?sec)

io_block_read_cost和memory_block_read_cost參數(shù)分別指定從磁盤和內(nèi)存讀取數(shù)據(jù)的相對(duì)成本。將 io_block_read_cost 參數(shù)的cost_value值設(shè)置為高于 memory_block_read_cost 的值，這會(huì)使優(yōu)化器生成的查詢計(jì)劃更喜歡讀取內(nèi)存中數(shù)據(jù)，而不是從磁盤讀取數(shù)據(jù)。 還可以通過將engine_name設(shè)置為要生效的存儲(chǔ)引擎的名稱來指定每個(gè)存儲(chǔ)引擎的成本值。 當(dāng)所有變更生效后，使用 FLUSH OPTIMIZER_COSTS 命令觸發(fā)優(yōu)化器重新讀取成本模型數(shù)據(jù)。 每個(gè)會(huì)話的優(yōu)化器成本都會(huì)被緩存。只有在 FLUSH OPTIMIZER_COSTS 語(yǔ)句執(zhí)行之后啟動(dòng)的會(huì)話才會(huì)受到更改的影響。 ? 例如，通過使用以下步驟將io_block_read_cost默認(rèn)值設(shè)置為 2.0，將 InnoDB 存儲(chǔ)引擎的默認(rèn)值設(shè)置為 3.0：

1.將io_block_read_cost的默認(rèn)值更新為 2.0：

mysql>?UPDATE?mysql.engine_cost
??????????SET?cost_value?=?2.0
????????WHERE?cost_name?=?'io_block_read_cost';
Query?OK,?1?row?affected?(0.01?sec)
Rows?matched:?1?Changed:?1?Warnings:?0

2.為InnoDB存儲(chǔ)引擎添加新的成本規(guī)則，將io_block_read_cost設(shè)置為3.0：

mysql>?INSERT?INTO?mysql.engine_cost?(engine_name,?device_type,?cost_name,?cost_value,?comment)
???????VALUES?('InnoDB',?0,?'io_block_read_cost',?3.0,?'Using?a?slower?disk?for?InnoDB');
Query?OK,?1?row?affected?(0.01?sec)

3.刷新新的成本值：

mysql>?FLUSH?OPTIMIZER_COSTS;
Query?OK,?0?rows?affected?(0.00?sec)

4.驗(yàn)證新的成本值是否在查詢計(jì)劃中給出了預(yù)期結(jié)果。

5.根據(jù)需要部署到生產(chǎn)。

InnoDB

如果您的表使用InnoDB 存儲(chǔ)引擎，您還應(yīng)該注意索引統(tǒng)計(jì)信息是基于有限數(shù)量記錄的隨機(jī)樣本/采樣的估計(jì)。如果您運(yùn)行ANALYZE TABLE tablename（將tablename替換為實(shí)際的表名），然后運(yùn)行SHOW INDEXES FROM tablename，那么您將看到基數(shù)將在后續(xù)一系列操作之間波動(dòng)。表中的總行數(shù)也是如此。如果多次執(zhí)行SHOW TABLE STATUS LIKE 'tablename'，即使表上沒有發(fā)生任何更新，那么您將看到每次執(zhí)行時(shí)行總數(shù)的估計(jì)值也會(huì)有所不同（另一方面，對(duì)于MyISAM存儲(chǔ)引擎表，表中的行數(shù)是精確的，因?yàn)槠淙狈Χ喟姹究刂坪褪褂帽礞i，使得維護(hù)精確的統(tǒng)計(jì)數(shù)據(jù)變得更容易）。然而，您也可能不走運(yùn)，最終對(duì)要檢查的行數(shù)的估計(jì)相對(duì)較差。這也意味著，如果您運(yùn)行ANALYZE TABLE tablename，那么行列中的值可能會(huì)發(fā)生變化，在某些情況下甚至查詢計(jì)劃本身也會(huì)發(fā)生變化。另請(qǐng)參閱MySQL 參考手冊(cè)中的InnoDB 表限制。

注意：如果innodb_stats_on_metadata設(shè)置為ON（MySQL 5.5 及更早版本中的默認(rèn)值），InnoDB會(huì)在元數(shù)據(jù)語(yǔ)句（例如SHOW TABLE STATUS或SHOW INDEX ）執(zhí)行期間訪問INFORMATION_SCHEMA表TABLES或STATISTICS時(shí)更新統(tǒng)計(jì)信息。

為了更好地估計(jì) InnoDB 表的索引基數(shù)，可以將參數(shù)innodb_stats_sample_pages設(shè)置為更大的值。但需要注意，較大的值將導(dǎo)致索引更新時(shí)間更長(zhǎng)，并且每次打開表時(shí)都會(huì)重新計(jì)算統(tǒng)計(jì)信息，因此可能會(huì)對(duì)性能產(chǎn)生影響。另請(qǐng)參閱MySQL 優(yōu)化器團(tuán)隊(duì)的Oystein Grovlen 的博客^[3]，文中討論了更改innodb_stats_sample_pages的值如何影響索引基數(shù)計(jì)算。 innodb_stats_sample_pages選項(xiàng)是隨 MySQL 5.1.38 中的 InnoDB 插件一起引入的。在MySQL 5.1和MySQL 5.0及更早版本的內(nèi)置InnoDB中，該值無法更改，并且默認(rèn)值為8。在 MySQL 5.6.3 中，innodb_stats_sample_pages已替換為innodb_stats_transient_sample_pages選項(xiàng)。對(duì)于 MySQL 5.6.2 及更高版本，另請(qǐng)參閱innodb_stats_persistent_sample_pages。 ? 實(shí)際檢查行數(shù)示例 要更好地估計(jì)實(shí)際檢查的行數(shù)，請(qǐng)?jiān)诓樵冎昂筒樵兺瓿芍髨?zhí)行SHOW SESSION STATUS LIKE 'handler%'命令。另一種可能性是確保查詢被慢查詢?nèi)罩居涗?，慢查詢?nèi)罩具€提供檢查的總行數(shù)。

使用會(huì)話狀態(tài)變量查找檢查的行數(shù)的示例是：

mysql>?SHOW?SESSION?STATUS?LIKE?'handler%';
+----------------------------+-------+
|?Variable_name??????????????|?Value?|
+----------------------------+-------+
|?Handler_commit?????????????|?0?????|
|?Handler_delete?????????????|?0?????|
|?Handler_discover???????????|?0?????|
|?Handler_prepare????????????|?0?????|
|?Handler_read_first?????????|?0?????|
|?Handler_read_key???????????|?0?????|
|?Handler_read_last??????????|?0?????|
|?Handler_read_next??????????|?0?????|
|?Handler_read_prev??????????|?0?????|
|?Handler_read_rnd???????????|?0?????|
|?Handler_read_rnd_next??????|?0?????|
|?Handler_rollback???????????|?0?????|
|?Handler_savepoint??????????|?0?????|
|?Handler_savepoint_rollback?|?0?????|
|?Handler_update?????????????|?0?????|
|?Handler_write??????????????|?0?????|
+----------------------------+-------+
16?rows?in?set?(0.00?sec)

mysql>?SELECT?*?FROM?(SELECT?id?FROM?t1?WHERE?id?<?100)?t1?INNER?JOIN?t2?USING?(id);
+----+-----+
|?id?|?val?|
+----+-----+
|??1?|?a???|
|??2?|?b???|
|??3?|?c???|
...
|?98?|?c???|
|?99?|?a???|
+----+-----+
76?rows?in?set?(0.00?sec)

mysql>?SHOW?SESSION?STATUS?LIKE?'handler%';
+----------------------------+-------+
|?Variable_name??????????????|?Value?|
+----------------------------+-------+
|?Handler_commit?????????????|?1?????|
|?Handler_delete?????????????|?0?????|
|?Handler_discover???????????|?0?????|
|?Handler_prepare????????????|?0?????|
|?Handler_read_first?????????|?1?????|
|?Handler_read_key???????????|?100???|
|?Handler_read_last??????????|?0?????|
|?Handler_read_next??????????|?99????|
|?Handler_read_prev??????????|?0?????|
|?Handler_read_rnd???????????|?0?????|
|?Handler_read_rnd_next??????|?100???|
|?Handler_rollback???????????|?0?????|
|?Handler_savepoint??????????|?0?????|
|?Handler_savepoint_rollback?|?0?????|
|?Handler_update?????????????|?0?????|
|?Handler_write??????????????|?99????|
+----------------------------+-------+
16?rows?in?set?(0.00?sec)

這表明SQL查詢總共讀取了 300 行，分布如下：

• 1 行讀取索引中的第一個(gè)條目 (Handler_read_first)
• 99 行按鍵順序讀取下一行（Handler_read_next）
• 100 行從索引查找行（Handler_read_key）
• 100 行進(jìn)行表掃描 (Handler_read_rnd_next) 此外，由于派生表 (Handler_write)，有 99 行被寫入內(nèi)部臨時(shí)表。

參考資料