TSQL優(yōu)化總結(jié)與TomcatJDBC連接池介紹馬同光/2015.05.20主要內(nèi)容1SelectMethod=Cursor2TSQL優(yōu)化目標(biāo)回顧億行、千萬行數(shù)據(jù)級別表43TomcatJDBC連接池1.

SelectMethod=Cursor

數(shù)據(jù)源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作用：以利用服務(wù)器端的游標(biāo)加快速度使用情況：1.執(zhí)行多個Statements的操作的時候用

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

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

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

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

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

②避免實時聚合查詢統(tǒng)計結(jié)果。統(tǒng)計信息可以使用單獨的表。

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

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

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

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

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

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

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

/s/blog_454fbf7401012m0h.html

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

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

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

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

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

1.支持多核系統(tǒng)，提供更好的高并發(fā)性能。

2.接口動態(tài)實現(xiàn)，運行時環(huán)境支持java.sql和javax.sql接口，可以使用低版本JDK編譯。

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

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

5.可以自定義攔截器增強功能?？啥x攔截器來收集查詢統(tǒng)計，緩存會話狀態(tài)，重新連接，重新查詢，緩存查詢結(jié)果等。

6.高性能

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

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

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

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

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

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

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