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 的成本分析】
推荐阅读
- 肺癌晚期到底还要不要治? 肺癌晚期能治吗
- 要不要为爱情奋不顾身,为爱情奋不顾身值得吗-
- 领导加微信怎么打招呼望多指教 方便联系工作,领导加你微信后要不要打招呼-
- 化妆|夏季化妆有什么技巧?想要不脱妆最好这样做,妆容持久还服帖
- 酸豆角要不要炒,酸豆角不炒可以直接吃吗-
- 炒面面煮好后要不要过凉水,炒面煮熟后要过冷水吗-
- 香辣虾炸虾的时候要不要淀粉,虾可以裹淀粉炸吗--
- 幽门螺杆菌检讨阳性毕竟要不要治? 幽门螺旋杆菌阳性是什么意思?
- 车辆的商业险到底要不要买? 汽车商业险可以不买吗
- 等额本息30年的房贷要不要提前还款,到底划不划算?