left join和in哪种查询效率要好

[小白晒太阳]

最近写sql，各种left join，一位大哥说left join用多了，在数据量很大的情况下是非常不好的。我一直不懂具体的区别在哪，用explain查看sql也看不太明白，求各位大哥大姐指点指点，帮忙分析分析。

[rucypli]

mysql5.6以下的版本禁止用in（select ）语句 效率极差

[ACMAIN_CHM]

在你的LEFTＪＯＩnＮ　语句相关的表上创建索引。 至于 left join 用多了效率差。这个首先所谓效率差是要有对应比较的，只能说实现相同的功能下两种或多种语句之间的效率优劣。 仅针对一种LEFT JOIN则无从评论，也就不存在所谓的LEFT JOIN效率差的说法。 网上的，大牛的很多说法要看上下文，不能断章取义，另外也不能轻信。

[小白晒太阳]

好像嵌套查询那个该这么写比较好。。

[ACMAIN_CHM]

引用 5 楼 Tro_picana 的回复:

关于left join查询的优化除了在字段上建立索引之外还有哪些优化方式，主表对应的查询记录数越小对应整个查询是否能起到优化效果。上面那个图中sc表 rows 451表示的是啥意思，sll 表的1表示的又是什么意思。求大哥指点一二。。

关于EXPLAIN结果的解释，在MYSQL的官方免费手册中有详细的介绍和例子。建议先自己阅读一下。 如果看不懂英语，也不要用它做借口，可以一利用晨线翻译，二可以阅读MYSQL官方免费的中文5.1版本帮助

引用

Each output row from EXPLAIN provides information about one table, and each row contains the following columns: id The SELECT identifier. This is the sequential number of the SELECT within the query. select_type The type of SELECT, which can be any of those shown in the following table. SIMPLE Simple SELECT (not using UNION or subqueries) PRIMARY Outermost SELECT UNION Second or later SELECT statement in a UNION DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query UNION RESULT Result of a UNION. SUBQUERY First SELECT in subquery DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query DERIVED Derived table SELECT (subquery in FROM clause) UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) DEPENDENT typically signifies the use of a correlated subquery. See Section 12.2.9.7, “Correlated Subqueries”. “DEPENDENT SUBQUERY” evaluation differs from UNCACHEABLE SUBQUERY evaluation. For “DEPENDENT SUBQUERY”, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. For UNCACHEABLE SUBQUERY, the subquery is re-evaluated for each row of the outer context. Cacheability of subqueries is subject to the restrictions detailed in Section 7.5.5.1, “How the Query Cache Operates”. For example, referring to user variables makes a subquery uncacheable. table The table to which the row of output refers. type The join type. The different join types are listed here, ordered from the best type to the worst: system The table has only one row (= system table). This is a special case of the const join type. const The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once. const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as a const table: SELECT * FROM tbl_name WHERE primary_key=1; SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2; eq_ref One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE index. eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an eq_ref join to process ref_table: SELECT * FROM ref_table,other_table

.

[小白晒太阳]

引用 2 楼 rucypli 的回复:

mysql5.6以下的版本禁止用in（select ）语句 效率极差

那不用in的嵌套查询该如何写。。。

[小白晒太阳]

引用 3 楼 ACMAIN_CHM 的回复:

在你的LEFTＪＯＩnＮ　语句相关的表上创建索引。 至于 left join 用多了效率差。这个首先所谓效率差是要有对应比较的，只能说实现相同的功能下两种或多种语句之间的效率优劣。 仅针对一种LEFT JOIN则无从评论，也就不存在所谓的LEFT JOIN效率差的说法。 网上的，大牛的很多说法要看上下文，不能断章取义，另外也不能轻信。

关于left join查询的优化除了在字段上建立索引之外还有哪些优化方式，主表对应的查询记录数越小对应整个查询是否能起到优化效果。上面那个图中sc表 rows 451表示的是啥意思，sll 表的1表示的又是什么意思。求大哥指点一二。。