SQL-Ex blog

Решение

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

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

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

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

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

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

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

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

CREATE TABLE TestData (TestId integer, TestName varchar(255), TestDate date, 

		       TestType integer, TestData1 integer, 

                       TestData2 varchar(100), TestData3 XML, 

		       TestData4 varbinary(max), TestData4_FileType varchar(3));

ALTER TABLE TestData REBUILD;

DROP TABLE TestData; 

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

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

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

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

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

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

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

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

CREATE CLUSTERED INDEX IX_TestData_TestId ON dbo.TestData (TestId);  

ALTER INDEX IX_TestData_TestId ON TestData REBUILD WITH (ONLINE = ON);

DROP INDEX IX_TestData_TestId on TestData WITH (ONLINE = ON);

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

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

Некластеризованный индекс - это еще один главный тип индексов, используемых в SQL Server. Подобно своей противоположности, кластеризованному индексу, столбцы ключа индекса сохраняются в структуре B-Tree; исключение составляет то, что фактические данные не сохраняются на листочных узлах. В индексах этого типа на листовых узлах сохраняется указатель на фактические данные. Он может указывать на данные в кластеризованном индексе или на структуру кучи, в зависимости от того, как хранятся данные.

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

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

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

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

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

CREATE INDEX IX_TestData_TestDate ON dbo.TestData (TestDate);  

ALTER INDEX IX_TestData_TestDate ON TestData REBUILD WITH (ONLINE = ON);

DROP INDEX IX_TestData_TestDate on TestData;

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

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

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

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

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

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

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

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

CREATE CLUSTERED COLUMNSTORE INDEX CIX_TestData_TestType ON TestData.TestType 

  WITH (DATA_COMPRESSION = COLUMNSTORE);

ALTER INDEX CIX_TestData_TestType ON TestData REORG IX_TestData_TestDate;

DROP INDEX CIX_TestData_TestType;

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

-- первичный индекс

CREATE PRIMARY XML INDEX PXML_TestData_TestData3 ON TestData (TestData3);

-- вторичные индексы

CREATE XML INDEX XMLPATH_TestData_TestData3 ON TestData (TestData3)

  USING XML INDEX PXML_TestData_TestData3 FOR PATH;

CREATE XML INDEX XMLPROPERTY_TestData_TestData3 ON TestData (TestData3)

  USING XML INDEX PXML_TestData_TestData3 FOR PROPERTY;

CREATE XML INDEX XMLVALUE_TestData_TestData3 ON TestData (TestData3)

  USING XML INDEX PXML_TestData_TestData3 FOR VALUE;

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

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

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

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

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

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

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

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

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

-- перед созданием индекса требуется создать каталог

CREATE FULLTEXT CATALOG fulltextCatalog AS DEFAULT;   

CREATE FULLTEXT INDEX ON dbo.TestData (TestData4 TYPE COLUMN TestData4_FileType) 

  KEY INDEX PK_TestData WITH STOPLIST = SYSTEM;

ALTER FULLTEXT CATALOG fulltextCatalog REBUILD;

DROP FULLTEXT INDEX ON dbo.TestData;

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

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

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

CREATE NONCLUSTERED INDEX IX_TestData_TestDate_incTestData3 ON TestData (TestDate) 

  INCLUDE (TestData3);

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

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

ALTER TABLE TestData ADD TestDatePlus7Days AS DATEADD(DAY,7,TestDate) PERSISTED;

CREATE NONCLUSTERED INDEX IX_TestData_TestDate_Plus7Days ON TestData (TestDatePlus7Days);

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

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

CREATE INDEX IX_TestData_TestDate_TestTypeEq1 on TestData (TestDate) WHERE TestType=1;

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

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

CREATE INDEX IX_TestData_TestDate_TestType_AllData on TestData (TestDate,TestType) 

   INCLUDE (TestData1,TestData2,TestData3,TestData4);

SELECT TestData1,TestData2,TestData3,TestData4 FROM TestData WHERE TestDate > current_timestamp-1 and TestType=1;

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

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

SELECT t.name AS TableName,i.name AS IndexName,

       ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

ORDER BY avg_fragmentation_in_percent DESC;

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

REBUILD	REORG
требуется достаточно пространства для создания нового индекса	работает на месте, поэтому никакого дополнительного пространства не требуется
если процесс прерывается, требуется начинать заново	может продолжиться с того места, на котором процесс прервался
может выполняться онлайн или офлайн	всегда выполняется онлайн
генерирует больше записей в журнал, чем reorg	записывает в журнал только блоки, которые были реорганизованы
статистика обновляется автоматически	обновление статистики должно быть выполнено вручную