Объясните что такое "индекс" в базах данных и их роль для приложений.

Как индексы баз данных помогают улучшить производительность запросов?

Подсказки:

• Подумайте о том, как база данных искала бы записи без индекса.
• Использование индексов имеет какие-то дополнительные затраты? (доп место хранения или скорость)
• Обсуждение различных типов индексов, таких как B-дерево, хеш или полнотекстовый, может быть полезным.

Выше ожиданий:

• Понимание селективности индекса, составных индексов и того, когда следует использовать определенные типы индексов (B-дерево для диапазонных запросов, хеш для точных совпадений, полнотекстовый для поиска по тексту).
• Знание влияния индексов на операции записи и способность объяснять планы выполнения запросов.

Индекс в БД — это специальные структуры данных, которые повышают скорость операций по извлечению данных. За счет того, что индекс содержит компактную информацию о местонахождении наборов данных в выбранном стоблце или стоблцах.

Без индексов базы данных выполняют полные сканирования таблиц, проверяя каждую строку в таблице для поиска совпадений.

Как индексы улучшают производительность

Индексы значительно ускоряют запросы SELECT, но могут замедлить операции INSERT, UPDATE и DELETE, так как структура индекса должна быть обновлена.

Пример разницы в производительности:

-- Без индекса: может потребоваться секунды или минуты на больших таблицах
SELECT * FROM customers WHERE email = 'user@example.com';

-- С индексом по столбцу email: часто миллисекунды

Типы индексов

1. B-tree индексы (сбалансированное дерево): наиболее часто используются, отлично подходят для запросов с диапазонами и сравнений по равенству

   Корень
   ├── Ветка1 (значения < 50)
   │   ├── Лист1 (значения 1-25)
   │   └── Лист2 (значения 26-49)
   └── Ветка2 (значения ≥ 50)
       ├── Лист3 (значения 50-75)
       └── Лист4 (значения 76-100)
   

2. Хэш-индексы: отлично подходят для сравнений по точному равенству, но бесполезны для диапазонов

3. Индексы полнотекстового поиска (Full-text): специализированы для эффективного поиска текстового содержимого

4. (Гео-)Пространственные индексы: оптимизируют запросы к географическим данным

Селективность индексов и составные индексы

Селективность индекса относится к уникальности значений в индексированном столбце. Высокая селективность (много уникальных значений) делает индексы более эффективными.

Составные индексы объединяют несколько столбцов:

CREATE INDEX idx_lastname_firstname ON users(last_name, first_name);

Они полезны для запросов, фильтрующих по нескольким столбцам, но порядок важен — приведенный выше индекс помогает с WHERE last_name = 'Smith', но не с WHERE first_name = 'John'.

Доп затраты при использовании индексов

• Стоимость хранения: индексы занимают дополнительное дисковое пространство
• Нагрузка при записи: каждая операция записи должна обновлять индексы
• Нагрузка по обслуживанию: индексы необходимо время от времени перестраивать

Планы выполнения запросов баз данных

Современные базы данных используют оптимизаторы запросов, чтобы определить, использовать ли индексы. Вы можете просмотреть эти решения с помощью EXPLAIN:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

Это помогает выявить отсутствующие или неиспользуемые индексы.