淺談數(shù)據(jù)庫性能的優(yōu)化

摘要：數(shù)據(jù)庫性能取決于許多因素，從數(shù)據(jù)庫層面說，有表、查詢、設置，從硬件層面說有磁盤吞吐量、尋道時間等。本文在普通的硬件條件下，從軟件方面對數(shù)據(jù)庫優(yōu)化進行了分析，主要目標是降低I/O次數(shù)和CPU運算次數(shù)，并以數(shù)據(jù)庫MySQL為例進行分析。

關鍵詞：I/O次數(shù) CPU運算次數(shù) MySQL SELECT

數(shù)據(jù)庫性能取決于許多因素，從數(shù)據(jù)庫層面說，有表、查詢、設置，從硬件層面說有磁盤吞吐量、尋道時間等。如果剛入門做數(shù)據(jù)庫優(yōu)化，可以從一些準則和優(yōu)化指南入手，通過查詢的執(zhí)行時間來衡量效果；如果要成為專家，要弄清楚數(shù)據(jù)庫的內(nèi)部運作機制，用CPU時間片和I/O操作數(shù)來衡量優(yōu)化效果。數(shù)據(jù)庫層面的優(yōu)化，普通用戶通過現(xiàn)有的軟硬件配置的優(yōu)化來提高性能；高級用戶可以自己修改數(shù)據(jù)庫的代碼、開發(fā)自己的存儲引擎，使用自制的高性能硬件設備。下面就從硬件和軟件兩個層面談一下數(shù)據(jù)庫性能的優(yōu)化。

1 硬件層面的優(yōu)化

隨著數(shù)據(jù)庫的存取量越來越大，任何數(shù)據(jù)庫應用都可能遇到硬件的瓶頸。DBA必需評估有無可能通過優(yōu)化邏輯和配置消除這些瓶頸，或者需要更多的硬件資源，系統(tǒng)瓶頸一般產(chǎn)生在這四個方面：磁盤尋道、磁盤讀寫、CPU、內(nèi)存。因此這四個方面可做如下優(yōu)化：

1.1 磁盤尋道。磁盤找到要讀寫的數(shù)據(jù)需要花費時間，現(xiàn)代硬盤的尋道時間一般低于10ms，所以我們理論上1秒鐘可以做100次尋道。硬盤在這方面的改進非常緩慢，單表也很難有優(yōu)化余地。優(yōu)化尋道的簡單方法是把數(shù)據(jù)分布到兩個或多個硬盤上。

1.2 磁盤讀寫。磁盤的磁頭尋道到正確的位置之后，接下來就是讀寫數(shù)據(jù)，現(xiàn)代磁盤最少能達到10-20MB/s的吞吐量。讀寫比尋道容易優(yōu)化，因為可以從幾塊硬盤并行的讀（磁盤陣列）。

1.3 CPU。當遇到 CPU 方面的資源瓶頸的時候，可能由兩個方面造成：①過多依賴數(shù)據(jù)庫進行邏輯運算：對于這種狀況，最好的優(yōu)化方式是將運算盡可能從數(shù)據(jù)庫端遷移到應用端，降低數(shù)據(jù)庫主機的計算量。如果從數(shù)據(jù)庫端的硬件來解決問題，一般要通過增加設備CPU數(shù)目（如果支持），或者是使用CPU能力更為高端的主機來替換老主機。②數(shù)據(jù)庫邏輯IO太大：對于這類狀況，從硬件角度來說能做的就只有提升CPU處理能力。增加CPU數(shù)目（如果支持），或換CPU更強勁的主機。但是在這之前，建議先嘗試從應用角度優(yōu)化看看是否能夠盡量降低非必要請求或者是減少每次請求的數(shù)據(jù)量。同時從數(shù)據(jù)庫角度針對Schema結構以及索引進行相應的優(yōu)化調整，盡可能讓完成一次請求所需要檢索的數(shù)據(jù)量更小，從而達到降低邏輯IO的目的。

1.4 內(nèi)存。當CPU需要的數(shù)據(jù)比CPU cache更大時，主內(nèi)存帶寬就成了瓶頸，內(nèi)存成為瓶頸的情況較為罕見。優(yōu)化的方法是增加內(nèi)存，加大可緩存的數(shù)據(jù)量。這個方案能否達到效果取決于系統(tǒng)熱點數(shù)據(jù)的總量，畢竟內(nèi)存的成本也是比較高的，而且單臺設備所能管理的內(nèi)存量也是有限的。

2 軟件層面的優(yōu)化

2.1 優(yōu)化SQL語句。數(shù)據(jù)庫程序的核心邏輯通過SQL語句執(zhí)行，有的通過解析器直接執(zhí)行，有的通過API提交到后臺程序。

2.2 優(yōu)化SELECT語句。SELECT形式的查詢執(zhí)行了數(shù)據(jù)庫中的全部查詢操作，不管是做動態(tài)網(wǎng)頁還是需要通宵執(zhí)行的巨大報表，優(yōu)化SELECT都是重中之重。SELECT調優(yōu)技術也可以應用到“CREATE TABLE...AS SELECT”、“INSERT INTO...SELECT”和DELETE語句的WHERE子句。這些語句在SELECT基礎上有附加的功能，讀出查詢結果之后又附加了寫操作。

2.3 優(yōu)化查詢的主要注意事項。①在where條件中用到的字段上簡歷索引，可加快過濾和最后的檢索速度。為了避免磁盤空間的浪費，應建一個盡可能小的索引去優(yōu)化程序中用到的大部分的查詢。②查詢中用到的表要盡可能少，最好單表查，大表尤其不要聯(lián)查。③盡量使表統(tǒng)計信息更新，使優(yōu)化器有足夠的信息找到一個最有效率的執(zhí)行方案。④學習不同存儲引擎的調優(yōu)技術、索引技術和配置。⑤分別調用優(yōu)化查詢的每一部分，比如：耗時的函數(shù)調用，要知道在大查詢操作中，這些函數(shù)可能被調用上百萬次。⑥如果一個性能問題無法通過簡單的原則解決，就要通過查看explain命令來了解細節(jié)，以便調整索引，WHERE語句，JOIN語句等等。高級用戶在做每個查詢的第一步就是explain。⑦調整MySQL用于緩存的內(nèi)存大小和設置，通過有效運用InnoDB的緩沖池、MyISAM的鍵緩存和查詢緩存，重復查詢可以更快執(zhí)行，因為第二次以及以后的查詢可以直接從內(nèi)存中讀取查詢結果。⑧即使一個查詢已經(jīng)通過內(nèi)存緩存優(yōu)化過，仍有進一步優(yōu)化的余地，優(yōu)化還可以使需要的內(nèi)存更少，讓程序可以處理更多的并發(fā)用戶，更大的請求數(shù)量，而性能卻不會明顯下降。⑨處理鎖問題，在同一時間內(nèi)，查詢速度可能被其他存取表所影響。

2.4 SELECT語句的速度。一般來說，當你要優(yōu)化“SELECT...WHERE”第一反應應該想到的是加索引，當引用多個表，使用連接（JOIN）和外鍵時，索引尤為重要?？梢酝ㄟ^EXPLAIN語句判斷查詢使用了哪個索引。

3 小結

其實數(shù)據(jù)庫的性能優(yōu)化是一個復雜的過程，上述這些只是在應用層次的一種體現(xiàn)，深入研究還會涉及數(shù)據(jù)庫層的資源配置、網(wǎng)絡層的流量控制以及操作系統(tǒng)層的總體設計。

參考文獻：

[1]顧長生，林超.SQL Server6.5的九種性能優(yōu)化方法[J].計算機應用研究，2001第7期-維普資訊網(wǎng).

[2]席潔.數(shù)據(jù)庫優(yōu)化技術的分析與探討[J].電腦知識與技術，2011（12）.

[3]張永霞.關于SQL數(shù)據(jù)庫的分析與探討[J].數(shù)字技術與應用， 2011（10）.

作者簡介：肖梅（1972-），女，山東茌平人，工程碩士，東營職業(yè)學院電子信息與傳媒學院講師，研究方向：網(wǎng)絡數(shù)據(jù)庫。