按时间顺序对最近800个条目进行MySQL查询的速度

Question

我有一个运行在Ubuntu上的MySQL数据库，它每分钟(24x7)点击一次250个客户机。我的专栏名称是：

ip_address varchar(16),
status varchar(7),
timestamp datetime 

已经运行了几个星期，并且已经增长了(10,581,421+行)。我运行一个查询来返回一个特定IP地址的最新800个结果：

SELECT *
FROM (
    SELECT Device_ip, Status, timestamp
    FROM ping_results
    where Device_ip = '192.168.1.1'
    order by timestamp desc
    LIMIT 800
) SUB ORDER BY timestamp asc;

返回结果需要10+秒。我能做些什么来加速这件事吗？

Answer 1

查看查询，我发现您需要按升序检索最近的800个pings。

您应该能够使用以下索引来改进查询

ALTER TABLE ping_results ADD INDEX DEV_TIME_IP_NDX (`Device_ip`,`timestamp`,`ip_address`);

这将以下列方式帮助您的查询

• 快速地将ORDER BY简化为对特定device_ip的反向索引扫描。
• 由于所有三列都在索引中，因此该表不用于检索任何数据。

试试看!！

更新2014-11-03 16:05 EST

如果ping_results表中有一个id列，则可以使用JOIN重新执行查询。

SELECT B.Device_ip, B.Status, B.timestamp FROM
(
    SELECT id FROM
    (
        SELECT id,timestamp FROM ping_results
        where Device_ip = '192.168.1.1'
        order by timestamp desc LIMIT 800
    ) SUB ORDER BY timestamp
) A LEFT JOIN ping_results B USING (id);

在创建了我建议的索引之后，您应该对这个查询和原始查询运行解释计划。然后，选择最佳解释计划或运行最快的查询。很有可能，您的第一个查询应该是足够的，因为它在查询优化器中有“较小的噪音”需要处理。

顺便说一句，我使用左联接，因为id值将按照从子查询生成的顺序排列。执行内部连接会无意中对键进行重新排序。

Answer 2

您可以像这样从子查询中分离出来：

SELECT Device_ip, Status, timestamp
    FROM ping_results
    WHERE Device_ip = '192.168.1.1'
    AND timestamp > DATE_SUB(NOW(), INTERVAL 800 MINUTE )
    ORDER BY timestamp ASC

这应该会稍微简化查询计划，而且您只执行一个订单，而不是两个订单。

正如其他人所提到的，索引对于提高性能来说是个好主意，而且可能会比操作查询做得更多。

Answer 3

有两件事：

1)你有索引吗？show index from ping_results怎么说？

2)不要使用子查询。将查询转换为联接语句。