数据库访问性能优化

在基于数据库进行业务功能的开发时，如何保证数据库访问的性能是区分普通程序员和高级程序员的分水岭。这里系统的梳理下如何在程序员视角下实现数据库访问性能优化。 本文是面向程序员的数据库访问性能优化法则一文的精简版，有兴趣的同学可以参考下原文。

IO性能分析

计算机各硬件性能指标，参考如下：

上图中，主要有两个性能指标：
(1) 延时（响应时间）：表示硬件的突发处理能力；
(2) 带宽（吞吐量）：代表硬件持续处理能力。
从上图可以看出，计算机系统硬件性能从高到代依次为：
CPU–Cache(L1-L2-L3)–内存–SSD硬盘–网络–硬盘
目前个人PC已经从硬盘替换成SSD硬盘，但是后端服务器还是以硬盘偏多(SSD硬盘有读写次数限制，要根据业务需要合理选择)。本文的内容不涉及SSD相关应用系统。

数据库性能优化漏斗法则

根据数据库知识，可以列出每种硬件主要操作内容：
(1) CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算，等；
(2) 网络：结果数据传输、SQL请求、远程数据库访问（dblink），等；
(3) 硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接，等。
根据计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下图所示的性能基本优化法则：

这个优化法则归纳为5个层次：
1、减少数据访问（减少磁盘访问）
2、返回更少数据（减少网络传输或磁盘访问）
3、减少交互次数（减少网络传输）
4、减少服务器CPU开销（减少CPU及内存开销）
5、利用更多资源（增加资源）
由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低。任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源解决问题。
以下是每个优化法则层级对应优化效果及成本经验参考：

接下来，针对5种优化法则列举常用的优化手段并结合实例分析。

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

(1) 正确的创建并使用索引

数据库存储的数据最终是以文件的形式存储在磁盘中。在使用这些数据的时候，需要要把磁盘中的数据读到内存中。而磁盘 IO 是非常高昂的操作。一种有效的解决方案是提供一种稳定的数据结构能够满足只需要查询很少的数据就可定位到期望的数据。也即每次查询数据仅需要进行少部分的磁盘 IO 操作。这种数据结构就是索引。索引（Index）是帮助数据库高效获取数据的数据结构。 如果将数据库比作书，那么索引就相当于目录。
数据库索引的原理非常简单，但在复杂的表中真正能正确使用索引的人不多。

索引生效场景

索引在SQL中使用，具体如下：
(a) 在where子句中，查询引擎会根据where子句中涉及的字段优先选择索引查询数据。
(b) 在order by子句，当使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序，但是如果对该字段建立索引后，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果是分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用取出所有数据进行排序再返回某个范围内的数据。
© 在join子句中，对join语句匹配关系（on）涉及的字段建立索引能够提高效率。
(d) 在select子句中，如果select中的查询字段存在于覆盖索引中，那么无需读取记录即可返回。

索引失效场景

在使用索引的时候，要注意索引失效的场景，尽量避免索引失效的场景。可能导致索引失效的常见场景有：
(a) like语句以%开头，会导致索引失效。模糊查询时，使用%且将其放在开头，会导致查询优化器不得不使用全表查询，从而导致索引失效。如果是"XXX%"，则可以正常使用索引。
(b) 索引列参与计算，会导致索引失效(如执行算数运算或使用函数或存在类型转换)。当索引列参与计算时，因为存在中间值，所以会导致索引失效。常见的计算场景有类型转换、算数运算、使用函数等场景。 © 查询条件中有or，如果存在or相关的字段没有索引，会导致语句索引失效。如果查询条件中有or，需要确保or相关的字段都要有索引，否则会导致索引失效。
(d) 违背最左匹配原则，会导致索引失效。如果是一个多码索引(也称联合索引、组合索引)，其索引匹配遵循最左匹配规则，如果违背会导致索引失效。
(e) 反向查询可能不会使用索引(如not in、not exist)，如果在查询的时候，使用了反向查询相关的语句，要注意确认下索引是否生效。

判断索引是否生效–执行计划

简单SQL可以根据索引使用语法规则判断索引是否生效，但是复杂的SQL不好办，判断SQL的响应时间是一种策略，但是这会受到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里，可能全表访问的时间比索引访问时间还少。如何判断一个SQL语句是否使用到了索引呢？其实可以通过该sql的"执行计划"来判断。关于MySQL执行计划的文章可以参考这篇WIKI。本文重点介绍下索引生效和失效的场景。

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

(1) 数据分页处理(减少行数)

客户端分页

将数据从应用服务器获取到本地应用程序后，在客户端通过本地代码进行分页处理。这种处理方式编码简单，可以减少客户端与应用服务器网络交互次数。但是，首次交互时间较长，且占用客户端内存(如果不控制数据总量，可能会导致OOM)。

服务器分页

将数据从数据库获取到本地应用程序后，在应用服务器内部再进行数据筛选。这种处理方式编码简单，只需要一次SQL交互，总数据与分页数据差不多时性能较好。但是总数据量较多时性能较差。

数据库分页

采用数据库SQL分页需要两次SQL完成：
(1) 一个SQL计算总数量；
(2) 一个SQL返回分页后的数据。
这种处理方式性能好，是主流的数据分页处理方式。但是，不同数据库的语法可能不同，且需要两次SQL交互。

(2) 只返回需要的字段(减少列数)

在数据库开发规范中，强制要求避免使用select * 语句。因为select * 会返回该表的所有字段。对于宽表来说，查询所有字段是一种灾难。使用select+特定字段，可以: 减少数据在网络上传输开销；减少服务器数据处理开销；减少客户端内存占用。而且，如果访问的所有字段刚好在一个索引里面，则可以使用覆盖索引访问提高性能。
此外，如果表中有大字段或内容较多的字段，如备注信息、文件内容等等，那在查询表时一定要注意这方面的问题，否则可能会带来严重的性能问题。如果表经常要查询并且请求大内容字段的概率很低，我们可以采用分表处理，将一个大表分拆成两个一对一的关系表，将不常用的大内容字段放在一张单独的表中。

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

batch 操作

对数据写入操作，尽量使用batch操作的接口，采用batch操作一般不会减少很多数据库服务器的物理IO，但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的CPU开销。

in list 操作

很多时候需要按一些ID查询数据库记录，我们可以采用一个ID一个请求发给数据库，如下所示：

for :var in ids[] do begin
  select * from mytable where id=:var;
end;

其实，这里可以做一个小的优化，如下所示，用ID INLIST的这种方式写SQL：

select * from mytable where id in(:id1,id2,...,idn);

通过这样处理可以大大减少SQL请求的数量，从而提高性能。但是，需要注意的是，在in语句里面一次放多少个值还需要考虑数据库的能力，对MySQL来说，建议in中元素的个数小于100。如果超过100个，可能会引起执行计划的不稳定性及增加数据库CPU及内存成本，这个需要专业DBA评估。

设置Fetch Size

当采用select从数据库查询数据时，数据默认并不是一条一条返回给客户端的，也不是一次全部返回客户端的，而是根据客户端fetch_size参数处理，每次只返回fetch_size条记录，当客户端游标遍历到尾部时再从服务端取数据，直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时，可以加大fetch_size，这样可以减少结果数据传输的交互次数及服务器数据准备时间，提高性能。
iBatis的SqlMapping配置文件可以对每个SQL语句指定fetchsize大小，如下所示：

<select id="getAllProduct" resultMap="HashMap" fetchSize="1000">
    select * from employee
</select>

注意，fetchsize不能设置太大，如果一次取出的数据大于JVM的内存会导致内存溢出，所以建议不要超过1000，太大了也没什么性能提高，反而可能会增加内存溢出的危险。

优化业务逻辑

要通过优化业务逻辑来提高性能是比较困难的，这需要程序员对所访问的数据及业务流程非常清楚。

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

使用绑定变量

绑定变量是指SQL中对变化的值采用变量参数的形式提交，而不是在SQL中直接拼写对应的值。非绑定变量写法：Select * from employee where id=1234567。绑定变量写法：Select * from employee where id=?。
SQL中预处理操作就是为处理绑定变量提供的对像，绑定变量有以下优点：
(1) 防止SQL注入；
(2) 提高SQL可读性；
(3) 提高SQL解析性能，不使用绑定变更我们一般称为硬解析，使用绑定变量我们称为软解析。

合理使用排序

现在CPU的性能增强，对于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上万条以上时，需要注意是否一定要这么做了，大记录集排序不仅增加了CPU开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘排序时性能会急剧下降，这种需求需要与DBA沟通再决定，取决于你的需求和数据，所以只有你自己最清楚，而不要被别人说排序很慢就吓倒。
以下列出了可能会发生排序操作的SQL语法：
Order by
Group by
Distinct
Exists子查询
Not Exists子查询
In子查询
Not In子查询
Union（并集），Union All也是一种并集操作，但是不会发生排序，如果你确认两个数据集不需要执行去除重复数据操作，那请使用Union All 代替Union。
Minus（差集）
Intersect（交集）
Create Index

大量复杂运算在客户端处理

什么是复杂运算，一般情况下，一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、3DES及BASE64数据加密算法等等。如果有大量这类函数运算，尽量放在客户端处理，一般CPU每秒中也只能处理1万-10万次这样的函数运算，放在数据库内不利于高并发处理。

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

客户端多进程并行访问

多进程并行访问是指在客户端创建多个进程(线程)，每个进程建立一个与数据库的连接，然后同时向数据库提交访问请求。当数据库主机资源有空闲时，我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时，采用多进程并行访问性能不会提高，反而可能会更慢。所以使用这种方式最好与DBA或系统管理员进行沟通后再决定是否采用。

数据库并行处理

数据库并行处理是指客户端一条SQL的请求，数据库内部自动分解成多个进程并行处理。注意，并不是所有的数据库都支持并行处理，并不是所有的SQL都可以使用并行处理。并行处理的优点是使用多进程处理，充分利用数据库主机资源（CPU,IO），提高性能。但是并行处理也有以下缺点：单个会话占用大量资源，影响其它会话，所以只适合在主机负载低时期使用；只能采用直接IO访问，不能利用缓存数据，所以执行前会触发将脏缓存数据写入磁盘操作。
需要注意的是：
(1) 并行处理在OLTP类系统中慎用，使用不当会导致一个会话把主机资源全部占用，而正常事务得不到及时响应，所以一般只是用于数据仓库平台。
(2) 一般对于百万级记录以下的小表采用并行访问性能并不能提高，反而可能会让性能更差。

参考

https://blog.csdn.net/yzsind/article/details/6059209 面向程序员的数据库访问性能优化法则
https://blog.csdn.net/solihawk/article/details/120756584 数据库系列之MySQL中的执行计划