Кластеризованные и некластеризованные индексы
Индекс является структурой на диске, которая связана с таблицей или представлением и ускоряет получение строк из таблицы или представления. Индекс содержит ключи, построенные из одного или нескольких столбцов в таблице или представлении. Эти ключи хранятся в виде структуры сбалансированного дерева, которая поддерживает быстрый поиск строк по их ключевым значениям в SQL Server.
В документации по SQL Server термин «сбалансированное дерево» обычно используется в отношении индексов. В индексах rowstore SQL Server реализует B+-дерево. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.
Таблица или представление может иметь индексы следующих типов.
- Кластеризованный
- Кластеризованные индексы сортируют и хранят строки данных в таблицах или представлениях на основе их ключевых значений. Эти ключевые значения — это столбцы, включенные в определение индекса. Существует только один кластеризованный индекс для каждой таблицы, так как строки данных могут храниться в единственном порядке.
- Строки данных в таблице хранятся в порядке сортировки только в том случае, если таблица содержит кластеризованный индекс. Если у таблицы есть кластеризованный индекс, то таблица называется кластеризованной. Если у таблицы нет кластеризованного индекса, то строки данных хранятся в неупорядоченной структуре, которая называется кучей.
- Некластеризованные индексы имеют структуру, отдельную от строк данных. В некластеризованном индексе содержатся значения ключа некластеризованного индекса, и каждая запись значения ключа содержит указатель на строку данных, содержащую значение ключа.
- Указатель из строки индекса в некластеризованном индексе, который указывает на строку данных, называется указателем строки. Структура указателя строки зависит от того, хранятся ли страницы данных в куче или в кластеризованной таблице. Для кучи указатель строки является указателем на строку. Для кластеризованной таблицы указатель строки данных является ключом кластеризованного индекса.
- Вы можете добавить неключевые столбцы на конечный уровень некластеризованного индекса, чтобы обойти существующее ограничение на ключи индексов и выполнять полностью индексированные запросы. Дополнительные сведения см. в статье Создание индексов с включенными столбцами. Дополнительные сведения об ограничениях ключа индекса см. в разделе «Максимальная емкость» для SQL Server.
Как кластеризованные, так и некластеризованные индексы могут быть уникальными. При использовании уникального индекса две строки не могут иметь одинаковое значение для ключа индекса. В противном случае индекс не является уникальным, и несколько строк могут совместно использовать одно и то же значение ключа. Дополнительные сведения см. в статье Создание уникальных индексов.
Обслуживание индексов таблиц и представлений происходит автоматически при любом изменении данных в таблице.
Дополнительные типы индексов специальных назначений см. в индексах индексов специальных назначений.
Индексы и ограничения
SQL Server автоматически создает индексы при определении ограничений PRIMARY KEY и UNIQUE в столбцах таблицы. Например, при создании таблицы с ограничением UNIQUE ядро СУБД автоматически создает некластеризованный индекс. Если вы настроите первичный ключ, ядро СУБД автоматически создает кластеризованный индекс, если кластеризованный индекс еще не существует. Если вы пытаетесь применить ограничение PRIMARY KEY в существующей таблице, для которой уже создан кластеризованный индекс, SQL Server применяет первичный ключ с помощью некластеризованного индекса.
Использование индексов оптимизатором запросов
Хорошо разработанные индексы могут снизить операции ввода-вывода на диске и использовать меньше системных ресурсов. Таким образом, эти индексы повышают производительность запросов. Индексы могут быть полезны для различных запросов, содержащих инструкции SELECT, UPDATE, DELETE или MERGE. Рассмотрим запрос SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250 в базе данных AdventureWorks2022 . При выполнении этого запроса оптимизатор запросов оценивает все доступные методы получения данных и выбирает наиболее эффективный метод. Этим методом может являться просмотр таблицы или просмотр одного или более индексов, если они существуют.
Во время сканирования таблицы оптимизатор запросов считывает все строки в таблице и извлекает строки, соответствующие критериям запроса. Просмотр таблицы формирует много дисковых операций ввода-вывода и может быть ресурсоемкой операцией. Но если результирующий набор запроса содержит высокий процент строк таблицы, то просмотр таблицы может оказаться самым эффективным методом.
Когда оптимизатор запросов использует индекс, он выполняет поиск по ключевым столбцам индекса, находит место хранения запрашиваемых строк и извлекает оттуда совпадающие строки. Как правило, поиск индекса гораздо быстрее, чем поиск в таблице. В отличие от таблицы, индекс часто содержит очень мало столбцов для каждой строки, а строки отсортированы по порядку.
Оптимизатор запросов обычно выбирает наиболее эффективный метод при выполнении запросов. Но если отсутствуют доступные индексы, оптимизатор запросов должен использовать просмотр таблицы. Ваша задача — спроектировать и создать индексы, которые лучше всего подходят для конкретной среды, чтобы оптимизатор запросов мог выбирать из нескольких эффективных индексов. SQL Server предоставляет помощник по настройке ядра СУБД для анализа среды базы данных и выбора соответствующих индексов.
Дополнительные сведения о рекомендациях по проектированию индексов и внутренних компонентах см . в руководстве по архитектуре индекса SQL Server и Azure SQL.
Далее
- Руководство по архитектуре и разработке индексов SQL Server и Azure SQ
- Создание кластеризованных индексов
- Создание некластеризованных индексов
T-SQL Кучи, кластеризованные индексы и некластеризованные индексы
• Первые 8 страниц «объекта» хранятся в смешанных участках. После этого данные хранятся только в унифицированных участках.
• 8 страниц группируются в «участки». Смешаные участки хранят данные из разных «объектов». Унифицированные участки хранят данные одного «объекта»
• SQL Server использует страницы именуемыми — «карты размещения индексов» (Index Allocation Map) или кратко — IAM для определения страниц принадлежащих «объекту»
Кучи – это данные, хранящиеся без какой-либо определенной сортировки, не имеющие индексов, доступ и поиск по таким данным происходит последовательно при сканировании страниц, и может занимать довольно долгое время влияя негативно на производительность.
Кучи подходят для хранения небольшого количества данных.
Существуют два типа индексов: кластеризованные и некластеризованные.
Кластеризованный индекс хранит в своих узлах-листьях реальные строки данных.
Некластеризованный индекс является вспомогательной структурой, которая указывает данные в таблице
Кластеризованные индексы
- В SQL Server индексы организованы в виде сбалансированных деревьев. Каждая страница в сбалансированном дереве индекса называется узлом индекса.
< >Верхний узел сбалансированного дерева называется корневым. Узлы нижнего уровня индекса называются конечными. < >Все уровни индекса между корневыми и конечными узлами называются промежуточными. < >В кластеризованном индексе конечные узлы содержат страницы данных базовой таблицы. < >На страницах индекса корневого и промежуточного узлов находятся строки индекса. < >Каждая строка индекса содержит ключевое значение и указатель либо на страницу промежуточного уровня сбалансированного дерева, либо на строку данных на конечном уровне индекса. < >Страницы на каждом уровне связаны в двунаправленный список.На схеме — кластеризованный индекс выглядит в виде B-дерева, где хранятся реальные строки данных таблицы в отсортированном порядке в узлах-листьях.
Т.Е. данные будут храниться так:
Т.к. данные кластеризованного индекса хранятся в узлах-листьях, они станут доступны, когда будет найден определенный узел-лист, это даст сокращение количества операций ввода-вывода и повысит производительность системы.
Также данные при чтении приходят отсортированными по индексу. К примеру, если известно, что всегда будет требоваться сортировка данных в определенном порядке, то использование кластеризованного индекса означает, что вам не потребуется выполнять сортировку данных при выборке.
Недостатком является то, что доступ к таблице всегда происходит через индекс, что приводит к дополнительной нагрузке на SQL Server. Доступ к данным начинается с корневого узла и проходит через индекс, пока не будет достигнут узел-лист, содержащий нужные данные. При больших объемах данных создается много узлов-листьев, соответственно количество уровней индекса, необходимых для поддержки столь большого числа узлов-листьев — увеличивается, и приводит к увеличению количества операций ввода-вывода для перемещения от корневого узла к узлу-листу.
Т.к. кластеризованный индекс хранит реальные данные, нельзя создать более одного кластеризованного индекса в таблице.
Некластеризованные индексы
Некластеризованный индекс не содержит реальных данных таблицы в узлах-листьях. Узлы-листья содержат один из двух типов информации о местоположении строк данных.
Если в таблице не создан кластеризованный индекс, то некластеризованные индексы по этой таблице хранят в своих узлах-листьях идентификаторы строк (Row ID на первой схеме). Идентификатор строки указывает на реальную строку данных в таблице, по сути это — значение, включающее в себя номер файла данных, номер страницы и местоположение строки на этой странице.
Если в таблице создан кластеризованный индекс, то некластеризованные индексы содержат в узле-листе значение ключа кластеризованного индекса для этих данных (вторая схема). При достижении узла-листа некластеризованного индекса находящееся в нем значение кластеризованного ключа используется для поиска в кластеризованном индексе, соответствующий узел-лист которого содержит искомую строку данных.
Возможно создать до 249 некластеризованных индексов на одну таблицу.
Т.е. хранение данных выглядит так:
На этом – все, желаю удач!
Почитать об индексах можно еще тут:
SQL-Ex blog
Индекс создается командой create index и непосредственно недоступен пользователю. Индексы используются оптимизатором запросов для доступа к данным в базовых таблицах и представлениях.
Индексы бывают двух видов — кластеризованные и некластеризованные. Некластеризованный индекс — это вспомогательная таблица, которая содержит данные столбца/столбцов (ключ индекса), по которым индексируется базовая таблица и указатель (RID) на данные в таблице, соответствующие этому ключу.
Кластеризованный индекс — это индекс, у которого на листовом уровне находятся сами табличные данные. Поскольку индекс содержит физически упорядоченные данные, то становится ясно, почему для каждой отдельной таблицы можно создать только один кластеризованный индекс.
Именно в силу упорядоченности данных индекс предоставляет более эффективный доступ по сравнению со сканированием таблицы к требуемым данным.
Поскольку речь идет о поиске по ключу, а кластеризованный индекс у нас только один (и, соответственно, только один ключ), то мы можем создать дополнительно некластеризованные индексы. Хорошей новостью является то, что таких индексов мы можем создать сколько угодно. Реально, конечно, их количество ограничено, но возможность создать несколько сотен индексов на одну таблицу может удовлетворить любым потребностям.
Индексы, как правило, имеют структуру B-Tree — древовидная иерарархическая структура — которая позволяет, наряду со сканированием индекса (index scan), использовать прямой доступ к данным — поиск по индексу (index seek). Эта структура используется как для кластеризованных, так и некластеризованных индексов. Различием между ними, повторю, является то, что на листовом уровне дерева у кластеризованного индекса находятся сами табличные данные, а у некластеризованного — указатели на данные в таблице.
Если сказанное выше вам не вполне понятно, могу порекомендовать хорошую статью Гейла Шоу (Gail Shaw. Introduction to Indexes).
Кластерный индекс создается автоматически на первичном ключе (который и является ключом индекса), если вы не укажите обратного.
Возьмем для примера таблицу utV (база данных «Окраска»), содержащую всего три столбца — v_id (идентификатор баллончика — первичный ключ), v_name (название баллончика) и v_color (цвет краски в баллончике). Как уже говорилось, на первичном ключе автоматически создается кластеризованный индекс, есть он и у нашей таблицы.
Рис.1 Кластеризованный индекс
Выполним три следующих запроса и посмотрим на планы их выполнения.
select v_id from utv;
select * from utv;
select v_name from utv;
Рис.2 Сканирование кластеризованного индекса
Как вы можете убедиться, все эти запросы имеют одинаковый план выполнения, а именно – сканирование кластеризованного индекса, вне зависимости от того, данные всех столбцов нам нужны, или нет. Тут нет ничего удивительного, т.к. кроме кластеризованного индекса у нас ничего нет, а данные нужны из всех строк.
Давайте теперь заменим кластеризованный индекс некластеризованным, удалив сначала кластеризованный первичный ключ, и создав затем некластеризованный. Предварительно нам потребуется удалить внешний ключ из таблицы utB, который ссылается на первичный ключ таблицы utV:
alter table utB
drop constraint FK_utB_utV; —удаляем внешний ключ
alter table utV
drop constraint PK_utV; — удаляем первичный ключ (кластеризованный индекс)
go
alter table utV
add constraint PK_utVn primary key nonclustered (v_id asc);Выполним теперь те же запросы, чтобы увидеть разницу:
Рис.3 Использование некластеризованного индекса
В двух последних случаях сканирование индекса изменилось на сканирование таблицы. И только в первом случае используется сканирование индекса. Это объясняется тем, что затребованные данные находятся в индексе (ключ индекса), и нет необходимости обращаться к самой таблице. Оптимизатор выбирает эту стратегию доступа, поскольку индекс имеет меньший размер, чем индексируемая таблица, и будет просматриваться быстрей, даже в нашем случае, когда нужны все его строки.
Рассмотрим теперь запросы на получение конкретной строки:
select v_id from utv where v_id = 15;
select v_name from utv where v_id = 15;Для выполнения первого запроса оптимизатором теперь выбирается поиск по индексу (index seek) – наиболее эффективная операция, поскольку это прямой доступ к данным с использованием структуры B-Tree. План для второго запроса помимо поиска по индексу содержит еще две операции. Это связано с тем, что мы в запросе хотим получить имя баллончика, а не его ИД, а в индексе содержится только v_id. Поэтому после нахождения строки с v_id = 15 выполняется обращение к таблице по RID-указателю, содержащемуся в индексе. Это прямая операция, которая называется поиском закладки (lookup). Последняя операция выполняет соединение полученных результатов. Тут следует заметить, что план читается справа налево.
Рис. 4 Поиск по индексу
Можно избежать лишней операции – поиска закладки, если включить в индекс требуемые запросом данные. Для этого мы удалим индекс PK_utVn и создадим вместо него новый.
alter table utV
drop constraint PK_utVn; — удаляем индекс
/* создаем уникальный индекс (не первичный ключ) с включенным столбцом */
create unique nonclustered index IX_utVi on utV(v_id asc) include(v_name);Посмотрим план выполнения второго запроса.
Рис. 5 Поиск по индексу с включенным столбцом
Как видим, теперь план не отличается от плана выполнения первого запроса.
Следует отметить, что последний индекс не является составным, т.е. индексом, построенным по двум столбцам – v_id, v_name>. Составной индекс для данного запроса использовался бы аналогичным образом, но есть одно важное отличие. При изменении данных, в частности, значений v_name составной индекс пришлось бы перестраивать, а индекс с включенным столбцом – нет, поскольку по включенному столбцу не выполняется физическое упорядочивание. Таким образом, накладные расходы на поддержку индексов в случае индекса с включенными столбцами будут ниже. Преимущества же составного индекса мы рассмотрим позже.
Рассмотрим, наконец, самый плохой вариант – отсутствие индексов.
drop index IX_utVi on utV; — удаляем индекс
go
select v_id from utv where v_id = 15;
select v_name from utv where v_id = 15;
Рис.6 Сканирование таблицы при отсутствии индексов
Как и ожидалось, оба запроса выполняются сканированием таблицы, хотя должна быть получена максимум одна строка. Причем поиск будет продолжен даже после нахождения искомой строки, поскольку система не имеет информации о том, что значение v_id уникально.
Для сравнения планов выполнения давайте вернем индекс по столбцу v_id
alter table utV
add constraint PK_utVn primary key nonclustered (v_id asc);
и выполним следующие запросы:
select v_id from utv where v_id = 15;
select v_id from utv where v_name= ‘Balloon # 15’;
Рис.7 Выборка по столбцу без индекса
Эти запросы возвращают одно и то же, но в первом из них поисковым аргументом является столбец, имеющий индекс, а во втором – нет. Как и следовало ожидать, для первого запроса используется план с поиском по индексу, а для второго – сканирования таблицы. Не обращайте внимания на то, что стоимости планов выполнения запроса (cost) оцениваются оптимизатором одинаково. Причина в незначительном количестве данных, которые что в одном, что в другом случае, целиком будут находиться в оперативной памяти, и количество дисковых операций, которые оптимизируются сервером, будет эквивалентно. Это хороший пример того, что при оптимизации запросов нужно полагаться не на оценку стоимости, а читать план. В данном случае потенциальной потери производительности можно избежать, создав индекс на столбце v_name.
Давайте так и поступим, и выполним предыдущие запросы.
create index IX_utVname on utV(v_name);
Рис. 8 Игнорирование неуникального индекса
Неожиданно? Мы ожидали, что будет использован поиск по индексу, а затем поиск закладки для нахождения значения v_id. Однако оптимизатор не использовал индекс на столбце v_name. Почему?
Причина, как я думаю, заключается в том, что индекс на столбце v_name не является уникальным. Т.е. оптимизатор полагает, что значений, отвечающих предикату v_name= ‘Balloon # 15’ может быть несколько. Тогда для каждого такого значения потребуется поиск закладки. Поскольку данных в таблице немного, оптимизатор решает не оценивать план с использованием индекса на основе имеющейся статистики о распределении значений в столбце v_name, а пойти по простому пути, сэкономив на оценке плана. Давайте проверим это предположение, создав уникальный индекс, полагая, что одинаковых названий нет и быть не должно.
drop index IX_utVname on utV;
create unique index IX_utVname on utV(v_name);
Рис.9 Использование уникального индекса
Теперь результат согласуется с нашими ожиданиями.
Разница между кластерным и некластеризованным индексом
Сгруппированные и разгруппированные индексы являются одноуровневыми типами индексов сортировки, где кластерный индекс определяет, как данные хранятся в строках таблицы. С другой стороны, некластеризованный индекс хранит данные в одном месте, а индексы хранятся в другом месте. Кроме того, каждая таблица может иметь только один сгруппированный индекс. В отличие от этого, в случае некластеризованного индекса, таблица может иметь несколько некластеризованных индексов.
Индексы по существу необходимы для эффективного обеспечения ограничений целостности и эффективной обработки запросов и транзакций. Они создаются в таблицах и представлениях. Например, индексы, используемые в книгах, облегчают пользователю быстрый доступ к содержимому книги, аналогично, у нас есть индексы в SQL.
Сравнительный график
Основа для сравнения индекса сгруппированный индекс не сгруппирован Бейсик Определяет порядок хранения строк в таблице в целом. Определите порядок хранения строк в таблице с помощью отдельной физической структуры. Количество индексов, разрешенных на одну таблицу Просто кластерный индекс Несколько индексов не сгруппированы Доступ к данным быстрее Медленнее по сравнению с кластерным индексом Дополнительное дисковое пространство ненужный Требуется хранить индексы отдельно. Определение кластерного индекса
кластерный индекс Он в основном используется для сортировки строк в таблице. Таблица может иметь только один сгруппированный индекс, потому что строки таблицы могут быть упорядочены в одном порядке, но есть способы создать Композитный кластерный индекс , Столбцы включены в кластеризованный индекс, и логический или индексированный порядок значений ключа такой же, как физически сохраненный порядок соответствующих строк. Если для данных нет кластеризованного индекса, он сохраняется в свая ,
Доступ к реестру в куче занимает много времени, когда каждая запись в таблице сканируется для доступа к нужным данным. При сканировании таблицы не было возможности узнать, есть ли еще совпадения или нет. Поэтому этот метод был очень неэффективным.
При использовании кластерного индекса доступ к данным более быстрый и систематический, если таблица размещена в некотором порядке. Он определяется в поле сортировки таблицы. Ваш поисковый ключ используется указывает на последовательный макет файла или таблицы. Кластерный индекс создается автоматически, когда первичный ключ для стола.