MySQL单字段查询优化求助：IN子句查询耗时超20秒

先理清楚当前的问题：你的查询语句是

SELECT `table1`.* FROM `table1` WHERE `table1`.`table2_id` IN (1,2,6,12,53,666)

执行耗时超20秒，从Explain结果来看，虽然已经用到了table2_id索引，type为range，预估扫描74778行，但Extra里的Using index condition暴露了核心问题——需要回表取数。因为你查的是全字段（*），数据库得先通过二级索引找到对应的主键ID，再去聚簇索引（主键索引）里读取完整行数据，加上你的表已经有八千多万行（AUTO_INCREMENT到86623178），磁盘IO的开销会非常大。

下面是几个针对性的优化方案：

1. 创建覆盖索引，消除回表操作 #

这是最直接有效的优化方式。覆盖索引包含查询所需的所有字段，让数据库不需要回表就能拿到全部数据。

如果你的MySQL版本是8.0+（或MariaDB 10.2+），可以用INCLUDE子句（不会把包含字段作为索引前缀，更节省空间）：

CREATE INDEX idx_table2_id_covering ON table1 (table2_id) 
INCLUDE (id, table3_id, field1, field2, created_at, updated_at, field3);

如果是较低版本的MySQL，直接创建联合索引：

CREATE INDEX idx_table2_id_all ON table1 (table2_id, id, table3_id, field1, field2, created_at, updated_at, field3);

创建后再执行原查询，Explain的Extra应该会显示Using index，表示直接用覆盖索引完成查询，能大幅降低IO开销。

2. 检查数据分布，考虑分页查询 #

先确认这些table2_id对应的实际数据量：

SELECT table2_id, COUNT(*) AS row_count 
FROM table1 
WHERE table2_id IN (1,2,6,12,53,666) 
GROUP BY table2_id;

如果其中某个/多个table2_id对应的数据量特别大（比如单ID就有几万行），而业务场景不需要一次性获取所有数据，可以拆分成分页查询，减少单次查询的压力：

SELECT `table1`.* FROM `table1` WHERE `table1`.`table2_id` IN (1,2,6,12,53,666) LIMIT 0, 1000;
SELECT `table1`.* FROM `table1` WHERE `table1`.`table2_id` IN (1,2,6,12,53,666) LIMIT 1000, 1000;

3. 维护索引与表统计信息 #

你的表数据量很大，索引可能存在碎片，或者统计信息过时，导致优化器选择的执行计划不是最优：

• 更新表统计信息（快速无锁）：ANALYZE TABLE table1;
• 整理索引碎片（需在业务低峰期执行，会锁表）：OPTIMIZE TABLE table1;
  这两个操作能让优化器更准确地预估扫描行数，提升索引使用效率。

4. 调整InnoDB缓冲池配置 #

如果服务器内存充足，增大innodb_buffer_pool_size可以让更多索引和数据缓存到内存，减少磁盘IO。先查看当前配置：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

通常建议设置为服务器可用内存的50%-70%（比如16G内存的服务器，设置为10G左右），修改my.cnf配置文件后重启MySQL生效：

innodb_buffer_pool_size = 10G

优先尝试创建覆盖索引，这应该能最快解决你的查询慢问题。

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