再谈SQL优化

kider

出处：鹇雨斋

前言:

SQL优化不仅是门技术,而是一门艺术!

有关SQL优化的太多了,再贴一篇SQL优化方面的文章,以后便不再发相关的文章,太多了,反而会被这些条条框框固定死,那就不再是艺术了.
另外,本文内容某些部分可能与前面发的观点有所冲突,各有所见嘛,了解即可,具体如何,还要靠自己去实践证明.
                                                                                                                     20081024  三断笛
第一部分 SQL SERVER数据库优化方案
    微软公司的SQL SERVER 是一个功能完备的数据库管理系统，它提供了完整的关系数据库创建、开发和管理功能。现社会信息技术的快速发展，对数据库技术的要求也越来越高，SQL SERVER数据库在信息化的过程中得到了广泛的应用。
第一章    数据库系统概述
    从20世纪60年代开始到现在，数据库技术经过了30多年的发展。在这30多年的历程中，在数据库技术的理论研究和系统开发上取得了辉煌的成就，确立了数据技术在现代计算机系统中不可或缺的地位。成为现代信息科学与技术的重要组成部分以及计算机数据处理和信息管理系统的核心。
1．1 基本概念
    与数据库技术密切相关的基本概念包括：数据、数据库、数据库管理系统和数据库系统四大概念。
1.    数据（Data）
    数据是对客观事物的一种描述，是由能被计算机识别与处理的数值、字符等符号构成的集合，即数据是指描述事物的符号记录。
    广义地说，数据是一种物理符号的序列，用于记录事物的情况，是对客观事物及其属性进行的一种抽象化及符号化的描述。数据的概念应包括数据的内容和形式两个方面。数据的内容是指所描述的客观事物的具体特性，也就是通常所说的数据的“值”；数据的形式则是指数据内容所存储的具体形式，即数据的“类型”。故此，数据可以用数据类型和值来表示。
2.    数据库（Data Base，DB）
    数据库是指长期存储在计算机内部、有组织的、可共享的数据集合，即在计算机系统中按一定的数据模型组织、存储和使用的相关联的数据集合成为数据库。
    数据库中的数据按照一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性、易扩展性、集中性和共享性，以文件的形式存储在存储介质上的。数据库中的数据由数据库管理系统进行统一管理和控制，用户对数据库进行的各种数据操作都是通过数据库管理系统实现。
3.    数据库管理系统（Data Base Management System，DBMS）
    数据库管理系统是数据库系统的核心，是为数据库的建立、使用和维护而配置的软件，是位于操作系统与用户之间的一层数据管理软件。主要功能是对数据库进行定义、操作、控制和管理。
1)    数据定义
    数据的定义包括：定义构成数据库结构的外模式、模式和内模式，定义各个外模式和模式之间的映射，定义模式与内模式之间的映射，定义有关的约束条件。
2)    数据处理
对数据的处理操作主要包括对数据库数据的检索、插入、修改和删除等基本操作。
3)    安全管理
    对数据库的安全管理主要体现在：对数据库进行并发控制、安全性检查、完整性约束条件的检查和执行、数据库的内部维护（如索引、数据字典的自动维护）等。并且能够管理和监督用户的权限，防止拥护有任何破坏或者恶意的企图。
4)    数据的组织、存储和管理
    负责分类地组织、存储和管理数据库数据，确定以何种文件结构和存取方式物理地组织数据，如何实现数据之间的联系，以便提高存储空间利用以及提高随机查找、顺序查找、增加、删除和查改等操作的时间效率。
5)    建立和维护数据库
    建立数据库包括数据库数据的初始化与数据转换等。维护数据库包括数据库的转储与恢复、数据库的重组织与重构造、性能的监视与分析等。
6)    数据通信接口
提供与其他软件系统进行通信的功能。
4.    数据库系统（Data Base System，DBS）
    数据库系统指在计算机系统中引入数据库后的系统构成，一般有数据库、数据库管理系统、应用系统、数据库管理员和用户构成。
1．2 数据库系统的特点
数据库系统的点主要有：数据的结构化、高共享性、低冗余度、易扩充、较高的独立性（物理数据独立、逻辑数据独立）以及数据由DBMS统一管理和控制（数据的安全性Security保护、数据的完整性Integrity保护、并发Concurrency控制、数据库恢复Recovery）等。第二章 数据库性能优化 数据库作为一种独立的、有组织、的可共享的数据集合，数据的查询访问是数据操作中频度最高的操作。当数据量和访问频率达到一定程度的时候，系统的响应速度就至关重要了，这时候就需要对数据库数据存储的结构和方式进行优化，使其满足系统需要的访问响应速度。 2．1 性能影响因素
    常见的影响数据访问速度的因素，有以下几种：
1.    没有索引或者没有用到索引
    数据库索引就像书籍中目录一样，使用户在访问数据库数据时，不必遍历所有数据就可以找到需要的数据。创建索引后，可以保证每行数据的唯一性，极大地提高数据检索效率，这是一中牺牲空间换取性能的方法。没有索引或者没有用到索引是数据访问速度慢最常见的因素，也是程序设计的一个缺陷所在。
2.    I/O吞吐量小，形成了瓶颈效应
    I/O吞吐量是影响数据访问速度的客观因素（硬件因素）。在一定的硬件环境下，利用优化的部署方案可适当提高I/O吞吐量。
3.    没有创建计算列导致查询不优化
    计算列是一个比较特殊的列，不填写任何设计类型，用户不可以改变该列的值。计算列的值是通过一定的函数公式等以另一个或多个列的值为输入值计算出的结果。如果没相应的计算列，在一些数据查询的时候需要对已有数据进行计算，从而浪费一部分性能。
4.    内存不足
    对数据库数据的查询访问毫无疑问会占用大量的内存空间，当内存不足的情况下，数据的访问速度会受到明显的影响甚至访问出现超时情况，是影响数据访问速度的客观因素。
5.    网络速度慢
    网络速度慢是影响数据访问速度的客观因素。可通过提高网络访问的位宽来解决。
6.    查询出的数据量过大
    当查询出的数据量过大时，内存的占用、系统时间的占用等都影响数据访问的速度。可以采用多次查询、定位查询、和查询数据量控制来解决。
7.    锁或者死锁
    锁或者死锁在数据库数据访问时会造成访问者等待时间过程或者永久无法获取到资源。这是查询慢最常见的因素之一，是程序设计的缺陷，要尽量避免。
8.    返回不必要的行和列
    在一般的数据查询中，都尽可能多的获取数据信息，这样造成了不必要的数据遍历，大大的增加了数据访问的响应的时间。所以在一般的查询中，尽量查询少的行和列，将数据遍历时间降到最低以满足数据输出需求。
9.    查询语句不够优化
    在数据查询访问过程中，使用最频繁的是使用自定义的查询语句进行数据输出的。所以编写优化的查询语句能够很大程度上提高数据查询访问的速度。
2．2 性能优化
    数据库性能优化主要是提高数据访问的速度，即提高数据库响应速度的性能指标。性能优化主要分为主观因素和客观因素两部分的优化。这里主要针对影响性能的客观因素进行优化。
2．2．1 主观因素优化
    主观因素主要是指服务器的硬件环境。主要优化有以下几个方面：
1、    把数据、日志、索引放到不同的I/O设备上，增加读取速度，数据量越大，提高I/O吞吐量越重要；
2、    纵向、横向分割表，减少表的尺寸（sp_spaceuse）；
3、    升级硬件；
4、    提高网络访问速度；
5、    扩大服务器的内存；配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置，一般设置为物理内存的1.5倍；如果安装了全文检索功能，并打算运行Microsoft搜索服务以便执行全文索引和查询，可考虑将虚拟内存大小设置为至少计算机中物理内存的3倍；
6、    增加服务器CPU个数；其中并行处理比串行处理更需要资源。SQL SERVER根据系统负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询适合并行处理。不过更新操作UPDATE、INSERT、DELETE不能进行并行处理。

2．2．2 客观因素优化
    客观因素主要指的是由于设计和开发中存在的缺陷和漏洞；主要优化有以下几个方面：
1.    优化索引
（1）    根据查询条件建立优化的索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建里索引（参照索引的创建），不要对有限的几个值的字段建立单一索引（如性别字段）。
（2）    如果使用LIKE进行查询的话，简单的使用INDEX是不行的，全文索引又太耗费空间。LIKE ‘N%’使用索引，LIKE ‘%N’不使用索引。用LIKE‘%N%’查询时，查询耗时和字段值总长度成正比，所以不能用CHAR类型而采用VARCHAR。对于字段的值很长的字段建立全文索引。
（3）    重建索引DBCC REINDEX，DBCC INDEXDEFRAG，收缩数据和日志DBCC SHRINKDB，DBCC SHRINKFILE。设置自动收缩日志，对与大的数据库不要设置数据库自动增长，它会降低服务器的性能。
2.    数据库部署优化
（1）    DB SERVER和APPLICATION SERVER分离，OLTP和OLAP分离；
（2）    使用分区视图。分布式分区视图可用于实现数据库服务器联合体，联合体是一组分开管理的服务器，他们互相协作分担系统的处理负荷。A、在实现分区视图之前，必须先水平分区表。B、在创建成员表后，在每个服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器都有一个原始表的复本一样，不过每个服务器上其实只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。
3.    查询语句优化
    T-SQL的写法上有很大的讲究，DBMS处理查询计划的过程是：a、查询语句的词法、语法检查；b、将语句提交给DBMS的查询优化器；c、优化器做代数优化和存取路径的优化；d、由预编译模块生成查询规划；e、在合适的时间提交给系统处理执行；f、将执行结果返回给用户。
（1）    COMMIT和ROLLBACK的区别：ROLLBACK回滚所有的事务；COMMIT提交当前的事务。在动态语句中写事务，请将事务写在外面，如：BEGIN
TRAN
EXEC（@SQL） COMMIT TRANS或者将动态SQL写成函数或者存储过程。
（2）    在大数据两的查询输出SELECT语句中尽量不要使用自定义函数，调用自定义函数的函数时系统调用是一个迭代过程，很影响查询输出性能的。在查询字段时尽可能使用小字段两输出，并在WHERE子句或者使用SELECT TOP
10/1 PERCENT来限制返回的记录数，使用SET ROWCOUNT来限制操作的记录数，避免整表扫描。返回不必要的数据，不但浪费了服务器的I/O资源，加重了网络的负担，如果表很大的话，在表扫描期间将表锁住，禁止其他的联接访问，后过很严重的。
（3）    SQL的注释申明对执行查询输出没有任何影响。
（4）    使用计算列对数据进行简单计算，尽量避免在查询语句中对数据进行运算。
（5）    尽可能不使用光标，它会占用大量的资源。如果需要ROW-BY-ROW地执行，尽量采用非光标技术，如：客户端循环、临时表、TABLE变量、子查询、CASE语句等等。
（6）    使用PROFILER来跟踪查询，得到查询所需的时间，找出SQL的问题所在，用索引优化器优化索引。
（7）    注意UNION和UNION ALL的区别。在没有必要的时候不要用DISINCT，它同UNION一样会降低查询速度，重复的记录在查询里是没有问题的。
（8）    用sp_configure ‘query governor cost limit’或者
        SET QUERY_COVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的        资源超出限制时，服务器自动取消查询，在查询之前就扼杀掉。SET LOCKTIME        设置锁的时间。
（9）    不要在WHERE子句中列名加函数，如CONVERT，SUBSTRING等，如果必须用函数的时候，创建计算列在创建索引来替代。NOT IN会多次扫描表，使用EXISTS、NOT
EXISTS、IN、LEFT
OUTER JOIN来替代，其中EXISTS比IN更快，最慢的NOT操作。
（10）    使用QUERY ANALYZER，查看SQL语句的查询计划和评估分析是否是优化的SQL。一般20%的代码占用了80%的资源，优化的重点就是这些慢的地方。
（11）    如果使用了IN或者OR等时发现查询没有走索引，使用显式申明指定索引，如：Select
*
From FA01(INDEX=IX_SEX) Where AA0107 IN(‘01’,‘02’)。
（12）    在需要对已有数据进行比较复杂计算才能获得查询的结果数据时,将需要查询的结果预先计算好放在表中,查询的时候在SELECT。
（13）    数据库有一个原则是代码离数据越近越好，所有有限选择DEFAULT，依次为RULES，CONSTRAINT，PROCEDURE来编写程序的质量高，速度快。如果要插入大的二进制到IMAGE列，使用存储过程，千万不要用内嵌INSERT直接插入。因为这样应用程序首先将二进制转换成字符串，服务器收到字符后又将他转换成二进制。存储过程直接传入二进制参数即可，处理速度明显改善，如：CREATE
PROCEDURE image_insert @image
varbinary
as
Insert
into
table(fImage) values(@image)。
（14）    Between在某些时候比IN速度更快，更快地根据索引找到范围。由于IN会比较多次，所以有时会慢些。
（15）    尽量不要建没有作用的事务例如产生报表时，浪费资源，只有在必须使用事务时才建立合适的事务。
（16）    用OR的字句可以分解成多个查询，并通过UNION连接多个查询。速度取决与是否使用索引。如果查询需要用联合索引，用UNION ALL执行的效率更高些。
（17）    尽量少用视图，视图的效率低。对视图操作比直接对表操作慢，可以用SRORED PROCEDURE来代替。特别是不要用视图嵌套，嵌套视图增加了寻找原始资料的难度。视图是存放在服务器上的被优化好了的已经产生查询规划的SQL。对单表数据检索时，不要使用指向多表的视图，否则增加了不必要的系统开销，查询也会受到干扰。没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行，增加了额外的开销，这同UNION和UNION ALL原理相同。
（18）    当使用SELECT INTO和CREATE TABLE时，会锁住系统表（SYSOBJECTS，SYSINDEXES等），从而阻塞其他的连接的存取。所以千万不要在事务内部使用。如果经常要用到临时表时请使用实表或者临时表变量。尽量少用临时表，用结果集和TABLE类型的变量来代替。
（19）    在使用GROUP BY HAVING子句时，在使用前剔除多余的行，尽量避免使用HAVING子句剔除行工作。剔除行最优的执行顺序是：SELECT的WHERE子句选择所有合适的行，GROUP BY用来分组统计行，HAVING字句用来剔除多余的分组。如果只是分组不进行计算则DISTINCT比GROUP BY速度快。
2．2．3 SP编程内容
    开发人员如果用到其他库的TABLE或者VIEW，请在当前库中建立VIEW来实现跨库操作，最好不要直接使用“database.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库TABLE和VIEW，不方便校验。在提交SP前，请先使用SET SHOWPLAN ON 分析过查询计划，做自身的查询优化检查。
    优化应用程序得到高速的运行效率，在SP编写过程中应该注意以下几点：
1.    SQL使用规范
A、    尽量避免大事务操作，慎用HOLDLOCK子句，提高系统并发能力。
B、    尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
C、    尽量避免使用游标，因为游标的性能较差，如果游标操作的数据超过一万行，那么就应该改写，如果使用了游标，就尽量避免在游标循环中再进行表连接操作。
D、    注意WHERE子句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序一直，范围从大到小。尽量不要在WHERE子句中的“=”左边进行函数、算术或其他表达式运算，否则系统可能无法正确使用索引。尽量使用“>=”,不使用“>”。
E、    尽量使用EXISTS代替SELECT COUNT（1）来判断是否存在记录，COUNT函数只有在统计表中所有行数时使用，而且COUNT（1）比COUNT（*）效率更高。
F、    注意一些OR子句和UNION子句之间的替换；避免表中不同数据类型之间的连接；注意存储过程中参数和数据类型的关系；注意INSERT、UPDATE操作的数据量，防止与其他应用冲突。数据量超过200个数据页面（400k）时系统将会进行锁升级，页级锁会升级成表级锁。
2.    索引的使用规范
A、    索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引；尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过INDEX INDEX_NAMEl来强制指定索引。避免对大表查询时进行 TABLE SCAN，必要时考虑新建索引。
B、    在使用索引字段作为条件时，如果该索引是联合索引，则必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。
C、    要注意索引的维护，周期性重建索引，重新编译存储过程。
3.    TEMPDB的使用规范
A、    尽量避免使用DISTINCT、ORDER
BY、GROUP
BY、HAVING、JOIN、COMPUTE，这些语句会加重TEMPDB的负担；避免频繁创建和删除临时表，减少系统表资源的耗费。
B、    在新建临时表时，如果一次性插入数据量很大，那么可以使用SELECT INTO代替CREATE TABLE避免LOG，提高速度；数据量不大时为了缓和系统表的资源，建议先CREATE TABLE然后INSERT。在使用了临时表后务必将所有的临时表显式删除，先TRUNCATE TABLE然后DROP TABLE，这样可以避免系统表的较长时间锁定。
C、    慎用大的临时表与其他大表的连接查询和修改，降低系统表负担，因为这种操作会在一条语句中多次使用TEMPDB的系统表。

1、操作符

NOT IN操作符
           此操作是强列推荐不使用的，因为它不能应用表的索引。
           推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替
           "IS
NULL", "<>", "!=", "!>", "!<", "NOT", "NOT
EXISTS", "NOT
IN", "NOT
LIKE", "LIKE
'%500'"，因为他们不走索引全是表扫描。
           NOT IN会多次扫描表，使用EXISTS、NOT
EXISTS、IN、LEFT
OUTER   JOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.
           如果列的值含有空，以前它的索引不起作用，现在2000的优化器能够处理了。相同的是IS   NULL，“NOT",   "NOT
EXISTS",   "NOT
IN"能优化她，
           而” <> ”等还是不能优化，用不到索引。  
        
        2、注意UNion和UNion   all的区别。UNION比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
        
        3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询。。
        
        4、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
           我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，
           直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。  

        5、创建合理的索引，对于插入或者修改比较频繁的表，尽量慎用索引。因为如果表中存在索引，插入和修改时也会引起全表扫描。
           索引一般使用于where后经常用作条件的字段上。

        6、在表中定义字段或者存储过程、函数中定义参数时，将参数的大小设置为合适即可，勿设置太大。这样开销很大。

        7、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。
           select
*
from   chineseresume   where   title   in   ('男','女')  
           Select
*
from   chineseresume   where
between
'男'
and
'女'是一样的。由于in会在比较多次，所以有时会慢些。  

        8、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。  

        9、 WHERE后面的条件顺序影响
            WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
            Select
*
from zl_yhjbqk where dy_dj =
'1KV以下'
and xh_bz=1
Select
*
from zl_yhjbqk where xh_bz=1
and dy_dj =
'1KV以下'
            以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，
            如果dy_dj =
'1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，
            在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，
            而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。
            所以尽量将范围小的条件放在前面。。

        10、用OR的字句可以分解成多个查询，并且通过UNION   连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION   all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。  

        11、没有必要时不要用DISTINCT和ORDER   BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNION   ALL一样的道理。   
            
        12、使用in时，在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数  

        13、当用SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示声明语句，
            在另一个连接中SELECT   *
from   sysobjects可以看到  SELECT
INTO   会锁住系统表，
            Create
table   也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。  

        14、一般在GROUP BY和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：
            select   的Where字句选择所有合适的行，Group   By用来分组个统计行，Having字句用来剔除多余的分组。
            这样Group   By和Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快  

        15、一次更新多条记录比分多次更新每次一条快,就是说批处理好  

        16、慎用临时表，临时表存储于tempdb库中，操作临时表时，会引起跨库操作。尽量用结果集和表变量来代替它。  

        17、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过，
            并且被组织到一个执行规划里、且存储在数据库中的 SQL语句，是控制流语言的集合，速度当然快。

        18、不要在一段SQL或者存储过程中多次使用相同的函数或相同的查询语句，这样比较浪费资源,建议将结果放在变量里再调用。这样更快。  

        19、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现