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

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

未优化前：

MySQL[xxuer]>SELECT->COUNT(*)->FROM->t_cmdb_app_version->WHERE->idIN(SELECT->pid->FROM->t_cmdb_app_relationUNIONSELECT->rp_id->FROM->t_cmdb_app_relation);+----------+|COUNT(*)|+----------+|266|+----------+1rowinset(0.21sec)

优化后：

MySQL[xxuer]>SELECT->count(*)->FROM->t_cmdb_app_versiona->INNERJOIN->(SELECT->pid->FROM->t_cmdb_app_relationUNIONSELECT->rp_id->FROM->t_cmdb_app_relation)bONa.id=b.pid;+----------+|count(*)|+----------+|266|+----------+1rowinset(0.00sec)

查看执行计划对比：

MySQL[xxuer]>explainSELECT->COUNT(*)->FROM->t_cmdb_app_version->WHERE->idIN(SELECT->pid->FROM->t_cmdb_app_relationUNIONSELECT->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|Usingwhere;Usingindex||2|DEPENDENTSUBQUERY|t_cmdb_app_relation|ALL|NULL|NULL|NULL|NULL|383|Usingwhere||3|DEPENDENTUNION|t_cmdb_app_relation|ALL|NULL|NULL|NULL|NULL|383|Usingwhere||NULL|UNIONRESULT|<union2,3>|ALL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+4rowsinset(0.00sec)

MySQL[xxuer]>explainSELECT->count(*)->FROM->t_cmdb_app_versiona->INNERJOIN->(SELECT->pid->FROM->t_cmdb_app_relationUNIONSELECT->rp_id->FROM->t_cmdb_app_relation)bONa.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|Usingwhere||1|PRIMARY|a|eq_ref|PRIMARY|PRIMARY|4|b.pid|1|Usingwhere;Usingindex||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|UNIONRESULT|<union2,3>|ALL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+5rowsinset(0.00sec)