问题核心分析 #

先从你贴出的EXPLAIN结果入手，这是性能瓶颈的关键线索：

1. 主表transactions（别名T）走了全表扫描（type: ALL），单次扫描20多万行，数据量上升后，磁盘IO开销会指数级增长；
2. 同时触发了Using filesort——MySQL需要先把所有符合条件的数据全量排序，再返回结果，数据量超过15万时，排序的内存/磁盘开销直接把耗时拉到9秒。

另外你用到的子查询、索引设计也有可优化的空间，下面给你一步步的具体优化方案：

具体优化步骤 #

1. 给transactions创建核心复合索引（最高优先级）

你的查询对transactions的过滤条件是payment_date >= '2025-07-19'，排序条件是creation_time DESC，还要关联user_id、ServiceCode字段。直接创建匹配查询逻辑的覆盖+排序复合索引：

CREATE INDEX idx_trans_payment_creation ON transactions (payment_date, creation_time DESC, user_id, ServiceCode);

这个索引的作用：

• 用payment_date快速过滤出目标时间范围的数据，彻底消除全表扫描；
• 索引自带creation_time DESC的有序结构，直接满足排序需求，去掉Using filesort；
• 包含关联所需的user_id和ServiceCode，查询时直接从索引取数，不用回表查原表字段，大幅减少IO。

2. 优化users表的子查询索引

你的第一个子查询SELECT user_id, user_name FROM users WHERE user_type = 5，可以创建覆盖索引让它直接走索引取数：

CREATE INDEX idx_users_type_id_name ON users (user_type, user_id, user_name);

这样MySQL不用回表查users的其他字段，直接从索引里拿到需要的user_id和user_name，速度更快。

3. 优化services和service_departments的关联索引

针对第二个子查询的关联逻辑，给两张表分别创建覆盖索引：

-- 给service_departments创建：匹配type过滤+关联id+取title
CREATE INDEX idx_sd_type_id_title ON service_departments (type, id, title);
-- 给services创建：匹配关联service_department_id+取service_code和title
CREATE INDEX idx_s_dept_id_code_name ON services (service_department_id, service_code, title);

这两个索引能让关联查询全程走索引，避免回表，减少子查询的执行开销。

4. 简化查询结构，去掉冗余子查询

你当前用子查询作为关联表的写法，可能会让MySQL生成临时表增加开销，改成直接关联的形式，让优化器有更多执行计划可选：

SELECT 
    U.user_name, 
    SD.title AS department, 
    S.service_code, 
    S.title AS service_name, 
    T.UniquePaymentRequestId, 
    T.TransactionNumber, 
    T.CustomerNumber, 
    T.OrderId, 
    T.Amount, 
    T.UserCharge, 
    T.DepartmentCharge, 
    T.creation_time 
FROM transactions AS T 
INNER JOIN users U 
    ON U.user_id = T.user_id 
    AND U.user_type = 5  -- 把user_type条件移到JOIN中，逻辑更清晰
LEFT JOIN services AS S 
    ON S.service_code = T.ServiceCode 
LEFT JOIN service_departments AS SD 
    ON SD.id = S.service_department_id 
    AND SD.type = "non" 
WHERE T.payment_date >= '2025-07-19' 
ORDER BY T.creation_time DESC

5. 清理无效索引，降低维护成本

你的transactions表有大量单字段索引，还有一个包含8个字段的长复合索引，这些索引不仅没被当前查询用到，还会增加数据插入/更新的维护成本。比如那个长复合索引(UniquePaymentRequestId, TransactionNumber, ...)，因为查询过滤条件是payment_date，和索引前缀字段不匹配，完全不会被用到，可以删除：

DROP INDEX transactions ON transactions;

其他单字段索引如果没有单独查询的需求，也可以逐步清理，只保留常用索引和我们上面创建的复合索引。

6. 检查关联字段的数据类型一致性

务必确保transactions.ServiceCode和services.service_code的数据类型完全一致（都是INT UNSIGNED），如果类型不匹配，MySQL会做隐式类型转换，直接导致索引失效，这是很多人容易忽略的细节。

7. 更新表统计信息

执行以下命令更新表的统计信息，让MySQL优化器基于最新的数据分布生成更准确的执行计划：

ANALYZE TABLE transactions, users, services, service_departments;

优化效果验证 #

完成以上操作后，重新运行EXPLAIN，你应该能看到：

• transactions表的type变成range或ref，不再是ALL；
• Extra字段里不再有Using filesort，甚至会出现Using index（表示走覆盖索引）；
• 各关联表的type为ref或eq_ref，rows数值大幅降低。

这样即使数据量继续增大，查询性能也能保持稳定，分页的LIMIT 50也会因为索引有序而直接取数，不会再做全量排序。

内容来源于stack exchange