SQLSVR数据库优化若干

1）从SQL SERVER 2005开始，数据库不默认生成NDF数据文件，一般情况下有一个主数据文件（MDF）就够了，但是有些大型的数据库，由于信息很多，而且查询频繁，所以为了提高查询速度，可以把一些表或者一些表中的部分记录分开存储在不同的数据文件里（和分区原理类似）

1. SQL Server按照同一个文件组里面的所有文件现有空闲空间的大小，按这个比例把新的数据分布到所有有空间的数据文件里，如果有三个数据文件A.MDF，B.NDF，C.NDF，空闲大小分别为200mb，100mb，和50mb，那么写入一个70mb的东西，他就会向ABC三个文件中一次写入40、20、10的数据
2. 日志文件是按照顺序写入的，一个写满，才会写入另外一个
3. 由上可见，如果能增加其数据文件NDF,有利于大数据量的查询速度，但是增加日志文件却没什么用处。

2）在SQL Server 2005中，默认MDF文件初始大小为5MB，自增为1MB，不限增长，LDF初始为1MB，增长为10%，限制文件增长到一定的数目，一般设计中，使用SQL自带的设计即可，但是大型数据库设计中，最好亲自去设计其增长和初始大小，如果初始值太小，那么很快数据库就会写满，如果写满，在进行插入会是什么情况呢？当数据文件写满，进行某些操作时，SQL Server会让操作等待，直到文件自动增长结束了，原先的那个操作才能继续进行。如果自增长用了很长时间，原先的操作会等不及就超时取消了（一般默认的阈值是15秒），不但这个操作会回滚，文件自动增长也会被取消。也就是说，这一次文件没有得到任何增大，增长的时间根据自动增长的大小确定的，如果太小，可能一次操作需要连续几次增长才能满足，如果太大，就需要等待很长时间，所以设置自动增长要注意一下几点：

1. 要设置成按固定大小增长，而不能按比例。这样就能避免一次增长太多或者太少所带来的不必要的麻烦。建议对比较小的数据库，设置一次增长50 MB到100 MB。对大的数据库，设置一次增长100 MB到200 MB。
2. 　　要定期监测各个数据文件的使用情况，尽量保证每个文件剩余的空间一样大，或者是期望的比例。
3. 　　设置文件最大值，以免SQL Server文件自增长用尽磁盘空间，影响操作系统。
4. 　　发生自增长后，要及时检查新的数据文件空间分配情况。避免SQL Server总是往个别文件写数据。
5. 　　因此，对于一个比较繁忙的数据库，推荐的设置是开启数据库自动增长选项，以防数据库空间用尽导致应用程序失败，但是要严格避免自动增长的发生。同时，尽量不要使用自动收缩功能。

3）数据和日志文件分开存放在不同磁盘上

1. 数据文件和日志文件的操作会产生大量的I/O。在可能的条件下，日志文件应该存放在一个与数据和索引所在的数据文件不同的硬盘上以分散I/O，同时还有利于数据库的灾难恢复

4）表分区，索引分区

1. 当一个表的数据量太大的时候，我们最想做的一件事是什么？将这个表一分为二或者更多分，但是表还是这个表，只是将其内容存储分开，这样读取就快了N倍了
2. 原理：表数据是无法放在文件中的，但是文件组可以放在文件中，表可以放在文件组中，这样就间接实现了表数据存放在不同的文件中。能分区存储的还有：表、索引和大型对象数据 。
3. 一般表的大小超过2GB要进行分区（由具体数据而定）
4. 表中包含历史数据，新的数据被增加到新的分区中。
5. 表分区有以下优点： 
     1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 
     2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 
     3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； 
     4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

5）分布式数据库设计，这个比较灵活和复杂，下次单独作为一个内容讨论。

6）整理数据库碎片

1. 如果你的表已经创建好了索引，但性能却仍然不好，那很可能是产生了索引碎片，你需要进行索引碎片整理
2. 在SQLServer数据库，通过DBCC ShowContig或DBCC ShowContig(表名)检查索引碎片情况，指导我们对其进行定时重建整理

解决方式：

1. 1. 一是利用DBCC INDEXDEFRAG整理索引碎片
   2. 二是利用DBCC DBREINDEX重建索引。

      两者区别调用微软的原话如下：

1. DBCC INDEXDEFRAG 命令是联机操作，所以索引只有在该命令正在运行时才可用，而且可以在不丢失已完成工作的情况下中断该操作。这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。
2. 重新创建聚集索引将对数据进行重新组织，其结果是使数据页填满。填满程度可以使用 FILLFACTOR 选项进行配置。这种方法的缺点是索引在除去/重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不会重新创建该索引。也就是说，要想获得好的效果，还是得用重建索引，所以决定重建索引。

 7）设计规范化表，消除数据冗余

1. 第一范式：属性(字段)的原子性约束，要求属性具有原子性，不可再分割；
2. 第二范式：记录的惟一性约束，要求记录有惟一标识，每条记录需要有一个属性来做为实体的唯一标识。
3. 第三范式：属性(字段)冗余性的约束，即任何字段不能由其他字段派生出来，在通俗点就是：主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们，当然外键除外)

8）存储过程、视图、函数的适当使用

1. 存储过程减少了网络传输、处理及存储的工作量，且经过编译和优化，执行速度快，易于维护，且表的结构改变时，不影响客户端的应用程序 
2. 使用存储过程，视图，函数有助于减少应用程序中SQL复制的弊端，因为现在只在一个地方集中处理SQL
3. 使用数据库对象实现所有的TSQL有助于分析TSQL的性能问题，同时有助于你集中管理TSQL代码，更好的重构TSQL代码

9）传说中的‘三少原则’

1. 数据库的表越少越好
2. 表的字段越少越好
3. 字段中的组合主键、组合索引越少越好
4. 当然这里的少是相对的，是减少数据冗余的重要设计理念

10）分割你的表，减小表尺寸

1. 如果你发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键的某个值为界线，将该表的记录水平分割为两个表。
2. 如果你若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表

11）字段设计原则

1. 数据类型尽量用数字型，数字型的比较比字符型的快很多。
2. 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。
3. 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
4. 少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。
5. 自增字段要慎用，不利于数据迁移

12）操作符优化

1. 　　IN、NOT IN 操作符（IN和EXISTS 性能有外表和内表区分的，但是在大数据量的表中推荐用EXISTS 代替IN）
2. 　　Not IN 不走索引的是绝对不能用的，可以用NOT EXISTS 代替
3. 　　IS NULL 或IS NOT NULL操作
4. 　　索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可

13）<> 操作符（不等于）

1. 不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 用其它相同功能的操作运算代替，如 a<>0 改为 a>0 or a<0    a<>’’ 改为 a>’’ 

14）用全文搜索搜索文本数据，取代like搜索

1. 全文搜索让你可以实现like不能完成的复杂搜索，如搜索一个单词或一个短语，搜索一个与另一个单词或短语相近的单词或短语，或者是搜索同义词
2. 实现全文搜索比实现like搜索更容易(特别是复杂的搜索)

15）SQL语句优化

1. 在查询中不要使用  select *
2. 尽量不要写没有WHERE的SQL语句
3. 注意SELECT INTO后的WHERE子句（因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程）
4. 对于聚合查询，可以用HAVING子句进一步限定返回的行

16）避免使用临时表

1. 除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替
2. 大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢

17）减少访问数据库的次数

1. 程序设计中最好将一些常用的全局变量表放在内存中或者用其他的方式减少数据库的访问次数

18）尽量少做重复的工作（尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：）

1. 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的
2. 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。
3. 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销
4. 合并对同一表同一条件的多次UPDATE，比如　：　

    UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’
　　UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’
　　这两个语句应该合并成以下一个语句
　　UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’  
　　WHERE EMP_ID=’ VPA30890F’

19）where使用原则

　　　　1. 第一个原则：在where子句中应把最具限制性的条件放在最前面。

　　select * from table1 where field1<=10000 and field1>=0;
　　select * from table1 where field1>=0 and field1<=10000;
　　如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。

　　2. 第二个原则：where子句中字段的顺序应和索引中字段顺序一致。

 

　　select * from tab where a=… and b=… and c=…;
　　若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。

 

 

 

　　3. select field3,field4 from tb where field1='sdf' 快
　　   select * from tb where field1='sdf' 慢，
　　   因为后者在索引扫描后要多一步ROWID表访问

 

4. select field3,field4 from tb where field1>='sdf' 快
　　 select field3,field4 from tb where field1>'sdf' 慢
　　 因为前者可以迅速定位索引。

 　　5. select field3,field4 from tb where field2 like 'R%' 快
　　    select field3,field4 from tb where field2 like '%R' 慢，
　　   因为后者不使用索引。

　　 6. select field3,field4 from tb where upper(field2)='RMN'不使用索引。
　　如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。 

 

　　

转载于:https://www.cnblogs.com/fyang/archive/2013/01/05/Freddy1.html