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

SQL-Ex blog

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

Добавил Sergey Moiseenko on Суббота, 29 февраля. 2020

Прочитав последнюю статью, наши пользователи продолжали выполнять этот запрос и хотели бы его ускорить:

SELECT Id 
FROM dbo.Users 
WHERE LastAccessDate > '2014/07/01' 
ORDER BY LastAccessDate;

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

CREATE INDEX IX_LastAccessDate_Id 
ON dbo.Users(LastAccessDate, Id);

Этим мы создадим отдельную копию нашей таблицы (также сохраняемой на 8-килобайтных страницах), которая выглядит следующим образом:

Этот индекс замедляет вставку и удаление.

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

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

Кластеризованный индекс (CX PK) имеет около 300К строк и занимает 58,1Мб на диске, поскольку он содержит все столбцы таблицы.

Некластеризованный индекс содержит то же число строк, но занимает только 5,3Мб места на диске, поскольку хранит только LastAccessDate и Id. Чем больше столбцов вы добавляете в индекс — в ключ или же во включенные столбцы — тем больше места он занимает на диске. (Я расскажу еще о проектировании индексов по ходу этой серии.)

Но этот индекс с лихвой окупается при выборке.

Снова испытайте наш запрос на выборку. Здесь я выполняю подряд два запроса: первый с хинтом INDEX=1, чтобы показать стоимость сканирования кластерного индекса. (Index #1 — это ваш кластеризованный индекс.)

• Мы получаем поиск (seek), а не сканирование (scan), поскольку SQL Server имеет возможность перейти непосредственно к строкам, у которых LastAccessDate > 2014/07/01. Это не требует сканирования всего объекта.
• Нам не требуется сортировка, поскольку мы считываем данные, которые уже отсортированы по LastAccessDate.

Сканирование кластеризованного индекса читает 7405 страниц и занимает 100мс времени процессора, чтобы выполнить ORDER BY.

Новый некластеризованный индекс читает только 335 страниц — примерно в 20 раз меньше — и не требует времени процессора на ORDER BY. Действительно хорошее проектирование индексов, подобное данному, это то, что позволит вам быстро получить улучшение в 20 или много более раз без переписывания ваших запросов. Хотел бы я, чтобы каждый запрос был идеально настроен? Конечно, но даже если бы это было так, вам все равно нужно будет помочь движку, организовав данные более удобным для поиска способом.

Этот индекс называется покрывающим.

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

Итак, наш план имеет поиск по индексу — это лучший план, который можно получить, верно? Нет, как мы увидим в следующем эпизоде.

SQL-Ex blog

Кучи в SQL Server: часть 3 — некластеризованные индексы

Добавил Sergey Moiseenko on Суббота, 19 сентября. 2020

1. Кучи в SQL Server: часть 1 — основы
2. Кучи в SQL Server: часть 2 — оптимизация чтений
3. Кучи в SQL Server: часть 3 — некластеризованные индексы (эта статья)

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

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

Демонстрация

• Номер файла
• Страница данных
• Слот

Для работы с примерами обратитесь к предыдущей статье. Используется таблица с приблизительно 4000000 записями данных.

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

SELECT * FROM dbo.CustomerOrderList 
WHERE OrderDate = '20081220' 
OPTION (QUERYTRACEON 9130); 
GO

Использование сканирования таблицы видно в плане выполнения на рис.1.

Рис.1: TABLE SCAN используется для получения 208 записей

Сканирования таблицы может быть достаточно быстрым на быстрых системах (0,716 секунд), и программиста может устроить такой временной интервал.

Рис.2: ЦП используется более 1,6 секунды, и затраченное время 0,716 секунды

Высокое время ЦП обусловлено тем фактом, что запрос использует параллельное выполнение, показанное на рис.3.

Рис.3: Параллельный план

• Запрос распараллеливается и использует процессоры, сконфигурированные для MAXDOP!
• Блокировка [SCH-S] удерживается на таблице во все время выполнения!
• Что происходит, если не только один пользователь выполнит запрос, но также имеется веб-клиент, выполняющий параллельно тысячи запросов?

CREATE NONCLUSTERED INDEX nix_CustomerOrderList_OrderDate 
ON dbo.CustomerOrderList (OrderDate); 
GO

Если вы снова выполните тот же запрос из предыдущего примера, то получите следующее улучшение, показанное на рисунке 4:

Рис.4: Уменьшается число операций ввода/вывода при использовании подходящего индекса

Теперь запрос уже не сканирует таблицу, и время выполнения также существенно уменьшилось, что видно на рисунке 5:

Рис.5: Нет избыточного использования ресурсов

• Запрос больше не распараллеливается, поскольку его стоимость упала ниже порога параллелизации.
• Затраты ЦП больше не могут быть измерены.
• Только блокировка SCH-S все еще используется, однако, имея в виду время выполнения, возможно, это можно проигнорировать,

Внутренние структуры

Некластеризованный индекс (НКИ) должен ВСЕГДА хранить ссылку на запись в таблице. Поскольку НКИ обычно содержит только несколько атрибутов таблицы, необходимо гарантировать, что атрибуты, которые не используются в НКИ, могут в любое время быть определены из таблицы. Эта ссылка хранится в куче как RID (RowLocatorID) и имеет размер 8 байт!

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

Рис.6: Поиск в структуре индекса B-Tree

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

-- Получить информацию о корневом узле индекса 
SELECT P.index_id, 
SIAU.total_pages, 
SIAU.used_pages, 
SIAU.data_pages, 
SIAU.root_page, 
sys.fn_PhysLocFormatter(SIAU.root_page) AS root_page 
FROM sys.system_internals_allocation_units AS SIAU 
INNER JOIN sys.partitions AS P 
ON (SIAU.container_id = P.partition_id) 
WHERE P.object_id = OBJECT_ID(N'dbo.CustomerOrderList', N'U'); 
GO

Подробная информация о структуре индекса, показанная на рисунке 7, получена с помощью системного dmv (динамического представления). Помним, что индексы с или представляют сами таблицы! — это куча, а представляет кластеризованный индекс. Все остальные индексы некластеризованные.

Рис.7: Индексы

Корневой узел в индексе nix_CustomerOrderList_OrderDate находится в файле #1 на странице данных 73346. Содержимое страницы данных можно проверить с помощью недокументированной команды DBCC PAGE.

DBCC TRACEON (3604); 
DBCC PAGE (0, 1, 73346, 3); 
GO

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

Рис.8: Поиск 2008-12-20 должен быть продолжен на странице 73348

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

Рис.9: С помощью 3 операций ввода/вывода вы достигаете первой записи в индексе

Фактически требуется три операции ввода/вывода. Принимая во внимание общее число операций 211, становится ясно, что произойдет на следующем шаге. Поскольку индекс хранит только атрибут OrderDate, информация о других атрибутах записи данных отсутствует. Чтобы получить эту информацию, Microsoft SQL Server должен декодировать RID для получения страницы данных, где размещена запись.

RID хранится в индексе для каждой записи и имеет фиксированную длину 8 байтов. Эти 8 байтов содержат всю необходимую информацию для получения записи данных.

Рис.10: Положение набора данных как RID

Запись, чья OrderDate представлена в индексе, находится в файле #1 на странице #219904 в слоте #8.

DBCC PAGE (0, 1, 219904, 3) WITH TABLERESULTS; 
GO

Рис.11: Содержимое слота #8

Процесс повторяется для каждой записи, найденной в индексе. Математика, стоящая за 211, довольно тривиальна: 3 операции ввода/вывода для поиска в индексе + 208*1 операций для ссылок на саму таблицу.

Рис.12: Доступ к куче по RID

Поиск RID против поиска ключа

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

Рис.13: Дополнительные операции ввода/вывода при использовании кластеризованного индекса

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

Рис.14: Сравнение планов с RID Lookup и Key Lookup

При поиске RID, потребуется преобразовывать RowLocatorID, что приведет (предположительно) к более высокой нагрузке на процессор. Одной из самых больших проблем в плане выполнения Microsoft SQL Server является оценка стоимости скалярных (SCALAR) операций. По умолчанию они оцениваются в 0%; однако тут забывается, что оператор должен выполняться для КАЖДОЙ записи, выходящей из предыдущего оператора.

Чтобы сделать обоснованное утверждение о стоимости преобразования, выполняется следующий код с SQLQueryStress Адама Мачаника и четырьмя потоками (число ЦП в тестовой машине):

Рис.15: Сравнение производительности кучи и кластеризованного индекса

При непосредственном сравнении куча «побеждает» по всем позициям. Особенно интересно в этом сравнении тот факт, что нагрузка на процессор в 3,3 мс/итерация значительно ниже нагрузки для кластеризованного индекса (5мс).

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

Выводы

1. Markus Wienand. Nonsensical defaults: primary key as cluster key
2. Thomas Kejser. Clustered Index Vs. Heap

Your access to this site has been limited by the site owner

If you think you have been blocked in error, contact the owner of this site for assistance.

If you are a WordPress user with administrative privileges on this site, please enter your email address in the box below and click «Send». You will then receive an email that helps you regain access.

Block Technical Data

Block Reason:	Access from your area has been temporarily limited for security reasons.
Time:	Mon, 20 Nov 2023 7:56:10 GMT

About Wordfence

Wordfence is a security plugin installed on over 4 million WordPress sites. The owner of this site is using Wordfence to manage access to their site.

You can also read the documentation to learn about Wordfence’s blocking tools, or visit wordfence.com to learn more about Wordfence.

Click here to learn more: Documentation

Generated by Wordfence at Mon, 20 Nov 2023 7:56:10 GMT.
Your computer’s time: .

Создание некластеризованных индексов

Некластеризованные индексы можно создавать в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Некластеризованный индекс — это структура индекса, отделенная от данных, хранящихся в таблице, и переупорядочивающая один или несколько выделенных столбцов. Некластеризованные индексы часто помогают быстрее находить данные, чем поиск в базовой таблице; Иногда запросы могут быть полностью отвечать данными в некластеризованном индексе, или некластеризованный индекс может указывать ядро СУБД на строки в базовой таблице. Обычно некластеризованные индексы создаются с целью повышения производительности часто используемых запросов, не входящих в кластеризованный индекс, либо для поиска строк таблицы, не имеющей кластеризованного индекса (которая называется кучей). Можно создать несколько некластеризованных индексов для таблицы или индексированного представления.

Подготовка к работе

Типичные реализации

Некластеризованные индексы реализуются следующим образом.

• Ограничения UNIQUE При создании ограничения UNIQUE создается уникальный некластеризованный индекс. Он нужен, чтобы принудительно применять ограничение UNIQUE по умолчанию. Если кластеризованный индекс в таблице еще не создан, то можно указать уникальный кластеризованный индекс. Дополнительные сведения см. в статье Ограничения уникальности и проверочные ограничения.
• Индекс, не зависящий от ограничения По умолчанию некластеризованный индекс создается в том случае, если ранее не был задан кластеризованный индекс. Для таблицы может быть создано не более 999 некластеризованных индексов. В это число входят любые индексы, созданные ограничениями PRIMARY KEY или UNIQUE, но не входят XML-индексы.
• Некластеризованный индекс в индексированном представлении Некластеризованные индексы в представлении могут создаваться только после создания в нем уникального кластеризованного индекса. Дополнительные сведения см. в разделе «Создание индексированных представлений».

Безопасность

Разрешения

Необходимо разрешение ALTER для таблицы или представления. Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner.

Использование среды SQL Server Management Studio

Создание некластеризованного индекса с помощью конструктора таблиц

1. В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо создать некластеризованный индекс.
2. Разверните папку Таблицы.
3. Щелкните правой кнопкой мыши таблицу, в которой нужно создать некластеризованный индекс, и выберите пункт Конструктор.
4. Щелкните правой кнопкой мыши столбец, для которого нужно создать некластеризованный индекс, и выберите Индексы/Ключи.
5. В диалоговом окне Индексы и ключи нажмите Добавить.
6. Выберите новый индекс в текстовом поле Выбранный первичный/уникальный ключ или индекс .
7. Выберите в сетке элемент Создать как кластеризованныйи в раскрывающемся списке справа от свойства выберите значение Нет .
8. Выберите Закрыть.
9. В меню Файл выберите пункт Сохранитьимя_таблицы.

Создание некластеризованного индекса в обозревателе объектов

1. В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо создать некластеризованный индекс.
2. Разверните папку Таблицы.
3. Разверните таблицу, для которой необходимо создать некластеризованный индекс.
4. Щелкните правой кнопкой мыши папку Индексы, выберите Создать индекс и Некластеризованный индекс.
5. В диалоговом окне Создание индекса на странице Общие введите имя нового индекса в поле Имя индекса .
6. В разделе Ключевые столбцы индекса щелкните Добавить….
7. В диалоговом окне Выбор столбцов изимя_таблицы установите флажки для столбцов таблицы, добавляемых к некластеризованному индексу.
8. Нажмите ОК.
9. В диалоговом окне «Создать индекс» нажмите кнопку «ОК«.

Использование Transact-SQL

Создание некластеризованного индекса в таблице с помощью Transact-SQL

1. В обозреватель объектов подключитесь к экземпляру ядро СУБД с AdventureWorks2022 установленным. Вы можете скачать AdventureWorks2022 из примеров баз данных.
2. На стандартной панели выберите пункт Создать запрос.
3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

USE AdventureWorks2022; GO -- Find an existing index named IX_ProductVendor_VendorID and delete it if found. IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_ProductVendor_VendorID') DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor; GO -- Create a nonclustered index called IX_ProductVendor_VendorID -- on the Purchasing.ProductVendor table using the BusinessEntityID column. CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor (BusinessEntityID); GO 

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

• Инструкция CREATE INDEX (Transact-SQL)
• Руководство по проектированию индексов SQL Server