sql - 使用大量 count(row) 和 sum(row+row2) 优化 MySQL 语句

Question

我需要在任何给定时间都在包含大约 100 万条记录的表上使用 InnoDB 存储引擎。它以非常快的速度插入记录，然后在几天甚至一周内删除这些记录。 ping 表大约有 100 万行，而 website 表只有大约 10,000 行。

我的声明是这样的:

select url
from website ws, ping pi 
where ws.idproxy = pi.idproxy and pi.entrytime > curdate() - 3 and contentping+tcpping is not null 
group by url 
having sum(contentping+tcpping)/(count(*)-count(errortype)) < 500 and count(*) > 3 and 
count(errortype)/count(*) < .15
order by sum(contentping+tcpping)/(count(*)-count(errortype)) asc;

我在进入时间添加了索引，但没有骰子。任何人都可以告诉我我应该考虑如何对该查询进行基本优化。结果集只有 200 行左右，所以我不会在那里被杀死。

Answer 1

在缺乏关系模式的情况下，我将不得不做出一些猜测。

• 如果您要创建 WHERE a.attrname = b.attrname 子句，则需要 JOIN。

• 使用 COUNT(*) 既多余，有时也比 COUNT(some_specific_attribute) 效率低。主键是一个很好的候选者。

• 为什么要测试 contentping+tcpping IS NOT NULL，要求进行看似不必要的计算，而不是仅仅测试各个属性是否为 null？

这是我的改进尝试:

SELECT url
FROM website AS ws
    JOIN ping AS pi
        ON ws.idproxy = pi.idproxy
WHERE
    pi.entrytime > CURDATE() - 3
    AND pi.contentping IS NOT NULL
    AND pi.tcpping IS NOT NULL
GROUP BY url
HAVING
    SUM(pi.contentping + pi.tcpping) / (COUNT(pi.idproxy) - COUNT(pi.errortype)) < 500
    AND COUNT(pi.idproxy) > 3
    AND COUNT(pi.errortype) / COUNT(pi.idproxy) < 0.15
ORDER BY
    SUM(pi.contentping + pi.tcpping) / (COUNT(pi.idproxy) - COUNT(pi.errortype)) ASC;

在 HAVING 和 ORDER BY 子句中执行大量相同的计算可能会降低性能。您可以将它们放入 SELECT 子句中，或者创建一个将这些计算作为属性的 View ，并使用该 View 来访问这些值。