sql优化

本节针对sql优化的常见优化点做一总结：

一、常见sql语句优化

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

2. 避免在where子句中使用is null 或 is not null对字段进行判断。

   select id from table where name is null

   在这个查询中，就算我们为 name 字段设置了索引，查询分析器也不会使用，因此查询效率底下。为了避免这样的查询，在数据库设计的时候，尽量将可能会出现 null 值的字段设置默认值，这里如果我们将 name 字段的默认值设置为0，那么我们就可以这样查询：

   select id from table where name = 0

3. 避免在 where 子句中使用 != 或 <> 操作符。

   select name from table where id <> 0

   数据库在查询时，对 != 或 <> 操作符不会使用索引，而对于 < 、 <= 、 = 、 > 、 >= 、 BETWEEN AND，数据库才会使用索引。因此对于上面的查询，正确写法应该是：

   select name from table where id < 0
   union all --这里没有用or连接where后面的两个条件
   select name from table where id > 0

4. 避免在 where 子句中使用 or来链接条件。

   select id from tabel where name = 'UncleToo' or name = 'PHP'

   这种情况应该这样写：

   select id from tabel where name = 'UncleToo'
   union all
   select id from tabel where name = 'PHP'

5. 少用 in 或 not in。虽然对于 in 的条件会使用索引，不会全表扫描，但是在某些特定的情况，使用其他方法也许效果更好。如：

   select name from tabel where id in(1,2,3,4,5)

   像这种连续的数值，我们可以使用 BETWEEN AND，如：

   select name from tabel where id between 1 and 5

6. 注意 like 中通配符的使用。下面的语句会导致全表扫描，尽量少用。如：

   select id from tabel where name like'%UncleToo%'
   select id from tabel where name like'%UncleToo'

   而下面的语句执行效率要快的多，因为它使用了索引：

   select id from tabel where name like'UncleToo%'

7. 避免在 where 子句中对字段进行表达式操作。

   select name from table where id/2 = 100

   正确的写法应该是：

   select name from table where id = 100*2

8. 避免在 where 子句中对字段进行函数操作。

   select id from table where substring(name,1,8) = 'UncleToo'
   select id from table where datediff(day,datefield,'2014-07-17') >= 0

   这两条语句中都对字段进行了函数处理，这样就是的查询分析器放弃了索引的使用。正确的写法是这样的：

   select id from table where name like'UncleToo%'
   select id from table where datefield <= '2014-07-17'

   也就是说，不要在 where 子句中的 = 左边进行函数、算术运算或其他表达式运算。

9. 在子查询中，用 exists 代替 in 是一个好的选择。

   select name from a where id in(select id from b)

   但是下面这条语句查询的速度要快的多：

   select name from a where exists(select 1 from b where id = a.id)

   结论：in适合B表比A表数据小的情况，exists()适合B表比A表数据大的情况。具体参考：in和exist区别

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

10. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么在sex上建了索引也对查询效率起不了作用。

    12. 一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。

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

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

13. 避免频繁创建和删除临时表，以减少系统表资源的消耗。需要使用临时表的时机：当需要重复引用大型表或常用表中的某个数据集时。在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。临时表最好显示的删除，先 truncate table ，然后 drop table ，这样可以避免系统资源占用。

14. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。游标使用时机：对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。

15. 尽量避免向客户端返回大数据量。

16. inner关联的表可以先查出来，再去关联leftjoin的表

17. 表关联时取别名，也能提高效率

18. 查询多用索引列取查，用charindex或者like[0-9]来代替%%

19. 使用数据仓库的形式，建立单独的表存储数据，根据时间戳定期更新数据。将多表关联的数据集中抽取存入一张表中，查询时单表查询，提高了查询效率。

二、sql查询优化

查询速度慢的常见原因

1. 没有索引或者没有用到索引（这是查询慢最常见的问题，是程序设计的缺陷）
2. I/O吞吐量小，形成了瓶颈效应。
3. 没有创建计算列导致查询不优化。
4. 内存不足 。
5. 网络速度慢 。
6. 查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）。
7. 锁或者死锁（这也是查询慢最常见的问题，是程序设计的缺陷）。
8. sp_lock，sp_who，活动的用户查看，原因是读写竞争资源。
9. 返回了不必要的行和列 。
10. 查询语句不好，没有优化。

优化方法

1. 把数据、日志、索引放到不同的I/O设备上，增加读取速度，数据量（尺寸）越大，提高I/O越重要。
2. 纵向、横向分割表，减少表的尺寸（sp_spaceuse）。
3. 升级硬件，提高网速。
4. 根据查询条件，建立索引，优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建），不要对有限的几个值的字段建单一索引如性别字段。
5. 扩大服务器的内存，Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍（虚拟内存大小设置的一半）。
6. 增加服务器 CPU个数。但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作Update,Insert， Delete还不能并行处理。
7. 如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like ‘a%’ 使用索引 like ‘%a’ 不使用索引用 like ‘%a%’ 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。
8. DB Server 和APPLication Server 分离；OLTP和OLAP分离
9. 重建索引 DBCC REINDEX ，DBCC INDEXDEFRAG，收缩数据和日志 DBCC SHRINKDB，DBCC SHRINKFILE.。设置自动收缩日志。对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的：
   • 查询语句的词法、语法检查
   • 将语句提交给DBMS的查询优化器
   • 优化器做代数优化和存取路径的优化
   • 由预编译模块生成查询规划
   • 然后在合适的时间提交给系统处理执行
   • 最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。
10. Commit和rollback的区别 ，Rollback：回滚所有的事物。 Commit：提交当前的事物.。没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。
11. 在查询Select语句中用Where字句限制返回的行数，避免表扫描，如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表，后果严重。
12. 尽可能不使用光标，它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。
13. 注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。重复的记录在查询里是没有问题的 。
14. 用select top 100 / 10 Percent 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行
15. 如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌Insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 方法：Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。
16. 尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。以前由于SQL SERVER对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程

三、 sql使用规范

sql使用规范

1. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。
2. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
3. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。
4. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。
5. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
6. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
7. 尽量使用“>=”，不要使用“>”。
8. 注意一些or子句和union子句之间的替换。
9. 注意表之间连接的数据类型，避免不同类型数据之间的连接。
10. 注意存储过程中参数和数据类型的关系。
11. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。

索引使用规范

1. 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。
2. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引。
3. 避免对大表查询时进行table scan，必要时考虑新建索引。
4. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。
5. 要注意索引的维护，周期性重建索引，重新编译存储过程。　　

临时表的使用规范

1. 尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。
2. 避免频繁创建和删除临时表，减少系统表资源的消耗。
3. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。
4. 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
5. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
6. 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。