创建统计特定aisle_id计数的新表时查询过慢，求解决方案

看起来你现在卡在了统计订单对应唯一aisle数量的慢查询上，我来帮你拆解问题并给出实用优化方案。

首先明确你的需求：从aisle_list表中，先统计每个order_id对应的唯一aisle_id数量，再统计这个数量为4、5、6的订单分别有多少个，且要求用嵌套GROUP BY的方式，但当前查询耗时过长。

先分析慢查询的核心原因 #

你的原始查询大概率类似这样（如果有偏差可以随时调整）：

SELECT aisle_count, COUNT(order_id) AS order_count
FROM (
    SELECT order_id, COUNT(DISTINCT aisle_id) AS aisle_count
    FROM aisle_list
    GROUP BY order_id
) AS sub_query
WHERE aisle_count IN (4,5,6)
GROUP BY aisle_count;

这种查询慢通常有两个关键诱因：

1. 缺少合适的索引：如果表数据量较大，内层查询需要全表扫描，再对每个order_id做分组和去重计数，会消耗大量CPU和IO资源。
2. COUNT(DISTINCT)的高开销：去重计数需要数据库维护临时集合存储每个订单的唯一aisle_id，数据量较大时这个过程会变慢，甚至会触发磁盘临时表的使用。

优化方案分步走 #

1. 优先添加索引（最立竿见影的优化）

给aisle_list表创建联合覆盖索引：

CREATE INDEX idx_order_aisle ON aisle_list(order_id, aisle_id);

这个索引的作用：

• 让数据库直接通过索引快速分组order_id，无需扫描全表。
• 索引包含了所需的两个字段，不需要回表查询原数据（覆盖索引特性）。
• 索引按order_id有序排列，分组和去重操作能利用有序性大幅减少内存开销。

2. 优化查询语句，替换COUNT(DISTINCT)

部分数据库对COUNT(DISTINCT)的优化不如多层分组高效，你可以尝试先去重再计数的写法：

CREATE TABLE your_new_table AS
SELECT aisle_count, COUNT(order_id) AS order_count
FROM (
    SELECT order_id, COUNT(*) AS aisle_count
    FROM (
        -- 先去重每个订单对应的aisle_id对
        SELECT DISTINCT order_id, aisle_id
        FROM aisle_list
    ) AS distinct_pairs
    GROUP BY order_id
) AS order_aisle_counts
WHERE aisle_count IN (4,5,6)
GROUP BY aisle_count;

这种写法让数据库先处理去重的订单-aisle对，再对每个订单计数，当同一个订单存在大量重复aisle_id时，效率会比直接用COUNT(DISTINCT)更高。

3. 数据库配置小调整（针对MySQL类数据库）

如果你的数据库是MySQL，可以临时调大临时表的内存限制，避免临时表写入磁盘：

SET tmp_table_size = 64M;
SET max_heap_table_size = 64M;

这样数据库会优先在内存中处理分组和去重操作，速度会有明显提升（只要数据量未超过内存限制）。

4. 非实时场景：预先计算结果

如果这个统计不需要实时更新，可以用定时任务（比如每天凌晨）预先计算好结果存入新表，而非每次查询都重新扫描全表，这是超大数据表的终极优化方案。

验证优化效果 #

加完索引后，先单独运行内层查询测试速度：

SELECT order_id, COUNT(DISTINCT aisle_id) AS aisle_count FROM aisle_list GROUP BY order_id;

如果这个查询速度明显提升，那么整个嵌套查询的性能也会跟着上来。

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