16. Индексы - KattyOG/Database GitHub Wiki

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

Индексы в PostgreSQL — специальные объекты базы данных, предназначенные в основном для ускорения доступа к данным.

Несмотря на все различия между типами индексов (называемыми также методами доступа), в конечном счете любой из них устанавливает соответствие между ключом (например, значением проиндексированного столбца) и строками таблицы, в которых этот ключ встречается. Строки идентифицируются с помощью TID (tuple id), который состоит из номера блока файла и позиции строки внутри блока. Тогда, зная ключ или некоторую информацию о нем, можно быстро прочитать те строки, в которых может находиться интересующая нас информация, не просматривая всю таблицу полностью.

Важно понимать, что индекс, ускоряя доступ к данным, взамен требует определенных затрат на свое поддержание. При любой операции над проиндексированными данными — будь то вставка, удаление или обновление строк таблицы, — индексы, созданные для этой таблицы, должны быть перестроены, причем в рамках той же транзакции. Заметим, что обновление полей таблицы, по которым не создавались индексы, не приводит к перестроению индексов; этот механизм называется HOT (Heap-Only Tuples).

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

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

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

Создание индекса

CREATE INDEX CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX имя- индекса
ON имя-таблицы-или-представления ( список-столбцов ) [ INCLUDE (список-столбцов) ]
[WITH список-опций]
[ ON файловая-группа ]

В SQL Server индексы хранятся в виде сбалансированных деревьев. Представление индекса в виде сбалансированного дерева означает, что стоимость поиска любой строки остается относительно постоянной, независимо от того, где находится эта строка. Сбалансированное дерево состоит из:

  • корневого узла (root node), содержащего одну страницу,
  • нескольких промежуточных уровней (intermediate levels), содержащих дополнительные страницы, и
  • листового уровня (leaf level). На страницах листового уровня находятся отсортированные элементы, соответствующие индексируемым данным. По мере добавления данных в таблицу индекс будет разрастаться, но по-прежнему оставаться в форме сбалансированного дерева.

В SQL Server существует два типа индексов:

  • кластерные индексы;
  • некластерные индексы, которые включают:
    ** некластерные индексы на основе кучи;
    ** некластерные индексы на основе кластерных индексов.

Кластерные индексы
В кластерном индексе таблица представляет собой часть индекса, или индекс представляет собой часть таблицы в зависимости от вашей точки зрения. Листовой узел кластерного индекса – это страница таблицы с данными. Поскольку сами данные таблицы являются частью индекса, для таблицы может быть создан только один кластерный индекс. В SQL Server кластерный индекс является уникальным индексом по определению.

Некластерные индексы на основе кучи
В листьях некластерного индекса на основе кучи хранятся указатели на строки данных. Указатель строится на основе идентификатора файла (ID), номера страницы и номера строки на странице. Весь указатель целиком называется идентификатором строки (RID).

Некластерные индексы, основанные на кластерных таблицах
В листьях некластерного индекса, основанного на кластерных таблицах, хранятся указатели на корневые узлы кластерных индексов. Поиск в таком индексе состоит из двух этапов:

  • поиск в некластерном индексе
  • поиск в кластерном индексе.

Индексы, создаваемые вместе с ограничениям
Такой тип индексов часто называют «связанными индексами». Связанные индексы создаются при добавлении одного из следующих двух типов ограничений:

  • ограничения первичного ключа (PRIMARY KEY);
  • ограничения уникальности (UNIQUE).

Пример создание индекса

CREATE TABLE [Person].[Address](
[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
...
CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED
([AddressID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]