数据表结构的设计与性能优化

1       数据表结构的设计与性能优化

1.1    、数据表的存储原理

SQL Server每次读取1个存储块，每个存储块大小为8KB，每读取1个存储块计算为1个逻辑读。

问题：如果数据内容非常大，像我们系统中的Feeling字段非常大，就会导致每个存储块存放的数据行数会非常少，这样当我们读取数据时，要读取许多的存储块。

 

存储块1（8KB）

聚集索引	数据内容
聚集索引	数据内容
聚集索引	数据内容
聚集索引	数据内容

 

 

存储块2（8KB）

聚集索引	数据内容
聚集索引	数据内容
聚集索引	数据内容
聚集索引	数据内容

 

1.2    表设计的优化

1.2.1、        字段类型优先级

Bit>int，int>date,time>char,varchar>text，原因：整型，time运算快，节省空间。

所以我们在表设计时，如果是bool类型的数据值就不应该用int

类型字段。

1.2.2、        聚集索引字段类型的选择

主键的索引结构中，即存储了主键值，又存储了行数据，这种结构称为“聚集索引”

在插入数据时，数据节点会分裂，这个问题比较严重，节点下存储了“行数据”，分裂的时候，还要移动行数据。如果主键是无规律的，则会加速它的数据节点分裂，且效率极低。
 

高性能索引策略：

主键，尽量用整型，而且是递增的整型。如果是无规律的数据，将会产生页的分裂，影响速度。索引长度直接影响索引文件大小，影响增删改的速度，并间接影响查询速度（占用内存多）。因为主键索引在物理存放时是有序的，如果主键的值是无序的，那么主键每次插入时，索引文件都重新进行排序，会产生额外的数据消耗，另外主键的叶子上存放的数据，还会导致叶子数据行的移动和分裂，又会产生一些消耗，所以主键尽量用整型，且自增的类型。

 

 

 

1.2.2、        纵向拆分

 

纵向拆分方法：把不需要用于查询的大字段，放到另外一个新建的附属表中，如feelingStructured表和Auto表。这样就将Evaluation表的数据内容减少到最少，存储块中可以多存储许多数据行，减少程序读取存储块的个数。

 

1.2.3、        横向拆分

横向拆分方法：表分区，表分区的条件，一张数据表的行数至少要达3000W行以上的数据，就可以考虑做表分区了。但这不是绝对，如果表的数据行内容特别多，查询特别慢时，也可以尽早做表分区。

注意问题：普通表在查询时，会比分区表要快一些，因为基于分区表的查询会遍历所有的分区表，而普通表只查询了普通表一个表。

解决办法，在查询条件中加入分区条件，这样查询就会落入指定的分区中，不用遍历所有的分区，但问题是，是不是所有的查询都能加入分区条件呢。只要进行了表分区，那么SQL的前提条件就是所有SQL都要加上分区条件，除非个别的汇总，统计类的SQL。

 

1.2.4、        数据库分库

数据库分库：当一个台数据库表服务器访问压力过大，数据量过大时，就需要考虑进行数据库分库，数据库分库条件和表分区的逻辑是比较像的。根据业务条件，如地区，时间，进行拆分。

1.2.5、        读索引为什么比读表快

这里面引发出一个触类旁通的问题，为什么索引查询会比直接查数据要快？因为索引做为一个独立的数据存储区，也是跟数据表存储块一样，以8KB为一个存储块，一个IO读取一次存储块，而索引中只有简单的几个索引列，而不是整个数据行的数据，所以它一个IO读取的数据会非常多，这样它的IO就会非常少，加快了查询速度。

1.2.6、        数据压缩的利与弊

数据压缩和索引压缩会使存储空间和逻辑读减少，但是会使表更新的开销加大，查询耗费的CPU也更多，所以压缩表一般适合在更新比较少，且CPU消耗不大，IO消耗很大系统中适用。像企业管理软件就比较适用于数据压缩和BI系统。如果当前系统的IO并不高，但CPU非常繁忙，则不应该采用表和索引压缩，传统数据库的压缩率并不是太高，真正压缩率比较高的应该是BI的数据。

2      索引优化

2.1    索引列的设计与优化

索引列数据的重复度称为可选择性，如性别列的取值范围为”男，女”，这个索引列可选择性就比较低，你在里面能找出太多相同的数据列出来，如选择列数据内容为唯一的，则可选择性非常高。我们选择索引列时，要尽量选择高选择性的列。

案例分析：现在有一个商家黑盒处罚功能，假如商家发布违规的商品和促销信息，则会被系统管理员设置为5天或10天的墨盒状态，墨盒状态期间无法发布商品和促销信息，现在设计有商家黑盒表，有3字段，BusinessID（商家ID），生效开始时间（StartTime）、生效结束时间（EndTime），这生效开始时间和生效结束时间2个列谁的选择性高呢？

示例业务数据：

BusinessID	StartTime	EndTime
11111	2014-3-1	2014-3-5
223333	2015-4-8	2015-4-13-
23423424	2016-1-13	2016-1-18

 

现在找出今天还属于黑盒的商家名单列表

 

Where startTime<=’2016-1-14’ and EndTime>=’2016-1-14’

 

大家可以看到，符合startTime<=’2016-1-14’的有3条记录，而符合EndTime>=’2016-1-14’的只有1条记录，所以EndTime的可选择性比StartTime高，这就会决定，我们到底是采用哪一列做索引列，如果2个列都做索引列，哪个索引列会排在前面（多列索引的设计见下面）。

2.2    表扫描查询如何修改为索引列扫描

如：我们项目中以前用来判断用户是否填写Feeling时，用where len(feeling)>0，len(best)>0，

Len(wrost)>0，

在where条件中用函数会导致表扫描，所以应该设计成标识字段，如IsEditFeeling(bit)，IsWroteBest(bit)，IsWroteWrost(bit)，SQL就可以优化成where IsEditFeeling=1，IsWrostBest=1，IsWroteWrost=1。这样就是索引扫描。

2.3    单列索引的设计与优化

单列索引设计比较简单，一般就是根据业务条件来定义就行，如根据车系ID或车型ID。但要注意索引列的可选择性。

2.4    多列索引的设计与优化

2.4.1、        多列索引的存储规则

假设某个表有一个联合索引(a,b,c)，我们来看下在这个索引中是如何存储这些字段数据的。

 

 

 

 

2.4.2、        多列索引左前缀规则

 

多列索引必须用到第1个，否则不生效。

 

 

2.4.3、        多列索引列的选择优化

根据以上的多列索引列的存储规则和左前缀规则，在建多列索引时，应该将可选择项高的列放在最左边，后面依次类推，如上面的黑盒案例分析中，应该将endTime列放在最左边，然后才是starttime列。

 

在选择性相同的情况下，应该把等值（如：=）的放在左边，不等值（如：>，<）放在后面。

Select * from t where object_id>=20andobject_id<2000 and object_type=’TABLE’;

这个查询对应的索引，应该建成

Create index idx_id_type on t(object_type,object_id);

 

2.5    书签查找优化

2.5.1、        数据表物理存储和索引的物理索引

 

 

数据存储块1（8KB）

聚集索引	数据内容
聚集索引	数据内容
聚集索引	数据内容
聚集索引	数据内容

 

 

数据存储块2（8KB）

聚集索引	数据内容
聚集索引	数据内容
聚集索引	数据内容
聚集索引	数据内容

 

 

索引存储块1（8KB）

非聚集索引1	非聚集索引2	非聚集索引N
非聚集索引1	非聚集索引2	非聚集索引N
非聚集索引1	非聚集索引2	非聚集索引N
非聚集索引1	非聚集索引2	非聚集索引N

 

因为索引里面只存储了固定了几列，如果查询时需要读取索引列之外的数据，就需要到数据存储块，根据聚集索引去重新查找我们想要的数据，这就叫书签查找。

2.5.2、        书签查找的缺点

 

 

 

找出书签查找中查找的列

 

2.5.3、        书签查找优化

 

 

2.6    索引与排序的优化

索引在物理存储上是有序的，所以如果我们的SQL的排序是基于索引列进行的，那么不需要再重新进行排序，反之，系统会在temp库中建立表变量或临时表，然后在这个表变量或临时表中重新进行排序。

Create index idx_t on t(col1 desc,col2 asc)

Select * from t order by col1 desc,col2 asc

0：sorts(memory)

0：sorts(disk)

 

SQL Server 执行时间:

   CPU 时间= 2182 毫秒，占用时间= 231 毫秒。

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒，占用时间= 0 毫秒。

 

SQL Server 执行时间:

   CPU 时间= 0 毫秒，占用时间= 2 毫秒。

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒，占用时间= 0 毫秒。

2.7    关于索引的坏处

在有几个索引和情况下和只有主键索引的情况下，插入数据的速度相差10倍，而且数据表里数据记录越大，插入速度越明显。

在无索引的情况下，表的记录越大，插入的速度只会受到很小的影响，基本不会越慢。

在无索引的情况下，分区表的插入要比普通表更慢，因为插入的数据需要做判断，有这方面的开销。

解决办法：读写分离，在主库（或叫写库）上只有主键索引，而没有别的索引。只读库上有许多用于查询的索引，然后写库的数据定时同步到只读库上，这样的话，插入时不会因为索引的原因导致插入变慢，也不会因为没有索引导致查询变慢。

2.8    索引建立时的开销及注意事项

建索引过程会产生全表锁和建索引过程中会产生全表排序.

后果：普通的对表建索引将会导致针对该表的更新操作无法进行，需要等待索引建完，更新操作将会被建索引的动作阻塞。

 

解决办法：

CREATE NONCLUSTERED INDEX [IX_Auto_Serial]ON [dbo].[Auto]

(

    [Serial] ASC,

    [RowStatus] ASC

)

 WITH (  ONLINE = ON) 

 

在创建索引时，加上online参数，这种建索引的方式不会阻止针对该表的更新操作，与建普通索引相反的是，online建索引的动作是反过来被更新操作阻塞。

3      多表联查优化

3.1    、字段冗余，不要联查

业务案例分析：在口碑表里冗余新建一个LastAppendingDrivenKilometers字段，这样就不用关联追加表进行查询了。再冗余新建一个AutoBoughtCity字段，就不用关联Auto表进行查询了。

3.2    、多表联查的实现原理及表关联字段的设计原则

三大表连接的概要说明：1、Nested Loops Join。2、Hash Join。3、Merge Sort Join

Nested Loops Join驱动结果集的条数决定被驱动表的访问次数

Hash Join两表各自只会访问1次或0次。

Merge Sort Join与Hash Join的相同

 

表驱动顺序与性能（Nested LoopsJoin性能与驱动顺序有关）

Hash Join性能与驱动顺序有关（和NL相似）

Merge Sort Join性能与表驱动顺序无关

 

 

Nested Loops Join优化要点

1：驱动表的限制条件要有索引。2、被驱动表限制条件要建立索引。3：确保小结果集先驱动，大的被驱动。

 

Hash Join忧化要点：1：请确保用在全扫描的OLAP场景。2：明确该SQL是否限制HashJoin。3、两表无任何索引倾向HashJoin。第1斧：两表限制条件有索引（看返回量）。第2斧：要小结果集先驱动，大的被驱动。第3斧：尽量保证PGA能容纳Hash运算。

 

Merge Sort Join优化第1式（两表限制条件有索引）

Merge Sort Join 优化第2式（连接条件索引消除排序，即消除2边分别排序，再合并的这种情况）

 

因为被驱动表的查询是依赖与ON 条件中写的字段列，如： A join B on A.CID=B.C ID，

那么应该B表的CID设置为索引列，最好设置为聚集索引列，这样关联SQL在运行时，从B表中查找数据时，可以命中索引，否则在B表中查找数据时，会引发表扫描和Hash匹配。

 

代码示例：

 

优化前的效果

 

(1 row(s) affected)

表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 'EvaluationPurposeTerms'。扫描计数 1，逻辑读取 1383 次，物理读取 3 次，预读 2269 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0次。

表 'Evaluation'。扫描计数 1，逻辑读取 110 次，物理读取 2 次，预读 80 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 

(1 row(s) affected)

 

 SQLServer 执行时间:

  CPU 时间 = 140 毫秒，占用时间 = 155 毫秒。

 

 

 

优化后的SQL:

(1 row(s) affected)

表 'EvaluationPurposeTerms'。扫描计数 0，逻辑读取 1638 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 'Evaluation'。扫描计数 1，逻辑读取 20 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQLServer 执行时间:

  CPU 时间 = 0 毫秒，占用时间 = 4 毫秒。

 

 

 

3.3    、关联查询的优化（使用表扫描）

QualityProblem是一个数据表，QualityProblemDictionary是一个字典表，字典表的数据基本上是固定不变的，大概80行的样子。而在与QualityProblem表关联时，QualityProblem表中符合条件数据的，每一行都会读取一次QualityProblemDictionary字典表。SQL和运行效果如下，现在打算将QualityProblemDictionary字典表一次全读取到内存中，这样就不用每条符合条件的QualityProblem表中数据都去访问QualityProblemDictionary字典表了，修改方法就是在表的Hint中指出不要用索引扫描，要采用表扫描。代码：with(index(0))

 

SELECT [QualityId] ,[ItemId] AS [ID],d.Name ,d.FullName ,d.FullPath ,d.Level ,d.ParentId FROM [QualityProblem] as pwith(nolock)

join [QualityProblemDictionary] as dwith(nolock) on d.Id  = p.ItemId wherep.QualityId =  592805 

优化前效果：

(7 row(s) affected)

表'QualityProblemDictionary'。扫描计数7，逻辑读取14 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'QualityProblem'。扫描计数1，逻辑读取3 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

 

 SQL Server 执行时间:

  CPU 时间= 15 毫秒，占用时间= 23 毫秒。

 

as d with(index(0),nolock) on d.Id  = p.ItemId

 

优化后效果：

(7 row(s) affected)

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'QualityProblemDictionary'。扫描计数1，逻辑读取19 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'QualityProblem'。扫描计数1，逻辑读取3 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

 

 SQL Server 执行时间:

  CPU 时间= 0 毫秒，占用时间= 2 毫秒。

4      查询计划的缓存与CPU高优化

4.1    、概述

我们SQL执行分为2部分，1是SQL查询计划编译部分，2是SQL运行部分。SQL分析器里显示的CPU时间也是包括编译时间和运行时间。

 

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒，占用时间= 0 毫秒。

SQL Server 执行时间:

   CPU 时间= 2668 毫秒，占用时间= 786 毫秒。

 

因为生成SQL的查询计划是非常耗时和耗CPU的，SQL Server在生成查询计划之后，会将已经生成的查询计划保存到计划缓存中，下次再执行该SQL时不用再重新生成一个全新的计划。

4.2    、如何使查询计划被缓存

4.2.1、        sp_executesql存储过程

普通的即席SQL的查询计划是没有被缓存的，通过下面的示例可以看出来。

 

 

通过把SQL改写为用sp_executesql存储过程，即可实现查询计划被缓存。ado.net底层就是把我们写的SQL全部生成sp_executesql sql这种格式的sql，但ado.net无法自动区分我们sql中哪些是参数，哪些不是参数，所以光靠ado.net帮我们自动实现sp_executesql还无法实现查询计划的缓存，还需要我们手动把查询条件参数化写完整。最终实现一个标准的语法

Sp_executesql @sqltext,@param1,,,,,,@paramN

 

 

4.2.2、        存储过程和函数

存储过程和函数的查询计划也会被缓存。

4.3    、如何解决查询计划不被缓存

只要采用sp_executesql，存储过程，函数这3种方式写的sql查询计划就会被缓存，但也有原因会导致他们不会被缓存，原因如下：

4.3.1、        查询计划不被缓存案例示警

上次线上数据库CPU总是高，从DMV中查到编译数过高，原因是开发人员为线上跟踪代码运行结果和查错方便，在公共数据访问层中，自动为每一条SQL中都加了一条注释，该注释内容为当前系统的Url，因为一个公共方法会被不同模块调用，当前模块的Url自然也是不停的在变，导致SQL的查询计划无法重用。

4.4    、缓存的查询计划什么时候被重新生成

1、当表数据增长率达到当前数据量的30%时，查询计划会被重编译。

2、当新建和修改索引时。

3、当新建和删除字段时。

 

4.5  、In条件参数化   

Declare @User1 int;Declare @User2int;Declare @User3 int;Declare @User4 int;declare @User5 int;

Where userId in(@User1,@User2,@User3,@User4,@User5) 

 

 

5      并发和阻塞问题

1、减少事务长度。

2、SQL语句加Nolock。

3、读写分离

6      日志库优化

当有insert和update,delete操作时，会在日志库中记录日志，随着时间的积累，日志库记录越来越多，每插入一条日志都非常耗时，直接影响我们系统的数据操作。

 

解决办法：设定每天晚上自动备份，在数据库备份之后，数据库会自动收缩日志库，删除一些日志数据，这样日志库的数据量就下来了。因为数据库觉得你已经备份过了，万一出什么问题，可以用备份文件来恢复，也不需要依靠日志库来进行还原了，没必要再保存那么多日志了。

7      SQLServer服务器系统参数配置与性能优化

7.1    、CPU最大并行度

详细描述文档：http://jimshu.blog.51cto.com/3171847/1266978

 

7.2    、并行性开销阀值

 

7.3    、优化即席工作负载

 

7.4    、数据库文件布局

将数据库文件分别存放在不同的服务器上，以加快并行处理速度。

 

8      DB服务器监控

参考附件