MySQL从子查询中选取第n行：30万行数据图表粒度优化

嘿，这个场景太有共鸣了——处理几十万行的数据用来做图表，全量拉取不仅慢得让人抓狂，还平白给数据库加了没必要的压力。咱们直接在MySQL层面搞定数据粒度化，比先拉全量再用脚本过滤高效太多！

这是最直观也最易维护的方式，利用MySQL 8.0引入的窗口函数，能轻松按你需要的粒度分组，然后选取每组内的指定行。

比如你要按天粒度，每天只取第一行数据：

SELECT timestamp, value
FROM (
    SELECT 
        timestamp,
        value,
        -- 按日期分组，每组内按时间排序并给行编号
        ROW_NUMBER() OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp) AS row_num
    FROM your_large_table
) AS subquery
-- 筛选每组的第一行
WHERE row_num = 1;

如果需要更密集的粒度（比如每100行取一行），可以去掉PARTITION BY，直接全局编号后按间隔筛选：

SELECT timestamp, value
FROM (
    SELECT 
        timestamp,
        value,
        ROW_NUMBER() OVER (ORDER BY timestamp) AS global_row_num
    FROM your_large_table
) AS subquery
-- 每100行取第一行
WHERE global_row_num % 100 = 1;

如果你还在使用MySQL 5.x（没有窗口函数），可以用用户变量来模拟行编号的逻辑：

按天粒度取每天第一行的示例：

SELECT timestamp, value
FROM (
    SELECT 
        timestamp,
        value,
        -- 当日期和上一行相同时，行号+1；否则重置为1
        @row_num := IF(@current_date = DATE(timestamp), @row_num + 1, 1) AS row_num,
        -- 更新当前日期变量
        @current_date := DATE(timestamp)
    FROM your_large_table,
         -- 初始化变量
         (SELECT @row_num := 0, @current_date := '') AS vars
    -- 必须按时间排序，保证分组逻辑正确
    ORDER BY timestamp
) AS subquery
WHERE row_num = 1;

你可以根据需求随意修改PARTITION BY的字段，实现不同的粒度：

• 按小时粒度：PARTITION BY DATE(timestamp), HOUR(timestamp)
• 按周粒度：PARTITION BY WEEK(timestamp)
• 按月份粒度：PARTITION BY MONTH(timestamp)

如果不想取每组的第一行，而是中间某一行（比如每组第5行），直接把WHERE row_num = 1改成WHERE row_num = 5就行。

无论用哪种方法，一定要给排序/分组的字段（比如timestamp）加索引，不然30万行数据的排序操作会让查询慢到离谱：

CREATE INDEX idx_timestamp ON your_large_table(timestamp);

这样数据库可以直接利用索引完成排序和分组，不用全表扫描，性能会提升一大截。

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