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

В этой статье мы рассмотрим простой подход к определению лучших кандидатов для кластеризованных индексов с использованием базы данных AdventureWorks в качестве примера. Давайте начнем с изучения таблицы Person.Address в AdventureWorks, которая в настоящее время имеет четыре индекса.

Чтобы собрать статистику использования индексов, мы можем выполнить запрос несколько раз в базе данных AdventureWorks:

SELECT AddressLine1, AddressLine2 
FROM Person.Address 
WHERE StateProvinceID = 1

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

Чтобы получить представление о том, как используются эти индексы, мы можем выполнить следующий запрос:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM sys.dm_db_index_usage_stats AS S 
INNER JOIN sys.indexes AS I 
ON I.[OBJECT_ID] = S.[OBJECT_ID] 
AND I.INDEX_ID = S.INDEX_ID 
WHERE OBJECT_NAME(S.[OBJECT_ID]) = 'Address'

Исходя из результатов, мы можем определить, что SQL Server выполнил пять операций USER_SEEK по индексу IX_Address_StateProvinceID (не кластеризованный индекс) и пять операций USER_LOOKUP по индексу PK_Address_AddressID (кластеризованный индекс). Это соответствует плану выполнения, где поиск по индексу следует за поиском по ключу. Если этот шаблон использования точно отражает способ доступа пользователей к базе данных, мы можем заключить, что индекс IX_Address_StateProvinceID будет лучшим выбором для кластеризованного индекса. Таким образом, мы можем устранить поиск по ключу, который составляет 96% плана выполнения.

Теперь, когда мы определили StateProvinceID как предпочтительный столбец для кластеризованного индекса, нам нужно выполнить несколько шагов для внесения изменений. Поскольку таблица Person.Address ссылается на внешние ключи (FK), мы должны удалить их перед удалением существующего первичного ключа (PK) и кластеризованного индекса. Вот запросы для удаления FK, PK и создания нового кластеризованного индекса:

ALTER TABLE [HumanResources].[EmployeeAddress] DROP CONSTRAINT [FK_EmployeeAddress_Address_AddressID]
ALTER TABLE [Sales].[CustomerAddress] DROP CONSTRAINT [FK_CustomerAddress_Address_AddressID]
ALTER TABLE [Purchasing].[VendorAddress] DROP CONSTRAINT [FK_VendorAddress_Address_AddressID]
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [FK_SalesOrderHeader_Address_ShipToAddressID]
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [FK_SalesOrderHeader_Address_BillToAddressID]
ALTER TABLE Person.Address DROP CONSTRAINT PK_Address_AddressID
CREATE CLUSTERED INDEX IX_StateProvinceID ON Person.Address (StateProvinceID)

В реальном сценарии рекомендуется воссоздать PK и создать скрипт для создания FK. Таким образом, после внесения изменений вы сможете легко восстановить их.

После внесения нового кластеризованного индекса мы можем повторно выполнить запрос к таблице и наблюдать обновленный план выполнения:

SELECT AddressLine1, AddressLine2 
FROM Person.Address 
WHERE StateProvinceID = 1

Теперь мы видим, что план выполнения включает поиск по кластеризованному индексу без поиска по ключу. Изучив статистику использования индексов, мы можем подтвердить, что есть только операции USER_SEEK и больше нет операций USER_LOOKUP.

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

Тщательный анализ образцов доступа к данным и статистики использования индексов позволяет принимать обоснованные решения по оптимизации кластеризованных индексов в SQL Server. Этот подход может значительно повысить производительность наших приложений и обеспечить эффективное извлечение данных.