mysql 优化中如何进行IN换INNER JOIN
本篇文章给大家分享的是有关mysql 优化中如何进行IN换INNER JOIN,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。
今天撸代码时,遇到SQL问题:
要将A表查询的ID,匹配B表的ID,并将B表全部内容查询出来:
未优化前:
MySQL[xxuer]>SELECT->COUNT(*)->FROM->t_cmdb_app_version->WHERE->idIN(SELECT->pid->FROM->t_cmdb_app_relationUNIONSELECT->rp_id->FROM->t_cmdb_app_relation);+----------+|COUNT(*)|+----------+|266|+----------+1rowinset(0.21sec)
优化后:
MySQL[xxuer]>SELECT->count(*)->FROM->t_cmdb_app_versiona->INNERJOIN->(SELECT->pid->FROM->t_cmdb_app_relationUNIONSELECT->rp_id->FROM->t_cmdb_app_relation)bONa.id=b.pid;+----------+|count(*)|+----------+|266|+----------+1rowinset(0.00sec)
查看执行计划对比:
MySQL[xxuer]>explainSELECT->COUNT(*)->FROM->t_cmdb_app_version->WHERE->idIN(SELECT->pid->FROM->t_cmdb_app_relationUNIONSELECT->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|Usingwhere;Usingindex||2|DEPENDENTSUBQUERY|t_cmdb_app_relation|ALL|NULL|NULL|NULL|NULL|383|Usingwhere||3|DEPENDENTUNION|t_cmdb_app_relation|ALL|NULL|NULL|NULL|NULL|383|Usingwhere||NULL|UNIONRESULT|<union2,3>|ALL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+4rowsinset(0.00sec)
MySQL[xxuer]>explainSELECT->count(*)->FROM->t_cmdb_app_versiona->INNERJOIN->(SELECT->pid->FROM->t_cmdb_app_relationUNIONSELECT->rp_id->FROM->t_cmdb_app_relation)bONa.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|Usingwhere||1|PRIMARY|a|eq_ref|PRIMARY|PRIMARY|4|b.pid|1|Usingwhere;Usingindex||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|UNIONRESULT|<union2,3>|ALL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+5rowsinset(0.00sec)
以上就是mysql 优化中如何进行IN换INNER JOIN,小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。