Как использовать индексирование для оптимизации SQL-запросов

[GuDron]

Предположим, вы работаете в команде аналитики электронной коммерции в Amazon. Данные, с которыми вы имеете дело, огромны. В нем миллионы строк. Я буду использовать следующую гипотетическую таблицу под названием "продукт", содержащую 12 миллионов продуктов для всех демонстраций. (Интересный факт: Amazon продает более 12 миллионов товаров, не считая книг, медиа, вина и услуг.)

Рис.1 Таблица ‘продукт’ с 12 миллионами строк

Рис.2 4 примерные строки таблицы "продукт"

Давайте начнем с простого запроса.

ВЫБЕРИТЕ COUNT(*)
ИЗ product
, ГДЕ category = ‘электроника’;

Теперь, чтобы выполнить этот запрос, база данных должна просканировать все 12 миллионов строк, чтобы проверить каждую запись на соответствие. Допустим, выполнение этого запроса занимает 4 секунды.
Можете ли вы сделать это быстрее? Да, вы можете.
Как? Путем индексирования.

Индексирование​

Что такое индексирование?​

Позвольте мне интуитивно объяснить всю концепцию индексации. Это называется "индексирование" из-за того, как индекс работает в книге. Если вы читаете книгу по статистике и хотите прочитать о "линейной регрессии", вам не захочется перелистывать сотни страниц одну за другой, чтобы добраться до главы, в которой говорится о "линейной регрессии".
Вместо этого вы откроете страницу индекса, найдете "линейная регрессия" и перейдете непосредственно на страницу.
Это метод, который базы данных используют с помощью индексации. Когда вы создаете индекс, база данных каким-то образом быстро находит данные, которые требуются запросу. Я расскажу об этом "как-нибудь" позже в статье.

Для просмотра ссылки Войди или Зарегистрируйся​

Давайте создадим индекс в таблице "product" и включим в индекс "category".
Синтаксис:

СОЗДАЙТЕ ИНДЕКС [имя_индекса]
ДЛЯ [имя_таблицы] ([имя_колонки]); Запрос:
СОЗДАЙТЕ ИНДЕКС product_category_index
ДЛЯ продукта (категории);

Когда вы выполняете этот запрос, это займет гораздо больше времени, чем обычный запрос. База данных сканирует 12 миллионов строк и создает индекс ‘категории’ с нуля. Допустим, это займет 4 минуты.
Теперь давайте проверим производительность старого запроса с помощью индексации.

ВЫБЕРИТЕ COUNT(*)
ИЗ product
, ГДЕ category = ‘электроника’;

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

ВЫБЕРИТЕ COUNT(*)
ИЗ product
, ГДЕ category = ‘электроника'
И product_subcategory = ‘наушники’;

Этот запрос займет меньше времени, чем обычно, скажем, 600 миллисекунд для этого запроса. База данных может быстро найти все продукты ‘электроника’, используя индекс. И из меньшего набора записей он находит наушники обычным образом.
Теперь давайте изменим порядок условий в предложении ‘WHERE’.

ВЫБЕРИТЕ COUNT(*)
ИЗ product
, ГДЕ product_subcategory = ‘наушники'
и category = ‘электроника’;

Даже когда ‘product_category’ упоминается перед ‘category’, база данных по-прежнему выбирает столбец с индексом, то есть ‘category’, а затем сканирует строки для поиска указанного ‘product_subcategory’ из этого подмножества записей.
Откуда он это знает?

Рис.3 Возможные планы запросов для оптимизатора запросов (изображение автора)
База данных рассматривает все возможные пути выполнения запроса, а затем выбирает наиболее оптимальный путь.
Теперь пришло время для некоторого жаргона базы данных. Каждый из возможных путей называется ‘Для просмотра ссылки Войди или Зарегистрируйся’. По сути, это последовательность шагов, используемых для доступа к данным в Для просмотра ссылки Войди или Зарегистрируйся.
И эта функция СУБД, которая определяет наиболее эффективный способ выполнения данного запроса путем рассмотрения всех возможных планов запросов, называется "Для просмотра ссылки Войди или Зарегистрируйся’.

 

[GuDron]

Многоколоночная индексация​

Теперь давайте рассмотрим Для просмотра ссылки Войди или Зарегистрируйся.

Индекс может быть создан для более чем одного столбца.

СОЗДАЙТЕ ИНДЕКС product_category_product_subcategory_index
ДЛЯ продукта (категория, product_subcategory);

Здесь у нас есть индекс как для ‘category’, так и для ‘product_subcategory’. Здесь важно отметить, что порядок здесь имеет значение. Это похоже на сортировку данных сначала по "категории", а затем по "product_subcategory’.
И запрос становится еще быстрее, используя этот многоколоночный индекс. Допустим, это составляет 60 миллисекунд.

Более того, база данных может иметь более одного индекса.

Когда использовать, а когда не использовать индексацию?​

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

• Индексы требуют хранения
• При добавлении данных в базу данных сначала обновляется исходная таблица, а затем обновляются все индексы этой таблицы

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

Типы индексирования​

Позвольте мне кратко коснуться двух типов индексов базы данных, чтобы дать вам полное представление о теме:
1. Кластеризация
2. Некластерная

Кластеризованные индексы​

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

В этой таблице "product" будет автоматически создан кластеризованный индекс "product_pkey", организованный вокруг первичного ключа ‘product_id’.
Теперь, когда вы выполняете запрос для поиска в таблице по ‘product_id’ (как в приведенном ниже запросе), кластеризованный индекс поможет базе данных выполнять оптимальный поиск и быстрее возвращать результат.

ВЫБЕРИТЕ product_name, category, price
ИЗ product
, ГДЕ product_id = 3;

Вам, должно быть, интересно, как именно это делается?
Индексы используют оптимальный метод поиска, известный как Для просмотра ссылки Войди или Зарегистрируйся.

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

Аналогично, для нашего набора данных с 12 миллионами строк требуется не более 24 поисков вместо 12 миллионов поисков в худшем случае, если используется двоичный поиск. Я думаю, теперь вы знаете силу индексов.

 

[GuDron]

Некластеризованный индекс​

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

• записи столбцов, для которых вы создали индекс
• адреса соответствующей строки (в основной таблице), к которой принадлежит запись столбца

Вы можете увидеть это визуально в левой мини-таблице на рисунке:

Рис.6 Некластеризованный индекс (изображение автора)
Позвольте мне объяснить это с помощью запроса.

СОЗДАЙТЕ ИНДЕКС product_category_index
ДЛЯ продукта (категории); ВЫБЕРИТЕ product_name, category, price
ИЗ product
, ГДЕ category = ‘электроника’;

База данных выполняет 3 шага:

• Во-первых, он переходит к некластеризованному индексу (product_category_index), находит запись столбца, которую вы искали (категория = ‘электроника’), используя двоичный поиск.
• Во-вторых, он ищет адрес соответствующей строки в основной таблице, к которой принадлежит запись столбца.
• Наконец, он переходит к этой строке в основной таблице и извлекает другие значения столбцов в соответствии с требованиями вашего запроса (product_name, price).

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

Заключение​

Это было все о мире индексирования для оптимизации производительности SQL-запросов, особенно когда вы имеете дело с огромными наборами данных. Я напишу больше о других методах оптимизации SQL-запросов очень скоро.