MySQL in 慢查询案例

现有如下表结构：

CREATE TABLE `student` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(63) NOT NULL,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

表中有1000w条数据。

执行以下语句：

explain select * from student where id in (10w条id);
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 9953605 |    50.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 2 warnings (0.08 sec)

show warnings;
Warning 3170 Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.

可以看到执行计划虽然possible_keys是主键，但是实际走的全表扫描，并且还有2个warnings。 warning显示内存超过8388608 bytes, 此次查询没有使用Range optimization。

可参考官网：Limiting Memory Use for Range Optimization

range_optimizer_max_mem_size 表示范围查询优化的最大内存，默认8388608 bytes，如果优化器预估内存过大，则可能退化到全表扫描。

此时需要限制in中参数个数来走索引。