Одним из ключевых показателей производительности базы данных является скорость выполнения запросов. При разработке и поддержке высоконагружаемых приложений данный фактор принимает первостепенное значение. Когда в таблицах базы данных содержатся десятки миллионов записей, которые востребованы каждую секунду, обеспечение быстрого выполнения запросов к базе становится весьма тяжелой задачей для разработчика. Данную проблему невозможно решить только наращиваем характеристик серверного оборудования.
В качестве наиболее эффективных решений для повышения производительности базы данных отмечают следующие:
1) Создание репликаций базы данных
2) Шардинг базы данных
3) Индексирование таблиц базы данных с целью оптимизации запросов
В этой статье будет рассмотрен последний способ, как один из наиболее эффективных и простых способов. Также индексирование – это первый шаг, на который идет разработчик при оптимизации базы данных.
Итак, разберемся, что такое индекс, и какие типы индексов мы можем создавать при разработке баз данных.
Индексы создаются для таблицы и, по сути, являются копией этой таблицы. Различие состоит в том, что индекс может включать не все столбцы исходной таблицы, а лишь те которые указал разработчик. Рассмотрим пример. У нас есть таблица Stuffs, которая содержит 10 колонок и упорядоченный индекс IDX_StuffsSalary с ключом Salary, который содержит только два столбца из таблицы Stuffs:
| Таблица Staffs | |
| Id | Int |
| Name | Nvarchar(50) |
| Surname | Nvarchar(50) |
| Salary | Int |
| Address | Nvarchar(50) |
| Telephone | Nvarchar(50) |
| Job | Nvarchar(50) |
| Photo | image |
| Индекс IDX_StaffsSalary | |
| Salary | Int |
| Name | Nvarchar(50) |
Как это работает и в чем тут оптимизация? Допустим, есть следующий запрос:
SELECT Name FROM Stuffs WHERE Salary > 400 AND Salary < 1000
Если бы не было индекса, данный запрос работал бы только с основной таблицей, и происходило бы следующее: для проверки значения колонки Salary из базы будет извлекаться вся строковая запись, то есть значения всех колонок таблицы. В случае наличия индекса, механизм SQL Server вообще не будет обращаться к таблице Staffs, работая полностью с данными в индексе, так как индекс содержит все, необходимые для выполнения запроса, данные. Таким образом, вместо извлечения на каждой итерации десяти колонок – извлекается только две. Поскольку индекс уже отсортирован по его ключу – полю Salary, запрос выполнится еще быстрее, за счет прохождения меньшего количества строк. Когда в таблице миллионы записей, прирост производительности колоссальный.
Стоит отметить, что на синтаксис запросов индексы никак не влияют. Разработчики точно также пишут запросы к таблицам базы, а оптимизатор SQL Server сам ищет нужные индексы, среди созданных, и пытается с помощью них оптимизировать запрос.
Однако есть у индексов и свои подводные камни. Они позволяют оптимизировать и существенно ускорить операции выборки данных, но при этом отрицательно сказываются на производительности запросов по вставке и удалению данных. Так как любые изменения в данных главной таблицы должны автоматически применяться для всех ее индексов. Если для таблицы создано четыре индекса, то простая операция вставки новой строки будет выполняться пять раз. Один раз – вставка в главную таблицу и по одному разу для каждого индекса. Таким образом, время выполнения запроса INSERT увеличится в четыре раза. Аналогично обстоит дело с операцией DELETE.
Стоит отметить, что, как правило, операции выборки данных выполняются гораздо чаще, чем операции вставки и удаления, поэтому в большинстве случаев создание индексов себя оправдывает.
Столбы в индексе могут быть двух типов:
1) Столбцы в ключе индекса
2) Столбы, включенные в индекс
В ключ индекса вносятся столбцы, по которым будет проводиться фильтрация, группировка или сортировка в запросе. А включенные столбцы – это столбы, значения которых возвращается запросом, но с ними не производиться никаких действий.
В зависимости от общего числа столбцов в индексе, индексы делятся на кластеризованные и некластеризованные индексы.
Кластеризованный индекс – это индекс, в который входят все столбцы исходной таблицы (в качестве ключа или включенного столбца). По сути, кластеризованный индекс является копией исходной таблицы. Оптимизация при создании такого индекса достигается за счет упорядочивания данных в индексе по ключу. Кластеризованные индексы рекомендуется создавать для первичного ключа и внешних ключей базовой таблицы.
Некластеризованный индекс – это индекс, в который включена только часть столбцов базовой таблицы. Пример такого индекса приведен выше.
В следующей статье мы научимся правильно создавать индексы для максимальной оптимизации наших баз данных.
Комментариев нет:
Отправить комментарий