mysql 优化中如何进行IN换INNER JOIN

本篇文章给大家分享的是有关mysql 优化中如何进行IN换INNER JOIN，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

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

要将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)

以上就是mysql 优化中如何进行IN换INNER JOIN，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。