MySQL查询本地正常服务器端极慢，求排查优化方案

核心问题 #

你的查询在服务器上速度极慢的原因主要有两点：

1. 多轮全表扫描：原查询包含12个相关子查询，每个子查询都对customer_migration_details表执行全表扫描（执行计划中ALL类型），加上主查询的扫描，总共要扫表13次，数据量叠加后效率暴跌。
2. 别名引用错误：子查询中Acquisition_Source = Source的写法不符合SQL标准——SELECT子句的别名无法在WHERE子句中直接引用，数据库无法正确关联主查询的Acquisition_Source，导致子查询无法利用索引，只能强制全表扫描。

优化后的查询（推荐） #

使用条件聚合替代子查询，只需扫描一次表即可完成统计，效率提升显著：

SELECT
    `Acquisition_Source` AS 'Source',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-12-01' AND '2022-12-31' THEN ID END) AS '2022-12',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-11-01' AND '2022-11-30' THEN ID END) AS '2022-11',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-10-01' AND '2022-10-31' THEN ID END) AS '2022-10',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-09-01' AND '2022-09-30' THEN ID END) AS '2022-09',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-08-01' AND '2022-08-31' THEN ID END) AS '2022-08',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-07-01' AND '2022-07-31' THEN ID END) AS '2022-07',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-06-01' AND '2022-06-30' THEN ID END) AS '2022-06',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-05-01' AND '2022-05-31' THEN ID END) AS '2022-05',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-04-01' AND '2022-04-30' THEN ID END) AS '2022-04',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-03-01' AND '2022-03-31' THEN ID END) AS '2022-03',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-02-01' AND '2022-02-28' THEN ID END) AS '2022-02',
    COUNT(CASE WHEN Date_Opened BETWEEN '2022-01-01' AND '2022-01-31' THEN ID END) AS '2022-01'
FROM `customer_migration_details`
WHERE `Acquisition_Source` != ''
GROUP BY `Acquisition_Source`;

• 原理：通过CASE语句按月份筛选数据，再用COUNT统计符合条件的记录，最后按Acquisition_Source分组，天然实现去重（无需DISTINCT）。

进一步提速：添加复合索引 #

为了让查询完全利用索引，避免回表操作，创建以下复合索引：

CREATE INDEX idx_acq_date ON customer_migration_details(Acquisition_Source, Date_Opened, ID);

这个索引覆盖了查询的过滤条件、分组字段和计数字段，数据库可以直接从索引中获取所需数据，无需访问主表。

原查询的别名修正（不推荐，仅作参考） #

如果一定要保留子查询写法，需要修正别名引用（给主表加别名，直接引用主表字段）：

SELECT DISTINCT
    cmd.`Acquisition_Source` AS 'Source',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-12-01' AND '2022-12-31' AND Acquisition_Source = cmd.`Acquisition_Source`) AS '2022-12',
    -- 其余子查询同理替换为 cmd.`Acquisition_Source`
FROM `customer_migration_details` cmd
WHERE cmd.`Acquisition_Source` != '';

但该方案仍需多次扫表，效率远低于条件聚合。

内容的提问来源于stack exchange，提问作者Drew Burston