mysql - 使用许多子选择优化 MySQL 查询

Question

我必须使用 SQL 命令维护一个应用程序(文章管理)。

一个查询如下所示:

SET @from_timestamp = '2013-07-01 00:00:00';
SET @serialnumber = '%aaaa01%';

SELECT
    test1.article,
    test1.serialnumber,
    test2.`timestamp` as test2_timestamp,
    test2.additionalinfo,
    test1.`timestamp` AS test1_timestamp,
    test1.text0 AS test1_text0,
    test1.text1 AS test1_text1,
    test1.text2 AS test1_text2,
    test1.text3 AS test1_text,
    test3.text1 as test3_text1,
    test3.`timestamp` AS test3_timestamp,
    test3.`status`,
    TIMESTAMPDIFF(MINUTE, test1.`timestamp`, test3.`timestamp`) as DeltaT
FROM (
    SELECT
        max(`timestamp`) AS TIMESTAMP,
        article,
        serialnumber,
        text1,
        text2,
        text3,
        text4
    FROM exampleTable
    WHERE
        test = 'test1' AND
        `timestamp` >= @from_timestamp AND
        os0 LIKE @serialnumber
    GROUP BY serialnumber
) AS test1
LEFT JOIN (
    SELECT
        max(`timestamp`) AS TIMESTAMP,
        serialnumber,
        status,
        text1
    FROM exampleTable
    WHERE
        test = 'test3' AND
        `timestamp` >= @from_timestamp
    GROUP BY serialnumber
) AS test3 
ON test1.serialnumber = test3.serialnumber
LEFT JOIN (
    SELECT
        max(`timestamp`) AS TIMESTAMP,
        serialnumber,
        article,
        text1
    FROM exampleTable
    WHERE
        LENGTH(serialnumber) = 13 AND
        test = 'test2' AND
        `status` = -1 AND
        `timestamp` >= @from_timestamp
    GROUP BY serialnumber
) AS test2 
ON test1.serialnumber = test2.serialnumber
ORDER BY test1.`timestamp`

它仅使用一张保存产品数据的表。但正如预期的那样，这个查询确实很慢。有什么优化的可能吗？

编辑:Attached image of the explain sql result

Edit2:该表有很多数据(最后 COUNT(*) 提供了 39589279 ;-) )

编辑3:Attached image of the explain table result

Edit4:请不要责怪我的表定义，它是由其他人开发的非常旧的并且不可能在不破坏更改的情况下更改它。

Answer 1

首先，您对同一个表进行了三次查询，然后进行连接。第一个简化是仅使用一个选择:

SELECT
    article,
    serialnumber,
    text1,
    text2,
    text3,
    text4,
    max(case when 
        test = 'test1' AND
        os0 LIKE @serialnumber
    then `timestamp` else null end) AS test1_timestamp,
    max(case when 
        LENGTH(serialnumber) = 13 AND
        test = 'test2' AND
        `status` = -1
    then `timestamp` else null end) AS test2_timestamp,
    max(case when 
        test = 'test3'
    then `timestamp` else null end) AS test3_timestamp,
FROM exampleTable
WHERE
    test in ('test1', 'test2', 'test3') AND
    `timestamp` >= @from_timestamp
GROUP BY serialnumber

这已经给你带来了很大的进步。

其次，您必须在 timestamp 列上放置索引以提高速度。