BI数据库查询长时间卡在creating sort index状态如何解决？

我遇到过类似的场景，这种卡在creating sort index的情况，本质是MySQL需要创建磁盘临时表来处理分组/排序操作，而磁盘IO的速度远远跟不上内存，所以才会耗近10分钟。结合你的视图定义，咱们一步步来解决：

先搞懂问题根源 #

你的POWERBI_FUNIL视图是先通过POWERBI_FUNIL_MAXDATE取出每个id_tenant+id_portfolio的最大id_date，再关联原表fact_contacts做分组求和。这种两次分组+关联的逻辑很容易生成超大的临时表，一旦临时表超过内存阈值，就会被写到磁盘，然后MySQL还要为这个磁盘临时表创建排序索引——这就是耗时的核心原因。

具体优化方案 #

1. 重构SQL逻辑，避免冗余分组 #

用窗口函数替代两次分组的逻辑，直接在子查询里筛选出每个租户+组合的最新日期数据，减少一次关联和分组操作，大幅降低临时表的大小：

SELECT 
    FC.id_date, 
    FC.id_tenant, 
    FC.id_portfolio, 
    SUM(FC.documents_qtt) AS documents_qtt, 
    SUM(FC.has_any_contact_qtt) AS has_any_contact_qtt, 
    SUM(FC.has_email_qtt) AS has_email_qtt, 
    SUM(FC.has_phone_qtt) AS has_phone_qtt
FROM (
    SELECT 
        *,
        -- 按租户、组合分组，按日期倒序排，标记最新的一条
        ROW_NUMBER() OVER (PARTITION BY id_tenant, id_portfolio ORDER BY id_date DESC) AS rn
    FROM BI_summarized.fact_contacts
    WHERE id_date >= 2426
) FC
WHERE FC.rn = 1 -- 只保留每个分组的最新数据
GROUP BY FC.id_date, FC.id_tenant, FC.id_portfolio;

2. 给核心表添加覆盖索引 #

你的查询依赖id_tenant、id_portfolio、id_date做筛选、分组和关联，还需要聚合几个字段。创建一个覆盖索引，让MySQL不用回表就能拿到所有数据，同时利用索引的顺序避免排序：

CREATE INDEX idx_fact_contacts_tenant_portfolio_date 
ON BI_summarized.fact_contacts (id_tenant, id_portfolio, id_date) 
INCLUDE (documents_qtt, has_any_contact_qtt, has_email_qtt, has_phone_qtt);

（如果你的MySQL版本不支持INCLUDE，可以把这些字段加到索引的末尾）

3. 调整MySQL配置，优化临时表性能 #

如果数据量实在太大，没法完全避免临时表，就调整以下配置（根据服务器内存情况调整数值）：

• 增大tmp_table_size和max_heap_table_size：比如设置为1G，让内存临时表能容纳更多数据，避免写到磁盘。
• 开启derived_merge：执行SET optimizer_switch='derived_merge=on';（可以写到my.cnf里永久生效），让MySQL合并派生表和视图，减少临时表的创建。
• 适当增大sort_buffer_size：比如设置为64M，提升排序效率，但不要设置过大，避免内存耗尽。

4. 用执行计划排查瓶颈 #

运行EXPLAIN EXTENDED SELECT * FROM POWERBI_FUNIL;查看执行计划：

• 如果Extra列出现Using temporary; Using filesort，说明确实在使用磁盘临时表和文件排序，这就是慢的关键。
• 检查type列是否是ref或range，如果是ALL说明没用到索引，得重新调整索引。

内容的提问来源于stack exchange，提问作者Vinicius Zolin De Jesus