Какая особенность MySQL вызывает такую разницу в обработке запросов?

Question

[Павел Китьян]

Приветствую. Предположим, что я хочу из таблицы messages получить данные сообщения, которое на сутки (или более) старше текущего (для текущего у меня есть его id).

Пробный запрос:

SELECT * FROM messages WHERE id = 
  (SELECT MAX(id) FROM messages WHERE 
     id < 40000 AND dt <= DATE_SUB('2016-04-18 23:23:23', INTERVAL 24 HOUR));

Функционирует быстро. Есть индекс на поле dt, хотя не уверен, что он используется в DATE_SUB()
А вот при использовании переменной - намертво глохнет:

SET @myid = 40000;
  (SELECT MAX(id) FROM messages WHERE 
     id < @myid AND dt <= DATE_SUB('2016-04-18 23:23:23', INTERVAL 24 HOUR));

Почему?! В чем особенность запроса?
В рабочем коде я использую хранимую процедуру, в которую передаётся myid. И, опять-таки, работает быстро.

DROP PROCEDURE IF EXISTS `TEST`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `TEST`(IN myid INT)
BEGIN
  (SELECT MAX(id) FROM messages WHERE 
     id < myid AND dt <= DATE_SUB('2016-04-18 23:23:23', INTERVAL 24 HOUR));
END$$
DELIMITER ;
CALL TEST(40000);

Comments

[Saboteur]

id < @myid AND dt
и
id < myid AND dt

?

Answer 1

[Melkij]

Посмотрите в explain, что планировщик думает по этим запросам. Есть большое подозрение, что хранимка и константный запрос идут по индексу по id, запрос с переменной - как-то иначе.

Одна очевидная грабля:
Значение переменной может изменяться во время выполнения запроса.
Константное значение в запросе и аргумент хранимки - не могут.
Соответственно моё предположение: глупый оптимизатор не смотрит, что в запросе переменная никак не изменяется, значит не может просто взять индекс по id и надо делать что-то другое. Скорей всего seq scan - судя по названию таблицы, данных подпадающих под условие dt <= дата куда больше, чем данных под него не попадающих. Для хорошей работы по индексу надо обратное свойство.

Comments

[Павел Китьян]

Знаете, смотрел немного, хотя и плаваю в понимании результатов EXPLAIN. Однако...
1) Если давать только вложенный запрос, без внешнего, то результаты одинаковы, как и план выполнения.
2) Если же завернуть как в условиях вопроса, то EXPLAIN у них очень разный. Там где переменная - появляется полное сканирование во внешнем запросе. Но главное - запрос виснет совсем, а не просто долго выполняется. Статус запроса на сервере - Sending data.
3) Опять-таки, если убрать условие по dt, то оба запроса выполняются нормально.

Честно говоря, непонятно отличие переменной заданной через SET и переданной в процедуру с точки зрения возможности её изменения. Ну пусть это не дает оптимизировать план выполнения или кэшировать запрос. Но неадекватно вешать? Таблица-то небольшая, не более 50 тыс. строк.

[Павел Китьян]

Да, и, если не затруднит, поясните сказанное по индексу на колонке dt. Имелось ввиду, что надо использовать DESC вместо ASC? Разве это влияет в случае, если индекс на одной колонке? Я не делал индекс на двух колонках id и dt. Там бы это имело наверное смысл.

Или обратный индекс - это вообще что-то другое?

[Melkij]

> непонятно отличие переменной заданной через SET и переданной в процедуру с точки зрения возможности её изменения
Я видимо спутал с каким-то другим диалектом, думал, что IN параметру в принципе ничего другого присвоить уже нельзя. Оказывается, можно.
Но переменные - всё-таки другая штука. Во время выполнения запроса переменную изменить легко, а вот параметр - уже нет, с ходу вообще не получилось найти способа. У них даже разный синтаксис.

По поводу индекса по dt - чтобы получить данные по индексу, надо прочитать индекс, доставать из него указатели на данные (а в случае Innodb это значения первичного ключа), потом доставать данные. Это всё дофига случайного чтения.
Если относительно общего массива данных вам нужно получить небольшое число строк - то по индексу работать всё-таки гораздо быстрее.
А вот если по индексу выбирается много данных относительно общего массива - часто выгоднее от использования индекса отказаться и читать напрямую таблицу простым и последовательным чтением seq scan. Некоторые данные прочитаем напрасно, но последовательное чтение на пару порядков быстрее случайного.
Судя по названию таблиц, у вас большая часть данных будет как раз в куске индекса до указанной даты.

Возвращаясь к теме - да, всего на 50 тыс записей виснуть не должен.
Посмотрите как mysql переписывает запрос: после explain сделайте show warnings - будет примерный вид переписанного оптимизатором запроса. Может будет понятнее, что он решает делать странно.