До MySQL 8.0 синтаксис INDEX (a DESC) приймався, але ігнорувався - індекс завжди будувався за зростанням. З 8.0 спадні індекси справжні: значення фізично впорядковані за спаданням.
Коли це потрібно - сортування в різних напрямках:
SELECT * FROM products
WHERE category_id = 3
ORDER BY rating DESC, price ASC
LIMIT 20;
- індекс
(category_id, rating, price): читаючи вперед, отримуємоrating ASC, price ASC; назад -rating DESC, price DESC. Потрібної комбінаціїDESC, ASCнемає - будеUsing filesort; - індекс
(category_id, rating DESC, price ASC)віддає рядки рівно в потрібному порядку, запит читає 20 записів і зупиняється.
Коли спадний індекс НЕ потрібен. Сортування в одному напрямку будь-яким індексом обслуговується і вперед, і назад:
-- індекс (user_id, created_at)
WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20
-- EXPLAIN: Extra = Backward index scan; Using index condition...
Зворотне сканування працює, але в InnoDB трохи повільніше за пряме: сторінки зв'язані у двонапрямлений список, проте всередині сторінки записи оптимізовані для руху вперед. На гарячих запитах «останні N записів» спадний індекс (user_id, created_at DESC) дає невеликий, але вимірний виграш - і прибирає Backward index scan з плану.
Інші ситуації:
MIN()/MAX()за колонкою індексу оптимізуються в обох напрямках;GROUP BYзі спадними частинами індексу підтримується;- спадні індекси підтримує лише InnoDB, і не для
FULLTEXT,SPATIALчи хеш-індексів.
У Laravel-міграції напрям колонки в індексі задається через сирий вираз:
$table->rawIndex('category_id, rating DESC, price', 'products_category_rating_price_idx');
Практичне правило: проєктувати індекс під найчастіше сортування. Якщо інтерфейс дозволяє сортувати таблицю за будь-якою колонкою в будь-якому напрямку, індекс під кожну комбінацію не створюють - обмежують варіанти сортування або приймають filesort на рідкісних комбінаціях.
PostgreSQL має спадні індекси давно, плюс NULLS FIRST/LAST. У MySQL NULL завжди вважається найменшим значенням, і окремого керування його позицією в індексі немає.
