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

标签：Optimizer read Doc mysql 1327497.1 Handler innodb MySQL cost

本文是对这篇文章How the MySQL Optimizer Calculates the Cost of a Query (Doc ID 1327497.1)的翻译，翻译如有不当的地方，敬请谅解，请尊重原创和翻译劳动成果，转载的时候请注明出处。谢谢！

适用于：

MySQL 4.0 及后续更高的版本

本文档中的内容适用于任何平台。

目标

了解 MySQL 优化器如何计算SQL语句的查询成本/代价以及如何分析EXPLAIN 语句的输出。

解决方案

MySQL优化器使用成本模型(cost model)，其中查询计划的总体成本由各种操作(operation)的成本总体决定。确定成本的主要方法是使用存储提供的统计数据并使用所谓的成本常量（另请参阅下面的“对数据库成本模型进行更改”部分）。例如，这些统计数据是索引基数值（即索引中值的唯一性的度量）和每个表中的总行数。由于统计数据的不精确性，因为统计数据可能已经过时，或者它是使用近似方法来计算获取的统计数据（对于InnoDB数据库尤其如此，见下文），并且值的分布未知，那么优化器只能执行查询时提供查询结果集的行数的估计值。该预估值在某些情况下非常准确，但在某些情况下则不太准确。实际的实现方式比上面的描述更复杂，确切的细节也可能取决于查询的类型。另请参阅参考手册中的优化器成本模型，了解 MySQL 5.7 及更高版本中优化器成本模型的讨论。在 MySQL 5.6 及后续更高版本中，当我们在评估优化器如何执行查询语句时，优化器跟踪功能(optimizer trace feature)可用于深入了解优化器的决策过程。更多详细信息，请参阅注释 2241524.1 。

更改数据库的成本模型

在 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 的博客，文中讨论了更改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 行被写入内部临时表。

参考资料：

NOTE:2241524.1 - How To Use the Optimizer Trace Feature in MySQL 5.6 and Later to Investigate Why a Given Query Plan Was Chosen?

https://dev.mysql.com/doc/refman/en/cost-model.html

NOTE:1464378.1 - Optimizer Enhancements in MySQL 5.6: Multi-Range Read (MRR), Block Nested-Loop (BNL), Batched Key Access (BKA)

NOTE:1328010.1 - Differing Query Execution Times

NOTE:1327825.1 - How Index Distribution Influences the MySQL Optimizer Query Plan

NOTE:1328002.1 - Controlling InnoDB Estimated Optimizer Statistics in MySQL 5.1 and Later

https://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html

https://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html

NOTE:1364899.1 - The MySQL Optimizer Does Not Use the Full Width of the Index

https://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

http://en.wikipedia.org/wiki/cardinalitydelete reference

http://oysteing.blogspot.com/2011/04/more-stable-query-execution-time-by.html

https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_cardinality

https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata

NOTE:1451889.1 - What is the Difference Between OPTIMIZE TABLE and ANALYZE TABLE in MySQL Server?

https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_sample_pages

https://dev.mysql.com/doc/refman/5.5/en/show-status.html

https://dev.mysql.com/doc/refman/5.5/en/analyze-table.html

https://dev.mysql.com/doc/refman/5.5/en/show-index.html

https://dev.mysql.com/doc/refman/5.5/en/show-table-status.html

NOTE:1023539.1 - What To Do If MySQL Does Not Execute the Wished Query Plan

NOTE:2317760.1 - Example Discussion of How the Optimizer Works

标签：Optimizer,read,Doc,mysql,1327497.1,Handler,innodb,MySQL,cost
From： https://www.cnblogs.com/kerrycode/p/17813862.html