超大数据量下含IN子句的SQL查询优化方案咨询

核心问题分析 #

当前查询SELECT * FROM storages_remains WHERE FREE>0 AND CATALOG_ID IN(...)依赖单列CATALOG_ID索引，执行时先匹配IN中的ID，再过滤FREE>0。虽然当前300ms的耗时不算特别差，但针对10亿行的大表，仍有纯SQL层面的优化空间。

纯SQL优化方案 #

1. 创建针对性联合索引 #

现有单列CATALOG_ID索引只能过滤出指定ID的所有仓库数据，之后还需要逐行检查FREE>0。创建(CATALOG_ID, FREE)联合索引可以直接在索引层完成两个条件的筛选：

• 索引先按CATALOG_ID分组，同一ID下按FREE排序
• 查询时直接定位到CATALOG_ID匹配且FREE>0的索引条目，无需额外过滤，大幅减少IO开销

创建索引语句：

CREATE INDEX idx_catalog_free ON storages_remains (CATALOG_ID, FREE);

2. 利用虚拟列优化索引效率 #

可以创建逻辑虚拟列avail来标识库存是否可用，再基于虚拟列创建联合索引：

• 虚拟列定义：avail = (FREE > 0)（MySQL支持布尔型虚拟列，实际存储为tinyint）
• 虚拟列的索引体积远小于原FREE列（整数），查询时匹配avail=1更高效

创建虚拟列和索引的语句：

ALTER TABLE storages_remains ADD COLUMN avail BOOLEAN GENERATED ALWAYS AS (FREE > 0) STORED;
CREATE INDEX idx_catalog_avail ON storages_remains (CATALOG_ID, avail);

修改后的查询语句：

SELECT * FROM storages_remains WHERE avail=1 AND CATALOG_ID IN(...);

3. 用临时表JOIN替代大IN子句 #

当IN子句包含5000个ID时，部分数据库对IN的长度有隐性限制，且优化器处理大IN列表的效率可能下降。改用临时表JOIN的方式更稳定：

-- 创建临时表并插入需要查询的CATALOG_ID
CREATE TEMPORARY TABLE temp_catalogs (
    catalog_id VARCHAR(64) PRIMARY KEY
);
INSERT INTO temp_catalogs VALUES 
('0d7bd57b-878a-11ed-96e5-001b21e8ace8'),
('fa2f272f-ca6d-11ea-809e-a0369f4567aa'),
...; -- 批量插入所有需要的ID

-- 用JOIN替代IN查询
SELECT sr.* 
FROM storages_remains sr
JOIN temp_catalogs tc ON sr.CATALOG_ID = tc.catalog_id
WHERE sr.FREE > 0;

临时表的主键会自动创建索引，JOIN时的匹配效率比大IN列表更高。

4. 清理无用索引 #

现有单列FREE索引对当前查询完全无用：

• 你的查询是先按CATALOG_ID过滤，再筛选FREE>0，单列FREE索引无法利用（优化器不会先扫所有FREE>0的行再匹配CATALOG_ID，因为数据量太大）
• 删除该索引可以节省磁盘空间，减少每日更新时的索引维护开销

删除语句：

DROP INDEX FREE ON storages_remains;

额外建议 #

如果查询不需要返回所有列（SELECT *），可以创建覆盖索引，把需要的列加入联合索引中，避免回表查询：

-- 假设只需要CATALOG_ID、STORAGE_ID、FREE三列
CREATE INDEX idx_catalog_free_cover ON storages_remains (CATALOG_ID, FREE, STORAGE_ID);

这样查询时直接从索引获取数据，无需访问主表，速度会进一步提升。

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