MySQL查询随记录量增长变慢，如何优化提取间隔3分钟跟踪记录的SQL？

• 全表扫描隐患：中间层关联的x表没有添加schedule_id、device_id的过滤条件，会对全表所有记录全量做时间差计算、分组，表越大扫描耗时自然线性增长。
• 分组粒度不匹配：原有逻辑按60秒（1分钟）做时间分桶，和业务要求的3分钟间隔抽点的需求不匹配。
• 无效索引占用：现有定义的schedule_id索引为(id, created_at)，最左前缀是主键id，完全无法支撑schedule_id=3这类过滤条件，属于冗余索引，反而拖慢写入性能。
• 计算冗余：多轮关联没有把过滤条件下推到最内层，导致中间结果集被不必要放大数倍。

方案1：MySQL 8.0+ 版本用窗口函数实现（性能最优） #

直接利用窗口函数做同组过滤，所有计算仅在匹配的子集内完成：

WITH matched_tracks AS (
    SELECT 
        *,
        -- 按和最早记录的时间差，每180秒（3分钟）划一个时间桶
        FLOOR(TIME_TO_SEC(TIMEDIFF(created_at, MIN(created_at) OVER ()))/180) AS time_bucket
    FROM schedules_trackings
    WHERE schedule_id = 3 AND device_id = 10017
),
bucket_points AS (
    -- 每个3分钟桶里取最早的一条跟踪记录
    SELECT MIN(id) AS pick_id
    FROM matched_tracks
    GROUP BY time_bucket
)
SELECT 
    a.*,
    DATE_FORMAT(a.created_at, "%d/%m/%Y %H:%i") AS dataora
FROM schedules_trackings a
INNER JOIN bucket_points b ON a.id = b.pick_id
ORDER BY a.created_at;

方案2：MySQL 5.7 无窗口函数版本 #

把过滤条件完全下推，避免全表扫描：

SELECT a.*, DATE_FORMAT(a.created_at,"%d/%m/%Y %H:%i") as dataora
FROM schedules_trackings a
INNER JOIN (
    SELECT MIN(x.id) AS pick_id
    FROM (
        -- 先查目标范围的最早创建时间，直接走索引匹配
        SELECT MIN(created_at) AS min_track_time
        FROM schedules_trackings
        WHERE schedule_id = 3 AND device_id = 10017
    ) base
    INNER JOIN schedules_trackings x
        -- 核心：给关联的x表加上和外层完全一致的过滤条件，避免全表扫描
        ON x.schedule_id = 3 
        AND x.device_id = 10017
    GROUP BY FLOOR(TIME_TO_SEC(TIMEDIFF(x.created_at, base.min_track_time))/180)
) b ON a.id = b.pick_id
WHERE a.schedule_id = 3 AND a.device_id = 10017;

• 可以直接删除冗余的schedule_id索引：该索引定义为(id, created_at)，最左前缀为主键id，对schedule_id的过滤场景无任何帮助，反而占用存储空间、拖慢写入速度。
• 现有device_id索引(device_id, schedule_id, created_at)已经可以支撑上述改写的所有查询场景，因为固定的device_id、schedule_id过滤可以快速定位到目标子集，后面的created_at字段刚好支撑时间差计算、分组操作。
• 如果要进一步榨干性能，可以把该索引调整为(device_id, schedule_id, created_at, id)，让内层的分组、取min(id)的操作完全走覆盖索引，不需要提前回表读数据，只需要最后拿到选中的id后回表拿全字段即可。
• 注意建表时schedule_id、device_id都是数值类型，查询条件的匹配值不需要加引号，避免隐式类型转换导致索引失效。

改写后所有的计算都只针对当前查询的目标数据子集（即对应行程、对应设备的跟踪记录，通常也就几百到几千条），哪怕全表总记录量涨到几百万，查询耗时也不会出现明显的线性增长，因为不再需要扫描全表数据。

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