Запропонувати правкуПокращити цю статтюДопрацюйте відповідь до «Що таке композитні індекси?». Ваші зміни проходять модерацію перед публікацією.Потрібне підтвердженняКонтентЩо ви змінюєте🇺🇸EN🇺🇦UAПереглядЗаголовок (UA)Коротка відповідь (UA)**Composite index (композитний індекс)** - індекс, побудований на двох або більше колонках. База даних читає його зліва направо: індекс на `(status, created_at)` прискорює фільтрацію за `status` або за `status + created_at`, але не за одним `created_at`. **Ключове:** порядок колонок визначає, які запити отримують прискорення.Показується над повною відповіддю для швидкого нагадування.Відповідь (UA)Зображення**Composite index (композитний індекс)** - індекс бази даних, побудований на двох або більше колонках, відсортованих зліва направо відповідно до порядку, який ти задаєш. ## Теорія ### TL;DR - Індекс на `(a, b, c)` допомагає запитам, які фільтрують за `a`, за `a + b` або за `a + b + c`. Запитам, що фільтрують тільки за `b` чи `c`, він не допомагає. - Порядок колонок - найважливіше рішення при проектуванні композитного індексу. - Якщо всі колонки, потрібні запиту, є в індексі, рядок таблиці не читається взагалі. Такий індекс називається покривним (covering index). - Спочатку йдуть колонки з умовами рівності, потім з умовами діапазону. - Один добре спроектований композитний індекс зазвичай замінює два або три окремих індекси без зайвого навантаження на запис. ### Швидкий приклад ```sql -- таблиця users: id, last_name, first_name, email, created_at CREATE INDEX idx_users_name ON users (last_name, first_name); -- Використовує індекс повністю (обидві колонки збігаються) SELECT * FROM users WHERE last_name = 'Kim' AND first_name = 'Alex'; -- Використовує індекс (тільки перша колонка) SELECT * FROM users WHERE last_name = 'Kim'; -- НЕ використовує індекс (пропускає першу колонку) SELECT * FROM users WHERE first_name = 'Alex'; ``` Індекс зберігає рядки, відсортовані спочатку за `last_name`, потім за `first_name` всередині кожної групи прізвищ. Без значення `last_name` у рушія немає точки входу в структуру. Сканувати з середини відсортованого дерева рівнозначно повному скануванню таблиці. ### Принцип ліво-правого читання (leftmost prefix) Композитний індекс на `(col1, col2, col3)` - це фактично три індекси в одному: на `col1`, на `(col1, col2)` та на `(col1, col2, col3)`. Тільки ці комбінації. Індекс не може обслуговувати запит на `(col2)` або `(col2, col3)`, бо на верхньому рівні дані відсортовані не так. MySQL, PostgreSQL та більшість реляційних баз даних реалізують це однаково. Планувальник запитів проходить по умовах WHERE зліва направо, порівнює їх із визначенням індексу і зупиняється, щойно натрапляє на колонку, якої немає в WHERE, або на умову діапазону. Умови діапазону розривають ланцюжок. Рівність для `col1` і діапазон для `col2` означає, що індекс допомагає для обох. Але якщо додати `AND col3 = 5`, індекс більше не може допомогти для `col3`, бо діапазон у `col2` перервав послідовність. ```sql CREATE INDEX idx_orders ON orders (status, created_at, customer_id); -- Індекс використовується для всіх трьох колонок (усі умови рівності) SELECT * FROM orders WHERE status = 'pending' AND created_at = '2024-01-15' AND customer_id = 42; -- Індекс використовується для status і діапазону created_at, але НЕ для customer_id SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01' AND customer_id = 42; -- Індекс використовується тільки для status SELECT * FROM orders WHERE status = 'pending'; ``` ### Порядок колонок Умови рівності мають стояти перед умовами діапазону. Щойно індекс натрапляє на діапазон, він не може далі робити прямий пошук за наступними колонками. Для запиту `WHERE user_id = 5 AND created_at > '2024-01-01'` індекс має бути `(user_id, created_at)`, а не `(created_at, user_id)`. При зворотному порядку рушій може використати індекс тільки для діапазону `created_at` і повинен фільтрувати `user_id` вручну по всіх рядках у тому діапазоні. Кардинальність теж має значення. Колонка з тисячами унікальних значень (`user_id`) перед колонкою з малою кількістю (`status`) дозволяє на кожному кроці відсіювати більше рядків. Але це вторинне правило. Правило рівності-перед-діапазоном завжди в пріоритеті. ### Покривні індекси Покривний (covering) індекс містить усі колонки, потрібні запиту: для фільтрації, для сортування і для вибірки. Якщо запит повністю покритий, рушій читає лише сторінки індексу і ніколи не звертається до самого рядка таблиці. На навантаженнях з великою кількістю читань це може скоротити I/O на 80-90%. ```sql -- Без покривного індексу: сканування індексу + зчитування рядка для кожного збігу CREATE INDEX idx_orders_status ON orders (status); SELECT order_id, created_at FROM orders WHERE status = 'shipped'; -- З покривним індексом: тільки індекс, без звернення до таблиці CREATE INDEX idx_orders_cover ON orders (status, order_id, created_at); SELECT order_id, created_at FROM orders WHERE status = 'shipped'; -- PostgreSQL EXPLAIN покаже: "Index Only Scan" замість "Index Scan" ``` Компроміс: ширші індекси займають більше місця і сповільнюють INSERT/UPDATE/DELETE, бо кожен запис оновлює і сам індекс. ### Типові помилки **Неправильний порядок колонок.** ```sql -- Запит: WHERE user_id = 5 AND status = 'active' -- Погано: низька кардинальність першою CREATE INDEX idx_bad ON orders (status, user_id); -- Добре: висока кардинальність і умова рівності першою CREATE INDEX idx_good ON orders (user_id, status); ``` Перший індекс спрацює, але прочитає більше рядків перш ніж звузиться до `user_id = 5`. **Фільтр по не-крайній лівій колонці.** Є індекс на `(a, b, c)`, а запит фільтрує за `WHERE b = 1`. Результат - повне сканування таблиці. Це найпоширеніша помилка з композитними індексами і в реальному коді, і на технічних співбесідах. **Діапазон в середині ланцюжка.** ```sql -- Індекс: (status, created_at, user_id) -- Рушій використовує status (рівність) і created_at (діапазон) -- але не може використати індекс для user_id -- фільтрує окремо SELECT * FROM orders WHERE status = 'active' AND created_at > '2024-06-01' AND user_id = 99; -- Виправлення: перемісти user_id перед created_at, бо це умова рівності CREATE INDEX idx_orders_fixed ON orders (status, user_id, created_at); ``` **Занадто багато колонок про всяк випадок.** Індекси з 5+ колонками рідко дають більше користі ніж індекс з 3 колонками, зате кожна додаткова колонка збільшує розмір і сповільнює записи. Додавай колонки тільки тоді, коли є конкретний виміряний запит, який це виправдовує. **Припущення, що NULL не індексується.** У PostgreSQL і MySQL значення `NULL` зберігаються в індексі. Запити з `WHERE col IS NULL` можуть використовувати індекс, і для багатьох це несподівана поведінка. ### Де зустрічається в реальному коді - E-commerce: `(user_id, status)` на таблиці orders для отримання всіх очікуваних замовлень конкретного користувача. - Аналітичні дашборди: `(event_type, created_at)` для фільтрації за подією і сортування за часом в одному скануванні індексу. - Авторизація: `(email, is_active)` для запитів входу, які перевіряють обидві умови. - Multi-tenant SaaS: `(tenant_id, resource_type, created_at)` зазвичай перший індекс, який додають до майже кожної таблиці. ### Питання на співбесіді **Q:** Чи можна використати композитний індекс, якщо WHERE містить тільки другу колонку? **A:** Ні. Принцип ліво-правого читання вимагає, щоб рушій починав з першої колонки. Запит по другій колонці призведе до повного сканування таблиці або індексу залежно від оцінки вартості планувальника. **Q:** Яка різниця між composite index і covering index? **A:** Composite index - будь-який індекс на кількох колонках. Covering index - композитний індекс, що містить усі колонки, потрібні конкретному запиту, тому рядок таблиці не читається взагалі. Кожен covering index є композитним, але не кожен композитний є покривним. **Q:** Чи впливає порядок колонок у SELECT на використання індексу? **A:** Ні. Важливий порядок у WHERE і у визначенні індексу. Список у SELECT впливає тільки на покривні індекси: всі вибрані колонки мають бути присутні в індексі. Порядок їх перерахування в SELECT ролі не грає. **Q:** Як планувальник запитів вирішує, чи використовувати композитний індекс? **A:** Він порівнює вартість різних планів: сканування індексу проти послідовного сканування таблиці. Якщо запит повертає велику частку рядків, послідовне сканування часто виявляється дешевшим через швидший послідовний I/O. Переглянути рішення планувальника можна через `EXPLAIN ANALYZE` у PostgreSQL або `EXPLAIN` у MySQL. **Q:** Є запити на `(a, b)` і окремо на `(a)`. Чи потрібні два індекси? **A:** Ні. Індекс на `(a, b)` вже обслуговує запити тільки на `a` за принципом ліво-правого читання. Окремий індекс на `(a)` - зайві витрати пам'яті і навантаження на запис. Типова помилка перевантаженості індексами в legacy-кодових базах. ## Приклади ### Фільтрація активних сесій користувача ```sql -- таблиця sessions: id, user_id, token, status, expires_at CREATE INDEX idx_sessions_lookup ON sessions (user_id, status); -- Швидко: обидві колонки в індексі, user_id першим SELECT token, expires_at FROM sessions WHERE user_id = 1042 AND status = 'active'; -- Також використовує індекс (leftmost prefix) SELECT COUNT(*) FROM sessions WHERE user_id = 1042; -- НЕ використовує індекс SELECT * FROM sessions WHERE status = 'active'; -- Для цього запиту потрібен окремий індекс на (status) ``` Цей патерн я бачив у продакшені: команда додала функцію пошуку, що фільтрувала тільки за `status`, і не помітила, що порядок колонок в існуючому індексі не підходить для такого запиту. ### Покривний індекс для запиту дашборду ```sql -- таблиця orders: id, tenant_id, status, total_amount, created_at -- Без покривного індексу: сканування + зчитування рядка для кожного збігу CREATE INDEX idx_orders_tenant ON orders (tenant_id, status); SELECT id, total_amount, created_at FROM orders WHERE tenant_id = 5 AND status = 'completed'; -- З покривним індексом: рушій читає тільки індекс, без звернення до таблиці CREATE INDEX idx_orders_cover ON orders (tenant_id, status, id, total_amount, created_at); -- EXPLAIN ANALYZE покаже "Index Only Scan" замість "Index Scan" -- Типовий результат на великих таблицях: в 3-5 разів менша затримка ``` Індекс виріс з 2 до 5 колонок. На таблиці з 10M рядків і запиті, який виконується 1000 разів на хвилину, зменшення читань окупило витрати на запис. ### Запит з умовою діапазону ```sql -- таблиця logs: id, service_name, severity, created_at -- Добре: умови рівності першими, діапазон останнім CREATE INDEX idx_logs_good ON logs (service_name, severity, created_at); SELECT * FROM logs WHERE service_name = 'api-gateway' AND severity = 'error' AND created_at > NOW() - INTERVAL '1 hour'; -- Погано: діапазон в середині розриває ланцюжок для severity CREATE INDEX idx_logs_bad ON logs (service_name, created_at, severity); -- З idx_logs_bad рушій використовує service_name і діапазон created_at, -- але не може застосувати індекс для severity = 'error' і фільтрує окремо ```Для рев’юераПримітка для модератора (необов’язково)Бачить лише модератор. Прискорює рев’ю.