Index sql: Все, что необходимо знать про индексы MS SQL OTUS

SQL Server. Работа с индексами через запросы SQL

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

Как получить все индексы базы данных SQL Server с их определением

declare @dbTablePrefix nvarchar(256) = '' 
SELECT 
 'if not exists( SELECT * FROM sys.indexes WHERE name='''+I.name+''' ) begin ' +    CHAR(13) + CHAR(10) +     --AND object_id = OBJECT_ID('Schema.YourTableName')
	'  CREATE ' +
       CASE 
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       I.name + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE 
            WHEN I. is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE 
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value 
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE 
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE 
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
	  -- ' drop_existing = on ' + ',' +
	   CASE 
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE 
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS. name + ' ] ' +  CHAR(13) + CHAR(10) + 
	   '  print ''Create index '+ I.name +'''' +  CHAR(13) + CHAR(10) + 
	   'end  -- if exists' +   CHAR(13) + CHAR(10) + 'GO '      [CreateIndexScript],
	   schema_name(t.schema_id) + '.' + t.[name] as table_view, 
	i.[name] as index_name,
	KeyColumns,
	IncludedColumns,
   -- substring(column_names, 1, len(column_names)-1) as [columns],
    case when i.[type] = 1 then 'Clustered index'
        when i.[type] = 2 then 'Nonclustered unique index'
        when i.[type] = 3 then 'XML index'
        when i.[type] = 4 then 'Spatial index'
        when i.[type] = 5 then 'Clustered columnstore index'
        when i.[type] = 6 then 'Nonclustered columnstore index'
        when i.[type] = 7 then 'Nonclustered hash index'
        end as index_type,
    case when i.is_unique = 1 then 'Unique'
        else 'Not unique' end as [unique],
    
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type]
FROM   sys.indexes I
       JOIN sys. tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , [' + C.name + ']' + CASE 
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
                                                                            = 1 THEN 
                                                                            ' DESC '
                                                                       ELSE 
                                                                            ' ASC '
                                                                  END
                                          FROM   sys. index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal) 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys. index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , [' + C.name + ']'
                                          FROM   sys. index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys. index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
       AND (@dbTablePrefix = '' or t.[name] like  @dbTablePrefix +  '%') --Comment for all tables  I.Object_id = object_id(@dbTable)
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes 
		and t.is_ms_shipped <> 1 
		   
order by table_view
 

Если указать @dbTablePrefix, то мы получим только индексы на таблицы, начинающиеся с  заданного префикса. 

В результате выполнения скрипта мы получим скрипты вида: 

if not exists( SELECT * FROM sys. indexes WHERE name='ind_apiActions_code' ) begin 
  CREATE NONCLUSTERED INDEX ind_apiActions_code ON dbo.as_api_actions (  [code] ASC  )   
WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , 
IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , 
ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ] 
  print 'Create index ind_apiActions_code'
end  
GO 

Копируем определения индексов и выполняем массово на другой базе. Если такой индекс был, то он пропускается. Если нет какого-то столбца или таблицы, то будет просто ошибка, но другие индексы создадутся. 

Поиск дубликатов индексов SQL Server

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

-- Ищем дубликаты индексов (создает нагрузку на процессор)
select t1.tablename,t1.indexname,t1.columnlist,t2.indexname,t2.columnlist from
   (select distinct object_name(i. object_id) tablename,i.name indexname,
             (select distinct stuff((select ', ' + c.name
                                       from sys.index_columns ic1 inner join 
                                            sys.columns c on ic1.object_id=c.object_id and 
                                                             ic1.column_id=c.column_id
                                      where ic1.index_id = ic.index_id and 
                                            ic1.object_id=i.object_id and 
                                            ic1.index_id=i.index_id
                                      order by index_column_id FOR XML PATH('')),1,2,'')
                from sys.index_columns ic 
               where object_id=i.object_id and index_id=i.index_id) as columnlist
       from sys.indexes i inner join 
    	    sys.index_columns ic on i.object_id=ic.object_id and 
                                    i.index_id=ic.index_id inner join
            sys.objects o on i.object_id=o. object_id 
      where o.is_ms_shipped=0) t1 inner join
   (select distinct object_name(i.object_id) tablename,i.name indexname,
             (select distinct stuff((select ', ' + c.name
                                       from sys.index_columns ic1 inner join 
                                            sys.columns c on ic1.object_id=c.object_id and 
                                                             ic1.column_id=c.column_id
                                      where ic1.index_id = ic.index_id and 
                                            ic1.object_id=i.object_id and 
                                            ic1.index_id=i.index_id
                                      order by index_column_id FOR XML PATH('')),1,2,'')
                from sys.index_columns ic 
               where object_id=i.object_id and index_id=i.index_id) as columnlist
       from sys.indexes i inner join 
    	    sys.index_columns ic on i.object_id=ic.object_id and 
                                    i. index_id=ic.index_id inner join
            sys.objects o on i.object_id=o.object_id 
 where o.is_ms_shipped=0) t2 on t1.tablename=t2.tablename and 
       substring(t2.columnlist,1,len(t1.columnlist))=t1.columnlist and 
       (t1.columnlist<>t2.columnlist or 
         (t1.columnlist=t2.columnlist and t1.indexname<>t2.indexname))

Далее можно ручками удалить ненужные индексы в базе. 

Реорганизация или перестройка индексов в базе SQL Server

Вы можете сделать либо reorganize либо rebuild для всех индексов в базе. 

Для этого можно использовать следующий скрипт: 

-- Скрипт реорганизует индексы по всем таблицам
IF OBJECT_ID(N'tempdb..#RowCounts') IS NOT NULL
BEGIN
	DROP TABLE #RowCounts
END
GO
declare @minRows int =400 -- перестройка индекса выполняется только для таблиц где больше N строк
CREATE TABLE #RowCounts(NumberOfRows BIGINT,TableName VARCHAR(128))
EXEC sp_MSForEachTable 'INSERT INTO #RowCounts
SELECT COUNT_BIG(*) AS NumberOfRows,
''?'' as TableName FROM ?'
select  TableName, NumberOfRows from #RowCounts where NumberOfRows>@minRows
---SELECT TableName,NumberOfRows FROM #RowCounts ORDER BY NumberOfRows DESC,TableName
declare @TableName nvarchar(256)
declare @NumberOfRows int
declare cur CURSOR LOCAL for
select  TableName, NumberOfRows from #RowCounts where NumberOfRows>@minRows
open cur
fetch next from cur into @TableName, @NumberOfRows
while @@FETCH_STATUS = 0 BEGIN
print @TableName
 -- Rebuild (блочит таблицу, но быстрее идет)    или   REORGANIZE (дольше.  но не блочит таблицу)
EXEC ('ALTER INDEX ALL ON ' +@TableName + ' Rebuild  ;')
fetch next from cur into @TableName, @NumberOfRows
END
close cur
deallocate cur
DROP TABLE #RowCounts

rebuild выполняется быстрее, но блокирует таблицу. Есть режим with ONLINE,но он работает только для Enterprise версии SQL Server. Фактически это пересоздание индекса. 

reorganize выполняется дольше, но не блокирует таблицу базы данных. 

В скрипте есть настройка @minRows — минимальное количество строк в таблицах, для которых будет перестроен индекс. 

Где нужно ставить индексы в SQL Server, какие индексы можно удалить из базы данных SQL Server

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

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

--Поиск, где можно установить индексы:
select d.name AS DatabaseName, mid.*
from sys.dm_db_missing_index_details mid
join sys.databases d ON mid.database_id=d.database_id
 
--Убрать лишние индексы:
--там где user_updates больше чем user_lookup - можно удалить индексы
SELECT d.name, t.name, i.name, ius.*
FROM sys.dm_db_index_usage_stats ius
JOIN sys.databases d ON d.database_id = ius.database_id
JOIN sys.tables t ON t.object_id = ius.object_id
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id =
ius.index_id
ORDER BY user_updates DESC
 

индексов — SQL Server | Microsoft Узнайте

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

• Статья
• 05. 04.2023

Применяется к: SQL Server База данных SQL Azure Azure SQL Управляемый экземпляр

Доступные типы индексов

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

Тип индекса	Описание	Дополнительная информация
Хэш	При использовании хеш-индекса доступ к данным осуществляется через хеш-таблицу в памяти. Хэш-индексы потребляют фиксированный объем памяти, который зависит от количества сегментов.	Рекомендации по использованию индексов в таблицах, оптимизированных для памяти Рекомендации по проектированию хеш-индексов
оптимизированный для памяти Некластеризованный	Для оптимизированных для памяти некластеризованных индексов потребление памяти зависит от количества строк и размера ключевых столбцов индекса	Рекомендации по использованию индексов в таблицах, оптимизированных для памяти Рекомендации по разработке некластеризованных индексов, оптимизированных для памяти
Кластерный	Кластеризованный индекс сортирует и сохраняет строки данных таблицы или представления в порядке, основанном на ключе кластеризованного индекса. Кластеризованный индекс реализован в виде структуры индекса B-дерева, которая поддерживает быстрое извлечение строк на основе их значений ключа кластеризованного индекса.	Описание кластеризованных и некластеризованных индексов Создание кластеризованных индексов Руководство по проектированию кластеризованного индекса
Некластерный	Некластеризованный индекс можно определить для таблицы или представления с кластеризованным индексом или для кучи. Каждая строка индекса в некластеризованном индексе содержит значение некластеризованного ключа и указатель строки. Этот локатор указывает на строку данных в кластеризованном индексе или куче, имеющую значение ключа. Строки в индексе хранятся в порядке значений ключа индекса, но не гарантируется, что строки данных будут располагаться в каком-либо определенном порядке, если для таблицы не создан кластеризованный индекс.	Описание кластеризованных и некластеризованных индексов Создание некластеризованных индексов Рекомендации по проектированию некластеризованных индексов
Уникальный	Уникальный индекс гарантирует, что ключ индекса не содержит повторяющихся значений, поэтому каждая строка в таблице или представлении в некотором роде уникальна. Уникальность может быть свойством как кластеризованных, так и некластеризованных индексов.	Создание уникальных индексов Рекомендации по проектированию уникальных индексов
Колонный магазин	Индекс columnstore в памяти хранит данные и управляет ими, используя хранилище данных на основе столбцов и обработку запросов на основе столбцов. Индексы Columnstore хорошо подходят для рабочих нагрузок хранилища данных, которые в основном выполняют массовые загрузки и запросы только для чтения. Используйте индекс columnstore, чтобы получить до 10-кратного увеличения производительности запросов, прироста по сравнению с традиционным хранилищем, ориентированным на строки, и до 7-кратного сжатия данных, по сравнению с размером несжатых данных.	Руководство по индексам Columnstore Руководство по проектированию индексов Columnstore
Указатель с включенными столбцами	Некластеризованный индекс, расширенный за счет включения неключевых столбцов в дополнение к ключевым столбцам.	Создание индексов с включенными столбцами
Индекс вычисляемых столбцов	Индекс столбца, полученный из значения одного или нескольких других столбцов или определенных детерминированных входных данных.	Индексы в вычисляемых столбцах
Отфильтровано	Оптимизированный некластеризованный индекс, особенно подходящий для покрытия запросов, которые выбирают из четко определенного подмножества данных. Он использует предикат фильтра для индексации части строк в таблице. Хорошо спроектированный отфильтрованный индекс может повысить производительность запросов, снизить затраты на обслуживание индекса и уменьшить затраты на хранение индекса по сравнению с полнотабличными индексами.	Создание отфильтрованных индексов Рекомендации по проектированию отфильтрованных индексов
Пространственный	Пространственный индекс позволяет более эффективно выполнять определенные операции над пространственными объектами ( пространственные данные ) в столбце типа данных геометрия . Пространственный индекс уменьшает количество объектов, к которым необходимо применять относительно дорогостоящие пространственные операции.	Обзор пространственных индексов
XML	Измельченное и сохраненное представление больших двоичных объектов XML (BLOB) в xml столбец типа данных.	XML-индексы (SQL Server)
Полный текст	Специальный тип функционального индекса на основе токенов, который создается и поддерживается полнотекстовым механизмом Microsoft для SQL Server. Он обеспечивает эффективную поддержку сложного поиска слов в данных символьных строк.	Заполнение полнотекстовых индексов

Примечание

В документации SQL Server термин B-дерево обычно используется в отношении индексов. В индексах rowstore SQL Server реализует дерево B+. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в Руководстве по архитектуре и дизайну индекса SQL Server.

Следующие шаги

• Руководство по проектированию индекса SQL Server
• Опция SORT_IN_TEMPDB для индексов
• Отключить индексы и ограничения
• Включить индексы и ограничения
• Переименовать индексы
• Установить параметры индекса
• Требования к дисковому пространству для индексных операций DDL
• Реорганизация и перестроение индексов
• Укажите коэффициент заполнения для индекса
• Руководство по архитектуре страниц и экстентов
• Описание кластеризованных и некластеризованных индексов

Обратная связь

Просмотреть все отзывы о странице

Описаны кластеризованные и некластеризованные индексы — SQL Server

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

• Статья
• 03. 03.2023

Применяется к: SQL Server База данных SQL Azure Управляемый экземпляр Azure SQL

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

Примечание

В документации SQL Server термин B-дерево обычно используется в отношении индексов. В индексах rowstore SQL Server реализует дерево B+. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в Руководстве по архитектуре и дизайну индекса SQL Server.

Таблица или представление могут содержать следующие типы индексов:

И кластеризованные, и некластеризованные индексы могут быть уникальными. Это означает, что никакие две строки не могут иметь одинаковое значение ключа индекса. В противном случае индекс не уникален, и несколько строк могут иметь одно и то же значение ключа. Дополнительные сведения см. в разделе Создание уникальных индексов.

Индексы автоматически поддерживаются для таблицы или представления при каждом изменении данных таблицы.

Дополнительные типы индексов специального назначения см. в разделе Индексы.

Индексы и ограничения

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

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

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

Хорошо спроектированные индексы могут сократить количество дисковых операций ввода-вывода и потреблять меньше системных ресурсов, тем самым повышая производительность запросов. Индексы могут быть полезны для различных запросов, содержащих операторы SELECT, UPDATE, DELETE или MERGE. Рассмотрим запрос SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250 в 9База данных 0243 AdventureWorks2019 . При выполнении этого запроса оптимизатор запросов оценивает каждый доступный метод извлечения данных и выбирает наиболее эффективный метод. Метод может представлять собой сканирование таблицы или сканирование одного или нескольких индексов, если они существуют.

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

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

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