记录自己数据库分析学习的一点心得

性能测试数据库性能分析

为什么要做数据库性能分析

在程序性能测试中，往往会涉及到对数据库的大量操作，有很大一部分程序的性能问题，其实是由数据库的性能导致的。所以这里简单介绍如何分析数据库性能问题。

影响数据库性能的因素

• 硬件：主要包括内存，CPU及磁盘I/O，网络带宽等。
• 软件：操作系统版本，32位和64位操作系统
• 数据库结构设计，SQL语句，绝大多数数据库性能不佳都是因为设计及编码引起。

查找性能低的语句

在做性能分析的时候，可以观察数据库的资源消耗情况，如CPU使用率，内存使用情况，磁盘使用情况等，如果发现数据库资源占用比较高，那优先排查数据库这块的性能问题。查找出执行效率低的语句，如何跟踪查找执行效率低的语句呢？

1. 使用数据库的profiler工具跟踪出数据库语句，查看语句的执行时间等信息。
2. 使用脚本建立存储过程，将跟踪的语句导出到文件中，跟踪完毕后再将文档导入到数据库中进行查询，可以按照条件查询出执行效率低的语句，然后再拿这些语句的执行计划进行性能分析。（脚本路径及使用方法）
3. 使用特定的语句查询出数据库最消耗CPU或者执行最长的语句，百度可以搜出很多这类语句，这里不再详述。

语句分析

查找到性能较差的语句后，我们就要对该语句进行具体分析，具体语句性能分析步骤：

• 查看执行计划

1. 常用的执行计划操作符：
2. 数据访问操作符：SCAN,SEEK,Bookmark Lookup(如Key Lookup)
3. 连接操作符：Nested loop Join ,Merge Join,Hash Join

在数据库中执行语句，查看语句的执行计划，执行计划中，每一个节点都表示一个实际操作，如下图：
[外链图片转存失败]
查询1表示成本总开销100%，查询1包括了A，B两个节点，执行计划的顺序都是从右往左看。我们看开销百分比较高的语句进行性能调优，此处A节点开销占用了99%，所以问题出在A节点处。具体查看A节点的执行信息如下，包括逻辑计算，CPU执行开销，读取行数，I/O开销等：
[外链图片转存失败]
这里可以看出，A节点是一个聚集索引的全表扫描，scan操作被认为是数据库性能的最大杀手，我们应该尽量避免索引的全表扫描操作，分析可能导致全表扫描的原因：

1. 索引缺失：很多数据库性能问题都是因为没有添加索引或者是索引失效导致。
   上面这个问题界面上已经提示我们索引缺失，我们将索引添加上之后再次执行，就不再进行全表扫描，而是使用了seek的方式，如下图：
   [外链图片转存失败]
2. 索引失效：索引失效的可能原因包括过滤的条件使用了计算（函数，逻辑计算，普通计算）
   例如：where left(emailaddress,3）= “jia” 这样的语句就会导致全表扫描,可以使用 emailaddress like ‘jia%’
   查看所有缺失索引的字段:
   [外链图片转存失败]
   Statement: 表示缺失索引字段的表名。
   User_seeks : 表示缺失索引可以被用户的语句用于SEEK操作的次数
   User_scans: 表示缺失索引可以被用户的语句用于SCAN操作的次数
   Avg_total_user_cost: 表示缺失索引被建立后，平均会降低的成本数，数值越大，表示索引创建之后，效果越好。

• SQL语句问题：

1. 避免使用select * 这样的语句，只查询出需要的字段，避免不必要的I/O
2. 限定查询结果集，如使用TOP，合理的利用分页处理，避免大数据量的操作。
3. 对数据进行过滤的时候，优先考虑使用索引字段，多个索引字段优先选择重复率低的索引字段。
4. 不要在过滤字段上使用任何的计算，否则会导致索引失效。
5. 尽量使用有索引的字段进行排序。

其他可能导致数据库性能问题原因：

• 数据库内存不足：如果数据库内存不够用，内存一直占满的情况下，会导致大量的缓存对象被清除，不仅会有频繁的I/O，还会导致大量重复计算导致CPU也升高。可以使用监控工具监控内存的使用情况，关注下数据库内存是否做了限制或者服务器内存配置较低。

• 统计信息过期：数据库会自动创建统计信息，统计信息过期会导致查询优化器执行不是最优的执行计划。

• 参数嗅探：当被缓存的执行计划和保存的编译时参数与当前运行时参数的数据分布存在较大的偏差时，会导致当前语句采用了不适合的执行计划，造成性能异常。

• 其他（待更新）