Леонид Каганов: дневник

Господа и особенно милые дамы!
Задача: две таблицы, надо выбрать из таблицы 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.
Можно это как-то красивее решить?
тэги записи:
mysql
<< предыдущая заметка
следующая заметка >>
пожаловаться на эту публикацию администрации портала
архив понравившихся мне ссылок
Оставить комментарий
2011-04-11 00:35
Windows Opera
whois
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.

а вообще оптимизатор может выбирать разные стратегии выполнения запроса в зависимости от статистики, надо изучать планы выполнения.
2011-04-11 01:19
Windows Firefox
whois
0
0
 10p
Здесь пишется, что в MySQL NOT EXISTS менее оптимальный, чем NOT IN или LEFT JOIN / IS NULL: http://explainextended.com/2009/09/18/not-in-vs-not-exists-v[...]
2011-04-11 01:44
Linux Firefox
 Москва
0
0
 LLeo
Предположу, что NOT IN один раз взял сей результат, закешировал, и далее работает с ним. А NOT EXISTS производит выборку для каждого элемента. Я прав?
2011-04-11 03:34
Windows Firefox
whois
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% менее эффективен. В этом блоге так же проверяется скорость подобных конструкций в других СУБД. Результаты, естественно, другие.
2011-04-11 14:17
Windows Firefox
whois
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)
2011-04-11 14:54
Linux Firefox
 Москва
0
0
 LLeo
Спасибо! Так и есть: PRIMARY KEY (`val`,`num`)
2011-04-11 16:03
Windows Firefox
whois
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')
2011-04-11 06:29
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 я бы на стала просто так - это надо всё самим на практике проверить, в своём контексте.
2011-04-11 14:26
Windows Firefox
whois
0
0
 {name}
Оптимизаторы у Oracle и MySQL очень разные. MySQL, например, не умеет делать MERGE JOIN и HASH JOIN, только NESTED LOOPS.
2011-04-11 14:58
Linux Firefox
 Москва
0
0
 LLeo
Ольга, спасибо, я надеялся, что именно вы что-то напишете :)
Жаль, я по английски не умею...

Но суть я понял - можно оставить пока, как есть. Верно?
2011-04-12 05:56
Windows Safari Chrome
 Австралия
0
0
 {name}
Ради интереса установила себе "5.5.9 MySQL Community Server" (под Windows, - другой ОС под рукой нет). После работы состряпаю test case по вашим данным, - тогда смогу ответить по сути. (за английские ссылки прошу пардона, - с чем работаю о том и треплюсь, как вшивый про баню...)
2011-04-12 19:54
Windows Safari Chrome
whois
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);
2011-04-12 20:24
Windows Firefox
whois
0
0
 {name}
В Oracle в данном случае разницы между конструкциями не будет: если поля NOT NULL (а они NOT NULL, так как в первичном ключе), то будут одинаковые планы (FFS по tab1, INDEX SCAN по tab2 и, скорее всего, HASH ANTI JOIN между ними) и одинаковые результаты.

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

Реализация алгоритмов HASH и MERGE в MySQL, к сожалению, в планах пока не стоит.
2011-04-14 06:22
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.. почитайте, не пожалеете..
2011-04-14 02:06
Nokia-E90 Safari
 Бельгия
0
0
 Леонит Каганофф
Спасибо, -Ольга. Я в Брюсселе, вернусь - продолжу эксперименты на основании услышанного.
2011-04-14 06:28
Windows Safari Chrome
 Австралия
0
0
 {name}
Пожалуйста :). К сожалению, мои мозги тут исчерпаны. Вот коллега Quassnoi лучше меня знает откуда у MySql ноги растут.. (снимаю шляпу).
2011-04-11 08:58
Linux Safari Chrome
whois
1
0
 Кир
SELECT `num` FROM `tab1` join tab2 on tab1.num=tab2.num WHERE tab1.`num` is null and tab1.`val`='123'
примерно так
2011-04-11 13:12
Nokia-E90 Safari
 Москва
0
0
 Леонит Каганофф
Вот это я пробовал - оно не работает.
2011-04-11 14:23
Windows Firefox
whois
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
2011-04-11 14:51
Linux Firefox
 Москва
0
0
 LLeo
Это не работает, увы. Всегда выдает пустое множество.
2011-04-11 15:57
Windows Firefox
whois
0
0
 {name}
Это точный эквивалент запроса с NOT EXISTS. Проверьте, везде ли стоят правильные алиасы.
2011-04-11 17:56
Linux Safari Chrome
whois
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
2011-04-11 22:23
Windows Firefox
whois
0
0
 {name}
Если вы ставите условие tab2.val = 123 в конструкцию WHERE, оно срабатывает после LEFT JOIN. Это условие надо ставить в конструкцию ON.
2011-04-11 23:02
Linux Safari Chrome
whois
0
0
 Кир
Совершенно верно, но по исходным условиям (для меня во всяком случае) не очевидно к которой таблице относится поле val
2011-04-11 12:19
Windows Opera
whois
0
0
 808
если все же использовать NOT IN, то не помешал бы индекс на tab2 по (val), или даже (val, num)
2011-04-11 14:58
Linux Firefox
 Москва
0
0
 LLeo
Да, так и есть (val, num)
Без индекса там нельзя - база гигантская.
2011-04-13 06:54
Windows Firefox
whois
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

Кстати, советую всегда писать алиасы таблиц, даже когда в них особой необходимости нет.
2011-04-20 18:35
Linux Firefox
whois
0
0
 {name}
Извините, Леонид, за оффтопик. Страница символического направления почему-то перестала переводить.
Как раз хотел воспользоваться греческим вариантом )
2011-04-20 21:02
Nokia-E90 Safari
whois
0
0
 lleo Nokia E90
Как это - "перестало переводить"? Что именно случилось?
2011-05-07 11:16
Linux Opera
whois
0
0
 avialaynen
Ллео, почему поиск не работает?


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

<< предыдущая заметка
следующая заметка >>
	апрель 2011
ПН	ВТ	СР	ЧТ	ПТ	СБ	ВС
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30