關于mysql優化之IN換INNER JOIN的實例分享

409

今天擼代碼時，遇到SQL問題：

（相關mysql視頻教程推薦：《mysql教程》）

要將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]&gt;?explain?SELECT? ????-&gt;?????COUNT(*) ????-&gt;?FROM ????-&gt;?????t_cmdb_app_version ????-&gt;?WHERE ????-&gt;?????id?IN?(SELECT? ????-&gt;?????????????pid ????-&gt;?????????FROM ????-&gt;?????????????t_cmdb_app_relation?UNION?SELECT? ????-&gt;?????????????rp_id ????-&gt;?????????FROM ????-&gt;?????????????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>??????????|?ALL???|?NULL??????????|?NULL????|?NULL????|?NULL?|?NULL?|?Using?temporary??????????| +----+--------------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+ 4?rows?in?set?(0.00?sec)</union2>
MySQL?[xxuer]&gt;?explain?SELECT? ????-&gt;?????count(*) ????-&gt;?FROM ????-&gt;?????t_cmdb_app_version?a ????-&gt;?????????INNER?JOIN ????-&gt;?????(SELECT? ????-&gt;?????????pid ????-&gt;?????FROM ????-&gt;?????????t_cmdb_app_relation?UNION?SELECT? ????-&gt;?????????rp_id ????-&gt;?????FROM ????-&gt;?????????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>??????????|?ALL????|?NULL??????????|?NULL????|?NULL????|?NULL??|?NULL?|?Using?temporary??????????| +----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+ 5?rows?in?set?(0.00?sec)</union2></derived2>

以上就是關于

? 版權聲明
THE END
數據庫
# mysql
喜歡就支持一下吧
點贊9 分享