mysql - 使用 ORDER BY 子句如何提高和降低性能？

Question

我有一个名为 devicelog 的 MySQL 表，它在 id 上有 PK，但在 device_id (INT)、field_id (INT) 上有多个索引和 unixtime (BIGINT)。它们只是默认的 InnoDB 索引。

我试图在特定时间之后获取 ID，但我通过不同的值和不同的 ORDER BY 获得了不同的性能。 ID 和 unixtimes 都有正相关关系，因为随着插入更多数据，它们都按顺序增加，因此似乎可以安全地忽略 unixtime 上的排序。我的表有大约 2500 万条记录，性能非常重要。

此查询相当慢(~0.5 秒):编辑:在使用 USE INDEX(unixtime) 后，我能够显着提高性能(<0.01 秒!)。

SELECT 
    id
FROM
    devicelog
USE INDEX(unixtime) /* edit: looking at the EXPLAIN, I can use this index and it sped things up a bit */
WHERE
    device_id = 26
        AND field_id = 64
        AND unixtime >= 1397166634707 /* a fairly recent time */
/* with no ORDER BY clause, this query is surprisingly slow */
LIMIT 1

解释:

1, SIMPLE, devicelog, index_merge, device_id,field_id,field_id_2,unixtime, field_id,device_id, 8,8, , 6667, Using intersect(field_id,device_id); Using where

这个查询非常快(<0.01 秒):

SELECT 
    id
FROM
    devicelog
WHERE
    device_id = 26
        AND field_id = 64
        AND unixtime >= 1397166634707 /* a fairly recent time */
ORDER BY unixtime ASC                 /* <- using unixtime to order */
LIMIT 1

解释:

1, SIMPLE, devicelog, range, device_id,field_id,field_id_2,unixtime, unixtime, 9, , 897776, Using index condition; Using where

省略 ORDER BY 会如何降低性能？认为它会提高速度似乎是合乎逻辑的。

但是，如果我将 unixtime 更改为更早的值，更改为“1”，当我使用 ORDER BY unixtime 时它会完全变慢。我相信 unixtime 索引是按升序排列的，所以这也没有多大意义。

此查询的执行方式与上述查询相反。

极快(<0.01 秒):

SELECT 
    id
FROM
    devicelog
WHERE
    device_id = 26
        AND field_id = 64
        AND unixtime >= 1 /* a long time ago */
LIMIT 1

解释:

1, SIMPLE, devicelog, index_merge, device_id,field_id,field_id_2,unixtime, field_id,device_id, 8,8, , 6742, 使用 intersect(field_id,device_id);使用位置

此查询与快速查询完全相同，只是它使用的时间较早:

极慢(~7 秒):

SELECT 
    id
FROM
    devicelog
WHERE
    device_id = 26
        AND field_id = 64
        AND unixtime >= 1         /* a long time ago */
ORDER BY unixtime ASC             /* <- using unixtime to order */
LIMIT 1

解释:

1, SIMPLE, devicelog, index, device_id,field_id,field_id_2,unixtime, unixtime, 9, , 3504, 使用位置

有人对巨大的性能差异有任何见解吗？

Answer 1

如果不了解表中的行数和表的确切结构等信息，就很难对性能提出明确的建议。

您可以在 (unixtime, device_id, file_id, id) 上尝试复合覆盖索引。 (如果您不知道该术语，请查找覆盖索引)。

这将允许查询的 unixtime 部分满足 BTREE 查找，然后查询的其余部分可以满足索引扫描。

如果您指定 ORDER BY unixtime ASC LIMIT 1，您是在告诉查询引擎停止扫描该索引(一旦它获得单打。

我不知道为什么当您省略 ORDER BY 时它有时会继续扫描七秒钟。它可能必须寻找匹配的 device_id 和 file_id 值。