Для чего нужны индексы в sql ускорения запросов select
Суперсила индексов для оптимизации SQL-запросов
Введение
Вы любите SQL и хотите улучшить свои навыки выполнения SQL-запросов? Вы знаете, что индексация — отличный инструмент для оптимизации запросов, но при этом не уверены, что она из себя представляет, с какой целью и как используется?
Добро пожаловать! Вы оказались именно там, где нужно. Сейчас объясним суть индексации на простом и понятном языке.
Начнем с простого запроса:
Для его выполнения база данных (БД) должна просканировать все 12 миллионов строк, чтобы проверить каждую запись на соответствие. Предположим, что время этой операции составляет 4 секунды.
Можно ли быстрее? Конечно. А Как? С помощью индексации.
Индексация
Понятие индексации
Свое название индексация получила по образу и подобию книжного индекса. Если, читая книгу по статистике, вы ищите информацию о “линейной регрессии”, то, вряд ли, станете поочередно перелистывать сотни страниц, чтобы добраться до главы с интересующим вас материалом.
Вы просто откроете страницу индексов, найдете “линейную регрессию” и сразу перейдете на нужную страницу.
Индексация позволяет задействовать данный метод и в работе БД, которая с помощью созданного индекса быстро находит данные по запросу. А как именно это происходит, разберемся далее.
Создание индексов
Давайте создадим индекс для таблицы product и включим в него ‘category’:
Теперь же задействуем индекс и протестируем выполнение самого первого нашего запроса:
Как видно, в этот раз он будет выполняться намного быстрее и, вероятно, займет 400 миллисекунд.
Выполнение этого запроса займет меньше времени, чем обычно — около 600 миллисекунд. С помощью индекса БД быстро найдет все товары ‘electronics’ и из небольшого списка записей выберет ‘headphones’.
Какова же внутренняя суть процесса?
БД анализирует все возможные пути выполнения запроса, выбирая самый оптимальный из них.
Теперь пора познакомиться с некоторыми терминами БД. Каждый возможный путь называется планом выполнения запроса. По сути, это последовательность операций для получения результата SQL-запроса в реляционной системе управления базами данных (СУРБД).
А компонент СУРБД, определяющий наиболее эффективный способ выполнения запроса с учетом анализа всех возможных планов, называется оптимизатором запросов.
Индексация по нескольким столбцам
Теперь рассмотрим индексацию по нескольким столбцам.
Индекс можно создать более чем для одного столбца.
Данный тип индекса еще больше ускорит выполнение запроса, предположительно до 60 миллисекунд.
Более того, БД может включать более одного индекса.
В каких случаях следует применять индексацию?
Индексы ускоряют работу БД, а по мере ее разрастания их эффективность становится очевиднее.
При этом важно помнить о том, что:
В связи с этим, лучше использовать индексы для БД в хранилищах данных, получающих плановые обновления, т. е. в часы наименьшей нагрузки, а не для производственных, которые обновляются постоянно. Это объясняется тем, что при постоянных обновлениях БД индексы обновляться не будут, а следовательно станут бесполезны.
Типы индексов
Здесь мы кратко рассмотрим 2 типа индексов БД для лучшего понимания темы:
1. Кластеризованные индексы
2. Декластеризованные индексы
Кластеризованные индексы
Кластеризованные называется особый индекс, который использует первичный ключ для структуризации данных в таблице. Он не требует явного объявления и создается по умолчанию при определении ключа. Отсортированный же в порядке возрастания первичный ключ по умолчанию применяется в качестве кластеризованного индекса.
Продемонстрируем вышесказанное на простом примере:
Интересно, как же именно это происходит?
Индексы используют оптимальный метод поиска, известный как двоичный поиск.
Двоичный поиск — это эффективный алгоритм поиска записи в сортированном списке. Принцип его работы основан на повторяющемся делении данных пополам и определении того, находится ли искомая запись до или после записи в середине структуры данных. Если значение искомой записи меньше срединного, то поиск продолжается в первой половине, иначе — во второй. Эта процедура повторяется вплоть до нахождения значения. Благодаря данному методу уменьшается число требуемых поисков и, следовательно, ускоряется выполнение запросов.
Следующая таблица отражает соотношение записей данных и максимальное число поисков:
Аналогичным образом для нашего датасета с 12 миллионами строк понадобится не 12 миллионов, а всего лишь 24 поиска — и всё благодаря двоичному поиску. Думаю, теперь вы осознаете супер силу индексов.
Некластеризованный индекс
Теперь узнаем, как применить преимущества индексации к столбцами, отличающимися от первичного ключа. Для этого существуют некластеризованные индексы.
Их примеры уже встречались в начальных разделах статьи во время написания оптимизированных запросов — это индексы, которые требуют явного определения.
Некластеризованный индекс хранится в одном месте, а физические данные таблицы — в другом. Опять нам на ум приходит сравнение со страницей индексов, которая размещается отдельно от содержимого книги. Благодаря этой особенности для каждой таблицы можно создавать более одного некластеризованного индекса, как было показано ранее.
Как именно это происходит?
Предположим, вы уже создали некластеризованный индекс для столбца и теперь пишите запрос для поиска в нем записи. Этот индекс содержит следующее:
Это наглядно отображено в таблице слева на рис.6:
Давайте рассмотрим этот запрос более подробно:
БД совершает 3 шага:
Как видим, работа с некластеризованным индексом предполагает дополнительный шаг, включающий поиск адреса строки и переход к ней в основной таблице. Следовательно запрос с таким индексом выполняется медленнее в отличие от кластеризованного аналога.
Заключение
Итак, мы выяснили, что такое индексы и какую роль они играют в оптимизации выполнения SQL-запросов, особенно при работе с огромными датасетами.
В завершении приведу вам высказывание Тайгера Вудса, лучшего гольфиста всех времен:
“Независимо от того, насколько хорошо вы играете, вы всегда можете стать лучше, и это вдохновляет”.
Оптимизация производительности SQL Server с использованием индексов
Введение
Как известно, индексы повышают производительность аналогично оглавлению или предметному указателю в кнгие. Прочитав несколько статей в интернете и пару глав из книжек, хотелось бы узнать, насколько индексы помогают увеличить скорость выборки данных из SQL Server. Рассмотрим на примере.
Для этого нам понадобятся две таблицы, связанные внешним ключом. В главной таблице будет 10 000 строк, во второй — 1 000 000. В первой таблице будет первичный ключ и 2 столбца с текстом, вторая таблица будет содержать первичный ключ, 2 текстовых поля, 2 числовых поля, дата, вычисляемый столбец и внешний ключ.
Структура базы данных
Вставка данных
Запускаем консольное приложение, жмём «1» и ждём. У меня ушло минут 15-20 на вставку.
Описание результатов
В этом разделе будем рассматривать запросы выборки в проиндексированных и непроиндексированных столбцах.
Один кластеризованный индекс создаётся неявно — это первичный ключ.
Поиск по первичному ключу
Выполним поиск строки по первичному ключу, указывая на конкретный номер строки из десяти тысяч:
Время, затраченное на поиск: 1,523 секунды.
Повторный запрос затратил 0,9 секунды, а третий — 0,1 секунды.
Выполним второй запрос с большой селективностью из этой же таблицы:
Первый запрос затратил 0,3 секунды, второй — 0,26 сек., третий — 0,25 сек.
Теперь выполним аналогичные запросы к таблице, содержащий миллион записей. По конкретному номеру строки:
Время, затраченное на поиск: 1,4 сек., второй запрос показал 1,36 сек.. Далее будет показано среднее время запроса.
Столбцы первичного ключа уже имеют индекс, поэтому повысить производительность скорее всего не получится. Исходя из результатов можно сказать, что повторные запросы с теми же параметрами увеличивают скорость поиска.
Поиск по шаблону
Рассмотрим поиск текста по шаблону с индексированными и непроиндексированными столбцами:
Без индекса такой запрос выполняется за 0,172 секунды из 10 000 строк. С индексом
такой выполняется за 0,112 секунды, а с составным индексом, содержащим поля «name» и «description», запрос занял 0,09 секунды.
Запрос
выполняется за 0,172 секунды без индекса и 0,112 секунды с индексом.
Выполним поиск по шаблону к таблице, содержащей миллион записей.
Этот запрос без индексации занял бы в среднем 1,7 секунды, тогда как с индексом столбца будет потрачено 0,15 секунды.
Поиск по дате
Для начала выполним запрос малой селективности
Без индекса он займёт в среднем 0,7 сек., проиндексированный — 0,22 сек.
Запрос большой селективности (420600 строк)
Без индекса — 19,219 сек, с индексом только поля даты — 9,9 сек.
Запрос по дате к вычисляемому столбцу
Этот запрос без индекса займет 12,2 секунды, тогда как с индексом — 9,75. Важно учесть то, что исходный столбец, содержащий дату, уже был проиндексирован.
Объединение таблиц
При объединении таблиц советуют всегда индексировать столбец, являющийся внешним ключом. В следующем примере мы убедимся, что это на самом деле так.
Такой запрос вернёт 101 строку. Без индекса — 0,422 сек, а с индексом — 0,122 сек. Как мы видим, скорость запроса увеличилась более чем в три раза. Рассмотрим остальные запросы, связанные с объединением.
Но индекс не всегда увеличивает производительность. Например, запрос (420 000 строк)
показал одинаковые результаты без индекса, с индексом только внешнего ключа, внешнего ключа и даты, с составным индексом, одновременно включающим в себя эти два столбца — примерно 6,8 секунд.
Объединение с поиском по шаблону
Выполним объединение с поиском по шаблону среди миллиона записей:
Без индексов запрос займёт 0,766 секунды и вернёт 5 строк.
С индексом только внешнего ключа — 0,4 сек.;
С индексом только текстового поля — 0,125 сек.;
С индексом внешнего ключа и индексом текстового поля — 0,109 сек.;
С составным индексом, содержащим внешний ключ и текстовое поле — 0,35 сек..
Сравнение результатов
Рассмотрим результаты в виде графиков. Вертикальная ось показывает затраченное время (а не скорость!).
1. Выборка по первичному ключу. Так как уже есть кластеризованный индекс, рассмотрим большую и малую селективную выборку из одной таблицы:
Из этого графика видно, что выборка большого количества данных обрабатывается быстрее.
2. Поиск по первичному ключу с объединением таблиц. Следующий график показывает, что индексирование внешнего ключа ускоряет операцию поиска более чем в три раза:
3. Поиск по шаблону текста. Из графика видно, что производительность во много раз вырастает, если использовать индекс при большом объёме данных:
4. Поиск по дате. Индексы значительно увеличивают производительность как для малой выборки, так и большой в пределах одной таблицы:
Объединение таблиц и поиск по дате показал одинаковые результаты как с индексами, так и без них.
5. Поиск по вычисляемому столбцу. Индекс также уменьшает время поиска.
6. Поиск по шаблону с объединением. Индекс текстового поля и оба индекса значительно увеличивают производительность:
Подведём итоги
Итак, мы видим, что индексирование значительно повышает SELECT запросы, однако индексы занимают дополнительную память и периодически должны быть перестроены. Оптимальное решение сложно найти, поэтому разработчики и администраторы баз данных тратят много времени на поиск золотой середины, к тому же индексы необходимо реорганизовывать. Но всё же, если у вас большое количество данных, то ответ, использовать индексы или нет, очевиден.
Оптимизация SQL запросов для MS SQL Server с помощью индексов
Я тут пообещал поделиться опытом оптимизации SQL запросов. Мне достаточно много приходилось работать над оптимизацией и на работе не раз просили рассказать, как я оптимизирую запросы. Тут нет какого-то секрета и, хотя я пытался поделиться, это все же такой процесс, в котором мастерство приходит с опытом.
На блоге я уже несколько раз делился некоторыми отдельными случаями, но пришла пора все же более подробно рассмотреть эту тему.
Это вводная часть, где я расскажу в статье и покажу в видео (его можно найти в конце этой статьи) базовые вещи о индексах и статистике.
О индексах и статистике можно писать очень много и возможно будет еще статья и видео. А это такой вводный курс, чтобы показать базу.
Когда я узнаю о том, что на сервере есть медленно выполняющийся запрос, первое на что я смотрю – статистику io. Это такое слабое звено, позволяет быстро определить легкие проблемные места SQL запросов.
В SQL Server есть несколько вариантов получения данных о том, как выполняется запрос – но именно статистика получается очень легко и тут же показывает проблемные места в читабельном виде.
Второй популярный способ – смотреть на план выполнения, но в случае с большими и сложными запросами план выполнения может быть не таким уж и наглядным, а по статистике чтений можно тут же увидеть, на какую таблицу идет нагрузка, потом посмотреть по каким полям в запросе происходит фильтрация данной таблицы и убедится, что по этим полям есть индекс.
Давайте займемся тем, чем любят заниматься программисты по ночам – посмотрим на код. Итак, для вводного курса рассмотрим простой пример с запросом:
У меня в тестовой базе данных почти 200 тысяч записей и поиск по ней происходит мгновенно. Если посмотреть на время выполнения, то будет ноль секунд, что очень хорошо. Но давайте включим отображение статистики, и посмотрим на нее. Я всегда включаю статистику io и время time:
Теперь после выполнения SQL запроса в SQL Management Studio внизу окна будет появляться не только результат, но и на закладке Messages будет показана статистика выполнения:
В моем случае статистика выглядела так:
Первые четыре строчки – это время компиляции запроса и для такого простого особо проблем не должно возникнуть, а я уже выполнял запрос, поэтому и 0 секунд.
После этого идет статистика выполнения и тут нужно смотреть на количество сканирований и количество чтений (logical reads, physical reads и др). У нас сейчас запрос простой, который решается одним сканированием. Судя по количеству чтений, сканировалась абсолютно вся таблица.
Казалось бы, да и черт с ним, что сканируется вся таблица, если запрос выполняется так быстро – ноль секунд. И если запрос выполняется только один раз, то можно и забыть и забить. Но если сразу тысяча человек будет выполнять этот запрос и искать по разной фамилии? Даже при такой статистике нагрузка на базу данных будет серьезная, а если в базе будет миллион записей, то сервер может серьезно затормозить.
Для оптимизации нужно создать индекс, который ускоряет поиск:
Более подробно о создании индексов можно почитать здесь: Индексы в MS SQL Server и еще немного интересной информации о индексах здесь: опции индексов.
Здесь я только скажу, что при создании индексов на таблицу, где много данных и с большим количеством выполняемых запросов может стать проблемой. Создание индекса по умолчанию потребует блокировки, что может стать препятствием. Индекс может не создаваться, потому что данные заблокированы или сайт может лечь, если индекс будет долго создаваться. Чтобы этого не произошло, нужно добавлять опцию: (online = on)
Снова выполняем запрос и смотрим на статистику:
Обратите внимание на количество чтений. Вот теперь даже если сотня тысяч пользователей будет выполнять такой запрос даже на многомиллионной базе, сервер скорей всего выдержит.
Попробуем взглянуть на план выполнения – графическое представление того, как сервер реально выполнял запрос. Для этого включаем опцию отображения плана – в меню Query выбираем Include Actual Execution Plan (или нажимаем Ctrl+M). Если теперь выполнить запрос, то появится еще одна вкладка – Execution Plan:
Здесь у нас две ветки и читать их нужно справа на лево. Самый правый блок – это то, с чего началось выполнение – Index Seak по индексу IX_Member_WSEmail. Наш простой запрос ищет данные по колонке WSEmail и эта колонка есть в индексе IX_Member_WSEmail, поэтому имеет смысл использовать его. И как мы уже увидели после создания индекса, результат как говориться на io. В индексе находятся индексируемые колонки и первичный ключ. Это все, что узнает сервер, когда находит строку по индексу. Но наш запрос выводит совершенно все колонки и чтобы найти оставшиеся данные, серверу приходится по первичному ключу находить их. Этот процесс быстрый – Key Lookup, потому что это первичный ключ, но он все же отнимает немного времени. И скоро мы увидим сколько. Получается, что серверу необходимо выполнить как бы две операции – поиск по индексу основного ключа, а потом по этому ключу найти данные колонок. А если выводить на экран только колонки WSEmail и первичный ключ MemberID? Эти данные уже есть индексе и второй Key Lookup не понадобиться. Посмотрим, как это будет выглядеть в статистике:
Статистика падает с 7 чтений до 3:
А план выполнения начинает выглядеть идеально просто:
Допустим, нам необходимо вывести на экран имя человека, в моем случае это колонка WSFirstName:
Теперь запросу нужно будет снова делать два поиска – чтобы найти первичный ключи, а потом по нему найти реальную строку, чтобы выцепить имя. И это в принципе не страшно, потому что поиск по первичному ключу занял всего 4 операции чтения, но что, если выводиться 1000 строк? Тогда уже будет 4000 операций. А если запрос у нас не такой простой и в нем потом еще есть left join на какую-то другую таблицу с именами, где, по имени храниться судьба человека (такой гороскоп по имени).
Можно создать индекс, который будет индексировать по email и по имени:
И хотя запрос фильтрует данные только по e-mail, этот индекс все же будет работать и позволит нам быстро найти данные, и в индексе будет уже имя и поэтому второй поиск уже не нужен будет. Круто, но не совсем эффективно. Имя меняется не часто и если мы по нему реально не ищем, то по нему индексировать смысла нет, но есть возможность сказать серверу, чтобы он хранил вместе с индексом еще и колонку WSFirstName, для этого есть такая фишка, как include:
Теперь данные индексируются только по колонке WSEmail, что нам и нужно, но вместе с индексом храниться не только первичный ключ, но и имя.
Конечно же, теперь при обновлении данных в колонке WSFirstName придется обновлять и индекс, но так как мы не индексируем по этой колонке, то к перестроению данных это не приведет. Такая операция будет очень быстрой.
Еще один пример, который может выиграть от такого индекса:
Мы выводим все колонки и от второго поиска по первичному ключу всех данных не убежать. Все колонки включать в индекс смысла нет, потому что это превратить его практически в первичный, просто не кластерный. Но.. Когда сервер отфильтрует данные по WSEmail по первому индексу и найдет допустим 1000 строк, он может тут же сократить эти данные и проверить имя и результат сократиться до (допустим) 100 строк. То есть Key Lookup может выполняться только 100 раз. Если колонка WSFirstName не включена в индекс, то эту операцию придется уже делать 1000 раз.
Чтобы индексы работали наиболее эффективно, тип данных значения и колонки должно совпадать. Как видите, в моем случае я просто передаю строку в одинарной кавычки, как varchar, потому что колонка имеет тип именно varchar. Если попробовать передать nvarchar:
Теперь строка e-mail адреса передается в качестве nvarchar и это серьезно ударит по производительности. Изменился только тип строки, которую мы сравниваем, а посмотрите как обрушилась статистика:
Когда я работал над сайтом регистрации продуктов для Sony, то допустил такую ошибку. На главной странице сайте есть автокомплитер, где пользователь может ввести код товара. Пока пользователь вводит, на заднем плане происходит поиск и когда я запустил этот сайт, то он нереально затормозил, хотя этот сайт не такой уж и популярный и по посещаемости самый слабый из всех, что я делал для Sony. Начали исследовать, а оказалось, что для этого проекта я перешел на Dapper, который по умолчанию все переменные делал nvarchar, а в базе данных у нас все было просто varchar (сайт только для США). В результате, даже небольшой нагрузки на сайт хватало для серьезного падения производительности. Пришлось хакать Dapper, чтобы он не создавал переменные Unicode, а делал их простыми varchar
Если тип колонки и искомого значения совпадают, то будет использоваться Index Seek. Если не совпадают, то Index Scan – сканирование по индексу. Что используется для поиска можно увидеть в Execution Plan
Серверу придется просканировать весь индекс. За счет того, что он занимает меньше места на диске, то поиск будет быстрее, чем сканирование по данным, но все же на много медленнее, чем могло быть при правильном использовании параметров.
Если ваш фреймворк косячит и передает неверно параметры, а у вас нет доступа к исходникам, чтобы исправить, то можно использовать вот такой финт ушами:
Здесь я с помощью cast привожу email переменную к правильному типу, чтобы он совпадал с типом данных колонки.
На этом наверно остановимся, для первого вводного курса этого должно быть достаточно. Если эта статья оказались интересна, рекомендую все же посмотреть еще и видео. Лайкни его, мне приятно видеть, если моя работа полезна и интересна другим. Подпишись на канал. Если будет востребованность, будет смысл продолжать эту тему.
Если будет продолжение, то со статистикой мы еще столкнемся не раз.
MySQL индексы и ускорение выборки данных
Создаётся простая таблица данных:
Как видно из этого примера, в колонке id создаются уникальные ключи (за счёт AUTO_INCREMENT ). Поле user_id также содержит уникальные значения идентификаторов пользователей, генерируемые скриптом (использую mt_rand) во время регистрации в личном кабинете.
Для выборки делаю следующий запрос:
Задумался об использовании индексов в своей таблице данных, для ускорения выборки. В документации написано:
Наличие индекса может существенно повысить скорость выполнения некоторых запросов и сократить время поиска необходимых данных за счет физического или логического их упорядочивания.
Нужно ли создавать индексы для поля user_id для ускорения выборки из миллионов записей, если все значения идентификаторов в этом поле и так уникальны?
Если всё таки нужно, то какой индекс создать: кластерный или не кластерный (не совсем понимаю различия между ними)?
Условие WHERE (в моём примере сверху) заставляет СУБД перебирать все записи в таблице? Или же СУБД сразу обращается конкретно только к тем записям, которые удовлетворяют условиям поиска ( user_id = 28572 ), не затрагивая при этом остальные записи?
4 ответа 4
Нужно ли создавать индексы для поля user_id для ускорения выборки из миллионов записей, если все значения идентификаторов в этом поле и так уникальны?
Если всё таки нужно, то какой индекс создать: кластерный или не кластерный(не совсем понимаю различия между ними)?
Кластерный индекс не нужен, пока Вы явно не осознаете его необходимость. Сюрприз: в InnoDB первичный ключ всегда кластерный. И он у Вас уже есть. Не думайте пока об этом.
Условие WHERE заставляет СУБД перебирать все записи в таблице?
Добавлю к ответу @Mirdin
Класерный индекс физически упорядочивает таблицу по индексу. Самый быстрый (для поиска). Очевидно что он может быть только один. PK всегда кластерный индекс по умолчанию.
Но правильно да, сделать этот столбец:
В общем как вы наверное поняли однозначного ответа нет. Действуйте последовательно, добавьте один индекс и посмотрите на результат. Вполне возможно что одного индекса будет достаточно.