Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 49
А
не такой же результат даст?
SELECT DATE(fp.created) AS dt, COUNT(1) AS qnt
FROM forum_posts AS fp
WHERE fp.created >= '2013-01-01'
AND fp.createde < '2013-02-01'
GROUP BY DATE(fp.created)
не такой же результат даст?
Ну и подзапрос у вас:
Группируете по DATE(fp2.created), fp2.user_id, а выводите fp2.id. Обычно выводят либо поля, по которым группировка, либо другие, обернутые в агрегатные функции.
...
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
...
Группируете по DATE(fp2.created), fp2.user_id, а выводите fp2.id. Обычно выводят либо поля, по которым группировка, либо другие, обернутые в агрегатные функции.
А почему бы и нет? Мне надо было просто фильтрануть исходную таблицу перед тем как группировать.
Дело в том, что запрос в такой форме практически не имеет смысла. Другие СУБД (Например: ORACLE, PostgreSQL) вернули бы Вам ошибку.
Ну я бы мог написать
MIN(fp2.id). Суть же просто в фильтрации таблицы, чтобы дата и пользователь были уникальными.
А вы попробуйте так написать — интересно удалит ли MariaDB GROUP из такого запроса.
Потому что насколько я понимаю эту оптимизацию, она в качестве неявного предположения как раз использует то, что в запросе не будут выбираться поля, не участвующие в группировке. В вашем же случае (когда такие поля выбираются) «GROUP BY without a corresponding HAVING clause is equivalent to a DISTINCT» становится неверным.
Потому что насколько я понимаю эту оптимизацию, она в качестве неявного предположения как раз использует то, что в запросе не будут выбираться поля, не участвующие в группировке. В вашем же случае (когда такие поля выбираются) «GROUP BY without a corresponding HAVING clause is equivalent to a DISTINCT» становится неверным.
> Другие СУБД (Например: ORACLE, PostgreSQL) вернули бы Вам ошибку.
Вот и вся разница между правильными СУБД и MySQL.
Вот и вся разница между правильными СУБД и MySQL.
у Вас в данных 3 уникальные даты — 2013-01-01, 2013-01-02, 2013-02-02
Условию WHERE fp2.created >= '2013-01-01' AND fp2.created < '2013-02-01' удовлетворяют 2013-01-01 и 2013-01-02
Результаты подзапроса Вы группируете по дате.
Ожидаемо получаете 2 результата. А что неожиданного?
Условию WHERE fp2.created >= '2013-01-01' AND fp2.created < '2013-02-01' удовлетворяют 2013-01-01 и 2013-01-02
Результаты подзапроса Вы группируете по дате.
Ожидаемо получаете 2 результата. А что неожиданного?
3 разные даты специально, чтобы показать, что WHERE в подзапросе работает.
Я удивляюсь не двум записям, а числам 10 и 8. Должно быть 5 и 4.
Я удивляюсь не двум записям, а числам 10 и 8. Должно быть 5 и 4.
И, к слову, такие подзапросы — зло. Вложенный SELECT выполнится для каждого ряда столько раз сколько, грубо говоря, будет проверяться индексов во внешнем запросе.
Да! Именно об этом и пост: оптимизатор MySQL ведет довольно странно.
На mysqlperformanceblog ещё в 2010 году писали, что в версии 6.0 оптимизатор заточат под такие запросы.
На mysqlperformanceblog ещё в 2010 году писали, что в версии 6.0 оптимизатор заточат под такие запросы.
С версии 5.6 мускуль стал оптимизировать конструкции вида WHERE… IN (SELECT ..), но на подзапросы всё равно накладывается ряд ограничений. Подробнее можете почитать в мануале вот тут Optimizing Subqueries with Semi-Join Transformations
Вернул это:
2013-01-01 10
2013-01-02 8
А должен был это
2013-01-01 5
2013-01-02 4
2013-01-01 10
2013-01-02 8
А должен был это
2013-01-01 5
2013-01-02 4
А на mysql 5.6 кто-нибудь может проверить, что там будет?
Вот это вернет вам 5 и 4:
SELECT COUNT(DISTINCT fp2.user_id)
FROM forum_posts fp2
WHERE fp2.created >= '2013-01-01'
AND fp2.created < '2013-02-01'
GROUP BY DATE(fp2.created)
Garbage In, Garbage Out
Говорим MySQL, подразумеваем MariaDB.
Пишем кривой запрос, ищем причину в MySQL. Ой, извините, в MariaDB.
Правильно ли я понял суть, не пишите кривые запросы, порой, они выдают неожиданные результаты? =)
Говорим MySQL, подразумеваем MariaDB.
Пишем кривой запрос, ищем причину в MySQL. Ой, извините, в MariaDB.
Правильно ли я понял суть, не пишите кривые запросы, порой, они выдают неожиданные результаты? =)
Тут был вариант запроса, который уже предложили
Так и не понял, зачем подзапрос?
SELECT DATE( fp.created ) AS dt, COUNT( DISTINCT fp.user_id ) AS qnt
FROM forum_posts AS fp
WHERE fp.created >= '2013-01-01'
AND fp.created < '2013-02-01'
GROUP BY DATE( fp.created )
LIMIT 0 , 30
Я предпочитаю считать это багом. Использовать негруппируемые столбцы в запросе можно — это MySQL-ская нестандартная фича. А раз фича, значит оптимайзер должен ее поддерживать, в том числе и в подзапросах.
mariadb.atlassian.net/browse/MDEV-4595
Но все-таки дело тут не в «полной совместимости и прозрачности перехода», а в намного более умном оптимизаторе подзапросов. Если перейти с MySQL 5.6 на MariaDB 5.5 — то в запросе топикстартера будет как раз полная совместимость и прозрачность. Одинаково неправильный результат.
mariadb.atlassian.net/browse/MDEV-4595
Но все-таки дело тут не в «полной совместимости и прозрачности перехода», а в намного более умном оптимизаторе подзапросов. Если перейти с MySQL 5.6 на MariaDB 5.5 — то в запросе топикстартера будет как раз полная совместимость и прозрачность. Одинаково неправильный результат.
Вложенные SELECTы имхо вообще плохая затея, 2 раза пробовал использовать и оба раза приходил к выводу что надо или небольшую переделку базы/таблиц сделать (чтобы избавиться от вложенности), выполнить на стороне скрипта вложенный SELECT и скормить его результать в IN или исхитриться с JOIN. В любом случа вложенные запросы клали базу на неопределенный период.
ps: хотя всегда есть вариант что я просто не умею вложенными запросами пользоваться.
ps: хотя всегда есть вариант что я просто не умею вложенными запросами пользоваться.
Не понятно зачем в указанном вами случае использовать вложенный запрос. Более корректно использовать таки join. В любом случае нормальный оптимизатор именно в него и развернет этот запрос.
Мне кажется в посте я уделил достаточно внимания этому моменту:
JOIN всем хорош. Но не любой JOIN легко сделать, когда ты используешь ORM.
Корректней и от JOIN отказаться.
И производить джойны руками на клиенте?
Зачем?
Прекрасно обходится без JOIN и без подзапросов.
Прекрасно обходится без JOIN и без подзапросов.
Ну вы опять за свое :) Есть конкретный запрос, который я привел в посте. В нем джойн не нужен. С этим я согласен.
Есть класс запросов с подзапросами, которые можно преобразовать в джойн. И их лучше преобразовать к джойну, пока оптимизаторы СУБД не поумнеют.
Ваш коммент «Корректней и от JOIN отказаться» показался мне призывом отказаться от джойнов вообще. Все чаще вижу людей, которые очень любят к этому призывать и недоумеваю по этому поводу.
Есть класс запросов с подзапросами, которые можно преобразовать в джойн. И их лучше преобразовать к джойну, пока оптимизаторы СУБД не поумнеют.
Ваш коммент «Корректней и от JOIN отказаться» показался мне призывом отказаться от джойнов вообще. Все чаще вижу людей, которые очень любят к этому призывать и недоумеваю по этому поводу.
Не факт. В MariaDB 5.3+ и MySQL 5.6+ оптимизатор может намного больше, чем просто развернуть в join [1]. И большинство этих стратегий лучше чем join. Так что «join лучше подзапроса» — эта истина верна для MySQL 5.5 и раньше. И только.
[1] kb.askmonty.org/en/subquery-optimizations/
[1] kb.askmonty.org/en/subquery-optimizations/
Я тоже сталкивался с подобной проблемой при переходе на MariaDB.
Смысл запроса был примерно такой:
SELECT * FROM (SELECT a, b FROM t1 ORDER BY b) GROUP BY a
Прикол в том, что MySQL возвращал для каждого a наименьший b, а MariaDB для каждого a — черте-какой b.
Я написал баг-репорт, а мне объяснили так:
Поэтому советую очень внимательно относиться к группировке и сортировке во вложенных запросах.
А в Вашем случае, конечно, всё без проблем делается без подзапросов.
Помните, что MySQL любит делать декартово произведение кортежей, где надо, и где не надо.
Смысл запроса был примерно такой:
SELECT * FROM (SELECT a, b FROM t1 ORDER BY b) GROUP BY a
Прикол в том, что MySQL возвращал для каждого a наименьший b, а MariaDB для каждого a — черте-какой b.
Я написал баг-репорт, а мне объяснили так:
>> I know that while grouping MySQL uses first met value for those columns that are not grouped.
No, it doesn't. Again, you rely on pure luck; and now, *this* is explicitly documented in MySQL manual:
MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. <..> You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.
Поэтому советую очень внимательно относиться к группировке и сортировке во вложенных запросах.
А в Вашем случае, конечно, всё без проблем делается без подзапросов.
Помните, что MySQL любит делать декартово произведение кортежей, где надо, и где не надо.
Для MySQL и его форков крайне предпочтительно настраивать SQL modes: по умолчанию (для обратной совместимости) mysql ведет себя весьма, кхм, вольготно.
Если бы был выставлен ONLY_FULL_GROUP_BY (или любой combination mode, его включающий), проблема бы не возникла — такой запрос бы просто не выполнился. При выключенном же ONLY_FULL_GROUP_BY надо всегда быть готовым к undefined behavior.
Если бы был выставлен ONLY_FULL_GROUP_BY (или любой combination mode, его включающий), проблема бы не возникла — такой запрос бы просто не выполнился. При выключенном же ONLY_FULL_GROUP_BY надо всегда быть готовым к undefined behavior.
для того чтобы сделать джойн, необходимо сначала описать связь.
В 2.3 появилась возможность делать произвольные джоины: github.com/doctrine/doctrine2/pull/368
У вас проблема не в запросе, а в подходе к решению задачи. Вы ее пытаетесь решать в лоб вместо того, чтобы решать ее правильно.
У вас есть конкретная задача «Необходимо посчитать количество пользователей, которые хотя бы раз писали на форум в течение дня за последний месяц». Звучит очень просто, верно? Перефразирую, чтобы было более понятно ДБА: необходимы каунтеры постов в день по каждому юзеру за месяц.
То есть нужно получить user_id, date, posts_cnt.
Но, вы отталкиваетесь от текущей реализации структуры БД. На самом деле с ней нет никаких проблем кроме одной, в вашем случае, вы не можете выполнить задачу, стояющую перед вами не прибегая к таким вот запросам. Каст DATE(fp.created) убивает индекс по fp.created, например. Если у вас хотябы 10 миллионов постов в базе, вы в глубокой ****. Что делать? Всё просто — при инсерте поста в базу подготавливайте данные заранее:
INSERT INTO forum_posts SET post = 'post text', created = NOW(), user_id = 'user id'
Помните, «необходимы каунтеры постов в день по каждому юзеру»?
Получите:
INSERT INTO user_posts_by_date SET user_id = 'user id', `date` = CURDATE(), posts_cnt = 1 ON DUPLICATE KEY UPDATE posts_cnt = posts_cnt +1
несложно догадаться, что date + user_id должен быть unique. Можете еще партицирование по месяцам сделать
Тогда ваш запрос превратится в:
select * from user_posts_by_date where `date` between 2013-01-01' and 2013-02-01'
это будет выполняться В РАЗЫ быстрее не говоря о том, что это более наглядно.
У вас есть конкретная задача «Необходимо посчитать количество пользователей, которые хотя бы раз писали на форум в течение дня за последний месяц». Звучит очень просто, верно? Перефразирую, чтобы было более понятно ДБА: необходимы каунтеры постов в день по каждому юзеру за месяц.
То есть нужно получить user_id, date, posts_cnt.
Но, вы отталкиваетесь от текущей реализации структуры БД. На самом деле с ней нет никаких проблем кроме одной, в вашем случае, вы не можете выполнить задачу, стояющую перед вами не прибегая к таким вот запросам. Каст DATE(fp.created) убивает индекс по fp.created, например. Если у вас хотябы 10 миллионов постов в базе, вы в глубокой ****. Что делать? Всё просто — при инсерте поста в базу подготавливайте данные заранее:
INSERT INTO forum_posts SET post = 'post text', created = NOW(), user_id = 'user id'
Помните, «необходимы каунтеры постов в день по каждому юзеру»?
Получите:
INSERT INTO user_posts_by_date SET user_id = 'user id', `date` = CURDATE(), posts_cnt = 1 ON DUPLICATE KEY UPDATE posts_cnt = posts_cnt +1
несложно догадаться, что date + user_id должен быть unique. Можете еще партицирование по месяцам сделать
Тогда ваш запрос превратится в:
select * from user_posts_by_date where `date` between 2013-01-01' and 2013-02-01'
это будет выполняться В РАЗЫ быстрее не говоря о том, что это более наглядно.
Конечно вы правы! Мой запрос далек от оптимальности. Раскрою секрет: в посте я немного слукавил.
Задача: «Необходимо посчитать количество пользователей, которые хотя бы раз писали на форум в течение дня за последний месяц» была придумана для демонстрации этого конкретного запроса. На самом мне было необходимо сделать конструктор, который выдает аналитику по предварительно обработанным данным. В простом случае это: «фильтрануть уникальных пользователей за день». Но душа маркетолога крайне многогранна: завтра это может быть «пользователи у которых больше 5 постов за день», а послезавтра — «пользователи у которых от 2 до 5 постов, которые зарегистрировались более месяца назад, но у них до сих пор не заполнен профиль». В связи с этим я решил разбить задачу на два этапа: сначала фильтруем данные, потом считаем по ним аналитику. С помощью подзапроса данная задача решается наиболее просто. Но, к сожалению, не оптимально.
Ну и самое главное: пост не о том, как решить конкретную задачу лучше всего, а о том, как затейливо работают оптимизаторы MySQL и MariaDB выполняя данный конкретный запрос.
Задача: «Необходимо посчитать количество пользователей, которые хотя бы раз писали на форум в течение дня за последний месяц» была придумана для демонстрации этого конкретного запроса. На самом мне было необходимо сделать конструктор, который выдает аналитику по предварительно обработанным данным. В простом случае это: «фильтрануть уникальных пользователей за день». Но душа маркетолога крайне многогранна: завтра это может быть «пользователи у которых больше 5 постов за день», а послезавтра — «пользователи у которых от 2 до 5 постов, которые зарегистрировались более месяца назад, но у них до сих пор не заполнен профиль». В связи с этим я решил разбить задачу на два этапа: сначала фильтруем данные, потом считаем по ним аналитику. С помощью подзапроса данная задача решается наиболее просто. Но, к сожалению, не оптимально.
Ну и самое главное: пост не о том, как решить конкретную задачу лучше всего, а о том, как затейливо работают оптимизаторы MySQL и MariaDB выполняя данный конкретный запрос.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Неожиданные результаты простого MySQL-запроса