Почему запрос стал работать в разы дольше?

Question

[HocReal]

После обновления Mysql на версию 5.5.53 стали в разы тормозить все запросы на выборку,
ниже пример. Скорее всего проблема в неверном определении оптимизатором порядка выполнения запроса, но как это победить? Судя по slow log сканируется вся таблица:
# Query_time: 5.574717 Lock_time: 0.000030 Rows_sent: 1 Rows_examined: 195589

Медленный запрос

select 
	t.id,
	t.name,
	t2.name
from 
	table t
	left join table2 u on t.user = t2.id
where 
	t.data = 1
	and t.is_deleted = 0 
order by 
	t.d_mod desc 
limit 20;

Если убираем order by и limit, то все работает быстро

select 
	t.id,
	t.name,
	t2.name
from 
	table t
	left join table2 u on t.user = t2.id
where 
	t.data = 1
	and t.is_deleted = 0;

Answer 1

[Дмитрий Дарт]

Если вы уверены что дело в неправильном выборе индекса оптимизатором можно MySQL вручную указать какой конкретно индекс использовать:
SELECT * FROM `table` USE INDEX (name_index)
На 5.5 радикальных изменений не было, это вот 5.5 -> 5.6 с репликацией проблемы, поэтом многие сидят на 5.5
Можно попробовать индексы пересоздать если индексов и данных не много.

Comments

[HocReal]

Не понятно почему вообще так с обновлением произошло, order by и limit ведь должны выполнятся после where? А сейчас получается что нет.

[HocReal]

USE INDEX помогает решить проблему в рамках этого запроса, но получается сейчас нужно все 100-500 запросов переписывать, а это не решение проблемы

[HocReal]

Возможно я сильно скакнул по версии?
Помогите пожалуйста решить проблему.

[Дмитрий Дарт]

HocReal: Индекс пробовали пересоздать хотя бы для 1 запроса, может новой версии что-то не нравится в старом индексе?

[Дмитрий Дарт]

HocReal: Составной индекс на 2 поля t.data и t.is_deleted есть?

[HocReal]

Дмитрий: да есть.

[Дмитрий Дарт]

HocReal: Ну если индекс есть и не используется, тогда можно сделать OPTIMIZE TABLE (но во время пока он делается ничего работать не будет) может селективность индекса низкая и даже составной индекс неэффективен, такое весьма вероятно.
Далее есть случаи когда mysql не использует индексы, например:

"Если использование индекса требует от MySQL прохода более чем по 30% строк в данной таблице (в таких случаях просмотр таблицы, по всей видимости, окажется намного быстрее, так как потребуется выполнить меньше операций поиска). Следует учитывать, что если подобный запрос использует LIMIT по отношению только к извлекаемой части строк, то MySQL будет применять индекс в любом случае, так как небольшое количество строк можно найти намного быстрее, чтобы вернуть результат."

"Если диапазон изменения индекса может содержать величины NULL при использовании выражений ORDER BY ... DESC"

Копайте дальше "Если убираем order by и limit" а если убрать только order by? А если убрать только limit? Обычно запросы замедляет как раз limit, поэтому надо чётко идентифицировать проблему. Сколько записей в таблице всего и сколько выбирается без order by и limit проще говоря если вы выбираете из миллиона 20, это не очень эффективно потому то сортируете вы всё равно миллион

[HocReal]

Дмитрий: Таблица Innodb, когда делаю oprimize такая ошибка
Table does not support optimize, doing recreate + analyze instead

[HocReal]

Дмитрий: Кажется проблема более менее обозначилась, если используется составной индекс t.data и t.is_deleted то появляются тормоза, если удаляю этот составной индекс, остаются одиночные индексы на t.data и t.is_deleted то скорость приемлемая.

Получается проблема не в оптимальных индексах?

[Дмитрий Дарт]

HocReal: Да. В составном индексе колонка data идёт первой в паре а колонка is_deleted второй? Удалив этот индекс вы возможно потеряете в эффективности в каком-то другом запросе где он был более эффективен чем одиночные. Вы тестируете с sql_no_cache? Потму что может быть какие-то запросы уже закешировались и данные по скорости недостоверны....

[HocReal]

data первый, is_deleted второй, действительно без sql_no_cache.
есть еще поле d_mod, на нем тоже индекс, это дата последнего изменения

[HocReal]

Удалив составные индексы из slow_query исчезли запросы

[Дмитрий Дарт]

HocReal: Ну работайте тогда если всё ок )) Но slow_query не выключайте ;)

[HocReal]

Дмитрий: Спасибо за помощь.

Answer 2

[Макс]

EXPLAIN?

Comments

[HocReal]

Explain почему то врет:
2091 Using where

Slow log показывает (сканируется вся таблица)
# Query_time: 5.574717 Lock_time: 0.000030 Rows_sent: 1 Rows_examined: 195589

[Макс]

HocReal: значит не используется индекс. "Using where" - всего лишь показатель наличия условия

[Макс]

HocReal: так что лучше все-же приведите полный EXPLAIN

[HocReal]

'1','SIMPLE','c','index','org,is_deleted,org_2','d_mod','4',NULL,'2394','Using where'
'1','SIMPLE','u','eq_ref','PRIMARY','PRIMARY','4','database.c.user','1',''