达梦8技术支持笔记（9）

数据库优化笔记

1、数据库访问优化法则简介

要正确的优化SQL，我们需要快速定位能性的瓶颈点，也就是说快速找到我们SQL主要的开销在哪里？而大多数情况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为什么这些一般的工作我们能快速确认瓶颈点呢，因为我们对这些慢速设备的性能数据有一些基本的认识，如网络带宽是2Mbps，硬盘是每分钟7200转等等。因此，为了快速找到SQL的性能瓶颈点，我们也需要了解我们计算机系统的硬件基本性能指标，下图展示的当前主流计算机性能指标数据。

 

从图上可以看到基本上每种设备都有两个指标：

延时（响应时间）：表示硬件的突发处理能力；

带宽（吞吐量）：代表硬件持续处理能力。

 

从上图可以看出，计算机系统硬件性能从高到代依次为：

CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

由于SSD硬盘还处于快速发展阶段，所以本文的内容不涉及SSD相关应用系统。

根据数据库知识，我们可以列出每种硬件主要的工作内容：

CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；

网络：结果数据传输、SQL请求、远程数据库访问（dblink）；

硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。

 

 

 

根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下图所示的性能基本优化法则：

 

 

 

这个优化法则归纳为5个层次：

1、  减少数据访问（减少磁盘访问）

2、  返回更少数据（减少网络传输或磁盘访问）

3、  减少交互次数（减少网络传输）

4、  减少服务器CPU开销（减少CPU及内存开销）

5、  利用更多资源（增加资源）

 

由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源解决问题。

以下是每个优化法则层级对应优化效果及成本经验参考：

 

 

优化法则	性能提升效果	优化成本
减少数据访问	1~1000	低
返回更少数据	1~100	低
减少交互次数	1~20	低
减少服务器CPU开销	1~5	低
利用更多资源	@~10	高

2、优化

1、别用<>不等于，不走索引，拆成> or <。

2、null的判断不走索引，尽量给列默认值。

3、union all 替换union。

4、全模糊查询拆开，或者用全文索引。

5、where 后面先放走索引的，删的多的。

6、隐式转换不走索引

7、data时间戳尽量用>\< ,用函数不走索引。

8、>=   <= 比 <   > 快。

9、不用*。

10、索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？

这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：

索引对于Insert性能降低56%

索引对于Update性能降低47%

索引对于Delete性能降低29%

因此对于写IO压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。

11、In List

很多时候我们需要按一些ID查询数据库记录，我们可以采用一个ID一个请求发给数据库，如下所示：

for :var in ids[] do begin

  select * from mytable where id=:var;

end;

 

我们也可以做一个小的优化， 如下所示，用ID INLIST的这种方式写SQL：

select * from mytable where id in(:id1,id2,...,idn);

       另外当前数据库一般都是采用基于成本的优化规则，当IN数量达到一定值时有可能改变SQL执行计划，从索引访问变成全表访问，这将使性能急剧变化。随着SQL中IN的里面的值个数增加，SQL的执行计划会更复杂，占用的内存将会变大，这将会增加服务器CPU及内存成本。

评估在IN里面一次放多少个值还需要考虑应用服务器本地内存的开销，有并发访问时要计算本地数据使用周期内的并发上限，否则可能会导致内存溢出。

综合考虑，一般IN里面的值个数超过20个以后性能基本没什么太大变化，也特别说明不要超过100，超过后可能会引起执行计划的不稳定性及增加数据库CPU及内存成本，这个需要专业DBA评估。

12、合理使用merge join 和hash join。

13、指定并行查询PARALLEL。

14、绑定变量复用执行计划

15、用exists代替distanct

16、写定时job在晚上预处理数据。

3、实例

1、cpu过高，开启游标共享，分会话级和永久。

2、数据量大使用分区技术、merge into技术，外部表优化。

3、少用函数索引，其维护成本和索引存储空间大于普通索引。

4、复合索引的前缀性和可选性，数据文件脱机，日志发现是异步i/o过多，超出允许范围，综合查询模块在各个条件上建立组合索引导致。

5、监控业务场景索引，删除无用索引，定期清理索引碎片，rebuild。

6、oltp优化parse阶段，olap优化execute和fetch阶段。

7、提高表连接性能，分析嵌套连接、排序连接、哈希连接。

8、hint强制修改执行计划，全局生效。

9、扩展快的频繁申请，临时表的反复分配，日志组的反复切换，调大参数。