sql优化问题

个人对于sql优化这方面的一些想法,仅供参数,也是结合自己实际开发经验讲解,如有错误希望大佬指正.优化顾名思义就是性能发挥到极致,sql优化我从两方面入手,一方面是设计阶段,另一方面就是开发阶段,开发阶段主要是结合自己的实际情况.

设计阶段：

三大范式:

第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。

第2规范:每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。

第3规范:一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。

完全按照三大范式规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

主键的设计

主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。

在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。

主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

 外键的设计

外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：

外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。

谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER 2000当年的新功能，在2005作了保留，应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。

字段数据类型设计原则:

A、数据类型尽量用数字型，数字型的比较比字符型的快很多,尤其是作为主键。

B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的,节省一个字节是一个字节,虽然硬盘便宜也不能浪费啊。

C、尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。

   为什么最好不要为null呢,因为在程序处理过程中,你经常要为null值进行处理,比如使用isnull进行判断,这样削弱查询的速度,还有程序中需要不断的为null值进行判断,多写了代码,减少了程序的性能.

D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。在sqlserver 2005尽可能使用nvarchar(max),或者varchar(max);除非必要图片尽量上传到服务器,数据库保留上传地址.

E、自增字段要慎用

1.不利于数据迁移.

2.不利于分布式部署.

3.无法预知Id,为子表数据插入造成困难.

4.没有实际意义,无法让人看出这个数字到底有什么用.

F、尽可能使用定长数据类型,而不是变长数据类型

为什么不要设计过多的变长类型的数据呢?

1.对于 SQl Server为说，变长类型的数据，在更新的时候，如果长度比以前的大，会进行页拆分。会对查询性能造成严重的影响。会增加查询时，O/I的花费 (Cost)页分隔越多，查询时，O/I的开销就越大。对于变长的字段来说，有可能，这个字符的内容，存储在不同的位置。这个字段的内容，存储在不同的位置。存储在不同的页中,它们之间有指针来关联。这种情况会造成查询时，磁头来回寻址，定位。可能你查一条记录，磁头找这条记录的这个变长字段的内容，都要去好几个页里找，才能完整的找到。这样，就造成了很大个 O/I开销，降低了查询性能。从物理上来说，文件本来就经常容易产生碎片。再加上变长类型的页拆分。

页是sql server存储数据的基本单位，大小为8kb，可以存储表数据、索引数据、执行计划数据、分配位图、可用空间信息。页是sql server可以读写的最小I/O单位。即便是读取一行数据，它也要把整个页加载到缓存并从缓存中读取数据。

页拆分是这样产生的:

比如：有一个变长类型的字段 Content: nvarchar(512).你添加一条记录，给 Content 的值是 N'ABC',那么，存储的时候，直接就存储 N'ABC'了。当你下次 Update这条记录的 Content字段时，给的值是 N'ABCDEF'那么就会发生页拆分。DEF对被存储在其它页。因为有可能上一次分配的数据页已经存储了其他行的数据对吧,对，512 ，只是用来限制这个字段的长度。并不与页拆分有关系。记录的物理顺序，与你 INSERT的顺序是一致。你 INSERT了 N条，然后再去修改第一条,这时候可能不在同一个页了.

以上结论就是把变长字段的内容加大，就会造成页拆分了。也就是说可变长类型是把一页填满，再填另一,影响比较大的是,每次insert的时候会增加分配数据页得次数

当然有可能造成一行数据保存在2个数据页里.但是，同样，不但页拆分对增加查询时的 O/I 开销，字符不必要的太长，也会增加 O/I开销。

2.字段大小对表总大小有影响

SQL Server 2005单行字段总长是8060字节

3.可变长类型是有长度限制的

 以上内容是第一篇,请大家多多发表意见!

 

 

 开发阶段：

 Sql语句本身：百万级的数据的sql优化

 

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num isnull

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num =0

3.应尽量避免在 where 子句中使用 !=或 <>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10or Name ='admin'

可以这样查询：

select id from t where num = 10unionallselect id from t where Name ='admin'

5.in 和 not in也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between就不要用 in了：

select id from t where num between1and3

很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a whereexists(select1from b where num=a.num)

 

6.下面的查询也将导致全表扫描：

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num = @num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num = @num

8.应尽量避免在 where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100

应改为:

select id from t where num = 100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t wheresubstring(name,1,3) = ’abc’       -–name以abc开头的idselect id from t wheredatediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    --生成的id

应改为:

select id from t where name like'abc%'select id from t where createdate >='2005-11-30'and createdate <'2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

13.Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

14.对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

15.select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

16.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

17.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

18.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

19.尽可能的使用 varchar/nvarchar代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

20.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

21.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

22. 避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件， 最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务操作，提高系统并发能力。

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。