MySql

Реальный пример индексирования нескольких полей с поиском по диапазону в MySQL

Fomenko Alexander

5 дек. 2021 г. • 7 min read

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

Эта статья представляет собой обзор сложного случая, с которым мы столкнулись, где происходит индексации несколько полей, где осуществляется поиск по диапазону значений. Чтобы не затягивать, я объясню только основные концепции индексирования, непосредственно связанные с примером. Для дальнейшего изложения могут потребоваться некоторые базовые знания по индексированию MySQL.

В этой статье поговорим про составной индекс в Mysql, и как правильно его строить в различных случаях. На реальном примере запросе покажем подход к оптимизации с помощью индексов и выбора правильного порядка колонок составного индекса. А так же, разберемся, как работать с индексом по диапазону значений, и какие способы есть обойти ограничение такого индекса.

Проблема

Примеры в этой статье основаны на следующей таблице.

CREATE TABLE `blog_index_example_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `to_be_processed_at` datetime NOT NULL,
  `booking_confirmed_at` datetime DEFAULT NULL,
  `processed_at` datetime DEFAULT NULL,
  `canceled_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ready_to_be_processed_idx_1` (`processed_at`, `canceled_at`, `to_be_processed_at`),
  KEY `ready_to_be_processed_idx_2` (`processed_at`, `canceled_at`, `to_be_processed_at`, `booking_confirmed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Представим, что blog_index_example_table - это большая таблица, содержащая миллионы строк. Мы стремимся сделать эффективный запрос к этой таблице, используя запрос:

SELECT 
  blog_index_example_table.*
FROM 
  blog_index_example_table
WHERE
  booking_confirmed_at IS NOT NULL
  AND to_be_processed_at <= "2020-10-08 09:45:02"
  AND processed_at IS NULL
  AND canceled_at IS NULL;

Для выполнения этого запроса к таблице, содержащей миллионы строк, определенно потребуется индекс. Но как нам структурировать этот индекс, по каким колонкам мы должны его создавать? Первая и очевидная идея для многих из нас, включая меня - это добавить четырех-колоночный составной индекс типа ready_to_be_processed_idx_2.

Этот индекс подходит для предложенного запроса и действительно позволяет выполнить его за несколько миллисекунд. Однако на самом деле этот индекс использует больше ресурсов, чем нам нужно. Позвольте мне объяснить, что с ним не так.

MySQL умна - но вы можете сделать ее умнее

Как многие из вас уже знают, порядок условий в запросе WHERE не имеет никакого значения. MySQL достаточно умна, чтобы найти лучший индекс для использования и соответствующим образом изменить порядок условий оператора WHERE.

Однако MySQL не меняет порядок самого индекса, чтобы выполнить его более эффективно в зависимости от ваших условий. Это стоило бы достаточно много ресурсов, и все равно не всегда было бы возможно правильно предсказать все сценарии. Другими словами: порядок столбцов во время создания индекса имеет значение (большое).

Однако оптимальный порядок столбцов в индексе не всегда одинаков. Это зависит от ваших данных и потребностей. В приведенном выше случае условие processed_at > фильтрует наибольшее количество строк. По этой причине мы выбрали его в качестве первого столбца нашего индекса. К сожалению, остальные три условия очень похожи по диапазону фильтрации. Как же выбрать правильный порядок индекса для остальных?

Что значит: фильтрует наибольшее количество строк?
Представим, что в нашем запросе не 4 условия WHERE, а 4 разных запроса с одним условием:

WHERE booking_confirmed_at IS NOT NULL

WHERE booking_confirmed_at IS NOT NULL

WHERE processed_at IS NULL

WHERE canceled_at IS NULL

И какое из этих условий фильтрует большее количество строк (возвращает меньше всего записей), то такое условие нам и подходит больше всего.

Условия

В нашем запросе осталось три колонки, не попадающие в индекс. Нам нужно добавить эти столбцы в индекс, однако мы не уверены на 100%, в каком порядке их следует выбирать. Хорошим началом будет изучение типа условий. Это одиночные условия или условия диапазона? Я сейчас объясню почему это важно и в чем разница.

Одиночные условия

Давайте сначала рассмотрим условие canceled_at:

WHERE ... canceled_at IS NULL

На первый взгляд может показаться, что IS NULL - это критерий описания множества значений, поскольку NULL означает неопределенное значение. Но, если присмотреться внимательнее, NULL - это одиночное и конкретное значение, поэтому оно поддается подсчету и индексированию.

Другими словами, мы можем перечислить NULL так же как и конкретное значение: 10, 20, 500, 15, 0, NULL, 1, 10923... Исходя из этого, можно сделать вывод, что приведенный выше фрагмент запроса является одиночным условием, поскольку указывает на вполне определенное значение.

Условия диапазона

Теперь посмотрим на оставшиеся условие выборки по диапазону значений:

WHERE ... booking_confirmed_at IS NOT NULL
  ... to_be_processed_at <= "2020-10-08 09:45:02"

Вы заметили разницу? booking_confirmed_at и to_be_processed_at обозначают не единственное значение, а диапазон значений. После применения условия на диапазон значений, последующие поля, учавствующие в составном индексе, теряют эффективность, так как MySQL теперь нужно искать внутри этого подмножества значений, чтобы продолжить любой поиск в индексе.

То есть, после применения поиска по диапазону значений, составной индекс по последующим полям перестает работать. Важно добавлять в составной индекс сначала поля, с одиночным значением, а потом поля с диапазоном значений.

Золотое правило: всегда сначала индексируйте столбцы которые в запросе имеют только одно значение, а затем сосредоточьтесь на условиях диапазона.

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

Таким образом, мы выбрали canceled_at в качестве второго столбца индекса. Что дальше босс?

Условия диапазона это: BETWEEN, <>, !=, IS NOT NULL и т.д.
Распространенным хаком обхода поиска по диапазону индекса - использование оператора IN(...).

Например, вместо запроса: WHERE age BETWEEN 10 AND 12 мы можем выполнить WHERE age IN(10, 11, 12), что позволит оптимизатору использовать остальные колонки индекса.

Условия по нескольких диапазонам

Осталось две колонки, которые мы хотим добавить в наш индекс. По обоим колонкам мы ищем диапазон значений с аналогичным диапазоном фильтрации. Какие из них мы должны использовать и в каком порядке? Инстинктивно вам может показаться, что можно добавить оба условия в любом, каком угодно порядке. Отдавая предпочтение условию, которое отфильтровывает немного больше или делает тот же составной индекс многократно используемым, вы можете оказаться полезным в других сценариях. Это справедливое предположение, но оно верно лишь наполовину.

На данный момент большинство решений, которые мы приняли здесь, приведут к аналогичным результатам. Настоятельно рекомендуется выбирать третий столбец этого индекса на основе условия, которое отфильтровывает больше строк. Также, если это применимо, хорошо построить составной индекс таким образом, чтобы его можно было использовать в других сценариях. Сокращение количества индексов и их размера - это хорошая оптимизация пространства и производительности.

В данном случае у нас осталось несколько условий поиска по диапазону. Как мы уже объясняли, условие диапазона будет иметь свой собственный диапазон значений, которые нужно перебрать перед использованием индекса. Это основная концепция, необходимая для понимания нашего кейса:

Как только вы достигнете первого условия диапазона, любое последующее условие в вашем запросе не будет использовать дальнейшие индексы.

Таким образом, добавление обоих индексов не изменит производительность запроса. Просто потребуется больше дискового пространства для хранения индекса. Вы будете иметь абсолютно одинаковую скорость выполнения запроса с четвертым столбцом или без него, что я продемонстрирую на примере ниже. Лучшим выбором в нашем случае будет индекс с тремя столбцами, выбирая между оставшимися двумя столбцами наиболее подходящий для вашего сценария.

Мы решили двигаться дальше с processed_at, canceled_at, to_be_processed_at. Он лучше подходит для наших сценариев, основываясь на диапазонах строк и потенциальном использовании этого индекса в других запросах.

Пример с книжным каталогом

Представьте себе каталог животных, индексированный по "имени" и "цвету". Его индекс организован примерно так:

Type, Color  - Page
...
Dog,  Black  -  11
Dog,  Blue   -  12
Dog,  Brown  -  13
Duck, Black  -  14
Duck, White  -  15
...
Rat,  Black  -  32
Rat,  Gray   -  33
Rat,  White  -  34
...

Теперь давайте выполним несколько запросов к этому каталогу и посмотрим, как он будет использовать этот индекс страниц:

1. Одиночное условия


SELECT * FROM catalog WHERE type = "Duck" AND color = "Black";
SELECT * FROM catalog WHERE type = "Rat"  AND color = "Black";
SELECT * FROM catalog WHERE type = "Dog"  AND color = "Brown";
SELECT * FROM catalog WHERE type = "Rat"  AND color = "White";

Обратите внимание, что с помощью простого индексного поиска можно найти точные значения страниц для этих запросов. Для пары условий всегда найдется одно значение.

2. Условие поиска по диапазону после одиночного условия

SELECT * FROM catalog WHERE type = "Duck" AND color != "Black";
SELECT * FROM catalog WHERE type = "Rat"  AND color != "Black";
SELECT * FROM catalog WHERE type = "Dog"  AND color != "Brown";
SELECT * FROM catalog WHERE type = "Rat"  AND color != "White";

Обратите внимание, что теперь есть дополнительный шаг для поиска страниц для наших значений. Это все еще очень просто, хотя и немного сложнее, чем в первом случае. Первая часть индекса сужает вашу выборку до одного типа. Затем нужно просто исключить ненужный цвет, и вы получите диапазон страниц, на которых находятся ваши результаты. Вам нужно было проверить несколько дополнительных индексов, но это не страшно. MySQL сделает это достаточно эффективно.

3. Условие поиска чисто по диапазону

SELECT * FROM catalog WHERE type != "Duck" AND color != "Black";
SELECT * FROM catalog WHERE type != "Rat"  AND color != "Black";
SELECT * FROM catalog WHERE type != "Dog"  AND color != "Brown";
SELECT * FROM catalog WHERE type != "Rat"  AND color  = "White";

Теперь все стало сложнее. Ваш диапазон индексов намного больше, чем раньше, и вы не можете использовать индексы должным образом. Для определения пар ключей, соответствующих вашему запросу, требуется почти полное сканирование индекса. Здесь MySQL также испытывает трудности. Чем больше у вас значений, тем больше становятся индексы, а значит, больше данных для сканирования. В итоге это не сильно отличается от сканирования таблицы, т.е. просмотра всей таблицы строка за строкой. Эффективность оказалась под угрозой.

Если посмотреть на наш исходный запрос, то этот пример показывает, почему добавление booking_confirmed_at не изменит поведение индекса и не ускорит запрос.

Теперь мы докажем вышесказанное, используя MySQL EXPLAIN.

Доказательство

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

KEY `ready_to_be_processed_idx_1` (`processed_at`, `canceled_at`, `to_be_processed_at`),
KEY `ready_to_be_processed_idx_2` (`processed_at`, `canceled_at`, `to_be_processed_at`, `booking_confirmed_at`)

Выполнив запрос из начала этой статьи, мы получим следующие результаты:
0_x4iwvambIKcpoNiM

0_c-6XkhiPlP5TagYK

Мы видим, что в обоих случаях использовался один и тот же ключевой диапазон (17). Это означает, что они использовали одну и ту же часть индекса и что дополнительный четвертый столбец был проигнорирован.

Вы можете внести небольшие изменения в свой запрос, которые позволят ему полностью использовать четырехколоночный индекс. Мы изменим условие to_be_processed_at на условие одного значения: to_be_processed_at = "2020-10-08 09:45:02". Таким образом, у нас теперь есть только одно условие диапазона.

Смотрите результаты ниже:
0_JkimfCA6x68exN3D

Вы должны были заметить, что теперь мы используем key_range равный 23, что означает, что теперь используется больший диапазон нашего индекса, как и ожидалось. Хотя это доказывает, что мы можем использовать четвертый индекс, он не подходит для нашего реального случая, поскольку нам нужен запрос с диапазоном дат.

Резюме

Все три возможности, представленные в этой статье, работают для нашего случая - и трехколоночный индекс, и четырехколоночный. Однако использование четырехколоночного индекса требует больше ресурсов: большее количество индексов приводит к увеличению размера таблиц и более медленному обновлению записей, но обеспечивает одинаковое время выполнения запроса. Выбор трехколоночного индекса оптимизирует наш запрос в наибольшей степени: как по производительности, так и по дисковому пространству.

В итоге мы перешли от времени выполнения 6с к 17мс для того же запроса.
Индексы могут показаться простыми на первый взгляд, и их идея довольно проста. Однако, потратив некоторое время на оптимизацию использования индексов, можно добиться значительных улучшений в производительности и занимаемом дисковом пространстве.
Цель данного примера - продемонстрировать некоторые идеи, которые важно учитывать при создании или оптимизации индексов SQL.