共計 4265 個字符，預計需要花費 11 分鐘才能閱讀完成。

MySQL 單機數據庫優化的實踐有哪些，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

數據庫優化有很多可以講，按照支撐的數據量來分可以分為兩個階段：單機數據庫和分庫分表，前者一般可以支撐 500W 或者 10G 以內的數據，超過這個值則需要考慮分庫分表。另外，一般大企業面試往往會從單機數據庫問起，一步一步問到分庫分表，中間會穿插很多數據庫優化的問題。丸趣 TV 小編試圖描述單機數據庫優化的一些實踐，數據庫基于 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 服務器。當然，我們也需要提供歷史數據的查詢接口，以備不時之需。

看完上述內容，你們掌握 MySQL 單機數據庫優化的實踐有哪些的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！