Вопросы по теме «Индексы в SQL: B-tree, составные индексы, EXPLAIN и когда индекс не работает»
Сначала изучите урок: Индексы в SQL: B-tree, составные индексы, EXPLAIN и когда индекс не работает
Всего: 7 вопросов
1. Что такое индекс в SQL, зачем он нужен и чем за него приходится платить?
Что такое индекс в SQL, зачем он нужен и чем за него приходится платить?
Индекс — отдельная упорядоченная структура, которая хранит значения одного или нескольких столбцов вместе со ссылками на строки и позволяет найти нужные строки без полного перебора таблицы. В InnoDB почти все индексы — B-дерево (B+tree).
- Без индекса MySQL проверяет каждую строку (full scan, в
EXPLAIN—type: ALL). - С индексом спускается по дереву к нужным значениям: ускоряются
WHERE, соединения и сортировка —ORDER BY столбец LIMIT 10берёт первые 10 значений из индекса безUsing filesort. - Цена — место на диске (копия индексированных столбцов) и более медленная запись: каждый
INSERTиDELETEобновляет все индексы таблицы, аUPDATEиндексированного столбца переставляет запись в индексе.
Индекс и ключ — не одно и то же: ключ задаёт правило (уникальность, ссылку), индекс ускоряет поиск. Для PRIMARY KEY, UNIQUE и FOREIGN KEY MySQL строит индекс сам, а обычный индекс из CREATE INDEX ничего не запрещает.
2. Как устроен B-tree индекс в InnoDB и чем кластерный индекс отличается от вторичного?
Как устроен B-tree индекс в InnoDB и чем кластерный индекс отличается от вторичного?
B-tree — сбалансированное дерево: значения отсортированы, все листья на одной глубине, поиск идёт от корня к листу за O(log n) чтений страниц. Соседние листья связаны, поэтому диапазоны и сортировка читаются подряд. InnoDB использует вариант B+tree: данные лежат только в листьях. Страница — 16 КБ, и даже для десятков миллионов строк высота дерева обычно 3–4 уровня.
- Кластерный индекс — это
PRIMARY KEY: его листья содержат строки целиком, поэтому поиск поidсамый быстрый. Без первичного ключа InnoDB берёт первыйUNIQUEпо столбцамNOT NULL, а если нет и его — скрытый 6-байтовый идентификатор строки. - Вторичный индекс хранит значения своих столбцов и значение первичного ключа. Поиск идёт в два этапа: найти
idво вторичном индексе, затем по каждомуidспуститься в кластерный.
Отсюда два вывода. Длинный первичный ключ (UUID в CHAR(36)) раздувает каждый вторичный индекс. А если запросу хватает столбцов вторичного индекса (включая id), второй этап не нужен — это покрывающий индекс, в EXPLAIN — Extra: Using index.
3. Как создать, удалить и посмотреть индексы в MySQL?
Как создать, удалить и посмотреть индексы в MySQL?
CREATE INDEX idx_topics_title ON topics (title);
ALTER TABLE topics ADD INDEX idx_topics_title (title); -- то же через ALTER TABLE
CREATE UNIQUE INDEX uq_topics_title ON topics (title); -- уникальный
CREATE INDEX idx_messages_topic_created ON messages (topic_id, created_at);
DROP INDEX idx_topics_title ON topics; -- или ALTER TABLE ... DROP INDEX
SHOW INDEX FROM messages;
- Индекс можно объявить и в
CREATE TABLE:INDEX idx_topics_title (title),KEY— синонимINDEX. - Имя уникально в пределах таблицы: повтор —
ERROR 1061: Duplicate key name. Уникальный индекс не создастся, если в столбце уже есть повторы (ERROR 1062). TEXTиBLOBцеликом не индексируются:ERROR 1170: BLOB/TEXT column 'body' used in key specification without a key length. Нужен префикс —(body(50)).SHOW INDEXвыводит строку на каждый столбец каждого индекса:Non_unique = 0— уникальный,Cardinality— оценка числа разных значений,Index_type—BTREE.
Индексы для PRIMARY KEY, UNIQUE и внешнего ключа MySQL создаёт сам.
4. Что такое составной индекс и правило левого префикса? В каком порядке ставить столбцы?
Что такое составной индекс и правило левого префикса? В каком порядке ставить столбцы?
Составной индекс строится по нескольким столбцам: записи упорядочены по первому, при равном первом — по второму и так далее. Поэтому индекс (a, b, c) работает для условий на a, на a и b, на все три, но не на b или c без a. Это правило левого префикса, как в справочнике по фамилии и имени: всех Ивановых найти легко, всех Иванов — нет. Порядок условий в WHERE не важен, важен порядок столбцов в индексе.
- Сначала равенство, потом диапазон. Для
topic_id = 2 AND created_at >= '2024-03-01'индекс(topic_id, created_at)читает один отрезок (key_len9), а(created_at, topic_id)захватывает все темы после 1 марта (key_len5). После диапазона следующие столбцы для поиска не используются. - Индекс под WHERE и ORDER BY.
WHERE topic_id = 2 ORDER BY created_at DESC LIMIT 20с индексом(topic_id)даётUsing filesort, а с(topic_id, created_at)—Backward index scan: строки уже упорядочены, MySQL останавливается после 20.
5. Как прочитать EXPLAIN в MySQL: что означают type, key, key_len, rows и Extra?
Как прочитать EXPLAIN в MySQL: что означают type, key, key_len, rows и Extra?
EXPLAIN SELECT ... показывает план и не выполняет запрос.
typeот лучшего к худшему:const(одна строка по первичному или уникальному ключу),eq_ref,ref(все строки с значением неуникального индекса),range(диапазон индекса),index(весь индекс),ALL(вся таблица).possible_keys— подходящие индексы,key— выбранный.possible_keysне пуст, аkey: NULL— индекс есть, но оптимизатор решил читать таблицу.key_len— байты ключа:INT NOT NULL4,INT NULL5,DATETIME5. По нему видно, сколько столбцов составного индекса задействовано.rowsиfiltered— оценки по статистике, а не точные числа.Extra:Using index— покрывающий индекс,Using index condition— условие проверяется на записях индекса,Using filesort— отдельная сортировка,Using temporary— временная таблица.
EXPLAIN ANALYZE (MySQL 8.0.18+) запрос выполняет и показывает реальное время и число строк рядом с оценками.
6. Почему MySQL не использует индекс, хотя он есть?
Почему MySQL не использует индекс, хотя он есть?
Индекс бесполезен, когда условие нельзя превратить в поиск по упорядоченным значениям. В EXPLAIN это key: NULL или type: ALL.
- Функция над столбцом:
DATE(created_at) = '2024-03-01'. Переписать в диапазонcreated_at >= '2024-03-01' AND created_at < '2024-03-02'или создать функциональный индекс. - Ведущий
%:LIKE '%рыбалк%';LIKE 'Зимн%'индекс использует. - Строковый столбец сравнивают с числом:
phone = 79001234567—Note 1739вSHOW WARNINGS. Передавать строку. ORпо разным столбцам — нужен индекс на каждом (index merge) илиUNION.- Нарушен левый префикс:
author_id = 3при индексе(topic_id, author_id). - Низкая селективность:
author_id = 1— треть таблицы, последовательный перебор дешевле переходов в кластерный индекс. Это не ошибка. !=,NOT INи разные кодировки или collation вJOIN.
7. Когда индекс не нужен или вредит и как безопасно удалить лишний индекс?
Когда индекс не нужен или вредит и как безопасно удалить лишний индекс?
Индекс не нужен на маленьких таблицах (справочник из десятка строк умещается в одну страницу), на столбцах, по которым не ищут, и на столбцах с двумя-тремя значениями. Вредит он на часто меняющихся данных: каждый лишний индекс замедляет INSERT, UPDATE и DELETE. Счётчик просмотров в индексе при каждом UPDATE удаляется и вставляется на новое место.
Дублирующий индекс: (a) лишний, если есть (a, b), — составной обслуживает те же запросы по левому префиксу. Обратное неверно, а (b, a) — другой индекс. Индекс внешнего ключа fk_messages_topic (topic_id) удаляется, когда есть (topic_id, created_at); без него — ERROR 1553: Cannot drop index ... needed in a foreign key constraint.
На рабочей базе индекс сначала делают невидимым: ALTER TABLE messages ALTER INDEX имя INVISIBLE; (MySQL 8.0). Оптимизатор его не использует, но индекс обновляется, и если запрос замедлился, видимость возвращают мгновенно.