MySQL 在 select 的时候，如果 where 里面有 in 条件的话，系统会判断范围会不会太大，如果太大的话，则会跳过索引，直接查表。而影响这个范围的参数，就是 eq_range_index_dive_limit。

这本来没什么，预估到，或者在生产环境遇到慢查询的时候，再优化这个参数和 sql 即可。可是，innodb 的锁是加在索引上的。当 update 的时候，如果系统也跳过索引，就会把锁加在主键上，造成锁表。下面是一个例子。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> select * from t;
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  0 |    0 |    0 |
|  1 |    1 |    1 |
|  3 |    3 |    1 |
|  5 |    5 |    1 |
|  7 |    7 |    1 |
|  9 |    9 |    0 |
| 10 |   10 |    0 |
+----+------+------+

mysql> explain select c from t where b in (3,5);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | b             | b    | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

mysql> explain update t set c=1 where b in(3,5);
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | t     | NULL       | range | b             | b    | 5       | const |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+

当 b 的范围是 (3,5) 的时候，select 和 update 都使用了b 这个索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select c from t where b in (3,5,7);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | b             | NULL | NULL    | NULL |    7 |    42.86 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql> explain update t set c=1 where b in(3,5,7);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE      | t     | NULL       | index | b             | PRIMARY | 4       | NULL |    7 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

当 b 的范围是 (3,5,7) 的时候，select 没有使用索引，直接查表。update 使用了主键。

如果这里

1
update t set c=1 where b in (3,5,7);

是一个事务，同时开另一个事务

1
update t set c=1 where a=0;

这时第一个 sql 会锁表，第二个 sql 就会进入等待。注意这里还不是 gap lock 的情况。

参考：
简述 mysql 的 eq_range_index_dive_limit 参数作用：https://blog.csdn.net/java_zone/article/details/53383876