SQL语言艺术(十二)明察秋毫：监控性能

这章讨论通过收集必要的监控数据来获取数据库运行细节，为性能优化做指导

数据库很慢，通常由5种原因引起：

非数据库问题，网络不稳，或主机因某种原因超过负荷

整体性能突然下降，有两种情况要考虑，可能是性能真的降低，通常是因为软件升级或硬件配置修改，也可能是突然涌入的查询。第二种情况是开发或设计问题，比如原先的设计为负载增加留的余地太小，或者应用未经充分的压力测试。改善某些关键的查询可以大幅降低平均服务时间，并可稍稍缓解硬件升级的成本压力

局部性能突然下降，应该考虑是否是加锁引起的问题，DBA可以监控锁，并确认正在竞争相同资源的多个工作，通过更快地释放锁来改善

性能达到临界值而缓慢下降，它可能与三种因素有关：

1.如果负载随着时间呈现规律性增长，可能是因为服务队列的整体性能突然下降

2.如果负载随着表大小的不断增加而不断增长，则是因为没有合适建立索引造成的

3.如果负载在大量删除/更新操作之后大幅增长，则是因为物理存储的质量下降造成

某查询很慢，应注意动态建立的查询，可能是一些很不常见的条件组合在作怪

如果已清楚服务器负载增加的原因，那么理清数据库活动与业务活动的联系，就能找到应用中最薄弱的环节。接着进行性能测试，专注于那些弱点的改善。为了预测实际应用的性能，必须密切关注压力测试和用户验收测试

除了速度很慢的糟糕SQL查询外，还有三类查询需要注意。比如硬编码所有查询，执行无用的查询，交互过于频繁。上述三种查询通常执行很快，但这些无用的查询会使处理高峰时资源短期加剧，虽然每个查询执行的速度都可以接受，但积少成多，往往比那些大型的糟糕查询的影响还大

对于性能的评估可借助以下3个必要条件

知道花费了什么，两个最重要的数据库负载指标是：CPU花费在语句解释上的总时间，以及执行查询需访问的数据库存储页数量

知道获得了什么，必须将产生的负载与特定的SQL语句对应起来，否则就无法评估性能。因此负载分析的第一个阶段必须找到所有SQL语句，并判断每个语句对性能的影响。找到让DBMS持续忙碌的SQL后，还必须将SQL活动与本质的业务活动联系起来。比如说了解每处理一个客户订单平均要调用多少个SQL语句，有助于预测下一次广告活动会对系统造成多大的影响，也有助于发现程序的问题

负载大小必然和SQL语句相关，SQL语句必然和业务活动相关，业务活动必然和业务需求相关

知道投资回报率比公认的标准是高还是低，了解基准值很重要，要了解环境的限制，比如在机器上量测每单元时间能插入、读取、更新、删除多少条记录。一旦定义了基准值，就可以找到最佳改进点，即业务回报较高，而技术可行性也较高的部分，专注这些部分，进行相应的改善。最终用户能感觉到的性能改善是最重要的。

优化SQL时不要忽略上下文，要从业务角度考虑，尽可能减少数据库访问次数。冗长，复杂的查询未必缓慢，这完全取决于编写的方式，把尽可能多的行为放入一个SQL语句中，应该是改善性能的先决条件

执行计划对性能“二等重要”

执行计划的长度并不重要，不能假设执行计划越短越好。判断查询性能的唯一标准，是花了多少时间执行，而不是执行计划是否符合偏见。执行计划就像战场的报告，有助于发现战术规划和实际执行是否一致

设法使优化器采用完全不同的执行计划，可通过如下手段：

当返回少量记录时，应增加索引，或重建复合索引并调整其中字段的顺序，引时将非关联子查询转换为关联子查询，也会有帮助

当返回大量记录，做法相反，并在from子句中使用子查询，以建议表连接以不同顺序进行

如果没有把握，则还有很多其它选择。例如用union或with子句分解查询，尽量使每个条件不依赖其它条件，一般来说，在干预优化器的执行顺序之前，应首先尽量去除查询中强制的处理顺序，使优化器尽量自由。在别无选择的时候，才考虑干预优化器这一手段

最后一招，就是优化器指令，应小心使用

优化器在以下情况下无法高效工作：

通过很多语句，分别读取数据片段。从应用角度看这些SQL语句当然是相关的，但SQL引擎绝不会知道这些，从而无法跨越语句的界限进行优化

随便使用SQL方言提供的各种非关系特性

执行计划之所以不可或缺，是因为它为调查性能提供了起点，并能提示复杂视图和触发器引起的隐藏数据库操作

影响查询性能真正的重要因素包括：

表的数量

表有哪些索引

存储特性(例如分区)，和索引同样重要

查询条件的质量

结果集的大小

有了上述知识作为坚实基础，就可以超越执行计划本身，研究更有价值的查询性能问题了。首先了解上下文和数据，然后开始行动。查询时要尽快去除多余数据，尽量保持优化器的自由，避免语句内部存在依赖性而限制了表的访问顺序