Базы данных | Практическое занятие 11

Создание ИНДЕКСА в SQL

В системах баз данных индекс — это структура для оптимизации производительности, связанная с таблицей или представлением. Его основная цель — ускорить операции извлечения данных (запросы SELECT) из таблицы за счет несколько более медленных операций модификации данных (INSERT, UPDATE, DELETE) и использования дополнительного дискового пространства. Сегодня мы рассмотрим, как работать с индексами в PostgreSQL.

Для наших примеров мы будем использовать демонстрационную таблицу с именем employees. Предположим, что эта таблица была создана следующим образом:

-- Удаляем таблицу, если она существует, чтобы начать с чистого листа
DROP TABLE IF EXISTS employees;

-- Создаем таблицу employees
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100), -- Ограничение UNIQUE удалено для примера с уникальным индексом
    hire_date DATE,
    salary NUMERIC(10, 2),
    department_id INT,
    status VARCHAR(10) DEFAULT 'active', -- например, 'active', 'inactive'
    profile JSONB -- Для примера индексации JSON
);

-- Вставляем несколько демонстрационных записей
INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id, profile) VALUES
('John', 'Doe', 'john.doe@company.com', '2022-01-15', 60000, 1, '{"skills": ["SQL", "Python"], "level": "Mid"}'),
('Jane', 'Smith', 'jane.smith@company.com', '2021-03-10', 75000, 2, '{"skills": ["Java", "Spring"], "level": "Senior"}'),
('Peter', 'Jones', 'peter.jones@company.com', '2023-05-20', 55000, 1, '{"skills": ["HTML", "CSS"], "level": "Junior"}'),
('Alice', 'Brown', 'alice.brown@company.com', '2022-08-01', 80000, 2, '{"skills": ["Project Management"], "level": "Lead"}'),
('Bob', 'White', 'bob.white@company.com', '2023-11-01', 50000, 3, null),
('Jane', 'Doe', 'jane.doe@company.com', '2023-02-28', 62000, 1, '{"skills": ["Python", "Data Analysis"], "level": "Mid"}');

Вы можете выполнить этот скрипт настройки в инструменте запросов (Query Tool) pgAdmin 4.

---

1. Создание индекса – Базовое определение индекса

Базовый индекс помогает ускорить запросы, которые фильтруют или сортируют данные на основе индексированных столбцов. PostgreSQL по умолчанию использует B-tree индексы, которые подходят для широкого спектра запросов, включающих операторы сравнения (=, <, <=, >, >=).

• Синтаксис:

  CREATE INDEX index_name
  ON table_name (column_name);
  

  Вы можете опционально указать тип индекса с помощью USING method (например, USING btree), но B-tree используется по умолчанию.

• Пример: Создадим индекс для столбца last_name таблицы employees, чтобы ускорить поиск по фамилии.

  CREATE INDEX idx_employees_last_name
  ON employees (last_name);
  

  Выполнение: Запустите эту команду в инструменте запросов (Query Tool) pgAdmin 4, подключенном к вашей базе данных.

• Примечание: Ограничения Primary Key (Первичный ключ) и Unique (Уникальность) автоматически создают соответствующие индексы в PostgreSQL. Вам не нужно вручную создавать индекс для столбца employee_id, поскольку он является первичным ключом.

---

2. Уникальный индекс – Обеспечение уникальности

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

• Синтаксис:

  CREATE UNIQUE INDEX index_name
  ON table_name (column_name);
  

• Пример: Убедимся, что у каждого сотрудника уникальный адрес электронной почты, создав уникальный индекс для столбца email.

  CREATE UNIQUE INDEX idx_employees_email_unique
  ON employees (email);
  

  Выполнение: Запустите эту команду. Если в ваших демонстрационных данных существуют дублирующиеся адреса электронной почты (исключая NULL), команда завершится ошибкой.

• Попытка нарушения: Попробуйте вставить дублирующийся адрес электронной почты:

  -- Эта команда должна ЗАВЕРШИТЬСЯ ОШИБКОЙ из-за уникального индекса
  INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id)
  VALUES ('Duplicate', 'User', 'john.doe@company.com', '2024-01-01', 50000, 1);
  

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

---

3. Индекс по выражению – Индексация вычисляемых значений

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

• Синтаксис:

  CREATE INDEX index_name
  ON table_name ((expression)); -- Обратите внимание на двойные скобки
  

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

  CREATE INDEX idx_employees_email_lower
  ON employees (LOWER(email));
  

  Выполнение: Запустите эту команду.

• Использование: Запрос, подобный следующему, теперь потенциально может использовать этот индекс:

  SELECT employee_id, first_name, last_name, email
  FROM employees
  WHERE LOWER(email) = 'jane.smith@company.com';
  

---

4. Частичный индекс – Индексация подмножества строк

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

• Синтаксис:

  CREATE INDEX index_name
  ON table_name (column_name)
  WHERE condition;
  

• Пример: Создадим индекс только для активных сотрудников на основе их даты найма (hire_date). Это полезно, если запросы часто ищут активных сотрудников, нанятых в определенные диапазоны дат.

  CREATE INDEX idx_employees_active_hire_date
  ON employees (hire_date)
  WHERE status = 'active';
  

  Выполнение: Запустите эту команду.

• Использование: Запросы, которые фильтруют по hire_date и включают WHERE status = 'active', являются кандидатами на использование этого индекса:

  SELECT first_name, last_name, hire_date
  FROM employees
  WHERE status = 'active' AND hire_date > '2023-01-01';
  

---

5. Многоколоночные индексы – Индексация нескольких столбцов

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

• Синтаксис:

  CREATE INDEX index_name
  ON table_name (column1, column2, ...);
  

• Пример: Создадим индекс по department_id и salary для оптимизации запросов, которые фильтруют по отделу, а затем сортируют или фильтруют по зарплате в пределах этого отдела.

  CREATE INDEX idx_employees_dept_salary
  ON employees (department_id, salary);
  

  Выполнение: Запустите эту команду.

• Использование: Этот индекс потенциально может оптимизировать запросы, подобные этим:

  -- Фильтрация по первому столбцу
  SELECT * FROM employees WHERE department_id = 1;
  
  -- Фильтрация по первому и второму столбцам
  SELECT * FROM employees WHERE department_id = 2 AND salary > 70000;
  
  -- Фильтрация по первому и сортировка по второму
  SELECT * FROM employees WHERE department_id = 1 ORDER BY salary DESC;
  

  Примечание: Запрос, фильтрующий только по salary, может не использовать этот индекс эффективно, поскольку salary не является ведущим столбцом.

---

6. Reindex – Перестроение индексов

Со временем, особенно в таблицах с частыми обновлениями или удалениями, индексы могут стать “раздутыми” (содержать пустое пространство) или фрагментированными, что потенциально снижает их эффективность. Также может потребоваться перестроить индексы после изменения некоторых параметров хранения. Команда REINDEX перестраивает индекс или все индексы для таблицы или базы данных.

• Синтаксис:

  -- Перестроить конкретный индекс
  REINDEX INDEX index_name;
  
  -- Перестроить все индексы для конкретной таблицы
  REINDEX TABLE table_name;
  
  -- Перестроить все индексы в текущей базе данных (требуются соответствующие права)
  REINDEX DATABASE database_name;
  
  -- Перестроить все индексы для системных таблиц в текущей базе данных
  REINDEX SYSTEM database_name;
  

• Пример: Перестроить индекс idx_employees_last_name.

  REINDEX INDEX idx_employees_last_name;
  

  Выполнение: Запустите эту команду. Она заблокирует таблицу для записи на время перестроения.

• Пример: Перестроить все индексы для таблицы employees.

  REINDEX TABLE employees;
  

  Выполнение: Запустите эту команду.

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

---

7. Drop Index – Удаление индекса

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

• Синтаксис:

  DROP INDEX index_name;
  

  Вы можете добавить IF EXISTS, чтобы избежать ошибки, если индекс не существует:

  DROP INDEX IF EXISTS index_name;
  

• Пример: Удалим индекс по выражению LOWER(email).

  DROP INDEX IF EXISTS idx_employees_email_lower;
  

  Выполнение: Запустите эту команду.

• Предостережение: Не удаляйте индексы, созданные автоматически ограничениями первичного ключа или уникальности, если вы не намерены также удалить само ограничение (что обычно делается с помощью ALTER TABLE ... DROP CONSTRAINT).

---

8. Список индексов – Поиск существующих индексов

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

• Способ 1: Использование команды psql (или панели SQL в pgAdmin 4) В psql или в инструменте запросов (Query Tool) pgAdmin 4 вы можете использовать мета-команду \d:

  \d employees
  

  Выполнение: Введите эту команду (без точки с запятой) в инструмент запросов и выполните ее. Вывод: Вывод покажет столбцы таблицы, ограничения и, внизу, под заголовком “Indexes:” (“Индексы:”), будут перечислены все индексы, связанные с таблицей employees, включая их тип (например, btree) и столбцы, которые они охватывают. Он также показывает индексы, лежащие в основе ограничений.

• Способ 2: Запрос к системным каталогам Вы можете запросить системное представление pg_indexes для получения более подробной информации.

  SELECT
      indexname,
      indexdef
  FROM
      pg_indexes
  WHERE
      tablename = 'employees';
  

  Выполнение: Выполните этот SQL-запрос. Вывод: Это предоставит имя индекса и команду CREATE INDEX, использованную для его определения.

• Способ 3: Использование обозревателя объектов (Object Browser) в pgAdmin 4
  1. В панели обозревателя pgAdmin 4 слева перейдите к вашему серверу.
  2. Разверните Databases (Базы данных) -> имя_вашей_базы_данных -> Schemas (Схемы) -> public (или соответствующую схему).
  3. Разверните Tables (Таблицы) -> employees.
  4. Разверните узел Indexes (Индексы) под таблицей employees.
  5. Будут перечислены все индексы таблицы по именам (например, idx_employees_last_name, employees_pkey). Щелчок по имени индекса покажет его свойства (включая индексируемые столбцы и определение) в панели свойств справа.