?2016-2026 Matools All rights reserved,
粵ICP備17059708號
從網(wǎng)上去搜數(shù)據(jù)庫優(yōu)化基本都是從SQL層次進(jìn)行優(yōu)化的，很少有提及到數(shù)據(jù)庫本身的實例優(yōu)化。就算有也都是基于某個特定數(shù)據(jù)庫的實例優(yōu)化，本文涵蓋目前市面上所有主流數(shù)據(jù)庫的實例優(yōu)化（Oralce、MySQL、POSTGRES、達(dá)夢）,按照文章的配置能夠?qū)⒛銛?shù)據(jù)庫性能用到80%或以上。
數(shù)據(jù)庫優(yōu)化方法論
這部分為理論知識，不感興趣的同學(xué)可以直接跳到后面參數(shù)配置部分。
數(shù)據(jù)庫優(yōu)化目標(biāo)
目標(biāo)
根據(jù)角色的不同，數(shù)據(jù)庫優(yōu)化分為以下幾個目標(biāo)：
* 業(yè)務(wù)角度（關(guān)鍵用戶）：
減少用戶頁面響應(yīng)時間
* 數(shù)據(jù)庫角度（開發(fā)）：
減少數(shù)據(jù)庫SQL響應(yīng)時間
* 數(shù)據(jù)庫服務(wù)器角度（運(yùn)維）：
充分使用數(shù)據(jù)庫服務(wù)器物理資源
減少數(shù)據(jù)庫服務(wù)器CPU使用率
減少數(shù)據(jù)庫服務(wù)器IO使用率
減少數(shù)據(jù)庫服務(wù)器內(nèi)存使用率
指標(biāo)
* SQL平均響應(yīng)時間變短
* 優(yōu)化前：數(shù)據(jù)庫平均響應(yīng)時間500ms
* 優(yōu)化目標(biāo)：數(shù)據(jù)庫平均響應(yīng)時間200ms
* 數(shù)據(jù)庫服務(wù)器CPU占用率變少
* 優(yōu)化前：數(shù)據(jù)庫高峰期CPU使用率70%
* 優(yōu)化目標(biāo)：數(shù)據(jù)庫高峰期CPU使用率50%
* 數(shù)據(jù)庫服務(wù)器IO使用率變低
* 優(yōu)化前：數(shù)據(jù)庫IO WAIT為30%
* 優(yōu)化目標(biāo)：數(shù)據(jù)庫IO WAIT低于10%
數(shù)據(jù)庫優(yōu)化誤區(qū)
在進(jìn)行數(shù)據(jù)庫優(yōu)化的時候可能會有以下幾個誤區(qū)：
* 優(yōu)化之前一定要深入了解數(shù)據(jù)庫內(nèi)部原理
優(yōu)化是有“套路”的，照著這些“套路”你也可以很好的完成數(shù)據(jù)庫優(yōu)化
* 不斷調(diào)整數(shù)據(jù)庫參數(shù)就可以最終實現(xiàn)優(yōu)化
有時候設(shè)計不合理怎么調(diào)整參數(shù)都不行
* 不斷調(diào)整操作系統(tǒng)參數(shù)就可以最終實現(xiàn)優(yōu)化
同上
* 數(shù)據(jù)庫性能由應(yīng)用、數(shù)據(jù)庫架構(gòu)決定，與應(yīng)用開發(fā)關(guān)系不大
恰恰相反，應(yīng)用開發(fā)的關(guān)系很大
* 必須要做讀寫分離，必須要弄分庫分表
數(shù)據(jù)量級只有達(dá)到一定的比例才有必要做讀寫分離，分表分庫，否則徒增復(fù)雜度。一般來說Oracle的單表量級可以達(dá)到1億，MySQL到1000萬~2000萬
數(shù)據(jù)庫優(yōu)化流程
完整的數(shù)據(jù)庫優(yōu)化流程如下：
首先需要盡可能的了解優(yōu)化問題，收集問題期間系統(tǒng)信息并做好存檔。根據(jù)當(dāng)前系統(tǒng)問題表現(xiàn)制定優(yōu)化目標(biāo)并與客戶溝通目標(biāo)達(dá)成一致；通過一系列工具分析系統(tǒng)問題，制定優(yōu)化方案，方案評審?fù)瓿珊笥筛髫?fù)責(zé)人員進(jìn)行實施。若達(dá)到優(yōu)化目標(biāo)則編寫優(yōu)化報告，否則需要重新制定優(yōu)化方案。
數(shù)據(jù)庫實例優(yōu)化
數(shù)據(jù)庫實例優(yōu)化遵循三句口訣：日志不能小、緩存足夠大、連接要夠用。
數(shù)據(jù)庫事務(wù)提交后需要將事務(wù)對數(shù)據(jù)頁的修改刷（
fsync）到磁盤上，才能保證數(shù)據(jù)的持久性。這個刷盤，是一個隨機(jī)寫，性能較低，如果每次事務(wù)提交都要刷盤，會極大影響數(shù)據(jù)庫的性能。數(shù)據(jù)庫在架構(gòu)設(shè)計中都會采用如下兩個優(yōu)化手法：
* 先將事務(wù)寫到日志文件RedoLog（WAL），將隨機(jī)寫優(yōu)化成順序?qū)?
* 加一層緩存結(jié)構(gòu)Buffer，將每次寫優(yōu)化成順序?qū)?
所以日志跟緩存對數(shù)據(jù)庫實例尤其重要。而連接如果不夠用，數(shù)據(jù)庫會直接拋出異常，系統(tǒng)無法訪問。
數(shù)據(jù)庫參數(shù)優(yōu)化
主流數(shù)據(jù)庫架構(gòu)都有如下的共同點(diǎn)：
* 數(shù)據(jù)緩存
* SQL解析區(qū)
* 排序內(nèi)存
* REDO及UNDO
* 鎖、LATCH、MUTEX
* 監(jiān)聽及連接
* 文件讀寫性能
接下來我們根據(jù)不同的數(shù)據(jù)庫調(diào)整參數(shù)以使數(shù)據(jù)庫達(dá)到最佳性能。
ORACLE
參數(shù)分類 參數(shù)名 參數(shù)值 備注
數(shù)據(jù)緩存 SGA_TAGET、MEMORY_TARGET 物理內(nèi)存70-80% 越大越好
數(shù)據(jù)緩存 DB_CACHE_SIZE 物理內(nèi)存70-80% 越大越好
SQL解析 SHARED_POOL_SIZE 4-16G 不建議設(shè)置過大
監(jiān)聽及連接 PROCESSES、SESSIONS、OPEN_CURSORS 根據(jù)業(yè)務(wù)需求設(shè)置 一般為業(yè)務(wù)預(yù)估連接數(shù)的120%
其他 SESSION_CACHED_CURSORS 大于200 軟軟解析
MYSQL（INNODB）
參數(shù)分類 參數(shù)名 參數(shù)值 備注
數(shù)據(jù)緩存 INNODB_BUFFER_POOL_SIZE 物理內(nèi)存50-80% 一般來說越大性能越好
日志相關(guān) Innodb_log_buffer_size 16-32M 根據(jù)運(yùn)行情況調(diào)整
日志相關(guān) sync_binlog 1、100、0 1安全性最好
監(jiān)聽及連接 max_connections 根據(jù)業(yè)務(wù)情況調(diào)整 可以預(yù)留一部分值
文件讀寫性能 innodb_flush_log_at_trx_commit 2 安全和性能的折中考慮
其他 wait_timeout，interactive_timeout 28800 避免應(yīng)用連接定時中斷
POSTGRES
參數(shù)分類 參數(shù)名 參數(shù)值 備注
數(shù)據(jù)緩存 SHARED_BUFFERS 物理內(nèi)存10-25%
數(shù)據(jù)緩存 CACHE_BUFFER_SIZE 物理內(nèi)存50-60%
日志相關(guān) wal_buffer 8-64M 不建議設(shè)置過大過小
監(jiān)聽及連接 max_connections 根據(jù)業(yè)務(wù)情況調(diào)整 一般為業(yè)務(wù)預(yù)估連接數(shù)的120%
其他 maintenance_work_mem 512M或更大
其他 work_mem 8-16M 原始配置1M過小
其他 checkpoint_segments 32或者更大
達(dá)夢數(shù)據(jù)庫
參數(shù)分類 參數(shù)名 參數(shù)值 備注
數(shù)據(jù)緩存 MEMROY_TARGET、MEMROY_POOL 物理內(nèi)存90%
數(shù)據(jù)緩存 BUFFER 物理內(nèi)存60% 數(shù)據(jù)緩存
數(shù)據(jù)緩存 MAX_BUFFER 物理內(nèi)存70% 最大數(shù)據(jù)緩存
監(jiān)聽及連接 max_sessions 根據(jù)業(yè)務(wù)需求設(shè)置 一般為業(yè)務(wù)預(yù)估連接數(shù)的120%
總結(jié)
數(shù)據(jù)庫的優(yōu)化手法太多太多，有換磁盤陣列升級硬件，有改寫SQL腳本添加索引，還有數(shù)據(jù)庫參數(shù)調(diào)整優(yōu)化性能，甚至還可以調(diào)整數(shù)據(jù)庫架構(gòu)。本文從數(shù)據(jù)庫本身參數(shù)進(jìn)行調(diào)優(yōu)，大家根據(jù)上面幾張表中的參數(shù)進(jìn)行調(diào)整基本能達(dá)到數(shù)據(jù)庫最佳性能的80%。
請關(guān)注個人公眾號:JAVA日知錄
熱門工具 換一換