MySQL不对ORDER BY使用索引 [英] MySQL not using index for ORDER BY
问题描述
我有一个名为'test'的简单MySQL表，其中有两列:
I've a simple MySQL table named 'test' with two columns:
- 自动递增称为"id"的int列
- 称为'textcol'的Varchar(3000)列
我基于'textcol'列在表中创建索引.但是，ORDER BY查询似乎未使用索引，即，简单查询中的EXPLAIN语句在textcol上使用ORDER BY在其输出的Key列中显示NULL，并且还使用了文件排序.
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.
任何进行更改以帮助通过查询使用ORDER索引的指针对我来说都是有用的.
Any pointers to make changes to help use the index for the ORDER by query will be useful to me.
"mysql --version"命令给定的MySQL版本:
MySQL version as given by "mysql --version' command:
mysql ver 14.14 Distrib 5.1.58，用于使用readline 6.2的debian-linux-gnu(x86_64)
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)
推荐答案
由于必须加载整个表来回答查询并且对4个元素进行排序很便宜，因此查询优化器可能只是避免触摸索引.大表仍然会发生这种情况吗?
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?
请注意，varchar(3000)列不能作为覆盖索引，因为MySQL在索引中最多只能包含varchar的前768个字节.
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.
如果您希望查询仅读取索引，则索引中必须包含要SELECT查找的每一列.在innodb上，一旦您使textcol足够小，它应该就可以开始用于您的两列表.在MyISAM上，您需要自己包括主键列，例如CREATE INDEX textcolindex ON test (textcol,id);
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);
这篇关于MySQL不对ORDER BY使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
