本篇文章给大家分享的是有关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 | <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优化中如何进行IN换INNERJOIN
标题链接:https://www.cdcxhl.com/article28/jcpocp.html
成都网站建设公司_创新互联,为您提供外贸建站、网站维护、微信公众号、网站设计公司、定制网站、响应式网站
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联