嵌套查询在千万级表上崩溃的解决方法是改写为JOIN+临时表、分段聚合或窗口函数。需建联合索引、避免全量扫描、控制内存驻留规模，根本在于分步执行与提前截断计算。
嵌套查询在千万级表上直接崩溃怎么办
嵌套查询（比如 SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 GROUP BY id HAVING COUNT(*) > 5)）在小数据量下很直观，但面对单表超千万行时，MySQL/PostgreSQL 常出现临时表爆内存、执行计划选错索引、甚至被 OOM Killer 干掉。根本原因不是语法错，而是优化器对子查询的物化策略不可控，且外层无法利用内层聚合结果的有序性。
实操建议：
- 把嵌套改写为
JOIN+ 中间临时表，强制分步控制生命周期 - 对内层聚合结果加
ORDER BY和LIMIT（如果业务允许近似结果），避免全量扫描 - 确认内层子查询字段是否已建联合索引——例如
GROUP BY user_id, date就需要(user_id, date)索引，否则Using temporary; Using filesort必现
用 WITH RECURSIVE 分段聚合替代全量嵌套
WITH RECURSIVE 不是为递归而递归，而是用来把“一次扫全表”拆成“按主键区间分片扫”。适用于需要按时间/ID 范围做滚动汇总，且原始表有单调主键（如自增 id 或 created_at）。
示例：对日志表按每 10 万条分段统计 UV
WITH RECURSIVE chunk AS (
SELECT 1 AS start_id, 100000 AS end_id
UNION ALL
SELECT start_id + 100000, end_id + 100000
FROM chunk
WHERE end_id < (SELECT MAX(id) FROM logs)
),
agg AS (
SELECT
c.start_id,
COUNT(DISTINCT user_id) AS uv
FROM chunk c
JOIN logs l ON l.id BETWEEN c.start_id AND c.end_id
GROUP BY c.start_id
)
SELECT SUM(uv) AS total_uv FROM agg;注意点：
- 递归深度受
cte_max_recursion_depth限制（MySQL 默认 1000），需预估分片数并调大 -
BETWEEN范围必须落在主键索引上，否则退化为全表扫描 - PostgreSQL 需用
GENERATE_SERIES()替代递归 CTE，语义更清晰
物化中间结果到临时表并加索引
当分段逻辑复杂（比如多条件过滤+多维分组），硬塞进 CTE 或 JOIN 容易让优化器放弃使用索引。此时应主动物化中间聚合结果，本质是把“查询优化权”从数据库手里拿回来。
操作步骤：
- 先建临时表：
CREATE TEMPORARY TABLE tmp_agg AS SELECT user_id, DATE(created_at) d, COUNT(*) cnt FROM logs WHERE created_at >= '2024-01-01' GROUP BY user_id, d; - 立刻加索引：
CREATE INDEX idx_tmp_user_d ON tmp_agg(user_id, d);（即使临时表，索引对后续 JOIN 效果显著） - 再用这个临时表参与外层关联或过滤，避免反复计算
关键细节：
- MySQL 临时表默认引擎是
MEMORY，但COUNT(DISTINCT)或大字符串会触发磁盘临时表，务必监控Created_tmp_disk_tables状态值 - PostgreSQL 的
TEMP TABLE默认走磁盘，但可指定ON COMMIT DROP控制生命周期
窗口函数能替代嵌套时，优先用它
很多所谓“嵌套需求”，其实只是想在聚合后进一步筛选（比如“找出每个部门工资前 3 的人”）。这类场景用 ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) 比 IN (SELECT ...) 高效得多，因为不产生中间结果集。
但要注意边界：
- 窗口函数不能直接用于
WHERE子句（会报错Window function is not allowed in WHERE），必须套一层子查询或 CTE - 若需跨窗口做二次聚合（比如“每个窗口内 top3 的平均薪资”），仍要物化，窗口函数本身不减少数据量
- MySQL 8.0+、PostgreSQL 11+ 支持良好，但旧版本需用变量模拟，稳定性差
真正棘手的从来不是语法怎么写，而是搞清哪部分计算可以提前截断、哪部分必须保留全量——分段的本质是控制内存驻留数据规模，而不是单纯切开 SQL。
