php - 当数据集很大时，连接查询同一个表的速度很慢(想要更快)？

Question

此查询维护在表格下方

SELECT A.XXX as XXX,B.total_result as total_cost
FROM TABLE  as A
LEFT JOIN 
(
    SELECT XXX,sum(`click`)*price as total_result
    FROM TABLE
    WHERE main_id = '1'  AND (DATE(`click_time`) BETWEEN '2017-11-01' AND '2017-11-30' OR `click_time` IS NULL)
    GROUP BY XXX,price
) B ON B.`XXX` = A.`XXX`
where B.click IS NOT NULL   
GROUP BY A.XXX
LIMIT 0,20

表格

    k_id    main_id     XXX      click  price   click_time
        1     1         aaa         1   0.25    2017-11-05
        2     1         bbb         1   0.36    2017-11-05
        3     1         bbb         1   0.45    2017-11-05
        4     1         aaa         1   0.36    2017-11-05
        5     1         ccc         1   0.98    2017-11-05
        6     1         bbb         1   0.55    2017-11-05
        7     1         aaa         1   0.25    2017-11-05
        8     1         ccc         1   0.98    2017-11-05
        9     1         aaa         1   0.25    2017-11-05
        10    1         bbb         1   0.45    2017-11-05

我想要此查询修改工作此大型数据集表

Answer 1

您可以采取一些措施来优化此查询。

在查询端:

尽可能避免排序 - 我可以看到子查询中有一个 GROUP BY 子句，但没有 ORDER BY 子句。如果您不太关心子查询中结果的顺序，请将以下 order 子句添加到子查询的末尾，这将告诉 MySQL 不要使用默认顺序并提高性能。

ORDER BY NULL

====

避免在潜在索引列上调用函数 - 因为它不允许 MySQL 使用该列上的索引。满足以下条件:

DATE(`click_time`) BETWEEN '2017-11-01' AND '2017-11-30'

可以修改为这一点，以避免在索引列上使用 DATE 函数，而只对常量值使用函数:

click_time >= DATE('2017-11-01') AND click_time < (DATE('2017-11-30') + INTERVAL 1 DAY)

====

不要在查询中使用 OFFSET 值 - 您可以使用 faster pagination with offset in MySQL 的替代方法来代替 LIMIT X,Y .

===

在适当的时候使用数字值 - 当将数字列与字符串进行比较时，您会强制 MySQL 将每行的列值从数字转换为字符串，然后才执行比较。因此，在条件main_id = '1'时，如果main_id是数字列，则条件应该是:

main_id = 1

而不是:

main_id = '1'

===

此外，如果您可以提供架构结构，则可以针对这种情况推荐适当的索引。

顺便说一句，我从这个online MySQL query optimizer得到了推荐，因此请随意输入您的查询和架构来获取索引建议(您没有提供架构结构，否则我也会提供索引建议)。