0
<< предыдущая заметкаследующая заметка >>
11 апреля 2011
MySQL - вопрос

Господа и особенно милые дамы!

Задача: две таблицы, надо выбрать из таблицы 1 те значения с кодом num, для которых НЕ СУЩЕСТВУЕТ записи в таблице 2 с этим num=num и val=123. В первой таблице уникальный ключ num, во второй — (num,val).

Сейчас эта задача решена так:

SELECT `num` FROM `tab1` WHERE `num` NOT IN (SELECT `num` FROM `tab2` WHERE `val`='123')

Но это неоптимально, учитывая дикий размер таблицы tab2.

Можно это как-то красивее решить?

<< предыдущая заметка следующая заметка >>
пожаловаться на эту публикацию администрации портала
архив понравившихся мне ссылок
Оставить комментарий
Windows Opera
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
а есть ли у прекрасного mysql конструкция WHERE NOT EXISTS?

ну типа

select num from tab1 where not exists (select num from tab2 where val = '123' and tab2.num = tab1.num)

по идее, если tab1 невелика по сравнению с tab2, то по перой имеем full index scan по второй seek.

а вообще оптимизатор может выбирать разные стратегии выполнения запроса в зависимости от статистики, надо изучать планы выполнения.
Windows Firefox
0
0
10p
Здесь пишется, что в MySQL NOT EXISTS менее оптимальный, чем NOT IN или LEFT JOIN / IS NULL: http://explainextended.com/2009/09/18/not-in-vs-not-exists-v[...]
Linux Firefox
 Москва
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Предположу, что NOT IN один раз взял сей результат, закешировал, и далее работает с ним. А NOT EXISTS производит выборку для каждого элемента. Я прав?
Windows Firefox
0
0
10p
Причина в статье не объясняется ("It’s hard to tell exact reason for this... it is possible that the code responsible for EXISTS makes some kind of an extra check which takes extra time."), но опытным путем установлено, что именно в MySQL NOT EXISTS где-то на 30% менее эффективен. В этом блоге так же проверяется скорость подобных конструкций в других СУБД. Результаты, естественно, другие.
Windows Firefox
3
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
В таблице tab2 имеет смысл поменять порядок полей в индексе: сделать (val, num). Значения с одинаковым val будут находиться близко друг к другу в индексе (т. е. в одних и тех же страницах), и это увеличит эффективность кэша.

NOT IN и NOT EXISTS, в данном случае, скорее всего, будут работать приблизительно одинаково. Если таблица большая, то скорость поиска по индексу будет определяющим фактором.

Обратите внимание: NOT IN и NOT EXISTS — это логически разные предикаты, они по-разному обрабатывают NULL.

1 NOT IN (SELECT NULL) вернёт NULL

NOT EXISTS (SELECT NULL WHERE NULL = 1) вернёт TRUE

Если в tab2 поле num может принимать значение NULL, то обязательно надо переписать запрос с использованием NOT EXISTS:

SELECT num FROM tab1 WHERE NOT EXISTS (SELECT NULL FROM tab2 WHERE tab2.val = '123' AND tab2.num = tab1.num)
Linux Firefox
 Москва
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Спасибо! Так и есть: PRIMARY KEY (`val`,`num`)
Windows Firefox
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Забыл спросить: поля все INTEGER, строк нет?

Не могли бы вы выложить результаты запросов:

SHOW CREATE TABLE tab1
SHOW CREATE TABLE tab2
EXPLAIN EXTENDED SELECT `num` FROM `tab1` WHERE `num` NOT IN (SELECT `num` FROM `tab2` WHERE `val`='123')
Windows Safari Chrome
 Австралия
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Чтобы не изобретать велосипед, можно я вам лучше ссылку дам? http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTI[...]
Ответ на ваш вопрос там дан в контексте Oracle, как сами видите, но поверьте - эти структуры, как минимум у основных баз, - они как близнецы-братья. К тому же Oracle теперь владеет всем Sun-ом, включая MySQL, со всеми потрохами...
Доверять заявлениям что EXISTS вариант "менее оптимальный" в MySQL я бы на стала просто так - это надо всё самим на практике проверить, в своём контексте.
Windows Firefox
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Оптимизаторы у Oracle и MySQL очень разные. MySQL, например, не умеет делать MERGE JOIN и HASH JOIN, только NESTED LOOPS.
Linux Firefox
 Москва
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Ольга, спасибо, я надеялся, что именно вы что-то напишете :)
Жаль, я по английски не умею...

Но суть я понял - можно оставить пока, как есть. Верно?
Windows Safari Chrome
 Австралия
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Ради интереса установила себе "5.5.9 MySQL Community Server" (под Windows, - другой ОС под рукой нет). После работы состряпаю test case по вашим данным, - тогда смогу ответить по сути. (за английские ссылки прошу пардона, - с чем работаю о том и треплюсь, как вшивый про баню...)
Windows Safari Chrome
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Как я его не кручу, не вижу существенной разницы: NOT IN и NOT EXISTS себя ведут примерно одинаково, вне зависимости от доп индексов. Так что это теперь больше вопрос к специалистам по MySQL оптимизатору.
PS
Оптимизатор и правда своеобразный, и есть смутное предчувствие что он будет существенно меняться от релиза к релизу, - надо надеяться что не в худшую сторону..
PPS
В объяснении по той ссылке говорится что оптимальный вариант (в контексте Oracle) у вас был бы при индексе на tab2.val с использованием "not exists", т.е. как-то так:
select t1.num from tab1 t1 where not exists (select null from tab2 t2 where t2.val = '123' and t1.num = t2.num);
Windows Firefox
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
В Oracle в данном случае разницы между конструкциями не будет: если поля NOT NULL (а они NOT NULL, так как в первичном ключе), то будут одинаковые планы (FFS по tab1, INDEX SCAN по tab2 и, скорее всего, HASH ANTI JOIN между ними) и одинаковые результаты.

MySQL, как уже было сказано, не умеет делать ни HASH, ни MERGE, поэтому он будет делать поиск по индексу по tab2 в цикле, каждый раз начиная с корневой страницы. Это, прямо скажем, не оптимально, но сделать с этим ничего нельзя.

Реализация алгоритмов HASH и MERGE в MySQL, к сожалению, в планах пока не стоит.
Windows Safari Chrome
 Австралия
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Насчёт MERGE - это вы про 11g, правда? - на своей шкуре испытано, в 11g это зависит от настроек оптимизатора. А до 11g всё было по-другому... Мы тут на этом MERGE (вкупе со статистикой) собаку съели. Если у вас есть доступ к чужим SR, то посмотрите вот на это хозяйство: "SR 3-2215009711: RC+ ::Incorrect query execution path causing ORA-01652". Там у коллеги с Oracle была просто феерическая перестрелка по поводу MERGE.. почитайте, не пожалеете..
Nokia-E90 Safari
 Бельгия
0
0
Леонит Каганофф
Спасибо, -Ольга. Я в Брюсселе, вернусь - продолжу эксперименты на основании услышанного.
Windows Safari Chrome
 Австралия
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Пожалуйста :). К сожалению, мои мозги тут исчерпаны. Вот коллега Quassnoi лучше меня знает откуда у MySql ноги растут.. (снимаю шляпу).
Linux Safari Chrome
1
0
Кир
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
SELECT `num` FROM `tab1` join tab2 on tab1.num=tab2.num WHERE tab1.`num` is null and tab1.`val`='123'
примерно так
Nokia-E90 Safari
 Москва
0
0
Леонит Каганофф
Вот это я пробовал - оно не работает.
Windows Firefox
2
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Надо использовать LEFT JOIN и поместить фильтр по val в конструкцию ON:

SELECT `num` FROM `tab1` LEFT JOIN tab2 on tab2.num=tab1.num AND tab2.`val`='123' WHERE tab2.num IS NULL
Linux Firefox
 Москва
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Это не работает, увы. Всегда выдает пустое множество.
Windows Firefox
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Это точный эквивалент запроса с NOT EXISTS. Проверьте, везде ли стоят правильные алиасы.
Linux Safari Chrome
0
0
Кир
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
В самом деле, mysql так борется за оптимизацию что даже не рассматривает условие is null для полей not null, но, всегда есть но. Такая оптимизация работает только для первого условия, так что имеет смыл попробовать вот такой вариант:
SELECT `num` FROM `tab1` left join tab2 on tab1.num=tab2.num WHERE (tab1.`num` is null or tab1.`num` is null) and tab1.`val`='123
Windows Firefox
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Если вы ставите условие tab2.val = 123 в конструкцию WHERE, оно срабатывает после LEFT JOIN. Это условие надо ставить в конструкцию ON.
Linux Safari Chrome
0
0
Кир
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Совершенно верно, но по исходным условиям (для меня во всяком случае) не очевидно к которой таблице относится поле val
Windows Opera
0
0
808
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
если все же использовать NOT IN, то не помешал бы индекс на tab2 по (val), или даже (val, num)
Linux Firefox
 Москва
0
0
LLeo
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Да, так и есть (val, num)
Без индекса там нельзя - база гигантская.
Windows Firefox
0
0
Anton
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Всегда использовал этот способ:
SELECT t1.num
FROM tab1 t1
LEFT JOIN tab2 t2 ON t1.num=t2.num AND t2.val='123'
WHERE t2.num IS NULL

Кстати, советую всегда писать алиасы таблиц, даже когда в них особой необходимости нет.
Linux Firefox
0
0
{name}
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Извините, Леонид, за оффтопик. Страница символического направления почему-то перестала переводить.
Как раз хотел воспользоваться греческим вариантом )
Nokia-E90 Safari
0
0
lleo Nokia E90
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Как это - "перестало переводить"? Что именно случилось?
Linux Opera
0
0
avialaynen
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Ллео, почему поиск не работает?


всего комментариев: 30

<< предыдущая заметка следующая заметка >>