MySQL查詢最佳化

標籤：查看   tab   優秀   多層   完整性   不同   sql查詢   reverse   多表   

1、簡介

     一個好的web應用，最重要的一點是有著優秀的訪問效能。資料庫MySQL是web應用的組成部分，也是決定其效能的重要部分。所以提升MySQL的效能至關重要。

     MySQL效能的提升可分為三部分，包括硬體、網路、軟體。其中硬體、網路取決於公司的財力，需要白嘩嘩的銀兩，這裡就不說啦。軟體又細分為很多種，在這裡我們通過MySQL的查詢最佳化從而達到效能的提升。

     最近看了一些關於查詢最佳化的書籍，同時也在網上看一些前輩們寫的文章。

以下是自己整理借鑒關於查詢最佳化的一些總結：

回到頂部2、截取SQL語句

     1、全面查詢日誌

     2、慢查詢日誌

     3、二進位日誌

     4、進程列表

　　SHOW FULL PROCESSLIST;

　　。。。

回到頂部3、查詢最佳化基本分析命令

　　1、EXPLAIN {PARTITIONS|EXTENDED}

　　2、SHOW CREATE TABLE tab;

　　3、SHOW INDEXS FROM tab;

　　4、SHOW TABLE STATUS LIKE ‘tab’;

　　5、SHOW [GLOBAL|SESSION] STATUS LIKE ‘’;

　　6、SHOW VARIABLES

　　。。。。

　　ps：我自己都感覺上面都是沒任何營養的東西。下面才是真正的乾貨哈。

回到頂部4、查詢最佳化幾個方向

　　1、盡量避免全文掃描，給相應欄位增加索引，應用索引來查詢

　　2、刪除不用或者重複的索引

　　3、查詢重寫，等價轉換（謂詞、子查詢、串連查詢）

　　4、刪除內容重複不必要的語句，精簡語句

　　5、整合重複執行的語句

　　6、緩衝查詢結果

回到頂部5、索引最佳化回到頂部　　5.1、索引優點：

　　　　1、保持資料的完整性

　　　　2、提高資料的查詢效能

　　　　3、改進表的串連操作（jion）

　　　　4、對查詢結果進行排序。沒索引將會採用內部檔案排序演算法進行排序，效率較慢

　　　　5、簡化彙總資料操作

回到頂部　　5.2、索引缺點

　　　　1、索引需要佔用一定的儲存空間

　　　　2、資料插入、更新、刪除時會受索引的影響，效能會降低。因為資料變更索引也需要進行更新

　　　　3、多個索引，最佳化器需要耗時則優選擇

回到頂部　　5.3、索引選擇

　　　　1、資料量大時採用

　　　　2、資料高度重複時，不採用

　　　　3、查詢取出資料大於20%，將採用全文掃描，不用索引

回到頂部　　5.4、索引細究

　　　　資料查詢：

　　　　MySQL中的InnoDB、MyISAM都是B-Tree類型索引

　　　　B-Tree包含：PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT

　　　　B-Tree類型索引不支援（即欄位使用以下符號時，將不採用索引）：

　　　　>, <, >=, <=, BETWEEN, !=, <>,like ‘%**’

　　　　【在此先介紹一下覆蓋索引】

　　　　以我自己理解的方式介紹吧。覆蓋索引並不是像主鍵索引、唯一索引一樣真實存在，它只是對索引應用某些特定情境的一種定義【另一種理解：查詢的列是索引列，因此列被索引覆蓋】。它可以突破傳統的限制，使用以上操作符，且依然採用索引進行查詢。

　　　　因為查詢的列是索引列，所以不需要讀取行，只需要讀取欄欄位資料就可以了。【例如你看一本書，需要找某一內容，剛好那內容出現在目錄中，那就不用一頁頁翻了，直接在目錄中定位到第幾頁尋找】

　　　　如何啟用覆蓋索引呢？什麼樣才是特定情境呢？

　　　　索引欄位，在select中出現就是了。

　　　　複合索引還可能有其他的特殊情境。例如，三列複合索引，僅需要在select、where、group by、order by中，任意一個地方出現一次複合索引最左邊列就可以啟用使用覆蓋索引了。

　　　　查看：

　　　　EXPLAIN中Extra顯示有Using index表示這條語句採用了覆蓋索引。

　　　　結論：

　　　　不建議在查詢的時候使用select*from進行查詢了，應該寫需要用的欄位，並且增加相應的索引，以提高查詢效能。

　　　　針對以上操作符實測結果：

　　　　1、以select*from形式，where中是primary key可以通殺【除like】（使用主鍵進行查詢）；index則全不可以。

　　　　2、以select 欄位a from tab where 欄位a《以上操作符》形式測試，結果依然可以使用索引查詢。【採用了覆蓋索引】

　　　　其他索引最佳化方法:

　　　　1、使用索引關鍵字作為串連的條件

　　　　2、複合索引使用

　　　　3、索引合并or and，將涉及到的欄位合并成複合索引

　　　　4、where、和group by涉及欄位加索引

回到頂部6、子查詢最佳化

　　在from中為非相互關聯的子查詢，可以上拉子查詢到父層。在多表串連查詢考慮串連代價再選擇。

　　查詢最佳化工具對子查詢一般採用嵌套執行的方式，即對父查詢中的每一行，都執行一次子查詢，這樣子查詢會執行很多次。這種執行方式效率很低。

　　子查詢轉化為串連查詢優點：

　　1、子查詢不用執行很多次

　　2、最佳化器可以根據資訊來選擇不同的方法和串連順序

　　3、子查詢的串連條件，過濾條件變成父查詢的篩選條件，以提高效率。

　　最佳化：

　　子查詢合并，若多個子查詢，能合并的盡量合并。

　　子查詢展開，即上拉變成多表查詢（時刻保證等價變化）

　　注意：

　　子查詢展開只能展開簡單的查詢，若子查詢含有聚集合函式、GROUP BY、DISTINCT，則不能上拉。

　　select * from t1 (select*from tab where id>10) as t2 where t1.age>10 and t2.age<25;

　　select*from t1,tab as t2 where t1.age>10 and t2.age<25 and t2.id>10;

　　具體步驟：

　　1、from與from合并，修改相應參數

　　2、where與where合并，用and串連

　　3、修改相應的謂詞（in改=）

回到頂部7、等價謂詞重寫：

　　1、BETWEEEN AND改寫為 >= 、<=之類的。實測：十萬條資料，重寫前後時間，1.45s、0.06s

　　2、in轉換多個or。欄位為索引時，兩個都能用到索引，or效率相對in好一點

　　3、name like ‘abc%’改寫成name>=’abc’ and name<’abd’;

　　注意：百萬級資料測試，name沒有索引之前like比後一種查詢快；給欄位增加索引後，後面的快一點點，相差不大，因為兩種方法在查詢的時候都用到了索引。

　　。。。。

回到頂部8、條件化簡與最佳化

　　1、將where、having（不存在groupby和聚集合函式時）、join-on條件能合并的盡量合并

　　2、刪除不必要的括弧，減少文法分許的or和and樹層，減少cpu消耗

　　3、常量傳遞。a=b and b=2轉換為 a=2 and b=2。盡量不使用變數a=b或[email protected]

　　4、消除沒用的SQL條件

　　5、where等號右邊盡量不出現運算式計算；where中不要對欄位進行運算式計算、函數的使用

　　6、恒等變換、不等式變換。例：測試百萬級資料a>b and b>10變為a>b and a>10 and b>10最佳化顯著

回到頂部9、外串連最佳化

　　即將外串連轉為內串連

　　優點：

　　1、最佳化處理器處理外串連比內串連步驟多且耗時

　　2、外串連消除後，最佳化器選擇多表串連順序有更多選擇，可以擇優而選

　　3、可以將篩選條件最為嚴格的表作為外表（串連順序最前面，是多層迴圈體的外迴圈層），

　　可以減少不必要的I/O開銷，能加快演算法執行的速度。

　　on a.id=b.id與where a.id=b.id的差別，on則表進行串連，where則進行資料對比

　　注意：前提必須是結果為NULL決絕（即條件限制不要NULL資料行，語意上是內串連）

　　最佳化原則：

　　精簡查詢，串連消除，等效轉換，去除多餘表對象串連

　　例如：主鍵/唯一鍵作為串連條件，且中間表列只作為等值條件，可以去掉中間表串連

回到頂部10、其他查詢最佳化

　　1、以下將會造成放棄索引查詢，採用全文掃描

　　　　1.1、where 子句中使用!=或<>操作符　　注意：主鍵支援。非主鍵不支援

　　　　1.2、避免使用or

　　　　　　經測試，並非是使用了or就一定不能使用索引，大多情況下是沒用到索引，但還有少數情況是用到的，因此具體情況具體分析。

　　　　　　類似最佳化：

　　　　　　select * from tab name=’aa’ or name=’bb’;

　　　　　　=>

　　　　　　select * from tab name=’aa’

　　　　　　union all

　　　　　　select * from tab name=’bb’;

　　　　　　實測：

　　　　　　1、十萬資料測試，沒任何索引的情況下，上面比下面的查詢速率快一倍。

　　　　　　2、三十萬資料測試，aa與bb都是單獨索引情況下，下面的查詢速率比or快一點。

　　　　1.3、避免使用not in

　　　　　　not in一般不能使用索引；主鍵欄位可以

　　　　1.4、where中盡量避免使用對null的判斷

　　　　1.5、like不能前置百分比符號 like ‘%.com’

　　　　　　解決：

　　　　　　　　1、若必須使用%前置，且資料長度不大，例如URL，可將資料翻轉存入資料庫，再來查。LIKE REVERSE‘%.com’;

　　　　　　　　2、使用覆蓋索引

 

　　　　1.6、使用索引欄位作為條件的時候，假若是複合索引，則應該使用索引最左邊首碼的欄位名

　　2、將exists代替in

　　　　select num from a where num in(select num from b)

　　　　select num from a where exists(select 1 from b where num=a.num)

　　　　一百萬條資料，篩選59417條資料用時6.65s、4.18s。沒做其他最佳化，僅僅只是將exists替換in。

　　3、欄位定義是字串，查詢時沒帶引號，不會用索引，將會進行全文掃描。

　　【以下是摘抄於半夜亂彈琴博文http://www.cnblogs.com/lingiu/p/3414134.html，本人沒進行相應的測試】

　　4、盡量使用表變數來代替暫存資料表

　　5、避免頻繁建立和刪除暫存資料表，以減少系統資料表資源的消耗

　　6、如果使用到了暫存資料表，在預存程序的最後務必將所有的暫存資料表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統資料表的較長時間鎖定

　　7、盡量避免使用遊標，因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該考慮改寫

　　8、大資料量，若資料量過大，應該考慮相應需求是否合理。

　　9、盡量避免大事務操作，提高系統並發能力。

　　。。。。。

回到頂部11、博文總結

　　經過這些天查資料敲代碼的學習，瞭解到了MySQL的查詢最佳化並不是簡簡單單的按照某個公式某個規則就可達到的。實驗是檢驗標準的唯一標準，經過這幾天的測試，得出的結論就是：MySQL的查詢最佳化是有大方向，但是想要得出一個萬能最佳化公式那是不可能的，畢竟每一條SQL查詢語句的寫法、結果著重點、以及表的欄位環境都不一樣。能夠達到看SQL查詢語句就能得出最佳化方法的大神，必定是仔細研究過SQL查詢最佳化並且有過好幾年最佳化經驗的老鳥。哈哈，我還只是個小菜鳥。

　　建議各位正在學習SQL查詢最佳化的童鞋們：不要僅僅只是看，要多敲代碼，多測試，各種欄位環境測試、各種資料量層級測試。

 

以上是自己的一些總結，也許有些不足。畢竟自己還只是個菜鳥，並且也不是DBA的方向，若大家發現有不足的地方，或者錯誤的地方，請您能夠提出來。

MySQL查詢最佳化