Індекси відіграють важливу роль у оптимізації запитів (query optimization), але їх використання відрізняється в базах даних (OLTP) і сховищах даних (OLAP). Нижче наведено структурований огляд стратегій індексації для SQL Server (OLTP), Synapse (OLAP) та MongoDB (NoSQL).
1. Індекси в SQL Server (OLTP — Реляційна база даних)
🔹 Призначення індексів в OLTP
- Прискорюють запити SELECT, зменшуючи необхідність виконувати повний скан таблиці.
- Оптимізують операції WHERE, JOIN, ORDER BY, GROUP BY.
- Підвищують ефективність пошуку в транзакційних навантаженнях.
- Компроміс: Збільшують накладні витрати на операції INSERT/UPDATE/DELETE.
🔹 Типи індексів в SQL Server
1.1 Кластеризований індекс
- Визначає фізичний порядок рядків.
- Один на таблицю (зазвичай це первинний ключ).
- Швидший для діапазонних запитів і сортування.
✅ Приклад:
CREATE CLUSTERED INDEX idx_order_id ON orders(order_id);
📌 Найкраще для: Запитів, що фільтрують за order_id, сортування даних.
1.2 Некластеризований індекс
- Зберігає вказівники на рядки замість їх перестановки.
- Таблиця може мати багато некластеризованих індексів.
- Покращує продуктивність для WHERE.
✅ Приклад:
CREATE INDEX idx_customer ON orders(customer_id);
📌 Найкраще для: Швидких пошуків по часто використовуваних стовпцях.
1.3 Унікальний індекс
- Забезпечує відсутність дублікативних значень.
- Автоматично створюється для обмежень
PRIMARY KEYіUNIQUE.
✅ Приклад:
CREATE UNIQUE INDEX idx_email ON users(email);
📌 Найкраще для: Запобігання дублюванням електронних адрес, імен користувачів.
1.4 Композитний індекс (Індекс на кілька стовпців)
- Індексує кілька стовпців разом.
- Використовується, коли запити фільтрують за кількома стовпцями.
✅ Приклад:
CREATE INDEX idx_customer_order ON orders(customer_id, order_date);
📌 Найкраще для: Запитів, що використовують customer_id + order_date разом.
1.5 Покриваючий індекс
- Зберігає всі стовпці, необхідні для запиту, в самому індексі.
- Уникає додаткових звернень, покращуючи продуктивність.
✅ Приклад:
CREATE INDEX idx_orders ON orders(customer_id) INCLUDE (order_date, total_amount);
📌 Найкраще для: Запитів, що потребують customer_id, order_date, total_amount.
1.6 Фільтрований індекс
- Створює індекс для підмножини даних.
- Зменшує розмір індексу та пришвидшує пошуки.
✅ Приклад:
CREATE INDEX idx_active_orders ON orders(order_id) WHERE status = 'Active';
📌 Найкраще для: Запитів, що часто фільтрують status = 'Active'.
1.7 Повнотекстовий індекс
- Оптимізований для пошуку текстових даних (наприклад, описів продуктів, логів).
✅ Приклад:
CREATE FULLTEXT INDEX idx_desc ON products(description);
📌 Найкраще для: Пошуку великих текстових полів.
Кращі практики індексації в SQL Server
✅ Використовуйте sp_helpindex('table_name') для перевірки наявних індексів.
✅ Уникайте індексації стовпців з низькою кардинальністю (наприклад, gender, is_active).
✅ Використовуйте INCLUDE для створення покриваючого індексу та уникання додаткових звернень.
✅ Моніторьте індекси за допомогою sys.dm_db_index_usage_stats.
2.
Індекси в Synapse (OLAP — Сховище даних)
🔹 Призначення індексів в OLAP
- Призначені для пакетних запитів (batch queries) та агрегацій.
- Запити сканують великі таблиці, тому стратегія індексації відрізняється від OLTP.
- Замість B-дерев, Synapse використовує Columnstore Indexes для швидкої аналітики.
🔹 Типи індексів в Synapse
2.1 Кластеризований індекс Columnstore (за замовчуванням)
- Зберігає дані в стиснутому стовпцевому форматі.
- Найкраще підходить для швидкого сканування великих наборів даних.
- Індекс за замовчуванням у Synapse.
✅ Приклад:
CREATE CLUSTERED COLUMNSTORE INDEX idx_orders ON orders;
📌 Найкраще для: Великих таблиць фактів в сховищах даних.
2.2 Кластеризований індекс (Rowstore)
- Зберігає дані по рядках, як у OLTP.
- Використовується, коли необхідні часті пошуки одного рядка.
✅ Приклад:
CREATE CLUSTERED INDEX idx_product_id ON products(product_id);
📌 Найкраще для: Таблиць вимірів з частими пошуками.
2.3 Некластеризований індекс
- Діє як традиційний індекс пошуку.
- Використовується, коли часто запитуються конкретні стовпці.
✅ Приклад:
CREATE NONCLUSTERED INDEX idx_customer ON orders(customer_id);
📌 Найкраще для: З'єднань між великими таблицями.
Кращі практики індексації в Synapse
✅ Уникайте надмірної кількості індексів → Це збільшує витрати на обслуговування.
✅ Використовуйте Columnstore Index для великих таблиць фактів → Це зменшує розмір і прискорює сканування.
✅ Використовуйте Rowstore Index для малих таблиць пошуку → Швидші запити для одного рядка.
✅ Коректно розподіляйте дані → Використовуйте DISTRIBUTION HASH(customer_id) для рівномірного розподілу даних.
3. Індекси в MongoDB (NoSQL)
🔹 Призначення індексів в NoSQL
- MongoDB не зберігає дані у рядках і стовпцях.
- Використовує B-дерева і хеш-індексацію для прискорення пошуків.
- Підтримує текстову індексацію, просторову індексацію та інше.
🔹 Типи індексів в MongoDB
3.1 Індекс одного поля
- Працює як некластеризований індекс в SQL.
- Прискорює запити по одному полю.
✅ Приклад:
db.orders.createIndex({ "customer_id": 1 })
📌 Найкраще для: Швидких пошуків за customer_id.
3.2 Композитний індекс
- Індексує кілька полів разом.
- MongoDB використовує правило лівого префікса (індекси повинні використовуватись в порядку).
✅ Приклад:
db.orders.createIndex({ "customer_id": 1, "order_date": -1 })
📌 Найкраще для: Запитів, що фільтрують за customer_id і сортують за order_date.
3.3 Текстовий індекс
- Оптимізований для повнотекстового пошуку в документах.
✅ Приклад:
db.products.createIndex({ "description": "text" })
📌 Найкраще для: Пошуку в описах продуктів, блогах, логах.
3.4 Хешований індекс
- Індексує поле за допомогою хешованого значення.
- Прискорює пошуки рівності, але не підходить для діапазонних запитів.
✅ Приклад:
db.users.createIndex({ "email": "hashed" })
📌 Найкраще для: Пошуку унікальних значень на зразок email.
Кращі практики індексації в MongoDB
✅ Використовуйте explain("executionStats") для аналізу продуктивності запитів.
✅ Уникайте надмірної кількості індексів → Кожен індекс збільшує накладні витрати на запис.
✅ Використовуйте композитні індекси розумно → Переконайтесь, що запити відповідають порядку індексів.
✅ Текстові індекси слід використовувати вибірково → Вони дорогі для великих наборів даних.
Висновок: SQL Server проти Synapse проти MongoDB
Кожна система оптимізує індексацію по-різному в залежності від робочого навантаження.
Для OLTP (SQL Server) → Орієнтуйтесь на B-Tree індекси.
Для OLAP (Synapse) → Використовуйте Columnstore індекси.
Для NoSQL (MongoDB) → Використовуйте хешовані, композитні та текстові індекси.
Перекладено з: Indexes: Database vs. Data Warehouse Perspective