MySQL-优化器如何估算sql成本

sql成本

MySQL以以下两个指标来估算一个sql的执行成本

1. IO成本：磁盘与内存进行交互是以页作为单位，读取一次页进内存就计算一次IO成本，MySQL规定一次IO操作的成本是1.0。
2. CPU成本：MySQL查询出来的所有记录，还要计算是否满足条件，MySQL规定一次CPU操作的成本是0.2。

优化器

在sql达到优化器的时候就会生成执行计划，计算执行这条sql的多个方式，最后会采用分值最低的访问方式。

全表扫描分值计算

MySQL可以很快计算出全表扫描的分值，因为MySQL有多少记录条数以及记录占用的空间，通过这些属性可以计算出表占用的页。
分值 = 页数 * 1.0 + 记录数 * 0.2

单索引查询分值计算

分值 = 1.0 + 记录数 * 0.2 + 记录数 * 1.0 + 记录数 * 0.2

• 1.0：MySQL设计者规定在二级索引读取页的成本，无论多少，统一为1.0
• 记录数 * 0.2：二级索引记录计算匹配的成本
• 记录数 * 1.0：回表成本，MySQL设计者规定无论如何每一个二级索引主键回表都认为会加载一次页。
• 记录数 * 0.2：聚餐索引记录计算匹配的成本

二级索引计算页数和记录数

找到第一个匹配的记录，找到最后一个匹配的记录，往后查询10个页。

1. 匹配的记录已经全部找到那么记录数和页数就可以直接得到了。
2. 如果没有查询完整，计算这10个匹配的平均记录数，通过上一层的B+树可以获得第一个匹配的记录和最后一个匹配的记录之间有多少页，再通过平均值 * 页数。

连接查询分值计算

分值 = 驱动表单次查询的成本 + 驱动表查询出来的记录数 * 被驱动表单次查询的成本

optimizer trace

虽然我们可以用EXPLAIN看到优化器最终的选择，但是对我来说优化器还是一个黑盒，我们看不到优化器是如何选择的，为此MySQL设计者提供了optimizer trace的功能，可以让我们看到优化器是如何选择执行计划的，在执行任意一条MySQL语句之后通过执行获得优化器对sql的具体分析以及选择：

SELECT * FROM information_scheme.optimizer_trace