一次非常有意思的SQL優(yōu)化經(jīng)歷:從30248.271s到0.001s
https://www.toutiao.com/i6668275333034148356
場景
用的數(shù)據(jù)庫是mysql5.6，下面簡單的介紹下場景
課程表：
數(shù)據(jù)100條
學(xué)生表：
數(shù)據(jù)70000條
學(xué)生成績表SC：
數(shù)據(jù)70w條
查詢目的：
查找語文考100分的考生
查詢語句：
執(zhí)行時間：30248.271s
為什么這么慢？先來查看下查詢計劃：
發(fā)現(xiàn)沒有用到索引，type全是ALL，那么首先想到的就是建立一個索引，建立索引的字段當(dāng)然是在where條件的字段。
先給sc表的c_id和score建個索引
再次執(zhí)行上述查詢語句，時間為:?1.054s
快了3w多倍，大大縮短了查詢時間，看來索引能極大程度的提高查詢效率，看來建索引很有必要，很多時候都忘記建索引了，數(shù)據(jù)量小的的時候壓根沒感覺，這優(yōu)化感覺挺爽。
但是1s的時間還是太長了，還能進(jìn)行優(yōu)化嗎，仔細(xì)看執(zhí)行計劃：
查看優(yōu)化后的sql:
補(bǔ)充：這里有網(wǎng)友問怎么查看優(yōu)化后的語句
方法如下：
在命令窗口執(zhí)行
有type=all
按照我之前的想法，該sql的執(zhí)行的順序應(yīng)該是先執(zhí)行子查詢
耗時：0.001s
得到如下結(jié)果：
然后再執(zhí)行
耗時：0.001s
這樣就是相當(dāng)快了啊，Mysql竟然不是先執(zhí)行里層的查詢，而是將sql優(yōu)化成了exists子句，并出現(xiàn)了EPENDENT SUBQUERY，
mysql是先執(zhí)行外層查詢，再執(zhí)行里層的查詢，這樣就要循環(huán)70007*11=770077次。
那么改用連接查詢呢？
這里為了重新分析連接查詢的情況，先暫時刪除索引sc_c_id_index，sc_score_index
執(zhí)行時間是：0.057s
效率有所提高，看看執(zhí)行計劃：
這里有連表的情況出現(xiàn)，我猜想是不是要給sc表的s_id建立個索引
CREATE index sc_s_id_index on SC(s_id);
show index from SC
在執(zhí)行連接查詢
時間: 1.076s，竟然時間還變長了，什么原因？查看執(zhí)行計劃：
優(yōu)化后的查詢語句為：
貌似是先做的連接查詢，再執(zhí)行的where過濾
回到前面的執(zhí)行計劃：
這里是先做的where過濾，再做連表，執(zhí)行計劃還不是固定的，那么我們先看下標(biāo)準(zhǔn)的sql執(zhí)行順序：
正常情況下是先join再where過濾，但是我們這里的情況，如果先join，將會有70w條數(shù)據(jù)發(fā)送join做操，因此先執(zhí)行where
過濾是明智方案，現(xiàn)在為了排除mysql的查詢優(yōu)化，我自己寫一條優(yōu)化后的sql
即先執(zhí)行sc表的過濾，再進(jìn)行表連接，執(zhí)行時間為：0.054s
和之前沒有建s_id索引的時間差不多
查看執(zhí)行計劃：
先提取sc再連表，這樣效率就高多了，現(xiàn)在的問題是提取sc的時候出現(xiàn)了掃描表，那么現(xiàn)在可以明確需要建立相關(guān)索引
再執(zhí)行查詢：
執(zhí)行時間為：0.001s，這個時間相當(dāng)靠譜，快了50倍
執(zhí)行計劃：
我們會看到，先提取sc，再連表，都用到了索引。
那么再來執(zhí)行下sql
執(zhí)行時間0.001s
執(zhí)行計劃：
這里是mysql進(jìn)行了查詢語句優(yōu)化，先執(zhí)行了where過濾，再執(zhí)行連接操作，且都用到了索引。
總結(jié)
1、mysql嵌套子查詢效率確實(shí)比較低
2、可以將其優(yōu)化成連接查詢
3、建立合適的索引
4、學(xué)會分析sql執(zhí)行計劃，mysql會對sql進(jìn)行優(yōu)化，所以分析執(zhí)行計劃很重要
最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識第二版》和《SQL高級知識第二版》的PDF電子版。里面有各個語法的解釋、大量的實(shí)例講解和批注等等，非常通俗易懂，方便大家跟著一起來實(shí)操。 有需要的讀者可以下載學(xué)習(xí)，在下面的公眾號「數(shù)據(jù)前線」(非本號)后臺回復(fù)關(guān)鍵字：SQL，就行 數(shù)據(jù)前線 ——End——
后臺回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。
推薦閱讀
