高性能MySQL part4 提高查询性能

案例

一：为什么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的方式和算法