mysql - 使用 mysql 连接表最快的方法是什么

Question

我有两个表“User”和“UsersSettings”。

我想选择用户的用户设置是什么什么查询会更快？

SELECT u.*, us.someSettings FROM User u,UserSettings us 
WHERE u.id = us.user_id 
AND us.somesettings = somevalue 
AND u.someProperty = someOtherValue
AND u.someProperty1 = someOtherValue1
AND u.someProperty2 = someOtherValue2
AND u.someProperty3 = someOtherValue3
AND u.someProperty4 = someOtherValue4
AND us.someUSProperty  = someUSvalue

或者

SELECT u.*, us.someSettings FROM User u
LEFT JOIN UserSettings us ON us.user_id = u.id
WHERE us.somesettings = somevalue 
AND u.someProperty = someOtherValue
AND u.someProperty1 = someOtherValue1
AND u.someProperty2 = someOtherValue2
AND u.someProperty3 = someOtherValue3
AND u.someProperty4 = someOtherValue4
AND us.someUSProperty  = someUSvalue

你能帮我一下吗谢谢

Answer 1

正确的查询是内部联接:

SELECT u.*, us.someSettings
FROM User u INNER JOIN
     UserSettings us
     ON us.user_id = u.id
WHERE us.somesettings = somevalue 
AND u.someProperty = someOtherValue
AND u.someProperty1 = someOtherValue1
AND u.someProperty2 = someOtherValue2
AND u.someProperty3 = someOtherValue3
AND u.someProperty4 = someOtherValue4
AND us.someUSProperty  = someUSvalue;

您在 UserSetting 上有一个过滤条件，因此外部联接无论如何都会变成内部联接。您应该避免隐式 join 语法，并将连接条件显式放在 on 子句中。这是为了可读性和可维护性。两个版本应该具有相同的性能。