提高SQL的查询效率

简介:

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
 
2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
 
    
1
select  id  from  where  num  is  null

  

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

  

 
3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
 
4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
     
   
1
select  id  from  where  num=10  or  num=20

  

 
可以这样查询:
 
1
2
3
select  id  from  where  num=10
union  all
select  id  from  where  num=20

  

 
5.in 和 not in 也要慎用,否则会导致全表扫描,如:
     
   
1
select  id  from  where  num  in (1,2,3)

  

 
对于连续的数值,能用 between 就不要用 in 了:
     
   
1
select  id  from  where  num  between  and  3

  

 
6.下面的查询也将导致全表扫描:
 
   
1
select  id  from  where  name  like  '%abc%'

  

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

  

 
可以改为强制查询使用索引:
 
    
1
select  id  from  with ( index (索引名))  where  num=@num

  

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

  

 
应改为:
     
    
1
select  id  from  where  num=100*2

  

 
9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
     
    
1
2
select  id  from  where  substring ( name ,1,3)= 'abc' --name以abc开头的id
     select  id  from  where  datediff( day ,createdate, '2005-11-30' )=0 --‘2005-11-30’生成的id

  

 
应改为:
     
1
2
select  id  from  where  name  like  'abc%'
      select  id  from  where  createdate>= '2005-11-30'  and  createdate< '2005-12-1'

  

 
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
 
11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
 
12.不要写一些没有意义的查询,如需要生成一个空表结构:
 
     
1
select  col1,col2  into  #t  from  where  1=0

  

 
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
 
1
create  table  #t(...)

  

 
13.很多时候用 exists 代替 in 是一个好的选择:
 
  
1
select  num  from  where  num  in ( select  num  from  b)

  

 
用下面的语句替换:
     
     
1
select  num  from  where  exists( select  from  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.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理

==============================================================================
本文转自被遗忘的博客园博客,原文链接:http://www.cnblogs.com/rollenholt/p/3691594.html,如需转载请自行联系原作者

目录
打赏
0
0
0
0
15
分享
相关文章
SQL做数据分析的困境,查询语言无法回答的真相
SQL 在简单数据分析任务中表现良好,但面对复杂需求时显得力不从心。例如,统计新用户第二天的留存率或连续活跃用户的计算,SQL 需要嵌套子查询和复杂关联,代码冗长难懂。Python 虽更灵活,但仍需变通思路,复杂度较高。相比之下,SPL(Structured Process Language)语法简洁、支持有序计算和分组子集保留,具备强大的交互性和调试功能,适合处理复杂的深度数据分析任务。SPL 已开源免费,是数据分析师的更好选择。
利用 PolarDB PG 版向量化引擎,加速复杂 SQL 查询!完成任务领发财新年抱枕!
利用 PolarDB PG 版向量化引擎,加速复杂 SQL 查询!完成任务领发财新年抱枕!
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
|
30天前
|
Java使用sql查询mongodb
通过MongoDB Atlas Data Lake或Apache Drill,可以在Java中使用SQL语法查询MongoDB数据。这两种方法都需要适当的配置和依赖库的支持。希望本文提供的示例和说明能够帮助开发者实现这一目标。
47 17
【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
本文详细介绍了MyBatis中的动态SQL功能,涵盖其背景、应用场景及实现方式。
106 6
如何在Django中正确使用参数化查询或ORM来避免SQL注入漏洞?
如何在Django中正确使用参数化查询或ORM来避免SQL注入漏洞?
141 77
|
2月前
|
Java使用sql查询mongodb
通过使用 MongoDB Connector for BI 和 JDBC,开发者可以在 Java 中使用 SQL 语法查询 MongoDB 数据库。这种方法对于熟悉 SQL 的团队非常有帮助,能够快速实现对 MongoDB 数据的操作。同时,也需要注意到这种方法的性能和功能限制,根据具体应用场景进行选择和优化。
116 9
Vanna:开源 AI 检索生成框架,自动生成精确的 SQL 查询
Vanna 是一个开源的 Python RAG(Retrieval-Augmented Generation)框架,能够基于大型语言模型(LLMs)为数据库生成精确的 SQL 查询。Vanna 支持多种 LLMs、向量数据库和 SQL 数据库,提供高准确性查询,同时确保数据库内容安全私密,不外泄。
412 7
Vanna:开源 AI 检索生成框架,自动生成精确的 SQL 查询
|
3月前
|
使用java在未知表字段情况下通过sql查询信息
使用java在未知表字段情况下通过sql查询信息
55 8
|
3月前
|
PHP开发中防止SQL注入的方法,包括使用参数化查询、对用户输入进行过滤和验证、使用安全的框架和库等,旨在帮助开发者有效应对SQL注入这一常见安全威胁,保障应用安全
本文深入探讨了PHP开发中防止SQL注入的方法,包括使用参数化查询、对用户输入进行过滤和验证、使用安全的框架和库等,旨在帮助开发者有效应对SQL注入这一常见安全威胁,保障应用安全。
114 4
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等