sql语句优化(转载)

sql语句优化(转载)

ASP.NET <wbr><wbr><span>2009-11-23 10:19</span> <span><wbr><wbr>阅读1<wbr><wbr><wbr>评论0</wbr></wbr></wbr></wbr></wbr></span> <span><wbr></wbr></span></wbr></wbr>

字号： 大大<wbr><a style="font-size: 12px;" href="http://www.blogbus.com/user/#" name="middlefont"><span style="color: #e79a0f;">中</span></a><span style="display: none;"><strong><span style="color: #7f7070;">中</span></strong></span><wbr><a style="display: none; font-size: 12px;" href="http://www.blogbus.com/user/#" name="smallfont"><span style="color: #e79a0f;">小</span></a><span style="display: inline;"><strong><span style="color: #7f7070;">小</span></strong></span></wbr></wbr>

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语句中:
<wbr><wbr> select * from table1<wbr> where<wbr> field1&lt;=10000 and field1&gt;=0;<br><wbr><wbr> select * from table1<wbr> where<wbr> field1&gt;=0 and field1&lt;=10000;<br><wbr><wbr> 如果数据表中的数据field1都&gt;=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。<br><wbr><wbr> 第一个原则：在where子句中应把最具限制性的条件放在最前面。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

(2)在下面的select语句中:
<wbr><wbr> select * from tab<wbr> where<wbr> a=… and b=… and c=…;<br><wbr> 若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。<br><wbr><wbr> 第二个原则：where子句中字段的顺序应和索引中字段顺序一致。</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

以下假设在field1上有唯一索引I1，在field2上有非唯一索引I2。
(3) select field3,field4 from tb where field1='sdf'<wbr><wbr><wbr><wbr><wbr><wbr><wbr> 快<br><wbr><wbr><wbr> select * from tb where field1='sdf'<wbr><wbr><wbr><wbr><wbr> 慢，<br> 因为后者在索引扫描后要多一步ROWID表访问。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

(4) select field3,field4 from tb where field1>='sdf'<wbr><wbr><wbr><wbr><wbr><wbr><wbr> 快<br> select field3,field4 from tb where field1&gt;'sdf'<wbr><wbr><wbr><wbr><wbr><wbr><wbr> 慢<br> 因为前者可以迅速定位索引。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

(5) select field3,field4 from tb where field2 like 'R%'<wbr><wbr><wbr> 快<br><wbr><wbr><wbr> select field3,field4 from tb where field2 like '%R'<wbr><wbr><wbr> 慢，<br><wbr><wbr><wbr> 因为后者不使用索引。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

(6) 使用函数如：
select field3,field4 from tb where upper(field2)='RMN'不使用索引。
如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。

(7) 空值不在索引中存储，所以
<wbr><wbr><wbr> select field3,field4 from tb where field2 is[not] null不使用索引。</wbr></wbr></wbr>

(8) 不等式如
<wbr><wbr><wbr> select field3,field4 from tb where field2!='TOM'不使用索引。<br><wbr><wbr><wbr> 相似地，<br><wbr><wbr><wbr> select field3,field4 from tb where field2 not in('M','P')不使用索引。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

(9) 多列索引，只有当查询中索引首列被用于条件时，索引才能被使用。

(10)<wbr> MAX，MIN等函数，如<br> Select max(field2) from tb使用索引。所以，如果需要对字段取max，min，sum等，应该加索引。<br> 一次只使用一个聚集函数，如：<br> select “min”=min(field1), “max”=max(field1)<wbr> from tb<wbr><wbr><wbr><wbr><wbr><br> 不如：select “min”=(select min(field1) from tb) , “max”=(select max(field1) from tb)<wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

(11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引，修改该字段值时还要修改索引，所以更新该字段的操作比没有索引更慢。

(12) 索引值过大（如在一个char(40)的字段上建索引），会造成大量的I/O开销（甚至会超过表扫描的I/O开销）。因此，尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。

(13) 对于多列索引，order by的顺序必须和索引的字段顺序一致。

(14) 在sybase中，如果order by的字段组成一个簇索引，那么无须做order by。记录的排列顺序是与簇索引一致的。

(15) 多表联结（具体查询方案需要通过测试得到）
<wbr><wbr><wbr> where子句中限定条件尽量使用相关联的字段，且尽量把相关联的字段放在前面。<br><wbr><wbr><wbr> select a.field1,b.field2 from a,b where a.field3=b.field3<br><wbr>1. field3上没有索引的情况下:<br><wbr><wbr><wbr> 对a作全表扫描，结果排序<br><wbr><wbr><wbr> 对b作全表扫描，结果排序<br><wbr><wbr><wbr> 结果合并。<br><wbr><wbr><wbr> 对于很小的表或巨大的表比较合适。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr>2. field3上有索引<br><wbr><wbr><wbr> 按照表联结的次序，b为驱动表，a为被驱动表<br><wbr><wbr><wbr> 对b作全表扫描<br><wbr><wbr><wbr> 对a作索引范围扫描<br><wbr><wbr><wbr> 如果匹配，通过a的rowid访问</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

(16) 避免一对多的join。如：
<wbr><wbr><wbr><wbr> select tb1.field3,tb1.field4,tb2.field2 from tb1,tb2 where tb1.field2=tb2.field2 and tb1.field2=‘BU1032’ and tb2.field2= ‘aaa’<br><wbr><wbr><wbr><wbr> 不如：<br><wbr><wbr><wbr><wbr> declare @a varchar(80)<br> select @a=field2 from tb2 where field2=‘aaa’<wbr><br><wbr><wbr><wbr><wbr> select tb1.field3,tb1.field4,@a from tb1 where field2= ‘aaa’</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

(16) 子查询
<wbr><wbr><wbr> 用exists/not exists代替in/not in操作<br><wbr><wbr><wbr> 比较：<br><wbr><wbr><wbr> select a.field1 from a where a.field2 in(select b.field1 from b where b.field2=100)<br><wbr><wbr><wbr> select a.field1 from a where exists( select 1 from b where a.field2=b.field1 and b.field2=100)</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr><wbr><wbr> select field1 from a where field1 not in( select field2 from b)<br><wbr><wbr><wbr> select field1 from a where not exists( select 1 from b where b.field2=a.field1)</wbr></wbr></wbr></wbr></wbr></wbr>

(17) 主、外键主要用于数据约束，sybase中创建主键时会自动创建索引，外键与索引无关，提高性能必须再建索引。

(18) char类型的字段不建索引比int类型的字段不建索引更糟糕。建索引后性能只稍差一点。

(19)<wbr><wbr> 使用count(*)而不要使用count(column_name)，避免使用count(distinct column_name)。</wbr></wbr>

(20) 等号右边尽量不要使用字段名，如：
select * from tb where field1 = field3
<wbr><br> (21) 避免使用or条件，因为or不使用索引。</wbr>

2.避免使用order by和group by字句。
<wbr><wbr><wbr> 因为使用这两个子句会占用大量的临时空间(tempspace),如果一定要使用，可用视图、人工生成临时表的方法来代替。<br><wbr><wbr> 如果必须使用，先检查memory、tempdb的大小。<br><wbr><wbr> 测试证明，特别要避免一个查询里既使用join又使用group by，速度会非常慢！</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

3.尽量少用子查询，特别是相关子查询。因为这样会导致效率下降。
一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

<wbr><br> 4．消除对大型表行数据的顺序存取<br> 在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。<br> 还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作：<br> SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num&gt;1001) OR order_num=1008<br> 虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：<br> SELECT ＊ FROM orders WHERE customer_num=104 AND order_num&gt;1001<br> UNION<br> SELECT ＊ FROM orders WHERE order_num=1008<br> 这样就能利用索引路径处理查询。<br> 5．避免困难的正规表达式<br> MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _”<br> 即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT ＊ FROM customer WHERE zipcode &gt;“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。<br> 另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3] &gt;“80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。<br> 6．使用临时表加速查询<br> 把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：<br> SELECT cust.name，rcvbles.balance，……other columns<br> FROM cust，rcvbles<br> WHERE cust.customer_id = rcvlbes.customer_id<br> AND rcvblls.balance&gt;0<br> AND cust.postcode&gt;“98000”<br> ORDER BY cust.name<br> 如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序：<br> SELECT cust.name，rcvbles.balance，……other columns<br> FROM cust，rcvbles<br> WHERE cust.customer_id = rcvlbes.customer_id<br> AND rcvblls.balance&gt;0<br> ORDER BY cust.name<br> INTO TEMP cust_with_balance<br> 然后以下面的方式在临时表中查询：<br> SELECT ＊ FROM cust_with_balance<br> WHERE postcode&gt;“98000”<br> 临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。<br> 注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。<br> 7．用排序来取代非顺序存取<br> 非顺序磁盘存取是最慢的操作，表现在磁盘存”鄣睦椿匾贫QL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。<br> 有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。<wbr></wbr></wbr>

<wbr></wbr>

<wbr></wbr>

<wbr></wbr>

<wbr></wbr>

SQL 语句优化原则(二):http://www.cnblogs.com/ruilei/archive/2006/04/30/389618.html
1． IN 操作符
<wbr><wbr><wbr> 用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。<br><wbr><wbr><wbr> 但是用IN的SQL性能总是比较低的，从执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：<br><wbr><wbr><wbr> 将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。<br> 推荐方案：在业务密集的SQL当中尽量不采用IN操作符。可以用exists代替。<br> SQL Server例子：<br> Exists用法：<br> select * from kj_dept where exists (select * from kj_dept_info where kj_dept.dept_id = dept_id and dept_id=XXX)<br> in用法：<br> select * from kj_dept where dept_id in (select dept_id from kj_dept_info where dept_id=XXX)<br><wbr><br> 2．NOT IN操作符<br><wbr><wbr><wbr> 此操作是强列推荐不使用的，因为它不能应用表的索引。<br><wbr><wbr><wbr> 推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

3. <> 操作符（不等于）
<wbr><wbr><wbr> 不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。<br><wbr><wbr><wbr> 推荐方案：用其它相同功能的操作运算代替，如<br><wbr><wbr><wbr> a&lt;&gt;0 改为 a&gt;0 or a&lt;0<br> a&lt;&gt;’’ 改为 a&gt;’’</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

4．IS NULL 或IS NOT NULL操作（判断字段是否为空）
<wbr><wbr><wbr> 判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。<br><wbr><wbr><wbr> 推荐方案：用其它相同功能的操作运算代替，如<br><wbr><wbr><wbr> a is not null 改为 a&gt;0 或a&gt;’’等。<br><wbr><wbr><wbr> 不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

5． > 及 < 操作符（大于或小于操作符）
<wbr><wbr><wbr> 大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段 A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A&gt;2与A&gt;=3的效果就有很大的区别了，因为A&gt;2时ORACLE会先找出为2的记录索引再进行比较，而A&gt;=3时ORACLE则直接找到=3的记录索引。<br><wbr><br> 6．LIKE操作符<br><wbr><wbr><wbr> LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

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<wbr> and dy_dj = '1KV以下'<br> 以上两个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占用率明显比第一条低。<br> 表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. [越有效地缩小范围的条件越放后]</wbr>

9. 查询表顺序的影响
在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引情况下，会按表出现的顺序进行链接，由此因为表的顺序不对,会产生十分耗服务器资源的数据交叉。

10. 联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。 　　
下面是一个采用联接查询的SQL语句：
select * from employss
<wbr>where　　 first_name=''　and　last_name ='Beill Cliton';<br> 上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，对于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写，系统就可以采用基于last_name创建的索引。<br> Select * from employee　　 where　　 first_name ='Beill' and last_name ='Cliton';<br> 遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着Bill Cliton这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本：<br> select * from employee<br> 　　 where<br> 　　 first_name = SUBSTR('&amp;&amp;name',1,INSTR('&amp;&amp;name',' ')-1)<br> 　　 and<br> 　　 last_name = SUBSTR('&amp;&amp;name',INSTR('&amp;&amp;name’,' ')+1)</wbr>

11. Order by语句 　　
ORDER BY语句决定了如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 　　
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在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往往牺牲了执行效率，能够掌握运用内部函数解决问题的方法，在实际工作中是非常有意义的。如果有可能，可以使用存储过程来加以实现！
但是避免在索引列上使用函数。