数据库访问优化法则

 

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