mysql inner join 优化_关于mysql优化之IN换INNERJOIN的实例分享

今天撸代码时，遇到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 | | 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 | | 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 | | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |

+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+

5 rows in set (0.00 sec)