В каком порядке оцениваются соединения MySQL?
У меня есть следующий запрос:
SELECT c.*
FROM companies AS c
JOIN users AS u USING(companyid)
JOIN jobs AS j USING(userid)
JOIN useraccounts AS us USING(userid)
WHERE j.jobid = 123;
У меня есть следующие вопросы:
- Является ли синтаксис USING синонимом синтаксиса ON?
- Эти объединения оцениваются слева направо? Другими словами, говорит ли этот запрос: x = companies JOIN users; y = x JOIN вакансии; z = y ПРИСОЕДИНЯЙТЕСЬ к учетным записям пользователя;
- Если ответ на вопрос 2 положительный, можно ли предположить, что в таблице компаний есть столбцы companyid, userid и jobid?
- Я не понимаю, как выражение WHERE может использоваться для выбора строк в таблице компаний, когда оно ссылается на псевдоним "j"
Любая помощь будет оценена!
7 ответов
USING (fieldname) - это сокращенный способ сказать ON table1.fieldname = table2.fieldname.
SQL не определяет "порядок", в котором выполняются JOINS, потому что это не характер языка. Очевидно, что в заявлении должен быть указан порядок, но INNER JOIN можно считать коммутативным: вы можете перечислить их в любом порядке, и вы получите те же результаты.
Тем не менее, при построении SELECT ... JOIN, особенно в том, который включает в себя LEFT JOIN, я обнаружил, что имеет смысл рассматривать третье JOIN как соединение новой таблицы с результатами первого JOIN, а четвертое JOIN - как присоединение к результаты второго JOIN и так далее.
В редких случаях указанный порядок может влиять на поведение оптимизатора запросов из-за того, как он влияет на эвристику.
Нет. При сборке запроса требуется, чтобы у компаний и пользователей был и идентификатор компании, и у заданий есть идентификатор пользователя, и идентификатор задания, а у учетной записи пользователя есть идентификатор пользователя. Однако только одна из компаний или пользователей нуждается в ИД пользователя для работы JOIN.
Предложение WHERE фильтрует весь результат - то есть все столбцы JOINed - используя столбец, предоставленный таблицей заданий.
Я не могу ответить немного о синтаксисе USING. Это странно. Я никогда раньше этого не видел, вместо этого всегда использовал предложение ON.
Но я могу вам сказать, что порядок операций JOIN определяется динамически оптимизатором запросов, когда он строит свой план запросов на основе системы эвристики оптимизации, некоторые из которых:
Осуществляется ли соединение с полем первичного ключа? Если это так, это получает высокий приоритет в плане запроса.
Выполняется ли JOIN в поле внешнего ключа? Это также получает высокий приоритет.
Существует ли индекс в объединенном поле? Если это так, увеличьте приоритет.
Выполняется ли операция JOIN над полем в предложении WHERE? Можно ли оценить выражение предложения WHERE, изучив индекс (а не выполнив сканирование таблицы)? Это главная возможность для оптимизации, поэтому она получает значительный приоритет.
Какова мощность объединенного столбца? Столбцы с большим количеством элементов дают оптимизатору больше возможностей для выявления ложных совпадений (тех, которые не удовлетворяют условию WHERE или предложению ON), поэтому объединения с большим количеством элементов обычно обрабатываются до объединений с низким числом элементов.
Сколько фактических строк в объединенной таблице? Объединение с таблицей, содержащей только 100 значений, приведет к меньшему взрыву данных, чем объединение с таблицей с десятью миллионами строк.
Во всяком случае... дело в том... есть много переменных, которые входят в план выполнения запроса. Если вы хотите увидеть, как MySQL оптимизирует свои запросы, используйте синтаксис EXPLAIN.
И вот хорошая статья для чтения:
http://www.informit.com/articles/article.aspx?p=377652
НА РЕДАКТИРОВАНИИ:
Чтобы ответить на ваш 4-й вопрос: вы не запрашиваете таблицу компаний. Вы запрашиваете объединенный перекрестный продукт ВСЕХ четырех таблиц в своих предложениях FROM и USING.
Псевдоним "j.jobid" - это просто полное имя одного из столбцов в этой объединенной коллекции таблиц.
В MySQL часто интересно спросить оптимизатора запросов, что он планирует делать, с помощью:
EXPLAIN SELECT [...]
Вот более подробный ответ на JOIN старшинство. В вашем случае JOINВсе они коммутативны. Давайте попробуем тот, где их нет.
Схема сборки:
CREATE TABLE users (
name text
);
CREATE TABLE orders (
order_id text,
user_name text
);
CREATE TABLE shipments (
order_id text,
fulfiller text
);
Добавить данные:
INSERT INTO users VALUES ('Bob'), ('Mary');
INSERT INTO orders VALUES ('order1', 'Bob');
INSERT INTO shipments VALUES ('order1', 'Fulfilling Mary');
Запустить запрос:
SELECT *
FROM users
LEFT OUTER JOIN orders
ON orders.user_name = users.name
JOIN shipments
ON shipments.order_id = orders.order_id
Результат:
Возвращается только строка Боба
Анализ:
В этом запросе LEFT OUTER JOIN был оценен первым и JOIN был оценен по составному результату LEFT OUTER JOIN,
Второй запрос:
SELECT *
FROM users
LEFT OUTER JOIN (
orders
JOIN shipments
ON shipments.order_id = orders.order_id)
ON orders.user_name = users.name
Результат:
Одна строка для Боба (с данными выполнения) и одна строка для Мэри с NULL для данных выполнения.
Анализ:
Скобка изменила порядок оценки.
Дополнительная документация по MySQL находится по адресу https://dev.mysql.com/doc/refman/5.5/en/nested-join-optimization.html
Я не уверен в части ON против USING (хотя этот сайт говорит, что они одинаковы)
Что касается вопроса заказа, то он полностью зависит от реализации (и, возможно, запроса). MYSQL, скорее всего, выбирает ордер при компиляции запроса. Если вы хотите навязать конкретный порядок, вам нужно "вложить" ваши запросы:
SELECT c.*
FROM companies AS c
JOIN (SELECT * FROM users AS u
JOIN (SELECT * FROM jobs AS j USING(userid)
JOIN useraccounts AS us USING(userid)
WHERE j.jobid = 123)
)
как для части 4: предложение where ограничивает, какие строки из таблицы заданий могут быть присоединены. Таким образом, если есть строки, которые будут объединены из-за совпадающих идентификаторов пользователей, но не имеют правильного идентификатора задания, они будут опущены
1) Использование не совсем то же самое, что и on, но это сокращение, когда в обеих таблицах есть столбец с тем же именем, к которому вы присоединяетесь... см.: http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/ThekeywordUSINGcanbeusedasareplacementfortheONkeywordduringthetableJoins.htm
По моему мнению, это сложнее читать, поэтому я хотел бы изложить соединения.
3) Это не ясно из этого запроса, но я думаю, что это не так.
2) Предполагая, что вы объединяете другие таблицы (не все напрямую в компаниях), порядок в этом запросе имеет значение... см. Сравнения ниже:
оригинальное:
SELECT c.*
FROM companies AS c
JOIN users AS u USING(companyid)
JOIN jobs AS j USING(userid)
JOIN useraccounts AS us USING(userid)
WHERE j.jobid = 123
То, что я думаю, это, вероятно, предполагает:
SELECT c.*
FROM companies AS c
JOIN users AS u on u.companyid = c.companyid
JOIN jobs AS j on j.userid = u.userid
JOIN useraccounts AS us on us.userid = u.userid
WHERE j.jobid = 123
Вы можете переключать свои линии, соединяющие вакансии и учетные записи пользователей здесь.
Как бы это выглядело, если бы все объединилось в компанию:
SELECT c.*
FROM companies AS c
JOIN users AS u on u.companyid = c.companyid
JOIN jobs AS j on j.userid = c.userid
JOIN useraccounts AS us on us.userid = c.userid
WHERE j.jobid = 123
Это не имеет логического смысла... если у каждого пользователя нет своей компании.
4.) Магия sql в том, что вы можете показывать только определенные столбцы, но все они предназначены для сортировки и фильтрации...
если ты вернулся
SELECT c.*, j.jobid....
вы могли ясно видеть, по чему он фильтрует, но серверу баз данных не важно, выводите ли вы строку или нет для фильтрации.
СМ. http://dev.mysql.com/doc/refman/5.0/en/join.html
И начать читать здесь:
Присоединяйтесь к обработке изменений в MySQL 5.0.12
Начиная с MySQL 5.0.12, естественные объединения и объединения с использованием USING, включая варианты внешнего объединения, обрабатываются в соответствии со стандартом SQL:2003. Цель состояла в том, чтобы согласовать синтаксис и семантику MySQL относительно NATURAL JOIN и JOIN ... USING в соответствии с SQL:2003. Однако эти изменения в обработке объединения могут привести к различным выходным столбцам для некоторых объединений. Кроме того, некоторые запросы, которые, казалось, работали правильно в более старых версиях, должны быть переписаны, чтобы соответствовать стандарту.
Эти изменения имеют пять основных аспектов:
Способ, которым MySQL определяет результирующие столбцы операций соединения NATURAL или USING (и, следовательно, результат всего предложения FROM).
Расширение SELECT * и SELECT tbl_name.* В список выбранных столбцов.
Разрешение имен столбцов в соединениях NATURAL или USING.
Преобразование NATURAL или USING включается в JOIN ... ON.
Разрешение имен столбцов в состоянии ON для JOIN ... ON.