Кластерный индекс ms sql: Основы индексов в Microsoft SQL Server | Info-Comp.ru

Содержание

Олег Филиппов — LiveJournal

Индекс лишним не бывает? Чем больше индексов тем лучше? А не проиндексировать ли это измерение на всякий случай?
Если подобные вопросы иногда возникают в вашей голове, то эту статью прочитать было бы весьма полезно.

Итак, традиционное мнение что «Индекс это хорошо, а блокировка это плохо». Часто бывает неверным до противоположности.
Вот эта картинка, которую я выбрал для заголовка статьи, очень часто напоминает многие базы 1С, которые неожиданно начинают «быстро рости»
и «тормозить».

Но прежде чем перейти к сути, придётся немного покопаться в теории.

Что такое индексы, для чего они нужны, какие они бывают?

Далее будет немного теории, объясняемой простым языком. Если все эти вопросы вам давно известны и вы считаете что нет необходимости в таком
примитивном объяснении то вы наверное слишком редко заглядывали в доработки конфигураций, да что и говорить в типовые конфигурации 3-4 летней давности,
в любом случае этот раздел всегда можно пропустить.

Итак, как работает индекс? Наверное все в детстве играли в игру «угадай число»? Кто-то загадал число от 1 до 50 а вам его нужно угадать за наименьшее число вопросов.
Как вы будуте его угадывать? Перебором: «Это 1? Это 2? Это 3?». Скорее всего вы будете задавать вопросы вида: «Оно больше 25?». И только когда вариантов останется около 2-3 вы будете перебирать возможные.
Т.е. поступите примерно как показано на картинке:

В ВУЗ-е мы уже узнаём что подобные структуры называются графами, вернее даже разновидностью графа — деревьями. Бывают ещё более вырожденные разновидности деревьев, так называемые B-деревья.
Собственно, они и лежат в основе большей части индексов.

Хотя конечно не большей. Индексы делятся на два типа: кластерные и не кластерные.
Конечно можно вспомнить что есть битовые индексы, функциональные индексы, XML индексы.
Но принципиальным отличаем будем считать физическую организацию и принцип работы.

Кластерный индекс — это по сути дела не индекс, а определенным образом организованная таблица. В Oracle его, к примеру, вообще называют Index Organized Table или IOT.

Некластерный индекс — это отдельная структура, как правило вида B-дерева, которая создаётся дополнительно к основной таблице.

Если вы сейчас думаете что эти два вида индексов придумали ИТ специалисты, то вы сильно ошибаетесь.

Вот так выглядит кластерный индекс, который появился ещё до появления компьютера:

А как то вот так, соответственно некластерный:

Данная аналогия оказывается на удивление полной.
Каким образом вам бы удобнее было искать? Сразу выбрать страницу, начиная с той буквы которая указана на срезанных полях
или сначала заглянуть в предметный указатель, потом узнать там номер страницы и уже искать нужную страницу по номеру?
Конечно первый способ куда удобнее. Но вот только не очень он гибкий… обычно на полях ставят только буквы.
Нельзя написать целое слово или несколько слов, в то время как в предметном указателе их можно организовать как угодно.

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

Теперь давайте посмотрим уже более детально, применительно к MS SQL Server:

В MS SQL существуют 2 физических операции Index Seek и Index Scan. Index Seek — это хорошо, Index Scan — плохо.
Index seek означает просмотр индекса в порядке упорядочевания, либо по B-Дереву, Index Scan — обычная операция просмотра всех
записей таблицы, аналогичная всем известной Table Scan. Чаще всего данная операция присутствует в случае «неполного покрытия» индекса,
Если в индексе, к примеру, есть поле «Контрагент, Номенклатура» а отобрать записи надо по «контрагенту, номенклатуре и заказу покупателя».
В этом случае в плане запроса MS SQL можно будет увидеть что-то вида:

Т.е. сначала по индексу были выбраны записи соответствующие покрытию, а остаток записей были получены полным последовательным просмотром таблицы.

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

Тут есть ещё одна интересная операция — RID Lookup, занимающая целых 50% времени — столько же, сколько поиск по индексу.

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

Но в данном случае есть ещё одна специфика — это слово «Heap» указанное в скобках операции. Но о нём далее.

Чем плохи индексы?

1) Накладные затраты при записи данных

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

Затраты на запись или чтение в транзакции намного «дороже» внетранзакционных издержек. Дело в том, что запись может вестись строго последовательно,
и время на фиксацию изменений в БД сократить достаточно сложно. Более мощное оборудование тут не всегда помогает.

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

2) Накладные затраты на обслуживание индексов

При интенсивной записи данных в таблицу данные индексов к ней не всегда распологаются на той странице на которой должны. Появляются «пропуски», физическая структура
индексов становится не эффективной. Поэтому иногда бывает необходимо производить дефрагментацию индексов. Производительность запросов к СУБД во время дефрагментации,
соответственно падает. Есть ещё процесс полного перестроения индексов — но в современных версиях MS SQL необходимости выполнения данной операции по регламенту нет.

3) Влияние индексов на размер базы
Не самое страшное последствие, но так или иначе если база весьит 150-200 ГБ, то об этом надо уже задуматься. Для средней OLTP базы размер индексов как правило превышает объём самой базы.
Не верите? Вполне можете воспользоваться какой-либо обработкой вроде этой: http://infostart.ru/public/19463/ и посмотреть сколько же в вашей базе места занимают индексы.

4) Затраты на создание и поддержание актуальной статистики
Статистику в базе нужно регулярно обновлять при интенсивных операциях вставки и обновления. Это занимает вычислительные ресурсы, хоть и не влияет непосредственно на процесс.
Неактуальная статистика может привести к проблемам производительности системы.

Но это не значит, что индексы — это плохо, без них СУБД были бы бесполезны. Плохи индексы, которые не используются.

Как оптимизатор выбирает какое индекс ему использовать? (статистика, плотность, селективность, кардинальность)

Итак, про то что «статистика должна быть» и «её нужно обновлять» слышали наверное все.
Многие длаже знают что нужно обновлять статистику наизусть помнят запрос:

EXEC sp_MSForEachTable ‘UPDATE STATISTICS ? WITH FULLSCAN;’

Главное не забыть, что хитрый MS SQL кэширует планы запроса, котьорые он уже раз посчитал. Даже если статистика изменится,
для того чтобы что-то заработало по-другому надо бы выполнить:

DBCC FREEPROCCACHE

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

Сделаем в БД простейшую таблицу сделующего вида:

Потом выполним:

DBCC SHOW_STATISTICS(‘TAB’, NAME)

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

Сейчас нам интересен в этой таблице столбец Density — Плотность записей. Плотность рассчитывается как

Плотность = Число дубликатов в колонке / Общее число записей в таблице

И является одним из самых выжных статистических показателей данных в таблице. Очевидно, что чем меньше плотность записей, тем эффективнее можно будет воспользоваться индексом.
А при плотности 0.5 как в приведённом примере, индекс в принципе вообще не нужен. Чем ниже в таблице значение Density, тем более правильно спроектирована данная таблица.
Для СУБД лучше всего таблицы с уникальными записями. Обратите внимание на столбец All density — его значение уже 0.25. Это означает что в таблице есть ещ одна колонка. Для MS SQL
этот показатель важен когда вы пишите «СГРУППИРОВАТЬ ПО».

Но плотности записей оптимизатору недостаточно. Основная задача оптимизатора — «догадаться» сколько строк вернёт запрос.
Допустим у нас в базе куча складов по всем регионам — небольшие торговые точки + виртуальные склады. И есть один центральный склад.
Плотность по складу будет достаточно неплохая. Но вот число строк по центральному складу которое вернёт запрос и по региональному будет различаться в тысячи раз.

Для этого есть показатель селективности:

Селективность=число строк удовлетворяющих предикату/всего строк в таблице

предикат — определенное условие.
Из картинки выше селективность определяется в третьей таблице. Там указан «ключ» — RANDE_HI_KEY и соответственно количество строк ему соответствующее… и количество значений ему соответствующее в таблице.

Ну и остался последний показатель статистики:

Кардинальность — это и есть предположительное число строк которое вернёт запрос

В каждом элементе плана запроса этот показатель присутствует.
Если навести на элемент плана запроса курсор, то его можно увидеть во всплывающей подсказке примерно так:

Итак, если итоговый запрос возвращает половину таблицы или около того, то индекс тут совсем не нужен.
Вспомним УТ 10.2, 10.1, даже 10.3 по-моему… В каждом документе был индекс по полю «Организация». В последних версиях его нет. Как думаете почему?

Индекс также бесполезен, если оптимизатор MS SQL решает что итоговый запрос вернёт половину таблицы или вроде того.
Почему MS SQL так может решить вроде разобрались.

Теперь самое время разобраться какие индексы есть в 1С:

Всё просто. Все объекты 1С делятся на ссылочные (у которых есть ссылка и, соответственно, GUID) и табличные (регистры).
У всех таблиц 1С есть кластерный индекс.
У ссылочных кластерный индекс создаётся по ссылке (GUID) — это самая быстрая выборка которая только может быть.
У табличных кластерный индекс создаётся по всем изменениям. Что тоже предельно логично. И конечно этот индекс уникальный.

А как же затраты на запись? Зачем создавать кластерный индекс везде?
Дело тут в том, что так работает MS SQL. В стандартной таблице просто должен быть кластерный индекс.
Без него выборка из этой таблицы будет приводить к тому что операция поиска записи по индексу (как было на картинке выше) будет занимать непростительно много времени.
В MS SQL таблицы без кластерного индекса называеют Heap Table — куча. Что примерно соответствует их физической организации.
Тем не менее такие таблицы могут быть важны. Если у вас 99% операций в этой таблице — запись, а для анализа этих данных вы, к примеру, применяете отдельное OLAP решение.
Поэтому возможность убирать кластерные индексы из таблиц очень бы не помешала разработчикам 1С.

Когда вы ставите у какого либо реквизита, ресурса или измерения объекта 1С признак «индексировать» — создаётся дополнительный или обновляется существующий не кластерный индекс для этой таблице.

Включая реквизиты объекта в критерий отбора, вы так же создаёте по ним отдельный индекс.

Это лишь общие правила по которым платформа создаёт индексы на уровне СУБД. В каких то деталях они могут отличаться, тем более в разных версиях платформы.

К счастью, 1С не пожадничали и дали нам инструмент чтобы структуру БД просматривать — я про фукнкцию «ПолучитьСтруктуруХранения()».
Умельцы этой функцией воспользовались и сделали для неё неплохой интерфейс, которым можно вопспользоваться чтобы точно посмотреть какие же есть индексы у таблицы:

http://infostart.ru/public/147147/

Зачем я всё это прочитал?

Что делать простому разработчику 1С?

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

1) Делайте индексы, покрывающие предикаты, если данный запрос планируется к использованию достаточно часто.
Но не забудьте что уже существуют кластерные индексы.

2) Если находится индекс уже покрывающий предикат в запросе не создавайте нового индекса.

3) Если индекс покрывает почти всё условие запроса — оцените число записей которое придётся перебрать СУБД при
данной выборке, если оно невелико (менее нескольких тысяч) — не создавайте нового индекса

4) Упределете селективность и/или плотность записей в выборке так как их определит в данном случае оптимизатор.
Если в итоговой выборке получится много записей по отношению к общему числу записей в таблице — не создавайте индекса, он лишний

5) Когда пишите запрос, думайте о том как его будет анализировать оптимизатор, сможет ли он корректно посчитать ориентировочное число строк возвращаемое каждой частью запроса.

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

Tags: 1С, ms sql, sql

18) Кластерный против некластеризованного индекса

Что такое индекс?

Индекс – это ключ, построенный из одного или нескольких столбцов в базе данных, который ускоряет выборку строк из таблицы или представления. Этот ключ помогает базе данных, такой как Oracle, SQL Server, MySQL и т. Д., Быстро найти строку, связанную со значениями ключа.

Два типа индексов:

Кластерный индекс
Некластерный индекс

В этом уроке вы узнаете:

Что такое индекс?
Что такое кластерный индекс?
Что такое некластеризованный индекс?
Характеристика кластерного индекса
Характеристики некластеризованных индексов
Пример кластерного индекса
Пример некластеризованного индекса
Различия между кластерным индексом и некластеризованным индексом
Преимущества кластерного индекса
Преимущества некластеризованного индекса
Недостатки кластерного индекса
Недостатки некластеризованного индекса

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

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

Кластерный индекс определяет порядок, в котором данные хранятся в таблице и могут быть отсортированы только одним способом. Таким образом, для каждой таблицы может быть только один кластеризованный индекс. В РСУБД, как правило, первичный ключ позволяет создавать кластерный индекс на основе этого конкретного столбца.

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

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

Например, книга может иметь более одного индекса, один в начале, который отображает содержание книги, а второй индекс показывает индекс терминов в алфавитном порядке.

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

Характеристика кластерного индекса

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

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

Хранить только значения ключей
Указатели на строки кучи / кластерного индекса
Позволяет вторичный доступ к данным
Мост к данным
Операции сканирования индекса и поиска индекса
Вы можете создать некластеризованный индекс для таблицы или представления
Каждая строка индекса в некластеризованном индексе хранит значение некластеризованного ключа и локатор строк.

Пример кластерного индекса

В приведенном ниже примере SalesOrderDetailID является кластеризованным индексом. Пример запроса для получения данных

SELECT CarrierTrackingNumber, UnitPrice
FROM SalesData
WHERE SalesOrderDetailID = 6

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

В следующем примере некластеризованный индекс создается для OrderQty и ProductID следующим образом

CREATE INDEX myIndex ON
SalesData (ProductID, OrderQty)

Следующий запрос будет получен быстрее по сравнению с кластерным индексом.

SELECT Product ID, OrderQty
FROM SalesData
WHERE ProductID = 714

Различия между кластерным индексом и некластеризованным индексом

параметры	кластерный	Некластерированных
Использовать для	Вы можете сортировать записи и физически хранить кластерный индекс в памяти в соответствии с порядком.	Некластеризованный индекс помогает вам создать логический порядок для строк данных и использует указатели для физических файлов данных.
Метод хранения	Позволяет хранить страницы данных в конечных узлах индекса.	Этот метод индексации никогда не сохраняет страницы данных в конечных узлах индекса.
Размер	Размер кластерного индекса довольно велик.	Размер некластеризованного индекса невелик по сравнению с кластеризованным индексом.
Доступ к данным	Быстрее	Медленнее по сравнению с кластерным индексом
Дополнительное дисковое пространство	Не требуется	Требуется хранить индекс отдельно
Тип ключа	По умолчанию первичные ключи таблицы являются кластерным индексом.	Его можно использовать с уникальным ограничением на таблицу, которое действует как составной ключ.
Главная особенность	Кластерный индекс может повысить производительность поиска данных.	Он должен быть создан на столбцах, которые используются в соединениях.

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

Плюсы / преимущества кластерного индекса:

Кластерные индексы являются идеальным вариантом для диапазона или группы с запросами типа max, min, count
В этом типе индекса поиск может идти прямо к определенной точке данных, чтобы вы могли продолжать последовательное чтение оттуда.
Метод кластеризованного индекса использует механизм определения местоположения для определения позиции индекса в начале диапазона.
Это эффективный метод для поиска диапазона, когда запрашивается диапазон значений ключа поиска.
Помогает минимизировать передачу страниц и максимизировать попадания в кэш.

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

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

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

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

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

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

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

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

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

КЛЮЧЕВАЯ РАЗНИЦА

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

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

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

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

Индексы SQL Server можно разделить на два основных типа:

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

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

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

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

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

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

CREATE DATABASE Больница

CREATE TABLE Пациенты
(
id INT PRIMARY KEY,
имя VARCHAR(50) NOT NULL,
пол VARCHAR(50) NOT NULL,
age)

Приведенный выше сценарий создает фиктивную базу данных Hospital. В базе 4 столбца: id, имя, пол, возраст. Столбец id является столбцом первичного ключа. Когда приведенный выше сценарий выполняется, для столбца id автоматически создается кластеризованный индекс. Чтобы увидеть все индексы в таблице, вы можете использовать хранимую процедуру «sp_helpindex».

USE Больница EXECUTE sp_helpindex Пациенты

Вот результат:

Вы можете увидеть имя индекса, описание и столбец, для которого создан индекс. Если вы добавите новую запись в таблицу «Пациенты», она будет храниться в порядке возрастания значения в столбце id. Если первая запись, которую вы вставляете в таблицу, имеет идентификатор три, запись будет сохранена в третьей строке, а не в первой строке, поскольку кластеризованный индекс поддерживает физический порядок.

Пользовательские кластерные индексы

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

USE Больница ALTER TABLE Пациенты DROP CONSTRAINT PK__Patients__3213E83F3DFAFAAD GO

Следующий сценарий создает настраиваемый индекс «IX_tblPatient_Age» в столбце age таблицы «Patients». Благодаря этому индексу все записи в таблице «Пациенты» будут храниться в порядке возрастания возраста.

use Hospital CREATE CLUSTERED INDEX IX_tblPatient_Age ON Пациенты (возраст ASC)

Теперь добавим несколько фиктивных записей в таблицу «Пациенты», чтобы увидеть, действительно ли они вставлены в порядке возрастания возраста:

3 ЕГЭ Больница

ВСТАВИТЬ В ПАЦИЕНТЫ

ЗНАЧЕНИЯ
(1, «Сара», «Женщина», 34),
(2, «Джон», «Мужчина», 20),
(3, «Майк», «Мужчина» , 54),
(4, ‘Ана’, ‘Женщина’, 10),
(5, ‘Ник’, ‘Женщина’, 29)

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

SELECT * FROM Пациенты

Вот результат:

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

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

Чтобы создать некластеризованный индекс, вы должны использовать оператор «CREATE NONCLUSTERED». Остальной синтаксис остается таким же, как синтаксис для создания кластеризованного индекса. Следующий сценарий создает некластеризованный индекс «IX_tblPatient_Name», который сортирует записи в порядке возрастания имени.

use Hospital CREATE NONCLUSTERED INDEX IX_tblPatient_Name ON Пациенты (имя ASC)

Приведенный выше сценарий создаст индекс, который содержит имена соответствующих пациентов, как показано ниже:0003

Имя	Адрес записи
Ана	Адрес записи
Джон	Адрес записи
Майк	Адрес записи
Ник	Адрес записи
Сара	Адрес записи

Здесь «Адрес записи» в каждой строке является ссылкой на фактические записи таблицы для Пациентов с соответствующими именами.

Например, если вы хотите получить возраст и пол пациента по имени «Майк», база данных сначала будет искать «Мик» в некластеризованном индексе «IX_tblPatient_Name», а из некластеризованного индекса будет извлечена фактическая ссылку на запись и будет использовать ее для возврата фактического возраста и пола пациента по имени «Майк». медленнее для операций поиска. Однако для операций INSERT и UPDATE некластеризованные индексы работают быстрее, поскольку порядок записей нужно обновлять только в индексе, а не в фактической таблице.

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

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

1. Количество индексов

Это довольно очевидно. Если вам нужно создать несколько индексов в базе данных, используйте некластеризованный индекс, поскольку может быть только один кластеризованный индекс.

2. Операции SELECT

Если вы хотите выбрать только значение индекса, которое используется для создания и индексирования, некластеризованные индексы работают быстрее. Например, если вы создали индекс для столбца «имя» и хотите выбрать только имя, некластеризованные индексы быстро вернут это имя.

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

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

3. Операции INSERT/UPDATE

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

4. Место на диске

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

5. Окончательный вердикт

Как правило, каждая таблица должна иметь хотя бы один кластеризованный индекс, предпочтительно для столбца, который используется для ВЫБОРА записей и содержит уникальные значения. Столбец первичного ключа — идеальный кандидат для кластеризованного индекса.

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

Включите JavaScript для просмотра комментариев с помощью Disqus.

Разница между кластеризованным и некластеризованным индексом

Ричард Петерсон

Часы

Обновлено 24 сентября 2022 г.

Ключевое различие между кластеризованным и некластеризованным индексом

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

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

Что такое индекс?

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

Два типа индексов:

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

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

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

Кластерный индекс определяет порядок хранения данных в таблице, которая может быть отсортирована только одним способом. Таким образом, для каждой таблицы может быть только один кластеризованный индекс. Обычно в СУБД первичный ключ позволяет создать кластеризованный индекс на основе этого конкретного столбца.

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

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

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

Характеристика кластерного индекса

Хранение данных по умолчанию и отсортированных данных
Использовать только один или несколько столбцов для индекса
Помогает хранить данные и индексировать вместе
Фрагментация
Операции
Сканирование кластеризованного индекса и поиск по индексу
Поиск ключа

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

Хранить только ключевые значения
Указатели на строки кучи/кластеризованного индекса
Разрешает доступ к вторичным данным
Мост к данным
Операции сканирования индекса и поиска по индексу
Вы можете создать некластеризованный индекс для таблицы или представления
Каждая строка индекса в некластеризованном индексе хранит значение некластеризованного ключа и указатель строки

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

Параметры	Кластерный	Некластеризованный
Используется для	Вы можете сортировать записи и хранить кластеризованный индекс физически в памяти в соответствии с порядком.	Некластеризованный индекс помогает создать логический порядок для строк данных и использует указатели для физических файлов данных.
Способ хранения	Позволяет хранить страницы данных в листовых узлах индекса.	Этот метод индексирования никогда не сохраняет страницы данных в конечных узлах индекса.
Размер	Размер кластеризованного индекса слишком велик.	Размер некластеризованного индекса мал по сравнению с кластеризованным индексом.
Доступ к данным	Быстрее	Медленнее по сравнению с кластеризованным индексом
Дополнительное место на диске	Не требуется	Требуется хранить индекс отдельно
Тип ключа	По умолчанию первичные ключи таблицы являются кластеризованным индексом.	Может использоваться с ограничением уникальности таблицы, которая действует как составной ключ.
Основной элемент	Кластеризованный индекс может повысить производительность извлечения данных.	Он должен быть создан для столбцов, которые используются в соединениях.

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

 SELECT CarrierTrackingNumber, UnitPrice
ИЗ данных о продажах
ГДЕ SalesOrderDetailID = 6

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

В приведенном ниже примере некластеризованный индекс создается для OrderQty и ProductID следующим образом:

 CREATE INDEX myIndex ON
Данные о продажах (ProductID, OrderQty)

Следующий запрос будет получен быстрее по сравнению с кластеризованным индексом.

 ВЫБЕРИТЕ ID продукта, OrderQty
ИЗ данных о продажах
ГДЕ ProductID = 714

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

Плюсы/преимущества кластеризованного индекса:

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

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

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

Некластеризованный индекс помогает быстро извлекать данные из таблицы базы данных.
Помогает избежать накладных расходов, связанных с кластеризованным индексом
Таблица может иметь несколько некластеризованных индексов в СУБД. Таким образом, его можно использовать для создания более одного индекса.

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

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

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