Индексы бд

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

Индекс — это ключ, построенный из одного или нескольких столбцов в базе данных, который ускоряет выборку строк из таблицы или представления. Этот ключ помогает базе данных, такой как 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 для вставки, обновления и удаления.
• Кластерный индекс занимает больше времени для обновления записей при изменении полей в кластерном индексе.
• Конечные узлы в основном содержат страницы данных в кластерном индексе.

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

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

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

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

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

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

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

Индекс помогает ускорить для запросов SELECT и предложения WHERE, но это замедляет ввод данных, с заявлениями UPDATE и INSERT. Индексы могут быть созданы или удалены без влияния на данные.

Создание индекса предполагает заявление CREATE INDEX, которое позволяет назвать индекс, чтобы указать таблицу и какой столбец или столбцы индексировать и указать, является ли индекс в порядке возрастания или убывания.

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

Команда CREATE INDEX

Основной синтаксис CREATE INDEX выглядит следующим образом:

CREATE INDEX index_name ON table_name;

Одноколоночные индексы

Индекс для одного столбца создается на основе только одного столбца таблицы. Базовый синтаксис выглядит следующим образом.

CREATE INDEX index_name ON table_name (column_name);

Уникальные индексы

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

CREATE UNIQUE INDEX index_name on table_name (column_name);

Составные индексы

Составной индекс является индексом для двух или более столбцов таблицы. Его основной синтаксис выглядит следующим образом.

CREATE INDEX index_name on table_name (column1, column2);

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

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

Неявные индексы

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

Команда DROP INDEX

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

Базовый синтаксис выглядит следующим образом:

DROP INDEX index_name;

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

Когда следует избегать индексов?

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

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

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

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

Рассмотрим теорию индексов.

• SQL Server хранит данные на страницах размером 8 килобайт – 8,060 байт

• Страницы принадлежащие объекту(например таблице) связаны в двунаправленный список

• Первые 8 страниц «объекта» хранятся в смешанных участках. После этого данные хранятся только в унифицированных участках.

• 8 страниц группируются в «участки». Смешаные участки хранят данные из разных «объектов». Унифицированные участки хранят данные одного «объекта»

• SQL Server использует страницы именуемыми — «карты размещения индексов» (Index Allocation Map) или кратко — IAM для определения страниц принадлежащих «объекту»

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

Кучи подходят для хранения небольшого количества данных.

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

Кластеризованный индекс хранит в своих узлах-листьях реальные строки данных.

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

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

• В SQL Server индексы организованы в виде сбалансированных деревьев. Каждая страница в сбалансированном дереве индекса называется узлом индекса.

< >Верхний узел сбалансированного дерева называется корневым. Узлы нижнего уровня индекса называются конечными. < >Все уровни индекса между корневыми и конечными узлами называются промежуточными. < >В кластеризованном индексе конечные узлы содержат страницы данных базовой таблицы. < >На страницах индекса корневого и промежуточного узлов находятся строки индекса. < >Каждая строка индекса содержит ключевое значение и указатель либо на страницу промежуточного уровня сбалансированного дерева, либо на строку данных на конечном уровне индекса. < >Страницы на каждом уровне связаны в двунаправленный список.На схеме — кластеризованный индекс выглядит в виде B-дерева, где хранятся реальные строки данных таблицы в отсортированном порядке в узлах-листьях.

Т.Е. данные будут храниться так:

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

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

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

Т.к. кластеризованный индекс хранит реальные данные, нельзя создать более одного кластеризованного индекса в таблице.

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

Некластеризованный индекс не содержит реальных данных таблицы в узлах-листьях. Узлы-листья содержат один из двух типов информации о местоположении строк данных.

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

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

Возможно создать до 249 некластеризованных индексов на одну таблицу.

Схема 1