MySQL не использует индекс для ORDER BY

У меня есть простая таблица MySQL с именем "test" с двумя столбцами:

Автоматический инкремент int column с именем 'id'
Столбец Varchar (3000), называемый "textcol"

Я создаю индекс в таблице на основе столбца "textcol". Тем не менее, запрос ORDER BY, похоже, не использует индекс, то есть оператор EXPLAIN в простом запросе с ORDER BY на textcol показывает NULL в столбце Key в его выводе, а также использует filesort.

Любые указатели на внесение изменений, чтобы помочь использовать индекс для запроса ORDER по запросу, будут полезны для меня.

Версия MySQL, заданная командой "mysql -version":

mysql Ver 14.14 Распределите 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)

Ответ 1

Поскольку он должен загрузить всю таблицу, чтобы ответить на запрос, и сортировка 4 элементов дешева, оптимизатор запросов может просто избегать касания индекса. Случается ли это с большими таблицами?

Обратите внимание, что столбец varchar (3000) не может быть индексом покрытия, поскольку MySQL не будет содержать больше, чем первые 768 или около того байтов varchar в индексе.

Если вы хотите, чтобы запрос читал только индекс, индекс должен иметь каждый столбец, в котором вы находитесь SELECT ing. На innodb, который должен начать работать для вашей таблицы с двумя столбцами, как только вы сделаете textcol достаточно маленьким; на MyISAM вам нужно будет включать в себя столбец первичного ключа, например CREATE INDEX textcolindex ON test (textcol,id);

Ответ 2

Некоторые полезные статьи по оптимизации ORDER BY:

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

Как в основном обсуждается, держите varchar до 767 и добавьте ключ для порядка:

CREATE TABLE test (
id INTEGER NOT NULL AUTO_INCREMENT,
textcol VARCHAR(767),
PRIMARY KEY(id),
KEY orderby (`textcol`)
);

Чтобы избежать 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`)
);

также:

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;

Дополнительно: 'Использование где; Использование индекса '

Ответ 3

У меня такая же проблема. MySQL глуп. fyi: У меня есть таблица с более чем 500 000 000 записей. Я хотел:

select * from table order by tid limit 10000000, 10;

tid является основным ключом в таблице и автоматически индексируется mysql.

Это заняло много времени, и я отменил запрос. то я даю mysql "объяснить" запрос и признал, что он не будет использовать индекс для первичного ключа. после прочтения многих документов из mysql я попытался заставить mysql использовать индекс через "USE INDEX (...)", и это также не сработало. Затем я узнал, что mysql, кажется, всегда коррелирует предложение where с предложением order by. Поэтому я попытался расширить предложение where с условием, касающимся индекса. Я закончил с:

select * from table use index (PRIMARY) where tid > 0 order by tid limit 10000000, 10;

где tid является первичным ключом в таблице и является значением автоинкремента, начинающимся с 1.

Это сработало после того, как я дал mysql объяснить запрос мне. И вот: запрос занял всего 4 секунды.