对于mysql优化之IN换INNER JOIN的案例分享

要将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的实例分享的详细内容，更多请关注php中文网其它相关文章！