sql语句优化(转载)

細細.魚 · 发表于 2016-11-11 10:24:33

sql语句优化(转载)
ASP.NET2009-11-23 10:19 阅读1评论0
字号：大大<a style="font-size: 12px;" href="http://www.blogbus.com/user/#" name="middlefont">中</a>中<a style="display: none; font-size: 12px;" href="http://www.blogbus.com/user/#" name="smallfont">小</a>小

　　SQL语句优化原则(一):http://www.cnblogs.com/tomsun/archive/2005/12/14/296959.html
　　1.合理使用索引
索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下：
●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
●如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。
●使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。
　　(1)在下面两条select语句中:
 select * fromtable1 wherefield1<=10000 and field1>=0; select * fromtable1 wherefield1>=0 and field1<=10000; 如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。 第一个原则：在where子句中应把最具限制性的条件放在最前面。
　　(2)在下面的select语句中:
 select * fromtab where a=… and b=… andc=…; 若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。 第二个原则：where子句中字段的顺序应和索引中字段顺序一致。
　　以下假设在field1上有唯一索引I1，在field2上有非唯一索引I2。
(3) select field3,field4 from tb wherefield1='sdf'快 select *from tb wherefield1='sdf'慢， 因为后者在索引扫描后要多一步ROWID表访问。
　　(4) select field3,field4 from tb wherefield1>='sdf'快 select field3,field4 from tb wherefield1>'sdf'慢 因为前者可以迅速定位索引。
　　(5) select field3,field4 from tb where field2 like'R%' 快 selectfield3,field4 from tb where field2 like'%R' 慢， 因为后者不使用索引。
　　(6) 使用函数如：
select field3,field4 from tb where upper(field2)='RMN'不使用索引。
如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。
　　(7) 空值不在索引中存储，所以
 selectfield3,field4 from tb where field2 is[not] null不使用索引。
　　(8) 不等式如
 selectfield3,field4 from tb where field2!='TOM'不使用索引。 相似地， selectfield3,field4 from tb where field2 not in('M','P')不使用索引。
　　(9) 多列索引，只有当查询中索引首列被用于条件时，索引才能被使用。
　　(10) MAX，MIN等函数，如 Select max(field2) from tb使用索引。所以，如果需要对字段取max，min，sum等，应该加索引。 一次只使用一个聚集函数，如： select “min”=min(field1),“max”=max(field1)fromtb 不如：select “min”=(selectmin(field1) from tb) ,“max”=(select max(field1) fromtb)
　　(11)重复值过多的索引不会被查询优化器使用。而且因为建了索引，修改该字段值时还要修改索引，所以更新该字段的操作比没有索引更慢。
　　(12)索引值过大（如在一个char(40)的字段上建索引），会造成大量的I/O开销（甚至会超过表扫描的I/O开销）。因此，尽量使用整数索引。Sp_estspace可以计算表和索引的开销。
　　(13) 对于多列索引，order by的顺序必须和索引的字段顺序一致。
　　(14) 在sybase中，如果order by的字段组成一个簇索引，那么无须做orderby。记录的排列顺序是与簇索引一致的。
　　(15) 多表联结（具体查询方案需要通过测试得到）
where子句中限定条件尽量使用相关联的字段，且尽量把相关联的字段放在前面。 selecta.field1,b.field2 from a,b where a.field3=b.field3 1. field3上没有索引的情况下: 对a作全表扫描，结果排序 对b作全表扫描，结果排序 结果合并。 对于很小的表或巨大的表比较合适。
　　2. field3上有索引 按照表联结的次序，b为驱动表，a为被驱动表 对b作全表扫描 对a作索引范围扫描 如果匹配，通过a的rowid访问
　　(16) 避免一对多的join。如：
select tb1.field3,tb1.field4,tb2.field2 from tb1,tb2 wheretb1.field2=tb2.field2 andtb1.field2=‘BU1032’ andtb2.field2= ‘aaa’ 不如： declare @a varchar(80) select @a=field2 from tb2 wherefield2=‘aaa’ select tb1.field3,tb1.field4,@a from tb1 where field2=‘aaa’
　　(16) 子查询
 用exists/notexists代替in/not in操作 比较： selecta.field1 from a where a.field2 in(select b.field1 from b whereb.field2=100) selecta.field1 from a where exists( select 1 from b wherea.field2=b.field1 and b.field2=100)
　　 selectfield1 from a where field1 not in( select field2 from b) selectfield1 from a where not exists( select 1 from b whereb.field2=a.field1)
　　(17) 主、外键主要用于数据约束，sybase中创建主键时会自动创建索引，外键与索引无关，提高性能必须再建索引。
　　(18) char类型的字段不建索引比int类型的字段不建索引更糟糕。建索引后性能只稍差一点。
　　(19)使用count(*)而不要使用count(column_name)，避免使用count(distinctcolumn_name)。
　　(20) 等号右边尽量不要使用字段名，如：
select * from tb where field1 = field3
 (21) 避免使用or条件，因为or不使用索引。
　　2.避免使用order by和group by字句。
因为使用这两个子句会占用大量的临时空间(tempspace),如果一定要使用，可用视图、人工生成临时表的方法来代替。 如果必须使用，先检查memory、tempdb的大小。 测试证明，特别要避免一个查询里既使用join又使用groupby，速度会非常慢！
　　3.尽量少用子查询，特别是相关子查询。因为这样会导致效率下降。
一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
　　
 4．消除对大型表行数据的顺序存取 在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。 还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作： SELECT ＊ FROM orders WHERE (customer_num=104 ANDorder_num>1001) OR order_num=1008 虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句： SELECT ＊ FROM orders WHERE customer_num=104 ANDorder_num>1001 UNION SELECT ＊ FROM orders WHERE order_num=1008 这样就能利用索引路径处理查询。 5．避免困难的正规表达式 MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT ＊ FROMcustomer WHERE zipcode LIKE “98_ __” 即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT ＊ FROM customerWHERE zipcode>“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。 另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3]>“80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。 6．使用临时表加速查询 把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如： SELECT cust.name，rcvbles.balance，……other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 ANDcust.postcode>“98000” ORDER BY cust.name 如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序： SELECT cust.name，rcvbles.balance，……other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 ORDER BY cust.name INTO TEMP cust_with_balance 然后以下面的方式在临时表中查询： SELECT ＊ FROM cust_with_balance WHEREpostcode>“98000” 临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。 注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。 7．用排序来取代非顺序存取 非顺序磁盘存取是最慢的操作，表现在磁盘存”鄣睦椿匾贫QL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。 有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。
　　
　　
　　
　　
　　SQL语句优化原则(二):http://www.cnblogs.com/ruilei/archive/2006/04/30/389618.html
1． IN 操作符
用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。 但是用IN的SQL性能总是比较低的，从执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别： 将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。 推荐方案：在业务密集的SQL当中尽量不采用IN操作符。可以用exists代替。 SQL Server例子： Exists用法： select * from kj_dept where exists (select * from kj_dept_infowhere kj_dept.dept_id = dept_id and dept_id=XXX) in用法： select * from kj_dept where dept_id in (select dept_id fromkj_dept_info where dept_id=XXX) 2．NOT IN操作符 此操作是强列推荐不使用的，因为它不能应用表的索引。 推荐方案：用NOTEXISTS 或（外连接+判断为空）方案代替。
　　3. <> 操作符（不等于）
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 推荐方案：用其它相同功能的操作运算代替，如 a<>0 改为 a>0 ora<0 a<>’’改为 a>’’
　　4．IS NULL 或IS NOT NULL操作（判断字段是否为空）
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。 推荐方案：用其它相同功能的操作运算代替，如 a is notnull 改为 a>0或a>’’等。 不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。
　　5． > 及 < 操作符（大于或小于操作符）
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。 6．LIKE操作符 LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE‘%5400%’ 这种查询不会引用索引，而LIKE‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号YY_BH LIKE ‘%5400%’这个条件会产生全表扫描，如果改成YY_BH LIKE’X5400%’ OR YY_BH LIKE’B5400%’则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。
　　7. UNION操作符
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
　　
推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
　　8. 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（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描；第一条SQL的dy_dj='1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。 表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.[越有效地缩小范围的条件越放后]
　　9. 查询表顺序的影响
在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引情况下，会按表出现的顺序进行链接，由此因为表的顺序不对,会产生十分耗服务器资源的数据交叉。
　　10. 联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（BillCliton）的职工。　　
下面是一个采用联接查询的SQL语句：
select * from employss
where　　 first_name=''　and　last_name ='BeillCliton'; 上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，对于last_name创建的索引没有使用。当采用下面这种SQL语句的编写，系统就可以采用基于last_name创建的索引。 Select * from employee　　 where　　 first_name ='Beill' and last_name='Cliton'; 遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着BillCliton这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本： select * from employee 　　 where 　　 first_name =SUBSTR('&&name',1,INSTR('&&name','')-1) 　　 and 　　 last_name =SUBSTR('&&name',INSTR('&&name’,'')+1)
　　11. Order by语句　　
ORDER BY语句决定了如何将返回的查询结果排序。Orderby语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度。　　
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写orderby语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
可以使用where子句代替order by。
　　12. 避免使用 ‘ * ‘
SELECT 子句中避免使用 ‘ * ‘当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*'是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,在解析的过程中, 会将'*' 依次转换成所有的列名,这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
　　13. 用表连接替换EXISTS
通常来说采用表连接的方式比EXISTS更有效率。
　　14. 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换。
　　15. 避免不合理的索引设计
合理索引设计，可以尽量使每个SQL都可以利用索引。in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
避免在索引列上使用NOT。
16. 通过内部函数提高SQL效率
复杂的SQL往往牺牲了执行效率，能够掌握运用内部函数解决问题的方法，在实际工作中是非常有意义的。如果有可能，可以使用存储过程来加以实现！
但是避免在索引列上使用函数。

账号		自动登录	找回密码
密码			立即注册

c++ size_t 和 int 的区别

HERE 使用 AWS EF 和 JFrog Artifactory 打

C++ 指针大全：从基础到进阶，一篇快速上手

wirelessnetview好用的无线分析工具

Red Hat RHCE 8 (EX294) Cert Guide

亿图图示专家(EDraw Max) V7.9 中文破解版

zabbix3.4.1安装部署+微信推送信息+大屏显

[经验分享] sql语句优化(转载)

浏览过的版块

扫码加入运维网微信交流群