Mysql выберите, где не в таблице
У меня есть 2 таблицы (A и B) с одинаковыми первичными ключами. Я хочу выбрать всю строку, которая находится в A, а не в B. Работает следующее:
select * from A where not exists (select * from B where A.pk=B.pk);
однако это кажется довольно плохим (~ 2 секунды только на 100 тыс. строк в A и на 3-10 тыс. меньше в B)
Есть ли лучший способ запустить это? Возможно, как левое соединение?
select * from A left join B on A.x=B.y where B.y is null;
По моим данным, это работает немного быстрее (~ 10%), но как насчет в целом?
Ответы 5
Я думаю, что ваше последнее заявление - лучший способ. Вы также можете попробовать
SELECT A.*
from A left join B on
A.x = B.y
where B.y is null
В этом нет никакого смысла. Когда B.y равно нулю, A.x = B.y никогда не будет истинным. Вы получите все строки A, а не только те, у которых нет соответствующей строки в B.
@Bill и все же работает! Кроме того, это то же самое, что и второе утверждение, указанное выше.
@hamstar, да, теперь правильно, поменяв and на where.
@Smok "Я думаю, что ваше последнее утверждение - лучший способ. Вы также можете попробовать ..." ~ Разве ваше не идентично?
@BillKarwin В этом есть большой смысл. Левое соединение включает каждую запись A хотя бы один раз - один раз для каждого соответствия B или одну с NULL в столбцах B, если нет соответствия B. Из w3schools.com/sql/sql_join_left.asp: «Ключевое слово LEFT JOIN возвращает все строки из левой таблицы (table1) , с совпадающими строками в правой таблице (table2). Результатом является NULL в правой части, когда совпадений нет ". A, у которых есть математическое вычисление B, будет иметь ненулевое B.y для каждого вхождения, в то время как A без B будет иметь одно вхождение с нулевым B.y
@IsaacBetesh, спасибо, но вы должны знать, что я разместил свой первый комментарий выше перед тем, как hamstar редактировал, чтобы исправить логику запроса. Проверьте историю изменений, чтобы увидеть исходный ответ, который был неправильным.
Я использую запросы в формате вашего второго примера. Соединение обычно более масштабируемо, чем коррелированный подзапрос.
Я также использую левые соединения с критерием типа «где table2.id - null».
Определенно кажется более эффективным, чем вариант вложенного запроса.
Соединения обычно выполняются быстрее (в MySQL), но вам также следует рассмотреть свою схему индексации, если вы обнаружите, что она все еще движется медленно. Как правило, для любого поля, настроенного как внешний ключ (с использованием INNODB), уже будет установлен индекс. Если вы используете MYISAM, убедитесь, что все столбцы в операторе ON проиндексированы, а также рассмотрите возможность добавления любых столбцов в предложении WHERE в конец индекса, чтобы сделать его покрывающим индексом. Это позволяет механизму иметь доступ ко всем данным, необходимым в индексе, устраняя необходимость повторного возврата к исходным данным. Имейте в виду, что это повлияет на скорость вставки / обновления / удаления, но может значительно увеличить скорость запроса.
На самом деле, Рауль, ваша правка звучит так, будто вы должны создать отдельный индекс для столбцов в предложении WHERE. Для ясности я хотел добавить столбцы в индекс (индекс покрытия), чтобы движку не пришлось возвращаться к базовым данным для предложения WHERE. Приносим извинения за непонятность.
Это мне очень помогло. Joins всегда быстрее, чем подзапросы, чтобы давать результаты:
SELECT tbl1.id FROM tbl1 t1
LEFT OUTER JOIN tbl2 t2 ON t1.id = t2.id
WHERE t1.id>=100 AND t2.id IS NULL ;
Вы имеете в виду, что у вас 100 тыс. Строк в таблице A и 100 тыс. Строк в таблице B? Или всего около 300 строк в каждой таблице, что означает сканирование 100 000 строк (или, надеюсь, сканирование индекса).