Проблеми надмірного індексування

Індекси — це, безперечно, крутий спосіб зробити твої бази даних швидшими, але, як кажуть, «краще — ворог хорошого». Не кожен індекс приносить користь, а їх надмірна кількість може більше нашкодити, ніж допомогти. Звучить парадоксально, але це так і є. Давай розбиратись.

Уяви велику бібліотеку, де для пошуку книжок є кілька каталогів — наприклад, за автором, за жанром, за роком видання. Кожен такий каталог допомагає швидко знайти потрібну книжку. Але якщо каталогів стане занадто багато — наприклад, для кожного слова з назви чи кожної деталі — замість допомоги ти отримаєш плутанину: на пошук йде більше часу, каталоги займають багато місця, і бібліотекарю доводиться постійно оновлювати всі ці списки.

У базі даних індекси працюють приблизно так само: вони допомагають швидко знайти потрібні дані, але якщо їх забагато, оновлювати їх при додаванні чи зміні записів стає важким завданням. І місце на диску теж страждає. До того ж, коли індексів надто багато, система може просто заплутатись, який з них використати.

Тому, як і з каталогами в бібліотеці, з індексами важливо не переборщити — краще мати кілька потрібних і ефективних, ніж десятки непотрібних.

Давай пограємо в "детективів PostgreSQL". Уяви, що ти додав три індекси для одного стовпця. Ти зробив це, бо думав, що це може покращити продуктивність. Але уяви тільки:

• Якщо твоя таблиця — це великий список студентів, а індексів три, кожне додавання нового студента в таблицю призведе до трьох операцій оновлення індексів. Не дуже схоже на "прискорення", правда?
• А якщо у тебе 10 таких таблиць, кожна з яких перевантажена індексами? Продуктивність всієї бази даних полетить у прірву.

Як зрозуміти, чи є у тебе проблема з надмірним індексуванням?

Перше, що варто зробити, щоб зрозуміти, чи є у тебе проблема, — це подивитись на існуючі індекси. У PostgreSQL це можна зробити за допомогою команди:

\d ім'я_таблиці

Ця команда покаже тобі таблицю, її стовпці та пов’язані індекси. Якщо ти бачиш купу індексів, пов’язаних з однією таблицею — це вже привід задуматись.

Ще один корисний інструмент — системне представлення pg_stat_user_indexes. Воно показує, наскільки активно використовуються індекси, що дає зрозуміти, чи є серед них "мертвий вантаж":

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Якщо idx_scan дорівнює 0, це означає, що індекс жодного разу не використовувався у запитах. Такий індекс — явний кандидат на видалення.

Приклад надмірного індексування

Давай уявимо таблицю з користувачами:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

І у нас є три індекси:

-- Індекс на email
CREATE INDEX idx_users_email ON users (email);

-- Індекс на username
CREATE INDEX idx_users_username ON users (username);

-- Індекс на created_at
CREATE INDEX idx_users_created_at ON users (created_at);

Тепер давай подивимось на типові запити, які ми виконуємо:

1. Пошук користувача за email.
2. Пошук користувача за username.
3. Сортування користувачів за created_at.

Здається, що індекси корисні. Але ось фішка: якщо ці запити рідкісні (наприклад, раз на тиждень), створення індексів не виправдане. Більше того, якщо частина цих індексів ніколи не використовується, вони просто створюють навантаження на операції вставки та оновлення даних.

Для ілюстрації: припустимо, у нас є такі дані в таблиці users:

Якщо запити по username майже не виконуються, індекс idx_users_username жодного разу не використовується (idx_scan = 0) і може бути видалений для оптимізації.

Тобто індекс — це класний інструмент, але його треба застосовувати з розумом. Краще щоб у нас було кілька потрібних і добре використовуваних індексів, ніж багато зайвих.

Як уникнути надмірного індексування

1. Аналіз використовуваних індексів. Як ми вже згадували, перевір статистику використання індексів за допомогою pg_stat_user_indexes. Якщо індекс майже не використовується, скоріш за все, його можна видалити:

DROP INDEX IF EXISTS ім'я_індексу;

2. Створюй індекси тільки для часто використовуваних запитів. Перш ніж додавати індекс, задай собі питання:

• Чи часто цей стовпець бере участь у WHERE, ORDER BY, GROUP BY?
• Чи великий обсяг даних у таблиці?
• Чи дійсно запит виконується занадто повільно без індексу?

Якщо відповідь "ні" хоча б на одне з цих питань, створення індексу може бути зайвим.

3. Використовуй складені індекси. Якщо ти часто використовуєш кілька стовпців в одному запиті, замість створення окремих індексів на кожен стовпець створи складений індекс:

CREATE INDEX idx_users_email_username ON users (email, username);

Це прискорить запити, які фільтруються по email і username одночасно.

4. Регулярно переглядай існуючі індекси. З ростом бази даних твої запити можуть змінитись. Те, що було корисним рік тому, може стати непотрібним сьогодні. Періодично перевіряй свої індекси і видаляй ті, які більше не використовуються.

Мінімізація індексів на прикладі

Давай повернемось до нашої таблиці users. Замість трьох окремих індексів ми можемо оптимізувати їх так:

• Прибрати окремий індекс на created_at, якщо сортування по цьому стовпцю трапляється рідко.
• Замість двох окремих індексів на email і username створити складений індекс:

CREATE INDEX idx_users_email_username ON users (email, username);

Підсумки: у чому секрет балансу?

Як і в багатьох аспектах програмування, тут працює принцип мінімалізму: "Чим менше, тим краще". Не треба індексувати кожен стовпець тільки тому, що ти можеш. Подумай, навіщо тобі індекс і наскільки він покращить продуктивність запитів. Будь прагматичним і пам’ятай, що хороший розробник — не той, хто додає індекси направо і наліво, а той, хто розуміє їх вплив і використовує їх ефективно.

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