TSQL优化总结与TomcatJDBC连接池介绍

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