Перелік лекцій

Індекси в базах даних

Призначення індексів у базах даних

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

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

---

Типи індексів

1. За унікальністю

• Унікальний індекс (UNIQUE) — забезпечує, щоб значення індексованого поля не повторювалися (наприклад, поле id).
  Якщо спробувати вставити дубльоване значення, СУБД поверне помилку.

• Неунікальний індекс (NON-UNIQUE) — допускає повторення значень (наприклад, поле «місто» у таблиці «клієнти»).

2. За кількістю полів

• Простий індекс — створюється для одного поля таблиці.
• Складений індекс (Composite Index) — створюється за кількома полями.
  Порядок полів має значення: індекс за (name, surname) не є еквівалентним індексу (surname, name).

---

3. Види індексів за структурою

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

1. B-Tree (Balanced Tree, B-дерево)

B-Tree (збалансоване дерево пошуку) — основна структура індексів у більшості реляційних СУБД (MySQL, PostgreSQL, SQLite).
Дані зберігаються у впорядкованому вигляді, а пошук, вставка та видалення виконуються зі складністю O(log n).

Переваги:

• Підтримує пошук за діапазоном (BETWEEN, >, <).
• Ефективний для сортування (ORDER BY) та фільтрації (WHERE).
• Може використовуватися для часткових збігів у складених індексах.

Недоліки:

• Повільніший при точному пошуку порівняно з Hash-індексом.
• Потребує балансування під час оновлення.

---

2. B+Tree

B+Tree — це модифікований варіант B-дерева, у якому всі ключі зберігаються лише в листових вузлах, а внутрішні вузли містять лише навігаційні значення.

Особливості:

• Листові вузли з’єднані між собою, що полегшує послідовний обхід.
• Пошук завжди доходить до листового рівня.
• Оптимальний для діапазонних запитів.

Використання:

• Реалізація індексів за замовчуванням у MySQL (InnoDB), PostgreSQL, Oracle.

---

3. Hash-індекс

Hash-індекс використовує хеш-функцію для обчислення позиції запису в таблиці.
Завдяки цьому пошук за точним збігом (=) виконується майже миттєво — за амортизовану складність O(1).

Переваги:

• Висока швидкість при точному пошуку.
• Простота реалізації.

Недоліки:

• Не підтримує пошук за діапазоном або сортування.
• Ефективність залежить від якості хеш-функції та уникнення колізій.

Використання:

• Підтримується в PostgreSQL для точного пошуку.
• Широко застосовується в кешах та NoSQL-сховищах.

---

4. GiST (Generalized Search Tree)

GiST — узагальнена структура для побудови спеціалізованих індексів.
Вона дозволяє створювати індекси для різних типів даних: числових, геометричних, текстових тощо.

Особливості:

• Підтримує багатовимірні структури (координати, діапазони, просторові об’єкти).
• Є основою для R-Tree, SP-GiST і GIN.

Використання:

• Геоінформаційні системи (PostGIS).
• Пошук за відстанню, подібністю чи належністю.

---

5. GIN (Generalized Inverted Index)

GIN (зворотний індекс) використовується для структур, які містять множини значень (масиви, JSON, документи, текст).

Особливості:

• Зберігає посилання на всі записи, які містять певне слово або елемент.
• Підтримує типи ARRAY, JSONB, tsvector.
• Ефективний для повнотекстового пошуку.

Приклад:

CREATE INDEX idx_docs_text_gin 
ON documents USING gin(to_tsvector('english', content));

6. R-Tree (Rectangle Tree)

R-Tree — це просторовий індекс, призначений для дво- та тривимірних об’єктів, наприклад координат, полігонів або областей.

Принцип роботи:

Кожен вузол зберігає мінімальний обмежувальний прямокутник (MBR).

Пошук здійснюється через перевірку перетинів між запитом і MBR вузлів.

Використання:

Геоінформаційні системи (PostGIS, Spatialite).

Просторові запити типу «знайти об’єкти в межах області».

Порівняльна таблиця структур індексів

Тип індексу	Структура	Складність пошуку	Підтримка діапазонів	Основне застосування
B-Tree	Балансоване дерево	O(log n)	Так	Загальні запити
B+Tree	Листове дерево	O(log n)	Так	СУБД, файлові системи
Hash	Хеш-таблиця	O(1)	Ні	Пошук за точним збігом
GiST	Узагальнене дерево	Залежить	Так	Гео та складні типи
GIN	Зворотний індекс	O(log n)	Частково	Повнотекстовий пошук
R-Tree	Просторове дерево	O(log n)	Так	Географічні дані

Теми для самостійного вивчення

1. Алгоритмічні властивості B-дерев та їх модифікацій (B+, B*).
2. Підтримка й оновлення індексів у процесі зміни даних.
3. Порівняльний аналіз продуктивності B-Tree і Hash-індексів.
4. Індекси GiST та GIN у PostgreSQL і їх застосування.
5. Методи просторової індексації (R-Tree, QuadTree).
6. Індексація структурованих даних типу JSON і масивів.
7. Оптимізація складених індексів і вплив порядку полів.

Контрольні питання

1. Що таке індекс у базі даних і для чого він використовується?
2. Які основні типи індексів ви знаєте?
3. У чому полягає відмінність між унікальним і неунікальним індексом?
4. Що таке складений індекс і як порядок полів у ньому впливає на результат?
5. Поясніть принцип побудови B-Tree та його асимптотичну складність.
6. У чому переваги та недоліки Hash-індексу?
7. Коли доцільно використовувати GiST або GIN-індекси?
8. Які індекси застосовуються для геопросторових даних?
9. Як надмірна кількість індексів впливає на швидкодію БД?
10. Як оцінити ефективність індексів у SQL-запитах?

Додаткові матеріали

1. Database Indexing Explained (GeeksforGeeks)
2. PostgreSQL Documentation: Indexes
3. MySQL Performance: B-Tree vs Hash Indexes