存儲過程性能優化

① 資料庫性能優化有哪些措施

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培訓班轉載自網路如有侵權請聯系我們感謝您的關注謝謝支持

② 學習如何創建一個簡單的存儲過程掌握存儲過程的基本原理和實踐技巧

它可以將一系列的SQL語句封裝成一個可重復執行的單元、存儲過程是資料庫中一種重要的對象、提高資料庫的性能和安全性。並給出具體的步驟和注意事項，本文將為您詳細介紹如何創建一個簡單的存儲過程。

1.存儲過程是什麼？

它們被命名並存儲在資料庫中、可以像調用函數一樣執行和調用，存儲過程是一組預定義的SQL語句。

2.存儲過程的優勢

提高性能、提供數據安全性，存儲過程可以減少網路傳輸開銷，簡化應用開發等。

3.存儲過程的創建步驟

a.創建存儲過程的語法格式：CREATEPROCEDUREprocere_name(parameters)ASBEGIN...END

b.編寫存儲過程的具體邏輯和SQL語句

c.執行CREATEPROCEDURE語句來創建存儲過程

4.存儲過程參數的使用

a.IN參數：傳遞值給存儲過程

b.OUT參數：從存儲過程返回值給調用者

c.INOUT參數：既可以傳遞值給存儲過程，又可以從存儲過程返回值給調用者

5.存儲過程的異常處理

，保證數據的完整性和一致性，、使用TRY，CATCH塊來捕捉和處理存儲過程中的異常情況。

6.存儲過程的調試方法

a.使用PRINT語句輸出調試信息

b.使用SELECT語句查詢中間結果

c.使用SQLServerManagementStudio提供的調試功能進行斷點調試

7.存儲過程的執行和調用

a.執行存儲過程的語法：EXECprocere_name

b.調用存儲過程並傳遞參數的語法：EXECprocere_nameparameter_values

8.存儲過程的修改和刪除

a.修改存儲過程的語法：ALTERPROCEDUREprocere_name

b.刪除存儲過程的語法：DROPPROCEDUREprocere_name

9.存儲過程的注意事項

a.使用明確的命名規則來命名存儲過程

b.注釋存儲過程中的重要代碼段

c.針對不同的資料庫平台，存儲過程的語法可能有所不同

10.存儲過程的性能優化

a.避免在存儲過程中執行耗時長的操作

b.適時使用臨時表來優化查詢性能

c.避免在存儲過程中使用不必要的循環

11.存儲過程的安全性考慮

a.對存儲過程進行訪問控制，限制只有授權用戶能夠執行

b.避免在存儲過程中直接使用用戶輸入的值，防止SQL注入攻擊

12.存儲過程的實際應用場景

a.資料庫備份和恢復

b.資料庫批量處理

c.資料庫許可權管理

13.存儲過程的常見問題和解決方法

a.存儲過程執行時間過長：優化查詢語句、增加索引等

b.存儲過程出現死鎖：合理設計事務和鎖定策略

c.存儲過程返回結果不正確：檢查存儲過程邏輯和參數傳遞是否正確

14.存儲過程與函數的區別與聯系

a.存儲過程是用於執行一系列操作的，而函數是用於返回單個值的

b.存儲過程可以調用函數，而函數不能直接調用存儲過程

15.存儲過程的進一步學習資源推薦

幫助讀者深入學習和掌握存儲過程的更高級技術，推薦一些書籍，在線教程和博客。

您已經了解了如何創建一個簡單的存儲過程、通過本文的學習，並掌握了存儲過程的基本原理和實踐技巧。讓您更加熟練地應用存儲過程來提高資料庫的性能和安全性、希望本文能為您提供幫助、存儲過程在資料庫開發中具有重要作用。

通過實例學習如何編寫存儲過程

它能夠接受參數並返回結果、存儲過程是一種在資料庫中存儲的可重復使用的代碼塊。我們將學習如何創建一個簡單的存儲過程，在本文中，並通過一個實例來了解它的用途和編寫方法。

段落1什麼是存儲過程

並存儲在資料庫中以供反復使用、存儲過程是一組預編譯的SQL語句，它們被封裝在一個命名塊中。輸出參數以及一系列的SQL語句、它可以包含輸入參數，用於執行特定的資料庫操作。

段落2為什麼使用存儲過程

使用存儲過程可以提高資料庫性能和安全性。減輕了網路負載、存儲過程通過減少與資料庫的通信次數。並將其封裝在資料庫中，存儲過程還可以實現復雜的業務邏輯，確保數據的安全性和一致性。

段落3如何創建一個簡單的存儲過程

我們需要在資料庫管理系統中選擇一個合適的編程語言來編寫存儲過程。T，PL/SQL，常用的選擇包括SQL-SQL等。我們需要定義存儲過程的名稱，輸入參數和輸出參數，接下來（如果有的話）。以實現特定的資料庫操作，我們可以在存儲過程中編寫一系列的SQL語句。

段落4存儲過程的輸入參數和輸出參數

用於在執行時傳遞數據給存儲過程、存儲過程可以接受輸入參數。如整數、字元串，輸入參數可以是各種數據類型，日期等。以便將結果返回給調用方，存儲過程也可以返回輸出參數，同時。

段落5存儲過程的控制流程

循環等，存儲過程可以包含控制結構，以實現復雜的業務邏輯，如條件判斷。我們可以根據不同的條件執行不同的SQL語句、實現更靈活的資料庫操作，通過使用這些控制結構。

段落6存儲過程的錯誤處理

我們需要考慮錯誤處理機制，在編寫存儲過程時。保證存儲過程的健壯性和可靠性、我們可以捕獲和處理可能出現的錯誤、通過使用異常處理語句。

段落7存儲過程的優化技巧

我們可以採用一些優化技巧、為了提高存儲過程的性能。選擇適當的存儲過程參數類型等，合理使用索引、避免大量的數據傳輸。這些優化技巧可以顯著提升存儲過程的執行效率。

段落8如何調用存儲過程

我們需要使用特定的語法來傳遞參數並獲取結果，在調用存儲過程時。因此我們需要根據實際情況來選擇適合的方法，不同的資料庫管理系統可能有不同的調用語法。

段落9存儲過程的應用場景

存儲過程在實際開發中有廣泛的應用場景。數據轉換，復雜查詢等，它可以用於數據清洗。提高開發效率和數據處理能力，通過使用存儲過程、我們可以將這些復雜的操作封裝在資料庫中。

段落10存儲過程的安全性

存儲過程可以增加資料庫的安全性。我們可以控制用戶對資料庫的操作范圍，通過限制對存儲過程的訪問許可權，並確保數據的安全性和機密性。

段落11存儲過程的維護和更新

我們可能需要對存儲過程進行維護和更新，隨著業務需求的變化。以確保修改後的存儲過程不會破壞現有的業務邏輯和數據完整性，我們需要謹慎處理，在進行維護和更新時。

段落12存儲過程的調試技巧

我們可能會遇到錯誤、在編寫存儲過程時。如列印調試信息，我們可以使用調試技巧，使用斷點等，為了排除這些錯誤。這些技巧可以幫助我們找出問題並進行修復。

段落13存儲過程的版本控制

以確保在不同的環境中能夠正確地部署和使用，存儲過程也需要進行版本控制。並進行合理的版本管理，我們可以跟蹤和管理存儲過程的變更歷史，通過使用版本控制工具。

段落14存儲過程的性能監控

我們可以使用性能監控工具來對其進行監控和優化，為了保證存儲過程的性能。並根據這些指標進行性能優化，資源佔用等指標、通過監控工具、我們可以了解存儲過程的執行時間。

段落15

我們了解了存儲過程的基本概念和使用方法，通過本文。優化技巧和安全性等方面的知識、並了解了它的應用場景、我們學習了如何創建一個簡單的存儲過程。為資料庫開發和管理工作提供更加高效和可靠的解決方案，通過不斷實踐和學習，我們可以進一步提升自己的存儲過程編寫能力。

以及存儲過程的使用、本文介紹了如何創建一個簡單的存儲過程，優化和安全性等方面的知識。並在實際開發中靈活應用，我們可以掌握存儲過程編寫的基本技巧，通過學習和實踐。希望本文對讀者在資料庫開發和管理方面有所幫助，存儲過程是提高資料庫性能和安全性的重要工具。

③ 為什麼這個存儲過程執行的性能這么差

大家都在討論關於資料庫優化方面的東東，剛好參與開發了一個數據倉庫方面的項目，以下的一點東西算是資料庫優化方面的學習 實戰的一些心得體會了，拿出來大家共享。歡迎批評指正阿！

SQL語句：
是對資料庫(數據)進行操作的惟一途徑；
消耗了70%~90%的資料庫資源；獨立於程序設計邏輯，相對於對程序源代碼的優化，對SQL語句的優化在時間成本和風險上的代價都很低；
可以有不同的寫法；易學，難精通。

SQL優化：
固定的SQL書寫習慣，相同的查詢盡量保持相同，存儲過程的效率較高。
應該編寫與其格式一致的語句，包括字母的大小寫、標點符號、換行的位置等都要一致

ORACLE優化器：
在任何可能的時候都會對表達式進行評估，並且把特定的語法結構轉換成等價的結構，這么做的原因是
要麼結果表達式能夠比源表達式具有更快的速度
要麼源表達式只是結果表達式的一個等價語義結構
不同的SQL結構有時具有同樣的操作（例如：= ANY (subquery) and IN (subquery)），ORACLE會把他們映射到一個單一的語義結構。

1 常量優化：
常量的計算是在語句被優化時一次性完成，而不是在每次執行時。下面是檢索月薪大於2000的的表達式：
sal > 24000/12
sal > 2000
sal*12 > 24000
如果SQL語句包括第一種情況，優化器會簡單地把它轉變成第二種。
優化器不會簡化跨越比較符的表達式，例如第三條語句，鑒於此，應盡量寫用常量跟欄位比較檢索的表達式，而不要將欄位置於表達式當中。否則沒有辦法優化，比如如果sal上有索引，第一和第二就可以使用，第三就難以使用。

2 操作符優化：
優化器把使用LIKE操作符和一個沒有通配符的表達式組成的檢索表達式轉換為一個「=」操作符表達式。
例如：優化器會把表達式ename LIKE 'SMITH'轉換為ename = 'SMITH'
優化器只能轉換涉及到可變長數據類型的表達式，前一個例子中，如果ENAME欄位的類型是CHAR(10)， 那麼優化器將不做任何轉換。
一般來講LIKE比較難以優化。

其中：
~~ IN 操作符優化：
優化器把使用IN比較符的檢索表達式替換為等價的使用「=」和「OR」操作符的檢索表達式。
例如，優化器會把表達式ename IN ('SMITH','KING','JONES')替換為
ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES『

~~ ANY和SOME 操作符優化:
優化器將跟隨值列表的ANY和SOME檢索條件用等價的同等操作符和「OR」組成的表達式替換。
例如，優化器將如下所示的第一條語句用第二條語句替換：
sal > ANY (:first_sal, :second_sal)
sal > :first_sal OR sal > :second_sal
優化器將跟隨子查詢的ANY和SOME檢索條件轉換成由「EXISTS」和一個相應的子查詢組成的檢索表達式。
例如，優化器將如下所示的第一條語句用第二條語句替換：
x > ANY (SELECT sal FROM emp WHERE job = 'ANALYST')
EXISTS (SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal)

~~ ALL操作符優化:
優化器將跟隨值列表的ALL操作符用等價的「=」和「AND」組成的表達式替換。例如：
sal > ALL (:first_sal, :second_sal)表達式會被替換為：
sal > :first_sal AND sal > :second_sal
對於跟隨子查詢的ALL表達式，優化器用ANY和另外一個合適的比較符組成的表達式替換。例如
x > ALL (SELECT sal FROM emp WHERE deptno = 10) 替換為：
NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10))
接下來優化器會把第二個表達式適用ANY表達式的轉換規則轉換為下面的表達式：
NOT EXISTS (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)

~~ BETWEEN 操作符優化:
優化器總是用「>=」和「<=」比較符來等價的代替BETWEEN操作符。
例如：優化器會把表達式sal BETWEEN 2000 AND 3000用sal >= 2000 AND sal <= 3000來代替。

~~ NOT 操作符優化:
優化器總是試圖簡化檢索條件以消除「NOT」邏輯操作符的影響，這將涉及到「NOT」操作符的消除以及代以相應的比較運算符。
例如，優化器將下面的第一條語句用第二條語句代替：
NOT deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
deptno <> (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
通常情況下一個含有NOT操作符的語句有很多不同的寫法，優化器的轉換原則是使「NOT」操作符後邊的子句盡可能的簡單，即使可能會使結果表達式包含了更多的「NOT」操作符。
例如，優化器將如下所示的第一條語句用第二條語句代替：
NOT (sal < 1000 OR comm IS NULL)
NOT sal < 1000 AND comm IS NOT NULL sal >= 1000 AND comm IS NOT NULL

如何編寫高效的SQL:
當然要考慮sql常量的優化和操作符的優化啦，另外，還需要：

1 合理的索引設計：
例：表record有620000行，試看在不同的索引下，下面幾個SQL的運行情況：
語句A
SELECT count(*) FROM record
WHERE date >'19991201' and date < '19991214『 and amount >2000

語句B
SELECT count(*) FROM record
WHERE date >'19990901' and place IN ('BJ','SH')

語句C
SELECT date,sum(amount) FROM record
group by date
1 在date上建有一個非聚集索引
A：(25秒)
B：(27秒)
C：(55秒)
分析：
date上有大量的重復值，在非聚集索引下，數據在物理上隨機存放在數據頁上，在范圍查找時，必須執行一次表掃描才能找到這一范圍內的全部行。
2 在date上的一個聚集索引
A：（14秒）
B：（14秒）
C：（28秒）
分析：
在聚集索引下，數據在物理上按順序在數據頁上，重復值也排列在一起，因而在范圍查找時，可以先找到這個范圍的起末點，且只在這個范圍內掃描數據頁，避免了大范圍掃描，提高了查詢速度。
3 在place，date，amount上的組合索引
A：（26秒）
C：（27秒）
B：（< 1秒）
分析：
這是一個不很合理的組合索引，因為它的前導列是place，第一和第二條SQL沒有引用place，因此也沒有利用上索引；第三個SQL使用了place，且引用的所有列都包含在組合索引中，形成了索引覆蓋，所以它的速度是非常快的。
4 在date，place，amount上的組合索引
A： (< 1秒)
B：（< 1秒）
C：（11秒）
分析：
這是一個合理的組合索引。它將date作為前導列，使每個SQL都可以利用索引，並且在第一和第三個SQL中形成了索引覆蓋，因而性能達到了最優。

總結1
預設情況下建立的索引是非聚集索引，但有時它並不是最佳的；合理的索引設計要建立在對各種查詢的分析和預測上。一般來說：
有大量重復值、且經常有范圍查詢（between, >,< ，>=,< =）和order by、group by發生的列，考慮建立聚集索引；
經 常同時存取多列，且每列都含有重復值可考慮建立組合索引；在條件表達式中經常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在雇員 表的「性別」列上只有「男」與「女」兩個不同值，因此就無必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度。
組合索引要盡量使關鍵查詢形成索引覆蓋，其前導列一定是使用最頻繁的列。

2 避免使用不兼容的數據類型：
例如float和INt、char和varchar、bINary和varbINary是不兼容的。數據類型的不兼容可能使優化器無法執行一些本來可以進行的優化操作。例如:
SELECT name FROM employee WHERE salary ＞ 60000
在這條語句中,如salary欄位是money型的,則優化器很難對其進行優化,因為60000是個整型數。我們應當在編程時將整型轉化成為錢幣型,而不要等到運行時轉化。

3 IS NULL 與IS NOT NULL：
不 能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排 除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。任何在WHERE子句中使用is null或is not null的語句優化器是不允 許使用索引的。

4 IN和EXISTS：
EXISTS要遠比IN的效率高。裡面關繫到full table scan和range scan。幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢。
例子：
語句1
SELECT dname, deptno FROM dept
WHERE deptno NOT IN
(SELECT deptno FROM emp);
語句2
SELECT dname, deptno FROM dept
WHERE NOT EXISTS
(SELECT deptno FROM emp
WHERE dept.deptno = emp.deptno);
明顯的，2要比1的執行性能好很多
因為1中對emp進行了full table scan,這是很浪費時間的操作。而且1中沒有用到emp的INdex，
因為沒有WHERE子句。而2中的語句對emp進行的是range scan。

5 IN、OR子句常會使用工作表，使索引失效：
如果不產生大量重復值，可以考慮把子句拆開。拆開的子句中應該包含索引。

6 避免或簡化排序：
應當簡化或避免對大型表進行重復的排序。當能夠利用索引自動以適當的次序產生輸出時，優化器就避免了排序的步驟。以下是一些影響因素：
索引中不包括一個或幾個待排序的列；
group by或order by子句中列的次序與索引的次序不一樣；
排序的列來自不同的表。
為了避免不必要的排序，就要正確地增建索引，合理地合並資料庫表（盡管有時可能影響表的規范化，但相對於效率的提高是值得的）。如果排序不可避免，那麼應當試圖簡化它，如縮小排序的列的范圍等。

7 消除對大型錶行數據的順序存取：
在 嵌套查詢中，對表的順序存取對查詢效率可能產生致命的影響。比如採用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那麼這個查詢就要查詢 10億行數據。避免這種情況的主要方法就是對連接的列進行索引。例如，兩個表：學生表（學號、姓名、年齡??）和選課表（學號、課程號、成績）。如果兩個 表要做連接，就要在「學號」這個連接欄位上建立索引。
還可以使用並集來避免順序存取。盡管在所有的檢查列上都有索引，但某些形式的WHERE子句強迫優化器使用順序存取。下面的查詢將強迫對orders表執行順序操作：
SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
雖然在customer_num和order_num上建有索引，但是在上面的語句中優化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語句：
SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊ FROM orders WHERE order_num=1008
這樣就能利用索引路徑處理查詢。

8 避免相關子查詢：
一個列的標簽同時在主查詢和WHERE子句中的查詢中出現，那麼很可能當主查詢中的列值改變之後，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那麼要在子查詢中過濾掉盡可能多的行。

9 避免困難的正規表達式：
MATCHES和LIKE關鍵字支持通配符匹配，技術上叫正規表達式。但這種匹配特別耗費時間。例如：SELECT ＊ FROM customer WHERE zipcode LIKE 「98_ _ _」
即使在zipcode欄位上建立了索引，在這種情況下也還是採用順序掃描的方式。如果把語句改為SELECT ＊ FROM customer WHERE zipcode >「98000」，在執行查詢時就會利用索引來查詢，顯然會大大提高速度。
另外，還要避免非開始的子串。例如語句：SELECT ＊ FROM customer WHERE zipcode[2，3] >「80」，在WHERE子句中採用了非開始子串，因而這個語句也不會使用索引。

10 不充份的連接條件：
例：表card有7896行，在card_no上有一個非聚集索引，表account有191122行，在account_no上有一個非聚集索引，試看在不同的表連接條件下，兩個SQL的執行情況：
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no
（20秒）
將SQL改為：
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no and a.account_no=b.account_no
（< 1秒）
分析：
在第一個連接條件下，最佳查詢方案是將account作外層表，card作內層表，利用card上的索引，其I/O次數可由以下公式估算為：
外層表account上的22541頁 （外層表account的191122行*內層表card上對應外層表第一行所要查找的3頁）=595907次I/O
在第二個連接條件下，最佳查詢方案是將card作外層表，account作內層表，利用account上的索引，其I/O次數可由以下公式估算為：
外層表card上的1944頁 （外層表card的7896行*內層表account上對應外層表每一行所要查找的4頁）= 33528次I/O
可見，只有充份的連接條件，真正的最佳方案才會被執行。
多表操作在被實際執行前，查詢優化器會根據連接條件，列出幾組可能的連接方案並從中找出系統開銷最小的最佳方案。連接條件要充份考慮帶有索引的表、行數多的表；內外表的選擇可由公式：外層表中的匹配行數*內層表中每一次查找的次數確定，乘積最小為最佳方案。
不可優化的WHERE子句
例1
下列SQL條件語句中的列都建有恰當的索引，但執行速度卻非常慢：
SELECT * FROM record WHERE substrINg(card_no,1,4)='5378'
(13秒)
SELECT * FROM record WHERE amount/30< 1000
（11秒）
SELECT * FROM record WHERE convert(char(10),date,112)='19991201'
（10秒）
分析：
WHERE子句中對列的任何操作結果都是在SQL運行時逐列計算得到的，因此它不得不進行表搜索，而沒有使用該列上面的索引；如果這些結果在查詢編譯時就能得到，那麼就可以被SQL優化器優化，使用索引，避免表搜索，因此將SQL重寫成下面這樣：
SELECT * FROM record WHERE card_no like '5378%'
（< 1秒）
SELECT * FROM record WHERE amount< 1000*30
（< 1秒）
SELECT * FROM record WHERE date= '1999/12/01'
（< 1秒）

11 存儲過程中，採用臨時表優化查詢：
例
1．從parven表中按vendor_num的次序讀數據：
SELECT part_num，vendor_num，price FROM parven ORDER BY vendor_num
INTO temp pv_by_vn
這個語句順序讀parven（50頁），寫一個臨時表（50頁），並排序。假定排序的開銷為200頁，總共是300頁。
2．把臨時表和vendor表連接，把結果輸出到一個臨時表，並按part_num排序：
SELECT pv_by_vn，＊ vendor.vendor_num FROM pv_by_vn，vendor
WHERE pv_by_vn.vendor_num=vendor.vendor_num
ORDER BY pv_by_vn.part_num
INTO TMP pvvn_by_pn
DROP TABLE pv_by_vn
這 個查詢讀取pv_by_vn(50頁)，它通過索引存取vendor表1.5萬次，但由於按vendor_num次序排列，實際上只是通過索引順序地讀 vendor表（40＋2=42頁），輸出的表每頁約95行，共160頁。寫並存取這些頁引發5＊160=800次的讀寫，索引共讀寫892頁。
3．把輸出和part連接得到最後的結果：
SELECT pvvn_by_pn.＊，part.part_desc FROM pvvn_by_pn，part
WHERE pvvn_by_pn.part_num=part.part_num
DROP TABLE pvvn_by_pn
這樣，查詢順序地讀pvvn_by_pn(160頁)，通過索引讀part表1.5萬次，由於建有索引，所以實際上進行1772次磁碟讀寫，優化比例為30∶1。

④ 如何對資料庫性能進行優化

1.資料庫I/O方面硬體性能

最有可能影響性能的是磁碟和網路吞吐量。解決辦法：

• 擴大虛擬內存，並保證有足夠可以擴充的空間

• 把資料庫伺服器上的不必要服務關閉掉

• 把SQL資料庫伺服器的吞吐量調為最大

2.調整資料庫

• 若對該表的查詢頻率比較高，則建立索引。

• 分區（如MySQL，按時間分區）

• 盡量使用固定長度欄位和限制欄位長度（如 varchar(10)）優勢：
  降低物理存儲空間
  提高資料庫處理速度
  附帶校驗資料庫是否合法功能

3.使用存儲過程

應用程序的實現過程中，能夠採用存儲過程實現的對資料庫的操作盡量通過存儲過程來實現。

因為存儲過程是存放在資料庫伺服器上的一次性被設計、編碼、測試，並被再次使用，需要執行該任務的應用可以簡單地執行存儲過程，並且只返回結果集或者數值。

這樣不僅可以使程序模塊化，同時提高響應速度，減少網路流量，並且通過輸入參數接受輸入，使得在應用中完成邏輯的一致性實現。

4.SQL語句方面

建立查詢條件索引僅僅是提高速度的前提條件，響應速度的提高還依賴於對索引的使用。不良的SQL往往來自於不恰當的索引設計、不充份的連接條件和不可優化的where子句。

• 優化sql語句，減少比較次數

• 限制返回條目數(mysql中使用limit)

5.Java方面

• 盡可能的少創造對象

• 合理擺正系統設計的位置。大量數據操作，和少量數據操作一定是分開的。

• 合理利用內存，有的數據要緩存。讓數據流起來，而不是全部讀到內存再處理，而是邊讀取邊處理。