TSQL優化總結與TomcatJDBC連接池介紹馬同光/2015.05.20主要內容1SelectMethod=Cursor2TSQL優化目標回顧億行、千萬行數據級別表43TomcatJDBC連接池1.

SelectMethod=Cursor

數據源context.xml中的配置：<Resourcename="jdbc/faq_write_jiaowu"auth="Container"type="javax.sql.DataSource"maxActive="10"maxIdle="10"maxWait="5000"driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"url="jdbc:sqlserver://50:1433;DatabaseName=ACCjiaowu;SelectMethod=Cursor;user=sa;password=cailiqiang"/>SQLServer端：declare@p1intset@p1=1073765208declare@p2intset@p2=180183227declare@p7intset@p7=-1execsp_cursorprepexec@p1output,@p2output,NULL,N'selectmajorID,shortNamefromfaq_majorwith(nolock)wheremajorIDin(72,73,74,75,76,77,78)orderbymajorID',16,8193,@p7outputselect@p1,@p2,@p7declare@p1intset@p1=1073742743declare@p2intset@p2=180153691declare@p7intset@p7=-1execsp_cursorprepexec@p1output,@p2output,NULL,N'selectzc.centerIDfromQZ_ZIKAO_CENTERzcinnerjoinQZ_CENTERconc.centerID=zc.centerIDwherec.openStatus=1',16,8193,@p7outputselect@p1,@p2,@p7sp_cursoropen作用：以利用服務器端的游標加快速度使用情況：1.執行多個Statements的操作的時候用

2.需要手動使用事務的時候使用缺點：它不僅會遍歷where條件檢索的數據，甚至有時會掃描整個表的數據，然后再獲取所需查詢的數據。sp_cursoropen帶來的所謂“加速”效果并不理想，甚至還會引發sqlserver的表掃描，資源消耗非常大。2.

TSQL優化目標回顧1.第一階段①處理“列與參數類型不匹配”，執行次數千萬級別、五百萬級別的SQL。②處理“平均CPU耗時”1秒以上的SQL。③處理“平均邏輯讀”一萬以上的SQL。④處理WHERE語句中對日期字段進行函數操作的SQL。參考：

65:8080/site/developer/sqlserver/youhua/advice.html2.第二階段①對于CPU耗時長、平均邏輯讀高的SQL語句，為對應的表添加合適的索引。②統一業務字段在不同表中的數據類型。③處理“列與參數類型不匹配”，執行次數百萬級別的SQL。④處理“平均CPU耗時”500毫秒以上的SQL。⑤處理“平均邏輯讀”3000以上的SQL。3.第三階段①處理in語句參數過的SQL。這里主要是獲取講義內容的SQL。②處理列類型char/varchar向參數類型nvarchar(4000)隱式轉換的問題。3.億行、千萬行數據級別表TSQL慢查詢報告中，涉及到億行數據級別的表要優先優化。同時，也要同步優化醫學網、建設網等等網站。這些表多數是用戶相關的表（如表Cware_TimeKcjy_User）。

與用戶相關的表，隨著訪問量的增加、時間的積累，很容易達到億行、千萬數據級別。

①字段盡量使用窄字段、非空。

②避免實時聚合查詢統計結果。統計信息可以使用單獨的表。

③where子句使用SARG(SearchableARGuments)語句。4.TomcatJDBCConnectionPoolTomcat7的JDBC連接池實現類為org.apache.tomcat.jdbc.pool，作為替換commons-dbcp的方案。替換commons-dbcp的原因如下：

1.commons-dbcp是單線程的，為了線程安全，就要瑣住整個連接池，查詢驗證階段也要瑣住整個連接池。

2.commons-dbcp速度慢，性能差，多CPU的環境下單線程運行，不支持高并發，在JAVA6中也不能解決速度和并發的問題。

3.commons-dbcp實現復雜，超過60個類。tomcat-jdbc-pool核心只有8個類，修改更加簡單，只需運行連接池本身，易測試。

4.commons-dbcp應用靜態接口，就意味著并不能用JDK1.6編譯它，在JDK1.6/1.7中運行時，即使驅動程序支持，所有沒有實現的方法也都會拋出NoSuchMethodException異常。

5.commons-dbcp幾乎停滯，很少更新。

6.作為一個連接池的簡單實現不值得重寫超過60個類。參考：/tomcat-7.0-doc/jdbc-pool.html

/s/blog_454fbf7401012m0h.html

http://286./blog/1920923?utm_source=tuicool

7.Tomcatjdbc連接池實現了一個commons-dbcp沒有的公平算法，并且比commons-dbcp性能更好。

8.Tomcatjdbc連接池實現了異步獲取連接，也不需增加額外的線程。

9.Tomcatjdbc連接池是一個Tomcat的模塊，依賴于TomcatJULI(Tomcat日志框架)10.使用javax.sql.PooledConnection接口獲取連接。

11.饑餓算法。如果連接池空了，同時一個線程要獲得連接，當一個連接返回到連接池，連接池會將正確的線程喚醒。除了commons-dbcp連接池，還有其它可以選擇的方案，如c3p0,bonecp等,與這些連接池實現相比，Tomcatjdbcpool更突出的功能體現在：

1.支持多核系統，提供更好的高并發性能。

2.接口動態實現，運行時環境支持java.sql和javax.sql接口，可以使用低版本JDK編譯。

3.無需每次使用連接時都驗證連接，可以在獲取或返回連接時驗證，不用比設置的間隔時間更頻繁。

4.當數據庫連接建立時，一個可設置的查詢將運行一次。這對保持連接建立整個時間中的會話十分有用。

5.可以自定義攔截器增強功能。可定義攔截器來收集查詢統計，緩存會話狀態，重新連接，重新查詢，緩存查詢結果等。

6.高性能

7.極其簡單，由于非常簡單的實現，源程序行數和文件數很少，相比c3p0的200多個源程序文件，Tomcatjdbc只有8個核心源文件，關于連接池的部分只有4個文件。這樣更容易追溯和修改Bug。減少復雜性就是起初開發的一個焦點。

8.異步獲取連接，可將連接請求形成隊列，系統返回Future<Connection>9.更好的空閑連接處理，應用更優化的算法調整連接池大小和連接的釋放。

10.用戶來決定當連接池滿了在什么時刻釋放連接，或者直接設置一個超時的閥值。

11.釋放連接定時器將會在查詢時重置。允許一個使用很長時間的連接不超時。這個功能由ResetAbandonedTimer完成。

12.在連接一定長時間后關閉連接。時間與返回連接池的時間相似。

13.當連接要被釋放時，將得到JMX通知并且記錄整個日志。這和removeAbandonedTimeout相似，但是只輸出信息，不做任何操作。使用suspectTimeout屬性完成設置。

14.可以從java.sql.Driver，javax.sql.DataSource或者javax.sql.XADataSource中取得連接，使用dataSource和dataSourceJNDI屬性完成。

15.支持XA連接。<Resourcename="jdbc/sqlserver_courseware"auth="Container"type="javax.sql.DataSource"factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"testWhileIdle="true"testOnBorrow="true"testOnReturn="false"validationQuery="SELECT1"validationInterval="30000"timeBetweenEvictionRunsMillis="600000"maxActive="150"maxIdle="150"maxWait="3000"initialSize="10"removeAbandonedTimeout="60"removeAbandoned="true"logAbandoned="true"numTestsPerEvictionRun="10"minEvictableIdleTimeMillis="720000"jmxEnabl