（相关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的实例分享的知识。速戳>>知识兔学习精品课！