Почему MySQL может выполнять LIKE в индексе, когда в индексе есть какой-то ведущий подстановочный знак при использовании «покрывающего индекса»?
Это пример «Высокопроизводительного MySQL 3rd».
mysql> EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY' AND title like '%APOLLO%';
В книге сказано, что MySQL не может выполнить LIKE, как показано ниже.
MySQL can’t perform the LIKE operation in the index. This is a limitation of the low-level storage engine API, which in MySQL 5.5 and earlier allows only simple comparisons (such as equality, inequality, and greater-than) in index operations. MySQL can perform prefix-match LIKE patterns in the index because it can convert them to simple comparisons, but the leading wildcard in the query makes it impossible for the storage engine to evaluate the match. Thus, the MySQL server itself will have to fetch and match on the row’s values, not the index’s values.
После этого книга дала улучшение «отложенного соединения».
mysql> EXPLAIN SELECT * FROM products
-> JOIN (
-> SELECT prod_id FROM products WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'
-> ) AS t1 ON (t1.prod_id=products.prod_id);
Даже (actor, title, prod_id) является «покрывающим индексом», MySQL не может также выполнять LIKE в индексе.
Я так растерялся!
@GordonLinoff Я не уверен, почему MySQL может выполнять LIKE в индексе во втором выражении. Я думаю, что они одинаковы, обеим нужно получить строку, потому что есть начальный подстановочный знак.
Да, индекс — это отсортированный список элементов, которые ставят под угрозу индекс. Это похоже на поиск в телефонной книге имен «%brook%», нет эффективного способа сделать это. Однако, если вам нужен «ручей%», вы можете начать со страницы «ручей» и сканировать вперед.
Ответы 1
Это оптимизация, которая работает с техническими ограничениями работы MySQL и в меньшей степени с логикой. Особенно ваше понимание того, что вы не можете использовать индекс для прямого поиска совпадений для ведущего подстановочного знака, верно.
Основная проблема заключается в том, что покрывающий индекс в MySQL 5.5 технически не делал того, что вы предполагаете (и мог бы делать).
Чтобы правильно прочитать цитируемое утверждение из книги, вы должны знать, что существует разница между Сервер MySQL и базовым механизм хранения. Сервер MySQL принимает ваш запрос, решает, как его выполнить, отправляет запрос в механизм хранения (InnoDB) через API, и возвращает несколько строк.
Итак, для вашего первого запроса MySQL запрашивает у InnoDB следующие данные: все столбцы (select *), используя индекс для поиска actor='SEAN CARREY'. Хотя было бы неплохо, и вы предполагаете, что это сделает покрывающий индекс, но, к сожалению, он не может также напрямую исключить строки на основе title like '%APOLLO%', потому что
This is a limitation of the low-level storage engine API, which in MySQL 5.5 and earlier allows only simple comparisons (such as equality, inequality, and greater-than) in index operations.
Поскольку вы запросили *, он извлекает все столбцы, что требует просмотра данных таблицы, для всех строк с правильным актером (с использованием индекса) из механизма InnoDB, а затем фильтрует их впоследствии, поскольку
the MySQL server itself will have to fetch and match on the row’s values, not the index’s values.
Во втором запросе серверу MySQL нужны только prod_id (согласно запросу) и title (для сравнения where) от механизма хранения. Теперь это фактически охвачено индексом! Хотя верхний уровень по-прежнему должен выполнять оценку title like '%APOLLO%', механизму хранения теперь не нужно считывать фактические данные таблицы для выполнения запроса на подзапрос.
Теперь сервер MySQL может оценить полученные данные и отправить другой запрос в подсистему хранения, чтобы получить все столбцы для prod_id, которые удовлетворяют where-условию. В крайних случаях это может вообще не фильтровать (например, каждая строка с actor='SEAN CARREY' может также выполнять title like '%APOLLO%'), и тогда отложенное соединение может быть немного медленнее, поскольку в целом вы выполняете больше работы.
Вы думаете, что это не то, что должен делать покрывающий индекс? Ты прав. И MySQL 5.6 научился это делать более правильно:
Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine.
[...]
MySQL can use the index to scan through people with
zipcode='95054'. The second part (lastname LIKE '%etrunia%') cannot be used to limit the number of rows that must be scanned, so without Index Condition Pushdown, this query must retrieve full table rows for all people who havezipcode='95054'.With Index Condition Pushdown, MySQL checks the
lastname LIKE '%etrunia%'part before reading the full table row. This avoids reading full rows corresponding to index tuples that match the zipcode condition but not the lastname condition.
Так как он нужен был только для обхода технических проблем, отложенное соединение здесь больше не нужно (хотя забывать о нем не стоит, оно может быть полезно и в других ситуациях). Ваш объяснить вывод для вашего первого запроса теперь должен включать
Using index condition(JSON property: using_index_condition)Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary. See Section 8.2.1.5, “Index Condition Pushdown Optimization”.
Какой у Вас вопрос?