如何诊断Java代码中常见的数据库性能热点问题？
当我在帮助一些开发者或架构师分析及优化 Java 应用程序的性能时，关键往往不在于对个别方法进行微调，以节省一或两微秒的执行时间。虽然对某些软件来说，微秒级的优化确实非常重要，但我认为这并非着眼点所在。我在 2015 年间对数百个应用进行了分析，发现多数性能与可伸缩性问题都来源于糟糕的架构决策、框架的错误配置、错误的数据库访问模式、过量的日志记录，以及由于内存过度消耗而导致的垃圾回收所带来的影响。
在我看来，性能工程的根本在于通过大量的观察，将关键的架构指标、可伸缩性指标以及性能指标关联在一起。通过对每次构建的结果以及不同负载情况下的表现进行分析，以发现系统中的回归缺陷或瓶颈所在。以下图中的仪表板作为示例：
通过将系统负载、响应时间与 SQL 语句的执行次数等指标相关联，可得出某些性能工程方面问题的根本原因。
最上面一张图叫做“层分解”图表，它显示了你的应用中各个逻辑组件（例如 Web Service、数据库访问、业务逻辑、Web 服务器等等）的总体执行时间。红色部分所表示的是某个后端 Web Service 所花费的时间，很明显这里产生了一个组件热点。
我们同时可以发现，该 Web Service 并没有承受异常的负载，因为从第二张图来看，当时应用程序所处理的请求数量这条线比较平稳。一般情况下，整体响应时间多数都耗费在数据层，但这并不代表数据库本身的速度缓慢！我了解，低效的数据库访问往往是造成性能不佳的主要原因，因此通常会结合分析 SQL 语句的执行次数。在这个示例中，已经能够很清楚地看到它与大多数响应时间的峰值是相关的。
我所观察到最常见的问题模式就是糟糕的数据库访问模式，此外还有过于细粒度的服务调用、糟糕的共享数据访问共享、过度的日志记录，以及由内存泄露以及大量的对象创建所导致的垃圾回收影响或是应用程序的崩溃。
在本文中，我将专注于探讨数据库方面的问题，因为我十分确信你的所有应用都是因这些访问模式中的某一种而产生问题的！你可以在市场上已有的各种性能诊断、追踪，或是 APM 工具之间随意选择，不过我所选择的是免费的 Dynatrace Personal License。Java 本身也提供了各种出色的工具，例如 Java Mission Control 等等。许多提供数据访问功能的框架也经常通过其日志输出提供各种诊断选项，例如 Hibernate 或 Spring 等等。
在使用这些跟踪工具时，通常不需要对代码进行任何修改，因为他们都利用了 JVMTI（JVM Tooling Interface）以捕获代码层面的信息，甚至能够跨远程的各层次进行调用追踪，这一点对于分布式、面向（微）服务的应用来说非常实用。你所要做的就是修改你的 JVM 启动命令行选项，以加载这些工具。
某些工具的开发商还提供了与 IDE 的集成功能，你只需简单地表示“在运行时开启 XYZ 性能诊断功能”。我在 YouTube 上做了一个简单的视频指南，演示了如何对在 Eclipse 中启动的应用进行追踪。
https://www.youtube.com/watch?v=unrey8wfq-M&list=PLqt2rd0eew1bmDn54E2_M2uvbhm_WxY_6&index=14
即使你已经发现造成应用整体响应时间过长的主要原因在于数据库，但也不要因此就轻率地指责数据库与 DBA！造成数据库繁忙的原因可能有以下几种：
对数据库的使用过于低效：错误的查询设计、糟糕的应用程序逻辑、对于数据访问框架的配置不正确
糟糕的数据库设计与数据结构：表的关联、缓慢的存储视图、缺失的或错误的索引、过期的表统计信息
不恰当的数据库配置，例如内存、磁盘、表空间、连接池等等
在本文中，我将着重讲解如何在应用程序端将访问数据库所消耗的时间减至最低。
在对应用程序进行问题诊断时，我通常总要检查几个数据库访问模式。我会逐个分析应用的请求，并将这些问题分别放入以下这个“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）：来自各个应用的请求过多，造成了数据库服务器超负荷
我的第一个示例是一个 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”的调用。
据我所知，Hibernate 或其他 O/R 映射器有许多使用者。我想要提醒你们一点，O/R 映射器所提供的延迟加载与贪婪加载选项，以及其他各种缓存层各有其存在的理由。对于特定的用例，需要确保你正确地使用了这些特性与选项。
在下面这个示例中，延迟加载并不是一种好的选择，因为加载 2 千个对象以及他们的属性会导致产生 4 千多次 SQL 查询。考虑到我们总是需要获取所有对象，那么更好的方式是贪婪加载这些对象，然后考虑对他们进行缓存，前提是这些对象不会变更得十分频繁：
在使用 Hibernate 或 Spring 等 O/R 映射器时，需要选择正确的加载与缓存选项。你需要理解他们的幕后工作原理。
大多数 O/R 映射器都会通过日志记录提供优秀的诊断选项，同时也可以查看在线社区中的内容，以了解各种最佳实践。推荐你阅读由 Alois Reitbauer 撰写的一系列博客文章，他曾经在 Hibernate 推出的早些年头对其进行过非常深入的研究。在这系列文章中，他特别强调了如何有效地使用缓存与加载选项。
当数据库引擎完成对某条 SQL 语句的解析，并创建了数据访问的执行计划后，该结果会被保存在数据库中的一个缓存区域中以便重用，而无需重新解析这一语句（语句解析是数据库中最耗费 CPU 时间的操作）。用于在缓存中找到某个查询的键是语句的全文本。这也意味着，如果你调用了 1000 次相同的语句，却为其传了 100 个不同的参数值（例如 where 语句中的值），那么在缓存中就会产生 1000 个不同的条目，而使用了新参数的第 1001 次查询也必须被再次解析。这种工作方式非常低效。
因此，我们提出了“预处理的语句”这一概念：某条语句经过预处理、解析后被保存在缓存中，以占位符的方式表示变量。在这条语句的实际执行过程中，这些占位符会被实际的值所替换，无需再次解析这条语句，可以直接从缓存中找出执行计划。
数据库访问框架通常在这一点上做得很出色，会对查询语句进行预处理。但在自定义代码中，我发现开发者经常会忽略这一点。在以下示例中，只有一小部分 SQL 执行经过了预处理过程：
通过对 SQL 执行次数与已预处理的 SQL 执行次数进行对比，发现了未经预处理的数据库访问的问题
如果你打算自行开发数据库访问代码，请再次确认你正确地调用了 prepareStatement。举例来说，如果你调用某个查询不止 1 次，那么通常来说最好能够使用 PreparedStatement。如果你选择使用框架以访问数据，也请再次确认这些框架的行为，以及在优化和执行所生成的 SQL 时有哪些配置选项可以选择。实现一点最简单的方式是对 executeStatement 与 prepareStatement 执行的次数进行监控。如果你重复对每个 SQL 查询进行相同的监控，那么将很容易地找到优化热点。
我经常发现有些应用会使用默认的连接池大小，例如每个池 10 或 20 个连接。开发者总是会忽略对连接池大小的优化，因为他们没有进行必要的大规模负载测试，也不知道有多少个用户会使用这些新特性，更不了解并行的 DB 访问会导致什么结果。也有可能是从预发布环境转向生产环境的部署时“丢失”了连接池的配置信息，导致生产环境中的配置使用了应用服务器中的默认配置。
通过 JMX 指标信息，能够方便地对连接池的使用情况进行监控。每种应用服务器（Tomcat、JBoss、Websphere 等等）都会提供这些指标，不过有些服务器需要你明确地开启这种特性。下图展示了某个群集中的 WebLogic 服务器的连接池使用情况。你可以看到，在其中三台应用服务器中，“活动的 DB 连接数量”都已经达到最大值。
确保你适当地调整了连接池的大小，不要使用与你期待的负载情况不符的默认设置
出现这一问题的根本原因不在于访问量的峰值。在本文开头部分所介绍的“系统负载 / 响应时间 / 数据库执行次数”这个仪表板中显示，应用并没有产生特别的访问量峰值情况。最终发现，在每天下午 2 点多这个时间段设定了一个运行报表的计划，它需要执行多个运行时间相当长的 UPDATE 语句，每个语句都使用了不同的连接。这会在几分钟内阻塞其他连接，导致了应用程序在“正常的”访问量下出现性能问题，因为用户的请求无法获得数据库的连接：
个别的 SQL 执行阻塞了其他连接达几分钟，造成了连接池资源消耗殆尽的问题
如果你已经了解到某些请求会使连接挂起一段较长的时间，你可以选择以下几种方案：
将这些请求发送至独立的服务器上，避免影响其他使用者
重新设定其执行时间，只在不会影响到其他人的时间段才执行
增加连接池大小，确保在正常的访问量下有足够的连接可用
不过，首先你要确保对这些查询进行优化。通过分析 SQL 查询执行计划，以找出哪些操作是最耗时的。如今，大多数 APM 工具都能够让你以某种方式获取某个 SQL 语句的执行计划。如果没有可用的工具，最简单的方式就是使用数据库的命令行工具，或者咨询某个 DBA，让他帮助你生成执行计划。
通过学习 SQL 查询执行计划，对你的 SQL 语句进行优化
执行计划能够显示出 DB 引擎处理 SQL 语句的方式。造成 SQL 语句执行缓慢的原因多种多样，不仅仅限于缺少索引或是使用索引的方式不对，很多情况下是因为设计、结构或连接查询所造成的。如果你并非 SQL 方面的专家，可以向 DBA 或 SQL 大牛求助。
除了对各个请求进行分析，以指出这些问题模式之外，我同样也会关注当某个应用程序在负载情况下的长期趋势。除了我在本文开头为你展示的仪表板之外，我也会指出数据驱动行为的变化，并对数据缓存是否正确运行进行验证。
检查点 1：由于数据缓存的存在，对 DB 的访问次数应当逐渐减少
下面这张图表展示了 SQL 语句执行的平均次数（绿色）以及 SQL 语句执行的总次数（蓝色）。我们为应用进行了一次两小时的性能测试，保持负载始终处于较高水平。我所期望的结果是平均次数逐渐减少，而总次数则趋向平稳。因为按照我的假设，从 DB 所获取的数据大多数是静态的，或是会被缓存在某个不同的层。
如果你的应用表现不符合这一预期，那么可能是遇到了数据驱动的性能问题，或是产生了缓存问题
假设如我之前所展示的一样，你的应用中产生了常见的 N+1 次查询问题。那么随着终端用户在 DB 中产生越来越多的数据，应用程序所产生的 SQL 平均次数也将不断提高，因为这些查询所返回的数据也会越来越多！因此，请务必注意这些数字！
检查点 2：按类别指出 SQL 访问模式
示例 4 表现了某个后台报表应用在每天下午 2 点执行所造成的问题，与之类似，我同样也会关注 SQL 访问随着时间变化的模式。我所关注的不仅包括总执行时间，同时也包括 SELECT、INSERT、UPDATE 与 DELETE 的执行次数。这样一来，我就能够指出是否在某个时间段内会进行一些特别的活动，例如通过后台作业对大批数据进行更新。
通过观察总执行时间，以及 SELECT、INSERT、UPDATE 与 DELETE 的执行次数，了解应用的数据库访问行为
进行大量更新操作的批处理作业的执行需要一段时间才能完成，尤其对于包含大量行的表来说更为明显。如果整张表因此被锁住，那么其他需要对这张表、哪怕只是对其中某些行进行更新的请求都必须等待锁被释放。你应考虑在没有其他用户在线的时间段运行这些作业，或实现某种不同的加锁逻辑，实现对单个行的加锁、更新以及释放操作。
检查点 3：数据库实例的运行状态
在本文中，我着重分析的数据库性能问题多数与数据库服务器本身是否缓慢是无关的，而主要是由使用了糟糕的数据库访问模式（N+1 次查询问题、未经预处理的语句等等）的应用程序代码、或是配置错误（低效的连接池访问、数据驱动问题）所导致的问题。
但是，如果我们完全忽略了数据库本身，那也是不明智的。因此，我总是会对关键的数据库性能指标进行检查。大多数数据库都会通过特殊的系统表提供丰富的性能信息，比如 Oracle 就会提供某些 v$ 表以及视图，以访问关键的数据库性能指标（会话、等待时间、解析时间、执行时间等等），或是表锁以及运行时间较慢的 SQL 等信息，这些信息来自于使用这个共享的数据库实例的各个应用程序。
我在进行数据库健康检查时通常会观察两个仪表板，你可以在此看到来自于这些性能表中的指标数据：
观察数据库是处于健康状态，还是由于共享该数据库实例的应用产生过多的负载而产生了影响。
通过表锁等信息，判断是否有某个正在执行中的 SQL 语句对服务器乃至你的应用造成了负面影响
在我为你介绍分析关键数据库指标以及用例的一些新点子之前，我希望首先能够弥补一个缺失的主题，而这一点是我们都应当考虑到的，那就是自动化！
我建议你不要手动地执行这些检查步骤，而是通过持续集成工具检查这些指标，将这一步骤与单元测试、集成测试、REST API 或其他类型的功能性测试等步骤结合在一起。如果你已经设计出一套测试用例集，用于检查各种 REST API 或新特性的功能，那么为什么不在每次构建的测试执行期间去捕获这些指标呢？这种方式可以带来以下益处：
让代码评审过程专注于这些指标，而不是翻来覆去地阅读每一行代码
如果某个代码签入导致了这种问题，则发出通知
下面这幅屏幕截图展示了每次构建与每次测试时对这些指标的追踪，并在其表现异常时发出警告。你可以将这些指标集成在你的构建管道中，并且当某个代码变更造成影响时通过通知信息了解情况，随后立即修复这一问题，避免当代码发布到生产环境时产生系统崩溃的情况。
在你的持续集成流程中加入这些指标，并对指标的变化进行观察，以自动地找出各种糟糕的数据库访问模式！
在本文中，我们专注的是数据库方面的热点问题。但在我的工作过程中，我也在其他领域发现许多类型的性能问题。我曾参与过一个将一体性应用迁移为（微）服务的项目，在其中发现了一个巨大的峰值问题。该问题类似于我们已分析过的某些模式，例如 N+1 次查询问题，原因在于某个用例会数百次调用某个后端服务。
大多数情况下，这种问题都是由糟糕的接口设计而造成的，并且没有考虑到某个原本在本地调用的方法在 Docker 容器或云计算环境中被执行时会发生什么。网络问题会突然间出现，包括通过网络传输的信息以及新的连接池（意味着你需要考虑线程与套接字），这些问题是你必须处理的。
本文翻译已获授权，原文链接：
http://www.infoq.com/articles/Diagnosing-Common-Java-Database-Performance-Hotspots
Andreas Grabner（@grabnerandi）是一位性能方面的工程师，他在过去十五年间一直致力于这一领域方面的工作。Andreas 的工作是帮助组织找出应用程序中的真正问题，并将从中了获得的知识作为工程最佳实践分享给他人，使他们了解如何避免这些问题。
Harald Zeitlhofer（@HZeitlhofer）为全球的各小型创业公司与大型企业的数据库与应用程序提供服务，他在这方面具有 15 年以上的经验。性能监控及优化始终是成功的环境的关键因素。
今日荐文
点击下方图片即可阅读
阿里巴巴正式开源其自研容器技术 Pouch
技术浪潮不断来袭，在新兴技术层出不穷的今天，如何洞悉技术前景显得尤为重要，左耳朵耗子等业内资深专家在极客时间开设针对前沿技术、职业发展、商业思维的付费专栏，现正感恩回馈，最后两天 199 元专栏现 1 元即可阅读 7 天，识别下图二维码，开启你的极客时间。