Пошук уроків, статей та іншого контенту
Розберете, як індекси пришвидшують запити, які бувають їхні типи та якою є ціна індексації для запису.
Індекс — це спеціальна структура даних, яка допомагає базі швидше знаходити записи за певними стовпцями.
Без індексу база може виконувати повне сканування таблиці:
прочитати перший рядок;
перевірити умову;
прочитати наступний рядок;
повторювати, доки не буде перевірено всю таблицю.
Для таблиці з кількома десятками рядків це майже непомітно. Але для мільйонів записів така операція може бути повільною.
Індекс зберігає значення стовпця в організованому вигляді та посилання на відповідні рядки таблиці. Завдяки цьому база може швидше знайти потрібні записи.
Наприклад, для таблиці users запит:
SELECT *
FROM users
WHERE email = 'olena@example.com';може перевірити всі рядки, якщо індексу на email немає. Індекс дозволяє спочатку знайти значення email, а потім перейти до потрібного рядка.
Розглянемо таблицю:
id | email | name
---|--------------------|-------
1 | anna@example.com | Anna
2 | ihor@example.com | Ihor
3 | olena@example.com | OlenaБез індексу база перевіряє значення email у кожному рядку.
З індексом база має окрему структуру приблизно такого вигляду:
anna@example.com -> рядок 1
ihor@example.com -> рядок 2
olena@example.com -> рядок 3Пошук відбувається не обов’язково послідовним переглядом усієї таблиці. Для найпоширенішого типу індексу — B-tree — використовується впорядкована структура пошуку.
Важливо: індекс не містить усі дані рядка. Зазвичай він містить:
значення індексованого стовпця;
посилання на рядок таблиці.
Тому після знаходження запису база може додатково звернутися до самої таблиці, щоб отримати інші стовпці.
Для створення звичайного індексу використовується команда:
CREATE INDEX index_name
ON table_name (column_name);Приклад:
CREATE INDEX users_email_idx
ON users (email);Після цього запити, у яких база може використати email, потенційно виконуватимуться швидше.
Назва індексу має бути унікальною в межах відповідної бази або схеми. Зазвичай у назві вказують таблицю та стовпець:
users_email_idx
orders_created_at_idx
products_category_id_idxВидалити індекс можна так:
DROP INDEX users_email_idx;Точний синтаксис може трохи відрізнятися між системами керування базами даних.
База даних сама вирішує, використовувати індекс чи ні. Перевірити її рішення можна за допомогою команди аналізу плану запиту.
Нижче наведено повний приклад для SQLite:
-- Створюємо таблицю користувачів
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL,
name TEXT NOT NULL
);
-- Додаємо тестові записи
INSERT INTO users (email, name) VALUES
('anna@example.com', 'Anna'),
('ihor@example.com', 'Ihor'),
('olena@example.com', 'Olena'),
('petro@example.com', 'Petro');
-- Перевіряємо план запиту до створення індексу
EXPLAIN QUERY PLAN
SELECT *
FROM users
WHERE email = 'olena@example.com';
-- Створюємо індекс для пошуку за email
CREATE INDEX users_email_idx
ON users (email);
-- Перевіряємо план того самого запиту після створення індексу
EXPLAIN QUERY PLAN
SELECT *
FROM users
WHERE email = 'olena@example.com';До створення індексу SQLite зазвичай показує повне сканування таблиці. Після створення індексу план може містити використання індексу users_email_idx.
Фактичний текст плану залежить від версії СКБД, але важлива сама ідея: план показує, чи використовує база індекс.
Типи індексів можуть називатися та реалізовуватися по-різному в різних СКБД. Найпоширеніші варіанти такі.
B-tree — стандартний тип індексу в багатьох базах даних.
Він добре підходить для:
точного пошуку:
WHERE email = 'olena@example.com'порівняння:
WHERE age >= 18сортування:
ORDER BY created_atпошуку діапазону:
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'Для більшості звичайних запитів саме B-tree є типовим вибором.
Hash-індекс оптимізований для точного порівняння:
WHERE user_id = 42Він не призначений для ефективного пошуку діапазону або сортування:
WHERE user_id > 42
ORDER BY user_idПідтримка та можливості hash-індексів залежать від конкретної СКБД. Тому для початку зазвичай використовують типовий B-tree, якщо немає конкретної причини обрати інший тип.
Унікальний індекс не лише пришвидшує пошук, а й гарантує, що значення не повторюються.
CREATE UNIQUE INDEX users_email_unique_idx
ON users (email);Після цього база не дозволить додати двох користувачів з однаковою електронною поштою.
Такий індекс корисний для полів, які повинні бути унікальними:
email;
ім’я користувача;
зовнішній ідентифікатор;
номер документа.
У багатьох СКБД обмеження UNIQUE автоматично створює відповідний індекс:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE
);Складений індекс містить кілька стовпців:
CREATE INDEX orders_user_status_idx
ON orders (user_id, status);Він може допомогти запиту:
SELECT *
FROM orders
WHERE user_id = 42
AND status = 'paid';Порядок стовпців має значення. Індекс (user_id, status) найкраще підходить для умов, що починаються з user_id:
WHERE user_id = 42або містять обидва стовпці:
WHERE user_id = 42
AND status = 'paid'Але такий індекс зазвичай не є настільки корисним для запиту лише за status:
WHERE status = 'paid'Це часто називають правилом лівого префікса: база ефективно використовує початкову частину складеного індексу.
Частковий індекс містить не всі рядки, а лише ті, що відповідають умові. Наприклад, можна індексувати лише активні записи:
CREATE INDEX users_active_email_idx
ON users (email)
WHERE is_active = 1;Такий індекс може бути меншим і дешевшим в обслуговуванні, але його підтримка залежить від СКБД.
Індекс найчастіше потрібен для стовпців, які використовуються в:
WHERE;
JOIN;
ORDER BY;
GROUP BY.
Наприклад:
SELECT *
FROM orders
WHERE user_id = 42;Для цього запиту може бути корисним індекс:
CREATE INDEX orders_user_id_idx
ON orders (user_id);Для з’єднання таблиць:
SELECT users.name, orders.total
FROM users
JOIN orders ON orders.user_id = users.id;індекс на orders.user_id може пришвидшити пошук замовлень для конкретного користувача.
Водночас індекс не завжди потрібен. Якщо таблиця маленька, повне сканування може бути швидшим, ніж звернення до індексу та подальше читання рядків таблиці.
Добрий кандидат для індексу зазвичай має такі властивості:
часто використовується в умовах запитів;
таблиця достатньо велика;
запит повертає невелику частину рядків;
значення стовпця достатньо різноманітні.
Наприклад, email зазвичай має високу вибірковість: кожне значення належить одному користувачу.
Стовпець is_active, що містить лише 0 або 1, має низьку вибірковість. Індекс на ньому може бути малокорисним, якщо приблизно половина таблиці має кожне зі значень.
Однак користь залежить від конкретних даних і запитів. Рішення краще перевіряти за допомогою плану виконання, а не приймати лише за назвою стовпця.
Індекс пришвидшує читання, але не є безкоштовним.
Індекс зберігається окремо від таблиці та займає місце на диску. Чим більше індексів і чим більше значення в індексованих стовпцях, тим більше простору потрібно.
Під час додавання або зміни рядка база повинна оновити не лише таблицю, а й усі індекси, яких це стосується.
Наприклад, якщо таблиця має п’ять індексів, вставка нового рядка може вимагати:
додати рядок до таблиці;
додати значення до першого індексу;
додати значення до другого індексу;
продовжити для інших індексів.
Тому велика кількість індексів може сповільнювати:
INSERT;
UPDATE;
DELETE.
Якщо змінюється значення стовпця, що входить до індексу, базі потрібно оновити позицію цього запису в індексі.
Наприклад:
UPDATE users
SET email = 'new@example.com'
WHERE id = 1;Якщо email індексований, база має змінити відповідний запис в індексі. Якщо на email також встановлено унікальність, потрібно додатково перевірити, що нового значення ще немає.
Під час проєктування індексів корисно дотримуватися таких правил:
Індексуйте запити, а не всі стовпці підряд.
Починайте з індексів для частих і повільних запитів.
Для складених індексів уважно визначайте порядок стовпців.
Перевіряйте план виконання запиту.
Враховуйте не лише швидкість читання, а й ціну запису.
Не створюйте кілька індексів, які дублюють один одного.
Переконайтеся, що обмеження PRIMARY KEY або UNIQUE вже не створило потрібний індекс автоматично.
Індексація всіх стовпців збільшує використання диска та сповільнює зміни даних. Індекс потрібно створювати через конкретну потребу запиту.
Індекси (user_id, status) і (status, user_id) — це не одне й те саме. Порядок має відповідати найчастішим умовам пошуку та сортування.
Оптимізатор може вирішити, що повне сканування таблиці дешевше. Це нормально для маленьких таблиць або запитів, які повертають більшість рядків.
Індекс на стовпці з кількома можливими значеннями не завжди дає перевагу. Його користь потрібно перевіряти на реальних даних.
Індекс може пришвидшити SELECT, але кожен додатковий індекс робить операції запису складнішими. У системі з великою кількістю вставок це особливо важливо.
Індекс — це структура, яка допомагає базі швидше знаходити записи.
Найпоширеніший тип для звичайних пошуків і діапазонів — B-tree.
Унікальний індекс одночасно пришвидшує пошук і забороняє дублікати.
Складений індекс містить кілька стовпців, а їхній порядок має значення.
Індекси можуть допомагати умовам WHERE, з’єднанням, сортуванню та групуванню.
За швидше читання потрібно платити додатковим місцем і повільнішими операціями INSERT, UPDATE та DELETE.
Індекси слід створювати на основі реальних запитів і перевіряти за планом виконання.