MySQL的查詢(xún)優(yōu)化方法

本篇內容主要講解“的查詢(xún)優(yōu)化方法”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強。下面就讓小編來(lái)帶大家學(xué)習“MySQL的查詢(xún)優(yōu)化方法”吧!

1、簡(jiǎn)介

     一個(gè)好的web應用，最重要的一點(diǎn)是有著(zhù)優(yōu)秀的訪(fǎng)問(wèn)性能。數據庫MySQL是web應用的組成部分，也是決定其性能的重要部分。所以提升MySQL的性能至關(guān)重要。

     MySQL性能的提升可分為三部分，包括硬件、網(wǎng)絡(luò )、軟件。其中硬件、網(wǎng)絡(luò )取決于公司的財力，需要白嘩嘩的銀兩，這里就不說(shuō)啦。軟件又細分為很多種，在這里我們通過(guò)MySQL的查詢(xún)優(yōu)化從而達到性能的提升。

     最近看了一些關(guān)于查詢(xún)優(yōu)化的書(shū)籍，同時(shí)也在網(wǎng)上看一些前輩們寫(xiě)的文章。

以下是自己整理借鑒關(guān)于查詢(xún)優(yōu)化的一些總結：

回到頂部

2、截取SQL語(yǔ)句

     1、全面查詢(xún)日志

     2、慢查詢(xún)日志

     3、二進(jìn)制日志

     4、進(jìn)程列表

　　SHOW FULL PROCESSLIST;

　　。。。

回到頂部

3、查詢(xún)優(yōu)化基本分析命令

　　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：我自己都感覺(jué)上面都是沒(méi)任何營(yíng)養的東西。下面才是真正的干貨哈。

回到頂部

4、查詢(xún)優(yōu)化幾個(gè)方向

　　1、盡量避免全文掃描，給相應字段增加索引，應用索引來(lái)查詢(xún)

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

　　3、查詢(xún)重寫(xiě)，等價(jià)轉換（謂詞、子查詢(xún)、連接查詢(xún)）

　　4、刪除內容重復不必要的語(yǔ)句，精簡(jiǎn)語(yǔ)句

　　5、整合重復執行的語(yǔ)句

　　6、緩存查詢(xún)結果

回到頂部

5、索引優(yōu)化

回到頂部

　　5.1、索引優(yōu)點(diǎn)：

　　　　1、保持數據的完整性

　　　　2、提高數據的查詢(xún)性能

　　　　3、改進(jìn)表的連接操作（jion）

　　　　4、對查詢(xún)結果進(jìn)行排序。沒(méi)索引將會(huì )采用內部文件排序算法進(jìn)行排序，效率較慢

　　　　5、簡(jiǎn)化聚合數據操作

回到頂部

　　5.2、索引缺點(diǎn)

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

　　　　2、數據插入、更新、刪除時(shí)會(huì )受索引的影響，性能會(huì )降低。因為數據變更索引也需要進(jìn)行更新

　　　　3、多個(gè)索引，優(yōu)化器需要耗時(shí)則優(yōu)選擇

回到頂部

　　5.3、索引選擇

　　　　1、數據量大時(shí)采用

　　　　2、數據高度重復時(shí)，不采用

　　　　3、查詢(xún)取出數據大于20%，將采用全文掃描，不用索引

回到頂部

　　5.4、索引細究

　　　　資料查詢(xún)：

　　　　MySQL中的InnoDB、MyISAM都是B-Tree類(lèi)型索引

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

　　　　B-Tree類(lèi)型索引不支持（即字段使用以下符號時(shí)，將不采用索引）：

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

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

　　　　以我自己理解的方式介紹吧。覆蓋索引并不是像主鍵索引、唯一索引一樣真實(shí)存在，它只是對索引應用某些特定場(chǎng)景的一種定義【另一種理解：查詢(xún)的列是索引列，因此列被索引覆蓋】。它可以突破傳統的限制，使用以上操作符，且依然采用索引進(jìn)行查詢(xún)。

　　　　因為查詢(xún)的列是索引列，所以不需要讀取行，只需要讀取列字段數據就可以了?！纠缒憧匆槐緯?shū)，需要找某一內容，剛好那內容出現在目錄中，那就不用一頁(yè)頁(yè)翻了，直接在目錄中定位到第幾頁(yè)查找】

　　　　如何激活覆蓋索引呢？什么樣才是特定場(chǎng)景呢？

　　　　索引字段，在select中出現就是了。

　　　　復合索引還可能有其他的特殊場(chǎng)景。例如，三列復合索引，僅需要在select、where、group by、order by中，任意一個(gè)地方出現一次復合索引最左邊列就可以激活使用覆蓋索引了。

　　　　查看：

　　　　EXPLAIN中Extra顯示有Using index表示這條語(yǔ)句采用了覆蓋索引。

　　　　結論：

　　　　不建議在查詢(xún)的時(shí)候使用select*from進(jìn)行查詢(xún)了，應該寫(xiě)需要用的字段，并且增加相應的索引，以提高查詢(xún)性能。

　　　　針對以上操作符實(shí)測結果：

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

　　　　2、以select 字段a from tab where 字段a《以上操作符》形式測試，結果依然可以使用索引查詢(xún)?！静捎昧烁采w索引】

　　　　其他索引優(yōu)化方法:

　　　　1、使用索引關(guān)鍵字作為連接的條件

　　　　2、復合索引使用

　　　　3、索引合并or and，將涉及到的字段合并成復合索引

　　　　4、where、和group by涉及字段加索引

回到頂部

6、子查詢(xún)優(yōu)化

　　在from中為非相關(guān)子查詢(xún)，可以上拉子查詢(xún)到父層。在多表連接查詢(xún)考慮連接代價(jià)再選擇。

　　查詢(xún)優(yōu)化器對子查詢(xún)一般采用嵌套執行的方式，即對父查詢(xún)中的每一行，都執行一次子查詢(xún)，這樣子查詢(xún)會(huì )執行很多次。這種執行方式效率很低。

　　子查詢(xún)轉化為連接查詢(xún)優(yōu)點(diǎn)：

　　1、子查詢(xún)不用執行很多次

　　2、優(yōu)化器可以根據信息來(lái)選擇不同的方法和連接順序

　　3、子查詢(xún)的連接條件，過(guò)濾條件變成父查詢(xún)的篩選條件，以提高效率。

　　優(yōu)化：

　　子查詢(xún)合并，若多個(gè)子查詢(xún)，能合并的盡量合并。

　　子查詢(xún)展開(kāi)，即上拉變成多表查詢(xún)（時(shí)刻保證等價(jià)變化）

　　注意：

　　子查詢(xún)展開(kāi)只能展開(kāi)簡(jiǎn)單的查詢(xún)，若子查詢(xún)含有聚集函數、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、等價(jià)謂詞重寫(xiě)：

　　1、BETWEEEN AND改寫(xiě)為 >= 、<=之類(lèi)的。實(shí)測：十萬(wàn)條數據，重寫(xiě)前后時(shí)間，1.45s、0.06s

　　2、in轉換多個(gè)or。字段為索引時(shí)，兩個(gè)都能用到索引，or效率相對in好一點(diǎn)

　　3、name like ‘a(chǎn)bc%’改寫(xiě)成name>=’abc’ and name<’abd’;

　　注意：百萬(wàn)級數據測試，name沒(méi)有索引之前l(fā)ike比后一種查詢(xún)快；給字段增加索引后，后面的快一點(diǎn)點(diǎn)，相差不大，因為兩種方法在查詢(xún)的時(shí)候都用到了索引。

　　。。。。

回到頂部

8、條件化簡(jiǎn)與優(yōu)化

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

　　2、刪除不必要的括號，減少語(yǔ)法分許的or和and樹(shù)層，減少cpu消耗

　　3、常量傳遞。a=b and b=2轉換為 a=2 and b=2。盡量不使用變量a=b或a=@var

　　4、消除沒(méi)用的SQL條件

　　5、where等號右邊盡量不出現表達式計算；where中不要對字段進(jìn)行表達式計算、函數的使用

　　6、恒等變換、不等式變換。例：測試百萬(wàn)級數據a>b and b>10變?yōu)閍>b and a>10 and b>10優(yōu)化顯著(zhù)

回到頂部

9、外連接優(yōu)化

　　即將外連接轉為內連接

　　優(yōu)點(diǎn)：

　　1、優(yōu)化處理器處理外連接比內連接步驟多且耗時(shí)

　　2、外連接消除后，優(yōu)化器選擇多表連接順序有更多選擇，可以擇優(yōu)而選

　　3、可以將篩選條件最為嚴格的表作為外表（連接順序最前面，是多層循環(huán)體的外循環(huán)層），

　　可以減少不必要的I/O開(kāi)銷(xiāo)，能加快算法執行的速度。

　　on a.id=b.id與where a.id=b.id的差別，on則表進(jìn)行連接，where則進(jìn)行數據對比

　　注意：前提必須是結果為NULL決絕（即條件限制不要NULL數據行，語(yǔ)意上是內連接）

　　優(yōu)化原則：

　　精簡(jiǎn)查詢(xún)，連接消除，等效轉換，去除多余表對象連接

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

回到頂部

10、其他查詢(xún)優(yōu)化

　　1、以下將會(huì )造成放棄索引查詢(xún)，采用全文掃描

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

　　　　1.2、避免使用or

　　　　　　經(jīng)測試，并非是使用了or就一定不能使用索引，大多情況下是沒(méi)用到索引，但還有少數情況是用到的，因此具體情況具體分析。

　　　　　　類(lèi)似優(yōu)化：

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

　　　　　　=>

　　　　　　select * from tab name=’aa’

　　　　　　union all

　　　　　　select * from tab name=’bb’;

　　　　　　實(shí)測：

　　　　　　1、十萬(wàn)數據測試，沒(méi)任何索引的情況下，上面比下面的查詢(xún)速率快一倍。

　　　　　　2、三十萬(wàn)數據測試，aa與bb都是單獨索引情況下，下面的查詢(xún)速率比or快一點(diǎn)。

　　　　1.3、避免使用not in

　　　　　　not in一般不能使用索引；主鍵字段可以

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

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

　　　　　　解決：

　　　　　　　　1、若必須使用%前置，且數據長(cháng)度不大，例如URL，可將數據翻轉存入數據庫，再來(lái)查。LIKE REVERSE‘%.com’;

　　　　　　　　2、使用覆蓋索引

　　　　1.6、使用索引字段作為條件的時(shí)候，假若是復合索引，則應該使用索引最左邊前綴的字段名

　　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)

　　　　一百萬(wàn)條數據，篩選59417條數據用時(shí)6.65s、4.18s。沒(méi)做其他優(yōu)化，僅僅只是將exists替換in。

　　3、字段定義是字符串，查詢(xún)時(shí)沒(méi)帶引號，不會(huì )用索引，將會(huì )進(jìn)行全文掃描。