數(shù)據(jù)庫優(yōu)化 - 實例參數(shù)優(yōu)化

一個新安裝的數(shù)據(jù)庫使用默認的參數(shù)配置，那么數(shù)據(jù)庫的性能很難達到生產(chǎn)要求。要想更好的發(fā)揮數(shù)據(jù)庫性能，對數(shù)據(jù)庫參數(shù)優(yōu)化是必不可少的。

本文涵蓋目前市面上所有主流數(shù)據(jù)庫的優(yōu)化配置（Oralce、MySQL、POSTGRES、達夢）,掌握本文的參數(shù)的配置可以讓你數(shù)據(jù)庫飛起來，更能讓你成為別人眼中的數(shù)據(jù)庫高手！

數(shù)據(jù)庫優(yōu)化方法論

數(shù)據(jù)庫優(yōu)化目標

根據(jù)使用數(shù)據(jù)庫角色的不同，數(shù)據(jù)庫優(yōu)化分為以下幾個目標：

• 業(yè)務(wù)角度（關(guān)鍵用戶）：
  減少用戶頁面響應(yīng)時間
• 數(shù)據(jù)庫角度（開發(fā)）：
  減少數(shù)據(jù)庫SQL響應(yīng)時間
• 數(shù)據(jù)庫服務(wù)器角度（運維）：
  充分使用數(shù)據(jù)庫服務(wù)器物理資源
  減少數(shù)據(jù)庫服務(wù)器CPU使用率
  減少數(shù)據(jù)庫服務(wù)器IO使用率
  減少數(shù)據(jù)庫服務(wù)器內(nèi)存使用率

數(shù)據(jù)庫優(yōu)化指標

要優(yōu)化數(shù)據(jù)庫，我們得先給本次優(yōu)化建立一個優(yōu)化目標，也就是優(yōu)化指標。一般來說數(shù)據(jù)庫優(yōu)化可以建立以下幾個指標：

• SQL平均響應(yīng)時間變短
• 優(yōu)化前：數(shù)據(jù)庫平均響應(yīng)時間500ms
• 優(yōu)化目標：數(shù)據(jù)庫平均響應(yīng)時間200ms
• 數(shù)據(jù)庫服務(wù)器CPU占用率變少
• 優(yōu)化前：數(shù)據(jù)庫高峰期CPU使用率70%
• 優(yōu)化目標：數(shù)據(jù)庫高峰期CPU使用率50%
• 數(shù)據(jù)庫服務(wù)器IO使用率變低
• 優(yōu)化前：數(shù)據(jù)庫IO WAIT為30%
• 優(yōu)化目標：數(shù)據(jù)庫IO WAIT低于10%

數(shù)據(jù)庫優(yōu)化誤區(qū)

在進行數(shù)據(jù)庫優(yōu)化的時候可能會有以下幾個誤區(qū)：

• 優(yōu)化之前一定要深入了解數(shù)據(jù)庫內(nèi)部原理
  其實數(shù)據(jù)庫優(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)系不大
  恰恰相反，數(shù)據(jù)庫性能與應(yīng)用開發(fā)的關(guān)系很大
• 必須要做讀寫分離，必須要弄分庫分表
  數(shù)據(jù)量級只有達到一定的比例才有必要做讀寫分離，分表分庫，否則徒增復雜度。一般來說Oracle的單表量級可以達到1億，MySQL到1000萬~2000萬

數(shù)據(jù)庫優(yōu)化流程

完整的數(shù)據(jù)庫優(yōu)化流程如下：

首先我們需要盡可能的了解優(yōu)化問題，收集問題期間系統(tǒng)信息并做好存檔。
根據(jù)當前系統(tǒng)問題表現(xiàn)制定優(yōu)化目標并與客戶溝通目標達成一致；
通過一系列工具分析系統(tǒng)問題，制定優(yōu)化方案，方案評審完成后由各負責人員進行實施。
若達到優(yōu)化目標則編寫優(yōu)化報告，否則需要重新制定優(yōu)化方案。

數(shù)據(jù)庫實例優(yōu)化

數(shù)據(jù)庫實例優(yōu)化大家只需記住三句口訣：日志不能小、緩存足夠大、連接要夠用。

在數(shù)據(jù)庫事務(wù)提交后需要將事務(wù)對數(shù)據(jù)頁的修改刷（ fsync）到磁盤上，才能保證數(shù)據(jù)的持久性。這個刷盤，是一個隨機寫，性能較低，如果每次事務(wù)提交都要刷盤，會極大影響數(shù)據(jù)庫的性能。
所有數(shù)據(jù)庫在架構(gòu)設(shè)計中都會采用如下兩個優(yōu)化手法：

• 先將事務(wù)寫到日志文件RedoLog（WAL），將隨機寫優(yōu)化成順序?qū)?/span>
• 加一層緩存結(jié)構(gòu)Buffer，將每次寫優(yōu)化成順序?qū)?/span>

所以日志跟緩存對數(shù)據(jù)庫實例尤其重要。而如果連接不夠用，數(shù)據(jù)庫會直接拋出異常，系統(tǒng)無法訪問，所以也需要對連接數(shù)進行優(yōu)化。

數(shù)據(jù)庫參數(shù)優(yōu)化

主流數(shù)據(jù)庫架構(gòu)都有如下幾個共同關(guān)注點：

• 數(shù)據(jù)緩存
• SQL解析區(qū)
• 排序內(nèi)存
• redo log及undo log
• 鎖、LATCH、MUTEX
• 監(jiān)聽及連接
• 文件讀寫性能

接下來我們根據(jù)不同的數(shù)據(jù)庫調(diào)整參數(shù)以使數(shù)據(jù)庫達到最佳性能。

ORACLE

MYSQL（INNODB）

POSTGRES

達夢數(shù)據(jù)庫

小結(jié)

數(shù)據(jù)庫的優(yōu)化手法有很多，比如有換磁盤陣列升級硬件的，有改寫SQL腳本添加索引的，還有數(shù)據(jù)庫參數(shù)調(diào)整優(yōu)化性能，甚至還可以業(yè)務(wù)架構(gòu)來對數(shù)據(jù)庫進行優(yōu)化。
本文從數(shù)據(jù)庫參數(shù)的角度進行調(diào)優(yōu)，大家根據(jù)上面幾張表中的配置對數(shù)據(jù)庫參數(shù)調(diào)整后可以讓數(shù)據(jù)庫性能達到數(shù)據(jù)庫最佳性能的80%。