Индексы в SQL: создание, виды и как работают

При работе с языком SQL (язык структурированных запросов) можно столкнуться с объектом, который называется «индекс». 

В данной статье мы поговорим об индексах, а также рассмотрим практические примеры при работе с индексами.

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

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

Принцип работы индексов

Индекс представляет собой структуру, которая хранится на диске и предназначена для столбцов таблицы, а также для объектов типа представления (Views).

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

Главная особенность индексов заключается в скорости поиска данных, которая достигается за счёт того, что индекс состоит из структуры (структуры сбалансированного дерева), которая оптимизирована под поиск.

Также стоит отметить, что при добавлении (вставке) новых данных или при удалении старых данных, древовидная структура индекса будет пересчитываться каждый раз. Отсюда можно сделать вывод: чем больше данных и индексов хранится в базе данных, тем больше деревьев индекса будет пересчитываться. Предположим, у нас есть таблица с пятью индексами, в которых суммарно содержатся 10000 записей. При каждом новом добавлении данных заново будут пересчитаны все пять индексов с 10000 записей.

Типы индексов

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

• Уникальный (Unique index) — все значения встречаются только один раз. Если в столбец с уникальным индексом попробовать добавить неуникальное значение, то возникнет ошибка.
• Неуникальный (Non-unique index) — индекс, при котором значения могут повторяться.
• Простой (Simple index) — индекс, состоящий из одного поля.
• Составной (Composite Index) — индекс, который строится по нескольким столбцам таблицы. В данном типе индекса расположение полей является важным.
• Древовидный (B-tree index) — индекс, который представлен в виде корневой вершины и узлов.
• Частичный (Partial Indexes) — индекс, который состоит из подмножеств строк таблицы по определенному выражению.  

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

Также существуют кластеризованные и некластеризованные индексы. Рассмотрим их более подробно на примере СУБД PostgreSQL 15 версии.

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

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

Ниже приведена сравнительная характеристика кластеризованного и некластеризованного индекса.

Далее рассмотрим создание кластеризованных и некластеризованных индексов на практических примерах. Примеры ниже были выполнены в СУБД PostgreSQL 15 версии. 

В качестве примера создадим таблицу с именем movies, в которой будет содержаться информация о фильмах. Таблица включает в себя два столбца: id (уникальный номер фильма) и title (наименование фильма). SQL-код для создания данной таблицы приведен ниже: 

CREATE TABLE movies (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

Далее создаем индекс для столбца id:

CREATE INDEX cluster_id ON movies (id);

После этого можно создать кластерный индекс при помощи команды CLUSTER:

CLUSTER movies USING cluster_id;

Благодаря созданному кластерному индексу было достигнуто ускорение при выполнении запросов на выборку данных.

Для создания некластеризованного индекса для той же таблицы movies воспользуемся следующей командой:

CREATE INDEX non_clustered_index ON movies (title);

Создание структуры БД

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

CREATE TABLE orders (             
         order_id                           INT PRIMARY KEY,
         client_id                          INT,
         client_name                        VARCHAR(100) NOT NULL,
         client_address                     VARCHAR(255) NOT NULL,
         client_city                        VARCHAR(100) NOT NULL,
         client_country                     VARCHAR(100) NOT NULL,
         client_ip_address                  inet
         );

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

Чтобы создать индекс в базе данных, существует команда CREATE INDEX.

Рассмотрим практические примеры создания индексов в PostgreSQL. Общий синтаксис создания индексов следующий:

CREATE INDEX <имя_индекса> ON <название_таблицы> (<имя_столбца1>, <имя_столбца2>);

Например, создадим индекс для таблицы orders для столбца order_id:

CREATE INDEX index_for_order ON orders (order_id);

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

CREATE INDEX index_for_order ON orders (order_id, client_id);

Чтобы создать уникальный индекс, необходимо использовать ключевое слово UNIQUE:

CREATE UNIQUE INDEX index_for_order ON orders (order_id);

Чтобы создать частичный индекс, необходимо использовать команду:

CREATE INDEX clients_ip ON  orders (client_ip_address)
WHERE NOT (client_ip_address > '10.26.74.1' AND client_ip_address < '10.26.74.2');

В примере выше создается индекс для таблицы orders и для столбца client_ip_address, в индекс которой не будут включены такие значения, как 10.26.74.1 и 10.26.74.2.

Для удаления индексов используется команда DROP INDEX. Синтаксис для удаления индекса следующий:

DROP INDEX <имя_индекса>;

Например:

DROP INDEX index_for_order;

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

ALTER INDEX <имя_существуюшего_индекса> RENAME TO <имя_нового_индекса>

Например:

ALTER INDEX index_for_order RENAME TO new_index_for_order;

Также можно переместить индекс в другое табличное пространство. 

Сначала создаем директорию для табличного пространства (в примере — user1) и выдаем права пользователю postgres: 

mkdir /home/user1 && chown postgres:postgres /home/user1

Далее создаем новое табличное пространство с помощью команды:

CREATE TABLESPACE new_tablespace LOCATION '/home/user1';

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

ALTER INDEX <имя_существуюшего_индекса> SET TABLESPACE <имя_табличного_пространства>

Например:

ALTER INDEX index_for_order SET TABLESPACE new_tablespace;

Рекомендации по работе с индексами

Несмотря на то, что индексы являются полезными и порой даже необходимыми объектами, существуют определенные правила их использования. Если не соблюдать эти правила, можно существенно ухудшить работоспособность вашей базы данных.

Следует придерживаться следующих рекомендаций при работе с индексами:  

• Не применять индексы в небольших таблицах.

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

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

• Не применять индексы в столбцах со значением NULL (поле, обозначенное типом NULL, означает, что столбец поддерживает пустые значения)

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

Также стоит учитывать следующие особенности при работе с индексами:

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

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

Заключение

В статье мы познакомились с понятием индекс, который используется в базах данных. Грамотное использование индексов в ваших базах данных позволит существенно увеличить скорость обработки запросов к БД.