Індекси в SQL

Передмова

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

Якщо порівняти БД з книгою, то індекси — це зміст, предметний покажчик. Можна, звісно, в пошуках потрібної інформації перечитати всю книгу, але набагато зручніше звернутися відразу до потрібної сторінки.

Область застосування

Індекси, як і будь-які інші інструменти, повинні застосовуватися осмислено. Немає сенсу ставити індекси, наприклад, на поля, за якими не буде здійснюватися пошук. Але є поля і таблиці, в яких індекси повинні бути обов'язково проставлені.

Наприклад, у нас є дві таблиці: Blog і Tag. Таблиці мають зв'язок "багато до багатьох" (ManyToMany), оскільки один запис у блозі може мати різні теги, як і кожен тег може належати кільком записам. Таким чином, у нас з'являється третя таблиця, назвемо її BlogTag, у якій є всього два стовпці: blog_id і tag_id, що посилаються на записи у відповідних таблицях. І в цій зв'язуючій таблиці обидва стовпці повинні бути не тільки проіндексовані, але й мати зовнішні ключі, що посилаються на записи у відповідних таблицях.

Як показала реальна практика, навіть у невеликих таблицях на ~100 000 записів, при використанні кількох операцій приєднання (JOIN), швидкість вибірки даних може збільшитися в рази. Я особисто у своєму досвіді досягав приросту швидкості обробки складних запитів майже в десять разів, з ~2.7 до ~0.4 секунд, просто проставивши потрібні індекси та прописавши зовнішні ключі.

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

B-tree

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

За таким індексом можна здійснювати пошук не тільки точних значень, але й відносних (більше або менше).

Такий індекс підійде для запитів наступних типів:

WHERE age = num;
WHERE age > num;
WHERE age < num;
WHERE name LIKE 'John%';

Водночас, цей індекс не підійде для запитів, у яких підстановка невизначених значень знаходиться на початку шуканого рядка, наприклад:

WHERE name LIKE '%Doe';

Оскільки індекс B-tree робить обхід по дереву, у цьому випадку початкова точка входу не визначена і індекс не буде врахований, натомість БД обійде всю таблицю в пошуках відповідних значень.

Це обмеження можна обійти, якщо додати reverse індекс. У такому випадку, запит виглядатиме інакше:

WHERE reverse(name) LIKE reverse('%Doe');

Для чого може використовуватися reverse? Наприклад, для пошуку адрес email конкретних серверів.

WHERE reverse(email) LIKE reverse('%@gmail.com');

HASH

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

WHERE name = 'John Doe';

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

WHERE name LIKE 'John%';
WHERE name LIKE '%Doe';
WHERE age > num;
WHERE age < num;
WHERE name IS NULL;

Крім того, через можливість зберігання однакових значень у БД, для співпадаючих хешів застосовуються методи розв'язання колізій.