Типы индекс

В SQL индексы используются для ускорения выполнения запросов к базе данных. Они создаются на столбцах таблиц и позволяют значительно повысить производительность при выборке данных. Существует несколько типов индексов в SQL, каждый из которых подходит для определённых задач:

1. Кластеризованные индексы (Clustered Index)

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

• Особенности:

  • Упорядочивает строки таблицы в соответствии с индексом.

  • Таблица с кластеризованным индексом называется кластеризованной таблицей.

  • Используется для ускорения запросов, которые возвращают диапазоны значений.

  • Пример: индексация первичного ключа.

-- Создание таблицы с кластеризованным индексом на столбце 'id'
CREATE TABLE Employees (
    id INT PRIMARY KEY,  -- Кластеризованный индекс создается автоматически, так как это PRIMARY KEY
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

-- Явное создание кластеризованного индекса (MS SQL Server)
CREATE CLUSTERED INDEX idx_employees_id ON Employees (id);

2. Некластеризованные индексы (Non-Clustered Index)

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

• Особенности:

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

  • Создаётся на определённом столбце или столбцах.

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

  • Пример: индексирование столбцов, по которым часто выполняются запросы с фильтрацией.

Пример:

-- Создание некластеризованного индекса на столбце 'last_name'
CREATE NONCLUSTERED INDEX idx_employees_last_name ON Employees (last_name);

3. Уникальные индексы (Unique Index)

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

• Особенности:

  • Гарантирует уникальность значений в столбце.

  • Может быть кластеризованным или некластеризованным.

  • Пример: индекс на столбце с уникальными данными (например, адреса электронной почты).

Пример:

-- Создание уникального индекса на столбце 'email'
CREATE UNIQUE INDEX idx_employees_email ON Employees (email);

4. Полнотекстовые индексы (Full-Text Index)

Полнотекстовые индексы используются для поиска текста в больших текстовых полях, таких как VARCHAR или TEXT.

• Особенности:

  • Оптимизирует запросы на поиск текстовых данных.

  • Используется для поиска ключевых слов в больших текстовых полях.

  • Пример: поиск по текстовому полю в документах или статьях.

Пример для MS SQL Server:

-- Сначала необходимо создать полнотекстовый каталог
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

-- Затем создать полнотекстовый индекс на столбце 'description'
CREATE FULLTEXT INDEX ON Employees(description) KEY INDEX PK_Employees;

5. Составные индексы (Composite Index)

Составные индексы создаются на нескольких столбцах одновременно.

• Особенности:

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

  • Порядок столбцов в индексе важен.

  • Пример: индекс на комбинации Фамилия и Имя.

Пример:

-- Создание составного индекса на столбцах 'first_name' и 'last_name'
CREATE INDEX idx_employees_name ON Employees (first_name, last_name);

6. Индексы с фильтром (Filtered Index)

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

• Особенности:

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

  • Повышает производительность и уменьшает размер индекса.

  • Пример: индекс на столбце с условием WHERE active = 1.

Пример для MS SQL Server:

-- Создание индекса с фильтром на активных сотрудниках
CREATE NONCLUSTERED INDEX idx_employees_active ON Employees (last_name) WHERE active = 1;

7. Пространственные индексы (Spatial Index)

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

• Особенности:

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

  • Пример: поиск ближайших объектов в определённой радиусе.

Пример для MS SQL Server:

-- Создание таблицы с пространственными данными
CREATE TABLE Locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    location GEOGRAPHY
);

-- Создание пространственного индекса
CREATE SPATIAL INDEX idx_locations_location ON Locations(location);

8. XML-индексы (XML Index)

Используются для ускорения запросов по данным, хранящимся в формате XML.

• Особенности:

  • Оптимизируют запросы на извлечение данных из XML.

  • Пример: запросы к столбцам XML-типа.

Пример для MS SQL Server:

-- Создание таблицы с XML-данными
CREATE TABLE XmlData (
    id INT PRIMARY KEY,
    data XML
);

-- Создание XML индекса
CREATE PRIMARY XML INDEX idx_xmldata_data ON XmlData(data);

9. Bitmap-индексы (Bitmap Index)

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

• Особенности:

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

  • Используются в аналитических запросах.

Пример для Oracle:

-- Создание bitmap индекса
CREATE BITMAP INDEX idx_employees_gender ON Employees (gender);

Заключение

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