数据库访问优化法则简介

第3章 面向程序员的数据库访问性能优化法则

编者说明：本章原为叶正盛同学发表在CSDN 博客的一篇技术文章，虽然其内容是“面向程序员”的，但是对于DBA 们仍然具有极大的借鉴意义，从来开发和数据库就是密不可分的。如果开发层面能够减少失误，则DBA就可以轻松许多；而正是由于开发人员对于数据库的理解不 足，才导致今天DBA 们大有可为。这篇文章可以让开发人员加深对于数据库的认知和了解，也可以帮助DBA 们理解开发人员的思路、想法和工作方式。

作者说明：

（1）本文只是面对数据库应用开发的程序员，不适合专业DBA。DBA 在数据库性能优化方面需要了解更多的知识。

（2）本文的许多示例及概念是基于Oracle 数据库描述的，对于其他关系型数据库也可以参考，但许多观点不适合KV 数据库或内存数据库，或者是基于SSD 技术的数据库。

（3）本文未深入数据库优化中最核心的执行计划分析技术。

读者对象：

开发人员：如果你是做数据库开发的，那本文的内容非常适合，因为本文是从程序员的角度来谈数据库性能优化。

架构师：如果你已经是数据库应用的架构师，那本文的知识你应该清楚90%，否则你可能是一个喜欢折腾的架构师。

DBA（数据库管理员）：大型数据库优化的知识非常复杂，本文只是从程序员的角度来谈性能优化，DBA 除了需要了解这些知识外，还需要深入数据库的内部体系架构来解决问题。

数据库访问优化法则简介

引言

在网上有很多文章介绍数据库优化知识，但是大部分文章只是对某个方面进行说明，而对于程序员来说通过这种介绍并不能很好地掌握优化知识，因为很多介绍只适用于一些特定的场景，有时则会产生误导，或让程序员感觉不明白其中的奥妙而对数据库优化感觉很神秘。

很多程序员总是问如何学习数据库优化，有没有好的教材之类的问题。在书店也看到了许多数据库优化的专业书籍，但是感觉更多的是面向DBA 或者是PL/SQL 开发方面的知识，感觉不太适合普通程序员。而要想做数据库优化的高手，不是花几周、几个月就能达到的。这并不是因为数据库优化有多高深，而是因为要做好优 化一方面需要有非常好的技术功底，对操作系统、存储硬件网络、数据库原理等方面有比较扎实的基础知识；另一方面是需要花大量时间对特定的数据库进行实践测 试与总结。

作为一个程序员，我们也许不清楚线上正式的服务器硬件配置，我们不可能像DBA那样专业地对数据库进行各种实践测试与总结，但我们都应该非常了解 SQL 的业务逻辑，清楚SQL 中访问表及字段的数据情况，我们其实只需要关心我们的SQL 是否能尽快返回结果。那程序员如何利用已知的知识进行数据库优化呢？如何能快速定位SQL 性能问题并找到正确的优化方向？

面对这些问题，笔者总结了一些面向程序员的基本优化法则，本文将结合实例来阐述数据库开发的优化知识。

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

 

 

 

 

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

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

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

从图3-1 中可以看出，计算机系统的硬件性能从高到低依次为：

CPU——Cache（L1-L2-L3）——内存——SSD 硬盘——网络——硬盘

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

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

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

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

硬盘：数据访问、数据写入、日志记录、磁盘排序、某些大表连接操作。

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

 

 

 

 

 

 

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

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

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

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

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

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

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

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

 

 

 

 

 

 

1．数据块（Block）

数据块是Oracle 数据库中数据在磁盘中存储的最小单位，也是一次IO 访问的最小单位，一个数据块通常可以存储多条记录，数据块大小是DBA 在创建数据库或表空间时指定的，可指定为2KB、4KB、8KB、16KB 或32KB。图3-3 是一个Oracle 数据库典型的物理结构，一个数据库可以包括多个数据文件，一个数据文件内又包含多个数据块。对于其他关系型数据库也有类似的架构，有些数据库把数据块称为 数据页（Page），大小为8KB 或16KB。

 

 

 

 

 

2．ROWID

ROWID 是每条记录在Oracle 数据库中的唯一标识，通过ROWID 可以直接定位记录到对应的文件号及数据块位置。ROWID 内容包括文件号、对象号、数据块号、记录槽号，如图3-4 所示。