mysql中in和left join哪个更好

发布网友发布时间：2022-04-13 02:03

共3个回答

懂视网时间：2022-04-13 06:24

今天撸代码时，遇到SQL问题：

（相关mysql视频教程推荐：《mysql教程》）

要将A表查询的ID，匹配B表的ID，并将B表全部内容查询出来：

未优化前：

MySQL [xxuer]> SELECT 
 -> COUNT(*)
 -> FROM
 -> t_cmdb_app_version
 -> WHERE
 -> id IN (SELECT 
 ->  pid
 ->  FROM
 ->  t_cmdb_app_relation UNION SELECT 
 ->  rp_id
 ->  FROM
 ->  t_cmdb_app_relation);
+----------+
| COUNT(*) |
+----------+
| 266 |
+----------+
1 row in set (0.21 sec)

优化后：

MySQL [xxuer]> SELECT 
 -> count(*)
 -> FROM
 -> t_cmdb_app_version a
 ->  INNER JOIN
 -> (SELECT 
 ->  pid
 -> FROM
 ->  t_cmdb_app_relation UNION SELECT 
 ->  rp_id
 -> FROM
 ->  t_cmdb_app_relation) b ON a.id = b.pid;
+----------+
| count(*) |
+----------+
| 266 |
+----------+
1 row in set (0.00 sec)

查看执行计划对比：

MySQL [xxuer]> explain SELECT 
 -> COUNT(*)
 -> FROM
 -> t_cmdb_app_version
 -> WHERE
 -> id IN (SELECT 
 ->  pid
 ->  FROM
 ->  t_cmdb_app_relation UNION SELECT 
 ->  rp_id
 ->  FROM
 ->  t_cmdb_app_relation);
+----+--------------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra   |
+----+--------------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY  | t_cmdb_app_version | index | NULL  | PRIMARY | 4 | NULL | 659 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t_cmdb_app_relation | ALL | NULL  | NULL | NULL | NULL | 383 | Using where  |
| 3 | DEPENDENT UNION | t_cmdb_app_relation | ALL | NULL  | NULL | NULL | NULL | 383 | Using where  |
| NULL | UNION RESULT | <union2,3>  | ALL | NULL  | NULL | NULL | NULL | NULL | Using temporary  |
+----+--------------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
4 rows in set (0.00 sec)

MySQL [xxuer]> explain SELECT 
 -> count(*)
 -> FROM
 -> t_cmdb_app_version a
 ->  INNER JOIN
 -> (SELECT 
 ->  pid
 -> FROM
 ->  t_cmdb_app_relation UNION SELECT 
 ->  rp_id
 -> FROM
 ->  t_cmdb_app_relation) b ON a.id = b.pid;
+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra   |
+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+
| 1 | PRIMARY | <derived2>  | ALL | NULL  | NULL | NULL | NULL | 766 | Using where  |
| 1 | PRIMARY | a   | eq_ref | PRIMARY | PRIMARY | 4 | b.pid | 1 | Using where; Using index |
| 2 | DERIVED | t_cmdb_app_relation | ALL | NULL  | NULL | NULL | NULL | 383 | NULL   |
| 3 | UNION | t_cmdb_app_relation | ALL | NULL  | NULL | NULL | NULL | 383 | NULL   |
| NULL | UNION RESULT | <union2,3>  | ALL | NULL  | NULL | NULL | NULL | NULL | Using temporary  |
+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+
5 rows in set (0.00 sec)

热心网友时间：2022-04-13 03:32

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

热心网友时间：2022-04-13 04:50

join 好，in 没法用到索引