关于SQL SERVER数据库的性能优化经验

优化数据库的方法：

　　1、关键字段建立索引。

　　2、使用存储过程，它使SQL变得更加灵活和高效。

　　3、备份数据库和清除垃圾数据。

　　4、SQL语句语法的优化。（可以用Sybase的SQL Expert，可惜我没找到unexpired的序列号）

　　5、清理删除日志。

　　SQL语句优化的原则：

　　◆1、使用索引来更快地遍历表

　　缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：①.有大量重复值、且经常有范围查询（between, > ,< ，> =,< =）和order by、group by发生的列，可考虑建立群集索引；②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。

　　◆2、IS NULL 与 IS NOT NULL

　　不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

　　◆3、IN和EXISTS

　　EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

　　◆4、在海量查询时尽量少用格式转换。

　　◆5、当在SQL SERVER 2000中，如果存储过程只有一个参数，并且是OUTPUT类型的，必须在调用这个存储过程的时候给这个参数一个初始的值，否则会出现调用错误。

　　◆6、ORDER BY和GROPU BY

　　使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer将无法优化。

　　◆7、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

　　◆8、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。

　　◆9、SET SHOWPLAN_ALL ON 查看执行方案。DBCC检查数据库数据完整性。

　　DBCC(DataBase Consistency Checker）是一组用于验证 SQL Server 数据库完整性的程序。

　　◆10、慎用游标

　　在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。

　　总结：

　　优化就是WHERE子句利用了索引，不可优化即发生了表扫描或额外开销。经验证，SQL Server性能的最大改进得益于逻辑的数据库设计、

　　索引设计和查询设计方面。反过来说，最大的性能问题常常是由其中这些相同方面中的不足引起的。其实SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，以上这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

 

sql数据库优化 非常重要，如果sql数据库优化的不好，不仅会增加客户端和服务器端程序的编程和维护的难度，而且还会影响系统实际运行的性能。

那我们可以从哪些方面来进行sql数据库优化呢？

sql数据库优化之一 ：就是合理的数据库的设计。

当前我们使用最多的就是关系型数据库， 关系数据库设计是对数据进行组织化和结构化的过程，核心问题是关系模型的设计。对于数据库规模较小的情况，我们可以比较轻松的处理数据库中的表结构。然而，随着项目规模的不断增长，相应的数据库也变得更加复杂，关系模型表结构更为庞杂，这时我们往往会发现我们写出来的SQL语句的是很笨拙并且效率低下的。更糟糕的是，由于表结构定义的不合理，会导致在更新数据时造成数据的不完整。因此数据库的规范化流程尤为重要，它可以以指导我们更好的设计数据库的表结构，减少冗余的数据，借此可以提高数据库的存储效率，数据完整性和可扩展性。

那怎么才算是规范化的设计流程： 规范化设计的过程就是按不同的范式，将一个二维表不断地分解成多个二维表并建立表之间的关联，最终达到一个表只描述一个实体或者实体间的一种联系的目标。目前遵循的主要范式包括1 NF、 2 NF、3 NF、BCNF、4NF和 5NF等几种；在工程中3NF、BCNF应用得最广泛，推荐采用 3 NF作为标准。规范化设计的优点包括可有效地消除数据冗余，理顺数据的从属关系，保持数据库的完整性，增强数据库的稳定性、伸缩性、适应性。通常认为规范化设计存在的主要问题是增加了查询时的连接库表运算，导致计算机时间、空间、系统及运行效率的损失。在大多数情况下，这一问题可通过良好的索引设计等方法得到解决。 数据库设计中关键的步骤就是要确保数据正确地分布到数据库的表中。

比如说，一个客户的地址信息不应该被存储在不同的表中，因为这里的客户地址是雇员的一个属性。如果存在过多的冗余数据，这就意味着要占用了更多的物理空间，同时也对数据的维护和一致性检查带来了问题，当这个客户的地址发生变化时，冗余数据会导致对多个表的更新动作，如果有一个表不幸被忽略了，那么就可能导致数据的不一致性。

sql数据库优化之二：查询的优化

如何让你写的SQL语句跑的更快呢？影响我们代码速度的都有哪些可能性呢？ 不恰当的索引设计、不充份的连接条件和不可优化的where子句都有可能造成速度的下降。

首先来看看索引的建立。 微 软的 sql server 提 供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引），聚集索引简单理解就是数据的实际的存放位置：如我们的汉语字典正文本身就是一个聚集索引，当我们知道要查的字拼音首字母为A时，迅速缩小查询范围，翻到前几页就可以很快找到了，避免全表扫描，当然这种索引对于一个表只能有一个，因为只能按照一种方法进行排序存放，所以一定得选择最合适的聚集索引规则。非聚集索引，简单来说就是目录，比如字典中的偏旁部首目录，当我们要查找数据的时候我们先通过目录缩写范围，再进行查询目标的确认。下表我们可以作为参考建立适合的索引

 

 

描述	聚集索引	非聚集索引
列经常被分组排序	应	应
返回某时间范围内的数据	应	不应
小数目的不同值	应	不应
极少不同值	不应	不应
频繁更新的列	不应	应
频繁修改索引列	不应	应

 

 

索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引， 数据库就要做 更多的工作。过多的索引甚至会导致索引碎片。所以合适的索引才能使数据库得到性能的提高。

再者就是连接条件：其实在 多表链接操作被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、数据记录数多的表；

可以打开执行计划，看具体的执行情况，哪些环节的资源的占用大，是否可以优化查询或者优化结构。另外： 动态管理视图(DMV)和动态管理函数 (DMF)返回的服务器状态信息可用于监控服务器实例的运行状况、诊断问题和优化性能。

如：查找哪些批处理 /请求生成的I/O最多、查询以查看  CPU、计划程序内存和缓冲池信息、CPU 平均占用率最高 SQL 语句等。涉及的视图及函数如下：sys.dm_exec_sql_text、dm_exec_query_stats、dm_os_sys_info、dm_exec_cached_plans、dm_exec_query_plan、等

sql数据库优化之三：就是 where条件

我们建立了索引就不是就可以使查询的速度达到最快，而是在查询的时候要使用到索引，才会优化我们的查询速度。比例根据部门列建立了索引，但是我们在使用部门的查询条件的时候用’%XXX%’，此种条件是使用不到索引的，如下like便可以‘XXX%’; UNION 在进行表链接后会筛选掉重复的记录，而往往重复的基本上不存在，可以采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回结果。等等

其实sql数据库优化的地方还有很多，此处只是大致说明sql数据库优化的方向。我们如何去找我们的速度的瓶颈，从哪些方面去优化我们的数据库。

 

 

 关于SQL SERVER数据库的性能优化经验

1.索引：对于主键肯定是Cluster。像外键及一些查询经常用到的字段，如单据日期要建立索引。  
  2.查询优化：很多东西可以做成存储过程处理，有助于提高速度；  
  3.SQL语法：写程序时要注意了。这个要写可以写一大堆，如少用Like，联接查询，字段过滤关键字等；  
  4.触发器：尽量少用，或只处理必要的精简任务；  
  5.CURSOR游标：尽量不用；  
  6.事务及锁的使用：避免长时间占用锁及独占事务。  

 

 

速度，影响它的因数太多了，且数据量越大越明显。  
  1、存储  
        将硬盘分成NTFS格式，NTFS比FAT32快，并看你的数据文件大小，1G以上你可以采用多数据库文件，这样可以将存取负载分散到多个物理硬盘或磁盘阵列上。  
   
  2、tempdb  
        tempdb也应该被单独的物理硬盘或磁盘阵列上,建议放在RAID   0上，这样它的性能最高,不要对它设置最大值让它自动增长  
   
  3、日志文件  
        日志文件也应该和数据文件分开在不同的理硬盘或磁盘阵列上，这样也可以提高硬盘I/O性能。  
   
  4、分区视图  
        就是将你的数据水平分割在集群服务器上，它适合大规模OLTP,SQL群集上，如果你数据库不是访问特别大不建议使用。  
   
  5、簇索引  
        你的表一定有个簇索引，在使用簇索引查询的时候，区块查询是最快的，如用between，应为他是物理连续的，你应该尽量减少对它的updaet,应为这可以使它物理不连续。  
   
  6、非簇索引  
        非簇索引与物理顺序无关，设计它时必须有高度的可选择性，可以提高查询速度，但对表update的时候这些非簇索引会影响速度，且占用空间大，如果你愿意用空间和修改时间换取速度可以考虑。  
   
  7、索引视图  
        如果在视图上建立索引,那视图的结果集就会被存储起来，对与特定的查询性能可以提高很多，但同样对update语句时它也会严重减低性能，一般用在数据相对稳定的数据仓库中。  
   
  8、维护索引  
        你在将索引建好后，定期维护是很重要的，用dbcc   showcontig来观察页密度、扫描密度等等，及时用dbcc   indexdefrag来整理表或视图的索引,在必要的时候用dbcc   dbreindex来重建索引可以受到良好的效果。  
   
  不论你是用几个表1、2、3点都可以提高一定的性能，5、6、8点你是必须做的，至于4、7点看你的需求，我个人是不建议的。

 

1、使用索引来更快地遍历表。

缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。

一般来说：

a.有大量重复值、且经常有范围查询（ > ,< ，> =,< =）和order by、group by发生的列，可考
虑建立群集索引；

b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；

c.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。

用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。

2、在海量查询时尽量少用格式转换。

3、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。 

4、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。

Sql的优化原则2：

1、只要能满足你的需求，应尽可能使用更小的数据类型：例如使用MEDIUMINT代替INT

2、尽量把所有的列设置为NOT NULL，如果你要保存NULL，手动去设置它，而不是把它设为默认值。

3、尽量少用VARCHAR、TEXT、BLOB类型

4、如果你的数据只有你所知的少量的几个。最好使用ENUM类型

5、正如graymice所讲的那样，建立索引。

以下是我做的一个实验，可以发现索引能极大地提高查询的效率：

我有一个会员信息表users，里边有37365条用户记录：

在不加索引的时候进行查询：
sql语句A:
select * from users where username like ’%许%’;
在Mysql-Front中的8次查询时长为：1.40,0.54,0.54,0.54,0.53,0.55,0.54 共找到960条记录

sql语句B:
select * from users where username like ’许%’;
在Mysql-Front中的8次查询时长为：0.53,0.53,0.53,0.54,0.53,0.53,0.54,0.54 共找到836条记录

sql语句C:
select * from users where username like ’%许’;
在Mysql-Front中的8次查询时长为：0.51,0.51,0.52,0.52,0.51,0.51,0.52,0.51 共找到7条记录

为username列添加索引:
create index usernameindex on users(username(6));

再次查询：
sql语句A:
select * from users where username like ’%许%’;
在Mysql-Front中的8次查询时长为：0.35,0.34,0.34,0.35,0.34,0.34,0.35,0.34 共找到960条记录

sql语句B:
select * from users where username like ’许%’;
在Mysql-Front中的8次查询时长为：0.06,0.07,0.07,0.07,0.07,0.07,0.06,0.06 共找到836条记录

sql语句C:
select * from users where username like ’%许’;
在Mysql-Front中的8次查询时长为：0.32,0.31,0.31,0.32,0.31,0.32,0.31,0.31 共找到7条记录

在实验过程中，我没有另开任何程序，以上的数据说明在单表查询中，建立索引的可以极大地提高查询速度。
另外要说的是如果建立了索引，对于like ’许%’类型的查询，速度提升是最明显的。因此，我们在写sql语句的时候也尽量采用这种方式查询。

对于多表查询我们的优化原则是：

尽量将索引建立在：left join on/right join on ...　＋条件，的条件语句中所涉及的字段上。

多表查询比单表查询更能体现索引的优势。

6、索引的建立原则：
如果一列的中数据的前缀重复值很少，我们最好就只索引这个前缀。Mysql支持这种索引。我在上面用到的索引方法就是对username最左边的6个字符进行索引。索引越短，占用的

磁盘空间越少，在检索过程中花的时间也越少。这方法可以对最多左255个字符进行索引。

在很多场合，我们可以给建立多列数据建立索引。

索引应该建立在查询条件中进行比较的字段上，而不是建立在我们要找出来并且显示的字段上

7、限制索引的使用的避归。

7.1  IN、OR子句常会使用工作表，使索引失效。
如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。这句话怎么理解决，请举个例子

例子如下:
如果在fields1和fields2上同时建立了索引，fields1为主索引
以下sql会用到索引
select * from tablename1 where fields1=’value1’ and fields2=’value2’
以下sql不会用到索引
select * from tablename1 where fields1=’value1’ or fields2=’value2’

7.2 使用IS NULL 或IS NOT NULL

         使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开     发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。

7.3 使用函数

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询不会使用索引（只要它不是基于函数的索引）
   select empno,ename,deptno
          from   emp
          where  trunc(hiredate)='01-MAY-81';
          把上面的语句改成下面的语句，这样就可以通过索引进行查找。
          select empno,ename,deptno
          from   emp
          where  hiredate<(to_date('01-MAY-81')+0.9999);

7.4 比较不匹配的数据类型

比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。
         select bank_name,address,city,state,zip
         from   banks
         where  account_number = 990354;
         Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：
         select bank_name,address,city,state,zip
         from   banks
         where  account_number ='990354';
     特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一 次“全表扫描”。

补充:

1.索引带来查询上的速度的大大提升,但索引也占用了额外的硬盘空间(当然现在一般硬盘空间不成问题),而且往表中插入新记录时索引也要随着更新这也需要一定时间. 有些表如果经常insert,而较少select,就不用加索引了.不然每次写入数据都要重新改写索引,花费时间; 这个视实际情况而定,通常情况下索引是必需的.

2.我在对查询效率有怀疑的时候,一般是直接用Mysql的Explain来跟踪查询情况. 你用Mysql-Front是通过时长来比较,我觉得如果从查询时扫描字段的次数来比较更精确一些.