MySQL 5.6多百分位数查询优化方案求助

我有一个存储各类API调用执行时间的数据集，需要在Domo使用的MySQL 5.6环境中，生成按不同API调用分组、展示多百分位数执行时间的结果表。目前用GROUP_CONCAT实现的查询虽然能出结果，但性能很差，推测是因为重复生成有序的executionTimeMillis列表导致的。而且MySQL 5.6不支持CTE，现在需要优化这个查询，包括重新设计查询逻辑。

数据示例 #

预期返回结果 #

现有查询代码 #

SELECT
    action,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(executionTimeMillis ORDER BY 
    executionTimeMillis SEPARATOR ','), ',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) 
    AS 99percentile,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(executionTimeMillis ORDER BY 
    executionTimeMillis SEPARATOR ','), ',', 95/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) 
    AS 95percentile,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(executionTimeMillis ORDER BY 
    executionTimeMillis SEPARATOR ','), ',', 90/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) 
    AS 90percentile,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(executionTimeMillis ORDER BY 
    executionTimeMillis SEPARATOR ','), ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) 
    AS median,
    AVG(executionTimeMillis) AS mean,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(executionTimeMillis ORDER BY 
    executionTimeMillis SEPARATOR ','), ',', 25/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) 
    AS 1quartile
FROM MY_TABLE GROUP BY action

优化方案 #

方案1：预生成有序行号，避免重复排序 #

通过子查询给每个分组的执行时间按顺序编号，同时计算分组总条数，之后通过CASE语句匹配对应百分位的行号取值。这种方式只排序一次，彻底避免原查询中多次生成有序列表的重复计算。

SELECT
    t.action,
    MAX(CASE WHEN rn = CEIL(0.25 * total) THEN executionTimeMillis END) AS 1quartile,
    MAX(CASE WHEN rn = CEIL(0.5 * total) THEN executionTimeMillis END) AS median,
    MAX(CASE WHEN rn = CEIL(0.9 * total) THEN executionTimeMillis END) AS 90th_Perc,
    MAX(CASE WHEN rn = CEIL(0.95 * total) THEN executionTimeMillis END) AS 95th_Perc,
    MAX(CASE WHEN rn = CEIL(0.99 * total) THEN executionTimeMillis END) AS 99percentile,
    AVG(executionTimeMillis) AS mean
FROM (
    SELECT
        action,
        executionTimeMillis,
        @row_num := IF(@current_action = action, @row_num + 1, 1) AS rn,
        @current_action := action,
        (SELECT COUNT(*) FROM MY_TABLE t2 WHERE t2.action = t1.action) AS total
    FROM MY_TABLE t1
    CROSS JOIN (SELECT @row_num := 0, @current_action := '') vars
    ORDER BY action, executionTimeMillis
) t
GROUP BY t.action;

方案2：提前统计分组总数，减少子查询开销 #

把分组总数的统计提前到独立子查询中，避免每个行都触发一次子查询计数，进一步提升性能：

SELECT
    action,
    MAX(CASE WHEN rn = CEIL(0.25 * total) THEN executionTimeMillis END) AS 1quartile,
    MAX(CASE WHEN rn = CEIL(0.5 * total) THEN executionTimeMillis END) AS median,
    MAX(CASE WHEN rn = CEIL(0.9 * total) THEN executionTimeMillis END) AS 90th_Perc,
    MAX(CASE WHEN rn = CEIL(0.95 * total) THEN executionTimeMillis END) AS 95th_Perc,
    MAX(CASE WHEN rn = CEIL(0.99 * total) THEN executionTimeMillis END) AS 99percentile,
    AVG(executionTimeMillis) AS mean
FROM (
    SELECT
        t1.action,
        t1.executionTimeMillis,
        @row_num := IF(@current_action = t1.action, @row_num + 1, 1) AS rn,
        @current_action := t1.action,
        t2.total
    FROM MY_TABLE t1
    INNER JOIN (
        SELECT action, COUNT(*) AS total
        FROM MY_TABLE
        GROUP BY action
    ) t2 ON t1.action = t2.action
    CROSS JOIN (SELECT @row_num := 0, @current_action := '') vars
    ORDER BY t1.action, t1.executionTimeMillis
) t
GROUP BY t.action;

方案3：添加联合索引优化排序效率 #

为action和executionTimeMillis建立联合索引，让排序操作直接利用索引的有序性，避免磁盘排序，这在大数据集下能大幅提升性能：

CREATE INDEX idx_action_executiontime ON MY_TABLE(action, executionTimeMillis);

方案说明 #

• 方案1和方案2都只对每个分组的执行时间排序一次，解决了原查询重复生成有序列表的性能瓶颈，实际执行效率会有明显提升。
• 方案3的索引是基础优化，不管采用哪种查询逻辑，都能有效降低排序阶段的耗时。
• 百分位计算使用CEIL函数取上界，和原查询的逻辑保持一致，确保结果和预期完全匹配。

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