前言：影響數(shù)據(jù)庫效率的因素

主要可以分為兩類：

1、一類是服務(wù)器的CPU、內(nèi)存、網(wǎng)卡流量、磁盤IO等硬件因素；

2、另一類就是數(shù)據(jù)庫的設(shè)計(jì)和使用方法，例如數(shù)據(jù)庫引擎選擇、表結(jié)構(gòu)設(shè)計(jì)、索引的使用、查詢語句等。

對于第一類一般可以通過升級硬件來達(dá)到更好的效果，也有一些可以在服務(wù)器部署等方面進(jìn)行優(yōu)化，例如減少從服務(wù)器的數(shù)量從而減少數(shù)據(jù)同步帶來的網(wǎng)絡(luò)傳輸消耗（從服務(wù)器都要從主服務(wù)器上復(fù)制日志，所以從服務(wù)器越多，網(wǎng)絡(luò)流量越大），進(jìn)行分級緩存等。

而作為開發(fā)人員，本文的重點(diǎn)是從第二個(gè)方面進(jìn)行總結(jié)。

一、存儲(chǔ)引擎的選擇

MySQL常用的存儲(chǔ)引擎有三種，即Innodb、Myisam以及memory引擎。

1、Innodb引擎：

主要特性：具有較好的事務(wù)支持；支持行級鎖定；支持外鍵；能夠緩存索引和數(shù)據(jù)；所有的二級索引都會(huì)保存主鍵信息，不支持FULLTEXT類型的索引;不保存表的具體行數(shù); DELETE FROM table時(shí)，是一行一行刪除的，可以回滾。

適用場景：需要事物支持，高并發(fā)請求，

2、MyISAM引擎：

主要特性：不支持事務(wù)；表級鎖定；讀寫相互阻塞；只會(huì)緩存索引

適用場景：不需要事務(wù)支持；以讀為主的，數(shù)據(jù)修改相對比較少（讀寫相互阻塞）；并發(fā)低。

3、memory引擎:

是MySQL中一類特殊的存儲(chǔ)引擎，它將數(shù)據(jù)全部放在內(nèi)存中。這樣有利于數(shù)據(jù)的快速處理，提高整個(gè)表的效率。但如果內(nèi)存出現(xiàn)異常或者關(guān)機(jī)，所有數(shù)據(jù)都會(huì)消失。因此，基于MEMORY的表的生命周期很短，一般只用在一次性的表。

注：由于實(shí)際工作中均使用Innodb引擎，因此本文以下內(nèi)容均以Innodb引擎為前提

二、表結(jié)構(gòu)的設(shè)計(jì)

首先，三大范式：

第一范式：當(dāng)關(guān)系模式R的所有屬性都不能在分解為更基本的數(shù)據(jù)單位時(shí)，稱R是滿足第一范式的，簡記為1NF。滿足第一范式是關(guān)系模式規(guī)范化的最低要求，否則，將有很多基本操作在這樣的關(guān)系模式中實(shí)現(xiàn)不了。

第二范式：如果關(guān)系模式R滿足第一范式，并且R得所有非主屬性都完全依賴于R的每一個(gè)候選關(guān)鍵屬性，稱R滿足第二范式，簡記為2NF。

第三范式：設(shè)R是一個(gè)滿足第一范式條件的關(guān)系模式，X是R的任意屬性集，如果X非傳遞依賴于R的任意一個(gè)候選關(guān)鍵字，稱R滿足第三范式，簡記為3NF.

其次，數(shù)據(jù)字段的設(shè)計(jì)：

（1）使用盡可能簡單的數(shù)據(jù)類型，int要比varchar類型在mysql處理上更簡單，效率更高。

（2）對于數(shù)字類型：盡量不使用double（8字節(jié)），不僅僅只是存儲(chǔ)長度的問題，同時(shí)還會(huì)存在精確性的問題。而對于固定精度的小數(shù)，也不建議使用decimal，可以乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲(chǔ)，這樣可以大大節(jié)省存儲(chǔ)空間。對于整數(shù)的存儲(chǔ)，建議區(qū)分開 tinyint / int / bigint 的選擇，例如數(shù)字范圍在-128到127之內(nèi)的，可以使用tinyint（1字節(jié)），而不使用int（4字節(jié)）。

（3）對于字符類型，定長字段最好使用 char類型，不定長字段使用varchar，且要根據(jù)實(shí)際情況設(shè)定適當(dāng)?shù)淖畲箝L度，因?yàn)椴煌拈L度范圍，MySQL也會(huì)有不一樣的存儲(chǔ)處理。盡量少用text類型，非用不可時(shí)最好考慮分表，可以將text字段單獨(dú)拆分出一個(gè)表。

（4）對于時(shí)間類型：盡量使用 timestamp類型，因?yàn)槠鋬?chǔ)存空間只需要 datetime 類型的一半。對于只需要精確到某一天的數(shù)據(jù)類型，建議使用date類型，因?yàn)樗拇鎯?chǔ)空間只需要3個(gè)字節(jié)，比timestamp還少。

（5）盡可能的使用not null定義字段，因?yàn)閚ull類型比較特殊，MySQL需要有額外的處理。

（6）將字段多的表分割成多個(gè)表，比如將使用頻率低的字段分割出來組成新的表。

三、索引

索引可以說是針對查詢進(jìn)行優(yōu)化的最有效和常用的手段，特別是表中的數(shù)據(jù)量越來越大時(shí)，索引對于查詢性能可以提升好幾個(gè)數(shù)量級。

1、雖然索引可以大大加快數(shù)據(jù)的查詢速度，但是它也有缺點(diǎn)：

（1）創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，當(dāng)對表中的數(shù)據(jù)進(jìn)行增、刪、改時(shí)，索引也需要?jiǎng)討B(tài)的維護(hù)，因此降低了數(shù)據(jù)維護(hù)的速度。并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時(shí)間也會(huì)增加；

（2）索引也需要占空間

2、因此并不是說不管什么場景，創(chuàng)建索引一定比沒有好，具體什么時(shí)候適合建索引，可以參考如下：

（1）對更新非常頻繁而查詢比較簡單的表不建議使用索引；

（2）對數(shù)據(jù)量小的表不需要使用索引，因?yàn)閿?shù)據(jù)量小時(shí)直接全表掃碼的效率可能會(huì)更高；

（3）在值的取值較少的字段上不用建立索引，比如某標(biāo)志位字段的值只可能是0和1時(shí)，因?yàn)楫?dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)SQL查詢可能不會(huì)去利用索引；

（4）經(jīng)常與其他表進(jìn)行連接的表，在連接字段上最好建立索引，特別是被驅(qū)動(dòng)表；

（5）經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引；

（6）索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引；

（7）索引不只是用于where條件中，還會(huì)用于order by，group by，join的on字段。

（8）一個(gè)表不能有太多索引，一般一個(gè)表的索引數(shù)最好不要超過6個(gè)。

四、查詢優(yōu)化

首先，查詢語句調(diào)優(yōu)要會(huì)使用Explain查看執(zhí)行計(jì)劃，這對于性能調(diào)優(yōu)非常有用，Explain的使用方法很簡單，在查詢語句前面加上Explain即可，對于Explain的各個(gè)列的含義網(wǎng)上隨便搜一下有很多文章，在此就不贅述。

1、有些sql語句會(huì)導(dǎo)致無法使用索引，應(yīng)盡量避免：

（1）where 子句中對字段進(jìn)行表達(dá)式或函數(shù)操作時(shí)，無法使用索引。如：

SELECT * from dept_emp where id/2= 100

SELECT * from dept_emp where left(int_string, 4)="2000"

應(yīng)改為:

SELECT * from dept_emp where id= 100 *2

SELECT * from dept_emp where int_string like "2000%“

（2）有左模糊匹配時(shí)無法使用索引，應(yīng)盡量避免，例如

SELECT * from dept_emp where int_string like "%100%"

（3）查詢條件中存在某些隱式轉(zhuǎn)換時(shí)，無法使用索引：

字段類型為字符串，傳入?yún)?shù)為數(shù)字時(shí)無法使用索引

SELECT * from dept_emp where int_string=1000

但是字段類型為數(shù)字，傳入字符串時(shí)不受影響，仍然可以使用索引。

2、組合索引最左前綴原則：在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，還有其他一些限制條件不太好文字描述，直接看例子：

有組合索引包括四個(gè)列，依次為a, b, c, d。

select ...... where a="value1"? => 可以使用到索引

select ...... where b="value2"? => 無法使用到索引

select ...... where a="value1" and b="value2"? and d="value4"? => a和b列可以使用索引，d無法使用

select ...... where a="value1" and b>"value2"? and c="value4"? => a和b列可以使用索引，c無法使用

select ...... where b="value2" and a="value1"? => a和b列可以使用索引，影響組合索引使用的是索引中列的順序，而不是where語句中的字段順序。

3、任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

4、join聯(lián)表查詢時(shí)，不改變結(jié)果集的前提下查詢條件盡量詳細(xì)，這樣過濾的數(shù)據(jù)越多，循環(huán)次數(shù)越少。

5、前面有說到字段類型盡量設(shè)置為not null，null除了會(huì)占用額外空間外，還可能會(huì)帶來一些其他的隱藏問題，例如在組合唯一索引中，當(dāng)其中某個(gè)字段為null時(shí)，null值是可以重復(fù)，這可能會(huì)導(dǎo)致某些與預(yù)想不一致的結(jié)果。可以考慮將字段設(shè)置為不為null，然后默認(rèn)值為空字符串。

例：建立三個(gè)列組成的組合唯一索引：a, b, c，以下數(shù)據(jù)是允許的

a=1, b=2, c=null

a=1, b=2, c=null

6、批量數(shù)據(jù)處理與事務(wù)

InnoDB在大批量插入數(shù)據(jù)時(shí)，盡量自己控制事物而不要使用autocommit自動(dòng)提交，兩者的效率相差很大，因?yàn)樽詣?dòng)提交是在每插入一條數(shù)據(jù)的時(shí)候就提交一次（若使用spring的事務(wù)管理@Transaction則不用擔(dān)心這個(gè)問題，因?yàn)閟pring會(huì)將底層連接的自動(dòng)提交特性設(shè)置為false）

實(shí)際做了比較如下，10000條數(shù)據(jù)一個(gè)insert語句批量插入時(shí)：

沒有事務(wù)：2790ms

有事務(wù)：3447ms

10000條數(shù)據(jù)循環(huán)10000次insert插入時(shí)：

有事務(wù)：12649ms

沒有事務(wù)：332970ms

通過sql腳本導(dǎo)入數(shù)據(jù)時(shí)也是同樣的道理，最好是導(dǎo)出sql腳本時(shí)就是一個(gè)insert語句的形式，如果是循環(huán)insert的話，可以在第一條插入語句之前加上begin，最后一條后面加上commit，從而將其放入一個(gè)事務(wù)中只提交一次。

3000條循環(huán)插入：

HeidiSQL：

原來使用時(shí)間：133s

加上事務(wù)后時(shí)間：28s

命令行：

原來使用時(shí)間：91s

加上事務(wù)后時(shí)間：1s

五、實(shí)踐出真知

1、where 子句中使用!=、is null、is not null時(shí)無法使用索引？

前提：int_string和extra兩個(gè)列分別建立了索引，其中只有一行數(shù)據(jù)int_string值為"6666"，只有一行數(shù)據(jù)int_string值為null，只有一行數(shù)據(jù)extra 值不為 "abcdefg"，使用Explain查看以下查詢語句是否使用了索引，結(jié)果為：

select * from dept_emp where int_string != "6666"（未使用索引）

select * from dept_emp? where extra != "abcdefg"（使用了索引）

select * from dept_emp where int_string is null（使用了索引）

結(jié)論：并不是無法使用索引，而是因?yàn)槎鄶?shù)情況下這些寫法獲取到的結(jié)果集較大，而使用二級索引查詢時(shí)的回表操作是隨機(jī)IO（覆蓋索引除外，不需要回表效率很高），需要掃描的二級索引記錄數(shù)量越多，隨機(jī)IO就越多，當(dāng)這個(gè)數(shù)量達(dá)到了某個(gè)比例時(shí)，使用二級索引執(zhí)行查詢的成本也就超過了全表掃描的成本，因此優(yōu)化器放棄了使用索引。當(dāng)查詢結(jié)果集較小時(shí)，例如所有數(shù)據(jù)只有一個(gè)不為a，那么查詢條件為!=a時(shí)仍然時(shí)會(huì)使用索引的。所以MySQL中決定是否使用某個(gè)索引執(zhí)行查詢是由使用成本決定的，并不是是否在where子句中用了!=、is null、is not null這些條件。