SQL-индексы за 10 минут

Канал Listen IT рассказал о суперсиле индексов для оптимизации SQL-запросов. Индексация позволяет ускорить выполнение запросов к базам данных. 

Вот, о чём идёт речь в видео:

1. Автор статьи о суперсиле индексов для оптимизации SQL-запросов - Дженни Ви, статья опубликована на сайте medium.com.
2. Индексация - это инструмент для оптимизации SQL-запросов, ускоряющий поиск данных в больших объемах информации.
   
3. Индексация сравнивается с книжным индексом, который позволяет быстро находить нужную информацию.
   
4. Создание индекса увеличивает скорость выполнения запросов, например, с 4 секунд до 400 миллисекунд.
   
5. Индексация может применяться как для одного, так и для нескольких столбцов, что ускоряет выполнение сложных запросов.
   
6. Порядок столбцов в многостолбцовом индексе важен, так как данные сначала сортируются по первому столбцу индекса.
   
7. Индексы требуют дополнительного места для хранения и обновления, что может замедлить работу при частых изменениях в базе данных.
   
8. Существуют кластеризованные (используют первичный ключ) и некластеризованные (для неключевых столбцов) индексы.
   
9. Двоичный поиск - это метод, используемый индексами для быстрого нахождения данных.
   
10. Индексы особенно эффективны в больших базах данных и в системах, где обновления происходят в плановое время.
    
11. Индексы не рекомендуется использовать в небольших таблицах, таблицах с частыми массовыми операциями и столбцах с большим количеством null значений.
    

Ниже — транскрибация ролика.

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

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

В этой статье про индексы мы будем говорить про некоторые простейшие операторы SQL, так что если ты их еще не знаешь, то смотри статью про SQL. Ну и начнем с простого запроса. Select count звездочка from product where category равно electronics. То есть посчитаем количество строк в таблице product, у которых категория равно electronics. Для его выполнения база данных должна просканировать все 12 миллионов строк, чтобы проверить каждую запись на соответствие.

Какая же там категория electronics или какая-то другая. Предположим, что время этой операции составляет 4 секунды. А можно ли быстрее? Конечно можно, и для этого есть индексация. Свое название индексация получила по образу и подобию книжного индекса.

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

Это будет CreateIndex, ну, назовем его ProductCategoryIndex. Он Product, да, таблица Product. В скобочках Category. В отличие от обычного запроса, наш запрос с индексом займет гораздо больше времени. База данных просканирует 12 миллионов строк и с нуля создаст индекс Category. Допустим, на это уйдет 4 минуты. Теперь давайте задействуем индекс и протестируем выполнение нашего первого запроса, где мы хотели понять, сколько у нас строк с категорией Electronics.

В этот раз он будет выполняться намного быстрее и, вероятно, займет где-то 400 миллисекунд. Даже расширенные запросы, у которых в условии будет не только категории, станут более эффективными благодаря созданному индексу. Рассмотрим пример, как на экране.

Select count звездочка from product where category равно electronics and product subcategory равно headphone. То есть ищем количество строк в таблице product, у которых категория равно electronics и product subcategory равно headphone. Выполнение этого запроса займет меньше времени, чем обычно.

Около 600 миллиметров миллисекунд. С помощью индекса база данных довольно быстро найдет все товары Electronics, и уже из небольшого списка записи сможет выбрать Headphones. А давайте теперь изменим порядок условий в пункте Where.

Смотрим на экран, то есть теперь у нас where сначала не категория electronics, а сначала product subcategory headphone, а потом уже категория electronics. Несмотря на то, что мы сначала подкатегорию указали, а потом категорию, база данных все равно сначала выберет столбец с индексом, то есть категория, после чего она просканирует строки в поиске указанной подкатегории из числа уже имеющихся записей. Но можно теперь и углубиться, какова же внутренняя суть процесса. База данных анализирует все возможные пути выполнения запроса, выбирая самый оптимальный из них. И тут нам понадобятся некоторые термины баз данных. Каждый возможный путь называется планом выполнения запроса.

По сути, это последовательность операции для получения результата SQL-запроса в реляционной системе управления базами данных. Сурбд. А компонент сурбд, который определяет наиболее эффективный способ выполнения запроса с учетом анализа всех возможных планов, называется оптимизатором запросов. Что в принципе логично. Теперь давайте рассмотрим индексацию по нескольким столбцам. Да, индекс можно создавать более чем для одного столбца, вот посмотрите пример.

Создадим индекс для нашей таблички product, но уже по колонкам category и product subcategory. Теперь для обоих столбцов у нас есть индекс. Обратите внимание, что здесь уже важна очередность.

Сначала данные сортируются в категории, а после чего в Product Subcategory. Данный тип индекса еще больше ускорит выполнение запроса, предположительно до 60 миллисекунд. Ну и более того, база данных может включать более одного индекса. В принципе понятно, но давайте ближе к практике.

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

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

Если не понятно, то давайте посмотрим на простом примере. Посмотрите на экран, у нас есть табличка product, в которой у нас есть первичный ключ productId. И для этой таблицы будет это автоматически создан кластеризованный индекс ProductPK, сформированный вокруг первичного ключа ProductID.

В этом случае, при выполнении в таблице поискового запроса по ProductID, как на экране, посмотрите, запрос Select, кластеризованный индекс поможет базе данных оптимально справиться с задачей и быстрее вернуть ответ. Но интересно, как же именно это происходит? Индексы используют оптимальный метод поиска, известный как двоичный поиск. Двоичный поиск – это такой эффективный алгоритм, итем поиска записи в сортированном списке. Принцип его работы довольно простой. Это повторяющееся деление данных пополам и определение того, находится ли запись, которая нам нужна до или после записи в середине структуры. Если значение искомой записи меньше срединного, то поиск продолжается в первой половине, иначе во второй. Эта процедура повторяется вплоть до нахождения значения. Благодаря данному методу уменьшается число требуемых поисков и, следовательно, ускоряется выполнение запросов. Таблица на экране отражает соотношение записи данных и максимальное число поисков. То есть, как мы видим из таблички, по последней строке из миллиона записей мы сможем найти ту, которая нам нужна всего за 20 попыток, но это максимум. Максимум за 20.

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

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

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

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

Наглядно это можно посмотреть на табличке на экране. Создадим индекс product category index для нашей таблички product из столбца category. И потом сделаем select трех атрибутов из таблицы product name category и price, где category равно electronics. База данных совершает три шага.

Во-первых, она переходит по некластеризованному индексу productCategoryIndex и методом двоичного поиска находит искомую запись столбца category="electronics". Во-вторых, в основной таблице она ищет адреса соответствующей строки, в которой находится запись столбца. И в-третьих, она переходит к этой строке в основной таблице и выбирает другие значения столбца в соответствии с требованиями запроса, то есть нам нужно будет еще productName и price. Как мы видим, работа с кластеризованным индексом предполагает дополнительный шаг, который включает поиск адреса строки и переход к ней в основной таблице. Следовательно, запрос с таким индексом выполняется медленнее, в отличие от кластеризованного аналога. Индекс также можно, кстати, удалить с помощью команды dropindex.

Просто пишем dropindex и название индекса, который мы задали при создании индекса. А вот еще несколько примеров, когда лучше индекса не использовать. Понятно, что бывают разные ситуации, но это это такое обобщенное видение.

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