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

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

1、簡(jiǎn)介

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

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

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

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

回到頂部

2、截取SQL語(yǔ)句

     1、全面查詢?nèi)罩?/p>

     2、慢查詢?nèi)罩?/p>

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

     4、進(jìn)程列表

　　SHOW FULL PROCESSLIST;

　　。。。

回到頂部

3、查詢優(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)養(yǎng)的東西。下面才是真正的干貨哈。

回到頂部

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

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

　　2、刪除不用或者重復(fù)的索引

　　3、查詢重寫，等價(jià)轉(zhuǎn)換（謂詞、子查詢、連接查詢）

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

　　5、整合重復(fù)執(zhí)行的語(yǔ)句

　　6、緩存查詢結(jié)果

回到頂部

5、索引優(yōu)化

回到頂部

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

　　　　1、保持?jǐn)?shù)據(jù)的完整性

　　　　2、提高數(shù)據(jù)的查詢性能

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

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

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

回到頂部

　　5.2、索引缺點(diǎn)

　　　　1、索引需要占用一定的存儲(chǔ)空間

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

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

回到頂部

　　5.3、索引選擇

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

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

　　　　3、查詢?nèi)〕鰯?shù)據(jù)大于20%，將采用全文掃描，不用索引

回到頂部

　　5.4、索引細(xì)究

　　　　資料查詢：

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

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

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

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

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

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

　　　　因?yàn)椴樵兊牧惺撬饕?，所以不需要讀取行，只需要讀取列字段數(shù)據(jù)就可以了?！纠缒憧匆槐緯?，需要找某一內(nèi)容，剛好那內(nèi)容出現(xiàn)在目錄中，那就不用一頁(yè)頁(yè)翻了，直接在目錄中定位到第幾頁(yè)查找】

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

　　　　索引字段，在select中出現(xiàn)就是了。

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

　　　　查看：

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

　　　　結(jié)論：

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

　　　　針對(duì)以上操作符實(shí)測(cè)結(jié)果：

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

　　　　2、以select 字段a from tab where 字段a《以上操作符》形式測(cè)試，結(jié)果依然可以使用索引查詢。【采用了覆蓋索引】

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

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

　　　　2、復(fù)合索引使用

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

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

回到頂部

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

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

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

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

　　1、子查詢不用執(zhí)行很多次

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

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

　　優(yōu)化：

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

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

　　注意：

　　子查詢展開只能展開簡(jiǎn)單的查詢，若子查詢含有聚集函數(shù)、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合并，修改相應(yīng)參數(shù)

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

　　3、修改相應(yīng)的謂詞（in改=）

回到頂部

7、等價(jià)謂詞重寫：

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

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

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

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

　　。。。。

回到頂部

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

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

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

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

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

　　5、where等號(hào)右邊盡量不出現(xiàn)表達(dá)式計(jì)算；where中不要對(duì)字段進(jìn)行表達(dá)式計(jì)算、函數(shù)的使用

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

回到頂部

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

　　即將外連接轉(zhuǎn)為內(nèi)連接

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

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

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

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

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

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

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

　　優(yōu)化原則：

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

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

回到頂部

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

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

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

　　　　1.2、避免使用or

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

　　　　　　類似優(yōu)化：

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

　　　　　　=>

　　　　　　select * from tab name=’aa’

　　　　　　union all

　　　　　　select * from tab name=’bb’;

　　　　　　實(shí)測(cè)：

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

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

　　　　1.3、避免使用not in

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

　　　　1.4、where中盡量避免使用對(duì)null的判斷

　　　　1.5、like不能前置百分號(hào) like ‘%.com’

　　　　　　解決：

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

　　　　　　　　2、使用覆蓋索引

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

　　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)條數(shù)據(jù)，篩選59417條數(shù)據(jù)用時(shí)6.65s、4.18s。沒(méi)做其他優(yōu)化，僅僅只是將exists替換in。

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

到此，相信大家對(duì)“MySQL的查詢優(yōu)化方法”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！