資料庫的優化方法

『壹』 鎬庢牱浼樺寲mysql鏁版嵁搴撴潵鎻愰珮mysql鎬ц兘(mysql鏁版嵁搴撶殑浼樺寲)

浼樺寲鈥渕ysql鏁版嵁搴撯濇潵鎻愰珮鈥渕ysql鎬ц兘鈥濈殑鏂規硶鏈夛細

1銆侀夊彇鏈閫傜敤鐨勫瓧孌靛睘鎬с

MySQL鍙浠ュ緢濂界殑鏀鎸佸ぇ鏁版嵁閲忕殑瀛樺彇錛屼絾鏄涓鑸璇存潵錛屾暟鎹搴撲腑鐨勮〃瓚婂皬錛屽湪瀹冧笂闈㈡墽琛岀殑鏌ヨ涔熷氨浼氳秺蹇銆傚洜姝わ紝鍦ㄥ壋寤鴻〃鐨勬椂鍊欙紝涓轟簡鑾峰緱鏇村ソ鐨勬ц兘錛屾垜浠鍙浠ュ皢琛ㄤ腑瀛楁電殑瀹藉害璁懼緱灝藉彲鑳藉皬銆

2銆佷嬌鐢ㄨ繛鎺ワ紙JOIN錛夋潵浠ｆ浛瀛愭煡璇(Sub-Queries)銆

MySQL浠4.1寮濮嬫敮鎸丼QL鐨勫瓙鏌ヨ銆傝繖涓鎶鏈鍙浠ヤ嬌鐢⊿ELECT璇鍙ユ潵鍒涘緩涓涓鍗曞垪鐨勬煡璇㈢粨鏋滐紝鐒跺悗鎶婅繖涓緇撴灉浣滀負榪囨護鏉′歡鐢ㄥ湪鍙︿竴涓鏌ヨ涓銆

3銆佷嬌鐢ㄨ仈鍚(UNION)鏉ヤ唬鏇挎墜鍔ㄥ壋寤虹殑涓存椂琛ㄣ

MySQL浠4.0鐨勭増鏈寮濮嬫敮鎸乁NION鏌ヨ錛屽畠鍙浠ユ妸闇瑕佷嬌鐢ㄤ復鏃惰〃鐨勪袱鏉℃垨鏇村氱殑SELECT鏌ヨ㈠悎騫剁殑涓涓鏌ヨ涓銆傚湪瀹㈡埛絝鐨勬煡璇浼氳瘽緇撴潫鐨勬椂鍊欙紝涓存椂琛ㄤ細琚鑷鍔ㄥ垹闄わ紝浠庤屼繚璇佹暟鎹搴撴暣榻愩侀珮鏁堛

4銆佷簨鍔°

瑕佹妸鏌愪釜鏁版嵁鍚屾椂鎻掑叆涓や釜鐩稿叧鑱旂殑琛ㄤ腑錛屽彲鑳戒細鍑虹幇榪欐牱鐨勬儏鍐碉細絎涓涓琛ㄤ腑鎴愬姛鏇存柊鍚庯紝鏁版嵁搴撶獊鐒跺嚭鐜版剰澶栫姸鍐碉紝閫犳垚絎浜屼釜琛ㄤ腑鐨勬搷浣滄病鏈夊畬鎴愶紝榪欐牱錛屽氨浼氶犳垚鏁版嵁鐨勪笉瀹屾暣錛岀敋鑷充細鐮村潖鏁版嵁搴撲腑鐨勬暟鎹銆傝侀伩鍏嶈繖縐嶆儏鍐碉紝灝卞簲璇ヤ嬌鐢ㄤ簨鍔★紝瀹冪殑浣滅敤鏄錛氳佷箞璇鍙ュ潡涓姣忔潯璇鍙ラ兘鎿嶄綔鎴愬姛錛岃佷箞閮藉け璐ャ

5銆侀攣瀹氳〃銆

灝界′簨鍔℃槸緇存姢鏁版嵁搴撳畬鏁存х殑涓涓闈炲父濂界殑鏂規硶錛屼絾鍗村洜涓哄畠鐨勭嫭鍗犳э紝鏈夋椂浼氬獎鍝嶆暟鎹搴撶殑鎬ц兘錛屽挨鍏舵槸鍦ㄥ緢澶х殑搴旂敤緋葷粺涓銆傜敱浜庡湪浜嬪姟鎵ц岀殑榪囩▼涓錛屾暟鎹搴撳皢浼氳閿佸畾錛屽洜姝ゅ叾瀹冪殑鐢ㄦ埛璇鋒眰鍙鑳芥殏鏃剁瓑寰呯洿鍒拌ヤ簨鍔＄粨鏉熴

6銆佷嬌鐢ㄥ栭敭銆

閿佸畾琛ㄧ殑鏂規硶鍙浠ョ淮鎶ゆ暟鎹鐨勫畬鏁存э紝浣嗘槸瀹冨嵈涓嶈兘淇濊瘉鏁版嵁鐨勫叧鑱旀с傝繖涓鏃跺欐垜浠灝卞彲浠ヤ嬌鐢ㄥ栭敭銆

7銆佷嬌鐢ㄧ儲寮

緔㈠紩鏄鎻愰珮鏁版嵁搴撴ц兘鐨勫父鐢ㄦ柟娉曪紝瀹冨彲浠ヤ護鏁版嵁搴撴湇鍔″櫒浠ユ瘮娌℃湁緔㈠紩蹇寰楀氱殑閫熷害媯緔㈢壒瀹氱殑琛岋紝灝ゅ叾鏄鍦ㄦ煡璇㈣鍙ュ綋涓鍖呭惈鏈塎AX(),MIN()鍜孫RDERBY榪欎簺鍛戒護鐨勬椂鍊欙紝鎬ц兘鎻愰珮鏇翠負鏄庢樉銆

8銆佷紭鍖栫殑鏌ヨ㈣鍙

緇濆ぇ澶氭暟鎯呭喌涓嬶紝浣跨敤緔㈠紩鍙浠ユ彁楂樻煡璇㈢殑閫熷害錛屼絾濡傛灉SQL璇鍙ヤ嬌鐢ㄤ笉鎮板綋鐨勮瘽錛岀儲寮曞皢鏃犳硶鍙戞尌瀹冨簲鏈夌殑浣滅敤銆

『貳』 資料庫性能優化主要包括哪些方面

包括網路、硬體、操作系統、資料庫參數和應用程序。

資料庫的優化通常可以通過對網路、硬體、操作系統、資料庫參數和應用程序的優化來進行。最常見的優化手段就是對硬體的升級。

根據統計，對網路、硬體、操作系統、資料庫參數進行優化所獲得的性能提升，全部加起來只佔資料庫系統性能提升的40%左右，其餘的60%系統性能提升來自對應用程序的優化。許多優化專家認為，對應用程序的優化可以得到80%的系統性能的提升。

(2)資料庫的優化方法擴展閱讀

資料庫性能優化法則歸納為5個層次：

1、減少數據訪問（減少磁碟訪問）

2、返回更少數據（減少網路傳輸或磁碟訪問）

3、減少交互次數（減少網路傳輸）

4、減少伺服器CPU開銷（減少CPU及內存開銷）

5、利用更多資源（增加資源）

由於每一層優化法則都是解決其對應硬體的性能問題，所以帶來的性能提升比例也不一樣。傳統資料庫系統設計是也是盡可能對低速設備提供優化方法，因此針對低速設備問題的可優化手段也更多，優化成本也更低。

任何一個SQL的性能優化都應該按這個規則由上到下來診斷問題並提出解決方案，而不應該首先想到的是增加資源解決問題。

『叄』 資料庫優化有哪些方法

1、調整數據結構的設計。這一部分在開發信息系統之前完成，程序員需要考慮是否使用ORACLE資料庫的分區功能，對於經常訪問的資料庫表是否需要建立索引等。
2、調整應用程序結構設計。這一部分也是在開發信息系統之前完成，程序員在這一步需要考慮應用程序使用什麼樣的體系結構，是使用傳統的Client、Server兩層體系結構，還是使用Browser、Web、Database的三層體系結構。
3、調整資料庫SQL語句。應用程序的執行最終將歸結為資料庫中的SQL語句執行，因此SQL語句的執行效率最終決定了ORACLE資料庫的性能。
4、調整伺服器內存分配。內存分配是在信息系統運行過程中優化配置的。
5、調整硬碟I/O，這一步是在信息系統開發之前完成的。資料庫管理員可以將組成同一個表空間的數據文件放在不同的硬碟上，做到硬碟之間I/O負載均衡。
6、調整操作系統參數。

『肆』 資料庫性能優化有哪些措施

1、調整數據結構的設計

這一部分在開發信息系統之前完成，程序員需要考慮是否使用ORACLE資料庫的分區功能，對於經常訪問的資料庫表是否需要建立索引等。

2、調整應用程序結構設計

這一部分也是在開發信息系統之前完成，程序員在這一步需要考慮應用程序使用什麼樣的體系結構，是使用傳統的Client/Server兩層體系結構，還是使用Browser/Web/Database的三層體系結構。不同的應用程序體系結構要求的資料庫資源是不同的。

3、調整資料庫SQL語句

應用程序的執行最終將歸結為資料庫中的SQL語句執行，因此SQL語句的執行效率最終決定了ORACLE資料庫的性能。ORACLE公司推薦使用ORACLE語句優化器(OracleOptimizer)和行鎖管理器(row-levelmanager)來調整優化SQL語句。

4、調整伺服器內存分配

內存分配是在信息系統運行過程中優化配置的，資料庫管理員可以根據資料庫運行狀況調整資料庫系統全局區(SGA區)的數據緩沖區、日誌緩沖區和共享池的大小;還可以調整程序全局區(PGA區)的大小。需要注意的是，SGA區不是越大越好，SGA區過大會佔用操作系統使用的內存而引起虛擬內存的頁面交換，這樣反而會降低系統。

5、調整硬碟I/O

這一步是在信息系統開發之前完成的。資料庫管理員可以將組成同一個表空間的數據文件放在不同的硬碟上，做到硬碟之間I/O負載均衡。

6、調整操作系統參數

例如：運行在UNIX操作系統上的ORACLE資料庫，可以調整UNIX數據緩沖池的大小，每個進程所能使用的內存大小等參數。

實際上，上述資料庫優化措施之間是相互聯系的。ORACLE資料庫性能惡化表現基本上都是用戶響應時間比較長，需要用戶長時間的等待。但性能惡化的原因卻是多種多樣的，有時是多個因素共同造成了性能惡化的結果，這就需要資料庫管理員有比較全面的計算機知識，能夠敏感地察覺到影響資料庫性能的主要原因所在。另外，良好的資料庫管理工具對於優化資料庫性能也是很重要的。

一、ORACLE資料庫性能優化工具

常用的資料庫性能優化工具有：

ORACLE資料庫在線數據字典，ORACLE在線數據字典能夠反映出ORACLE動態運行情況，對於調整資料庫性能是很有幫助的。

操作系統工具，例如UNIX操作系統的vmstat，iostat等命令可以查看到系統系統級內存和硬碟I/O的使用情況，這些工具對於管理員弄清出系統瓶頸出現在什麼地方有時候很有用。

SQL語言跟蹤工具(SQLTRACEFACILITY)，SQL語言跟蹤工具可以記錄SQL語句的執行情況，管理員可以使用虛擬表來調整實例，使用SQL語句跟蹤文件調整應用程序性能。SQL語言跟蹤工具將結果輸出成一個操作系統的文件，管理員可以使用TKPROF工具查看這些文件。

ORACLEEnterpriseManager(OEM)，這是一個圖形的用戶管理界面，用戶可以使用它方便地進行資料庫管理而不必記住復雜的ORACLE資料庫管理的命令。

EXPLAINPLAN——SQL語言優化命令，使用這個命令可以幫助程序員寫出高效的SQL語言。

二、ORACLE資料庫的系統性能評估

信息系統的類型不同，需要關注的資料庫參數也是不同的。資料庫管理員需要根據自己的信息系統的類型著重考慮不同的資料庫參數。

1、在線事務處理信息系統(OLTP)，這種類型的信息系統一般需要有大量的Insert、Update操作，典型的系統包括民航機票發售系統、銀行儲蓄系統等。OLTP系統需要保證資料庫的並發性、可靠性和最終用戶的速度，這類系統使用的ORACLE資料庫需要主要考慮下述參數：

資料庫回滾段是否足夠?

是否需要建立ORACLE資料庫索引、聚集、散列?

系統全局區(SGA)大小是否足夠?

SQL語句是否高效?

2、數據倉庫系統(DataWarehousing)，這種信息系統的主要任務是從ORACLE的海量數據中進行查詢，得到數據之間的某些規律。資料庫管理員需要為這種類型的ORACLE資料庫著重考慮下述參數：

是否採用B*-索引或者bitmap索引?

是否採用並行SQL查詢以提高查詢效率?

是否採用PL/SQL函數編寫存儲過程?

有必要的話，需要建立並行資料庫提高資料庫的查詢效率

三、SQL語句的調整原則

SQL語言是一種靈活的語言，相同的功能可以使用不同的語句來實現，但是語句的執行效率是很不相同的。程序員可以使用EXPLAINPLAN語句來比較各種實現方案，並選出最優的實現方案。總得來講，程序員寫SQL語句需要滿足考慮如下規則：

1、盡量使用索引。試比較下面兩條SQL語句：

語句A：SELECTdname,

(SELECTdeptnoFROMemp);

語句B：SELECTdname,deptnoFROMdeptWHERENOTEXISTS

(SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno);

這兩條查詢語句實現的結果是相同的，但是執行語句A的時候，ORACLE會對整個emp表進行掃描，沒有使用建立在emp表上的deptno索引，執行語句B的時候，由於在子查詢中使用了聯合查詢，ORACLE只是對emp表進行的部分數據掃描，並利用了deptno列的索引，所以語句B的效率要比語句A的效率高一些。

2、選擇聯合查詢的聯合次序。考慮下面的例子：

SELECTstuffFROMtabaa,tabbb,tabcc

WHEREa.acolbetween:alowand:ahigh

ANDb.bcolbetween:blowand:bhigh

ANDc.ccolbetween:clowand:chigh

ANDa.key1=b.key1

AMDa.key2=c.key2;

這個SQL例子中，程序員首先需要選擇要查詢的主表，因為主表要進行整個表數據的掃描，所以主表應該數據量最小，所以例子中表A的acol列的范圍應該比表B和表C相應列的范圍小。

3、在子查詢中慎重使用IN或者NOTIN語句，使用where(NOT)exists的效果要好的多。

4、慎重使用視圖的聯合查詢，尤其是比較復雜的視圖之間的聯合查詢。一般對視圖的查詢最好都分解為對數據表的直接查詢效果要好一些。

5、可以在參數文件中設置SHARED_POOL_RESERVED_SIZE參數，這個參數在SGA共享池中保留一個連續的內存空間，連續的內存空間有益於存放大的SQL程序包。

6、ORACLE公司提供的DBMS_SHARED_POOL程序可以幫助程序員將某些經常使用的存儲過程「釘」在SQL區中而不被換出內存，程序員對於經常使用並且佔用內存很多的存儲過程「釘」到內存中有利於提高最終用戶的響應時間。

四、CPU參數的調整

CPU是伺服器的一項重要資源，伺服器良好的工作狀態是在工作高峰時CPU的使用率在90%以上。如果空閑時間CPU使用率就在90%以上，說明伺服器缺乏CPU資源，如果工作高峰時CPU使用率仍然很低，說明伺服器CPU資源還比較富餘。

使用操作相同命令可以看到CPU的使用情況，一般UNIX操作系統的伺服器，可以使用sar_u命令查看CPU的使用率，NT操作系統的伺服器，可以使用NT的性能管理器來查看CPU的使用率。

資料庫管理員可以通過查看v$sysstat數據字典中「CPUusedbythissession」統計項得知ORACLE資料庫使用的CPU時間，查看「OSUserlevelCPUtime」統計項得知操作系統用戶態下的CPU時間，查看「OSSystemcallCPUtime」統計項得知操作系統系統態下的CPU時間，操作系統總的CPU時間就是用戶態和系統態時間之和，如果ORACLE資料庫使用的CPU時間占操作系統總的CPU時間90%以上，說明伺服器CPU基本上被ORACLE資料庫使用著，這是合理，反之，說明伺服器CPU被其它程序佔用過多，ORACLE資料庫無法得到更多的CPU時間。

資料庫管理員還可以通過查看v$sesstat數據字典來獲得當前連接ORACLE資料庫各個會話佔用的CPU時間，從而得知什麼會話耗用伺服器CPU比較多。

出現CPU資源不足的情況是很多的：SQL語句的重解析、低效率的SQL語句、鎖沖突都會引起CPU資源不足。

1、資料庫管理員可以執行下述語句來查看SQL語句的解析情況：

SELECT*FROMV$SYSSTATWHERENAMEIN

('parsetimecpu','parsetimeelapsed','parsecount(hard)');

這里parsetimecpu是系統服務時間，parsetimeelapsed是響應時間，用戶等待時間，waitetime=parsetimeelapsed_parsetimecpu

由此可以得到用戶SQL語句平均解析等待時間=waitetime/parsecount。這個平均等待時間應該接近於0，如果平均解析等待時間過長，資料庫管理員可以通過下述語句

SELECTSQL_TEXT,PARSE_CALLS,EXECUTIONSFROMV$SQLAREA

ORDERBYPARSE_CALLS;

來發現是什麼SQL語句解析效率比較低。程序員可以優化這些語句，或者增加ORACLE參數SESSION_CACHED_CURSORS的值。

2、資料庫管理員還可以通過下述語句：

SELECTBUFFER_GETS,EXECUTIONS,SQL_TEXTFROMV$SQLAREA;

查看低效率的SQL語句，優化這些語句也有助於提高CPU的利用率。

3、資料庫管理員可以通過v$system_event數據字典中的「latchfree」統計項查看ORACLE資料庫的沖突情況，如果沒有沖突的話，latchfree查詢出來沒有結果。如果沖突太大的話，資料庫管理員可以降低spin_count參數值，來消除高的CPU使用率。

五、內存參數的調整

內存參數的調整主要是指ORACLE資料庫的系統全局區(SGA)的調整。SGA主要由三部分構成：共享池、數據緩沖區、日誌緩沖區。

1、共享池由兩部分構成：共享SQL區和數據字典緩沖區，共享SQL區是存放用戶SQL命令的區域，數據字典緩沖區存放資料庫運行的動態信息。資料庫管理員通過執行下述語句：

select(sum(pins-reloads))/sum(pins)"LibCache"fromv$librarycache;

來查看共享SQL區的使用率。這個使用率應該在90%以上，否則需要增加共享池的大小。資料庫管理員還可以執行下述語句：

select(sum(gets-getmisses-usage-fixed))/sum(gets)"RowCache"fromv$rowcache;

查看數據字典緩沖區的使用率，這個使用率也應該在90%以上，否則需要增加共享池的大小。

2、數據緩沖區。資料庫管理員可以通過下述語句：

SELECTname,valueFROMv$sysstatWHEREnameIN('dbblockgets','consistentgets','physicalreads');

來查看資料庫數據緩沖區的使用情況。查詢出來的結果可以計算出來數據緩沖區的使用命中率=1-(physicalreads/(dbblockgets+consistentgets))。

這個命中率應該在90%以上，否則需要增加數據緩沖區的大小。

3、日誌緩沖區。資料庫管理員可以通過執行下述語句：

selectname,valuefromv$sysstatwherenamein('redoentries','redologspacerequests');

查看日誌緩沖區的使用情況。查詢出的結果可以計算出日誌緩沖區的申請失敗率：

申請失敗率=requests/entries，申請失敗率應該接近於0，否則說明日誌緩沖區開設太小，需要增加ORACLE資料庫的日誌緩沖區。

昆明北大青鳥java培訓班轉載自網路如有侵權請聯系我們感謝您的關注謝謝支持