匿名通过本文主要向大家介绍了INNER,mysql,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 | <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的实例分享的详细内容，更多请关注微课江湖其它相关文章！
您可能想查找下面的文章:
- 超详细mysql left join,right join,inner join用法分析
- 关于mysql优化之IN换INNER JOIN的实例分享
- 用 INNER JOIN语法联接多个表建记录集
- union这个连接是有什么用的和INNER JOIN有什么区别
- 数据库Left join , Right Join, Inner Join 的相关内容，非常实
- SQL 外链接操作小结 inner join left join right join
- MSSQL内外连接(INNER JOIN)语句详解
- 探究MySQL优化器对索引和JOIN顺序的选择
- 在MySQL中使用JOIN语句进行连接操作的详细教程
- MySQL中视图的使用及多表INNER JOIN的技巧分享