MySQL не использует индекс для ORDER BY
У меня есть индекс (column_1, column_2, column_3), и когда я запускаю этот запрос
SELECT * FROM table ORDER BY column_1 DESC, column_2 ASC, column_3 ASC
этот индекс не используется.
Мне нужно было бы использовать этот индекс, поскольку существует большая разница во времени запроса, когда я использую только ORDER BY column_1 DESC (около 0,0005 секунды) и ORDER BY column_1 DESC, column_2 ASC, column_3 ASC (около 0,4 секунды).
Кроме того, этот индекс даже не используется в ORDER BY column_1 DESC.
Это EXPLAIN для ORDER BY column_1 DESC, column_2 ASC, column_3 ASC, то же самое только для ORDER BY column_1 DESC:
Есть ли причина, по которой индекс не используется?
Какой это тип индекса? Сколько столбцов у вас в таблице?
Индекс @ M.Kanarkowski - BTREE, у меня в этой таблице 11 столбцов, но это не имеет значения, если используется индекс?
Ответы 1
Ваше определение индекса должно соответствовать запрашиваемому порядку сортировки или, по крайней мере, смешанному направлению сортировки (то есть либо DESC, ASC, ASC, либо ASC, DESC, DESC). Документы ORDER BY говорят:
If a query mixes ASC and DESC, the optimizer can use an index on the columns if the index also uses corresponding mixed ascending and descending columns. [...] The optimizer can use an index on (key_part1, key_part2) if key_part1 is descending and key_part2 is ascending. It can also use an index on those columns (with a backward scan) if key_part1 is ascending and key_part2 is descending.
См. здесь для получения дополнительной информации.
Таким образом, решением было бы создать индекс (column_1 DESC, column_2, column_3)? Я сделал это, и это все еще не используется. Может быть, это потому, что эти столбцы новые и всего меньше 100 разных строк, поэтому механизм базы данных решил не использовать индекс?
Возможно, вам понадобится ANALYZE TABLE. Но да, оптимизатор сначала учитывает стоимость чтения индекса, а затем чтения строк, на которые индекс указывает Vs. просто читаю таблицу. Если индекс не считается полезным, он не используется. Если таблица (целая таблица?!) состоит всего из нескольких сотен строк, вся таблица, скорее всего, умещается в памяти, и польза от индекса для начала невелика.
Обратите внимание, что определение индексов с сочетанием столбцов ASC и DESC — это новая функция в MySQL 8.0. Объявление ASC и DESC в столбцах индекса игнорируется в более ранних версиях MySQL.
@BillKarwin Тогда в этом проблема, у меня версия 5.6.41.
Да, в версии 5.6 вы можете объявить столбцы индекса как DESC, но они будут храниться только как ASC.
ascиdescимеют значение в индексе. Это не должно, но это так. И, к сожалению, MySQL исторически не поддерживал нисходящие индексы.