案例
一:为什么Range执行效率差?
- 根据查询条件计算所有的possbile key
- 计算全表扫描代价 (cost_all)
- 计算最小的索引范围访问代价–核心步骤,直接决定了Range查询效率
对每个possible keys(可选索引),调用record_in_ranges函数计算范围中的rows
根据rows,计算二级索引访问代价
获取cost最小的二级索引访问代价(cost_range)
- 选择最小优化访问代价:cost_all与cost_range二选一
原因分析:
- 查询性能仅次于全表扫描(ALL)和全索引扫描(INDEX)三者需要重点关注
- Range使用records_in_range函数对估算每个值范围的rows,结果依赖于possible_keys
- possible_keys越多,随机IO代价越大,Range查询效率越差
总结:
- 减少possbile_key,减少records_in_range调用
- 删除冗余索引,可以用工具pt-index-usage检查
- 删除重复索引,可以用工具pt-duplicate-key-checker检查
二:force index的应用,告别possible_keys选择的忧虑
减少其他possbile_key的干扰
三:拓展知识
ICP优化(Index Condition Pushdown)
简介: 是MySQL使用索引从表中检索数据的一种优化方式。目的是减少从基表中全记录读取操作的数量,从而降低IO操作。
原理:
- 禁用ICP,存储引擎会通过遍历索引查找基表中的行,然后返回给MySQL Server层,再去为这些数据进行where后的条件的过滤
- 开启ICP,如果部分where条件能使用索引中的字段,MySQL Server会把这部分下推到存储引擎层,存储引擎通过索引过滤,把满足的行从表中读取出来。ICP能减少
- 引擎层访问基表的次数和MySQL Server访问存储引擎的次数
tips:InnoDB,ICP只适用于辅助索引
MRR优化(Multi-Range Read)
简介: 是MySQL优化器随机IO转化为顺序IO以降低查询过程中IO开销的一种手段
BKA(Batched Key Access)被join的表有索引可用
简介: 是MySQL优化器提高join性能的一种手段,它是一种算法
BNL(Block Nested Loop)被join的表无索引时
简介: 是默认的处理表join的方式和算法