sql что такое индексы какие они бывают

SQL-Ex blog

Новости сайта «Упражнения SQL», статьи и переводы

Типы индексов SQL Server

Проблема

Решение

Таблицы кучи в SQL Server

Что такое куча (heap) в SQL Server?

Прежде чем мы погрузимся в различные типы индексов SQL Server, сначала следует описать базовую структуру таблицы. Таблицы, на которых не определен кластеризованный индекс (об этом позже), сохраняются в структурах «кучи», что означает, главным образом, отсутствие порядка хранения наборов данных на каждой странице.

Преимущества и использование кучи в SQL Server

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

Недостатки кучи в SQL Server

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

Базовый синтаксис кучи в SQL Server

Кластеризованный индекс в SQL Server

Что такое кластеризованный индекс?

Кластеризованный индекс один из главных типов индекса в SQL Server. Кластеризованный индекс сохраняет индексный ключ в структуре B-Tree, наряду с фактическими данными таблицы в каждом листовом узле индекса. Задание кластеризованного индекса на таблице аннулирует структуру кучи, которая описывалась выше. Поскольку остальные данные таблицы (например, неключевые столбцы) сохраняются на листовых узлах индексной структуры, таблица может иметь только один кластеризованный индекс.

Преимущества и использование кластеризованного индекса

Наличие кластеризованного индекса на таблице приносит много пользы, но главным преимуществом является ускорение производительности запроса. Запросы, которые содержат столбцы ключа индекса в предложении WHERE, используют структуру индекса для прямого доступа к данным таблицы. Кластеризованный индекс также устраняет необходимость в лишнем поиске для получения данных остальных столбцов в запросах на основе значений ключа индекса. Это зачастую не справедливо для индексов других типов. Вы также можете избавиться от необходимости сортировать данные. Если предложение ORDER BY запроса основывается на значениях ключа индекса, то сортировка не потребуется, поскольку данные уже упорядочены по этим значениям.

Недостатки кластеризованного индекса

Имеется пара недостатков, связанных с кластеризованными индексами. Это некоторые накладные расходы на поддержание индексной структурой, связанные с любыми операциями DML (INSERT, UPDATE, DELETE). Это особенно справедливо, если вы обновляете фактические значения ключа в индексе, т.к. в этом случае все связанные табличные данные также должны быть перемещены, поскольку они хранятся на листовом узле записи индекса. В каждом случае это окажет определенное влияние на производительность вашего DML-запроса.

Основной синтаксис кластеризованного индекса в SQL Server

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

Что такое некластеризованный индекс?

Преимущества и использование некластеризованных индексов

Преимущества некластеризованного индекса подобны упомянутым выше преимуществам кластеризованного. Главным образом, это ускорение производительности запроса. Тем не менее, есть два отличия. Во-первых, вы можете иметь множество некластеризованных индексов, определенных на одной таблице. Это позволяет вам индексировать различные столбцы, что может помочь запросам с различными столбцами в предложении WHERE более быстро извлекать данные и избежать необходимости сортировки, заданной в предложении ORDER BY. Во-вторых, хотя имеются накладные расходы в обслуживании некластеризованных индексов при выполнении операторов DML, они меньше, чем для кластеризованного индекса.

Недостатки некластеризованного индекса

Главным недостатком, как и для кластеризованного индекса, является избыточная нагрузка, требуемая для поддержания индекса при операциях DML. Иногда может быть сложно сбалансировать производительность запросов, если таблица имеет слишком много некластеризованных индексов. Помогая всем запросам на выборку, они, в то же время, могут реально замедлить выполнение запросов DML.

Основной синтаксис некластеризованного индекса в SQL Server

Индексы поколоночного хранения в SQL Server

Что такое индекс поколоночного хранения?

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

Преимущества и использование поколоночного индекса

Индексы поколоночного хранения были спроектированы для использования при индексировании очень больших объемов данных в приложениях хранилищ данных, в частности, для фактологических таблиц. В зависимости от индексируемых данных вы можете наблюдать до 100-кратного улучшения производительности запроса. Поколоночные индексы также обладают возможностью сжатия данных. В зависимости от ваших данных можно получить 10-кратную экономию пространства хранения. Чем меньше селективность вашего столбца, тем больше он может быть сжат.

Недостатки индексов поколоночного хранения

Как и для каждой возможности, имеются некоторые недостатки при использовании поколоночных индексов. Они не могут использоваться со всеми типами данных: типы varchar(max)/nvarchar(max), xml и text/ntext, image и CLR не поддерживаются в индексах поколоночного хранения. Их также нельзя использовать, если включены такие функции, как репликация, сбор данных об изменениях или отслеживание изменений. Что касается производительности, хотя запросы SELECT могут показать большое улучшение, это не относится к операциям DML. В силу накладных расходов, связанных с обновлением поколоночного индекса, любые операции DML будут выполняться хуже, чем их построчные аналоги. Наконец, хотя это уже не должно быть проблемой, если вы пользуетесь последней версией SQL Server, но до SQL Server 2014 индексы поколоночного хранения не были обновляемыми, поэтому наличие одного такого индекса делало соответствующую таблицу доступной только на чтение.

Основной синтаксис поколоночных индексов в SQL Server

XML индексы в SQL Server

Что такое XML индекс?

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

Преимущества и использование индекса XML

Вообще говоря, вы можете получить выгоду из использования XML индекса, когда XML значения столбца велики, но извлекаемые части малы. Это будет препятствовать загрузке всего XML значения в память и парсинга для каждого запроса. Первичный XML индекс будет индексировать все теги, значения и пути вашего столбца XML и может вернуть скалярные значения или поддеревья XML. Вторичные индексы могут быть трех разных типов. Вторичный XML-индекс PATH выгоден, если ваши запросы используют выражения пути. Если ваши запросы не знают имен атрибутов в XML значениях, то вторичный XML-индекс VALUE может ускорить эти запросы. Запросы, для которых первичный ключ объекта значения известен, и вы используете метод value() типа XML, могут получить преимущество с индексом PROPERTY.

Недостатки XML индексов

Главным недостатком при использовании XML индексов является то, что они могут использовать большое количество дискового пространства, поскольку каждый тег в XML значении создает множество строк в индексе. Как и для всех индексов, здесь также имеются накладные расходы при обновлении/поддерживании индекса, что вызывает замедление операций DML на XML столбце.

Основной синтаксис индекса XML в SQL Server

Полнотекстовые индексы в SQL Server

Что такое полнотекстовый индекс?

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

Преимущества и использование полнотекстового индекса

Как упоминалось выше, это возможность полнотекстового поиска с созданием индексов на столбцах, которые не индексируются с помощью индексов стандартных типов. В помощью полнотекстовых индексов мы можем проиндексировать большие столбцы varchar(max) и nvarchar(max), а также столбцы любых следующих типов данных: char, varchar, nchar, nvarchar, text, ntext, image, xml и FILESTREAM. После создания индекса вы можете писать запросы, которые выполняют индексный поиск, используя функции полнотекстовых запросов, которые будут искать данные, связанные с любыми из следующих условий.

. Одно или больше конкретных слов или фраз (простой термин).
. Слово или фраза, когда слова начинаются с конкретного текста (префиксный термин).
. Флективные формы конкретного слова (термин рода).
. Слово или фраза, близкие к другому слову или фразе (термин близости).
. Синонимические формы конкретного слова (тезаузус).
. Слова или фразы, использующие взвешенные значения (взвешенный термин).

Недостатки полнотекстовых индексов

Большинство недостатков использования полнотекстовых индексов связано с потреблением ресурсов. Поскольку поиск в полнотекстовом индексе выполняется службой MSFTESQL, а не службой SQL Server, эти две службы могут конкурировать за ресурсы на вашем сервере. Если не сконфигурировать правильно эти две службы, могут возникнуть проблемы. Кроме того, хотя дисковое пространство становится все более дешевым с годами, все еще есть нагрузка, связанная с количеством операций ввода/вывода для ваших файлов данных, поэтому для относительно большого каталога может иметь смысл размещать этот каталог на отдельном диске, чтобы две службы (SQL Server и MSFTESQL) не конкурировали за этот ресурс.

Основной синтаксис полнотекстового индекса в SQL Server

Вариации индексов в SQL Server

Включенные в индекс столбцы

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

Индекс на базе функций SQL Server (вычисляемые столбцы)

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

Фильтрованный индекс в SQL Server

Покрывающий индекс SQL Server

Обслуживание индексов в SQL Server

Хотя индексы хороши для улучшения производительности вашей базы данных, они требуют некоторого обслуживания для поддержания их оптимальной работы. Спустя некоторое время после выполнения операторов INSERT, UPDATE и DELETE на вашей таблице, связанные с ней индексы могут стать фрагментированными. Существует много факторов, которые влюяют на то, насколько быстро индекс становится фрагментированным, но к счастью у нас есть несколько встроенных в SQL Server инструментов, которые покажут точную фрагментацию индекса. Динамическое представление sys.dm_db_index_physical_stats даст вам размер и статистику фрагментации по каждому их ваших индексов. Приведенный ниже запрос может быть использован как исходная точка в списке фрагментации ваших индексов. Обратите внимание на последний параметр, SAMPLED. Этот параметр определяет уровень сканирования при сборе статистики индекса. Я обычно использую SAMPLED, т.к. большинство систем, с которыми я работаю, довольно большие, но существуют и другие варианты, которые дают более или менее подробную информацию в зависимости от ваших потребностей. Другими вариантами являются DEFAULT, NULL, LIMITED, DETAILED.

Как только вы определили уровень фрагментации ваших индексов, у вас есть два варианта для её устранения. Вы можете использовать либо REORGANIZE (реорганизовать индекс) либо REBUILD (перестроить индекс). Майкрософт рекомендует ничего не предпринимать, если уровень фрагментации менее 10%, от 10% до 30% использовать REORG, а свыше 30% перестраивать (REBUILD). Эти числа выбраны на основе времени, котрое требуется для выполнения каждой операции. REBUILD создает новый индекс с нуля, в то время как REORGпросто работает над устранением фрагментации. Индекс с небольшой фрагментацией может быть реогранизован довольно быстро. Но когда фрагментация превышает 30%, индекс обычно может быть перестроен за то же время, что и REORG, поэтому имеет смысл просто перестроить его. Есть и другие факторы, которые следует принять во внимание при выборе варианта. Вот они.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Источник

Основы индексов в Microsoft SQL Server

В данном материале будут рассмотрены такие объекты базы данных Microsoft SQL Server как индексы, Вы узнаете, что такое индексы, какие типы индексов бывают, как их создавать, оптимизировать и удалять.

Что такое индексы в базе данных?

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

Типы индексов в Microsoft SQL Server

В Microsoft SQL Server существуют следующие типы индексов:

Создание и удаление индексов в Microsoft SQL Server

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

Примечание! В качестве SQL сервера у меня выступает версия Microsoft SQL Server 2016 Express.

Создание индексов

Для создания индексов в Microsoft SQL Server существует два способа: первый – это с помощью графического интерфейса среды SQL Server Management Studio (SSMS), и второй – это с помощью языка Transact-SQL, мы с Вами разберем оба способа.

Исходные данные для примеров

Давайте представим, что у нас есть таблица с товарами под названием TestTable, в которой есть три столбца:

Пример создания кластеризованного индекса

Как я уже говорил, кластеризованный индекс создается автоматически, если мы, например, при создании таблицы указываем конкретный столбец в качестве первичного ключа (PRIMARY KEY), но так как мы этого не сделали, давайте рассмотрим пример самостоятельного создания кластеризованного индекса.

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

Для примера давайте просто создадим кластеризованный индекс, без создания первичного ключа. Сначала сделаем это с помощью Management Studio.

Открываем SSMS и в обозревателе объектов находим нужную таблицу и щелкаем правой кнопкой мыши по пункту «Индексы», выбираем «Создать индекс» и тип индекса, в нашем случае «Кластеризованный».

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

После ввода всех необходимых параметров жмем «ОК», в итоге будет создан кластеризованный индекс.

Точно также можно было бы создать кластеризованный индекс, используя инструкцию T-SQL CREATRE INDEX, например, вот так

Или, как мы уже говорили, можно было бы использовать и инструкцию создания первичного ключа, например

Пример создания некластеризованного индекса с включенными столбцами

Сейчас давайте рассмотрим пример создания некластеризованного индекса, при этом мы укажем столбцы, которые не будет являться ключевыми, но будут включаться в индекс. Это полезно в тех случаях, когда Вы создаете индекс для конкретного запроса, например, для того чтобы индекс полностью покрывал запрос, т.е. содержал все столбцы (это называется «Покрытием запроса»). Благодаря покрытию запроса повышается производительность, так как оптимизатор запросов может найти все значения столбцов в индексе, при этом не обращаясь к данным таблиц, что приводит к меньшему числу дисковых операций ввода-вывода. Но помните, что включение в индекс неключевых столбцов влечет за собой увеличение размера индекса, т.е. для хранения индекса потребуется больше места на диске, а также может повлечь и снижение производительности операций INSERT, UPDATE, DELETE и MERGE на базовой таблице.

После открытия формы «Новый индекс» мы указываем название индекса, добавляем ключевой столбец или столбцы с помощью кнопки «Добавить», например, для нашего тестового случая давайте укажем CategoryID.

Далее переходим на вкладку «Включено столбцы» и с помощью кнопки «Добавить» добавляем столбцы, которые мы хотим включить в индекс, в нашем случае, например, ProductName.

На Transact-SQL это будет выглядеть следующим образом.

Пример удаления индекса в Microsoft SQL Server

Для того чтобы удалить индекс можно щелкнуть правой кнопкой по нужному индексу и нажать «Удалить», затем подтвердить свое действия нажав «ОК».

или также можно использовать инструкцию DROP INDEX, например

Следует отметить, что инструкция DROP INDEX неприменима к индексам, которые были созданы путем создания ограничений PRIMARY KEY и UNIQUE. В данном случае для удаления индекса нужно использовать инструкцию ALTER TABLE с предложением DROP CONSTRAINT.

Оптимизация индексов в Microsoft SQL Server

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

В каких случаях использовать реорганизацию индекса, а в каких перестроение?

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

В данном случае нас интересует столбец avg_fragmentation_in_percent, т.е. процентная доля логической фрагментации.

Так вот, Microsoft рекомендует:

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

Реорганизация индексов

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

Для реорганизации индекса можно использовать как графический инструмент SSMS, так и инструкцию Transact-SQL.

Реорганизация индекса с помощью Management Studio

Реорганизация индекса с помощью Transact-SQL

Перестроение индексов

Перестроение индекса – это процесс, при котором происходит удаление старого индекса и создание нового, в результате чего фрагментация устраняется.

Для перестроения индексов можно использовать два способа.

Первый. Используя инструкцию ALTER INDEX с предложением REBUILD. Эта инструкция заменяет инструкцию DBCC DBREINDEX. Обычно для массового перестроения индексов используется именно этот способ.

И второй, используя инструкцию CREATE INDEX с предложением DROP_EXISTING. Можно использовать, например, для перестроения индекса с изменением его определения, т.е. добавления или удаления ключевых столбцов.

В Management Studio функционал для перестроения также доступен. Правой кнопкой по нужному индексу «Перестроить».

На этом материал по основам индексов в Microsoft SQL Server закончен, если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server, удачи!

Источник