TSQL優(yōu)化總結(jié)與TomcatJDBC連接池介紹

TSQL優(yōu)化總結(jié)與TomcatJDBC連接池介紹馬同光/2015.05.20主要內(nèi)容1SelectMethod=Cursor2TSQL優(yōu)化目標(biāo)回顧億行、千萬(wàn)行數(shù)據(jù)級(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í)行多個(gè)Statements的操作的時(shí)候用

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

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

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

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

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

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

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

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

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

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

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

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

6.作為一個(gè)連接池的簡(jiǎn)單實(shí)現(xiàn)不值得重寫(xiě)超過(guò)60個(gè)類(lèi)。參考：/tomcat-7.0-doc/jdbc-pool.html

/s/blog_454fbf7401012m0h.html

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

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

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

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

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

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

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

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

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

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

6.高性能

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

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

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

11.釋放連接定時(shí)器將會(huì)在查詢時(shí)重置。允許一個(gè)使用很長(zhǎng)時(shí)間的連接不超時(shí)。這個(gè)功能由ResetAbandonedTimer完成。

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

13.當(dāng)連接要被釋放時(shí)，將得到JMX通知并且記錄整個(gè)日志。這和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