說說 MySQL 子查詢
前言
前兩天開發(fā)找DBA解決一個含有子查詢的慢sql，我們通過將其修改為關(guān)聯(lián)查詢和添加索引解決。考慮到 大多數(shù)開發(fā)并沒有準(zhǔn)確的理解 MySQL 的子查詢執(zhí)行原理。本文介紹如何解決子查詢慢查的思路。
原理
首先 知其然，知其所以然。大部分子查詢?yōu)槭裁绰课覀兊昧私?MySQL 關(guān)聯(lián)查詢和子查詢的處理機(jī)制。
MySQL 在處理所有的查詢的時候都強(qiáng)行轉(zhuǎn)換為聯(lián)接來執(zhí)行，將每個查詢包括多表中關(guān)聯(lián)匹配，關(guān)聯(lián)子查詢，union，甚至單表的的查詢都處理為聯(lián)接，接著MySQL執(zhí)行聯(lián)接，把每個聯(lián)接再處理為嵌套循環(huán) (nest-loop);
很多使用子查詢的人 想當(dāng)然的認(rèn)為 子查詢會由內(nèi)到外，先完成子查詢的結(jié)果， 然后在用子查詢來驅(qū)動外查詢的表，完成查詢。例如：select * from test where tid in (select aid from sub_test where gid=3)通常我們會想到該sql的執(zhí)行順序?yàn)?
a. 先從 sub_test 表中獲取 gid=3的記錄(3,4,5)
b. 然后和外面的查詢做匹配 tid in (3,4,5)。
但是，實(shí)際上對于子查詢，外部查詢的每條符合條件的記錄，都會把子查詢執(zhí)行一次。如果遇到子查詢查詢量比較大或者索引不合理的情況，sql就變慢查。
當(dāng)我們使用explian查看包含子查詢的執(zhí)行計劃時，尤其要注意select_type 字段的內(nèi)容，如果包含 SUBQUERY , DEPENDENT SUBQUERY 就需要提高警惕。
官方含義為：
SUBQUERY：子查詢中的第一個SELECT；
DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢 ，注意如果外部查詢的結(jié)果集數(shù)量比較大，比如幾十萬上百萬，就會執(zhí)行幾十萬上百萬次子查詢，必然造成慢查。
優(yōu)化策略
MySQL子查詢優(yōu)化策略大致分為：
半連接（semi-join): 半連接優(yōu)化本質(zhì)上是把子查詢上拉到父查詢中，與父查詢的表做join/semi-join的操作。關(guān)鍵詞上拉。 物化子查詢(Materialization)：子查詢的結(jié)果通常緩存在內(nèi)存或臨時表中。 EXISTS strategy：把半連接轉(zhuǎn)換為EXISTS操作。本質(zhì)上是把父表的條件下推到子查詢中關(guān)鍵詞下推。
一圖勝千言 ，下圖展示了 MySQL 針對子查詢的優(yōu)化策略
需要對圖中做解釋的是:
白色區(qū)域是常見的 子查詢類型， x IN (SELECT ...) ，x= any(select)，exists (select )。 白色區(qū)域越大說明使用頻率越多，比如最常見的子查詢是 x IN (SELECT ...) 有顏色的區(qū)域表示優(yōu)化方法和策略，不同顏色代表不同的mysql 分支。
強(qiáng)烈安利 Mariadb 的一系列博客，里面有n篇文章介紹subquery的優(yōu)化。
書上來的終覺淺，絕知此事要躬行。
優(yōu)化案例
業(yè)務(wù)的sql 如下，該sql 執(zhí)行超過1200ms ，被sql-killer kill掉，影響業(yè)務(wù)使用。
select app_name,pkg_version,zone,created_at
from activity
where id in (
select MAX(id) AS id
from activity
where zone = 'qa' AND status = 2 AND zanpkg_version != ''
AND namespace = 'qa'
group by app_name,zone)
order by id desc limit 500;
執(zhí)行計劃
第一步 MySQL 執(zhí)行 select id, app_name,pkg_version,zone,created_at from activity order by id desc limit 500; 獲取一個結(jié)果集
第二部 拿第一步中的結(jié)果500多行每一個記錄去執(zhí)行 子查詢，每次遍歷70w行左右。而且子查詢里面沒有合適的索引。
優(yōu)化方法
1 where條件中zone=qa是固定值，group by zone 無意義，去掉group by zone。
2 針對 (zone, namespace, status) 加上組合索引。
3 改子查詢?yōu)殛P(guān)聯(lián)查詢。
select a.app_name, a.zanpkg_version, a.zone, a.created_at
from activity a, ( select MAX(id) AS mid
from activity
where zone = 'qa' AND status = 2 AND zanpkg_version != ''
AND namespace = 'qa'
group by app_name) b
where a.id = b.mid limit 500;
修改之后的sql執(zhí)行時間在 300-500ms 之間。感覺還是慢，因?yàn)橐獙κ畮兹f的數(shù)據(jù)量做 聚合運(yùn)算。
參考文章
https://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html https://blog.csdn.net/kk185800961/article/details/49340589
https://blog.csdn.net/fly2nn/article/details/61924636
https://blog.csdn.net/fly2nn/article/details/61924637
https://blog.csdn.net/fly2nn/article/details/61924640
-The End-
往期推薦
推薦一個長期關(guān)注于
數(shù)據(jù)庫技術(shù)以及性能優(yōu)化、故障案例分析的公眾號