Как создать индекс DESC в MySQL?

У меня есть таблица, из которой мне нужно получить строки, упорядоченные по полю в порядке убывания. При запуске запроса EXPLAIN выполните следующие действия:

EXPLAIN SELECT ... FROM table WHERE ... ORDER BY field DESC

Я получаю Using where; Using filesort в столбце Extra. Поэтому я пытаюсь создать индекс DESC:

CREATE INDEX name ON table (field DESC);

Но когда я снова запускаю EXPLAIN, я получаю тот же Using where; Using filesort в столбце Extra, и производительность почти такая же.

Что я делаю неправильно?

Ответ 1

Это одна из этих "функций" MySQL, в которой он молча игнорирует ваш запрос, чтобы что-то сделать, потому что он просто не реализован:

От http://dev.mysql.com/doc/refman/5.5/en/create-index.html

" Спецификация index_col_name может заканчиваться ASC или DESC. Эти ключевые слова разрешены для будущих расширений для указания восходящего или нисходящего значения значения индекса. В настоящее время они анализируются, но игнорируются, значения индекса всегда сохраняются в порядке возрастанияя > "

Ответ 2

Как уже упоминалось, функция не реализована, но могут возникнуть некоторые обходные пути:

Одна возможность - сохранить поле в отрицательном или обратном значении.

Если это число, вы можете сохранить (-n) или (MAXVAL -n), если unsigned

Если это дата или временная метка, некоторые будут выступать за сохранение номера вместо этого и использовать такие функции, как FROM_UNIXTIME()

Конечно, такое изменение не всегда легко выполняется... зависит от существующего кода и т.д.

Ответ 3

MySQL, начиная с версии 8, поддерживает индексы DESC. До этого DESC молча игнорировался. Это не было проблемой для (a) индексов с одним столбцом или (b) для индексов с несколькими столбцами, где все столбцы имели одно направление: либо все ASC, либо все DESC - поскольку индексы двунаправлены.

Но если вам нужен индекс с несколькими столбцами, где разные направления столбцов, например, вы запускаете несколько запросов вроде:

SELECT * from MyTable WHERE ColumnA = 1 ORDER BY ColumnB ASC, ColumnC DESC

вам понадобился следующий индекс: (ColumnA, ColumnB ASC, ColumnC DESC)

Вы можете создать индекс с этими параметрами в MySQL до версии 8, но он был создан на самом деле (ColumnA ASC, ColumnB ASC, ColumnC ASC)

Таким образом, ваш запрос не смог полностью использовать этот индекс - он использовал только столбцы A и B из индекса, используя unindexed (filesort) для столбца C.

Это больше не будет проблемой в MySQL 8.0 и более поздней версии. См. https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html