AlexeyVD14 авг 2012 в 13:43

Несколько интересных приемов и особенностей работы с MySQL

3 мин

89K

MySQL *

Из песочницы

+99

Комментарии 83

gibson 14 авг 2012 в 14:34

спасибо, полезно — добавил пост в избранное

nooze 14 авг 2012 в 14:43

Интересные решения. Конечно не для рабочего варианта базы, а скорее как единичные запросы запускаемые вручную.

parpalak 14 авг 2012 в 15:11

Мне кажется, в подсчете количества различных записей в таблице вы предлагаете не оптимальный путь, потому что в нем не будут использоваться индексы. Другой способ — попробовать разбить один запрос на три и подобрать индексы. В конкретной задаче EXPLAIN SELECT подскажет, какой из способов быстрее.

Shedal 14 авг 2012 в 15:36

За что минусанули человека? Мне тоже кажется, что SUM(IF(f1 = 2, f2, 0)) индексы использовать если и будет, то гораздо менее оптимально, т.к. IF — это функция, и в неё нужно будет подать каждое существующее значение f1. В то же время, SELECT SUM(f2) WHERE f1 = 2 прекрасно использует индекс по условию, а потом уже просуммирует отфильтрованные данные.

niga 14 авг 2012 в 16:49

Насколько я помню, MySQL не использует индексы в подзапросах.

kuber 14 авг 2012 в 17:46

Выполните Explain и посмотрите. Зачем гадать?

kuber 14 авг 2012 в 16:28

Вы правы.
При наличии индексов у полей f1 и f2 запрос предложенный автором в реальности (за исключением специально подогнанных случаев) будет работать заметно медленнее, чем запрос, который автор называет не оптимальным.

Vampiro 14 авг 2012 в 17:27

Запрос с функциями также будет использовать индексы, и не обратится к основной таблице, если в селект не добавить других полей, а ключик сделать составным по (f1,f2). Почему нет?

Shedal 14 авг 2012 в 17:35

Одно дело index seek, совсем другое — index scan.

Vampiro 14 авг 2012 в 18:22

а значение count(1) в запрос упадет из астрала? =(

Shedal 14 авг 2012 в 15:41

Поиск данных за последнюю дату
…

SELECT p.uid, p.amount
  FROM payments p
  JOIN
    (SELECT uid, MAX(pay_date) AS max_dt
       FROM payments
       GROUP BY uid) sel ON p.uid = sel.uid AND p.pay_date = sel.max_dt;

В результатах будут дубликаты, если один пользователь совершил несколько платежей в один день.

niga 14 авг 2012 в 16:38

По моему GROUP BY не допустит этого.

kuber 14 авг 2012 в 17:14

Вы ошибаетесь. Будут там дубликаты, при условии, что пользователь совершил несколько платежей в последний день.

niga 14 авг 2012 в 17:50

pastebin.com/7scfDNrq Минусуйте

kuber 14 авг 2012 в 17:54

Вы структуру таблицы предложенную автором для этого запроса внимательно рассмотрели?

У него поле pay_date типа DATE.

niga 15 авг 2012 в 05:15

payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL(15, 2))

Я один вижу в этой таблице тип DATETIME?
Единственное, что я не воспроизвёл в своей таблице, так это тип поля amount, но оно в данном случае не играет роли.

kuber 15 авг 2012 в 05:45

>> payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL(15, 2))
Я один вижу в этой таблице тип DATETIME?

Автор поступил очень не красиво и поправил структуру таблицы в статье, после того как появились большинство комментариев на эту тему (примерно 15.08.2012 00:15).

Но даже поле типа DATETIME в данном случае Вас не спасет. Все равно будут дубликаты, но с точнотью до секунды, а не до дня.

equand 14 авг 2012 в 17:19

distinct не допустил бы этого, group by группирует тупо

humbug 14 авг 2012 в 17:51

Дело не в group by, а в типе pay_date. Если он timestamp, то и дубликатов не будет. Ну они возможны, если юзер умудрился совершить 2 операции подряд с разницей в 1 секунду.

kuber 14 авг 2012 в 17:59

>> Дело не в group by, а в типе pay_date.
Нет, тут дело в не правильно построенном запросе.

humbug 14 авг 2012 в 19:38

SELECT uid, MAX(pay_date) FROM payments GROUP BY uid;

Данный запрос гарантирует уникальные значения, но соединение значений с таблицей — нет. Еще раз, если тип будет timestamp, а при добавлении строки в поле pay_date будет добавляться current_timestamp, то запрос отработает как надо, даже если пользователь совершит несколько платежей в один день. Добавление записи с разницей < 1 секунду маловероятно. Niga доказал правильность запроса.

kuber 14 авг 2012 в 19:46

>> Добавление записи с разницей < 1 секунду маловероятно.
Вы это серьезно?

>> Niga доказал правильность запроса.
Он ошибся со структурой таблицы, и я ему это сразу пояснил. Не верите? возьмите и проверьте. Всё увидите своими глазами.

humbug 14 авг 2012 в 21:16

Вы это серьезно?

Всё упирается лишь в точность поля pay_date.

Он ошибся со структурой таблицы, и я ему это сразу пояснил. Не верите? возьмите и проверьте. Всё увидите своими глазами.

Так дело в запросе или в точности поля, хранящего время запроса? Вы уж определитесь.

В конце концов, можно решить это параноидальным запросом:

SELECT uid, amount
  FROM payments
  WHERE (id,uid) IN (SELECT MAX(id), uid
       FROM payments p
       GROUP BY uid, pay_date
       HAVING pay_date = (SELECT max(pay_date) FROM payments WHERE uid=p.uid)
  );

Shedal 14 авг 2012 в 21:48

Ага, вот только бОльший id не означает, что платёж был проведён позже. В наших бизнес-требованиях об это ничего не сказано. Знаете ли, бывают распределённые системы, карточки с чипами, и т.д., когда платёж совершается сегодня, а в БД попадает через неделю.
В общем, проблема задачи — в том, что типичной её назвать сложно. Слишком разные бывают требования.
Нужно показать данные о последнем платеже, а вот как выяснить, какой платёж на самом деле был последним, — не сказано.

humbug 14 авг 2012 в 21:56

Ага, вот только бОльший id не означает, что платёж был проведён позже.

Внимательно посмотрите на HAVING.

Shedal 14 авг 2012 в 22:06

Вы, наверное, не поняли, что я имел в виду. ВНУТРИ одной даты (наибольшей для данного uid), бОльший id не означает, что платёж был проведён позже. И неважно, какая точность у payment_date — день, или секунда. Всё равно может быть несколько платежей, совершённых за одну секунду.
А ваш запрос выдаст точно тот же результат, что и мой, который я написал на час раньше. Критерии выбора те же самые, хотя структура запросов совсем разная.
Проблема в том, что неизвестно, как выяснить, какой платёж выбирать из тех, что имеют одинаковое значение payment_date. Бизнес-требования недостаточно проработаны.

ef_end_y 14 авг 2012 в 22:17

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

Shedal 14 авг 2012 в 22:19

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

humbug 14 авг 2012 в 22:55

Ага, а hello world стоит писать так.

ef_end_y 15 авг 2012 в 06:11

Ну мой-то вариант рабочий, в отличие от варианта в статье. «Малая вероятность ошибки» — это самая тупая отмазка, которая может быть у программиста.

Shedal 15 авг 2012 в 07:36

А вот сейчас автор статьи уточнил бизнес-требования, и его запрос стал рабочим, а ваш — излишне сложным.

kuber 15 авг 2012 в 05:20

>> Так дело в запросе или в точности поля, хранящего время запроса? Вы уж определитесь.

Вы понимаете, что результат запроса зависит от структуру таблицы?

sphinxy 14 авг 2012 в 19:32

SELECT p1.uid, p1.amount
FROM payments p1
where not exists (select uid from payments p2 where p2.uid=p1.uid and p2.id>p1.id)

Shedal 14 авг 2012 в 19:38

Сработает только для случаев, когда больший id означает более поздний платёж. Что совсем не обязательно истинно.

sphinxy 14 авг 2012 в 19:42

Делов-то, p2.pay_date>p1.pay_date вместо id. Главное — смысл.

select uid в подзапросе тоже возможно не оптимально, если индекс по uid отсутствует, а по id индекс есть.

Shedal 14 авг 2012 в 19:45

pay_date вместо id

Тогда опять дубликаты появятся ;-)

sphinxy 14 авг 2012 в 20:03

Тогда задача не решаема? Максимум могу предложить суммировать все платежи в последний день, если их больше одного.

Shedal 14 авг 2012 в 20:26

Да, я думаю, что в описанном варианте задача действительно нерешаема. Наиболее близким к искомому, наверное, будет вот такой вариант:

SELECT
  p.uid,
  p.amount
FROM
  payments p
WHERE
  p.id = (
    SELECT p2.id
    FROM payments p2
    WHERE p.uid = p2.uid
    ORDER BY p2.pay_date DESC, p2.id DESC
    LIMIT 1
  )

(онлайн-пример)

ef_end_y 14 авг 2012 в 22:14

Ну или еще полухакерский вариант:

SELECT p.uid, p.amount
  FROM payments p
  JOIN
    (SELECT uid, MAX(pay_date*1000000 + id) AS max_dt
       FROM payments
       GROUP BY uid) sel ON p.uid = sel.uid
          AND (p.pay_date*1000000 + p.id) = sel.max_dt;

Думаю план у него будет плохим, да и 1000000 надо заменить на «очень большое значение». Короче, просто такой себе вариант

Shedal 14 авг 2012 в 22:36

Ну, если говорить о «хаках», можно и вот так :)

SELECT
  uid,
  CAST(
    SUBSTRING(
      MAX(CONCAT(pay_date, ',', LPAD(id, 11, '0'), ',', amount)),
      24
    ) AS SIGNED) amount
FROM
  payments
GROUP BY
  uid

AlexeyVD 14 авг 2012 в 20:26

Да, вы правы, спасибо, моя ошибка. Тут конечно же DATETIME.

Shedal 14 авг 2012 в 20:27

На самом деле, это мало что меняет.

Shedal 14 авг 2012 в 21:49

Ок, это меняет точность записи времени. Но дубликаты всё равно могут быть, хотя и с меньшей вероятностью.

kuber 15 авг 2012 в 05:24

И что Вам даст DATETIME?
Он Вам даст те же проблемы, но с точностью до секунды, а не до дней, что собственно ничего не меняет. Ваш запрос все равно будет возвращать дубликаты.

AlexeyVD 15 авг 2012 в 06:23

Согласен с вами, был выбран неудачный пример, сейчас немного конкретизирую задачу.

kuber 15 авг 2012 в 06:40

Только обязательно укажите как Вы поставили задачу с самого начала, а потом новую постановку, а то получается, что мы здесь ерунду пишем в комментариях.

Shedal 15 авг 2012 в 07:10

Вы привели какое-то абсолютно нереальное ограничение. Лучше бы написали, что если платежи произошли в одну секунду, то можно выбирать любой из них (например, тот, у которого больший id) — пользователю это подойдет. Ах да, хотя тогда бы ваш запрос не работал :)

easterism 14 авг 2012 в 15:53

Column 'id' in group statement is ambiguous
Я не понял «о чудо» этого абзаца. Очевидно же что для GROUP, id двусмысленный.
Так добавьте ему смысла.

SELECT t1.id, t2.id
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id_t1
  GROUP BY t1.id;

AlexeyVD 14 авг 2012 в 20:35

Именно об этом я и хотел сказать, чтобы люди не забывали добавлять смысла полям в группировке. Иначе можно нарваться на ошибку, добавив в список выводимых полей t2.id, хотя без него запрос прекрасно работал. В этом отличие MySQL от других СУБД, которые таких вольностей не допускают.

DoctorGester 14 авг 2012 в 16:36

Плохо разбираюсь в MySQL, не подскажите, что за ключевое слово ON? Гугл плохо ищет по коротким словам.

Shedal 14 авг 2012 в 16:40

ON используется в совокупности с JOIN'ами: dev.mysql.com/doc/refman/5.0/en/join.html

niga 14 авг 2012 в 16:43

После ON следует условие для выборки из связанных таблиц в случае JOIN.

doxa 14 авг 2012 в 16:45

ON — аналог WHERE, используемый при JOIN.

DoctorGester 14 авг 2012 в 16:55

Всем спасибо, все понял.

thebestzorro 14 авг 2012 в 16:59

Спасибо, хорошие рецепты.

Nevostruev 14 авг 2012 в 17:51

> Column 'id' in group statement is ambiguous

Для этого случая есть еще более простое решение: GROUP BY 1

Цифры в GROUP BY или ORDER BY обозначают номер колонки. Это плохая практика для рабочего кода. Но очень удобно, когда вам надо просто извлечь информацию из таблицы (особенно если вы пишете запрос руками).

taliban 14 авг 2012 в 17:59

Шикарно, наконец-то сбылась мечта идиота, я понял как сделать одно поле в выборке со счетчиком строк =)

truezemez 14 авг 2012 в 18:08

Дополню Ваш пост: Common MySQL Queries

shuler 14 авг 2012 в 18:44

Вы пизданулись что ли все? Кто эти люди кто добавил статью в избранное? Вы хотите открыть что-то новое для себя в повторном чтении этих обычных запросов?

Sauron 14 авг 2012 в 22:30

А вы про фичу с удалением дубликатов знали?
Ну и, как видно из коментов, даже в обычных запросах сам автор сделал ошибку — так что не всё так просто (-:

kuber 15 авг 2012 в 05:33

>> даже в обычных запросах сам автор сделал ошибку ...
В данном случаи из этого следует, что автор плохо разбирается в СУБД MySQL и крайне не внимательный, а не то, что он рассказал, что то сложное и интересное.
Он просто даже не потрудился протестировать свои запросы.

iDark 14 авг 2012 в 21:51

Если я узнал после прочтения статьи новое и добавил эту статью в избранное, то я «плохое слово»? Почему?

tagir_valeev 15 авг 2012 в 01:28

Про использование переменных не знал, спасибо. Решал такую задачу более громоздким запросом.

fifonik 15 авг 2012 в 01:55

SELECT *
  FROM address
  WHERE num + 0 = 1;

Жесть.
Это запрос из разряда «прощай индексы».
Хотя даже LIKE их будет использовать (хотя и плохенько).

FB3 15 авг 2012 в 05:37

SELECT t1.id, t2.id
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id_t1
  GROUP BY 1;

ef_end_y 15 авг 2012 в 06:14

Лучше стараться не использовать специфичные для mysql фишки — легче будет мигрировать на другую СУБД, если вдруг понадобится

kuber 15 авг 2012 в 06:19

>> Лучше стараться не использовать специфичные для mysql фишки
Может Вы еще посоветуете не использовать специцифичные для СУБД ORACLE фишки?

ef_end_y 15 авг 2012 в 06:25

Я ожидал, что в пример будет приведен эталон Оракл. Речь идет о нарушении стандартов. Например, group by мускула позволяет делать выборку полей, которые не участвуют в группировке. Это отход от стандарта. В большинстве случаев можно обойтись и без специфичных хаков

kuber 15 авг 2012 в 06:29

>> Речь идет о нарушении стандартов.

Вы знаете сколько дополнений к стандартам практически в любой СУБД (MySQL, MSSQL, PostgreSQL, ORACLE, ...)?

Если говорить о миграции, то речь скорее надо поднимать об использовании уровня абстракции над СУБД, но никак не отказываться от всяких вкусностей, которые предлагают различные СУБД.

ef_end_y 15 авг 2012 в 06:33

Да, наверное, соглашусь

webhamster 15 авг 2012 в 08:28

Насколько все эти трюки совместимы с PostgreSQL?

tolyjan 15 авг 2012 в 10:08

Вот ещё один хороший способ удалить дубликаты, не используя ключей

DELETE t1 FROM t1, t2 WHERE t1.id > t2.id AND t1.name = t2.name;

serjoga 15 авг 2012 в 10:20

Начало статьи реально порадовало ALTER IGNORE TABLE table1 ADD UNIQUE..., но в продолжении описаны методы, которые реально ломают использование индексов — огорчили.
На счет последнего примера я бы написал так:

SELECT ua.uid, ua.amount
FROM (
   SELECT p.uid, p.amount
   FROM payments p
   ORDER BY pay_date DESC
) ua
GROUP BY ua.uid

kuber 15 авг 2012 в 10:33

Я предлагал автору этот вариант.
По-моему мнению это запрос наиболее хорошо решает задачу в самой первой постановке и очень подходит для этой статьи, так как реализует именно особенность работы MySQL с GROUP BY в без агрегирующих функций, что в других СУБД может оказаться ошибочным.

warsoul 15 авг 2012 в 12:24

Нужно написать запрос, который бы вывел для каждого юзера дату и сумму последнего платежа.
UPD. Считаем, что юзер не может провести больше одного платежа за секунду. (Без этого условия постановка задачи некорректна). Тип pay_date изменен с DATE на DATETIME.

Возможно я открою вам глаза:

SELECT SELECT p.uid, p.amount
  FROM payments p
  GROUP BY p.uid
  ORDER BY p.pay_date DESC

Работает в разы быстрее чем джойны виртуальных таблиц, честно.

Shedal 15 авг 2012 в 12:32

… вот только результат возвращает совсем не тот, что нужен.

warsoul 15 авг 2012 в 12:36

Это вы проверили или просто ожидаемое поведение себе представили?
Ну и да, возможно это от версии зависит (работает верно как в 5.1 так и в 5.5).

warsoul 15 авг 2012 в 12:38

был не прав.

AlexeyVD 15 авг 2012 в 12:39

Такой запрос вернет amount не за последнюю дату, будьте внимательнее.

warsoul 15 авг 2012 в 12:46

угумс, обещаю в следующий раз не спешить тыкать кнопку «написать» :)
Правка ниже

warsoul 15 авг 2012 в 12:44

исправлюсь, SELECT * FROM (SELECT p.uid, p.amount FROM payments p ORDER BY p.pay_date DESC) t GROUP BY t.uid

kuber 15 авг 2012 в 12:47

Запрос представленный serjoga выше точно такой же.

warsoul 15 авг 2012 в 12:52

очень часто случается что мнения и методы двух разных людей совпадают :)

Зарегистрируйтесь на Хабре, чтобы оставить комментарий