`

sql优化总结

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)。

 

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

分享到:
评论

相关推荐

    SQL优化总结,很实用

    以下是对SQL优化的总结,主要聚焦于Oracle数据库的SQL语句调优。 1. **理解执行计划**:优化SQL的第一步是理解查询的执行计划。Oracle的`EXPLAIN PLAN`可以展示SQL执行的步骤,包括表扫描方式、连接顺序、索引使用...

    【整理】数据库面试题索引sql优化+数据库SQL优化总结之百万级数据库优化

    接下来,"数据库SQL优化总结之百万级数据库优化.pdf"可能深入到实际的优化实践。 1. **数据库架构设计**:在百万级数据量下,合理的设计能避免性能瓶颈,如垂直分割、水平分割,以及读写分离策略。 2. **缓存与...

    SQL优化总结

    ### SQL优化总结 #### 一、SQL优化的重要性 在数据库应用中,SQL查询性能的优劣直接影响着应用程序的响应时间和用户体验。高效的SQL查询不仅能够提升系统整体性能,还能减少服务器资源消耗,降低运营成本。因此,...

    SQL优化总结.xmind

    SQL优化总结.xmind

    SQL语句优化总结

    SQL语句优化是一项重要的数据库管理技能,能够显著提高数据库查询性能,减少资源消耗,缩短响应时间。以下是对上述文件内容中提及的关键知识点的详细总结: 1. 选择最有效的表名顺序 在使用基于规则的优化器时,...

    Oracle数据库SQL优化总结

    Oracle数据库SQL优化是一个关键的技能,对于提升数据库性能和应用响应速度至关重要。以下是一些针对非DBA的Oracle SQL优化技巧: 1. **选择最有效的表名顺序**:在FROM子句中,应将记录最少的表放在最前面,基础表...

    数据库sql优化总结

    SQL语句操作,消耗了70%~90%的数据库资源; 独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低;

    SQL优化经验总结34条.pdf

    ### SQL优化经验总结34条 #### 一、选择最有效率的表名顺序 - **要点**: 在基于规则的优化器(RBO)中,Oracle解析器从右至左处理FROM子句中的表名。为了提高性能,应将记录条数最少的表放在最后,即作为基础表。...

    SQL优化总结.docx

    SQL优化是数据库性能提升的关键环节,它涉及到查询速度、资源消耗和系统稳定性等多个方面。以下是一些关于SQL优化的关键知识点: 1. **索引优化**: - 索引的合理使用至关重要,应确保主要的查询条件字段都有索引...

    sql 优化个人总结

    sql优化总结,对sql 优化在实战中对于sql优化的浅显理解,大神勿喷。

    sql优化经验总结

    通过阅读"sql优化经验总结V1.5.ppt"和"sql优化经验总结.ppt",可以更深入地学习到更多实用技巧和案例分析,进一步提升在Oracle SQL优化方面的能力。不断学习和实践,才能在这个领域取得更大的成就。

    数据库SQL优化大总结之 百万级数据库优化方案.pdf

    数据库SQL优化大总结之百万级数据库优化方案 本文总结了数据库SQL优化的十一个重要知识点,以帮助开发者提高SQL查询效率,避免全表扫描。 一、数据库SQL优化之索引优化 1. 在where及order by涉及的列上建立索引,...

    SQL优化经验总结34条

    ### SQL优化经验总结34条深度解读 #### (1) 最优表名顺序:提升查询效率 在基于规则的优化器中,Oracle按照从右至左的顺序处理FROM子句中的表名。为了提高查询效率,应将记录条数最少的表置于FROM子句的最后,作为...

    收获不止SQL优化

    16.4.2 优化总结 445 16.5 本章习题、总结与延伸 446 第17章 总结与延伸:从勿信讹传到洞若观火 447 17.1 SQL优化的各个误区 447 17.1.1 COUNT(*)与COUNT(列)的传言 447 17.1.2 谈SQL编写顺序之流言蜚语 451 ...

    通过分析SQL语句的执行计划优化SQL(总结)

    这篇博客"通过分析SQL语句的执行计划优化SQL(总结)"深入探讨了这一主题,下面将对其中的主要知识点进行详细阐述。 1. **执行计划的理解**: 执行计划是数据库管理系统在执行SQL语句之前预先生成的一种详细步骤,它...

    收获,不止SQL优化--抓住SQL的本质1

    - **全书总结**:本书不仅是一本关于SQL优化的技术书籍,更是引导读者进入SQL优化世界的指南。通过丰富的案例、实战经验和深入的技术探讨,帮助读者建立起从宏观到微观的优化思路,并最终达到“爽”的境界。 - **...

Global site tag (gtag.js) - Google Analytics