我有一个名为’test’的简单MySQL表，其中有两列：

1. 自动递增int列，称为“ id”
2. Varchar（3000）列称为“ textcol”

我基于“ textcol”列在表中创建索引。但是，ORDER BY查询似乎未使用索引，即在简单查询上的EXPLAIN语句与textcol上的ORDER
BY在其输出的Key列中显示NULL，并且还使用了文件排序。

进行任何更改以帮助按查询将索引用于ORDER的指针对我来说都是有用的。

由“ mysql –version”命令给定的MySQL版本：

适用于使用readline 6.2的debian-linux-gnu（x86_64）的mysql Ver 14.14 Distrib 5.1.58

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)