MySQL сортировка файлов происходит даже с индексами - Как я могу исправить

У меня есть простой запрос ниже, что я могу убедиться, что работает быстро, если таблица. Я сделал объяснение по запросу, и он говорит Использование где; Использование сортировки файлов. Есть ли способ избавиться от сортировки файлов? Данные содержат только около 25 элементов; но это может закончиться 300 или больше.

mysql> show create table phppos_categories;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_categories | CREATE TABLE `phppos_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `phppos_categories_ibfk_1` (`parent_id`),
  KEY `name` (`name`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM (`phppos_categories`) WHERE `parent_id` = 5 ORDER BY `name` asc;
+----+-----------+-------------------+
| id | parent_id | name              |
+----+-----------+-------------------+
|  3 |         5 | Basketball Shoes  |
|  7 |         5 | Basketball Shorts |
+----+-----------+-------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM (`phppos_categories`) WHERE `parent_id` = 5 ORDER BY `name` asc;
+----+-------------+-------------------+------+------------------------------------+--------------------------+---------+-------+------+-----------------------------+
| id | select_type | table             | type | possible_keys                      | key                      | key_len | ref   | rows | Extra                       |
+----+-------------+-------------------+------+------------------------------------+--------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | phppos_categories | ref  | phppos_categories_ibfk_1,parent_id | phppos_categories_ibfk_1 | 5       | const |    2 | Using where; Using filesort |
+----+-------------+-------------------+------+------------------------------------+--------------------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql>
Источник

2 ответа

Решение

Здесь вы можете удалить сортировку файлов, добавив многостолбцовый индекс (parent_id, name).

ALTER TABLE phppos_categories ДОБАВИТЬ ИНДЕКС (parent_id,name);

Вообще говоря, MySQL будет использовать только один индекс, когда он потенциально может сортировать с одним из них (если вы просто используете два индекса для запроса к таблице, он МОЖЕТ использовать два индекса с помощью индекса-слияния, но часто это не так), Решение состоит в том, чтобы создать единый индекс, охватывающий все столбцы, которые необходимо запросить.

Во-вторых, MySQL может выполнять поиск "диапазона" или "сортировать" только по последнему столбцу в индексе, который он использует. Любые столбцы до этого должны быть точными совпадениями.

Исходя из этого, мы можем сначала создать индекс с parent_id, который имеет точное совпадение (=5), а затем по имени, которое является ограничением вашего порядка.

Главное, о чем нужно знать, это то, что вы не хотите добавлять больше индексов, чем необходимо, и индекс, подходящий для каждого возможного запроса, может оказаться нецелесообразным, особенно с учетом дополнительного пространства для хранения и работы, необходимой для поддержания индекса в актуальном состоянии., В рамках этого сравнения вам также необходимо учитывать частоту обновления таблицы. Если он редко обновляется, то больше индексов потенциально меньше проблем.

Смотрите здесь для получения дополнительной информации: https://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html

Источник

Вы видите "использование сортировки файлов", потому что вы заказываете name столбец, который является полем varchar(255).

ORDER BY `name` asc;

Объясните отчеты, что количество проверенных записей составляет только 2, и используется индекс для parent_id. В вашем наборе результатов есть 2 строки. Поэтому MySQL не делал никакой дополнительной или ненужной работы.

Лично я не стал бы беспокоиться о попытке избавиться от "использования файловой сортировки" в этом случае. Даже если у вас было 300 строк в таблице, вы все еще ограничиваете набор результатов с помощью где в индексированном поле (parent_id), и количество проверенных строк будет равно числу в вашем наборе результатов.

Источник
Другие вопросы по тегам