Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Навіщо спадні (DESC) індекси в MySQL 8 і коли вони дають виграш?

До 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 завжди вважається найменшим значенням, і окремого керування його позицією в індексі немає.

Докладніше в документації: Спадні індекси

1

Перевір себе

20 випадкових питань за спробу, після завершення - розбір кожної помилки

Схожі питання