数据库优化及编码技巧

1      表

1.1    简化业务模型

熟悉业务场景，理解各功能或模块间的逻辑关系。

对于结构清晰、关联单一的业务模型，最终的系统对数据库的操作形式也会简单很多,才有可能将单次操作消耗时间优化到毫秒级,支持大数据量、高并发的需求。

在进行需求分析、业务模型定义、表结构定义时，需要有意识的简化表关联关系,减少表之间的相互依赖和层次。

1.2    适当建立数据冗余

数据冗余简单的说就是非键字段的重复出现；关联较为紧密，查询比较频繁的对象表之间可以存在适当的冗余字段，通过数据冗余查询关联，提高性能。

由于冗余字段需要保持同步，增加编码难度和维护量，也会造成一定的性能消耗，建议仅模块内部存在冗余字段, 并时刻注意保持冗余字段的同步。

1.3    降低记录字节数

         单条记录所占字节数，直接影响到整个数据库数据文件的大小，从而影响到磁盘IO以及数据库操作的速度，所以需要尽可能的减少单条记录所占字节数。

l  根据业务需要设定合适的字段长度，不能随意指定较大的长度

l  减少某些情况下才会生效的字段的数量

1.4    避免动态扩展表的出现

某些情况下，为了可以自由的进行属性扩展，采用了扩展表的方式，建立了一系列的扩展属性表，和主表进行关联。

如果采用这种形式，必须遵循一个限制：扩展表的数据，仅用于单条记录的查询和更新。这种情况下扩展表的应用作用有限，因此，不建议采用扩展表的形式满足业务需要。

1.5    避免使用视图

一般情况下视图都会进行多表关联，这种情况下，系统会忽略原表的索引、主键等等，每次针对视图进行查询，都会按照视图的定义，读取磁盘，重组数据，性能会有极大的消耗。

如在此情况下在进行数量汇总和分页，即使十万级别的数据也无法支持。

1.6    表关系

表的关系大致可以分为：一对一、一对多、多对多的关系。在一般情况下，它们是一对一的关系：即一张原始单据对应且只对应一个实体。

在特殊情况下，它们可能是一对多或多对一的关系，即一张原始单证对应多个实体，或多张原始单证对应一个实体。正确识别表关系对我们数据库设计是有很大帮助的。

1.7    大数据量分表存储

         大数据量表，对于查询统一以及更新的影响都非常大，对于频繁性操作，但是临时性较强的数据，需要进行分表存储和操作，以减少对大表进行频繁的操作带来的性能消耗。

         比较常见的有日志表、消息表、流程表，具体实现的时候，需要根据其也特点制定合适的分表策略。

         分表的方式主要有：

l  将固定业务数据，分为固定的两个表，分别用于存储最新数据和历史数据

l  将固定业务数据，按时间段（按年、月、周等），创建数据表

l  将所有数据，按时间段创建数据帐套

分表存储后，转移数据的操作可以按业务需要、操作方便性、时间要求，实现为手动或自动方式。

分表存储后，相应的业务以及对应的查询统计和其他操作，也应分开来进行操作，否则分表就没有什么实际的意义。

 

2      索引

2.1    索引简介

数据库表的数据是线性排列的方式，依次存储每条记录，以每条记录500个字节，100万条记录来计算，需要占用500M大小的磁盘空间，这些数据仅仅从磁盘读入到内存就需要很长时间。

数据库索引，以树的方式，将数据以指定字段进行分类，并存储其实际记录指针，主要有以下特点：

l  本身是经过排序的，查找速度很快。

l  相对于实际数据表而言，存储空间更小，加载更快。

l  如果索引的排序和需要的结果排序方式匹配，可以节省重新排序的时间。

l  如果所有涉及字段都在索引中，不需要再查询实际数据表。

2.2    创建索引

根据业务需要分析出使用频率较高、数据量较大、关联查询较多的表，然后根据可能的查询操作设定合适的索引。

如果是系统已完成开发，最好的方式是统计执行的查询语句，按使用频率、重要程度设定优先级，针对性的建立索引。

下面是一些必须要创建索引的场景：

l  首页各信息面板（系统入口）

l  各模块主界面数据查询和暂时（主工作场景）

l  各数据统计导出操作（耗时较长的功能点）

l  。。。

创建索引需要注意以下几点：

l  仅选择查询条件和排序包含的字段作为索引字段

l  如果需要返回的字段非常少，也可以作为索引的一部分

l  注意索引的字段顺序和排序方式和业务需要尽可能的匹配（Where条件的字段和索引前面的字段匹配，Order和剩余的字段匹配或剩余部分的前部分匹配）

l  注意索引字段的长度总和不能超过900字节（数据库存在限制）

l  索引名称统一为IX_TABLENAME_COLNAME（全部大写、第二段为表名，第三段为首字段名，如果为多个，后面增加序号）

2.3    控制索引数量

         由于索引的增加会影响更新的速度，所以在进行业务分析的时候，最好仅提供必须、足够的操作，屏蔽其他操作，这样可以减少所需索引的数量，保证更新操作的顺利进行。

         小数据量表可以不建立索引，大数据量表除主键之外以6个以内为准。

2.4    保持索引有效

无效的索引，除了增加磁盘存储、降低操作速度，没有任何用处，只有在和查询语句匹配的时候，索引才可以发挥应有的作用，匹配度越高，查询速度越快。

在进行产品研发、维护、项目二次开发的过程中，需要根据业务和开发的情况，随时对现有索引进行增删和调整，保持索引的有效性。

2.5    重建索引

由于索引数据会因为不断的更新发生变化，可能无法保持最利于查询的状态，因此每隔一段时期对索引进行重建，也会起到一定的效果。

3      语句编写

一般系统80%以上的操作都是数据查询操作，基本上100%的数据库更新操作之前都会有数据查询动作。

编写合适的查询语句，建立相匹配的索引，以加快查询的速度，这也是数据库优化的最通用和有效的手段。

3.1    数据库缓存

当SQL Server执行SQL 语句时，如果需要的数据已经在其内存中，则直接从内存缓冲区读取并进行必要的运算然后输出执行结果。如果数据还未在内存中，则首先将数据从磁盘上读入内存Buffer中。而我们通常评价SQL性能指标中的IO逻辑读取数对应的正是从内存缓冲区读取的页数，而IO物理读取数则对应数据从磁盘读取的页数。

数据库会分配一定的内存区域，用于缓存从磁盘读入的实际数据以及索引数据，以便减少磁盘IO。

由于内存大小相对于整个数据库数据文件来说，还是比较小，所以缓存会将使用不频繁的数据从内存移除，以便读入新的数据，再次用到原来的数据时，只能重新读入。

所以只有真正合理的利用缓存，提高缓存命中率，才可以真正的减少磁盘IO。

提高缓存利用率的原则是：让用不到的数据永远不被读入到缓存(这里包括实际的数据以及索引数据)，只有这样，才可以不挤占常用数据的空间。

         提高缓存利用率的具体方式，主要还是从查询语句和索引的着手。

         数据库的缓存大小对系统其它系统的运行也会有一定的影响，所以数据库服务器缓存的大小，需要根据实际情况进行设定。

缓存优化：我们可以选择针对那些执行计划占用较大内存、而被重用次数较少的SQL语句进行重点分析。看其调用方式是否合理；

 

3.2    执行计划分析

         数据库都会提供sql语句的查询计划分析工具，用于进行查询语句的调优，虽然实际的执行计划以及执行时间会因为各种情况出现一定的偏差，但还是可以用于进行查询语句的优化。

3.2.1  分析工具

         下图为SqlServerd的计划分析工具入口（新建查询后显示的工具栏上）：   

          

         注：其中按钮一作用是直接对当前语句进行计划分析，按钮二的作用是在点击“执行”执行语句的同时显示执行计划。

 

下图为分析后给出的查询执行计划图示（执行的动作、顺序及时间百分比）：

         下图为具体的步骤的执行信息图示（IO和cpu开销，估计执行次数，算法和输出）：

3.2.2  优化要点

匹配较好的查询语句和索引，其执行计划的调整，需要考虑以下几点：

l  减少最终需要从磁盘读取的数据量（磁盘IO）

l  最快的速度减少参与中间计算的数据量（嵌套查询及计划分析）

l  减少最终返回的数据量（返回结果）

l  减少内存的运算量（排序、联合等等）

3.2.3  步骤分析

计划经过分解后，会将整条语句解析成不同的动作类型，其中主要的步骤类型如下：

3.2.3.1 表扫描

通过直接遍历查询整个数据表，来查找需要的数据。当不存在合适的索引，或者数据库认为执行索引会更慢的情况下，会执行全表扫描。对于大数据量表的查询，进行全表遍历非常慢，其执行计划中，不应该存在此类执行步骤。

 

3.2.3.2 索引扫描

如果有匹配的索引，会根据索引进行数据查找，执行计划中会 出现此类步骤。由于进行索引查询比全表遍历的速度快很多。所以，性能优化时，需要尽可能的将“表遍历”转变为“索引扫描”。

 

3.2.3.3 键查找

即使查询已经匹配了索引，如果条件、排序、返回结果中存在索引中没有的字段时，还是需要再对实际的数据表进行查找，获取需要的数据参与计算。

如果索引扫描步骤已经过滤掉了大部分数据，需要按键查找其他字段的数据量比较小时，速度会比较快（相对于“索引扫描”来说，会占用更多的时间，所以仍是优化的对象）。

 

注：对于“键查找“，优化方式如下：

l  条件、排序相关字段应该尽量在索引中，不能因此引起“键查找”

l  最终的返回结果需要的字段较多，索引中不存在时，可以引起“键查找”

l  参与查询的字段较少时，所有的字段都可以建立在索引中，不需要进行“键查找”

3.2.3.4 排序

返回的数据记录，一般都要求进行排序，如果查询到的中间结果与要求的排序顺序不一致时，需要在内存中对其进行重新排序。由于计算量非常大，所以排序的速度也非常慢。

 

对于“排序“，优化方式是：

l  建立合适的索引或主键，和最后要求的排序顺序完全一致，不需要进行“排序”

l  在进行排序之前，尽可能的通过过滤条件，降低参与排序的记录数量，减少最终排序需要进行的循环和判断次数

3.3    SQL示例

本章节以实际的表结构sql示例，演示不同查询不同的索引及查询语句对于执行效率的影响，以帮助大家理解性能优化的必要性和基本方法。

这些示例会使用“dbccdropcleanbuffers”命令提前清除缓存，并使用“select getdate()”语句获取执行时间

 

3.3.1  缓存的作用

3.3.1.1 查询1

无缓存的情况下，进行全表扫描，耗时将近6s

dbcc dropcleanbuffers

select getdate()

select ID,NAME,CONTENT,CREATETIMEfrom Test whereNAME = 'bdff'

select getdate()

3.3.1.2 查询2

多次执行后，不清除缓存，需要进行全表扫描，耗时0.060s

select getdate()

select ID,NAME,CONTENT,CREATETIMEfrom Test whereNAME = 'bdff'

select getdate()

 

3.3.1.3 结论

单用户对100万条记录进行全表扫描就需要花费6秒，说明了全表遍历的速度非常慢。

不清理缓存，再次执行时花费0.060秒，说明之前的查询时间都大部分消耗在了io上，缓存命中率较高的情况下，性能提升和io降低非常明显。

即使数据都在缓存中，查询出结果耗时0.060毫秒，说明内存计算部分，如果不合理规划，也会占用很多时间，也需要想办法进行优化。

随着数据量的增大、并发的增多，不常用数据会被很快移出缓存，命中率会不断下降，查询速度会受到很大影响，所以不能简单认为有缓存支持就可以了，还需要想办法优化查询计划，提高缓存命中率。

3.3.2  索引扫描

3.3.2.1 查询1

100万数据，先清除缓存，需要进行全表扫描，耗时6s

dbcc dropcleanbuffers

select getdate()

select ID,NAME,CONTENT,CREATETIMEfrom Test whereNAME = 'BDFF'

select getdate()

3.3.2.2 查询2

100万数据，先清除缓存，需要匹配索引，并进行键查找，耗时0.067s

dbcc dropcleanbuffers

select getdate()

select ID,NAME,CONTENT,CREATETIMEfrom Test whereID = 'BDFF'

select getdate()

3.3.2.3 查询3

100万数据，先清除缓存，仅需要匹配索引，耗时0.023s

dbcc dropcleanbuffers

select getdate()

select ID, CREATETIMEfrom TEST whereID = 'DBFF'

select getdate()

3.3.2.4 结论

就搜索算法而言，纯索引扫描最快，带键查找的索引扫描次之，全表扫描最慢。因此，优化过程中，应主要避免表扫描的存在，减少键查找的使用，尽量仅采用索引扫描。

        

3.3.3  索引匹配度

3.3.3.1 查询1

按时间排序和查询，获取前50行，耗时大于6s

dbcc dropcleanbuffers

select getdate()

select *from

(selectID,CREATETIME,ROW_NUMBER() over(orderby CREATETIME)as idx from Test whereCREATETIME > 10000)A

where idx> 0 and idx < 50

select getdate()

3.3.3.2 查询2

按ID排序和查询，获取前50行，耗时0.177s

dbcc dropcleanbuffers

select getdate()

select *from

(selectID,CREATETIME,ROW_NUMBER() over(orderby ID)as idx from Test  )A

where idx> 0 and idx < 50

select getdate()

 

3.3.3.3 查询3

按ID排序和查询，指定更精确的查询条件，获取前50行，耗时0.013s

dbcc dropcleanbuffers

select getdate()

select *from

(selectID,CREATETIME,ROW_NUMBER() over(orderby ID)as idx from  Test whereID='00001D60-66FD-44AA-B9B2-086434ED7D5F')A

where idx> 0 and idx < 50

select getdate()

3.3.3.4 结论

即使查询采用了索引，下列情况也会导致查询效率很低：

l  没有条件或者条件过滤作用太差，中间结果太多

l  排序字段和索引不匹配，内存中需要进行大量的排序计算

 

4      经验分享

4.1    优化建议

1.      建立匹配的索引，避免全表扫描（会占用大量io）

2.   建立并遵循SQL编码规范，SQL语句应简洁、易读、便于维护

3.   为提高查询性能，应限制操作的数据量，尽量在小结果集上操作，减少行数和列数

4.      使用高选择性的where子句，让数据库返回必须的数据集，而不是返回大量数据给应用程序，再由应用程序进行筛选

5.      Where子句中列的顺序与需使用的索引顺序保持一致

6.      可能引起索引失效的操作，如NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE, like ‘%XXX’及函数等

7.      尽量使用“>=”，不要使用“>”

8.      LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%XXX%’这种查询不会引用索引，而LIKE ‘XXX%’则会引用范围索引

9.      尽量用相同数据类型进行比较，避免发生数据类型转换

10.  当需要验证是否有符合条件的记录时，使用exists，不要使用count(*)，前者在第一个匹配记录处返回，后者需要遍历所有匹配记录

11.  在有min、max、distinct、order by、group by操作的列上建索引，避免额外的排序开销

12.  尽量保持短事务，尽量不要在事务中处理与数据库操作无关的工作

13.  对于无法优化的SQL，需要从业务上进行合理的拆分

14.  避免对于大数据量表全表进行数量统计（会对全表或全索引进行遍历）

15.  避免复杂的多表关联（避免4个以上的表关联查询）

16.  减少查询的嵌套（不能超过两层）

17.  不允许使用子查询获取字段的值

18.  不允许进行视图嵌套

19.  限制返回的结果数量和字段数（简单操作不允许超过100行，导出和统计类操作不允许超过5000条，否则业务设计应该进行调整）

20.  应尽量避免在 where 子句中对字段进行表达式操作（select id from t where num/2=100）

21.  应尽量避免在where子句中对字段进行函数操作

22.  用Where子句替换HAVING子句

23.  查询尽量用确定的列名，少用*号（Select * from）

24.  注意一些or子句和union子句之间的替换；对于有比较多or运算的查询，建议分成多个查询，用union all联结起来；

25.  用Union all替换Union（UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果）

26.  尽量避免使用insert、update做大批量的数据处理（大数据操作会使页级锁升级为表级锁）

27.  不对进行大数据量数据统计（至少应使用过滤条件过滤后再进行统计）

28.  减少distinct、order by、group by、having、join等查询方式（会引起计算）

29.  复杂查询中尝试降低表间关系（减少多对多操作）

30.  注意存储过程中参数和数据类型的关系

31.  避免过多的使用触发器（触发器有较高的执行效率，但是过多的触发器会降低程序的可维护性）

32.  对于不需要在用户界面上展示，但需要与数据库进行多次、大量数据交互的情况，最好的选择就是使用存储过程，可以减少网络通讯次数，降低网络流量，而存储过程已经过预编译，其执行速度也很快

33.  开发中减少访问数据库的次数

34.  对于以上所述内容，在不同版本的数据库上可能存在一定的差异，具体以实际执行结果为准

5      死锁

5.1    定义

 当多个进程同时访问一个数据库时，其中的每个进程拥有的资源都是其他进程所需的，由此造成的每个进程都无法继续下去的情况。  

5.2    预防

1、 按同一顺序访问数据库对象

不同的存储过程、触发器、动态SQL语句段按照同一的顺序同时访问多张表；

2、 尽量使用短事务

使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；

3、 避免事务中的人为干预

避免在事务操作中，处理大数据或需要与用户交互的操作；

4、 使用低隔离级别

尽量不要人为提高事务级别，使用默认或降低操作级别；

5、 避免批量Insert或Update大量数据

6、 对于访问频繁的表添加索引