系統優化怎麼做-資料庫優化

來自專欄聊聊系統優化

前言

目前大部分公司的資料庫都是MySQL，雖然現在NoSQL資料庫比如mongo, hbase越來越流行了，但傳統的MySQL依然是業界用得最多。本文是以MySQL為例。

資料庫

資料庫是唯一在應用系統中的單點資源，對於資料庫的資源的使用要特別小心。有如下幾點注意點

1. 資料庫作為數據存儲的地方，不應該把寶貴的資源用於數據的轉換或統計操作，SQL中不使用一些字元轉換等操作。
2. 資料庫連接資源寶貴，外圍系統按需繼續分配使用
3. 資料庫不怕高qps的小查詢，但害怕慢查詢，因此請消滅慢查詢。
4. 索引不是越多越好，維護索引資源也耗費資料庫運算資源
5. 資料庫運算能力寶貴程度大於存儲
6. 如果是主從架構，主機器與從機器的網路帶寬及穩定性要保證
7. 不在資料庫中存儲圖片、文件等大數據
8. 禁止在線上做資料庫壓力測試
9. 禁止從測試、開發環境直連線上資料庫
10. 不在業務高峰期批量更新、查詢資料庫
11. 不在MySQL資料庫中存放業務邏輯,寫儲存過程及觸發器等
12. 禁止在主庫上執行後台管理和統計報表類的功能查詢，都放到從庫

硬體

1. 磁碟

MySQL每秒鐘都在進行大量、複雜的查詢操作，對磁碟的讀寫量可想而知。所以，通常認為磁碟I/O是制約MySQL性能的最大因素之一，推薦使用RAID-0+1磁碟陣列。

2.CPU

推薦使用至少4U以上的伺服器來專門做資料庫伺服器，基本上是越多越好

3.內存

伺服器內存建議不要小於4GB。基本上是越大越好

系統配置

MySQL配置在my.conf,影響新能的幾個關鍵配置屬性

• 使用INNODB存儲引擎 5.5以後的默認引擘，支持事務，行級鎖，更好的恢復性，高並發下性能更好，對多核，大內存，ssd等硬體支持更好。
• 表字符集使用utf8mb4 使用utf8mb4字符集，如果是漢字，佔3個位元組，但ASCII碼字元還是1個位元組；統一，不會有轉換產生亂碼風險，並能解決符號表情亂碼問題；
• max_connections 最大連接（用戶）數
• innodb_log_file_size 在高寫入負載尤其是大數據集的情況下很重要。這個值越大則性能相對越高，但是要注意到可能會增加恢復時間。設置為 64-512MB，根據伺服器大小而異
• Innodb_buffer_pool_pages_data 分配出去， 正在被使用頁的數量
• Innodb_buffer_pool_pages_total 緩衝區總共的頁面數
• Innodb_page_size 編譯的InnoDB頁大小(默認16KB)

調優參考計算方法：

val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%

val > 95% 則考慮增大 innodb_buffer_pool_size， 建議使用物理內存的75%

val < 95% 則考慮減小 innodb_buffer_pool_size， 建議設置為：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

資料庫表結構

表結構的設計目標除了滿足業務以外，盡量減少代碼實現上的聯表查詢操作，因此在設計上可以適當有一些冗餘欄位的設計，減少資料庫IO次數。

現在很流行的ElasticSearch等大數據存儲寬表的概念也是這種思想的體現

1. 盡量避免使用分區表 MySQL的分區表實際性能不是很好。
2. 拆分大欄位和訪問頻率低的欄位，分離冷熱數據
3. 採用合理的分庫分表策略，推薦使用HASH進行分表，表名後綴使用十進位數，下標從0開始首次分表盡量多的分，避免二次分表，二次分表的難度和成本較高
4. 單表欄位數控制在20個以內
5. 一條完整的建表語句中應包含必要的欄位、主鍵、合理的索引（綜合代碼中所有的條件語句創建合理的索引，主鍵必須要有

索引設計

索引是一把雙刃劍，它可以提高查詢效率但也會降低插入和更新的速度並佔用磁碟空間。

1. 單張表中索引數量不超過5個
2. 單個索引中的欄位數不超過5個
3. 對字元串使用前綴索引，前綴索引長度不超過10個字元；如果有一個CHAR(200)列，如果在前10個字元內，多數值是惟一的，那麼就不要對整個列進行索引。對前10個字元進行索引能夠節省大量索引空間，也可能會使查詢更快
4. 表必須有主鍵，不使用UUID、MD5、HASH作為主鍵，盡量不選擇字元串列作為主鍵；主鍵建議選擇自增id
5. 創建複合索引時區分度較大的欄位放在最前面；不在低區分度的欄位上創建索引，如「性別」
6. 避免冗餘或重複索引
7. 合理創建聯合索引（避免冗餘），index(a、b、c) 相當於index(a)、index(a、b)、index(a、、b、c)
8. 索引不是越多越好，按實際需要進行創建
9. 每個額外的索引都要佔用額外的磁碟空間，並降低寫操作的性能
10. 不在索引列進行數學運算和函數運算；
11. 盡量不要使用外鍵 外鍵用來保護參照完整性，可在業務端實現，對父表和子表的操作會相互影響，降低可用性；
12. 不使用%前導的查詢，如like「%xxx」，不使用反向查詢，如not in / not like 無法使用索引，導致全表掃描 全表掃描導致buffer pool利用降低

欄位設計

1. 儘可能不要使用TEXT、BLOB類型。刪除這種值會在數據表中留下很大的"空洞"，可以考慮把BLOB或TEXT列分離到單獨的表中
2. 用DECIMAL代替FLOAT和DOUBLE存儲精確浮點數。浮點數相對於定點數的優點是在長度一定的情況下，浮點數能夠表示更大的數據範圍；浮點數的缺點是會引起精度問題
3. 將字元轉化為數字
4. 使用TINYINT來代替ENUM類型
5. 欄位長度盡量按實際需要進行分配，不要隨意分配一個很大的容量 VARCHAR(N)，N表示的是字元數不是位元組數，比如VARCHAR(255)，可以最大可存儲255個漢字，需要根據實際的寬度來選擇N。VARCHAR(N)，N儘可能小，因為MySQL一個表中所有的VARCHAR欄位最大長度是65535個位元組，進行排序和創建臨時表一類的內存操作時，會使用N的長度申請內存；
6. 如果可能, 所有欄位均定義為not null
7. 使用UNSIGNED存儲非負整數 同樣的位元組數，存儲的數值範圍更大。如tinyint有符號為-128-127，無符號為0-255
8. 使用TIMESTAMP存儲時間. 因為TIMESTAMP使用4位元組，DATETIME使用8個位元組,同時TIMESTAMP具有自動賦值以及自動更新的特性.
9. 使用INT UNSIGNED存儲IPV4
10. 使用VARBINARY存儲大小寫敏感的變長字元串
11. 禁止在資料庫中存儲明文密碼

思考題

1. 資料庫有哪些高可用措施？
2. 如果資料庫掛了,怎麼保證核心業務是可用的

推薦閱讀：