MySQL中SELECT与UPDATE的range_optimizer_max_mem_size行为差异咨询

我有一张包含700万行数据的example_table，range_optimizer_max_mem_size参数设置为2MB。已知当查询的范围内存超出该限制时，优化器会切换为全表扫描。但发现特殊现象：使用4000个ID的UPDATE语句会超出限制并触发全表扫描，而相同条件的SELECT语句却未超出该内存限制。

环境与测试详情 #

MySQL版本：5.7.18

表结构 #

mysql> show create table example_table;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                     |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| example_table | CREATE TABLE `example_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=7000001 DEFAULT CHARSET=latin1 |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

索引信息 #

mysql> show indexes from example_table;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| example_table |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| example_table |          1 | name     |            1 | name        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| example_table |          1 | email    |            1 | email       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

数据量 #

mysql> select count(*) from example_table;
+----------+
| count(*) |
+----------+
|  7000000 |
+----------+
1 row in set (1.22 sec)

EXPLAIN结果 #

UPDATE语句

mysql> explain UPDATE example_table SET age=100 where (id IN (<4k ids>) and ((id >= 1) and (id <= 7000000)));
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | UPDATE      | example_table | NULL       | index | NULL          | PRIMARY | 8       | NULL | 7000000 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.10 sec)

SELECT语句

mysql> explain Select * from example_table where (id IN (<4k ids>) and ((id >= 1) and (id <= 7000000)));
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | example_table | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 4000 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

差异原因分析 #

1. SELECT的范围优化器会做区间合并
   SELECT语句处理IN(<4k ids>)时，优化器会将离散的ID列表转换为连续的range区间（比如把IN(1,2,3,5,6)合并成id BETWEEN 1 AND 3 OR id BETWEEN 5 AND 6），大幅减少需要存储的索引查找项数量，内存消耗远低于2MB的限制，因此能正常使用PRIMARY KEY的range扫描。

2. UPDATE的范围优化器不做区间合并
   UPDATE语句在处理IN列表时，不会执行区间合并优化，而是为每个ID单独生成对应的索引查找计划。每个bigint类型的ID占8字节，加上优化器需要的额外结构开销，4000个ID的内存占用会快速逼近甚至超过2MB的range_optimizer_max_mem_size阈值。当内存超出限制时，优化器会放弃索引扫描，切换为全表扫描。

3. 冗余条件不影响核心逻辑
   WHERE子句中的(id >= 1 and id <= 7000000)属于冗余条件（IN列表中的ID必然落在该范围内），不会对两种语句的内存计算逻辑产生影响。

内容的提问来源于stack exchange，提问作者logavanan logi