SQL优化教程之in与range查询

前言

《高性能MySQL》里面提及用in这种方式可以有效的替代一定的range查询，提升查询效率， 因为在一条索引里面，range字段后面的部分是不生效的（ps.需要考虑 ICP） 。MySQL优化器将in这种方式转化成 n*m 种组合进行查询，最终将返回值合并，有点类似union但是更高效。

MySQL在 IN() 组合条件过多的时候会发生很多问题。查询优化可能需要花很多时间，并消耗大量内存。新版本MySQL在组合数超过一定的数量就不进行计划评估了，这可能导致MySQL不能很好的利用索引。

这里的 一定数 在MySQL5.6.5以及以后的版本中是由eq_range_index_dive_limit这个参数控制 。默认设置是10，一直到5.7以后的版本默认修改为200，当然可以手动设置的。5.6手册说明如下：

The eq_range_index_dive_limit system variable enables you to configure the number of values at which the optimizer switches from one row estimation strategy to the other. To disable use of statistics and always use index dives, set eq_range_index_dive_limit to 0. To permit use of index dives for comparisons of up to N equality ranges, set eq_range_index_dive_limit to N + 1. eq_range_index_dive_limit is available as of MySQL 5.6.5. Before 5.6.5, the optimizer uses index dives, which is equivalent to eq_range_index_dive_limit=0.

换言之，

eq_range_index_dive_limit = 0 只能使用index dive

0 < eq_range_index_dive_limit <= N 使用index statistics

eq_range_index_dive_limit > N 只能使用index dive

在MySQL5.7版本中将默认值从10修改成200目的是为了尽可能的保证范围等值运算（IN()）执行计划尽量精准，因为IN()list的数量很多时候都是超过10的。

在MySQL的官方手册上有这么一句话:

the optimizer can estimate the row count for each range using dives into the index or index statistics.

大意:

优化器预估每个范围段－－如"a IN (10, 20, 30)" 视为等值比较, 括3个范围段实则简化为3个单值,分别是10,20,30－－中包括的元组数,用范围段来表示是因为 MySQL 的"range"扫描方式多数做的是范围扫描，此处单值可视为范围段的特例;

估计方法有2种:

1. dive到index中即利用索引完成元组数的估算,简称index dive;
2. index statistics:使用索引的统计数值,进行估算;

对比这两种方式

1. index dive: 速度慢,但能得到精确的值（MySQL的实现是数索引对应的索引项个数，所以精确）
2. index statistics: 速度快,但得到的值未必精确

简单说，**选项 eq_range_index_dive_limit 的值设定了 IN列表中的条件个数上线，超过设定值时，会将执行计划从 index dive 变成 index statistics **。

为什么要区分这2种方式呢?

1. 查询优化器会使用代价估算模型计算每个计划的代价,选择其中代价最小的
2. 单表扫描时,需要计算代价;所以单表的索引扫描也需要计算代价
3. 单表的计算公式通常是: 代价 = 元组数 * IO平均值
4. 所以不管是哪种扫描方式,都需要计算元组数
5. 当遇到“a IN (10, 20, 30)”这样的表达式的时候，发现a列存在索引，则需要看这个索引可以扫描到的元组数由多少而计算其索引扫描代价，所以就用到了本文提到的“index dive”、“index statistics”这2种方式。

讨论主题

1. range查询与索引使用
2. eq_range_index_dive_limit的说明

range查询与索引使用

SQL如下：

SELECT * FROM pre_forum_post WHERE tid=7932552 AND invisible IN('0','-2') ORDER BY dateline DESC LIMIT 10;