使用ORDER BY时MySQL不使用索引(“使用文件排序") [英] MySQL not using indexes ("Using filesort") when using ORDER BY

问题描述

由于在我的SQL代码中使用了"ORDER BY"语句，我遇到了一些非常主要的性能问题.

I'm hitting some quite major performances issues due to the use of "ORDER BY"-statements in my SQL-code.

一切都很好，只要我不在SQL中使用ORDER BY语句即可.但是，一旦在SQL代码中引入ORDER BY:s，由于缺少正确的索引编制，一切都会大大降低.有人会认为解决此问题是微不足道的，但是从论坛讨论等来看，这似乎是一个相当普遍的问题，我尚未看到对此问题的明确而简明的答案.

Everything is fine as long as I'm not using ORDER BY-statements in the SQL. However, once I introduce ORDER BY:s in the SQL code everything slows down dramatically due to the lack of correct indexing. One would assume that fixing this would be trivial, but judging from forum discussions, etc this seems to be a rather common issue that I've yet to see a definitive and concise answer to this question.

问题:鉴于下表...

CREATE TABLE values_table (
  id int(11) NOT NULL auto_increment,
  ...
  value1 int(10) unsigned NOT NULL default '0',
  value2 int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY value1 (value1),
  KEY value2 (value2),
) ENGINE=MyISAM AUTO_INCREMENT=2364641 DEFAULT CHARSET=utf8;

...如何创建索引，以对表中的 value1 范围进行查询，同时对 value2 的值进行排序?

... how do I create indexes that will be used when querying the table for a value1-range while sorting on the value of value2?

当前，当不使用ORDER BY子句时，获取就可以了.

Currently, the fetching is OK when NOT using the ORDER BY clause.

请参阅以下解释查询"输出:

See the following EXPLAIN QUERY output:

OK, when NOT using ORDER BY:

EXPLAIN select ... from values_table this_ where this_.value1 between 12345678 and 12349999 limit 10;

+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | this_ | range | value1        | value1   | 4       | NULL | 3303 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

However, when using ORDER BY I get "Using filesort":

EXPLAIN select ... from values_table this_ where this_.value1 between 12345678 and 12349999 order by this_.value2 asc limit 10;

+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | this_ | range | value1        | value1   | 4       | NULL | 3303 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+

有关表内容的一些其他信息:

Some additional information about the table content:

SELECT MIN(value1), MAX(value1) FROM values_table;
+---------------+---------------+
| MIN(value1)   | MAX(value2)   |
+---------------+---------------+
|             0 |    4294967295 |
+---------------+---------------+

...

SELECT MIN(value2), MAX(value2) FROM values_table;
+---------------+---------------+
| MIN(value2)   | MAX(value2)   |
+---------------+---------------+
|             1 |        953359 |
+---------------+---------------+

请让我知道是否需要进一步的信息来回答问题.

Please let me know if any further information is needed to answer the question.

非常感谢！

更新#1:添加新的复合索引( ALTER TABLE values_table ADD INDEX(value1，value2)； )不能解决问题.添加这样的索引后，您仍然会获得使用文件排序".

Update #1: Adding a new composite index (ALTER TABLE values_table ADD INDEX (value1, value2);) does not solve the problem. You'll still get "Using filesort" after adding such an index.

更新#2:我在问题中没有提到的一个约束是，我宁愿更改表的结构(例如添加索引等)，也不愿更改所使用的SQL查询. . SQL查询是使用Hibernate自动生成的，因此请考虑将其固定的方式.

Update #2: A constraint that I did not mention in my question is that I'd rather change the structure of the table (say adding indexes, etc.) than changing the SQL queries used. The SQL queries are auto-generated using Hibernate, so consider those more or less fixed.

推荐答案

在这种情况下，由于使用RANGE过滤条件，因此无法使用索引.

You cannot use an index in this case, as you use a RANGE filtering condition.

如果您使用类似的东西:

If you'd use something like:

SELECT  *
FROM    values_table this_
WHERE   this_.value1 = @value
ORDER BY
        value2
LIMIT 10

，然后在(VALUE1, VALUE2)上创建一个复合索引将用于过滤和排序.

, then creating a composite index on (VALUE1, VALUE2) would be used both for filtering and for ordering.

但是您使用的是远程条件，这就是为什么您仍然需要执行订购的原因.

But you use a ranged condition, that's why you'll need to perform ordering anyway.

您的综合索引将如下所示:

Your composite index will look like this:

value1 value2
-----  ------
1      10
1      20
1      30
1      40
1      50
1      60
2      10
2      20
2      30
3      10
3      20
3      30
3      40

，并且如果在value1中选择1和2，您仍然不会得到完整排序的value2集.

, and if you select 1 and 2 in value1, you still don't get a whole sorted set of value2.

如果您在value2上的索引不是选择性很强(即表中的DISTINCT value2并不多)，您可以尝试:

If your index on value2 is not very selective (i. e. there are not many DISTINCT value2 in the table), you could try:

CREATE INDEX ix_table_value2_value1 ON mytable (value2, value1)

/* Note the order, it's important */    

SELECT  *
FROM    (
        SELECT  DISTINCT value2
        FROM    mytable
        ORDER BY
                value2
        ) q,
        mytable m
WHERE   m.value2 >= q.value2
        AND m.value2 <= q.value2
        AND m.value1 BETWEEN 13123123 AND 123123123

这称为SKIP SCAN访问方法. MySQL不直接支持它，但是可以这样模拟.

This is called a SKIP SCAN access method. MySQL does not support it directly, but it can be emulated like this.

在这种情况下将使用RANGE访问，但是除非DISTINCT value2包含的行少于1%行，否则您可能不会获得任何性能上的好处.

The RANGE access will be used in this case, but probably you won't get any performance benefit unless DISTINCT value2 comprise less than about 1% of rows.

注意以下用途:

m.value2 >= q.value2
AND m.value2 <= q.value2

代替

m.value2 = q.value2

这使得MySQL在每个循环上执行RANGE检查.

This makes MySQL perform RANGE checking on each loop.

这篇关于使用ORDER BY时MySQL不使用索引(“使用文件排序")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！