时间：2021-07-01 10:21:17 帮助过：3人阅读
在实际开发，我们主要是需要对SQL语句进行优化，我们需要快速定位能性的瓶颈点，也就是说快速找到我们SQL主要的开销在哪里？根据木桶原理可以知道，最慢的设备往往是性能瓶颈。例如：互联网运用中的带宽，本地数据复制时的硬盘的访问速度。
根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下五条性能基本优化法则：
（1）减少数据访问（减少磁盘访问）
（2）返回更少数据（减少网络传输或磁盘访问）
（3）减少交互次数（减少网络传输）
（4）减少服务器CPU开销（减少CPU及内存开销）
（5） 利用更多资源（增加资源）
由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源解决问题。
以下是每个优化法则层级对应优化效果及成本经验参考：
|
优化法则 |
性能提升效果 |
优化成本 |
|
减少数据访问 |
1~1000 |
低 |
|
返回更少数据 |
1~100 |
低 |
|
减少交互次数 |
1~20 |
低 |
|
减少服务器CPU开销 |
1~5 |
低 |
|
利用更多资源 |
@~10 |
高 |
二、数据库访问优化法则详解
2.1、减少数据访问
（1）正确创建索引
索引有哪些种类？
常见的索引有B-TREE索引、位图索引、全文索引，位图索引一般用于数据仓库应用，全文索引由于使用较少，这里不深入介绍。B-TREE索引包括很多扩展类型，如组合索引、反向索引、函数索引等等，以下是B-TREE索引的简单介绍：
B-TREE索引也称为平衡树索引(Balance Tree)，它是一种按字段排好序的树形目录结构，主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。
我们一般在什么字段上建索引？
这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：
1、字段出现在查询条件中，并且查询条件可以使用索引；
2、语句执行频率高，一天会有几千次以上；
3、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？
这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：
小表(记录数小于10000行的表)：筛选比例<10%；
大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16
单条记录长度≈字段平均内容长度之和+字段数*2
索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？
这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：
索引对于Insert性能降低56%
索引对于Update性能降低47%
索引对于Delete性能降低29%
因此对于写IO压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。
（2）只通过索引访问数据
有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。
（3）优化SQL执行计划
SQL执行计划是关系型数据库最核心的技术之一，它表示SQL执行时的数据访问算法。由于业务需求越来越复杂，表数据量也越来越大，程序员越来越懒惰，SQL也需要支持非常复杂的业务逻辑，但SQL的性能还需要提高，因此，优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外，还需要有一套优秀的算法库来提高SQL性能。
2.2、返回更少的数据
减少数据的返回也是优化的重要手段，主要有两种方法 分页 和 只返回需要的字段。
（1）分页
分页总共包括三种分页方式：客户端分页、服务端分页、数据库分页。
客服端分页：将数据从应用服务器全部下载到本地应用程序或浏览器，在应用程序或浏览器内部通过本地代码进行分页处理。
优点：编码简单，减少客户端与应用服务器网络交互次数
缺点：首次交互时间长，占用客户端内存
适应场景：客户端与应用服务器网络延时较大，但要求后续操作流畅，如手机GPRS，超远程访问（跨国）等等。
服务端分页：将数据从数据库服务器全部下载到应用服务器，在应用服务器内部再进行数据筛选。
优点：编码简单，只需要一次SQL交互，总数据与分页数据差不多时性能较好。
缺点：总数据量较多时性能较差。
适应场景：数据库系统不支持分页处理，数据量较小并且可控。
数据库分页：采用数据库SQL分页需要两次SQL完成，一个SQL计算总数量，一个SQL返回分页后的数据。
优点：性能好
缺点：编码复杂，各种数据库语法不同，需要两次SQL交互。
ps:oracle数据库一般采用rownum来进行分页。常用分页方式如下
rownum分页：
?| 1 2 3 4 5 |
select * from (
select a.*,rownum rn from
(select * from student a where id=? order by status) a
where rownum<=20)
where rn>10;
|
rowid分页语法：
?