本篇文章给大家分享的是有关MySQL 优化中如何进行IN换INNER JOIN，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
网站设计制作过程拒绝使用模板建站；使用PHP+MYSQL原生开发可交付网站源代码;符合网站优化排名的后台管理系统；网站设计制作、网站建设收费合理；免费进行网站备案等企业网站建设一条龙服务.我们是一家持续稳定运营了10年的创新互联网站建设公司。
今天撸代码时，遇到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，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注创新互联行业资讯频道。
本文标题：mysql优化中如何进行IN换INNERJOIN
当前路径：
http://bjjierui.cn/article/jcpocp.html
