（相关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)
以上就是关于mysql优化之in换inner join的实例分享的详细内容。