多表选择vs. JOIN(性能)
从MySQL的多个表中进行选择时,以下两个查询均返回相同的结果集.
When selecting from multiple tables in MySQL, both of the following queries return the same result set.
这些查询中的一个是更好还是比另一个更有效?根据我在一个小型数据集(每个表中约有2000行)的测试中,它们都在大约相同的执行时间上返回了相同的结果集.
Is one of these queries better or more efficient than the other? From my testing on a small dataset (~2k rows in each table) they both return the same result set in around the same execution time.
查询1:
SELECT
*
FROM
products,
product_meta,
sales_rights
WHERE
(
products.id = product_meta.product_id
AND products.id = sales_rights.product_id
)
AND (...)
LIMIT 0,10;
查询2:
SELECT
*
FROM
products
INNER JOIN product_meta ON products.id = product_meta.product_id
JOIN sales_rights ON product_meta.product_id = sales_rights.product_id
WHERE
(...)
LIMIT 0,10;
它们相同,但是语法不同.因此,您不应该期望这两种语法之间的性能差异.但是,建议使用最后一种语法(ANS SQL-92语法),有关更多详细信息,请参见以下内容:
They are the same, but with a different syntax. So you shouldn't expect any performance difference between the two syntaxes. However the the last syntax(ANS SQL-92 syntax) is the recommended, see these for more details: