数据库性能优化—全局优化思路

一、数据库访问优化法则

要正确的优化SQL，我们需要快速定位能性的瓶颈点，也就是说快速找到我们SQL主要的开销在哪里？

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

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

• 延时（响应时间）：表示硬件的突发处理能力；
• 带宽（吞吐量）：代表硬件持续处理能力。

2、从上图可以看出，计算机系统硬件性能从高到代依次为：

CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

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

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

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

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

硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。

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

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

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

优化法则	性能提升效果	优化成本	优化手段
减少数据访问	1~1000	低	1、创建并使用正确的索引 2、只通过索引访问数据 3、优化SQL执行计划
返回更少数据	1~100	低	1、数据分页处理：客户端分页、应用程序分页、sql分页 2、只返回需要的字段
减少请求次数	1~20	低	1、批提交batch DML 2、In List：一个ID一个请求发给数据库改成In List的写法 3、设置Fetch Size 4、使用存储过程 5、优化业务逻辑 6、使用ResultSet游标处理记录
减少服务器CPU开销	1~5	低	1、使用绑定变量 2、合理使用排序 3、减少比较操作 4、大量复杂运算在客户端处理
利用更多资源	@~10	高	1、客户端多进程并行访问 2、数据库并行处理

二、数据库优化手段详解

1、减少数据访问

1）创建并使用正确的索引

如果我们把一个表的内容认为是一本字典，那索引就相当于字典的目录，如下图所示：

一般在什么字段上建索引？这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：

1）字段出现在查询条件中，并且查询条件可以使用索引；

2）语句执行频率高，一天会有几千次以上；

3）通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？

这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：

小表(记录数小于10000行的表)：筛选比例<10%；

大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16，单条记录长度≈字段平均内容长度之和+字段数*2

2）只通过索引访问数据

有些时候，只访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。

如：select id,name from company where type='2';，如果这个SQL经常使用，我们可以在type,id,name上创建组合索引

create index my_comb_index on company(type,id,name);

有了这个组合索引后，SQL就可以直接通过my_comb_index索引返回数据，不需要访问company表。

还是拿字典举例：有一个需求，需要查询一本汉语字典中所有汉字的个数，如果我们的字典没有目录索引，那我们只能从字典内容里一个一个字计数，最后返回结果。如果我们有一个拼音目录，那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页，拼音目录有20页，那我们的数据访问成本相当于全表访问的50分之一。

切记，性能优化是无止境的，当性能可以满足需求时即可，不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里，所以这种只通过索引访问数据的方法一般只用于核心应用，也就是那种对核心表访问量最高且查询字段数据量很少的查询。

3）优化SQL执行计划

 

2、返回更少的数据

1）数据分页处理

分页方式	操作过程	优点	缺点	适用场景
客户端(应用程序或浏览器)分页	数据：应用服务器 -> (本地应用程序或浏览器)，在应用程序或浏览器内部通过本地代码进行分页处理。	编码简单，减少客户端与应用服务器网络交互次数	首次交互时间长，占用客户端内存	客户端与应用服务器网络延时较大，但要求后续操作流畅，如手机GPRS，超远程访问（跨国）等等。
应用服务器分页	数据：数据库服务器 -> 应用服务器，在应用服务器内部再进行数据筛选。 以下是一个应用服务器端Java程序分页的示例： List list=executeQuery(“select * from employee order by id”); Int count= list.size(); List subList= list.subList(10, 20);	编码简单，只需要一次SQL交互，总数据与分页数据差不多时性能较好。	总数据量较多时性能较差。	数据库系统不支持分页处理，数据量较小并且可控。
数据库SQL分页	采用数据库SQL分页需要两次SQL完成：一个SQL计算总数量，一个SQL返回分页后的数据	性能好	编码复杂，各种数据库语法不同，需要两次SQL交互。

2）只返回需要的字段

通过去除不必要的返回字段可以提高性能，例：

调整前：select * from product where company_id=?;

调整后：select id,name from product where company_id=?;

优点：

1、减少数据在网络上传输开销

2、减少服务器数据处理开销

3、减少客户端内存占用

4、字段变更时提前发现问题，减少程序BUG

5、如果访问的所有字段刚好在一个索引里面，则可以使用纯索引访问提高性能。

缺点：增加编码工作量

由于会增加一些编码工作量，所以一般需求通过开发规范来要求程序员这么做，否则等项目上线后再整改工作量更大。

如果你的查询表中有大字段或内容较多的字段，如备注信息、文件内容等等，那在查询表时一定要注意这方面的问题，否则可能会带来严重的性能问题。如果表经常要查询并且请求大内容字段的概率很低，我们可以采用分表处理，将一个大表分拆成两个一对一的关系表，将不常用的大内容字段放在一张单独的表中。如一张存储上传文件的表：

T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT）

我们可以分拆成两张一对一的关系表：

T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE）

T_FILECONTENT（ID, FILE_CONTENT）

         通过这种分拆，可以大大提少T_FILE表的单条记录及总大小，这样在查询T_FILE时性能会更好，当需要查询FILE_CONTENT字段内容时再访问T_FILECONTENT表。

转自：http://my.oschina.net/xianggao/blog/87216

另有参考 http://my.oschina.net/xianggao/blog/87448 数据库性能优化之SQL语句优化2

http://my.oschina.net/xianggao/blog/87450 数据库性能优化之SQL语句优化3

http://my.oschina.net/xianggao/blog/87453 数据库性能优化之SQL语句优化4

http://my.oschina.net/xianggao/blog/87223  关于如何形成一个好的数据库设计