Неожиданные результаты простого MySQL-запроса

Недавно я потратил кучу времени, пытаясь понять, почему один запрос выдает такой странный результат. На MariaDB он выдавал явно некорректный результат, а на старом добром MySQL не выполнялся вовсе. Т.е. запрос запускался, но дождаться его завершения не удавалось. Для того чтобы разобраться, в чем же дело, пришлось провести небольшое исследование. Но давайте обо всем по порядку.

Необходимо было посчитать количество пользователей, которые хотя бы раз писали на форум в течение дня за последний месяц.
Допустим сообщения форума лежат в следующей таблице:

CREATE TABLE `forum_posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `forum_posts` (`user_id`, `created`) VALUES
(1, '2013-01-01'),
(1, '2013-01-01'),
(2, '2013-01-01'),
(2, '2013-01-01'),
(2, '2013-01-01'),
(3, '2013-01-01'),
(3, '2013-01-01'),
(4, '2013-01-01'),
(5, '2013-01-01'),
(5, '2013-01-01'),
(1, '2013-01-02'),
(1, '2013-01-02'),
(2, '2013-01-02'),
(2, '2013-01-02'),
(3, '2013-01-02'),
(3, '2013-01-02'),
(4, '2013-01-02'),
(4, '2013-01-02'),
(1, '2013-02-02'),
(1, '2013-02-02'),
(2, '2013-02-02'),
(2, '2013-02-02'),
(3, '2013-02-02'),
(3, '2013-02-02'),
(4, '2013-02-02'),
(4, '2013-02-02'),
(5, '2013-02-02'),
(5, '2013-02-02');

Запрос, который выдает нужные данные::

SELECT DATE(fp.created) dt, count(*) qnt
FROM forum_posts fp
WHERE fp.id IN (
	SELECT fp2.id
	FROM forum_posts fp2
	WHERE fp2.created >= '2013-01-01'
	AND fp2.created < '2013-02-01'
	GROUP BY DATE(fp2.created), fp2.user_id
)
GROUP BY dt;

Результат выполнения этого запроса был неожиданным, он вернул:

2013-01-01   10
2013-01-02   8

Не поверив своим глазам, я бросился выполнять подзапрос отдельно — он вернул правильный результат (9 строк). То есть запрос с условием WHERE id IN (...) вернул больше записей, чем передано идентификаторов в IN (...). Стало ясно, что вечер предстоит долгий.

Что мы имеем?

У нас есть простой запрос с подзапросом. Подзапрос отсекает значения по WHERE, группирует по дате и пользователю, а затем передает полученные идентификаторы во внешний запрос, который группирует только по дате. Но, как показывает тест, БД игнорирует результаты подзапроса.

Поскольку я не верю в мистику и не очень доверяю себе по вечерам, я привлек к решению проблемы товарища xzander. Он запустил запрос на дампе моей БД (на самом деле там было около 4 млн строк). Результат был неожиданный — за 10 минут запрос не выполнился. Сравнивая окружения, мы выяснили, что я запускаю запрос на MariaDB (5.5.30), а он на MySQL (5.5.28)

Копнув глубже, мы поняли, что MySQL и MariaDB по-разному подходят к выполнению запроса. «Умный» оптимизатор MySQL решает, что надо сначала выполнить внешний запрос, и каждую строку сджойнить с подзапросом, выполнив его в итоге N раз. Таким образом, на большой БД такой запрос будет выполняться очень долго.

MariaDB поступает хитрее — она выполняет запрос быстро, но игнорирует GROUP BY в подзапросе и, как оказалось, это вовсе не баг, а фича. Вот тебе и полная совместимость и прозрачность перехода.

Казалось бы, как я вообще оказался в такой ситуации? Зачем подзапрос, если можно обойтись джойном? Все просто: в этом проекте использовалась Doctrine, а в ней, как в любой порядочной ORM, для того чтобы сделать джойн, необходимо сначала описать связь. Описывать же связь таблицы на саму себя только ради одного запроса — это уже как-то чересчур.

Что же делать?

Оказалось, что обмануть оптимизатор не составило труда: достаточно внушить ему, что подзапрос сложнее, чем ему кажется, добавив в подзапрос “HAVING 1”

Итого вот запрос, который корректно работает на MariaDB:

SELECT DATE(fp.created) dt, count(*) qnt
FROM forum_posts fp
WHERE fp.id IN (
	SELECT fp2.id
	FROM forum_posts fp2
	WHERE fp2.created >= '2013-01-01'
	AND fp2.created < '2013-02-01'
	GROUP BY DATE(fp2.created), fp2.user_id
	HAVING 1
)
GROUP BY dt;

На MySQL видимо все-таки придется переписать запрос, избавившись от подзапроса.

Какие выводы мы можем сделать?

1. Необходимо проверять результаты даже простых запросов. Не заметить, что такой запрос возвращает некорректные данные, было легко.
2. Переход от MySQL к MariaDB не такой прозрачный, как рассказывают.
3. Не стоит завязываться на ORM, когда необходимо выполнить сложные статистические запросы.

Автор: yetanotherape

Источник