如何在MySQL 5.7中计算百分位值 解决PHP计算内存超时问题

本方案完全兼容MySQL 5.7版本，不需要依赖8.0新增的窗口函数特性，计算逻辑和你提供的PHP百分位实现完全对齐。
假设你的业务表名为score_records，需要统计指定时间范围的分数前10%阈值（对应百分位参数为90），查询语句如下：

-- 自定义参数：计算的百分位、统计时间范围
SET @target_percentile = 90;
SET @start_time = '2021-08-01 00:00:00';
SET @end_time = '2021-08-31 23:59:59';

-- 计算符合条件的总条数、目标索引
SET @total_count = (SELECT COUNT(*) FROM score_records WHERE requestTime BETWEEN @start_time AND @end_time);
SET @calc_index = (@target_percentile / 100) * @total_count;
SET @row_num = 0;

-- 计算最终百分位阈值
SELECT 
    IF(
        FLOOR(@calc_index) = @calc_index,
        -- 索引为整数时取相邻两位的平均值，对齐PHP数组下标从0开始的逻辑
        (SUM(CASE WHEN rank IN (@calc_index, @calc_index + 1) THEN score END) / 2),
        -- 索引为非整数时取下整对应的数值
        MAX(CASE WHEN rank = FLOOR(@calc_index) + 1 THEN score END)
    ) AS percentile_threshold
FROM (
    -- 对符合条件的分数升序排序，生成连续行号
    SELECT 
        (@row_num := @row_num + 1) AS rank,
        score
    FROM score_records
    WHERE requestTime BETWEEN @start_time AND @end_time
    ORDER BY score ASC
) AS sorted_scores
-- 仅过滤需要用到的行，减少计算量
WHERE rank IN (FLOOR(@calc_index), FLOOR(@calc_index) + 1);

优化建议 #

为了进一步提升查询效率，避免大数据量下的性能问题，可以给业务表添加联合索引：

ALTER TABLE score_records ADD INDEX idx_reqtime_score (requestTime, score);

添加索引后查询无需回表，15万条数据的计算耗时可以控制在几十毫秒级别，不会出现超时问题。如果需要频繁计算不同时间范围的百分位，还可以定期预计算分位值存储到独立结果表，进一步降低查询开销。

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