如何提高M(jìn)ySQL數(shù)據(jù)庫的查詢效率懒浮，可以從兩個(gè)方面入手：使用索引和使用JOIN梭伐，本文主要講使用索引的一些原則和優(yōu)化方法读整。以及如何設(shè)計(jì)數(shù)據(jù)庫和SQL語句狂秘，來避免一些會(huì)導(dǎo)致性能差的操作。

關(guān)于索引的原理層面的東西假残，本文暫不細(xì)講缭贡。

本篇包括：

• 索引類型
• 使用索引的原則
• 索引優(yōu)化的方式
• SQL優(yōu)化方式
• 單表的使用優(yōu)化

索引類型

在MySQL中，索引分為量大類型：聚簇索引 和 非聚簇索引辉懒。
聚簇索引是按照數(shù)據(jù)存放的物理位置為順序的阳惹，而非聚簇索引則不同；聚簇索引可以提高多行的檢索速度眶俩，而非聚簇索引對單行的檢索速度很快穆端。
（PS：聚簇和非聚簇索引的原理部分也很重要，可以去參考其他文章）

在這兩大索引類型下仿便，又細(xì)分為四個(gè)類型：
1）普通索引：最基本的索引体啰，沒有任何限制，我們大多數(shù)情況下使用到的索引嗽仪。
2）唯一索引：與普通索引不同的是荒勇，唯一索引的列值必須唯一，但是允許為空值闻坚。
3）全文索引：僅適用于MyISAM引擎的數(shù)據(jù)表沽翔；作用于CHAR，VARCHAT窿凤，TEXT數(shù)據(jù)的列仅偎。
4）聯(lián)合索引：將幾個(gè)列作為一條索引進(jìn)行檢索，適用最左匹配原則雳殊。

建立索引的原則

1）最左匹配原則橘沥。
這是非常，非常夯秃，非常重要的原則：MySQL使用聯(lián)合索引時(shí)座咆，會(huì)從左邊一直向右匹配，直到遇到范圍查詢（>, <, between, like 操作）就停止匹配仓洼。

比如：where a=1 and b=2 and c>3 and d=4介陶，如果建立的是(a,b,c,d)的聯(lián)合索引，那么 d 是用不到索引的色建。而如果建立成(a,b,d,c)那么d的索引就會(huì)用到了哺呜。因?yàn)镸ySQL優(yōu)化器將d=4的條件前置到了c>3前面。

MySQL創(chuàng)建聯(lián)合索引的規(guī)則是箕戳，首先會(huì)對聯(lián)合索引最左邊的字段進(jìn)行數(shù)據(jù)排序某残，在第一個(gè)字段的基礎(chǔ)上，對第二個(gè)字段進(jìn)行排序漂羊。按上面的例子來說驾锰，就相當(dāng)于實(shí)現(xiàn)了 order by a,b,c,d 的規(guī)則。

還是拿上面例子來說走越，a索引是天然有序的椭豫。當(dāng)滿足了a=1 這個(gè)條件之后，b就有序了旨指，而確定了a=1 and b=2 以后赏酥，第三個(gè)索引也是有序的了。

所以谆构，mysql索引規(guī)則中要求裸扶，要想使用聯(lián)合索引的第二個(gè)索引，必須首先使用第一個(gè)索引（而且必須是等值匹配）搬素，這也是最左匹配原則的根本原因呵晨。

2）= 和 in 可以亂序魏保。
因?yàn)镸ySQL優(yōu)化器會(huì)判斷糾正這條SQL語句，并使用效率最高的方式利用索引摸屠，生成執(zhí)行計(jì)劃谓罗。

3）盡量選擇區(qū)分度高的列作為索引。
區(qū)分度的公式是 count(distinct col) / count(*)季二，表示字段的不重復(fù)比率檩咱，結(jié)果是 [0,1] 的范圍。

比率越大胯舷，我們掃描的數(shù)據(jù)越少刻蚯，唯一索引的區(qū)分度是1。而一些狀態(tài)位桑嘶，性別字段炊汹，區(qū)分度就很低，當(dāng)數(shù)據(jù)量很大時(shí)不翩，區(qū)分度就無限趨近于0了兵扬。也就是說，即使使用了這些字段做索引口蝠，那匹配出來的數(shù)據(jù)量也會(huì)很大器钟，幾乎沒有作用。

那這個(gè)比率一般是多少呢妙蔗？根據(jù)使用場景做具體判斷傲霸，不過一般需要JOIN的字段我們要求在0.1以上，也就是平均1條數(shù)據(jù)眉反，掃描10條記錄昙啄。
</br>

4）索引不要參與計(jì)算
比如FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引。
原因很簡單寸五，B+樹中存儲(chǔ)的都是數(shù)據(jù)表中的字段值梳凛，但是進(jìn)行檢索時(shí)，需要把所有數(shù)據(jù)進(jìn)行計(jì)算以后才能進(jìn)行比較梳杏。顯然代價(jià)太大韧拒。

5）盡可能擴(kuò)展索引，而不是新建索引
比如表中已經(jīng)有了 a 的索引十性，現(xiàn)在要使用 (a,b) 索引叛溢，那么只要將 a 索引 改為 (a,b) 索引即可，不需要新加一個(gè)索引劲适。
而且執(zhí)行SQL時(shí)楷掉，MySQL只能使用一個(gè)索引，會(huì)從多個(gè)索引中選擇一個(gè)限制最為嚴(yán)格的索引霞势。所以說烹植，索引的數(shù)量不需要太多斑鸦。

索引的優(yōu)化方法

1）索引不會(huì)包含有NULL值的列
只要列中包含有NULL值，都將不會(huì)被包含在索引中草雕，組合索引中只要有一列有NULL值鄙才，那么這一列對于此條組合索引就是無效的。所以我們在數(shù)據(jù)庫設(shè)計(jì)時(shí)促绵，不要讓索引字段的默認(rèn)值為NULL。

例如：select id from table where num is NULL 可以在num上設(shè)置默認(rèn)值0嘴纺，確保列中沒有NULL值败晴，這樣查詢可以變?yōu)椋?code>select id from table where num=0

2）索引列排序
MySQL查詢只使用一個(gè)索引，因此如果WHERE子句中已經(jīng)使用了索引的話栽渴，那么ORDER BY中的列是不會(huì)使用索引的尖坤。因此數(shù)據(jù)庫默認(rèn)排序可以符合要求的情況下，不要使用排序操作闲擦；盡量不要包含多個(gè)列的排序慢味，如果需要，最好給這些列也創(chuàng)建組合索引墅冷。

3）使用短索引
如果一個(gè)索引列纯路，只在前10~20個(gè)字符是唯一的，那么就不要堆全部字段做索引寞忿。

4）盡可能使用varchar代替char
因?yàn)関archar是變長字符串驰唬，存儲(chǔ)空間相對較小，節(jié)省存儲(chǔ)空間腔彰。對于查詢來說叫编，在一個(gè)相對較小的字符串上查詢效率也會(huì)較高。

5）盡量使用數(shù)字字段
若只含數(shù)值信息的字段不要設(shè)置為字符型霹抛，這會(huì)降低查詢和連接的性能搓逾，并會(huì)增加內(nèi)存開銷。
這是因?yàn)镸ySQL引擎在處理查詢和連接時(shí)杯拐，需要從左到右逐個(gè)比對字符串的每個(gè)字符霞篡，而對于數(shù)字，只要比較一次就夠了藕施。

6）LIKE語句操作
一般情況下寇损，不建議使用LIKE操作；如果非使用不可裳食，如何使用也是一個(gè)研究的課題矛市。
例如：LIKE "%aaaaa%"不會(huì)使用索引，但是LIKE "aaa%"卻可以使用索引诲祸。

7）不要在索引列上進(jìn)行運(yùn)算
在建立索引的原則中浊吏，提到了索引列不能進(jìn)行運(yùn)算而昨，這里就不再贅述了。

SQL 語句的優(yōu)化

1）盡量避免在 where 子句中使用 > < != 操作符找田，否則數(shù)據(jù)庫引擎將放棄索引使用全表掃描歌憨。

2）盡量避免在 where 子句中使用 or 條件，否則數(shù)據(jù)庫引擎將放棄索引進(jìn)行全表掃描墩衙。（可以使用 union 來代替or連接查詢結(jié)果）
舉栗： select id from t where num=10 or num=20 可以這樣查詢： select id from t where num=10 union all select id from t where num=20

3）慎用 in 和 not in务嫡。對于一組連續(xù)的數(shù)據(jù)，可以使用 between代替漆改⌒牧澹或者可以考慮使用 exists 代替 in

4）like %aaa% 語句會(huì)導(dǎo)致全表掃描。

5）盡量避免在 where 子句中進(jìn)行表達(dá)式操作挫剑，這將導(dǎo)致放棄索引使用全表掃描去扣。
舉栗：select id from t where num/2=100 應(yīng)改為: select id from t where num=200

6）盡量避免在 where 子句中對字段進(jìn)行函數(shù)操作，可以在 = 右邊進(jìn)行結(jié)果匹配樊破。
舉栗：select id from t where substring(name,1,3)=’abc’ 應(yīng)改為：select id from t where name like ‘a(chǎn)bc%’

7）任何地方都不要使用 select * from table 愉棱，查詢應(yīng)該指定具體的字段來代替 *，不要返回用不到的字段哲戚。

8）應(yīng)盡量避免向客戶端返回大數(shù)據(jù)量奔滑。
如果結(jié)果條數(shù)過多，考慮在業(yè)務(wù)端使用分頁請求惫恼。
如果有大字段(BLOB)档押，則最好在業(yè)務(wù)端提供剔除大字段的接口請求（例如 selectById 和 selectByIdwithBlob）。
（好吧這一條不屬于SQL優(yōu)化祈纯，但是也需要牢記）

關(guān)于數(shù)據(jù)庫表的使用優(yōu)化

1）字符串字段使用 varchar 而不要使用char令宿。

2）單表字段不要太多，建議在20個(gè)字段以內(nèi)腕窥。原因是存儲(chǔ)引擎的API在工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎層之間通過行緩沖方式拷貝數(shù)據(jù)粒没，然后在服務(wù)器層將緩沖內(nèi)容轉(zhuǎn)碼成各個(gè)列，這個(gè)轉(zhuǎn)換過程代價(jià)非常高簇爆。如果字段太多癞松，建議拆分成多個(gè)表，但注意不要過度設(shè)計(jì)入蛆。

3）在索引列上盡量設(shè)置值為 NOT NULL响蓉。因?yàn)樵谒饕猩线M(jìn)行NULL判斷將會(huì)導(dǎo)致放棄使用索引。

4）通常來講哨毁，沒必要使用 decimal 類型枫甲。即使是在存儲(chǔ)財(cái)務(wù)數(shù)據(jù)時(shí)也可以使用int 或者 bigint 類型，只要放大一定的備注就可以消除誤差。而精確計(jì)算的代價(jià)太高想幻。

5）時(shí)間使用DATETIME粱栖，不建議使用TIMESTAMP。有的地方建議使用TIMESTAMP脏毯，因?yàn)樗徽加?個(gè)字節(jié)闹究，DATETIME占用8個(gè)字節(jié)。但是TIMESTAMP只能表示1970-2038年食店。而且會(huì)因?yàn)闀r(shí)區(qū)而不同渣淤。

（如果有什么錯(cuò)誤或者建議，歡迎留言指出）
（本文內(nèi)容是對各個(gè)知識(shí)點(diǎn)的轉(zhuǎn)載整理吉嫩，用于個(gè)人技術(shù)沉淀砂代，以及大家學(xué)習(xí)交流用）

參考資料：
在一個(gè)千萬級數(shù)據(jù)庫查詢中，如何提高查詢效率
Mysql學(xué)習(xí)之索引
MySQL最左匹配原則的理解
SQL優(yōu)化-索引——掘金
SQL優(yōu)化——簡書
SQL優(yōu)化核心思想