诊断Java代码中常见的数据库性能热点问题(2)

　　诊断糟糕的数据库访问模式

　　在对应用程序进行问题诊断时，我通常总要检查几个数据库访问模式。我会逐个分析应用的请求，并将这些问题分别放入以下这个“DB问题模式”的分类表中：

　　过多的SQL执行(Excessive SQLs)：执行大量(大于500)不同的SQL语句

　　N+1次查询问题(N+1 Query)：多次(大于20)执行相同的SQL语句

　　单一SQL语句执行缓慢(Slow Single SQL)：某个单一的SQL语句执行时间占据了响应时间的80%以上

　　数据驱动问题(Data-Driven)：同样的请求，由于输入参数的不同，会执行不同的SQL语句

　　数据库繁忙(Database Heavy)：数据库执行的总体时间占据总体响应时间的60%以上

　　未经预处理的语句(Unprepared Statements)：在执行相同的SQL时未对语句进行预处理

　　连接池资源用光(Pool Exhaustion)：由于连接获取时间过长所导致(getConnection的时间超过了executeStatement)

　　低效的连接池访问(Inefficient Pool Access)：对连接池的访问次数过多(对getConnection的调用超过了executeStatement调用次数的50%)

　　数据库服务服务器超负荷(Overloaded Database Server)：来自各个应用的请求过多，造成了数据库服务器超负荷

　　示例1：自主设计的O/R映射器产生了过多的SQL

　　我的第一个示例是一个web应用程序，它能够提供某幢大楼中的会议室信息。会议室的信息都保存在某个数据库中，每次当用户生成会议室信息的报表时，就会调用某个自定义的数据访问层以访问该数据库。

　　在对个别请求进行分析时，我总是从所谓的事务流(Transaction Flow)着手检查。事务流是一种可视化选项，可展现出应用程序处理请求的过程。对于会议室信息报表这个请求来说，可以看到，该请求首先进入web服务器层(图左)、随后进入应用服务层(图中)，然后对数据层发起调用(图右)。这些层之间的“链接”表现了这些层之间的交互次数，例如这个单一的请求执行了多少次SQL查询。

　　从这个屏幕上我们可以立即发现造成问题的头两种模式，即过多的SQL执行模式以及数据库繁忙模式。让我们来分析一下：

　　(点击放大图像)

　　很容易就可以看出这个请求产生了大量的SQL语句执行，并且造成数据库繁忙效应：它总共执行了24889次SQL!花费了40.27秒(占整个请求时间的66.51%)才完成整个执行过程!

　　如果我们对个别的SQL语句进行分析，将发现这个请求还有另外的问题，即N+1 次查询问题以及低效的连接池访问(下文将进行详细讨论)：

　　(点击放大图像)

　　这种糟糕的访问模式是无法通过对数据库的索引进行优化而解决的。

　　我已经无数次看到这种问题发生了。应用的逻辑需要对某个对象列表进行迭代，但它并没有选择使用“贪婪加载”(Eager Loading)方式，则是使用了“延迟加载”(Lazy Loading)方式。这种选择可能来自于O/R映射框架，例如Hibernate或Spring，也可能来自于自主开发的框架，正如上文所述的示例一样。该示例使用了某种自主开发的实现方式，它会加载每个会议室对象，并通过独立的SQL查询语句获取每个会议室的全部属性。每个SQL查询都是在一个向连接池获取的JDBC连接中执行的，然后在每个查询完成之后都会返回。这也解释了为什么该请求会产生12444次set clientname操作，因为Sybase JDBC驱动每次向连接池请求连接时都会提交这一请求。这就是问题所在!其他的JDBC驱动未必会产生set clientname这个调用，你可以查看一下调用getConnection的次数，这同样可反映出这个问题。

　　对于N+1次查询问题本身来说，使用连接查询就可以轻易地避免这一问题。在这个会议室与属性的示例中，可以使用以下连接查询：

　　select r.*, p.*

　　from meeting_rooms as r

　　inner join room_properties as p on p.room_id = r.room_id

　　结果就是整个执行过程只产生了1次查询执行，不再是12000多次了!同时也免除了12000次连接的获取操作以及对“set clientname”的调用。

原文转自：http://www.infoq.com/cn/articles/Diagnosing-Common-Java-Database-Performance-Hotspots