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

发表于:2016-05-04来源:infoq作者:Andreas Grabner and点击数: 标签:数据库
诊断糟糕的数据库访问模式 在对应用程序进行问题诊断时,我通常总要检查几个数据库访问模式。我会逐个分析应用的请求,并将这些问题分别放入以下

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

  在对应用程序进行问题诊断时,我通常总要检查几个数据库访问模式。我会逐个分析应用的请求,并将这些问题分别放入以下这个“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