摘要:最近遇見(jiàn)一個(gè)的慢查問(wèn)題,于是排查了下,這里把相關(guān)的過(guò)程做個(gè)總結(jié)。而且,我檢查了子查詢的表的索引,中用到的查詢條件都已經(jīng)增加了索引。還好我們的子查詢加了必要的索引,不然結(jié)果會(huì)更加慘不忍睹。這個(gè)結(jié)果真是太坑爹,而且十分違反直覺(jué)。
最近遇見(jiàn)一個(gè) MySQL 的慢查問(wèn)題,于是排查了下,這里把相關(guān)的過(guò)程做個(gè)總結(jié)。
定位原因我首先查看了 MySQL 的慢查詢?nèi)罩荆l(fā)現(xiàn)有這樣一條 query 耗時(shí)非常長(zhǎng)(大概在 1 秒多),而且掃描的行數(shù)很大(10 多萬(wàn)條數(shù)據(jù),差不多是全表了):
</>復(fù)制代碼
SELECT * FROM tgdemand_demand t1
WHERE
(
t1.id IN
(
SELECT t2.demand_id
FROM tgdemand_job t2
WHERE (t2.state = "working" AND t2.wangwang = "abc")
)
AND
NOT (t1.state = "needConfirm")
)
ORDER BY t1.create_date DESC
這個(gè)查詢不是很復(fù)雜,首先執(zhí)行一個(gè)子查詢,取到任務(wù)的狀態(tài)(state)是 "working" 并且任務(wù)的關(guān)聯(lián)人 (wangwang)是"abc"的所有需求 id(這個(gè)設(shè)計(jì)師進(jìn)行中的任務(wù)對(duì)應(yīng)的需求 id),然后再到主表 tgdemand_demand 中帶入剛才的 id 集合,查詢出需求狀態(tài)(state)不是 "needConfirm" 的所有需求,最后進(jìn)行一個(gè)排序。
按道理子查詢篩選出 id 后到主表過(guò)濾是直接使用到主鍵,應(yīng)該是很快的啊。而且,我檢查了子查詢的 tgdemand_job 表的索引,where 中用到的查詢條件都已經(jīng)增加了索引。怎么會(huì)這樣呢?
于是,我對(duì)這個(gè) query 執(zhí)行了一個(gè) explain(輸出 sql 語(yǔ)句的執(zhí)行計(jì)劃),看看 MySQL 的執(zhí)行計(jì)劃是怎樣的。輸出如下:
我們看到,第一行是 t1 表,type 是 ALL(全表掃描),rows(影響行數(shù))是 157089,沒(méi)有用到任何索引;第二行是 t2 表,用到了索引。和我之前理解的執(zhí)行順序完全不一樣!
為什么 MySQL 不是先執(zhí)行子查詢,而是對(duì) t1 表進(jìn)行了全表掃描呢?我們仔細(xì)看第二行的 select_type,發(fā)現(xiàn)它的值是 DEPENDENT_SUBQUERY,意思是這個(gè)子查詢的查詢方式依賴外層的查詢。這是什么意思?
實(shí)際上,MySQL 對(duì)于這種子查詢會(huì)進(jìn)行改寫,上面的 SQL 會(huì)被改寫成下面的形式:
</>復(fù)制代碼
SELECT * FROM tgdemand_demand t1 WHERE EXISTS (
SELECT * FROM tgdemand_job t2 WHERE t1.id = t2.demand_id AND (t2.state = "working" AND t2.wangwang = "abc")
) AND NOT (t1.state = "needConfirm")
ORDER BY t1.create_date DESC;
這表示,SQL 會(huì)去掃描 tgdemand_demand 表的所有數(shù)據(jù),每條數(shù)據(jù)再傳入到子查詢中與表 tgdemand_job 進(jìn)行關(guān)聯(lián),執(zhí)行子查詢,子查詢根本不會(huì)先執(zhí)行,而且子查詢會(huì)執(zhí)行 157089 次(外層表的記錄數(shù)量)。還好我們的子查詢加了必要的索引,不然結(jié)果會(huì)更加慘不忍睹。
這個(gè)結(jié)果真是太坑爹,而且十分違反直覺(jué)。對(duì)于慢查詢,千萬(wàn)不要想當(dāng)然,還是多多 explain,看看數(shù)據(jù)庫(kù)實(shí)際上是怎么去執(zhí)行的。
問(wèn)題修復(fù)既然子查詢會(huì)被改寫,那最簡(jiǎn)單的解決方案就是不用子查詢,將內(nèi)層獲取需求 id 的 SQL 多帶帶拿出來(lái)執(zhí)行,取到結(jié)果后再執(zhí)行一條 SQL 去獲取實(shí)際的數(shù)據(jù)。大概像這樣(下面的語(yǔ)句是不合法的,只是示意):
</>復(fù)制代碼
ids = SELECT t2.demand_id
FROM tgdemand_job t2
WHERE (t2.state = "working" AND t2.wangwang = "abc");
SELECT * FROM tgdemand_demand t1
WHERE
(
t1.id IN ids
AND
NOT (t1.state = "needConfirm")
)
ORDER BY t1.create_date DESC;
說(shuō)干咱就干,我找到了下面的代碼(是 python 語(yǔ)言寫的):
</>復(fù)制代碼
demand_ids = Job.objects.filter(wangwang=user["wangwang"], state="working").values_list("demand_id", flat=True)
demands = Demand.objects.filter(id__in=demand_ids).exclude(state__in=["needConfirm"]).order_by("-create_date")
咦!這不是和我想得是一樣的嘛?先查出需求 id(代碼第一行),然后用 id 集合再去執(zhí)行實(shí)際的查詢(代碼第二行)。為什么經(jīng)過(guò) ORM 框架的處理后產(chǎn)出的 SQL 就不一樣了呢?
帶著這個(gè)問(wèn)題我搜索了一番。原來(lái) Django 自帶的 ORM 框架生成的 QuerySet 是懶執(zhí)行的(lazy evaluated),我們可以將這種 QuerySet 到處傳,直到需要時(shí)才會(huì)實(shí)際的執(zhí)行 SQL。
比如,我們代碼里面的 Job.objects.filter(wangwang=user["wangwang"], state="working").values_list("demand_id", flat=True) 這個(gè) QuerySet 實(shí)際上并沒(méi)有執(zhí)行,就被作為參數(shù)傳遞給了 id__in,當(dāng) Demand.objects.filter(id__in=demand_ids).exclude(state__in=["needConfirm"]).order_by("-create_date") 這個(gè) QuerySet 執(zhí)行時(shí),剛才未執(zhí)行的 QuerySet 才開始作為 SQL 執(zhí)行,于是生成了最開始的 SQL 語(yǔ)句。
既然如此,我們的目的要讓 QuerySet 提前執(zhí)行,獲得結(jié)果集。根據(jù)文檔,對(duì) QuerySet 進(jìn)行循環(huán)、slice、取 len、list 轉(zhuǎn)換的時(shí)候被執(zhí)行。于是我將代碼更改為了下面的樣子:
</>復(fù)制代碼
demand_ids = list(Job.objects.filter(wangwang=user["wangwang"], state="working").values_list("demand_id", flat=True))
demands = Demand.objects.filter(id__in=demand_ids).exclude(state__in=["needConfirm"]).order_by("-create_date")
終于,頁(yè)面打開速度恢復(fù)正常了。
實(shí)際上,我們也可以對(duì) SQL 進(jìn)行改寫來(lái)解決問(wèn)題:
</>復(fù)制代碼
select * from tgdemand_demand t1, (select t.demand_id from tgdemand_job t where t.state = "working" and t.wangwang = "abc") t2
where t1.id=t2.demand_id and not (t1.state = "needConfirm")
order by t1.create_date DESC
思路是去掉子查詢,換用 2 個(gè)表進(jìn)行 join 的方式來(lái)取得數(shù)據(jù)。這里就不展開了。
感想框架可以提高生產(chǎn)率的前提是對(duì)背后的原理足夠了解,不然應(yīng)用很可能就會(huì)在某個(gè)時(shí)間暴露出一些隱蔽的要命問(wèn)題(這些問(wèn)題在小規(guī)模階段可能根本都發(fā)現(xiàn)不了......)。保證應(yīng)用的健壯真是個(gè)大學(xué)問(wèn),還有很多東西值得我們?nèi)ヌ剿鳌?/p> 參考資料
http://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html
http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
https://docs.djangoproject.com/en/1.9/ref/models/querysets/
文章版權(quán)歸作者所有,未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址:http://m.specialneedsforspecialkids.com/yun/37701.html
摘要:所以在掃描次后,率先到達(dá)狀態(tài),那么此刻將停止掃描,進(jìn)入到算分的階段。除了這條引發(fā)故障的之外,其他的字段命中索引數(shù)量都非常小,有的甚至只有一條。那這里很明顯在中只去根據(jù)中執(zhí)行計(jì)劃的相關(guān)索引來(lái)進(jìn)行判斷是不合理的。 前段時(shí)間筆者遇到一個(gè)MongoBD Plan Cache的bug,于是深究了下MongoDB優(yōu)化器相關(guān)源碼。在這里分享給大家,一方面讓大家知道MongoDB優(yōu)化器工作原理,一方面...
閱讀 2501·2021-11-15 18:14
閱讀 1724·2021-10-14 09:42
閱讀 3765·2021-10-11 10:58
閱讀 3964·2021-10-09 09:44
閱讀 2425·2021-09-26 09:55
閱讀 2449·2021-09-24 10:38
閱讀 2038·2021-09-04 16:48
閱讀 3278·2021-09-02 15:21