优化含多SELECT列的MariaDB查询——性能提升方案咨询

你的问题核心在于多次重复扫描全表——原查询对每个选手都执行7次独立的子查询，相当于把25000条数据扫了7遍，这肯定慢。在MariaDB 10.2里，用条件聚合+行转列的方式可以把扫描次数降到最低，是这类统计查询的最优解。

优化后的查询语句 #

直接用UNION ALL把每个比赛的两个选手拆成单独行，再通过CASE语句做条件聚合，只需要扫描原表两次：

SELECT 
    player,
    COUNT(*) AS since_start_matches,
    SUM(CASE WHEN ht_total_goals = 0 THEN 1 ELSE 0 END) AS since_start_ht_0,
    SUM(CASE WHEN ht_total_goals = 1 THEN 1 ELSE 0 END) AS since_start_ht_1,
    SUM(CASE WHEN ht_total_goals = 2 THEN 1 ELSE 0 END) AS since_start_ht_2,
    SUM(CASE WHEN ht_total_goals = 3 THEN 1 ELSE 0 END) AS since_start_ht_3,
    SUM(CASE WHEN ht_total_goals = 4 THEN 1 ELSE 0 END) AS since_start_ht_4,
    SUM(CASE WHEN ht_total_goals >= 5 THEN 1 ELSE 0 END) AS since_start_ht_5_plus
FROM (
    -- 拆分每个比赛的两个选手为独立行
    SELECT player_1 AS player, ht_total_goals FROM results
    UNION ALL
    SELECT player_2 AS player, ht_total_goals FROM results
) AS player_matches
GROUP BY player
ORDER BY player;

为什么这个方案更快？ #

• 原查询：对每个player_1的选手，执行7次全表扫描去匹配player_1或player_2的记录，总扫描量是7 * 25000行。
• 优化后：只扫描原表两次（UNION ALL的两个分支），然后一次分组聚合完成所有统计，总扫描量仅2 * 25000行，计算逻辑也更高效。

另外，原查询还存在一个逻辑漏洞：会漏掉只出现在player_2列的选手，而优化后的查询会包含所有参与过比赛的选手，结果更完整。

配套索引优化 #

为了让UNION ALL的两个分支直接用索引覆盖查询（不需要回表读取原数据），可以创建两个针对性的联合索引：

-- 覆盖player_1和ht_total_goals的查询
CREATE INDEX idx_results_p1_ht ON results (player_1, ht_total_goals);
-- 覆盖player_2和ht_total_goals的查询
CREATE INDEX idx_results_p2_ht ON results (player_2, ht_total_goals);

或者创建一个包含所有需要字段的联合索引，也能达到同样效果：

CREATE INDEX idx_results_players_ht ON results (player_1, player_2, ht_total_goals);

添加索引后，这个查询的速度应该能从7秒降到几百毫秒，完全满足性能需求。

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