[翻譯]——How the MySQL Optimizer Calculates the Cost of a Query (Doc ID 1327497.1)

本文是對這篇文章How the MySQL Optimizer Calculates the Cost of a Query (Doc ID 1327497.1)^[1]的翻譯，翻譯如有不當的地方，敬請諒解，請尊重原創和翻譯勞動成果，轉載的時候請註明出處。謝謝！

適用於：

MySQL 4.0 及後續更高的版本

本文檔中的內容適用於任何平臺。

目標

瞭解 MySQL 優化器如何計算SQL語句的查詢成本/代價以及如何分析EXPLAIN 語句的輸出。

解決方案

MySQL優化器使用成本模型(cost model)，其中查詢計劃的總體成本由各種操作(operation)的成本總體決定。確定成本的主要方法是使用存儲提供的統計數據並使用所謂的成本常量（另請參閱下麵的“對資料庫成本模型進行更改”部分）。例如，這些統計數據是索引基數值（即索引中值的唯一性的度量）和每個表中的總行數。由於統計數據的不精確性，因為統計數據可能已經過時，或者它是使用近似方法來計算獲取的統計數據（對於InnoDB資料庫尤其如此，見下文），並且值的分佈未知，那麼優化器只能執行查詢時提供查詢結果集的行數的估計值。該預估值在某些情況下非常準確，但在某些情況下則不太準確。 實際的實現方式比上面的描述更複雜，確切的細節也可能取決於查詢的類型。另請參閱參考手冊中的優化器成本模型，瞭解 MySQL 5.7 及更高版本中優化器成本模型的討論。 在 MySQL 5.6 及後續更高版本中，當我們在評估優化器如何執行查詢語句時，優化器跟蹤功能(optimizer trace feature)可用於深入瞭解優化器的決策過程。更多詳細信息，請參閱註釋 2241524.1^[2] 。

更改資料庫的成本模型

在 MySQL 5.7 以及後續版本中，優化器模型中使用的成本常量(cost constants)由資料庫用來進行成本估算。如果需要的話，可以更改此資料庫的配置。

警告：更改資料庫的成本模型被認為是高級資料庫管理。如果您進行更改以驗證其行為是否符合預期時，請務必小心，併在部署到生產環境之前進行完全徹底充分的測試。

從 MySQL 5.7.17 開始，資料庫成本模型中有兩個可以調整的變數。數據存儲在mysql.engine_cost表中，預設值為：

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參數分別指定從磁碟和記憶體讀取數據的相對成本。將 io_block_read_cost 參數的cost_value值設置為高於 memory_block_read_cost 的值，這會使優化器生成的查詢計劃更喜歡讀取記憶體中數據，而不是從磁碟讀取數據。 還可以通過將engine_name設置為要生效的存儲引擎的名稱來指定每個存儲引擎的成本值。 當所有變更生效後，使用 FLUSH OPTIMIZER_COSTS 命令觸發優化器重新讀取成本模型數據。 每個會話的優化器成本都會被緩存。只有在 FLUSH OPTIMIZER_COSTS 語句執行之後啟動的會話才會受到更改的影響。   例如，通過使用以下步驟將io_block_read_cost預設值設置為 2.0，將 InnoDB 存儲引擎的預設值設置為 3.0：

1.將io_block_read_cost的預設值更新為 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存儲引擎添加新的成本規則，將io_block_read_cost設置為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.驗證新的成本值是否在查詢計劃中給出了預期結果。

5.根據需要部署到生產。

InnoDB

如果您的表使用InnoDB 存儲引擎，您還應該註意索引統計信息是基於有限數量記錄的隨機樣本/採樣的估計。如果您運行ANALYZE TABLE tablename（將tablename替換為實際的表名），然後運行SHOW INDEXES FROM tablename，那麼您將看到基數將在後續一系列操作之間波動。表中的總行數也是如此。如果多次執行SHOW TABLE STATUS LIKE 'tablename'，即使表上沒有發生任何更新，那麼您將看到每次執行時行總數的估計值也會有所不同（另一方面，對於MyISAM存儲引擎表，表中的行數是精確的，因為其缺乏多版本控制和使用表鎖，使得維護精確的統計數據變得更容易）。然而，您也可能不走運，最終對要檢查的行數的估計相對較差。這也意味著，如果您運行ANALYZE TABLE tablename，那麼行列中的值可能會發生變化，在某些情況下甚至查詢計劃本身也會發生變化。另請參閱MySQL 參考手冊中的InnoDB 表限制。

註意：如果innodb_stats_on_metadata設置為ON（MySQL 5.5 及更早版本中的預設值），InnoDB會在元數據語句（例如SHOW TABLE STATUS或SHOW INDEX ）執行期間訪問INFORMATION_SCHEMA表TABLES或STATISTICS時更新統計信息。

為了更好地估計 InnoDB 表的索引基數，可以將參數innodb_stats_sample_pages設置為更大的值。但需要註意，較大的值將導致索引更新時間更長，並且每次打開表時都會重新計算統計信息，因此可能會對性能產生影響。另請參閱MySQL 優化器團隊的Oystein Grovlen 的博客^[3]，文中討論了更改innodb_stats_sample_pages的值如何影響索引基數計算。 innodb_stats_sample_pages選項是隨 MySQL 5.1.38 中的 InnoDB 插件一起引入的。在MySQL 5.1和MySQL 5.0及更早版本的內置InnoDB中，該值無法更改，並且預設值為8。在 MySQL 5.6.3 中，innodb_stats_sample_pages已替換為innodb_stats_transient_sample_pages選項。對於 MySQL 5.6.2 及更高版本，另請參閱innodb_stats_persistent_sample_pages。   實際檢查行數示例 要更好地估計實際檢查的行數，請在查詢之前和查詢完成之後執行SHOW SESSION STATUS LIKE 'handler%'命令。另一種可能性是確保查詢被慢查詢日誌記錄，慢查詢日誌還提供檢查的總行數。

使用會話狀態變數查找檢查的行數的示例是：

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 行讀取索引中的第一個條目 (Handler_read_first)
• 99 行按鍵順序讀取下一行（Handler_read_next）
• 100 行從索引查找行（Handler_read_key）
• 100 行進行表掃描 (Handler_read_rnd_next) 此外，由於派生表 (Handler_write)，有 99 行被寫入內部臨時表。

參考資料