准线上事故之MySQL优化器索引选错( 二 ) _MySQL

附书中关于mysql执行的过程图

文章插图
图片
再来看书中关于优化器的执行过程图

文章插图
图片
从以上资料中，我们得出了一些结论，基于这些结论，最后我们可以思考一些解决办法：

在MySQL里面，优化器的优化依据是执行成本，它的本质是CBO【Cost-based Optimizer，基于成本的优化器】，也就是说执行计划的生成是基于成本的。
MySQL优化器工作的前提是了解数据，工作的目的是解析SQL，生成执行计划。但是优化器并没有想象中的那么完善，执行成本主要基于行数去决定，但是扫描行数并不是唯一的执行策略，优化器同时会结合是否使用临时表、是否排序、查询数量等因素进行综合判断。
总的来说，我们上面出现的三种奇怪现象都可以用上面优化器的判断标准去解释，子查询（临时表）、order by（排序）、limit（查询数量）。

这里我考虑使用优化器的trace工具来详细分析下limit 1 和 limit 3为什么走了不同索引。由于trace会影响性能，我们把部分数据还原到本地进行测试，两次执行sql分别如下：
trace分析LIMIT 3

set optimizer_trace="enabled=on";select `bonus_id`, `bonus_code`, `over_at`, `status`, `bonus_num`, `product_id` from `zz_test_table` where `user_id` = '16723149' and `over_at` > '2020-11-25 20:45:41' and `status` = 0 and `bonus_id` in ('382364983', '486697270', '486834963') order by `over_at` asc limit 3select * FROM information_schema.optimizer_trace;set optimizer_trace="enabled=off";

LIMIT 3 分析结果

文章插图
图片
具体参数解析如下：

"range_analysis": {"table_scan": {"rows": 1446041, "cost": 695910 }} 表示全表扫描操作预估会扫描到大约1446041行数据，属于非常大的操作量，全表扫描的预计代价（时间或资源消耗）为695910 。
"potential_range_indices": 列出了查询优化器分析后认为可以使用的索引。
PRIMARY 索引，在本次查询中是可用的。这个索引基于 bonus_id 这一列，idx_over_at 索引，也在本次查询中是可用的。

trace分析LIMIT 1

set optimizer_trace="enabled=on";select `bonus_id`, `bonus_code`, `over_at`, `status`, `bonus_num`, `product_id` from `zz_test_table` where `user_id` = '16723149' and `over_at` > '2020-11-25 20:45:41' and `status` = 0 and `bonus_id` in ('382364983', '486697270', '486834963') order by `over_at` asc limit 1select * FROM information_schema.optimizer_trace;set optimizer_trace="enabled=off";

LIMIT 1 分析结果

文章插图
图片
【准线上事故之MySQL优化器索引选错】具体参数解析如下：

"rechecking_index_usage": 代表查询优化器对我们的索引进行了重新检查和考虑。
{"recheck_reason": "low_limit", "limit": 1, "row_estimate": 3,} ：原因(recheck_reason)是因为 LIMIT 参数比较低（只有1），即查询只需要返回一行记录，而先前的索引选择可能返回的记录大于1（estimated 3行）。
"range_analysis": {"table_scan": {"rows": 1446041, "cost": 1.74e6 }} 这是查询优化器对主键（通常被视作一种默认索引）进行全表扫描的预估，大约有1446041行数据，预计的成本（用时 or IO次数）是1.74e6 。
"potential_range_indices": 这列出了查询优化器考虑过的索引和它们可用性。
PRIMARY 是第一个索引，也就是主键索引。它在这次查询中并不可用。原因 not_applicable 表示这个索引在查询时并不适用。idx_over_at 是另一个被考虑的索引，结果是可用的。

通过这段日志，我们可以知道查询优化器为了优化查询操作（特别是对 LIMIT 1的优化）做出了一系列的决策和调整，当limit 1的时候，查询优化器认为不使用主键索引的成本会更小。因为这在优化器的成本分析中是更优更快的查询方式。老实说，这里感觉MySQL有点自作聪明了。