MySQL 不为 ORDER BY 使用索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9641463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL not using index for ORDER BY
提问by Kowshik
I've a simple MySQL table named 'test' with two columns:
我有一个名为“test”的简单 MySQL 表，其中包含两列：
- Auto incrementing int column called 'id'
- Varchar(3000) column called 'textcol'
- 名为“id”的自动递增 int 列
- Varchar(3000) 列名为“textcol”
I create an index in the table based on the 'textcol' column. However, the ORDER BY query doesn't seem to be using the index i.e. the EXPLAIN statement on a simple query with ORDER BY on textcol shows NULL in the Key column in its output and also uses filesort.
我根据“textcol”列在表中创建了一个索引。但是，ORDER BY 查询似乎没有使用索引，即在 textcol 上使用 ORDER BY 的简单查询上的 EXPLAIN 语句在其输出的 Key 列中显示 NULL 并且还使用文件排序。
Any pointers to make changes to help use the index for the ORDER by query will be useful to me.
任何进行更改以帮助使用 ORDER by 查询索引的指针对我都很有用。
MySQL version as given by "mysql --version' command:
MySQL 版本由“mysql --version”命令给出：
mysql Ver 14.14 Distrib 5.1.58, for debian-linux-gnu (x86_64) using readline 6.2
mysql Ver 14.14 Distrib 5.1.58，用于 debian-linux-gnu (x86_64) 使用 readline 6.2
mysql> CREATE TABLE test (id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), textcol VARCHAR(3000));
Query OK, 0 rows affected (0.05 sec)
mysql> DESCRIBE test;
+---------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| textcol | varchar(3000) | YES | | NULL | |
+---------+---------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> CREATE INDEX textcolindex ON test (textcol);
Query OK, 0 rows affected, 2 warnings (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM test;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
| test | 1 | textcolindex | 1 | textcol | A | NULL | 1000 | NULL | YES | BTREE | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
mysql> INSERT INTO test (textcol) VALUES ('test1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (textcol) VALUES ('test2');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (textcol) VALUES ('test3');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (textcol) VALUES ('test4');
Query OK, 1 row affected (0.00 sec)
mysql> EXPLAIN SELECT * FROM test ORDER BY textcol;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 4 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test ORDER BY id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 4 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
采纳答案by BCoates
Since it has to load the entire table to answer the query and sorting 4 elements is cheap, the query optimizer might just be avoiding touching the index. Does it still happen with larger tables?
由于它必须加载整个表来回答查询并且对 4 个元素进行排序很便宜，因此查询优化器可能只是避免接触索引。更大的桌子还会发生这种情况吗？
Note that a varchar(3000) column can't be a covering index because MySQL won't include more than the first 768 or so bytes of a varchar in an index.
请注意，varchar(3000) 列不能是覆盖索引，因为 MySQL 不会在索引中包含超过 varchar 的前 768 个字节左右。
If you want the query to only read the index, the index must have every column you're SELECTing for in it. On innodb, that should start working for your two-column table once you make textcol small enough; on MyISAM you'll need to include the primary key column yourself, like CREATE INDEX textcolindex ON test (textcol,id);
如果您希望查询仅读取索引，则索引必须包含您要SELECT查找的每一列。在 innodb 上，一旦您将 textcol 设置得足够小，这应该开始适用于您的两列表；在 MyISAM 上，您需要自己包含主键列，例如CREATE INDEX textcolindex ON test (textcol,id);
回答by Sensatus
Some useful articles on ORDER BY optimisation:
一些关于 ORDER BY 优化的有用文章：
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
http://opsmonkey.blogspot.co.uk/2009/03/mysql-query-optimization-for-order-by.html
http://opsmonkey.blogspot.co.uk/2009/03/mysql-query-optimization-for-order-by.html
As largely discussed, keep the varchar down to 767 and add a key for the order by:
正如大部分讨论的那样，将 varchar 保持为 767 并通过以下方式为订单添加一个键：
CREATE TABLE test (
id INTEGER NOT NULL AUTO_INCREMENT,
textcol VARCHAR(767),
PRIMARY KEY(id),
KEY orderby (`textcol`)
);
To avoid filesortsif adding extra 'WHERE' parameters, extend the 'orderby' index key using a multiple column index:
为避免filesorts添加额外的“WHERE”参数，请使用多列索引扩展“orderby”索引键：
CREATE TABLE test (
id INTEGER NOT NULL AUTO_INCREMENT,
tom INT(11) NOT NULL DEFAULT 0,
gerry INT(11) NOT NULL DEFAULT 0,
textcol VARCHAR(767),
PRIMARY KEY(id),
KEY orderby (`tom`,`gerry`, `textcol`)
);
Also:
还：
INSERT INTO test (tom, gerry, textcol) VALUES (1,2,'test4');
INSERT INTO test (tom, gerry, textcol) VALUES (1,2,'test2');
EXPLAIN SELECT id, textcol FROM test WHERE tom = 1 AND gerry =2 ORDER BY textcol;
Extra: 'Using where; Using Index'
额外的：'使用哪里; 使用索引'
回答by Erhard Dinhobl
I got the same problem. MySQL is stupid. fyi: I have a table with more than 500,000,000 records. I wanted to to:
我遇到了同样的问题。MySQL是愚蠢的。仅供参考：我有一张包含超过 500,000,000 条记录的表。我想：
select * from table order by tid limit 10000000, 10;
tid is the primary key in the table and is automatically indexed by mysql.
tid 是表中的主键，由 mysql 自动索引。
This took a long time and I cancelled the query. then I let mysql "explain" the query and recognized that it won't use the index for the primary key. after reading many docs from mysql i tried to force mysql to use the index via "USE INDEX(...)" and dis also did not work. Then I rocognized that mysql seems to always correlate the where clause with the order by clause. So I tried to extend the where clause with a condition with touches the index. I ended up with:
这花了很长时间，我取消了查询。然后我让mysql“解释”查询并认识到它不会使用主键的索引。从 mysql 阅读了许多文档后，我试图通过“USE INDEX(...)”强制 mysql 使用索引，但 dis 也不起作用。然后我意识到mysql似乎总是将where子句与order by子句相关联。因此，我尝试使用触及索引的条件扩展 where 子句。我结束了：
select * from table use index (PRIMARY) where tid > 0 order by tid limit 10000000, 10;
where tid is the primary key in the table and is an autoincrement value which starts at 1.
其中 tid 是表中的主键，是从 1 开始的自动增量值。
This worked after I let mysql explain the query to me. And behold: the query took only 4 seconds.
在我让 mysql 向我解释查询之后，这奏效了。看哪：查询只用了 4 秒。
