Индексы в 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;

Кроме того, из-за возможности хранения одинаковых значений в БД, для совпадающих хэшей применяются методы разрешения коллизий.