1.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
3.应尽量避免在 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
4.in 和 not in 也要慎用,因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
5.尽量避免在索引过的字符数据中,使用非打头字母搜索。这也使得引擎无法利用索引。
见如下例子:
SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’
即使NAME字段建有索引,前两个查询依然无法利用索引完成加快操作,引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。
6.必要时强制查询优化器使用某个索引,如在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访
问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下
面语句将进行全表扫描:
select id from t where num=@num
可以改为强制查询使用索引:
select id from t with(index(索引名)) where num=@num
7.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
SELECT * FROM T1 WHERE F1/2=100
应改为:
SELECT * FROM T1 WHERE F1=100*2
SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
应改为:
SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’
SELECT member_number, first_name, last_name FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
应改为:
SELECT member_number, first_name, last_name FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
即:任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
8.应尽量避免在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'
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该
索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
11.很多时候用 exists是一个好的选择:
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)
SELECT SUM(T1.C1)FROM T1 WHERE(
(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)
SELECT SUM(T1.C1) FROM T1WHERE EXISTS(
SELECT * FROM T2 WHERE T2.C2=T1.C2)
两者产生相同的结果,但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。
如果你想校验表里是否存在某条纪录,不要用count(*)那样效率很低,而且浪费服务器资源。可以用EXISTS代替。如:
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
可以写成:
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')
经常需要写一个T_SQL语句比较一个父结果集和子结果集,从而找到是否存在在父结果集中有而在子结果集中没有的记录,如:
SELECT a.hdr_key FROM hdr_tbl a---- tbl a 表示tbl用别名a代替
WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)
SELECT a.hdr_key FROM hdr_tbl a
LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL
SELECT hdr_key FROM hdr_tbl
WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)
三种写法都可以得到同样正确的结果,但是效率依次降低。
12.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。
13.避免频繁创建和删除临时表,以减少系统表资源的消耗。
14.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。
15.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
16.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
17.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
18.尽量避免大事务操作,提高系统并发能力。
19.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
20. 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:
SELECT name FROM employee WHERE salary > 60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。
21.充分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这时在 WHERE 子句中将连接条件完整的写上,有可能大大提高查询速度。
例:
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO
第二句将比第一句执行快得多。
22、使用视图加速查询
把表的一个子集进行排序并创建视图,有时能加速查询。它有助于避免多重排序 操作,而且在其他方面还能简化优化器的工作。例如:
SELECT cust.name,rcvbles.balance,……other columns
FROM cust,rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
AND cust.postcode>“98000”
ORDER BY cust.name
如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个视图中,并按客户的名字进行排序:
CREATE VIEW DBO.V_CUST_RCVLBES
AS
SELECT cust.name,rcvbles.balance,……other columns
FROM cust,rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
ORDER BY cust.name
然后以下面的方式在视图中查询:
SELECT * FROM V_CUST_RCVLBES
WHERE postcode>“98000”
视图中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以查询工作量可以得到大幅减少。
23、能用DISTINCT的就不用GROUP BY
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改为:
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
24.能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数,这样就会减少很多不必要的资源
35.尽量不要用SELECT INTO语句。
SELECT INOT 语句会导致表锁定,阻止其他用户访问该表。
相关推荐
本篇文章将深入探讨Oracle中的B*Tree索引、反向索引、降序索引、位图索引和函数索引。 1. **B*Tree索引**: - B*Tree索引是Oracle中最常见的索引类型,其结构类似于二叉树,可以高效地处理高基数数据列,即具有...
本篇将详细讲解Oracle数据库中的索引及其重要性。 一、索引的概念与类型 1. 索引是一种特殊的数据结构,它为数据库表中的列提供快速访问路径。就像书的目录一样,通过索引,数据库系统可以迅速定位到所需的数据行,...
总的来说,理解Oracle索引的工作原理并适当使用,是实现数据库高效运行的关键。正确选择和设计索引,避免使用限制索引的因素,能显著提高查询性能,减少数据库响应时间,从而提升整个系统的性能。在实践中,需要结合...
本篇文章将深入探讨Oracle索引的学习,重点关注索引的原理、类型、优缺点以及如何使用和分析执行计划。 首先,理解索引的基本概念。索引是一种特殊的数据结构,它存储在表空间中,用于加速对表数据的访问。当执行...
这篇博文将深入探讨Oracle中索引的基本概念、类型、创建与管理,以及如何通过索引来优化查询性能。 首先,我们来理解什么是索引。索引就像是书的目录,当我们需要查找某个特定的信息时,通过目录可以直接定位到相应...
本篇将深入探讨Oracle中的主要索引类型,以及它们的应用场景和优缺点。 一、B-Tree索引 B-Tree(二叉树)索引是最常见的索引类型,适用于等值查询。当用户执行WHERE子句中含有等于、不等于、大于、小于或在某个范围...
### Oracle索引创建详解 #### 一、Oracle索引的重要性及基本概念 在数据库系统中,索引是一种用于提高数据检索速度的数据结构。对于大型数据库系统而言,索引的合理设计与使用对于提升查询性能至关重要。Oracle...
本篇文章将深入探讨Oracle数据库中的索引及其相关知识。 一、索引的概念与作用 索引是一种数据结构,它为数据库表中的数据提供快速访问路径。就像书籍的目录,索引可以帮助数据库系统迅速定位到所需的数据行,从而...
本篇内容将深入解析Oracle索引的基本概念、原理以及类型。 首先,索引是可选的表对象,其主要目的是提升查询速度。尽管索引可以显著提高数据检索的效率,但同时也占用额外的存储空间,并可能影响数据插入、更新和...
本文将深入探讨Oracle索引的使用、存储原理以及如何分析索引效率,特别是通过`clustering_factor`这个指标来评估索引的性能。 首先,让我们理解索引的工作原理。在Oracle中,索引是一种特殊的数据库对象,它为表的...
本篇主要讨论的是B树索引。 B树索引是Oracle中最常见的索引类型,它采用树形结构进行数据组织。B树索引主要由叶子节点、分支节点和根节点组成。叶子节点包含指向表数据行的ROWID,而分支节点则包含指向其他分支节点...
这篇博客文章将深入探讨Oracle索引的基本概念、类型以及如何优化其使用。 首先,我们需要理解索引是如何创建和存储的。在Oracle中,最常见的索引类型是B树索引(B-Tree Index),它按照键值的顺序组织数据。当一个...
【Oracle索引介绍】 在数据库管理系统中,索引是一种至关重要的数据结构,它极大地提高了数据检索的速度。Oracle数据库,作为一款广泛使用的RDBMS(关系型数据库管理系统),同样利用索引来优化查询性能。本篇文章...
本篇将详细探讨Oracle优化器模式以及Oracle索引优化规则。 首先,Oracle优化器主要有两种模式:基于规则的优化(RBO)和基于代价的优化(CBO)。RBO在早期版本中较为常见,它根据预设的规则来决定查询路径,例如,...
本篇将深入探讨Oracle数据库的B-Tree索引,以及如何理解和分析索引的内部结构。 B-Tree索引是Oracle中最常见的索引类型,它是一种自平衡的树形数据结构。B-Tree索引的主要特点是,每个节点可以有多个子节点,每个...
Oracle数据库的经典优化中,索引原理是至关重要的一环。索引能够显著提升数据检索速度,但正确使用和理解各种索引选项才能发挥其最大效益。本文主要讨论了Oracle数据库中的一些关键索引概念和技术。 首先,基本的...
根据提供的标题、描述以及部分代码内容,我们可以了解到这篇文章主要探讨的是如何在Oracle数据库中查询所有用户表的相关信息,包括表名、主键名称、索引、外键等元数据信息。接下来,我们将对这些知识点进行详细的...
在Oracle数据库管理中,索引是提升数据查询性能的关键元素。索引的健康状况直接影响到数据库的运行效率,因此,定期进行索引检查、重建和碎片收集是非常必要的维护工作。本篇文章将深入探讨这些概念,并提供相关的...
5. **Oracle的竞争调整**:表和索引的参数设置对UPDATE和INSERT等DML操作的性能至关重要。减少锁定冲突、优化并发访问、调整并行度和批处理大小等策略可以缓解竞争现象,提高多用户环境下的性能。 在实际工作中,...
本篇文档主要涉及Oracle数据库的索引和数据优化知识,同时也提到了ArcGIS的空间数据管理和分析。 在Oracle中,存储过程是一种预编译的SQL和PL/SQL代码集合,用于执行特定任务。与SQL Server相比,Oracle的存储过程...