Индексы бд

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

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

Add a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *