设计高效的SQL查询语句

内部培训教程

设计高效的SQL查询语句

王佩 2010年7月16日

内部培训教程

概要设计高效的SQL语句 设计SQL语句的经验

内部培训教程

SQL语句是怎么被执行的SQL语句(SELECT) 数据库 语法分析和编译

关系代数表达式

查询优化器优化

统计信息

生成执行计划

存储索引

数据

输出查询结果

内部培训教程

SQL语句是如何被优化的 执行计划是由一系列数据库内部基本操作构成 的执行方案 两种优化方法基于规则的优化(RBO) 又称基于语法的优化，选择执行计划的标准是查询 的确切语法以及查询中各子查询的顺序 基于代价的优化(CBO) 又称基于成本的优化，选择执行计划的标准是代价 计算(IO操作数，CPU秒数等，需要表和索引的统计信 息)

内部培训教程

性能分析指令SET STATISTICS IO 用来查看SQL语句的读 写操作代价 SET STATISTICS TIME用于查看SQL语句的 执行时间 SET NOCOUNT ON 可以关闭消息反馈机制， 减少网络流量 SET STATISTICS PROFILE ON 返回有关语句 的执行方式和语句预计所需资源的详细信息， 即执行计划的内容 DEMO

内部培训教程

SEEK VS. SCAN判断是否需要优化该SQL查询语句的规则是： 判 断 在 执 行 计 划 中 的 操 作 是 SEEK 操 作 还 是 SCAN操作。 SEEK:索引搜索，性能要好 SCAN:索引扫描，扫描整个对象 Demo

内部培训教程

SQL优化和数据库性能SQL语句是对数据库操作的唯一途径。程 序的执行最终归结为SQL语句的执行，所 以SQL语句的执行效率对数据库的性能起 决定性的作用。 SQL语句消耗了70%-90%的数据库资源。

内部培训教程

设计SQL语句的经验(一)尽量不使用SELECT * FROM TABLE1 而使用 SELECT FIELD1,FIELD2 FROM TABLE1 在SQL语句中尽量使用索引列加快查询速度 任何ORDER BY语句的非索引项或计算表达式 将降低查询速度 任何WHERE语句中使用IS NULL 或 IS NOT NULL的语句不允许使用索引 有些返回列少的SQL语句我们可以将其包含在 索引内,即创建包含索引,这样速度会快很多.

内部培训教程

设计SQL语句的经验(二)海量数据的SQL查询语句中尽量少使用格式转 换 统配符%出现在词首时,系统将不是用索引. IN/OR子句经常会使用工作表,使索引失效,如 果不产生大量重复值,可以考虑把子句拆开,拆 , , 开子句要使用索引 尽量不要在where子句中的“=”左边进行函数、 算术运算或其他表达式运算，否则系统将可 能无法正确使用索引。 DEMO

内部培训教程

存储过程编写优化a) SQL的使用规范 b) 索引的使用规范 c) tempdb的使用规范 tempdb

内部培训教程

SQL的使用规范(1)慎用holdlock子句，避免大事务操作，提高系统 并发能力。 避免反复访问同一张或几张表，尤其是数据量较 大的表，可以考虑先根据条件提取数据到临时表 中，然后再做连接。 避免使用游标，因为游标的效率较差，如果游标 操作的数据超过1万行，那么就应该改写；如果 使

用了游标，就要尽量避免在游标循环中再进行 表连接的操作。可以使用循环来解决 根据索引顺序、范围大小来确定where条件子句 的前后顺序，尽可能的让字段顺序与索引顺序相 一致，范围从大到小。 如果频繁使用的动态拼接SQL,尽量使用 SP_EXECUTESQL

内部培训教程

SQL的使用规范(2)不要在where子句中的“=”左边进行函数、算术 运算或其他表达式运算，否则系统将可能无法正 确使用索引。 Count函数只有在统计表中所有行数时使用，而且 count(1)比count(*)更有效率。 表之间连接的数据类型，避免不同类型数据之间 的连接,并且连接的字段都包含在索引内部,并且确 认是索引的首字母。

在编写存储过程传值的时候,一定要注意参 数的数据类型尽量和对应字段表中的数据 类型一致.

内部培训教程

索引的使用规范索引的创建要与应用结合考虑，建议大的OLTP 表不要超过6个索引。 尽可能的使用索引字段作为查询条件，尤其是 聚簇索引，必要时可以通过index index_name 来强制指定索引。 避免对大表查询时进行table scan,必要时考虑 新建索引。 在使用索引字段作为查询条件时，如果该索引 是联合索引，那么必须使用到该联合索引中的 第一个字段作为条件时才能保证系统使用该索 引，否则该索引将不会被使用。

内部培训教程

Tempdb的使用规范尽量避免使用distinct、order by、group by、having、join、 compute,因为这些语句会加重tempdb的负担。 避免频繁创建和删除临时表，减少系统表资源的消耗。 在新建临时表时，如果一次性插入数据量很大，那么可以 使用select into代替create table,避免log,提高速度。如 select into create table log 果数据量不大，为了缓和系统表的资源，建议先create table,然后insert。 如果临时表的数据量较大，需要建立索引，那么应该将创 建临时表和建立索引的过程放在单独一个子存储过程中， 这样才能保证系统能够很好的使用到该临时表的索引。 慎用大的临时表与其他大表的连接查询和修改，减低系统 表负担，因为这种操作会在一条语句中多次使用tempdb的 系统表。

内部培训教程

谢谢！