Документация: Оптимизация индексов и запросов в SQL

Вопрос интервью 25: Что такое индекс? Расскажите нам о своем понимании индекса

Ответ:
Индекс в базе данных - это структура данных, которая ускоряет операции поиска и извлечения данных из таблицы. Аналогично оглавлению в книге, индекс позволяет СУБД быстро находить данные без полного сканирования таблицы.

Основные характеристики:

• Ускоряет SELECT, WHERE, ORDER BY, GROUP BY

• Замедляет INSERT, UPDATE, DELETE (требуется обновление индекса)

• Занимает дополнительное место на диске

• Может быть кластеризованным или некластеризованным

Вопрос интервью 26: Каковы преимущества B+Tree перед B-Tree?

Ответ:
Преимущества B+Tree:

1. Все данные хранятся в листьях, что делает поиск по диапазону более эффективным

2. Листья связаны в linked list, упрощая range-запросы

3. Более высокая плотность хранения ключей во внутренних узлах

4. Более стабильная производительность (все запросы проходят одинаковое количество узлов)

5. Лучшая производительность для дискового ввода-вывода (блоки данных больше и выровнены)

Вопрос интервью 27: Сколько данных может хранить B+Tree?

Ответ:
Емкость B+Tree зависит от:

• Размера страницы (обычно 16KB в InnoDB)

• Размера ключа

• Размера указателя

• Уровней дерева

Пример расчета для 3-уровневого B+Tree:

• Внутренний узел может хранить ~1200 ключей (16KB / (8B ключ + 6B указатель))

• Листья хранят данные (например, 16B на запись)

• Емкость = 1200 * 1200 * (16KB/16B) ≈ 1.44 млрд записей

Вопрос интервью 28: Каковы преимущества и недостатки индекса хэширования?

Преимущества:

• O(1) время поиска для точных совпадений

• Эффективен для операций равенства (=)

Недостатки:

• Не поддерживает range-запросы (>, <, BETWEEN)

• Не поддерживает сортировку

• Возможны коллизии

• Неэффективен при частых изменениях данных

• Обычно не сохраняется на диске (только в памяти)

Вопрос интервью 29: Что такое кластеризованный индекс и чем он отличается от некластеризованного индекса?

Кластеризованный индекс:

• Определяет физический порядок данных в таблице

• Только один на таблицу (InnoDB использует первичный ключ)

• Данные хранятся в листьях индекса

• Быстрее для range-запросов

Некластеризованный индекс:

• Отдельная структура от данных

• Может быть несколько на таблицу

• Содержит указатели на данные

• Требуется дополнительный шаг для доступа к данным

Вопрос интервью 30: Что такое возврат таблицы? Как решить проблему возврата стола?

Возврат таблицы (Table lookup/Bookmark lookup):
Когда некластеризованный индекс не покрывает все необходимые поля, СУБД должна выполнить дополнительный поиск в кластеризованном индексе или куче.

Решение:

1. Использовать покрывающие индексы (include все необходимые поля)

2. Уменьшить количество выбираемых столбцов

3. Использовать кластеризованный индекс

4. Оптимизировать размер строк (меньше данных для чтения)

Вопрос интервью 31: Что такое индекс pushdown?

Индекс pushdown (Index Condition Pushdown, ICP):
Оптимизация в MySQL, где условия фильтрации применяются на уровне движка хранения, а не сервера. Это уменьшает количество строк, передаваемых на сервер.

Преимущества:

• Уменьшает количество обращений к таблице

• Улучшает производительность для сложных условий

• Особенно эффективен для составных индексов

Вопрос интервью 32: Как выбрать тип первичного ключа базы данных? Автоинкремент или UUID?

Автоинкремент:

• Плюсы: компактный, последовательный (лучше для индексов), быстрый

• Минусы: предсказуемый, сложности при шардинге

UUID:

• Плюсы: уникальный в распределенных системах, безопаснее

• Минусы: больше места, случайный порядок (фрагментация)

Рекомендация:

• Для монолитных систем - автоинкремент

• Для распределенных систем - UUID v7 (временные UUID с упорядочиванием)

Вопрос интервью 33: Пожалуйста, расскажите об архитектуре системы MySQL

Архитектура MySQL:

1. Соединительный уровень: управление соединениями, аутентификация

2. Серверный уровень: парсер, оптимизатор, кэш запросов

3. Движки хранения: InnoDB (по умолчанию), MyISAM, Memory и др.

4. Файловая система: табличные пространства, журналы транзакций

InnoDB компоненты:

• Buffer pool

• Change buffer

• Adaptive hash index

• Redo/undo logs

• Transaction system

Вопрос интервью 34: Каков процесс SQL-запроса в MySQL?

Процесс выполнения запроса:

1. Установка соединения

2. Анализ запроса (парсинг)

3. Преобразование в AST

4. Оптимизация (выбор плана выполнения)

5. Выполнение:

   • Чтение из кэша (если доступно)

   • Доступ через индексы

   • Фильтрация, сортировка

   • Возврат результатов

6. Запись в кэш (если применимо)

7. Возврат клиенту

Вопрос интервью 35: Вы когда-нибудь использовали Explain? Каковы ее основные направления?

EXPLAIN анализирует план выполнения запроса.

Ключевые поля:

• id: порядок выполнения

• select_type: тип операции (SIMPLE, SUBQUERY и т.д.)

• table: таблица

• type: тип доступа (ALL, index, range и т.д.)

• possible_keys: возможные индексы

• key: выбранный индекс

• rows: оценка строк

• Extra: дополнительная информация

Вопрос интервью 36: Что означают разные значения в поле «Тип» и дополнительном поле?

Типы доступа (type):

• ALL: полное сканирование таблицы

• index: полное сканирование индекса

• range: диапазон по индексу

• ref: поиск по не уникальному индексу

• eq_ref: поиск по уникальному индексу

• const: поиск по первичному ключу

Extra:

• Using index: покрывающий индекс

• Using where: фильтрация после доступа

• Using temporary: временная таблица

• Using filesort: внешняя сортировка

• Using join buffer: буферизация соединения

Вопрос интервью 37: На что следует обратить внимание при создании индекса?

Рекомендации:

1. Индексировать часто используемые WHERE условия

2. Учитывать порядок столбцов в составных индексах

3. Избегать индексов на часто изменяемые столбцы

4. Использовать покрывающие индексы

5. Учитывать селективность (высокая селективность лучше)

6. Избегать избыточных индексов

7. Мониторить использование индексов

Вопрос интервью 38: Что такое индекс Samsung?

Индекс Samsung (Skip Scan Index):
Оптимизация, когда MySQL использует составной индекс, даже если не указан первый столбец. Работает, когда первый столбец имеет мало уникальных значений.

Пример:
Индекс (gender, age) может быть использован для запроса WHERE age > 30, если gender имеет мало значений (M/F).

Вопрос интервью 39: Как избежать аннулирования индекса, используемого при выполнении запроса?

Способы:

1. Не использовать функции над индексированными столбцами

2. Избегать неявных преобразований типов

3. Не использовать отрицательные условия (NOT, !=)

4. Избегать OR с неиндексированными столбцами

5. Не использовать leading wildcards в LIKE ('%term')

6. Учитывать порядок столбцов в составных индексах

Вопрос интервью 40: Можете ли вы объяснить основной принцип правила самого левого префикса?

Принцип:
Составные индексы используются слева направо. Запрос должен включать первый столбец индекса, затем может включать второй и т.д.

Пример:
Для индекса (A, B, C):

• Работает: WHERE A=1 AND B=2

• Работает: WHERE A=1

• Не работает: WHERE B=2 (без A)

Вопрос интервью 41: Поддерживает ли MySQL внутреннее кэширование запросов?

Кэш запросов MySQL:

• Кэширует полные результаты SELECT запросов

• Работает только для идентичных запросов

• Неэффективен при частых изменениях данных

• Отключен по умолчанию в MySQL 8.0

Альтернативы:

• Кэширование на уровне приложения

• Proxy-кэши (Redis, Memcached)

Вопрос интервью 42: В чем разница между InnoDB и MyISAM?

InnoDB:

• Поддержка транзакций (ACID)

• Строковые блокировки

• Кластеризованные индексы

• Внешние ключи

• Crash recovery

• Оптимизирован для записи

MyISAM:

• Нет транзакций

• Табличные блокировки

• Не кластеризованные индексы

• Быстрее для read-only нагрузок

• Поддержка FULLTEXT индексов

• Нет crash recovery

Вопрос интервью 43: Какие ситуации приведут к ухудшению производительности запросов SQL?

Причины:

1. Отсутствие подходящих индексов

2. Блокировки и конкуренция

3. Большое количество данных

4. Сложные соединения

5. Подзапросы и временные таблицы

6. Неоптимальные типы данных

7. Частые коммиты в транзакциях

8. Плохая статистика

Вопрос интервью 44: Как оптимизировать медленные запросы?

Процесс оптимизации:

1. Выявить медленные запросы (slow query log)

2. Анализировать EXPLAIN

3. Оптимизировать структуру индексов

4. Переписать запрос (упростить, изменить подход)

5. Оптимизировать схему данных

6. Настроить параметры сервера

7. Рассмотреть кэширование

8. Проверить после изменений

Вопрос интервью 45: Вы когда-нибудь узнавали об оптимизации параметров, связанных с InnoDB?

Ключевые параметры InnoDB:

• innodb_buffer_pool_size (70-80% памяти)

• innodb_log_file_size (больше для частых записей)

• innodb_flush_log_at_trx_commit (баланс durability/performance)

• innodb_read_io_threads / innodb_write_io_threads

• innodb_io_capacity (для SSD)

• innodb_stats_on_metadata (отключить для стабильности)

Вопрос интервью 46: Как оптимизировать JOIN?

Оптимизация JOIN:

1. Использовать индексы для join-столбцов

2. Уменьшать размер соединяемых таблиц (фильтрация до join)

3. Правильно выбирать порядок соединения (меньшие таблицы первыми)

4. Использовать покрывающие индексы

5. Рассмотреть denormalization для частых join

6. Использовать STRAIGHT_JOIN для указания порядка

7. Мониторить использование буферов соединения

Вопрос интервью 47: Какие типы можно выбрать при создании индекса?

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

1. B-Tree (основной тип в MySQL)

2. Hash (только Memory движок)

3. Full-text (для текстового поиска)

4. Spatial (для геоданных)

5. Composite (многоколоночные)

6. Covering (включают все необходимые поля)

7. Unique (гарантирует уникальность)

8. Clustered (InnoDB PK)

Вопрос интервью 48: Как оптимизировать заказ?

Оптимизация ORDER BY:

1. Использовать индексы для сортировки

2. Уменьшать количество сортируемых данных

3. Избегать сортировки больших результатов

4. Использовать покрывающие индексы

5. Рассмотреть denormalization для частых сортировок

6. Оптимизировать параметры sort_buffer_size

7. Избегать сортировки по неиндексированным столбцам

Вопрос интервью 49: Как оптимизировать группировку?

Оптимизация GROUP BY:

1. Использовать индексы для группировки

2. Фильтровать данные до группировки

3. Избегать группировки по выражениям

4. Использовать покрывающие индексы

5. Рассмотреть материализованные представления

6. Оптимизировать параметры tmp_table_size

7. Использовать ONLY_FULL_GROUP_BY для контроля

Вопрос интервью 50: Как рассчитать стоимость запроса для одной таблицы?

Факторы стоимости:

1. Тип доступа (сканирование таблицы vs индекс)

2. Количество читаемых строк

3. Фильтрация (селективность условий)

4. Сортировка и группировка

5. Временные таблицы

6. Ввод-вывод (дисковый vs память)

7. Размер строки

Формула оценки:
Стоимость ≈ (чтение индекса) + (чтение данных) + (фильтрация) + (сортировка) + (накладные расходы)