使用ORDER BY子句如何提高和降低性能? [英] How would using an ORDER BY clause both increase and decrease performance?

问题描述

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

I have a MySQL table called devicelog with it's PK on id, but multiple indices on device_id (INT), field_id (INT), and unixtime (BIGINT). They are just the default InnoDB indices.

我正在尝试在特定时间获取ID，但是使用不同的值和不同的ORDER BY会获得不同的性能. ID和unixtimes都具有正相关关系，因为随着插入更多数据，ID和unixtimes的顺序都在增加，因此似乎可以安全地省略对unixtime的排序.我的桌子有大约2500万条记录，性能至关重要.

I'm trying to get the ID next to a certain time, I get WAY different performance with different values and different ORDER BYs. IDs and unixtimes both have a positive association, since they both are increasing in order as more data gets inserted, so it seems like it would be okay to safely omit ordering on unixtime. My table has around 25 million records and performance is extremely vital.

此查询相当慢(〜0.5秒):使用USE INDEX(unixtime)后，我能够相当大地提高性能(<0.01秒！).

This query is fairly slow (~0.5 seconds): after using USE INDEX(unixtime), I was able to increase performance quite a bit (<0.01 seconds!).

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秒):

This query is extremely fast (<0.01 seconds):

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会如何降低性能?认为这会提高速度似乎是合乎逻辑的.

How would omitting an ORDER BY decrease performance? It seems logical to think that it would increase speed.

但是，如果我将unixtime更改为"1"，则当我使用ORDER BY unixtime时，它将完全变慢.我相信unixtime索引是按升序排列的，所以这也没有太大意义.

Yet, if I change the unixtime to something far back, to "1", it will completely slow down when I use the ORDER BY unixtime. I believe the unixtime index is ordered ascendingly, so this doesn't make much sense either.

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

This query performs in an opposite manner as the queries above.

极快(<0.01秒):

Extremely fast (<0.01 seconds):

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, Using intersect(field_id,device_id); Using where

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

This query is the exact same as the fast one, except it's using an older time:

极慢(〜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, Using where

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

Does anyone have any insight on the vast performance differences?

推荐答案

我认为已记录了LIMIT优化的行为，请参见

I think it's documented behavior of LIMIT optimization, see http://dev.mysql.com/doc/refman/5.5/en/limit-optimization.html

优化LIMIT查询

Optimizing LIMIT Queries

MySQL有时会优化具有LIMIT row_count子句而没有HAVING子句的查询:

MySQL sometimes optimizes a query that has a LIMIT row_count clause and no HAVING clause:

[...] 如果您将LIMIT row_count与ORDER BY一起使用，MySQL会在找到排序结果的前row_count行后立即结束排序，而不是对整个结果进行排序.如果通过使用索引进行排序，这将非常快.如果必须执行文件排序，则在找到第一个row_count之前，将选择与查询匹配的所有行(不带LIMIT子句)，并对其中的大多数或全部进行排序.找到初始行后，MySQL不会对结果集的其余部分进行排序.

[...] If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

[...]

MySQL一旦向客户端发送了所需的行数，它将立即中止查询，除非您使用的是SQL_CALC_FOUND_ROWS.

As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS.

因为您要获取某个日期旁边的ID，所以我认为对结果进行排序非常重要，因为否则您可以获取任意值.否则，您必须在条件中使用MIN(id)以获得所需的id值.

Because you're trying to get the ID next to a certain date, I would think ordering the result very vital, because else you can get an arbitrary value. Else you've got to use MIN(id) with your conditions to get the desired id value.

这篇关于使用ORDER BY子句如何提高和降低性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！