MySQL中包含IN子句的语句是怎样执行的
MySQL
中包含IN子句的语句肯定熟悉的不能再熟悉了，几乎天天用，时时用。可是很多小伙伴并不知道包含IN子句的语句是怎样执行的，在一些查询优化的场景中就开始找不着北了，本篇文章就来唠叨一下MySQL中包含IN子句的语句是怎样执行的（以MySQL 5.7的InnoDB存储引擎为例）。准备工作
CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;
t
中包含两个索引：
-
以 id列为主键的聚簇索引 -
为 key1列建立的二级索引
mysql> SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
从B+树中定位记录
SELECT * FROM t WHERE
key1 >= 'b' AND key1 <= 'c';
小贴士：原谅我把索引对应的复杂的B+树结构搞了一个极度精简版，为了突出重点，我们忽略掉了页的结构，直接把所有的叶子节点的记录都放在一起展示。我们想突出的重点就是：B+树叶子节点中的记录是按照索引列值大小排序的，对于的聚簇索引来说，它对应的B+树叶子节点中的记录就是按照id列排序的，对于idx_key1二级索引来说，它对应的B+树叶子节点中的记录就是按照key1列排序的。
key1
列的值在
['b', 'c']
这个区间中的记录，那么就需要：
-
先通过 idx_key1索引对应的B+树快速定位到key1列值为'b'、并且最靠左的那条二级索引记录，该二级索引记录中包含着对应的主键值，根据这个主键值再到聚簇索引中定位到完整的记录（这个过程称之为回表），将其返回给server层，server层再发送给客户端。 -
记录按照键值由小到大的顺序排列成一个单链表的形式，所以我们可以沿着这个单链表接着定位到下一条二级索引记录，并且执行回表操作，将完整的记录交给server层之后发送给客户端。 -
继续沿着记录的单向链表查找，重复上述过程，直到找到的二级索引记录的key1列的值不满足 key1 <= 'c'的这个条件，如图所示，也就是当我们在idx_key1二级索引中找到了key1='ca'的那条记录后，发现它不符合key1 <= 'c'的条件，所以就停止查找。
B+
树查找一个键值在某一个范围区间的记录的过程。
包含IN子句的执行过程
SELECT * FROM t WHERE
key1 IN ('b', 'c');
-
['b', 'b'] -
['c', 'c']
B+
树去定位两次记录所在的位置：
-
先定位键值在范围区间 ['b', 'b']的记录： -
先通过 idx_key1索引对应的B+树快速定位到key1列值为'b'、并且最靠左的那条二级索引记录，之后回表将其发送给server 层后再发送给客户端。 -
再沿着记录组成的单链表把符合 key1=b的二级索引记录找到，并且回表后发送给server层，之后再发送给客户端。 -
重复上述过程，直到找到的二级索引记录的key1列的值不满足 key1 = 'b'的这个条件为止。 -
再定位键值在范围区间 ['c', 'c']的记录：查找过程类似，就不多赘述了。
B+
树定位记录的次数就越多。
IN子句中参数值重复的情况
SELECT * FROM t WHERE
key1 IN ('b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b');
['b', 'b']
。
IN子句的参数顺序问题
SELECT * FROM t WHERE key1 IN ('c', 'b');
key1 = 'c'
的记录，再去找
key1 = 'b'
的记录么？如果是这样的话，下边两条语句岂不是可能发生死锁：
事务T1中的语句一：
SELECT * FROM t WHERE
key1 IN ('b', 'c') FOR UPDATE;
事务T2中的语句二：
SELECT * FROM t WHERE
key1 IN ('c', 'b') FOR UPDATE;
IN ('c', 'b')
，那优化器也会先让存储引擎去找键值在
['b', 'b']
这个范围区间中的记录，然后再去找键值在
['c', 'c']
这个范围区间中的记录。
系统变量eq_range_index_dive_limit对IN子句的影响
SELECT * FROM t WHERE
key1 IN ('b', 'c');
idx_key1
执行查询的话，键值在
['b', 'b']
和
['c', 'c']
这两个范围区间的记录共有多少条，然后通过一定方式计算出成本，与全表扫描的成本相对比，选取成本更低的那种方式执行查询。
-
如果IN子句对应的范围区间比较少，那么将率先去访问一下存储引擎，看一下每个范围区间中的记录有多少条（如果范围区间的记录比较少，那么统计结果就是精确的，反之会采用一定的手段计算一个模糊的值，当然算法也比较麻烦，我们就不展开说了，小册里有说），这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。 -
如果IN子句对应的范围区间比较多，这样就不能采用index dive的方式去真正的访问二级索引idx_key1（因为那将耗费大量的时间），而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条（很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多）。
mysql> SHOW VARIABLES LIKE 'eq_range_index_dive_limit';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200 |
+---------------------------+-------+
1 row in set (0.20 sec)
近期精彩内容推荐：
在看点这里好文分享给更多人↓↓