前言

目前大部分公司的數(shù)據(jù)庫都是MySQL，雖然現(xiàn)在NoSQL數(shù)據(jù)庫比如mongo, hbase越來越流行了，但傳統(tǒng)的MySQL依然是業(yè)界用得最多。本文是以MySQL為例。

數(shù)據(jù)庫

數(shù)據(jù)庫是唯一在應(yīng)用系統(tǒng)中的單點資源，對于數(shù)據(jù)庫的資源的使用要特別小心。有如下幾點注意點

數(shù)據(jù)庫作為數(shù)據(jù)存儲的地方，不應(yīng)該把寶貴的資源用于數(shù)據(jù)的轉(zhuǎn)換或統(tǒng)計操作，SQL中不使用一些字符轉(zhuǎn)換等操作。

數(shù)據(jù)庫連接資源寶貴，外圍系統(tǒng)按需繼續(xù)分配使用

數(shù)據(jù)庫不怕高qps的小查詢，但害怕慢查詢，因此請消滅慢查詢。

索引不是越多越好，維護(hù)索引資源也耗費數(shù)據(jù)庫運算資源

數(shù)據(jù)庫運算能力寶貴程度大于存儲

如果是主從架構(gòu)，主機器與從機器的網(wǎng)絡(luò)帶寬及穩(wěn)定性要保證

不在數(shù)據(jù)庫中存儲圖片、文件等大數(shù)據(jù)

禁止在線上做數(shù)據(jù)庫壓力測試

禁止從測試、開發(fā)環(huán)境直連線上數(shù)據(jù)庫

不在業(yè)務(wù)高峰期批量更新、查詢數(shù)據(jù)庫

不在MySQL數(shù)據(jù)庫中存放業(yè)務(wù)邏輯,寫儲存過程及觸發(fā)器等

禁止在主庫上執(zhí)行后臺管理和統(tǒng)計報表類的功能查詢，都放到從庫

硬件

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

CPU
推薦使用至少4U以上的服務(wù)器來專門做數(shù)據(jù)庫服務(wù)器，基本上是越多越好

內(nèi)存
服務(wù)器內(nèi)存建議不要小于4GB。基本上是越大越好
系統(tǒng)配置
MySQL配置在my.conf,影響新能的幾個關(guān)鍵配置屬性

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

調(diào)優(yōu)參考計算方法：
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 則考慮增大 innodb_buffer_pool_size， 建議使用物理內(nèi)存的75%
val < 95% 則考慮減小 innodb_buffer_pool_size， 建議設(shè)置為：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)

查看數(shù)據(jù)庫配置及狀態(tài)
查詢innodb配置
show global variables where variable_name like ‘%innodb%’

查詢線程Cache配置
show variables like ‘thread_cache_size’;

查詢連接配置
show variables like ‘%connect%’;

查詢當(dāng)前狀態(tài)
status

show global status like ‘Thread%’;

數(shù)據(jù)庫表結(jié)構(gòu)

表結(jié)構(gòu)的設(shè)計目標(biāo)除了滿足業(yè)務(wù)以外，盡量減少代碼實現(xiàn)上的聯(lián)表查詢操作，因此在設(shè)計上可以適當(dāng)有一些冗余字段的設(shè)計，減少數(shù)據(jù)庫IO次數(shù)。
現(xiàn)在很流行的ElasticSearch等大數(shù)據(jù)存儲寬表的概念也是這種思想的體現(xiàn)

盡量避免使用分區(qū)表 MySQL的分區(qū)表實際性能不是很好。

拆分大字段和訪問頻率低的字段，分離冷熱數(shù)據(jù)

采用合理的分庫分表策略，推薦使用HASH進(jìn)行分表，表名后綴使用十進(jìn)制數(shù)，下標(biāo)從0開始首次分表盡量多的分，避免二次分表，二次分表的難度和成本較高

單表字段數(shù)控制在20個以內(nèi)

一條完整的建表語句中應(yīng)包含必要的字段、主鍵、合理的索引（綜合代碼中所有的條件語句創(chuàng)建合理的索引，主鍵必須要有
索引設(shè)計
索引是一把雙刃劍，它可以提高查詢效率但也會降低插入和更新的速度并占用磁盤空間。

單張表中索引數(shù)量不超過5個

單個索引中的字段數(shù)不超過5個

對字符串使用前綴索引，前綴索引長度不超過10個字符；如果有一個CHAR(200)列，如果在前10個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進(jìn)行索引。對前10個字符進(jìn)行索引能夠節(jié)省大量索引空間，也可能會使查詢更快

表必須有主鍵，不使用UUID、MD5、HASH作為主鍵，盡量不選擇字符串列作為主鍵；主鍵建議選擇自增id

創(chuàng)建復(fù)合索引時區(qū)分度較大的字段放在最前面；不在低區(qū)分度的字段上創(chuàng)建索引，如“性別”

避免冗余或重復(fù)索引

合理創(chuàng)建聯(lián)合索引（避免冗余），index(a、b、c) 相當(dāng)于index(a)、index(a、b)、index(a、、b、c)

索引不是越多越好，按實際需要進(jìn)行創(chuàng)建

每個額外的索引都要占用額外的磁盤空間，并降低寫操作的性能

不在索引列進(jìn)行數(shù)學(xué)運算和函數(shù)運算；

盡量不要使用外鍵 外鍵用來保護(hù)參照完整性，可在業(yè)務(wù)端實現(xiàn)，對父表和子表的操作會相互影響，降低可用性；

不使用%前導(dǎo)的查詢，如like“%xxx”，不使用反向查詢，如not in / not like 無法使用索引，導(dǎo)致全表掃描 全表掃描導(dǎo)致buffer pool利用降低

字段設(shè)計

盡可能不要使用TEXT、BLOB類型。刪除這種值會在數(shù)據(jù)表中留下很大的"空洞"，可以考慮把BLOB或TEXT列分離到單獨的表中

用DECIMAL代替FLOAT和DOUBLE存儲精確浮點數(shù)。浮點數(shù)相對于定點數(shù)的優(yōu)點是在長度一定的情況下，浮點數(shù)能夠表示更大的數(shù)據(jù)范圍；浮點數(shù)的缺點是會引起精度問題

將字符轉(zhuǎn)化為數(shù)字

使用TINYINT來代替ENUM類型

字段長度盡量按實際需要進(jìn)行分配，不要隨意分配一個很大的容量 VARCHAR(N)，N表示的是字符數(shù)不是字節(jié)數(shù)，比如VARCHAR(255)，可以最大可存儲255個漢字，需要根據(jù)實際的寬度來選擇N。VARCHAR(N)，N盡可能小，因為MySQL一個表中所有的VARCHAR字段最大長度是65535個字節(jié)，進(jìn)行排序和創(chuàng)建臨時表一類的內(nèi)存操作時，會使用N的長度申請內(nèi)存；

如果可能, 所有字段均定義為not null

使用UNSIGNED存儲非負(fù)整數(shù) 同樣的字節(jié)數(shù)，存儲的數(shù)值范圍更大。如tinyint有符號為-128-127，無符號為0-255

使用TIMESTAMP存儲時間. 因為TIMESTAMP使用4字節(jié)，DATETIME使用8個字節(jié),同時TIMESTAMP具有自動賦值以及自動更新的特性.

使用INT UNSIGNED存儲IPV4

使用VARBINARY存儲大小寫敏感的變長字符串

禁止在數(shù)據(jù)庫中存儲明文密碼

總結(jié)

以上是生活随笔為你收集整理的系统优化怎么做-数据库优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。