数据库性能优化
以前看过的一篇长文
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 |
高 |
接下来,我们针对5种优化法则列举常用的优化手段并结合实例分析。