10.3.13 Descending Indexes — MySQL 8.4

10.3.13 Индексы по убыванию

MySQL поддерживает индексы по убыванию: DESC в определении индекса больше не игнорируется, но приводит к хранению значений ключа в порядке убывания. Раньше индексы можно было сканировать в обратном порядке, но это снижало производительность. Индекс по убыванию можно сканировать в прямом порядке, что более эффективно. Индексы по убыванию также позволяют оптимизатору использовать индексы по нескольким столбцам, когда наиболее эффективный порядок сканирования комбинирует возрастающий порядок для некоторых столбцов и убывающий порядок для других.

Рассмотрим следующее определение таблицы, которое содержит два столбца и четыре определения индексов по двум столбцам для различных комбинаций возрастающих и убывающих индексов по столбцам:

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

Определение таблицы приводит к четырем различным индексам. Оптимизатор может выполнить прямой сканирование индекса для каждого из ORDER BY и не должен использовать filesort операцию:

ORDER BY c1 ASC, c2 ASC    -- optimizer can use idx1
ORDER BY c1 DESC, c2 DESC  -- optimizer can use idx4
ORDER BY c1 ASC, c2 DESC   -- optimizer can use idx2
ORDER BY c1 DESC, c2 ASC   -- optimizer can use idx3

Использование индексов по убыванию подчиняется следующим условиям:

Индексы по убыванию поддерживаются только для хранилища InnoDB, с такими ограничениями:
- Кэширование изменений не поддерживается для вторичного индекса, если индекс содержит столбец ключа по убыванию или если первичный ключ включает столбец индекса по убыванию.
- Парсер SQL InnoDB не использует индексы по убыванию. Для InnoDB полнотекстового поиска это означает, что индекс, необходимый по столбцу FTS_DOC_ID индексируемой таблицы, не может быть определен как индекс по убыванию. Дополнительную информацию см. в Разделе 17.6.2.4, «InnoDB Full-Text Indexes».
Индексы по убыванию поддерживаются для всех типов данных, для которых доступны возрастающие индексы.
Индексы по убыванию поддерживаются для обычных (негенерируемых) и сгенерированных столбцов (как VIRTUAL, так и STORED).
DISTINCT может использовать любой индекс, содержащий соответствующие столбцы, включая части ключа по убыванию.
Индексы, имеющие части ключа по убыванию, не используются для оптимизации MIN()/MAX() запросов, которые вызывают агрегатные функции, но не имеют GROUP BY описания.
Индексы по убыванию поддерживаются для BTREE, но не для HASH индексов. Индексы по убыванию не поддерживаются для FULLTEXT или SPATIAL индексов.

Явно указанные ASC и DESC обозначения для HASH, FULLTEXT и SPATIAL индексов приводят к ошибке.

Вы можете увидеть в столбце Extra вывода EXPLAIN, что оптимизатор может использовать индекс по убыванию, как показано здесь:

mysql> CREATE TABLE t1 (
    -> a INT,
    -> b INT,
    -> INDEX a_desc_b_asc (a DESC, b ASC)
    -> );

mysql> EXPLAIN SELECT * FROM t1 ORDER BY a ASC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: a_desc_b_asc
      key_len: 10
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Backward index scan; Using index

В выводе EXPLAIN FORMAT=TREE использование индекса по убыванию показано добавлением (reverse) после названия индекса, например:

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 ORDER BY a ASC\G
*************************** 1. row ***************************
EXPLAIN: -> Index scan on t1 using a_desc_b_asc (reverse)  (cost=0.35 rows=1)

См. также EXPLAIN Дополнительная информация.