多表选择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: