【系统性能优化】（三）数据库访问优化

比眉伴天荒 2022-08-09 15:58 245阅读 0赞

要正确的优化SQL，我们需要快速定位能性的瓶颈点，也就是说快速找到我们SQL主要的开销在哪里？而大多数情况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为什么这些一般的工作我们能快速确认瓶颈点呢，因为我们对这些慢速设备的性能数据有一些基本的认识，如网络带宽是2Mbps，硬盘是每分钟5400\\7200转等等。因此，为了快速找到SQL的性能瓶颈点，我们需要了解计算机系统的硬件基本性能指标，下图展示了当前主流计算机性能指标数据。  
  
从图上可以看到基本上每种设备都有两个指标：  
延时（响应时间）：表示硬件的突发处理能力；  
带宽（吞吐量）：代表硬件持续处理能力。  
![Center][]  
从上图可以看出，计算机系统硬件性能从高到代依次为：  
CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘  
根据数据库知识，我们可以列出每种硬件主要的工作内容：  
CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；  
网络：结果数据传输、SQL请求、远程数据库访问（dblink）；  
硬盘：数据访问、数据写入、日志记录、大数据量排序等。  
根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下图所示的性能基本优化法则：  
  
![Center 1][]  
这个优化法则归纳为5个层次：    
1、 减少数据访问（减少磁盘访问）比如：创建并正确使用的索引，索引是否被使用可以通过sql的执行计划来查看  
2、 返回更少数据（减少网络传输或磁盘访问） 比如：数据分页处理、只返回需要的字段（重点）  
3、 减少交互次数（减少网络传输） 比如：batch DML（批量提交）  
4、 减少服务器CPU开销（减少CPU及内存开销） 比如：使用绑定变量（Java中Preparestatement就是为处理绑定变量提供的对像）、减少比较操作、大量复杂运算在客户端处理（高并发的时候不影响数据库的正常工作）  
5、 利用更多资源（增加资源） 比如：客户端使用多进程并行访问  
总结：          
    由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低。我们任何一个SQL的性能优化都应该按上面说的这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源来解决问题。

[Center]: /images/20220731/718225b109ef4f0d88a52523b9646c95.png
[Center 1]: /images/20220731/32770a9a1711464893359a0fde2d76e2.png