Query 優(yōu)化基本思路

• 優(yōu)化更需要優(yōu)化的Query，什么語句更需要優(yōu)化？

1.優(yōu)化一個高并發(fā)Query比一個低并發(fā)的Query收益要大很多;
例子：
    比如一個一小時執(zhí)行1w次，每次需耗20次IO;另外一個每小時執(zhí)行20次，每次需要10000個IO；
    同樣優(yōu)化節(jié)省20000次IO，對于前一個來說每次優(yōu)化時只需要降低2個IO值便可；
    但對于后者，平均每次需要降低1000個；相比較前者優(yōu)化來得容易得多；

2.一個頻繁執(zhí)行的高并發(fā)Query的危險性比一個低并發(fā)的Query要大很多;
當一個低并發(fā)的Query走錯執(zhí)行計劃，所帶來的影響只是該Query的請求者的體驗會變差，對整個系統(tǒng)的影響不會特別突出，至少還在可控范圍；
（當然了，如果是使用了復雜join語句造成大量鎖表操作就另當別論了~）；
但是當一個高并發(fā)的Query走錯了執(zhí)行計劃，可能帶來災(zāi)難性的后果，很多時候連自救的機會都不給你就讓整個系統(tǒng)Crash掉；

• 定位優(yōu)化對象的性能瓶頸；

優(yōu)化要明確Query語句有什么問題，我為什么要優(yōu)化它？
拿到需要優(yōu)化的Query語句后，我們首先要判斷這個Query的瓶頸到底是IO還是CPU？
到底是因為數(shù)據(jù)訪問耗時太多，還是在數(shù)據(jù)運算（分組排序）？

• 明確的優(yōu)化目標；

做事情都要有目標，沒有目的沒完沒了的優(yōu)化也不行；
通常情況下，查詢響應(yīng)一般我會為自己設(shè)定查詢優(yōu)化響應(yīng)時間來作為量化的目標；
比如，我最近在做的業(yè)務(wù)表優(yōu)化，優(yōu)化目標就是:百萬級至至千萬級數(shù)據(jù)優(yōu)化單表聚合查詢響應(yīng)時間保持的200ms左右；

• 從Explain入手；

Explain是用來獲取一個Query在當前的數(shù)據(jù)庫中的執(zhí)行計劃，但在查看Explain結(jié)果之前，作為Query優(yōu)化人員，應(yīng)該要有一個
清晰的目標執(zhí)行計劃‘

Query 優(yōu)化基本原則：

• 多使用profile;

Query Profiler是一個非常方便的Query診斷分析工具，通過該工具可以獲取一條Query在整個執(zhí)行過程中對中資源的消耗情況；

1.開啟profiling參數(shù)：
  >set profiling=1;

2.執(zhí)行Query:
  >select  count(*) from users;

3.獲取系統(tǒng)中保存的所有Query的profile概要信息：
  >show profiles;

4.針對單個Query獲取詳細的profile信息,比如查看Query_ID為4的數(shù)據(jù)：
  >show profile cpu,block io for query 4;

• 永遠用小結(jié)果集驅(qū)動大的結(jié)果集；

mysql中的join只有一種實現(xiàn)方式：Nested Loop，即：嵌套循環(huán)來實現(xiàn)的；
驅(qū)動表的結(jié)果集越大，被驅(qū)動表的訪問次數(shù)增加，IO總量及CPU運算次數(shù)也隨即上升；
減少驅(qū)動表的結(jié)果集，從而減少IO總量及CPU運算總量

• 盡可能在索引中完成排序；
• 只取出自己需要的Columns;

減少Columns數(shù)量可以減少傳輸數(shù)據(jù)量，如果需要涉及排序，可以減少數(shù)據(jù)在排序區(qū)占用的內(nèi)存空間

• 僅僅使用最有效的過濾條件；

最有效的過濾條件可以減少索引所占用的存儲空間；

• 盡可能避免復雜的Join和子查詢

Join語句：
mysql在并發(fā)性的處理上并不是太好，當并發(fā)量太高的時候，系統(tǒng)整體性能可能會急劇下降；
尤其是遇到一些較為復雜的Query的時候更是如此；
當出現(xiàn)復雜的join語句，就意味著需要鎖定的資源也就多，所堵塞的其他線程也就多；
相反的，如果我們將比較復雜的Query語句拆成多個較為簡單的Query語句分布執(zhí)行，每次鎖定的資源會少很多，
所堵塞的其他線程也要少一些；當然啦，整個查詢的時間也會因此增長，這是一個取舍的過程；

子查詢：
子查詢通常很難得到一個很好的執(zhí)行計劃，很多時候命名有索引可以用，但是Query Optimizer就是不用；

對于復雜的Join和子查詢的應(yīng)用，明白其原理及可能造成的危害；在設(shè)計之時，有意識的規(guī)避及預估風險；
我也很反感為了優(yōu)化而優(yōu)化，為了吐槽而吐槽的行為；在曾經(jīng)的團隊里，筆者也遇到看到j(luò)oin語句就開始吐槽的同學；
畢竟優(yōu)化本身就是一個系統(tǒng)性能，平衡決策的過程；