一. 数据库访问优化法则
为了了解sql执行的瓶颈，我们需要先了解计算机的硬件基本性能指标
image.png
从图上可以看到基本上每种设备都有两个指标：
延时（响应时间）：表示硬件的突发处理能力；
带宽（吞吐量）：代表硬件持续处理能力。
从上图可以看出，计算机系统硬件性能从高到代依次为：
CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘
根据数据库知识，我们可以列出每种硬件主要的工作内容：
CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；
网络：结果数据传输、SQL请求、远程数据库访问（dblink）；
硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。
根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下性能基本优化法则：
1、 减少数据访问（减少磁盘访问）
2、 返回更少数据（减少网络传输或磁盘访问）
3、 减少交互次数（减少网络传输）
4、 减少服务器CPU开销（减少CPU及内存开销）
5、 利用更多资源（增加资源）
二. 减少数据库访问
1. 使用索引
常用的索引有B-TREE索引、位图索引、全文索引，位图索引一般用于数据仓库应用，全文索引由于使用较少
B-TREE索引也称为平衡树索引(Balance Tree)，它是一种按字段排好序的树形目录结构，主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。
叶子节点内容：索引字段内容+表记录ROWID
根节点，分支节点内容：当一个数据块中不能放下所有索引字段数据时，就会形成树形的根节点或分支节点，根节点与分支节点保存了索引树的顺序及各层级间的引用关系。
一个普通的BTREE索引结构示意图如下所示：
b-tree结构示意图
关于建索引的技巧和使用索引时需要注意的点在 索引使用技巧中详细介绍
2.使用分页和批量处理
使用数据库SQL分页
采用数据库SQL分页需要两次SQL完成
一个SQL计算总数量
一个SQL返回分页后的数据
优点：性能好
缺点：编码复杂，各种数据库语法不同，需要两次SQL交互。
使用批量处理
使用数据库框架的batch对数据库进行DML操作；
使用IN LIST的sql方式代替循环查询的方式；
设置Fetch Size来减少数据库交互的次数，当我们通过select方式查询大量的数据时，不是一次将所有数据返回给客户端，而是通过客户端设置的fetch_size大小，每次只返回fetch_size条记录，通过游标完成结果集的遍历；mybatis可以在mapper.xml文件中设置fetchSize属性，在设置单条查询返回的fetchSize值
优化业务逻辑
在执行复杂查询时，将筛选效果最好的语句放在最前，尽量减少数据库的行访问量
如： 查询一个学校所有三年级的女生
select * from school where grade = 3 and sex = '女'；
select * from school where sex = '女' and grade = 3；
使用ResultSet游标处理记录
采用jdbc原始的resultset游标处理记录，在resultset循环读取的过程中处理记录，这样就可以一次从数据库取出所有记录，减少与数据库的交互次数；
3.减少数据库服务器CPU运算
使用绑定变量
非绑定写法： select * from account where id = 123
绑定写法： select * from account where id = ?
pst.set(1,123);
为何绑定写法对CPU的资源消耗小？
数据库执行SQL原理图
SQL字符的HASH值是根据SQL字符串来进行计算的，任何占位符（空格，大小写）都有不同的HASH值，当在SQL缓存中查找到了相同的SQL HASH值，会直接使用缓存的执行计划执行SQL，省去生成执行计划的步骤
合理使用排序
普通OLTP系统排序操作一般都是在内存里进行的，对于数据库来说是一种CPU的消耗。大记录集排序不仅增加了CPU开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘排序时性能会急剧下降
减少比较操作
数据库对模糊查询的效率很低，尤其是前导模糊查询，会是索引失效。
数据库不能使用索引定位大量的IN LIST，可以用exist或Hash Join替代
参考文章
面向程序员的数据库访问性能优化法则
网友评论