当前位置:首页 > 开发 > 编程语言 > 编程 > 正文

sql优化总结

发表于: 2011-03-03   作者:macroli   来源:转载   浏览:
sql
摘要: 为了是自己对sql优化有更好的原则性,在这里做一下总结,个人原则如有不对请多多指教。谢谢!   要知道一个简单的sql语句执行效率,就要有查看方式,一遍更好的进行优化。   一、简单的统计语句执行时间 declare @d datetime ---定义一个datetime的变量set @d=getdate() ---获取查询语句开始前的时间select user_id

为了是自己对sql优化有更好的原则性,在这里做一下总结,个人原则如有不对请多多指教。谢谢!

 

要知道一个简单的sql语句执行效率,就要有查看方式,一遍更好的进行优化。

 

一、简单的统计语句执行时间

declare @d datetime ---定义一个datetime的变量
set @d=getdate() ---获取查询语句开始前的时间
select user_id,baby_alias,provice,city,hits from baby ---执行查询语句
select 语句的执行时间=datediff(ms,@d,getdate()) ---使用datediff()函数,计算精确到ms的执行时间

二、通过系统函数来查看语句具体执行过程,以了解什么位置使语句效率下降了,从而可以知道优化的着重点。

SET STATISTICS io ON
SET STATISTICS time ON
go
---你要测试的sql语句
select * from baby
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF

 

接下来就要知道sql的优化原则:

一、建立必要的索引,合理恰当的使用索引可以带来很的收获,每个索引的建立是有资源消耗的,所以要合理的创建索引;建立索引一般规则如下:

1 表主键、外键必须建立索引。

2 数据量大的表必须建立索引,一般数据大于500就需要建立索引。

3 索引应建立在选择性高的字段上。

4 在where语句经常出现的条件字段上建立索引。

5 在经常有其他表连接的表字段上建立索引。

6 频繁操作的表不要建立太多的索引。

7 索引应建立在小字段上,不要建立在大的文本字段上。

8 尽量使用但索引代替组合索引。

9 建立组合索引必须仔细考虑其必要性,从而加以设置。

10 删除不必要的索引。

创建聚集索引和非聚集索引

--排序(聚集索引)
createclusteredindexinx_tablenameontablename(tablename)

--创建非聚集索引

createnonclusteredindexinx_tablename ontablename(tablename)

--主键
altertabletablenameaddprimarykeynonclustered--主键且非聚集
(
entry_stock_bi,aid
)

二、对索引主列限制使用<> != is (not) null 等条件限制,如果一定要使用就要使用like限制条件。

e.g. select * from tablename where t_name like 'aa%'

 

三、避免对条件列处理

任何对列的操作都可能导致全表扫描,这里所谓的操作包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等式的右边,甚至去掉函数。

e.g.

select * from tablename where substrb(CardNo,1,4)='5378'

 

select * from tablename where amount/31< 1000

 

select * from tablename where to_char(ActionTime,'yyyymmdd')='20110303'

 

由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的,因此它不得不进行表扫描,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表扫描,因此将SQL重写如下:

 

select * from tablename where CardNo like '5378%'

 

select * from tablename where amount < 1000*31

 

select * from tablename where ActionTime= to_date ('20110303' ,'yyyymmdd')

 

当表数据量达到10万条以上时效果是明显的。

 

 

 

四、避免条件列没有必要的类型转换。

e.g. 表example1 中的列col1是字符型(char),则以下语句存在类型转换:

 

select col1,col2 from example1 where col1>10,

 

应该写为: select col1,col2 from example1 where col1>'10'。

 

 

五、尽量不适用in 和 or 条件限制 以下是2500条的数据

e.g. select * from tablename where tid in ('1','2')(315ms)

可以将其优化成

select * from tablename where tid = '1' (6ms)

select * from tablename where tid = '2' (3ms)

然后做一个加法,执行速度会快很多

 

六、尽量不要使用<>避免全表扫描,如果数据是枚举值,且取值范围固定,则修改为"or"方式,最好是想五 将其拆开处理.

 

七、数据量超过1000条时添加查询的范围,查询效率会有很大提高.

e.g. select * from baby where baby_id<2000

 

应该优化成

select * from baby where baby_id<2000 and baby_id>0

 

八、like子句尽量使用前端匹配,因为在查询中会频繁使用,所以对所选字段建立索引会很好的提高效率。

e.g. select * from userdiary where diary_name like '%北%'

这里应该优化成

select * from userdiary where diary_name like '北%'

这样处理后会有很好的收获。

 

九、用case语句合并表的多次扫描。

e.g.

select count(*) from hitcount where id<100

select count(*) from hitcount where id between 100 and 500

select count(*) from hitcount where id >500

 

将其优化成下面这样

select count ( case when id<100 then 1 else null end) count1,
count (case when id between 100 and 500 then 1 else null end) count2,
count (case when id >500 then 1 else null end) count3 from hitcount;

 

十、使用基于函数的索引

 

e.g.

 

select * from baby where substr(ename,1,2)=’SM’;

 

但是这种查询在客服系统又经常使用,我们可以创建一个带有substr函数的基于函数的索引,

 

create index baby_ename_substr on baby( substr(ename,1,2) );

 

这样在执行上面的查询语句时,这个基于函数的索引将排上用场,执行计划将是(INDEX RANGE SCAN)。

 

版权声明:本文为博主原创文章,未经博主允许不得转载。

sql优化总结

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

推荐文章
编辑推荐
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号