sql优化总结

为了是自己对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）。

 

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