Індекси в базах даних. Види індексів.
.png?locale=ua)
Індекси в базах даних використовуються для прискорення пошуку та отримання даних. Вони працюють аналогічно до покажчиків у книзі, дозволяючи швидко отримувати доступ до необхідних записів без сканування всієї таблиці. Основні цілі використання індексів:
1. Прискорення пошуку. Індекси дозволяють швидко знаходити рядки, що відповідають певним умовам запиту (наприклад, `WHERE`).
2. Оптимізація сортування та групування. Якщо запит містить операції `ORDER BY` або `GROUP BY`, індекси допомагають прискорити виконання.
3. Прискорення об'єднання (JOIN). При об'єднанні таблиць індекси допомагають швидко знаходити відповідні рядки у зв’язаних таблицях.
4. Забезпечення унікальності. Унікальні індекси гарантують відсутність дубльованих значень у стовпці або групі стовпців.
5. Підвищення продуктивності. Індекси зменшують кількість операцій введення/виведення (I/O), що особливо важливо для великих таблиць.
Однак індекси мають і недоліки:
- Додаткове використання пам’яті. Індекси займають додатковий дисковий простір.
- Навантаження на оновлення. Під час вставки, оновлення або видалення даних індекси повинні оновлюватися, що може уповільнювати ці операції.
Тому індекси слід створювати з урахуванням конкретних запитів і навантаження на базу даних, щоб досягти оптимального балансу між продуктивністю та витратами на обслуговування.
Типи індексів.
Існує кілька типів індексів у базах даних, кожен з яких призначений для певних випадків використання. Основні типи індексів:
1. Одностовпцеві індекси
- Створюються для одного стовпця таблиці.
- Підходять для прискорення пошуку за одним стовпцем.
Приклад: `CREATE INDEX idx_name ON users (name);`
2. Складені індекси
- Створюються для кількох стовпців.
- Корисні для запитів, що фільтрують або сортують дані за кількома стовпцями.
- Порядок стовпців в індексі має значення: запити повинні використовувати префікс індексу (наприклад, якщо індекс створений на `(col1, col2)`, запити, що фільтрують тільки за `col1`, використовуватимуть індекс, а запити лише за `col2` — ні).
Приклад: `CREATE INDEX idx_name_age ON users (name, age);`
3. Унікальні індекси
- Гарантують, що всі значення в індексованому стовпці (або групі стовпців) є унікальними.
- Використовуються для підтримки цілісності даних.
Приклад: `CREATE UNIQUE INDEX idx_email ON users (email);`
4. Повнотекстові індекси
- Призначені для пошуку текстових даних (наприклад, ключових слів або фраз).
- Підтримують складні пошукові запити, такі як пошук за синонімами або морфологією.
Приклад: `CREATE FULLTEXT INDEX idx_content ON articles (content);`
5. Просторові індекси
- Використовуються для роботи з геопросторовими даними (наприклад, координатами, геометрією).
- Оптимізують пошук географічних об'єктів.
Приклад: `CREATE SPATIAL INDEX idx_location ON places (coordinates);`
6. Хеш-індекси
- Використовують хеш-функції для швидкого пошуку за точними збігами.
- Ефективні для операцій `=` , але не підходять для діапазонних запитів (наприклад, `BETWEEN`, `>`).
Приклад: `CREATE INDEX idx_hash ON users USING HASH (email);`
7. B-деревоподібні індекси
- Найпоширеніший тип індексів.
- Підтримують діапазонні запити (наприклад, `>`, `<`, `BETWEEN`), а також точні збіги.
Приклад: `CREATE INDEX idx_btree ON users (age);`
8. Бітові індекси
- Використовуються для стовпців із невеликою кількістю унікальних значень (наприклад, стать, статус).
- Ефективні для операцій `AND`, `OR` та `NOT`.
Приклад: `CREATE BITMAP INDEX idx_status ON orders (status);`
9. Кластеризовані індекси
- Визначають фізичний порядок даних у таблиці.
- Таблиця може мати лише один кластеризований індекс.
Приклад: У MySQL первинний ключ (`PRIMARY KEY`) автоматично створює кластеризований індекс.
10. Некластеризовані індекси
- Створюють окрему структуру даних, що посилається на рядки в таблиці.
- Таблиця може мати кілька некластеризованих індексів.
Приклад:`CREATE INDEX idx_nonclustered ON users (name);`
11. Часткові індекси
- Індексують лише підмножину даних, що відповідає певній умові.
- Корисні для великих таблиць, де індексація всіх рядків є неефективною.
Приклад: `CREATE INDEX idx_active_users ON users (name) WHERE status = 'active';`
12. Функціональні індекси
- Індексують результат функції, застосованої до стовпця.
- Корисні для запитів, які фільтрують або сортують дані на основі результатів функцій.
Приклад: `CREATE INDEX idx_lower_name ON users (LOWER(name));`
13. Покриваючі індекси
- Містять усі необхідні дані для виконання запиту, що дозволяє уникнути звернення до самої таблиці.
Приклад: `CREATE INDEX idx_covering ON users (id, name, age);`(якщо запит вибирає лише ці стовпці).
14. Індекси на виразах
- Подібні до функціональних індексів, але можуть містити складніші вирази.
Приклад: `CREATE INDEX idx_expr ON orders (total_price * quantity);`
15. Індекси для масивів (GIN, GiST, BRIN)
- Використовуються в PostgreSQL для роботи з масивами, JSON, повнотекстовим пошуком та іншими складними типами даних.
Приклад: `CREATE INDEX idx_gin ON documents USING GIN (tags);`
Вибір правильного типу індексу залежить від структури даних, типів запитів і вимог до продуктивності. Неправильне використання індексів може знизити продуктивність, тому важливо аналізувати запити та тестувати їхній вплив на базу даних.
Назад