Индекс покрытия (и функциональный) MySQL 8.0.28 не используется
Я пытаюсь использовать индекс покрытия (с функциональным индексом) в MySQL 8.0.28, чтобы ускорить группировку по запросу в большой таблице InnoDB.
Индекс покрытия создан:
ALTER TABLE `sp_files`
ADD INDEX `ix_site_header_to_cleaned_text2` (`sp_site_foreign_key`, (length(`cleaned_text`)));
Используя только поля из покрывающего индекса, я ожидаю, что MySQL будет использовать индекс вместо базовой таблицы, поэтому я ожидаю, что в столбце Extra выходных данных EXPLAIN будет отображаться Using index:
EXPLAIN
SELECT sp_site_foreign_key,
count(case when length(`cleaned_text`) > 100 then 1 else NULL END) as num1,
count(1) as num2
FROM sp_files USE INDEX (`ix_site_header_to_cleaned_text2`)
GROUP by sp_site_foreign_key;
Есть идеи, почему он не использует индекс?
Все поля запроса содержатся в индексе ix_site_header_to_cleaned_text2, поэтому я ожидаю, что explain отобразится Using index в столбце Extra.
Ответы 2
Я не профессионал в MySQL, но что, если вы окружите ix_site_header_to_cleaned_text2 `` внутри FROM sp_files USE INDEX (ix_site_header_to_cleaned_text2)?
Он использует индекс (отсюда и тип: тип соединения индекса, показанный в отчете EXPLAIN), но не может добиться оптимизации покрывающего индекса.
Поддержка функциональных индексов в MySQL имеет некоторые ограничения, которые вы можете не заметить, если не внимательно прочтете руководство:
https://dev.mysql.com/doc/refman/8.0/en/generated-column-index-optimizations.html
Оптимизатор может использовать индексы сгенерированных столбцов для создания планов выполнения даже при отсутствии прямых ссылок в запросах на эти столбцы по имени. Это происходит, если предложение
WHERE,ORDER BYилиGROUP BYотносится к выражению, которое соответствует определению некоторого индексированного сгенерированного столбца.
Это означает, что он должен позволять вам использовать то же выражение, что и функциональная часть индекса (в данном случае length(cleaned_text)), и автоматически получать это значение из индекса, поэтому не нужно его пересчитывать.
Но это не работает, если вы используете это выражение в других предложениях запроса, например, в списке выбора в вашем случае или в предложении JOIN. Эта оптимизация не реализуется в других разделах, кроме тех, которые указаны в описании руководства.
Возможно, они улучшат поддержку функциональных индексов в будущих версиях MySQL.
Между тем, вы можете получить покрывающий индекс только в том случае, если вы явно определите виртуальный столбец и ссылаетесь на этот столбец в индексе и в своем запросе.
Демо, протестировано на MySQL 8.0.36:
CREATE TABLE sp_files (
id SERIAL PRIMARY KEY,
sp_site_foreign_key INT,
cleaned_text TEXT,
other INT,
length_cleaned_text INT AS (length(cleaned_text))
);
ALTER TABLE `sp_files`
ADD INDEX `ix_site_header_to_cleaned_text2` (`sp_site_foreign_key`, length_cleaned_text);
EXPLAIN
SELECT sp_site_foreign_key,
count(case when length_cleaned_text > 100 then 1 else NULL END) as num1,
count(1) as num2
FROM sp_files USE INDEX (`ix_site_header_to_cleaned_text2`)
GROUP by sp_site_foreign_key\G
Выход:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sp_files
partitions: NULL
type: index
possible_keys: ix_site_header_to_cleaned_text2
key: ix_site_header_to_cleaned_text2
key_len: 10
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index
О, вы правы, я пропустил эту часть документации MySQL!!! Я не осознавал, что столбец функционального индекса нельзя использовать в SELECT. Спасибо, идея с виртуальной колонкой просто золотая. Я только что реализовал это, и теперь EXPLAIN отображается Using index. СПАСИБО!!!
Спасибо, я только что попробовал - никакой разницы. Это имеет смысл, поскольку если бы он не смог получить этот индекс (поскольку ссылка на имя была бы отключена), тогда он не будет показывать это в столбце
keyвыводаexplain. Так что я определенно хочу, чтобы он использовал индексix_site_header_to_cleaned_text2. Чтобы убедиться, что это не сбивает с толку других, я отредактировал исходное сообщение, добавивix_site_header_to_cleaned_text2между ` в командуExplain.