mysql - 提高此 SQL 的查询性能

Question

我想提高此查询的性能，现在需要 13 秒才能返回结果。

主表有超过 2,000,000 条记录。结果仅包含 34 条记录。这些表在 r.date_gps 上有索引。

SELECT
    r.id,
    c.name,
    CONCAT(v.title, '', v.plaque) AS title,v.type_vehicle,
    r.sn,
    r.lat,
    r.lng,
    r.direction,
    r.date_db,
    r.date_gps,
    r.volt_main,
    r.speed,
    r.ing,
    t.sat_fix,
    r.sat
FROM registers r
JOIN trackers t ON t.cod = r.sn
JOIN installations i ON t.id = i.trackers_id
JOIN vehicles v ON v.id = i.vehicles_id
JOIN clients c ON c.id = v.clients_id
WHERE r.date_gps = (
    SELECT MAX(rr.date_gps) FROM registers rr WHERE r.sn = rr.sn
)
AND c.management_id = p_management
GROUP BY r.sn
ORDER BY r.date_gps DESC;

Answer 1

很遗憾 Mysql 不支持 Row_Number 窗口函数

尝试将子查询重写为INNER JOIN

SELECT r.id, 
       c.NAME, 
       Concat(v.title, '', v.plaque) AS title, 
       v.type_vehicle, 
       r, 
       sn, 
       .....
FROM   registers r 
       JOIN trackers t 
         ON t.cod = r.sn 
       JOIN installations i 
         ON t.id = i.trackers_id 
       JOIN vehicles v 
         ON v.id = i.vehicles_id 
       JOIN clients c 
         ON c.id = v.clients_id 
            AND c.management_id = p_management 
       INNER JOIN (SELECT Max(rr.date_gps) AS date_gps, 
                          sn 
                   FROM   registers rr
                   GROUP BY sn) a 
               ON r.sn = a.sn 
                  AND r.date_gps = a.date_gps 
GROUP  BY r.sn 
ORDER  BY r.date_gps DESC;