MYSQL-подобный запрос не показывает полное сканирование таблицы в объяснении

У меня есть запрос, работающий в Mysql, например (запутанные имена)

explain 
select this_.id as id1_0_,
this_.column1 as column1,
this_.column2 as column2,
this_.column3 as column3,
this_.column4 as column4,
this_.column5 as column5,
from
tablename this_
where
this_.column1 like '/blah%'
and this_.column2 = 'a9b51a14-4338-94f7-f23dbf9d539e'
and this_.column3 <> 'DUH'
and this_.column4=0
and this_.column5 like '%somename%'
order by this_.created desc 
limit 20

Изменить: столбец 1 имеет индекс BTREE, столбец 2, столбец 3, столбец 4, столбец 5, созданные все имеют индексы HASH.

Таблица имеет один внешний ключ, который выбирается в предложении select, но не в предложении WHERE.

Мне говорили и читали, что

как %somename%

приведет к полному сканированию таблицы. Однако, когда я запустил объяснение, вывод объяснения

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  this_   ref  somecolumnandinexnames 111 const   30414   Using where

Вывод объяснения выглядит точно так же, если я уберу подобное предложение.

Основываясь на этом, мы решили запустить запрос в производство только для того, чтобы обнаружить, что на практике запрос с like выполнялся намного дольше (несколько секунд по сравнению с несколькими миллисекундами без Like).

Есть ли объяснение, почему объяснение не предупредило меня об этом заранее?

Изменить: наблюдения

  • Убрав порядок, запрос снова будет выполняться быстро, даже если LIKE все еще присутствует.
  • Разделение на подзапрос с подобным во внешнем запросе, как указано ниже в посте, действительно работает!

Как говорит @Uueerdo, перемещение остальных условий в подзапрос фактически повышает производительность! Поэтому у меня возникает соблазн заключить, что одна из вещей, которые могут произойти, заключается в том, что предложение WHERE с подобным выполняется до других условий, ведущих к большому набору результатов. Как говорит @Uueerdo, перемещение остальных условий в подзапрос фактически повышает производительность! Поэтому у меня возникает соблазн заключить, что одна из вещей, которые могут произойти, заключается в том, что предложение WHERE с подобным выполняется до других условий, ведущих к большому набору результатов. Однако у меня до сих пор нет объяснения, почему удаление порядка ускоряет производительность. Запрос выбирает все 10 строк, поэтому порядок должен быть довольно быстрым.

Есть ли способ увидеть порядок, в котором MYSQL оценивает запрос. Кажется, я помню, как однажды видел какое-то графическое представление в MS SQL Server, объясняющее планы. Не помню, было ли это точно так же.


mysql explain
person Devu    schedule 03.06.2016    source источник
comment
пожалуйста, покажите результат: SHOW CREATE TABLE имя_таблицы; - и - SELECT * FROM tablename PROCEDURE ANALYSE(); чтобы проанализировать это немного больше   -  person Bernd Buffen    schedule 04.06.2016

Ответы (2)


arrow_upward
3
arrow_downward

Даже если он не требует сканирования таблицы, он все равно может быть дорогим; что, вероятно, происходит, так это то, что MySQL использует другие условия в вашем where для первоначального выбора строки-кандидата, а затем уменьшает эти результаты с остальными условиями.

Если имеется большое количество кандидатов и/или значения column5 длинные, для оценки этого условия может потребоваться некоторое время. Имейте в виду, что LIMIT стоит после WHERE, так что это не уменьшает объем необходимой работы.

Вы можете заметить некоторое улучшение, если поместите большую часть запроса в подзапрос и отфильтруете его результаты по условию like '%somename%' во внешнем запросе.

Что-то типа:

SELECT * FROM (
   SELECT t.id as id1_0_
      , t.column1, t.column2, t.column3, t.column4, t.column5
      , t.created
   FROM tablename AS t
   WHERE t.column2 = 'a9b51a14-4338-94f7-f23dbf9d539e'
      AND t.column3 <> 'DUH'
      AND t.column4=0
      AND t.column1 like '/blah%'
) AS subQ
WHERE subQ.column5 like '%somename%'
ORDER BY subQ.created DESC 
LIMIT 20
person Uueerdo    schedule 03.06.2016
comment
но правильный составной индекс важнее для рабочей нагрузки. - person Bernd Buffen; 04.06.2016
comment
Составной индекс не поможет с like '%xyz', но я согласен; Я просто предположил, что у спрашивающего уже был соответствующий индекс. - person Uueerdo; 04.06.2016
comment
Они используют столбцы 2, 3 и 4 без лайков, а также аналоги из столбца 1 могут использовать индекс. поэтому вы уменьшаете набор результатов, когда у вас есть составной индекс бога - person Bernd Buffen; 04.06.2016
comment
Согласен, но также возможно, что 90% данных в базе данных с миллиардом строк имеют одинаковые значения для этих четырех полей. Это маловероятный сценарий, поэтому я выделил в ответе курсивом и жирным шрифтом; но возможно, поэтому я даже дал ответ. Также возможно, что автору запроса не разрешено изменять db. - person Uueerdo; 04.06.2016

arrow_upward
0
arrow_downward

Если у вас есть индекс, включающий все считываемые столбцы (в предложении SELECT или WHERE), MySQL сможет прочитать все эти значения из индекса без сканирования таблицы.

Также обратите внимание, что все столбцы первичного ключа также будут в индексе.

В этом случае, даже если он не сканирует всю таблицу, он будет сканировать каждую строку в индексе, чтобы обработать запрос LIKE '%somename%', поэтому он не будет особенно более эффективным.

person jkinkead    schedule 03.06.2016
comment
Насколько это будет эффективно, зависит от того, сколько строк получается в результате проиндексированной части запроса. - person Barmar; 04.06.2016