MySQL优化特性 Index Condition Pushdown(ICP，索引条件下推)、MRR、BKA

一、Index Condition Pushdown

• 概念：ICP 是 MySQL 针对索引从表中检索数据时的一种特性；
• 未使用索引下推时查询处理流程：
  1. 通过二级索引读取一条索引记录 📝 然后使用叶子节点中的主键值进行回表查询读取整个表行；
  2. 判断这行记录是否复合 WHERE 条件，具体请看 👇 下图；

• 使用索引下推时查询处理过程：
  1. 根据二级索引读取一条索引记录，但不进行回表取出整行数据；
  2. 判断记录是否满足 WHERE 条件中的一部分，只使用索引字段来检查，如果索引字段不满足条件则继续获取下一条索引记录；
  3. 如果索引字段满足 WHERE 则回表查询整行数据记录；
  4. 再判断 WHERE 条件中的剩余部分，选择满足条件的记录；

• Summarizing：ICP 的意思就是筛选字段在索引中的 WHERE 条件从服务器层下推到存储引擎层，这样可以在存储引擎层过滤数据。由此可见，ICP 可以减少存储引擎访问基表的次数和 MySQL 服务器访问存储引擎的次数。
• Use Restrictions 🚫：
  1. 只能用于 innodb 或 MyISAM 的数据库；
  2. 适应于二级索引；
  3. 不适用于虚拟字段的二级索引；

二、Multi-Range Read(MRR)

• 作用：将二级索引回表读的随机 IO 优化为顺序 IO；

• 未使用 MRR 下的回表查询：

  1. 当二级索引需要做回表查询，那么此时主键顺序和二级索引顺序不一致会导致大量的随机 I/O 读，非常消耗资源；
     

• 使用 MRR 下的回表查询：

  1. 进行二级查询时首先会先将 ROW ID 放到缓冲区中进行排序，排序之后再进行回表访问，此时就是顺序 I/O
  2. 缓冲区的参数为 read_rnd_buffer_size 控制缓冲区的大小；

  

三、Batched Key Access(BKA)

• 作用：在多表连接 🔗 中可以使用顺序 I/O 基于 MRR 实现；
• 表连接中未使用 BKA：
  1. t1 表连接 t2 表中读取数据时是随机 I/O；

• 表连接中使用 BKA：
  1. 当 t1 表连接 t2 表访问 t2 时，先将 t1 中的相关的字段放到 join buffer 中，然后利用 MRR 的特性接口进行排序；
  2. 通过顺序 I/O 到 t2 表中查找数据；
     
  3. 使用条件：关联字段需要有索引，否则使用的就不是 BKA 算法而是 BNL；