查询优化 -- 耗时过长并停止服务器 [英] Query optimization -- takes too long and stops the server

问题描述

我的查询会生成一些关于超速、上次和平均速度的报告.这是我的查询:

My query generates some reports about speeding, last time, and average speed. This is my query:

Select 
    r1 . *, r2.name, r2.notes, r2.serial
From
    (SELECT 
        k.idgps_unit,
            MIN(k.dt) AS DT_Start,
            MIN(CASE
                WHEN k.RowNumber = 1 THEN k.Lat
            END) AS Latitude_Start,
            MIN(CASE
                WHEN k.RowNumber = 1 THEN k.Long
            END) AS Longitude_Start,
            MIN(CASE
                WHEN k.RowNumber = 1 THEN k.Speed_kmh
            END) AS Speed_Start,
            MAX(k.dt) AS dt_end,
            MIN(CASE
                WHEN k.RowNumber = MaxRowNo THEN k.Lat
            END) AS Latitude_End,
            MIN(CASE
                WHEN k.RowNumber = MaxRowNo THEN k.Long
            END) AS Longitude_End,
            MIN(CASE
                WHEN k.RowNumber = MaxRowNo THEN k.Speed_kmh
            END) AS Speed_End,
            AVG(Speed_kmh) AS Average_Speed
    FROM
        (SELECT 
        gps_unit_location . *,
            @i:=CASE
                WHEN Speed_Kmh > 80 AND @b = 0 THEN @i + 1
                ELSE @i
            END AS IntervalID,
            @r:=CASE
                WHEN Speed_Kmh > 80 AND @b = 0 THEN 1
                ELSE @r + 1
            END AS RowNumber,
            @b:=CASE
                WHEN Speed_Kmh > 80 THEN 1
                ELSE 0
            END AS IntervalCheck
    FROM
        gps_unit_location, (SELECT @i:=0) i, (SELECT @r:=0) r, (SELECT @b:=0) b
    ORDER BY dt , idgps_unit_location) k
    INNER JOIN (SELECT 
        IntervalID, MAX(RowNumber) AS MaxRowNo
    FROM
        (SELECT 
        gps_unit_location . *,
            @i:=CASE
                WHEN Speed_Kmh > 80 AND @b = 0 THEN @i + 1
                ELSE @i
            END AS IntervalID,
            @r:=CASE
                WHEN Speed_Kmh > 80 AND @b = 0 THEN 1
                ELSE @r + 1
            END AS RowNumber,
            @b:=CASE
                WHEN Speed_Kmh > 80 THEN 1
                ELSE 0
            END AS IntervalCheck
    FROM
        gps_unit_location, (SELECT @i:=0) i, (SELECT @r:=0) r, (SELECT @b:=0) b
    ORDER BY dt , idgps_unit_location) d
    WHERE
        IntervalCheck = 1
    GROUP BY IntervalID) MaxInt ON MaxInt.IntervalID = k.IntervalID
    WHERE
        k.IntervalCheck = 1
            and k.idgps_unit in (SELECT 
                idgps_unit
            FROM
                instafleet.gps_unit
            where
                id_customer = (select 
                        idcustomer
                    from
                        user
                    where
                        iduser = 14))
    GROUP BY k.IntervalID , k.idgps_unit) r1
        Inner join
    gps_unit r2 ON r1.idgps_unit = r2.idgps_unit

目前 783,723 条记录需要 3 分钟.我认为适当的索引可能会有所帮助；虽然经过一些试验和错误，我无法弄清楚.如果您认为自己可以提供帮助，并且需要一些其他信息 - 我很乐意为您提供.

Currently it takes 3 minutes for 783,723 records. I am thinking that proper indexes might help; although after some trial and error, I can't figure it out. If you think you can help, and need some additional info - I will be happy you provide it to you.

说明

结果

推荐答案

添加索引在很多情况下都有帮助，但是您有一个子查询加入另一个子查询，当前表上没有索引可以帮助您加快速度.在这里使用索引的唯一方法是创建临时表.

Adding an index helps in many cases, but you have a subquery joining another subquery, no index on your current table can help you speed up. The only way you can use indexes here is to create temporary table.

因此，正如 Markus 所指出的，您需要将您的查询分解为几个较小的查询，将它们的结果存储在一个临时表中.比您可以向它们添加索引并希望加速您的查询.将大查询分解为几个小查询的另一个好处是，您可以更好地分析哪个部分较慢并修复它.

So as Markus pointed you need to break your query into a couple of smaller ones which store their results in a temporary table. Than you can add indexes to them and hopefully speedup your query. Another good thing about breaking big query into couple of smaller ones is that you can better profile which part is the slower one and fix it.

您还两次使用了一个子查询，这对性能不利，因为结果没有被缓存.

You have also used one subquery two times which is bad for performance as the result was not cached.

以下是您如何执行此操作的示例:

Here is an example of how you could do this:

DROP TEMPORARY TABLE IF EXISTS tmp_k;
CREATE TEMPORARY TABLE tmp_k
    ENGINE=Memory
SELECT 
    gps_unit_location.*,
    @i:= IF(((Speed_Kmh > 80) AND (@b = 0)), @i + 1, @i) AS IntervalID,
    @r:= IF(((Speed_Kmh > 80) AND (@b = 0)), 1, @r + 1) AS RowNumber,
    @b:= IF((Speed_Kmh > 80), 1, 0) AS IntervalCheck
FROM
    gps_unit_location,
    (SELECT @i:=0) i, 
    (SELECT @r:=0) r, 
    (SELECT @b:=0) b
ORDER BY
    dt,
    idgps_unit_location;

ALTER TABLE tmp_k ADD INDEX (IntervalID);

DROP TEMPORARY TABLE IF EXISTS tmp_max;
CREATE TEMPORARY TABLE tmp_max
    ENGINE=Memory
SELECT 
    IntervalID, 
    MAX(RowNumber) AS MaxRowNo
FROM
    temp_k
WHERE
    IntervalCheck = 1
GROUP BY 
    IntervalID;

ALTER TABLE tmp_max ADD INDEX (IntervalID);

SELECT 
    k.idgps_unit,
    MIN(k.dt) AS DT_Start,
    MIN(IF(k.RowNumber = 1, k.Lat, NULL)) AS Latitude_Start,
    MIN(IF(k.RowNumber = 1, k.Long, NULL)) AS Longitude_Start,
    MIN(IF(k.RowNumber = 1, k.Speed_kmh, NULL) AS Speed_Start,
    MAX(k.dt) AS DT_End,
    MIN(IF(k.RowNumber = m.MaxRowNo, k.Lat, NULL)) AS Latitude_End
    MIN(IF(k.RowNumber = m.MaxRowNo, k.Long, NULL)) AS Longitude_End
    MIN(IF(k.RowNumber = m.MaxRowNo, k.Speed_kmh, NULL)) AS Speed_End,
    AVG(Speed_kmh) AS Average_Speed,
    gu.name,
    gu.notes,
    gu.serial
FROM
    tmp_k AS k
    INNER JOIN tmp_max AS m
        USING(IntervalID)
    INNER JOIN gps_unit AS gu
        USING(idgps_unit)
    INNER JOIN user AS u
    ON (gu.idcustomer = u.idcustomer)
WHERE
    (k.IntervalCheck = 1) 
     AND (u.iduser = 14)
GROUP BY 
    k.IntervalID, 
    k.idgps_unit;

DROP TEMPORARY TABLE tmp_k;
DROP TEMPORARY TABLE tmp_max;

这篇关于查询优化 -- 耗时过长并停止服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！