要不要走索引?MySQL 的成本分析( 二 )

30435 是查询二级索引的 IO 成本和 CPU 成本之和,再加上回表查询聚簇索引的 IO 成本和 CPU 成本之和 。
2、使用 create_time 进行索引扫描需要扫描 23758 行,成本是 28511 。
{  "index": "create_time",  "ranges": [    "0x5e2a79d0 < create_time"  ],  "rows": 23758,  "cost": 28511,  "chosen": false,  "cause": "cost"}3、全表扫描 100086 条记录的成本是 20306 。(和上面计算的一致)
{  "considered_execution_plans": [{    "table": "`person`",    "best_access_path": {      "considered_access_paths": [{        "rows_to_scan": 100086,        "access_type": "scan",        "resulting_rows": 100086,        "cost": 20306,        "chosen": true      }]    },    "rows_for_plan": 100086,    "cost_for_plan": 20306,    "chosen": true  }]}所以 MySQL 最终选择了全表扫描方式作为执行计划 。
把 SQL 中的 create_time 条件从 05:00 改为 06:00,再次分析 OPTIMIZER_TRACE 可以看到:
{  "index": "create_time",  "ranges": [    "0x5e2a87e0 < create_time"  ],  "rows": 16588,  "cost": 19907,  "chosen": true}因为是查询更晚时间的数据,走 create_time 索引需要扫描的行数从 23758 减少到了 16588 。这次走这个索引的成本 19907 小于全表扫描的 20306,更小于走 name_score 索引的 30435 。
所以这次执行计划选择的是走 create_time 索引 。
解决方案有时会因为统计信息的不准确或成本估算的问题,实际开销会和 MySQL 统计出来的差距较大,导致 MySQL 选择错误的索引或是直接选择走全表扫描,这个时候就需要人工干预,使用强制索引了 。
比如,像这样强制走 name_score 索引:
explain select * from person FORCE INDEX(name_score) where NAME >'name84059' and create_time>'2020-01-24 00:00:00'小结本文通过一个例子,谈到了 MySQL 还有另外一个索引失效的场景,即分析器成本分析 。
对于是否走索引,我们要学会使用 explain 进行分析 。另外,在 MySQL 5.6 及之后的版本中,我们可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程 。
整理自极客时间《JAVA开发常见错误》学习笔记
公众号:杨同学technotes

【要不要走索引?MySQL 的成本分析】


推荐阅读