为何嵌套IN子查询与分步执行的SQL性能差异巨大？

表结构 #

CREATE TABLE `user_bill_cp` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'gmt_create',
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'gmt_modified',
  `user_id` bigint unsigned NOT NULL COMMENT 'user ID',
  `bill_no` varchar(50) NOT NULL COMMENT 'bill no',
  `money` decimal(10,2) NOT NULL COMMENT 'money',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='user bill'

索引信息 #

+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_bill_cp |          0 | PRIMARY     |            1 | id          | A         |     9713675 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_bill_cp |          1 | idx_user_id |            1 | user_id     | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

数据情况 #

表中插入了1000万条user_id=77820的重复数据：

mysql> select count(id) from user_bill_cp where user_id=77820;
+-----------+
| count(id) |
+-----------+
|  10001000 |
+-----------+
1 row in set (0.99 sec)

两种查询方法的耗时差异 #

方法1（嵌套IN子查询） #

执行耗时4.27秒：

select * 
from user_bill_cp 
where id in ( 
    select a.id 
    from (
        select id 
        from user_bill_cp 
        where user_id=77820 order by id asc limit 20 offset 5000000
        ) a
    );

方法2（分步执行） #

1. 先执行子查询获取ID列表，耗时0.69秒：

select a.id 
from (
    select id 
    from user_bill_cp 
    where user_id=77820 order by id asc limit 20 offset 5000000
    ) a;

2. 用得到的ID列表查询全量数据，耗时0.01秒：

select * 
from user_bill_cp 
where id in (5000001 ,5000002,5000003,5000004,5000005,5000006,5000007,5000008,5000009,5000010,5000011,5000012,5000013,5000014,5000015,5000016,5000017,5000018,5000019,5000020);

疑问：为何方法1耗时远大于方法2的耗时总和（4.27秒 > 0.69+0.01秒）？另外测试发现嵌套子查询中limit 1和limit 200的耗时几乎相同，不符合“嵌套查询耗时=分步执行总和”的预期。

1. MySQL对子查询的执行逻辑差异 #

MySQL优化器并未按你预期的“先执行内部子查询得到ID列表，再匹配主表”来处理嵌套IN子查询，而是可能将其转换为关联执行逻辑：

• 遍历主表的每一行数据，都要去子查询中验证id是否存在。而你的子查询带有limit offset，意味着每次验证都要重新执行一遍select id from user_bill_cp where user_id=77820 order by id asc limit 20 offset 5000000，相当于重复执行百万次高开销的子查询，直接导致总耗时暴涨。

而分步执行时，子查询仅执行一次，得到固定ID列表后，主查询通过主键id直接定位数据（主键查询为O(1)操作），总耗时为两次查询的直接相加。

2. 低基数索引加剧性能问题 #

从索引信息可见，idx_user_id的基数仅为1（所有数据的user_id相同），这意味着MySQL执行where user_id=77820时，会选择全表扫描而非使用该索引（索引过滤能力为0，全表扫描成本更低）：

• 嵌套查询时，每次重复执行子查询都要做一次全表扫描+排序+偏移，开销极高；
• 分步执行时，子查询仅做一次全表扫描+排序+偏移，后续主键查询无额外开销。

3. limit 1与limit 200耗时相近的原因 #

嵌套查询的核心开销在于重复执行子查询的次数（主表每一行都触发一次），而子查询的limit大小对单次执行耗时影响极小——因为都需要先扫描到偏移位置，再取N条数据，扫描成本是主导因素。所以无论limit是1还是200，总耗时几乎无变化。

改用JOIN替代IN子查询，让优化器明确先执行子查询得到ID集合，再关联主表：

select u.*
from user_bill_cp u
join (
    select id 
    from user_bill_cp 
    where user_id=77820 order by id asc limit 20 offset 5000000
) a on u.id = a.id;

这种写法会让MySQL先执行子查询得到20个ID，再通过主键关联查询主表数据，耗时与分步执行接近。

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