Введение
Я полагаю, что вы используете SQL и хотите улучшить свои навыки выполнения запросов. И вы, наверное, слышали, что индексирование отлично подходит для оптимизации запросов, но вы не уверены в том, что именно это такое, почему оно используется и как его использовать.
Добро пожаловать! Вы находитесь в том месте, где должны быть. Я объясню как это легко для понимания и обещаю, что вы будите рады этому научиться .
Предположим, вы работаете в команде Amazon по аналитике электронной коммерции. Данные, с которыми вы имеете дело, огромны. В них миллионы строк. Я буду использовать следующую гипотетическую таблицу под названием «продукт», содержащую 12 миллионов продуктов для всех демонстраций. (Интересный факт: Amazon продает более 12 миллионов товаров, не считая книг, медиа, вина и услуг.)
Начнем с простого запроса.
SELECT COUNT(*) FROM product WHERE category = ‘electronics’;
Теперь, чтобы выполнить этот запрос, база данных должна просканировать все 12 миллионов строк, чтобы проверить каждую запись на соответствие. Допустим, выполнение этого запроса занимает 4 секунды.
Вы можете сделать это быстрее? Да!
Как? Путем индексации.
Индексирование Что такое индексирование?
Позвольте мне объяснить всю концепцию индексации интуитивно. Он назван «индексированием» из-за того, что работает как содержание в книге. Если вы читаете книгу по статистике и хотите прочитать о «линейной регрессии», вам не захочется перелистывать сотни страниц одну за другой, чтобы перейти к главе, в которой говорится о «линейной регрессии». Вместо этого вы откроете страницу содержания, поищите «линейную регрессию» и сразу перейдете на страницу. Это метод, который базы данных используют посредством индексации. Когда вы создаете индекс, база данных каким-то образом быстро находит данные, которые нужны запросу. Я расскажу об этом «как-нибудь» позже в статье.
Как создавать индексы
Давайте создадим индекс для таблицы «продукт» и включим в него «категорию».
Синтаксис:
CREATE INDEX [index_name] ON product ([column_name]);
Запрос:
CREATE INDEX product_category_index ON product (category);
Когда вы выполните этот запрос, это займет намного больше времени, чем обычный запрос. База данных сканирует 12 миллионов строк и создает индекс «категории» с нуля. Допустим, это займет 4 минуты. Теперь давайте проверим производительность старого запроса с индексированием.
SELECT COUNT(*) FROM product WHERE category = ‘electronics’;
Вы увидите, что на этот раз запрос будет выполняться намного быстрее. На этот раз это, вероятно, займет всего 400 миллисекунд.
Даже запросы, которые выходят за рамки использования "категории" в качестве условия, выиграют от индексации по "категории". Давайте посмотрим, например.
SELECT COUNT(*) FROM product WHERE category = ‘electronics’ AND product_subcategory = ‘headphone’;
Этот запрос займет меньше времени, чем обычно, скажем, 600 миллисекунд для этого запроса. В базе данных можно быстро найти всю "электронику" с помощью индекса. А из меньшего набора записей наушники находит нормально. Теперь давайте изменим порядок условий в предложении "WHERE".
SELECT COUNT(*) FROM product WHERE product_subcategory =‘headphone’ AND category = ‘electronics’;
Даже когда «product_category» упоминается перед «категорией», база данных по-прежнему выбирает столбец с индексом, то есть «category», а затем сканирует строки для поиска указанной «product_subcategory» из этого подмножества записей. Откуда он это знает?
База данных рассматривает все возможные пути выполнения запроса, а затем выбирает наиболее оптимальный путь. Пришло время поговорить с базой данных на жаргоне. Каждый из возможных путей называется «Планом запроса». По сути, это последовательность шагов, используемых для доступа к данным в системе управления реляционными базами данных (RDBMS) SQL. И эта функция СУБД, которая определяет наиболее эффективный способ выполнения данного запроса с учетом всех возможных планов запросов, называется «Оптимизатором запросов». Индексирование по нескольким столбцам Теперь давайте рассмотрим индексирование по нескольким столбцам.
Индекс можно создать более чем для одного столбца.
CREATE INDEX product_category_product_subcategory_index
ON product (category, product_subcategory);
Здесь у нас есть указатель как для «category», так и «product_subcategory». Здесь важно отметить, что порядок здесь имеет значение. Это похоже на сортировку данных сначала по «категории», а затем по «product_subcategory». И запрос становится еще быстрее, используя этот многостолбцовый индекс. Скажем, до 60 миллисекунд.
Более того, в базе данных может быть более одного индекса.
Когда использовать, а когда не использовать индексирование?
Индексы ускоряют работу базы данных. А по мере увеличения базы данных индексы становятся еще более полезными.
Но следует помнить о двух важных вещах:
- Индексы требуют места в памяти для хранения
- Когда вы добавляете данные в базу данных, сначала обновляется исходная таблица, а затем обновляются все индексы этой таблицы.
Таким образом, полезно использовать индексы для баз данных в базах данных, которые обновляют новые данные по расписанию (в непиковые часы), а не для производственных баз данных, которые будут получать новые обновления все время. Это связано с тем, что, если база данных постоянно получает обновления, индексы не будут обновляться и, следовательно, останутся непригодными для использования.
Типы индексации
Позвольте мне кратко коснуться двух типов индексов баз данных, чтобы дать вам полное представление об этой теме:
1. Кластерные
2. Некластеризованные
Кластерные индексы
Кластерный индекс - это уникальный индекс таблицы, который использует первичный ключ для организации данных в таблице. Кластерный индекс необязательно объявлять явно, он создается по умолчанию при определении первичного ключа. Первичный ключ, отсортированный в порядке возрастания, по умолчанию используется в качестве кластеризованного индекса.
Позвольте мне продемонстрировать это на простом примере.
Для этой таблицы product будет автоматически создан кластерный индекс product_pkey, организованный вокруг первичного ключа product_id. Теперь, когда вы запускаете запрос для поиска в таблице по "product_id", кластерный индекс поможет базе данных выполнять оптимальный поиск и быстрее возвращать результат. Вам должно быть интересно, как именно он это делает? В индексах используется оптимальный метод поиска, известный как двоичный поиск.
Двоичный поиск - это эффективный алгоритм поиска записи из отсортированного списка записей. Он работает, многократно разделяя данные пополам и проверяя, находится ли запись, которую вы ищете с помощью вашего запроса, до или после записи в середине данных. Если значение вашей поисковой записи меньше, чем значение в середине, это сужает поиск до нижней половины, в противном случае он сужает поиск до верхней половины. Он делает это неоднократно, пока значение не будет найдено. Этот метод уменьшает количество требуемых поисков и, следовательно, ускоряет выполнение запросов. Следующая таблица помогает понять влияние двоичного поиска на количество поисков:
Точно так же для нашего набора данных с 12 миллионами строк требуется максимум 24 поиска вместо 12 миллионов поисков в худшем случае, если используется двоичный поиск. Думаю, теперь вы знаете силу индексов.
Некластеризованный индекс
Следующая таблица помогает понять влияние двоичного поиска на количество поисков: Рис.5 Сложность двоичного поиска Точно так же для нашего набора данных с 12 миллионами строк требуется максимум 24 поиска вместо 12 миллионов поисков в худшем случае, если используется двоичный поиск. Думаю, теперь вы знаете силу индексов. Некластеризованный индекс Теперь вопрос в том, как расширить возможности индексации на столбцы, отличные от первичного ключа. Ответ - через некластеризованные индексы. Все запросы, которые мы научились писать в начале статьи для оптимизации производительности запросов, использовали некластеризованные индексы, индексы, которые должны быть явно определены. Некластеризованный индекс хранится в одном месте, а физические данные в таблице хранятся в другом месте. Это как индексная страница книги, о которой мы говорили ранее. Индексная страница книги находится в одном месте, а содержание книги - в другом. Это позволяет использовать более одного некластеризованного индекса для каждой таблицы, как мы видели ранее. И как именно это делается? Предположим, вы пишете запрос, который включает поиск записи в столбце, для которого вы уже создали некластеризованный индекс. Некластеризованный индекс по своей сути содержит следующее:
- записи столбца, для которых вы создали индекс
- адреса соответствующей строки (в основной таблице), которой принадлежит запись столбца
Визуально это можно увидеть в левой мини-таблице на рисунке:
Позвольте мне объяснить это с помощью запроса.
CREATE INDEX product_category_index ON product (category);
SELECT product_name, category, price FROM product WHERE category = ‘electronics’;
- Во-первых, просиходит переходи к некластеризованному индексу (product_category_index), находит запись столбца, которую вы искали (category = «electronics»), используя двоичный поиск.
- Во-вторых, он ищет адрес соответствующей строки в основной таблице, которой принадлежит запись столбца.
- Наконец, он переходит к этой строке в основной таблице и извлекает значения других столбцов в соответствии с требованиями вашего запроса (product_name, price).
Так же есть дополнительный шаг (нахождение адреса и переход к этой строке в основной таблице), связанный с работой некластеризованного индекса, и, следовательно, он медленнее, чем кластерный индекс.
Вывод
Это все о мире индексации для оптимизации производительности SQL-запросов, особенно когда вы имеете дело с огромными наборами данных. Я очень скоро напишу больше о других методах оптимизации SQL-запросов. Я надеюсь, что смог выполнить свое обещание сделать статью приятной и простой для понимания. И я надеюсь, вы сочли это полезным. В заключение величайший игрок в гольф всех времен Тайгер Вудс сказал:
«Независимо от того, насколько хорошо вы играете, вы всегда можете стать лучше».
