SQL数据库调优技巧指南

1、 SQL数据库调优技巧指南 对于SQL调优，要调就调到极致，因为在一个并发量很大的业务系统中，对于频繁执行的单条SQL性能的提升，可能对整体数据库的性能提升都有很大的意义。但是遇到order by字段后面的字段，特别是当这个字段不在过滤条件中时，是加到索引里面呢，还是不加到索引里面呢，加进去会不会没有起到提升性能的作用，反而让索引变得更加复杂，给系统带来不必要的额外负担？但是如果直接忽略掉这个问题，很可能这个提升系统性能的机会就被错过了。这里就和大家探讨一下，面对order by字段后面的条件，特别是这个条件不在过滤条件中时，到底要不要加入索引中，对于SQL调优这笔账，索引中加入order b

2、y字段，是赚了还是赔了? Part 1先来一个小实验，热一下身。通过多次复制dba_objects中的数据，生成测试表T1，大约1000万行数据。做一个简单的查询，查询T1表中object_id最小的10行数据，select * from (select * from T1 order by object_id) where rownum=10，耗时Elapsed: 00:00:35.92，执行计划如下：执行计划中可以看到，先作了一个全表扫，取到了结果集11M行（可以粗略理解为11百万行，这个测试表T1行数为11943842）。然后作了一个排序，截取最小的10条记录，最后返回结果。下面我们在o

3、bject_id字段上建一个索引I_T1_ORDER3，作一个比较。耗时从刚才的35秒，直接降到了 Elapsed: 00:00:00.01，提升性能的效果非常明显。索引和执行计划如下：从执行计划中可以看到，优化器直接从索引中找到了最小的10条记录，然后回表取得结果集返回。相比上一个执行计划，省去了全表扫描，省去了排序，所以执行时间和系统资源消耗都大大减少。在这里作一个简单的分析，首先索引和数据不同，是按照有序的排列存储的，当结果集要求按照顺序取得一部分数据时，索引的功效会体现的非常明显，本次查询就是要取得object_id最小的10条记录。其次，建立索引系统只需要消耗一次资源完成排序过程，而

4、如果没有索引，执行不同的语句可能每次都要经历排序的过程，会消耗更多的系统资源。从这个实验看，在order by字段建索引是非常划算的，而且order by字段并不一定非要加入到where条件中也可以生效。这里要和大家分享一个自己踩到的“坑”：就是起初在建了索引I_T1_ORDER3后，这条查询语句的执行计划并不选择索引，增加了hint提示也不选择。明显使用索引会好，为什么优化器偏偏不选择索引呢，而且是加了hint也不走。在修改object_id列为非空属性（NOT NULL）后，优化器才选择了这个索引。这里是这么理解的：如果这一列存在NULL值，NULL值是没有大小这一说法的，而且不会被保存在

5、索引中。如果优化器无法确定该列没有NULL值，为了保证结果集的准确性，宁愿选择更慢的全表扫描，也不会选择走可能存在NULL的索引，即使用户指定了hint也不会选择（这里的几句话有点绕，大家耐心读一下）。从这一点来看，开发Oracle优化器的小伙伴是非常靠谱的。 Part 2上面的实验中order by字段加入索引的作用非常明显。可是在实际生产环境中，能有这么简单的SQL来给DBA调优的机会并不多，实际生产中的SQL往往要更复杂一些。下面我们就把测试变得复杂一点，复制测试表T1，生成测试表T2，查询object_type类似INDEX中object_id最小的10条记录，select * fro

6、m (select * from T2 where object_type like %INDEX% order by object_id) where rownum avm 当前系统中已经激活的虚拟内存页的数量（该数值不包含文件系统缓存）vmstat - fre 系统中平均空闲页的数量（不能完全代表系统中可用的空闲内存：文件系统缓存驻留内存，并不会返还给空闲列表，除非被虚拟内存管理器盗取）svmon - clnt与in use交叉项 代表有多少内存被文件系统使用（加上free项，可以初步认为是该系统中可以被应用程序所使用的内存）第二步 数据库级别性能1. db2grep -dump | mo

7、re 查看服务器安装了几个DB2版本2. ps -elf | grep db2inst1 查看数据库进程的CPU计数器3. db2 get dbm cfg | grep -i dft_mon 确认快照打开4. 实例级快照，了解当前实例有多少应用程序在执行db2 get snapshot for database manager - Remote connections & Local connections5. 数据库级快照连接数信息：applications connected currently，appls executing in db manager currently锁信息：锁总数，

8、锁等待数量，锁等待总时间，当前数据库锁列表占用内存，死锁次数，锁升级次数，锁超时次数排序信息：-排序是CPU杀手，过多的排序会造成CPU的极大消耗；-排序溢出是说，如果排序堆无法容纳排序数据，就会被溢出到临时空间；-排序是一种状态，根源在SQL语句；数据索引I/O信息：-逻辑读 DB2向缓冲池请求的次数 逻辑读越多，需要的物理I/O就越少-物理读 如果请求的数据页不在缓冲池，需要从磁盘中读取数据页的次数吞吐量或事务信息：提交/回滚事务数，执行动态和静态语句次数，增删改查次数( rows read / rows selected ) 是一个非常重要的性能指标，它表示为了检索一行数据需要读取多少行

9、，该值越大，表示代价越高，需要的I/O越多，可调优的余地越大事务日志信息：日志I/O在很大程度上会影响数据库整体的性能6. 应用程序快照在数据库快照中发现存在大量的逻辑读，通过应用程序快照可以细化到某条特定的语句7. 表空间快照在数据库快照中发现存在大量的逻辑读，通过表空间快照可以轻松地定位哪个表空间被频繁使用8. 表快照如果发现一个表的页数很少，但是读的行数非常多，那么可以合理地猜测该表在某些查询语句中可能处于NLJOIN的内部子节点9. 动态SQL快照：SQL执行次数，总共读的行数，消耗的CPU，逻辑物理读数量，排序数量等第三步 内存使用监控1. db2pd -osinfo系统内存使用情况2. db2pd -dbptnmem整个实例的内存使用情况3. db2pd -memsets内存段使用情况在实例中会有多个不同的内存段，每一个内存段