本文來源 |>
作者 | Java技術架構
背景介紹
我用的數據庫是mysql5.6，下面簡單的介紹下場景：
課程表：
數據100條
學生表：
數據70000條
學生成績表SC：
數據70w條
查詢目的：
查找語文考100分的考生
查詢語句：
執行時間：30248.271s
為什麼這麼慢？先來查看下查詢計劃：
發現沒有用到索引，type全是ALL，那麼首先想到的就是建立一個索引，建立索引的字段當然是在where條件的字段。
先給sc表的c_id和score建個索引
再次執行上述查詢語句，時間為: 1.054s
快了3w多倍，大大縮短了查詢時間，看來索引能極大程度的提高查詢效率，看來建索引很有必要，很多時候都忘記建索引了，數據量小的的時候壓根沒感覺，這優化感覺挺爽。
但是1s的時間還是太長了，還能進行優化嗎，仔細看執行計劃：
查看優化後的sql:
補充：這裡有網友問怎麼查看優化後的語句
方法如下：
在命令窗口執行
有type=all
按照我之前的想法，該sql的執行的順序應該是先執行子查詢
耗時：0.001s
得到如下結果：
然後再執行
耗時：0.001s
這樣就是相當快了啊，Mysql竟然不是先執行裡層的查詢，而是將sql優化成了exists子句，並出現了EPENDENT SUBQUERY，
mysql是先執行外層查詢，再執行裡層的查詢，這樣就要循環70007*11=770077次。
那麼改用連接查詢呢？
這裡為了重新分析連接查詢的情況，先暫時刪除索引sc_c_id_index，sc_score_index
執行時間是：0.057s
效率有所提高，看看執行計劃：
這裡有連表的情況出現，我猜想是不是要給sc表的s_id建立個索引
CREATE index sc_s_id_index on SC(s_id);
show index from SC
在執行連接查詢
時間: 1.076s，竟然時間還變長了，什麼原因？查看執行計劃：
優化後的查詢語句為：
貌似是先做的連接查詢，再執行的where過濾
回到前面的執行計劃：
這裡是先做的where過濾，再做連表，執行計劃還不是固定的，那麼我們先看下標準的sql執行順序：
苦逼的碼農注：知道執行順序是非常重要的，建議通過這張表熟悉一下，最後不要死記硬背，而是理解他們。
正常情況下是先join再where過濾，但是我們這裡的情況，如果先join，將會有70w條數據發送join做操，因此先執行where過濾是明智方案，現在為了排除mysql的查詢優化，我自己寫一條優化後的sql
即先執行sc表的過濾，再進行表連接，執行時間為：0.054s
和之前沒有建s_id索引的時間差不多
查看執行計劃：
先提取sc再連表，這樣效率就高多了，現在的問題是提取sc的時候出現了掃描表，那麼現在可以明確需要建立相關索引
再執行查詢：
執行時間為：0.001s，這個時間相當靠譜，快了50倍
執行計劃：
我們會看到，先提取sc，再連表，都用到了索引。
那麼再來執行下sql
執行時間0.001s
執行計劃：
這裡是mysql進行了查詢語句優化，先執行了where過濾，再執行連接操作，且都用到了索引。
總結
1、mysql嵌套子查詢效率確實比較低
2、可以將其優化成連接查詢
3、建立合適的索引
4、學會分析sql執行計劃，mysql會對sql進行優化，所以分析執行計劃很重要。
閱讀更多 TOP100 的文章
