`
goodguyzl
  • 浏览: 47176 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

SQL查询优化技巧30则

阅读更多
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

  2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

  select id from t where num is null

  可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

  select id from t where num=0

  3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

  4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

  select id from t where num=10 or num=20

  可以这样查询:

  select id from t where num=10

  union all

  select id from t where num=20

  5.in 和 not in 也要慎用,否则会导致全表扫描,如:

  select id from t where num in(1,2,3)

  对于连续的数值,能用 between 就不要用 in 了:

  select id from t where num between 1 and 3

  6.下面的查询也将导致全表扫描:

  select id from t where name like '%abc%'

  若要提高效率,可以考虑全文检索。

  7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

  select id from t where num=@num

  可以改为强制查询使用索引:

  select id from t with(index(索引名)) where num=@num

  8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

  select id from t where num/2=100

  应改为:

  select id from t where num=100*2

  9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

  select id from t where substring(name,1,3)='abc'--name以abc开头的id

  select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id

  应改为:

  select id from t where name like 'abc%'

  select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

  10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

  11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

  12.不要写一些没有意义的查询,如需要生成一个空表结构:

  select col1,col2 into #t from t where 1=0

  这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

  create table #t(...)

  13.很多时候用 exists 代替 in 是一个好的选择:

  select num from a where num in(select num from b)

  用下面的语句替换:

  select num from a where exists(select 1 from b where num=a.num)

  14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

  15.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

  16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

  17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

  18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

  19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

  20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

  21.避免频繁创建和删除临时表,以减少系统表资源的消耗。

  22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

  23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

  24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

  25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

  26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

  27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

  28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

  29.尽量避免大事务操作,提高系统并发能力。

  30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
分享到:
评论

相关推荐

    sql查询优化(提高MySQL数据库查询效率的几个技巧)

    其他查询优化技巧 除了使用 Statement 进行绑定查询外,还有其他几种方法可以提高 MySQL 数据库查询效率: * 使用索引:在频繁查询的列上建立索引,可以提高查询效率。 * 优化 SQL 语句:使用 EXPLAIN 语句来分析 ...

    提升性能的艺术:深入探索SQL查询优化技巧

    SQL(Structured Query Language)是关系数据库的标准查询语言,用于管理和操作数据库中的数据。然而,随着数据量的增长...希望这篇文章能够帮助你更好地理解和运用SQL查询优化技巧,为你的数据库应用提升性能和效率。

    百万数据查询优化技巧三十则

    百万数据查询优化技巧三十则 1.优化sq查询 2.编写sql规范 3.适用大部分sql

    sql查询优化 查询优化

    SQL查询优化是数据库管理系统中至关重要的一环,它旨在提高数据检索速度,降低系统资源消耗,提升整体应用性能。SQL查询优化主要包括以下几个...正确理解和运用这些优化技巧,能够显著提升数据库应用的性能和响应速度。

    SQL性能优化技巧分享 SQL性能优化技巧.docx

    3. PL/SQL优化技巧 PL/SQL是Oracle数据库的过程语言,可以用来编写存储过程、函数和触发器。优化PL/SQL代码可以提高整个数据库的性能。可以使用以下技巧来优化PL/SQL代码: * 使用缓存机制 * 限制循环的次数 * ...

    informix数据库sql查询优化

    假设表`T1`上存在`(C1, C2, C3)`组合的索引,则查询优化器将优先使用该索引进行快速定位。 ### 总结 通过对Informix数据库中的SQL单表查询优化进行深入探讨,我们了解了查询优化的基本原理、谓词的重要性以及如何...

    Oracle SQL性能优化技巧总结

    ### Oracle SQL性能优化技巧总结 #### 一、选择最有效率的表名顺序 在Oracle数据库中,SQL语句的执行顺序对查询性能有着显著的影响。对于基于规则的优化器而言,FROM子句中表的顺序至关重要。Oracle的解析器会按照...

    优化sql查询速度

    高级优化技巧 - **使用动态 SQL**:虽然动态 SQL 在安全性方面存在一定的风险,但在某些特定场景下,动态 SQL 能够提供更灵活的查询方式,从而达到优化查询速度的目的。 - **事务处理**:合理使用事务处理机制可以...

    提升MYSQL查询效率的10个SQL语句优化技巧.doc

    以下是十个 SQL 语句优化技巧来提升 MYSQL 查询效率: 1. 优化 MySQL 查询缓存 MySQL 查询缓存可以启用高速查询缓存,让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一。当同一个查询被执行多次时,如果...

    Oracle SQL性能优化技巧大总结

    ### Oracle SQL性能优化技巧大总结 #### 一、选择最有效率的表名顺序 **背景**:在基于规则的优化器(RBO)中,Oracle解析器处理FROM子句中的表名是从右向左的。为了提高查询效率,需要合理安排表的顺序。 **技巧...

    ORACLE SQL性能优化技巧

    书写高质量的oracle sql,用表连接替换EXISTS,索引的技巧等等

    sql面试题(sql查询优化)

    本文将深入探讨SQL查询优化的关键知识点,包括常见的优化策略、查询优化技巧以及解决SQL查询中常见问题的方法。 ### SQL查询优化的重要性 SQL查询优化的目标是在满足查询需求的前提下,尽可能减少数据库的I/O操作...

    MySQL性能优化 SQL优化方法技巧

    #### 三、SQL语句优化技巧 **1. 避免全表扫描** - 尽量使用索引来避免全表扫描,提高查询效率。 - 对于频繁查询的字段建立索引。 **2. 选择合适的数据类型** - 选择最适合数据特性的数据类型,以减少存储空间的...

    sql大数据量查询的优化技巧

    以下是对标题和描述中提及的SQL优化技巧的详细说明: 1. **建立索引**:索引可以显著提高查询速度,尤其是在WHERE子句和ORDER BY子句涉及的列上。创建合适的索引可以避免全表扫描,提高查询效率。 2. **避免NULL值...

    oracle SQL优化技巧

    Oracle SQL 优化技巧 Oracle SQL 优化是提高数据库性能的关键技巧之一。以下是 Oracle SQL 优化的 12 个技巧: 1. 选择最有效率的表名顺序 在基于规则的优化器中,Oracle 的解析器按照从右到左的顺序处理 FROM ...

    ORACLE_SQL性能优化技巧

    Oracle SQL性能优化是数据库管理中的关键环节...通过理解和应用这些技巧,数据库管理员和开发人员可以有效地优化ORACLE SQL查询,提升系统整体性能。记住,每个数据库环境都有其独特性,最佳实践需要根据实际情况调整。

    查询优化技巧三十则

    ### 查询优化技巧三十则 #### 1. 避免全表扫描 在编写 SQL 查询时,应尽可能地使用 `WHERE` 子句并结合 `ORDER BY` 或其他过滤条件来减少查询范围,避免不必要的全表扫描。例如: ```sql SELECT id FROM t WHERE ...

    SQL查询优化[定义].pdf

    在实际应用中,还应注意其他一些查询优化技巧,如避免在子查询中使用NOT IN或EXISTS操作,因为它们可能迫使数据库进行全表扫描。使用JOIN代替子查询有时能提高效率,尤其是当JOIN的关联条件可以利用索引时。另外,...

    SQL_Server_2008查询性能优化

    下面将详细讨论SQL Server 2008中的关键性能优化技巧。 首先,我们需要理解SQL查询执行的基础——查询计划。SQL Server通过解析查询语句,生成一个执行计划,然后按照这个计划来执行查询。优化查询计划是提高性能的...

Global site tag (gtag.js) - Google Analytics