sql语句优化原则.docx
《sql语句优化原则.docx》由会员分享，可在线阅读，更多相关《sql语句优化原则.docx（11页珍藏版）》请在冰豆网上搜索。
sql语句优化原则
SQL语句优化原则
数据库性能是整个应用程序性能的重要部分。
数据库优化涉及的内容非常广泛，各类数据库都提供众多的性能指标和大量的优化工具。
下面我们简单介绍一下优化的基本概念。
一个运行良好的数据库至少应具有以下特点：
合理的物理结构及硬件能力
合理的物理结构指数据库文件及整个网络的物理分布。
硬件能力指是否有足够的硬件资源来完成应用程序功能。
合理的物理结构至少带来两个方面的好处：
1、适量的数据冗余，提高数据安全性。
2、平衡磁盘IO，增强数据读写能力。
而足够的硬件能力的作用自然不言而喻。
一旦建立好数据库系统并开始运行，数据库的物理结构就不能改变。
合理的系统参数
对应数据库来说，随着数据量的变化，数据库性能也一直处在变化之中，因此数据库建立之初设定的系统参数会变的越来越不合适，有时甚至阻碍了数据库的正常运行，导致性能瓶颈。
因此观察性能变化，随时调整系统参数，使数据库一直处于一个良好的运行状态，就成为管理员最重要的日常工作之一。
对系统参数的合理调整，常常能将数据库从崩溃的边缘挽救回来。
oracle、sqlserver这样的高品质数据库都为系统参数提供了灵活多变的调整方式。
一般来说，只要数据库结构设计不存在重大缺陷，通过后期的调整，都可以使数据库运行在一个良好的状态下。
性能优良的sql语句
sql语句是在程序开发阶段就已经决定了的，由低效率的sql语句给数据库性能带来的问题，往往在数据库开始运行一段时间后才凸现出来（随着数据量的不断增加），但发现后就变的难以改变，成为不可突破的性能瓶颈。
因此，作为一名合格的开发人员，应该建立基本的优化概念和良好的编程习惯，从整体上提高应用程序的质量。
同时，提高sql语句的执行效率，是提高整个数据库性能的最立竿见影且价格低廉的方法之一。
因为几乎所有的数据库都会不可避免的运行一些效率低下的sql语句。
对数据库性能的调整，往往都是从sql语句调优开始的。
下面我们简单介绍数据库是怎么执行sql语句的。
sql语句是唯一从应用程序发送到数据库实例的命令。
数据库实例所做的全部工作就是接受、解释和执行sql语句。
在绝大多数情况下，我们并不需要关心sql语句是怎么执行的，这是因为在当前流行的数据库软件中都无一例外的采用了高性能的优化器，而这些优化器在绝大多数的情况下都能将用户某些不合理的sql语句结构转换成更合理的形式，从而有效提高sql的执行效率。
优化器的优化原理有两种：
基于成本的优化
对一条sql语句，优化器会生成所有可能的执行方式，估计这些执行方式将使用的硬件成本，相互比较后从中选择成本最低的执行计划。
缺点是必须收集大量统计数据，对服务器造成额外的负担。
基于规则的优化
相对基于成本的优化，基于规则的优化则显得死板的多。
比如sqlserver遇到blog_id=*的情况就会去找索引，实在找不到索引才使用全表扫描。
而不考虑有时候不用索引可能效率更高。
显然有些情况下基于规则的优化并不合适。
/*执行计划的概念:
简单的说执行计划就是指在执行sql语句前对代码进行编译时数据库实例为sql选择的执行路径。
如一个sql对A、B、C三个表进行联合查询，数据库会首先以某种方式对这3个表种符合条件的记录进行查询（全表扫描或其他），再将A、B、C表中符合条件的记录读入内存，将A、B表的记录相比较后得出的结果集与C表相比较，最后得到符合的结果集。
但实际情况比这复杂的多。
*/
oracle可以选择两种优化模式之中的一种，而其他数据库则是固定的（DB2不清楚，关于DB2的资料太少了），基本都是基于规则的优化。
然而优化器毕竟不是智能的。
很多时候，它不可避免的受到sql语句结构的影响。
而SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表扫描的发生。
sql的书写原则
以下介绍的标准sql的书写原则是本文的核心，这些原则适用于绝大部分数据库。
介绍这些原则的时候，我会举一些简单的例子来说明，但这些例子在实际生产中可能没有什么实际意义。
1、使用索引
如果没有任何索引，在执行sql语句时必定将做全表扫描。
这和我们在看一本书时，如果没有目录，我们只能从第一页开始查找，直到找到查询的内容是一个道理。
全表扫描是效率最低的查询方式，我们会看到绝大多数的低效率sql就是使用的全表扫描。
因此在对一些大表进行查询的时候，我们需要关心一下表中是否建立了索引并尽量使用索引。
总的来说索引可以分为聚集索引和非聚集索引。
一个表中只能有一个聚集索引。
对一个表建立一个聚集索引后，数据库会调整表记录的顺序，使表按照索引的顺序重新排列。
而非聚集索引则不会改变表的结构。
使用的最常见的索引就是主键索引。
实际上在表中指定一列为主键的时候，就在这列上建立了唯一值索引并强制该列的值唯一，这就成了主键索引。
在下面几种情况下，应该建立索引：
1.有大量重复值、且经常有范围查询（between,>,<，>=,<=）和orderby、groupby发生的列，可以考虑使用聚集索引；
2.在使用最频繁的列且常常需要使用函数的时候，可以考虑使用函数索引。
3.在使用最频繁的列且常常需要对两个或多个列进行联合查询的时候，可以考虑使用组合索引。
4.在重复率较低的列上建立唯一索引。
索引虽有助于提高性能，但不是索引越多越好，恰好相反过多的索引会导致系统低效。
因为用户在表中每加进一个索引，维护索引集合就要做相应的更新工作，同时每次有数据改变的时候就需要维护索引。
因此尽管使用索引非常有必要，但是在以下情况下，我们并不赞成使用索引。
1、大量对表数据的修改（插入过删除）
2、数据量很小
关于怎么使用索引，就是多使用存在索引的列作为查询条件。
但是使用了带索引的列，也不一定就使用了索引，后面会提到相应的知识。
2、和任何高级语言一样，sql也是越简单越好。
大家先看看下面的语句：
SELECTTOP$size$BLOG_IDASblogID,
BLOG_DOMINOASblogDomino,
BLOG_TEMPLATE_IDASblogTemplateID
FROMblog
WHEREBLOG_IDNOTIN
（SELECTBLOG_ID
fromblog
whereBLOG_ID>=$size$+$fromID$）
ANDLOCKED=1
ORDERBYBLOG_IDDESC；
通过查看执行计划，我们可以看到执行查询的步骤：
1、子查询中使用blog.pk_blog（主键索引）查询BLOG中满足blog>=$size$+$fromID$的记录,这步占总成本的46％。
2、使用blog.pk_blog（主键索引）,查询BLOG中满足LOCKED=1的记录。
这步占总成本的44％。
3、使用Mergejoin。
这步占总成本的10％。
总成本为0.23。
我们可以看到两次对blog表的查询都使用了blog_id上的主键索引，同时表连接消耗的资源也比较少。
应该说，对于子查询结构，这个查询的效率并不低。
但是，这和下面的语句完全是等价的：
SELECTTOP$size$BLOG_IDASblogID,
BLOG_DOMINOASblogDomino,
BLOG_TEMPLATE_IDASblogTemplateID
FROMblog
WHEREBLOG_ID<$size$+$fromID$
ANDLOCKED=1
ORDERBYBLOG_IDDESC；
总成本为0.0035。
优化器不是人工智能，在第一个查询中指定使用子查询结构，优化器就无法突破这个限制，尽管两个查询是相同的，优化器还是不能将第一种查询结构等价的转换成第二种。
所以查询语句使用的结构对优化器影响重大，我们在进行多表查询等复杂情况的时候的时候（如可能需要使用联接查询、子查询、嵌套查询、groupby等），应多进行一些考虑。
一个原则是结构要尽量简单，这样在编译效率、执行效率和程序可读性方面都有好处。
3、谨慎使用isnull和isnotnull
不能用null作索引，任何包含null值的列都将不会被包含在索引中。
即使在列上建立了索引，只要这些列中有一个含有null，该列就会从索引中排除。
也就是说，如果某列存在空值，即使对该列建索引也不会提高性能。
同时，任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的。
4、in和exists
很多资料都声称exists的效率高于in。
而实际情况证明，在子句中使用in和exists效率是一样的。
SELECTProductName
WHEREUnitPricein
（SELECTUnitPrice
WHEREProductName='SirRodney''sScones'）
SELECTProductName
WHEREexists
（SELECTUnitPrice
WHEREa.ProductName='SirRodney''sScones'
anda.UnitPrice=b.UnitPrice）
查看执行计划可以看到，上面两个语句的执行路径和执行成本都完全一样。
5、in和or
sqlserver会自动将in转换成or，因此对sqlserver来说下面两个语句是一样的，而其他数据库中in的效率要高于or。
selectblog_name
fromblog
whereblog_namelike‘AB%’
orblog_namelike‘CD%’
orblog_namelike‘EF%’;
selectblog_name
fromblog
whereblog_namein（‘AB%’,’CD%’,‘EF%’）;
6、查询字段要需要多少，查询多少
我们每少提取一个字段，查询速度就会有相应的上升。
这主要是因为物理读取成本降低了。
所以要避免select*这样的查询，需要哪些字段就查询哪些字段。
7、将行和操作数减到最少
使用WHERE和HAVING子句只选择需要的行，可以将SELECT语句返回的行数减到最少。
尽量少用不等于运算符<>或!
=。
数据库将必须在表或索引中扫描所有的值，以查看它们是否不等于表达式中给定的值。
可以使用范围重写表达式：
WHEREKeyColumn<'TestValue'ANDKeyColumn>'TestValue'
8、尽量少用格式转换，防止出现隐含的格式转换。
举个简单的例子：
某个表中有一个时间字段timedate型，现在开发人员需要写一个sql：
查询出在2004-04-21到2005-04-21之间，注册了多少blog新用户。
我们比较下面两个语句：
select*
fromblog
whereto_char（create_time,yyyymmdd）between’’and’’;
――将time转换成字符型，和’’及’’做比较。
select*
fromblog
wherecreate_time<=to_date（‘2004-04-21’,’yyyy-mm-dd’）
andcreate_time<=to_date（‘2005-04-21’,’yyyy-mm-dd’）；
――将及转换成date型，和create_time做比较。
（PL/SQL）
第二个语句的性能明显优于第一个。
这是因为第一个sql数据库必须将每一行记录的create_time都转换一次，而第一个只需要对常量做一次转换。
（第二个语句的性能明显优于第一个的另一个重要原因就是第一个查询不能使用create_time上的索引）。
隐含的格式转换既是数据库在必要时会自动将一个数据类型转换成另一种可兼容的数据类型：
最常见的情况，一个字段sort_idvarchar（10）型，现在要查询出所有sort_id为3的用户。
Select*FromblogWheresort_id=3;
如果用户没有查看表结构，很可能认为sort_id是int型而写出上面的语句。
同时，数据库不会报错，这是因为编译引擎自动做了一次数据类型转换。
这就是一个隐藏转换的典型例子。
在查询大量数据的时候，这样不必要的转换会降低sql的效率。
9、带通配符（%）的like语句
要求在blog表中查询名字中包含“我”的人。
可以采用如下的查询SQL语句：
select*fromblogwhereblog_namelike'%我%';
这里由于通配符（%）在搜寻词首出现，所以Oracle将不使用blog_name的索引。
在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。
然而当通配符出现在字符串其他位置时，优化器就能利用索引。
在下面的查询中索引得到了使用：
select*fromblogwhereblog_namelike'c%';
对于sqlserver，这种情况没有影响。
10、ORDERBY和GROPUBY
对于ORDERBY和GROUPBY短语，任何一种索引都有助于SELECT的性能提高。
但是使用聚集索引的效率最高。
如blog表，blog_id为主键索引，blog_name上有一个聚集索引。
Select*
Fromblog
Orderbyblog_name;
由于聚集索引一个表中只能有一个，因此在无法使用聚集索引的情况下，使用其他索引也能提高查询效率。
11、禁止进行列运算
任何对列的操作都将导致表扫描，原因是对列进行运算将直接导致无法使用该列上的索引（除非建立了函数索引）。
它包括数据库函数、计算表达式等等。
因此，查询时要尽可能将操作移至等号右边。
Select*
Fromblog
Whereblog_id=3;
观察执行计划，发现使用了索引blog.PK_blog,整个查询消耗了cputime：
0.00008;I/O成本：
0.0032。
预计成本：
0.0032。
而如果改成：
Select*
Fromblog
Wherecast（blog_idaschar）=’3’;
――将blog_id转换为char型后与’3’相比较。
观察执行计划，发现使用了索引blog.IX_plogs_blog_id（这里使用这个索引的原因是由于无法使用blog.PK_blog，sqlserver退而求其次，使用了建立在plogs_blog_id字段上的索引）,整个查询消耗了cputime：
0.264;I/O成本：
0.345。
预计成本：
0.61。
性能下降了200倍！
同样，应将类似:
whereblog_id*1000>28847;的语句改为：
whereblog_id>28.847;
12、使用表提示
使用表提示的本质是影响优化器，使优化器按照指定的执行计划来执行sql语句（通常是制定多表连接时的连接方式）。
一般情况下并不需要使用表提示，因为优化器总是能选择适当的执行计划。
这种做法只是增加了性能优化的可能性，并不一定会产生好的影响。
由于使用表提示有时反而会降低执行效率，因此，只有当我们发现一些sql在低效运行且认为有必要干预sql执行计划的时候（可能已经严重影响了应用程序的运行）才使用。
同时需要通过反复调试来达到最佳的效果。
使用表提示的方法：
Oracle中使用hint提示，sqlserver中使用option子句。
13、慎用游标
在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。
14、关于多表连接和子查询
在实际应用中我们常常会遇到需要使用多表连接或子查询的情况。
同时我们会发现，很多时候这两种查询结构可以做等量的转换。
下面的例子就是这样：
selectProductName
WHEREUnitPricein
（SELECTUnitPrice
WHEREProductName='SirRodney''sScones'）
SELECTPrd1.ProductName
WHEREPrd2.ProductName='SirRodney''sScones'
（T-sql。
使用的是northwind示例数据库中的表）
子查询和不包括子查询但语义上等效的语句在性能方面通常没有区别。
但是，在一些必须检查存在性的情况中，使用联接会产生更好的性能。
否则，为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。
所以在这些情况下，联接方式会产生更好的效果。
如下面两个等效的sql：
Selectdistincta.blog_name
Frombloga
Whereexists（
Selectb.blog_id
Fromblog_noteb
Wherea.blog_id=b.blog_id
Andb.create_time<’2005-04-25’）;
Selectdistincta.blog_name
Frombloga,blog_noteb
Wherea.blog_id=b.blog_id
Andb.create_time<’2005-04-25’;
分析结果：
查询执行计划可以看到执行成本：
语句一：
使用exists子查询，总成本0.0567语句二：
联接查询，总成本0.0535。
15、增加执行计划的重复使用率
执行计划生成后便驻留在高速缓存中。
很多时候一段sql代码并不是执行一次就完了，同样的代码会连续执行很多次。
如一个用户查询了自己的用户资料，0.1秒后另一个用户执行了相同的操作。
如果数据库能识别出这两次sql语句是同样的，在第二次执行时就不会再次编译sql，而是直接使用驻留在高速缓存中的执行计划。
在某些大型系统中一些基础的sql可能会每秒执行上千次。
因此重复使用执行计划可以大大缩短sql的执行时间。
但是有的时候数据库不能识别出类似的sql语句，比如用户查询自己资料的例子：
第一个用户：
select*fromblogwhereblog_id=1;
第二个用户查询的是：
select*fromblogwhereblog_id=455;
sql文本的变化导致数据库认为这是两个不同的查询，无法重复使用执行计划。
为了提高执行计划的重复使用率，建议：
1、使用对象（如表和视图）的完全合法名称。
如：
Select*fromblogctynnd.dbo.blog;
2、Oracle强烈建议在应用程序中大量使用“绑定变量”，如下：
将：
select*fromblogwhereblog_id=1；
改为：
SQL>declare
vidintdefault1;
resvarchar（4）;
begin
executeimmediate'select*fromblogwhereblog_id=:
x'intoresusingvid;
dbms_output.put_line（res）;--oracle系统包，用于输出。
end;
/
（PL/SQL。
这一招的用意很明显就是固定sql文本，把编译器骗过去，有点瞒天过海的意思。
其他数据库使用绑定变量的方法也是一样的，只是使用的语句稍有不同。
）
对于sqlserver来说使用绑定变量意义不大，因为sqlserver数据库引擎可以识别出上面给出的例子，绑定变量具有一定的优势，但并不明显。
另外需要说明的是在优化器内部对执行计划会存在一个衰减列表，到一定时间后执行计划还没有被重复使用，就会被清除出这个列表，这时再执行sql语句就必须重新编译了。
其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。
另外，不同的数据库使用的优化原则不同，导致对同样的sql语句，做出完全不同的执行计划。
即使是同样的数据库、同样的语句，在数据库运行的不同时期执行效率也会发生很大的变化。
因此，对数据库的优化更多的时候是对数据库的观察和调试，而不是教条式的简单修改。
有兴趣的同事可以使用下面的方式查看sql语句的执行计划，而执行计划反映了此刻sql语句的执行过程，使用的资源等等，是sql语句效率高低的量化数据。
Oracle:
sql>@rdbms\admin\utlxplan.sql――运行utlxplan.sql脚本，会自动创建一个plan_table。
这步只需要做一次。
建立后每次进入sqlplus时：
sql>setautotraceon
这时每次执行sql，都会显示出相应的执行计划。
Sqlserver:
在sqlserver中查看执行计划非常简单：
进入查询分析器，在窗体中输入T-sql语句。
点击【查询预计的执行计划】（该按钮在切换数据库下拉菜单的左边。
或ctrl＋l）。
这时执行计划就会显示在窗体下端。
其他数据库查看执行计划的方式如有需要可以上网查找。
欢迎多交流。
后记
对于现有的每一种数据库来说，标准SQL的功能显然太弱了。
因此，每一种数据库都对标准SQL进行了性能扩展。
对于oracle是PL/SQL。
Sqlserver是T－sql。
在本文中列举的例子，并不一定在你的数据库中也能成功执行。
同时，本文中的某些原则，可能对于特定数据库、特定优化模式下的sql性能并不能起到明显改善的作用。
比如在使用通配符的例子中：
select*fromemployeewherelast_namelike'%cliton%';
对于oracle，将无法使用last_name上的索引，导致这个查询会发生全表扫描。
而对于sqlserver来说，仍然可以正常使用索引。
尽管如此，在开发过程中对一些小细节的注意，不仅可以保证在大多数数据库中有较高的执行效率，还可以使sql语句在今后数据库运行的过程中或某些特殊情况下（如系统迁移），也能长期保持稳定的状态。
本文中的sql示例凡是没有特殊注明的，都是针对T－sql。
对于某些概念性的东西，为了写的简单易懂，我用自己的理解进行了修改，可能有很多不准确的地方。
本文分为几个部分，大家可以使用文档结构图来查看自己感兴趣的部分。
如果觉得有什么不清楚或是不认同的地方，欢迎和我交流，共同进步。
陆雁
2005-04-21
升级会员 