使用批量计数(行)和(row+row2)优化MySQL语句

Question

我需要在一个表上使用InnoDB存储引擎，在任何给定的时间，表中都有大约1mil的记录。它的记录以非常快的速度被插入到其中，然后在几天内被删除，可能是一周。ping表大约有一百万行，而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;

我在entrytime上添加了一个索引，但没有骰子。有没有人可以告诉我这个查询的基本优化应该考虑什么？结果集只有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子句中，或者创建一个将这些计算作为属性的视图，并使用该视图来访问值。