本文由碼農網 – 吳極心原創，轉載請看清文末的轉載要求，歡迎參與我們的付費投稿計劃！

資料庫優化有很多可以講，按照支撐的資料量來分可以分為兩個階段：單機資料庫和分庫分表，前者一般可以支撐500W或者10G以內的資料，超過這個值則需要考慮分庫分表。另外，一般大企業面試往往會從單機資料庫問起，一步一步問到分庫分表，中間會穿插很多資料庫優化的問題。本文試圖描述單機資料庫優化的一些實踐，資料庫基於mysql，如有不合理的地方，歡迎指正。

1、表結構優化

在開始做一個應用的時候，資料庫的表結構設計往往會影響應用後期的效能，特別是使用者量上來了以後的效能。因此，表結構優化是一個很重要的步驟。

1.1、字符集

一般來說盡量選擇UTF-8，雖然在存中午的時候GBK比UTF-8使用的儲存空間少，但是UTF-8相容各國語言，其實我們不必為了這點儲存空間而犧牲了擴充套件性。事實上，後期如果要從GBK轉為UTF-8所要付出的代價是很高的，需要進行資料遷移，而儲存空間完全可以用花錢擴充硬碟來解決。

1.2、主鍵

在使用mysql的innodb的時候，innodb的底層儲存模型是B+樹，它使用主鍵作為聚簇索引，使用插入的資料作為葉子節點，通過主鍵可以很快找到葉子節點，從而快速獲取記錄。因此在設計表的時候需要增加一個主鍵，而且最好要自增。因為自增主鍵可以讓插入的資料按主鍵順序插入到底層的B+樹的葉子節點中，由於是按序的，這種插入幾乎不需要去移動已有的其它資料，所以插入效率很高。如果主鍵不是自增的，那麼每次主鍵的值近似隨機，這時候就有可能需要移動大量資料來保證B+樹的特性，增加了不必要的開銷。

1.3、欄位

1.3.1、建了索引的欄位必須加上not null約束，並且設定default值

1.3.2、不建議使用float、double來存小數，防止精度損失，建議使用decimal

1.3.3、不建議使用Text/blob來儲存大量資料，因為對大文字的讀寫會造成比較大的I/O開銷，同時佔用mysql的快取，高併發下會極大的降低資料庫的吞吐量，建議將大文字資料儲存在專門的檔案儲存系統中，mysql中只儲存這個檔案的訪問地址，比如部落格文章可以儲存在檔案中，mysql中只儲存檔案的相對地址。

1.3.4、varchar型別長度建議不要超過8K。

1.3.5、時間型別建議使用Datetime，不要使用timestamp，雖然Datetime佔用8個位元組，而timestamp只佔用4個位元組，但是後者要保證非空，而且後者是對時區敏感的。

1.3.6、建議表中增加gmt_create和gmt_modified兩個欄位，用來記錄資料建立的修改時間。這兩個欄位建立的原因是方便查問題。

1.4、索引建立

1.4.1、這個階段由於對業務並不瞭解，所以儘量不要盲目加索引，只為一些一定會用到索引的欄位加普通索引。

1.4.2、建立innodb單列索引的長度不要超過767bytes，如果超過會用前255bytes作為字首索引

1.4.3、建立innodb組合索引的各列索引長度不要超過767bytes，一共加起來不要超過3072bytes

2、SQL優化

一般來說sql就那麼幾種：基本的增刪改查，分頁查詢，範圍查詢，模糊搜尋，多表連線

2.1、基本查詢

一般查詢需要走索引，如果沒有索引建議修改查詢，把有索引的那個欄位加上，如果由於業務場景沒法使用這個欄位，那麼需要看這個查詢呼叫量大不大，如果大，比如每天呼叫10W+，這就需要新增索引，如果不大，比如每天呼叫100+，則可以考慮保持原樣。另外，select * 儘量少用，用到什麼欄位就在sql語句中加什麼，不必要的欄位就別查了，浪費I/O和記憶體空間。

2.2、高效分頁

limit m,n其實質就是先執行limit m+n，然後從第m行取n行，這樣當limit翻頁越往後翻m越大，效能越低。比如

select * from A limit 100000,10，這種sql語句的效能是很差的，建議改成下面的版本:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

2.3、範圍查詢

範圍查詢包括between、大於、小於以及in。Mysql中的in查詢的條件有數量的限制，若數量較小可以走索引查詢，若數量較大，就成了全表掃描了。而between、大於、小於等，這些查詢不會走索引，所以儘量放在走索引的查詢條件之後。

2.4、模糊查詢like

使用 like %name%這樣的語句是不會走索引的，相當於全表掃描，資料量小的時候不會有太大的問題，資料量大了以後效能會下降的很厲害，建議資料量大了以後使用搜尋引擎來代替這種模糊搜尋，實在不行也要在模糊查詢前加個能走索引的條件。

2.5、多表連線

子查詢和join都可以實現在多張表之間取資料，但是子查詢效能較差，建議將子查詢改成join。對於mysql的join，它用的是Nested Loop Join演算法，也就是通過前一個表查詢的結果集去後一個表中查詢，比如前一個表的結果集是100條資料，後一個表有10W資料，那麼就需要在100*10W的資料集合中去過濾得到最終的結果集。因此，儘量用小結果集的表去和大表做join，同時在join的欄位上建立索引，如果建不了索引，就需要設定足夠大的join buffer size。如果以上的技巧都無法解決join所帶來的效能下降的問題，那乾脆就別用join了，將一次join查詢拆分成兩次簡單查詢。另外，多表連線儘量不要超過三張表，超過三張表一般來說效能會很差，建議拆分sql。

 

3、資料庫連線池優化

資料庫連線池本質上是一種快取，它是一種抗高併發的手段。資料庫連線池優化主要是對引數進行優化，一般我們使用DBCP連線池，它的具體引數如下：

3.1  initialSize

初始連線數，這裡的初始指的是第一次getConnection的時候，而不是應用啟動的時候。初始值可以設定為併發量的歷史平均值

3.2、minIdle

最小保留的空閒連線數。DBCP會在後臺開啟一個回收空閒連線的執行緒，當該執行緒進行空閒連線回收的時候，會保留minIdle個連線數。一般設定為5，併發量實在很小可以設定為1.

3.3、maxIdle

最大保留的空閒連線數，按照業務併發高峰設定。比如併發高峰為20，那麼當高峰過去後，這些連線不會馬上被回收，如果過一小段時間又來一個高峰，那麼連線池就可以複用這些空閒連線而不需要頻繁建立和關閉連線。

3.4、maxActive

最大活躍連線數，按照可以接受的併發極值設定。比如單機併發量可接受的極值是100，那麼這個maxActive設定成100後，就只能同時為100個請求服務，多餘的請求會在最大等待時間之後被拋棄。這個值必須設定，可以防止惡意的併發攻擊，保護資料庫。

3.5、maxWait

獲取連線的最大等待時間，建議設定的短一點，比如3s，這樣可以讓請求快速失敗，因為一個請求在等待獲取連線的時候，執行緒是不可以被釋放的，而單機的執行緒併發量是有限的，如果這個時間設定的過長，比如網上建議的60s，那麼這個執行緒在這60s內是無法被釋放的，只要這種請求一多，應用的可用執行緒就少了，服務就變得不可用了。

3.6、minEvictableIdleTimeMillis

連線保持空閒而不被回收的時間，預設30分鐘。

3.7、validationQuery

用於檢測連線是否有效的sql語句，一般是一條簡單的sql，建議設定

3.8、testOnBorrow

申請連線的時候對連線進行檢測，不建議開啟，嚴重影響效能

3.9、testOnReturn

歸還連線的時候對連線進行檢測，不建議開啟，嚴重影響效能

3.10、testWhileIdle

開啟了以後，後臺清理連線的執行緒會沒隔一段時間對空閒連線進行validateObject，如果連線失效則會進行清除，不影響效能，建議開啟

3.11、numTestsPerEvictionRun

代表每次檢查連結的數量，建議設定和maxActive一樣大，這樣每次可以有效檢查所有的連結。

3.12、預熱連線池

對於連線池，建議在啟動應用的時候進行預熱，在還未對外提供訪問之前進行簡單的sql查詢，讓連線池充滿必要的連線數。

 

4、索引優化

當資料量增加到一定程度後，靠sql優化已經無法提升效能了，這時候就需要祭出大招：索引。索引有三級，一般來說掌握這三級就足夠了，另外，對於建立索引的欄位，需要考慮其選擇性。

4.1、一級索引

在where後面的條件上建立索引，單列可以建立普通索引，多列則建立組合索引。組合索引需要注意最左字首原則。

4.2、二級索引

如果有被order by或者group by用到的欄位，則可以考慮在這個欄位上建索引，這樣一來，由於索引天然有序，可以避免order by以及group by所帶來的排序，從而提高效能。

4.3、三級索引

如果上面兩招還不行，那麼就把所查詢的欄位也加上索引，這時候就形成了所謂的索引覆蓋，這樣做可以減少一次I/O操作，因為mysql在查詢資料的時候，是先查主鍵索引，然後根據主鍵索引去查普通索引，然後根據普通索引去查相對應的記錄。如果我們所需要的記錄在普通索引裡都有，那就不需要第三步了。當然，這種建索引的方式比較極端，不適合一般場景。

4.4、索引的選擇性

在建立索引的時候，儘量在選擇性高的欄位上建立。什麼是選擇性高呢？所謂選擇性高就是通過這個欄位查出來的資料量少，比如按照名字查一個人的資訊，查出來的資料量一般會很少，而按照性別查則可能會把資料庫一半的資料都查出來，所以，名字是一個選擇性高的欄位，而性別是個選擇性低的欄位。

5、歷史資料歸檔

當資料量到了一年增加500W條的時候，索引也無能為力，這時候一般的思路都是考慮分庫分表。如果業務沒有爆發式增長，但是資料的確在緩慢增加，則可以不考慮分庫分表這種複雜的技術手段，而是進行歷史資料歸檔。我們針對生命週期已經完結的歷史資料，比如6個月之前的資料，進行歸檔。我們可以使用quartz的排程任務在凌晨定時將6個月之前的資料查出來，然後存入遠端的hbase伺服器。當然，我們也需要提供歷史資料的查詢介面，以備不時之需。

關於作者

本文作者吳極心，目前就職於阿里巴巴，精通java，高併發，架構設計