数据库访问优化法则简介（一）

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

要正确的优化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	高

 

接下来，我们针对5种优化法则列举常用的优化手段并结合实例分析。

二、Oracle数据库两个基本概念
数据块(Block)
数据块是数据库中数据在磁盘中存储的最小单位，也是一次IO访问的最小单位，一个数据块通常可以存储多条记录，数据块大小是DBA在创建数据库或表 空间时指定，可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的物理结构，一个数据库可以包括多个数据文件，一个数据文 件内又包含多个数据块；
ROWID
ROWID是每条记录在数据库中的唯一标识，通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号