實例講解資料庫最佳化

從網路上去搜尋資料庫最佳化基本上都是從SQL層次進行最佳化的，很少提及到資料庫本身的實例最佳化。就算有也都是基於某個特定資料庫的實例優化，本文涵蓋目前市面上所有主流資料庫的實例優化（Oralce、MySQL、POSTGRES、達夢）,按照文章的配置能夠將你資料庫性能用到80%或以上。

資料庫最佳化方法論

這首分為理論知識，不感興趣的同學可以直接跳到後面參數配置部分。

資料庫最佳化目標

推薦《mysql影片教學》  

#根據角色的不同，資料庫最佳化分為以下幾個目標：

業務角度（關鍵使用者）：

減少使用者頁面回應時間

#資料庫角度（開發）：

#減少資料庫SQL回應時間

##資料庫伺服器角度（運維）：

充分使用資料庫伺服器實體資源

#減少資料庫伺服器CPU使用率

減少資料庫伺服器IO使用率

減少資料庫伺服器記憶體使用率

指標

# 1. SQL平均回應時間變短

a. 優化前：資料庫平均回應時間500ms

b. 最佳化目標：資料庫平均回應時間200ms2. 資料庫伺服器CPU佔用率變少

a. 最佳化前：資料庫高峰期CPU使用率70%

b.最佳化目標：資料庫高峰期CPU使用率50%

##3 .資料庫伺服器 IO使用率變低

a.優化前：資料庫IO WAIT為30%

b.優化目標：資料庫IO WAIT低於10%

#資料庫最佳化誤解

在進行資料庫最佳化的時候可能會有以下幾個誤解：

1. 優化之前一定要深入了解資料庫內部原理

優化是有「套路」的，照這些「套路」你也可以很好的完成資料庫優化

#2. 不斷調整資料庫參數就可以最終實現優化

#有時候設計不合理怎麼調整參數都不行

3. 不斷調整作業系統參數就可以最終實現最佳化

#同上

4. 資料庫效能由應用程式、資料庫架構決定，與應用開發關係不大

恰恰相反，應用程式開發的關係很大

5. 必須要做讀寫分離，必須弄分庫分錶

資料量級只有達到一定的比例才有必要做讀寫分離，分錶分庫，否則徒增複雜度。一般來說Oracle的單表量級可以達到1億，MySQL到1000萬~2000萬

資料庫最佳化流程

完整的資料庫最佳化流程如下：

首先需要盡可能的了解最佳化問題，收集問題期間系統資訊並做好存檔。根據目前系統問題表現制定優化目標並與客戶溝通目標達成一致；透過一系列工具分析系統問題，制定優化方案，方案評審完成後由各負責人員進行實施。若達到最佳化目標則編寫最佳化報告，否則需要重新制定最佳化方案。

資料庫實例最佳化

資料庫實例最佳化遵循三句口訣：

日誌不能小、快取夠大、連線要夠用。

資料庫事務提交後需要將交易對資料頁的修改刷（ fsync）到磁碟上，才能保證資料的持久性。這個刷盤，是一個隨機寫，效能較低，如果每次交易提交都要刷盤，會極大影響資料庫的效能。資料庫在架構設計中都會採用以下兩個最佳化手法：

a. 先將交易寫到日誌檔RedoLog（WAL），將隨機寫優化成順序寫b. 加一層快取結構Buffer，將每次寫入最佳化成順序寫

###所以日誌跟快取對資料庫實例尤其重要。而連線如果不夠用，資料庫會直接拋出異常，系統無法存取。 ######資料庫參數最佳化######主流資料庫架構都有如下的共同點：##########資料快取######SQL解析區###### #排序記憶體######REDO及UNDO######鎖定、LATCH、MUTEX#######監聽及連線######檔案讀寫效能######## #接下來我們根據不同的資料庫調整參數以使資料庫達到最佳效能。 #########ORACLE########越大越好數據快取DB_CACHE_SIZE物理記憶體70-80%越大越好SQL解析#SHARED_POOL_SIZE 4-16G不建議設定過大#監聽及連線#其他

參數分類	參數名稱	參數值	備註
資料快取	SGA_TAGET、MEMORY_TARGET	#實體記憶體70-80%
##PROCESSES、SESSIONS、OPEN_CURSORS	根據業務需求設定	一般為業務預估連線數的120%
SESSION_CACHED_CURSORS	#大於200	軟體軟體解析

MYSQL（INNODB）

參數分類資料快取日誌相關#日誌相關監聽及連線檔案讀寫效能#其他

參數名稱	參數值	#備註
INNODB_BUFFER_POOL_SIZE	物理記憶體50-80%	#一般來說越大效能越好
#Innodb_log_buffer_size	16-32M	根據運行情況調整
sync_binlog	1、100、0	1安全性最好
#max_connections	依照業務狀況調整	可以預留一部分值
innodb_flush_log_at_trx_commit	2	安全與效能的摺中考慮
wait_timeout，interactive_timeout	#28800	避免套用連線定時中斷

#POSTGRES

參數分類資料快取SHARED_BUFFERS實體記憶體10-25%CACHE_BUFFER_SIZE實體記憶體50-60%wal_buffer#max_connections根據業務狀況調整

參數名稱	參數值
			資料快取
			日誌相關
8-64M	不建議設定過大過小		監聽及連線

一般為業務預估連線數的120%

其他maintenance_work_mem512M或更大#其他work_memcheckpoint_segments#32或更大達夢資料庫參數值MEMROY_TARGET、MEMROY_POOL

#8-16M	#原始配置1M過小		其他
		#參數分類	參數名稱
備註			資料快取

#實體記憶體90%

資料快取BUFFER

###實體記憶體60%######資料快取## ##########資料快取######MAX_BUFFER######實體記憶體70%######最大資料快取############監聽與連線######max_sessions######依業務需求設定#######一般為業務預估連線數的120%############### ####總結#########資料庫的最佳化手法太多太多，有換磁碟陣列升級硬件，有改寫SQL腳本添加索引，還有資料庫參數調整最佳化效能，甚至還可以調整資料庫架構。本文從資料庫本身參數進行調優，大家根據上面幾張表中的參數進行調整基本能達到資料庫最佳效能的80%。 ######本文來自php中文網，###mysql教學###欄目，歡迎學習！  ###

以上是實例講解資料庫最佳化的詳細內容。更多資訊請關注PHP中文網其他相關文章！