MySQL内核月报 2015.03-MySQL · 优化限制· MySQL index_condition_pushdown

<b>背景</b>

mysql 5.6 开始支持index_condition_pushdown特性，即server层把可以在index进行filter的谓词传递给引擎层完成过滤，然后结果返回到server。

<b>工作方式</b>

下面看一下innodb的处理方式:

通过设置set global optimizer_switch= "index_condition_pushdown=on"来启用这个特性。

例如:

1. 评估

<dl><dd>在执行计划评估阶段，通过push_index_cond函数把index filter谓词传递给引擎handler。</dd></dl>

2. 执行

<dl><dd>innodb通过row_search_for_mysql获取每行记录的时候，使用innobase_index_cond函数来check index filter谓词条件是否成立。通过这种方式来完成index上的filter，整个过程并不复杂。</dd></dl>

<b>收益和限制</b>

下面来看一下index_condition_pushdown的收益和限制:

收益: index_condition_pushdown所带来的收益可以从三个方面来看:

1. 数据copy

<dl><dd>减少了innodb层返回给server层的数据量，减少了数据copy。</dd></dl>

2. 随机读取

<dl><dd>对于二级索引的扫描和过滤，减少了回primary key上进行随机读取的次数</dd></dl>

3. 记录锁

<dl><dd>记录锁是在innodb层完成的，比如如果是select for update语句，就会发现index_condition_pushdown会大大减少记录锁的个数。</dd></dl>

限制: 目前index_condition_pushdown还有诸多的限制:

1. 索引类型

<dl><dd>如果索引类型是primary key，就不会采用，因为index_condition_pushdown最大的好处是减少回表的随机io，所以如果使用的index是pk，那么收益就大大减少，不过mysql官方也在从新评估是否采用，见wl#6061。</dd></dl>

2. 性能衰减

3. sql类型

<dl></dl>

<dd>1. 不支持多表update和delete语句，因为select和update会共用handler，而一个是一致性读，一个是当前读，同样的filter都apply的话，update会找不到记录。</dd>

<dd>2. 如果join是const 或者 system，不能使用。 因为const和system做了特别优化，只执行一次，做了缓存，而应用filter的话，会产生数据一致性问题。</dd>

<b>索引设计的原则</b>

除了mysql提供的这些新特性以外，dba或者开发在设计index的时候，应该遵循的一些原则:

<dd>1. 查询谓词都能够通过index进行扫描</dd>

<dd>2. 排序谓词都能够利用index的有序性</dd>

<dd>3. index包含了查询所需要的所有字段</dd>

这就是传说中的three-star index。

可以参考《wiley,.relational.database.index.design.and.the.optimizers》

mysql的index_condition_pushdown，前进了一大步，不过相比较oracle的index扫描方式，还有空间。比如oracle的index扫描支持的index skip scan方式。