一个SQL语句的优化(sqlserver)

by admin on 2020年1月3日

最早的写法:

SQL语句及数据库优化,SQL语句数据库优化

 1,统一SQL语句的写法

对于以下两句SQL语句,程序员认为是相同的,数据库查询优化器认为是不同的。 所以封装成复用方法,用标准模板来控制。

select*from dual  select*From dual
其实就是大小写不同,查询分析器就认为是两句不同的SQL语句,必须进行两次解析。生成2个执行计划
 

2,不要把SQL语句写得太复杂

我经常看到,从数据库中捕捉到的一条SQL语句打印出来有2张A4纸这么长。一般来说这么复杂的语句通常都是有问题的。我拿着这2页长的SQL语句去请教原作者,结果他说时间太长,他一时也看不懂了。可想而知,连原作者都有可能看糊涂的SQL语句,数据库也一样会看糊涂。
  比如 Select语句的结果作为子集
简化SQL语句的重要方法就是采用临时表暂存中间结果,但是,临时表的好处远远不止这些,将临时结果暂存在临时表,后面的查询就在tempdb中了,这可以避免程序中多次扫描主表
也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。
 

3,必须采用绑定变量 

select*from orderheader where changetime >‘2010-10-20 00:00:01‘ 
select*from orderheader where changetime >‘2010-09-22 00:00:01‘

 

以上两句语句,查询优化器认为是不同的SQL语句,需要解析两次。如果采用绑定变量

select*from orderheader where changetime >@chgtime  

4,使用like进行模糊查询时应注意

有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’
 关键词%yue%,由于yue前面用到了“%”,因此该查询必然走全表扫描,除非必要,否则不要在关键词前加%,
  5,联表查询

(1)    连接字段尽量选择聚集索引所在的字段

(2)    仔细考虑where条件,尽量减小A、B表的结果集
 获取下载地址  springmvc+mybatis+spring 整合 bootstrap html5 6,索引,
看sql 的性能,主要看执行计划,还有cpu成本,io成本等。这里就以一个简单的表为例。
首先,创建一个简单的表,一般会先建个主键,系统自动以主键建聚集索引。
判断是否需要优化sql的一个简单规则是:看执行计划中的操作是seek(搜索)还是scan(扫描)
是scan的话就要索引。   使用场景:
当一个系统查询比较频繁,而新建,修改等操作比较少时,可以创建覆盖索引,将查询字段和where子句里的字段全部包含在内,这样查询的速度会比以前快很多,同时也带来弊端,就是新建或修改等操作时,比没有索引或没有建立覆盖索引时的要慢。读写数据库分离也能解决问题
  经常对Creator_Id字段查询,就做个索引。
对表Article的Creator_Id字段建索引
CREATE INDEX Ix_article_creatorid ON Article(Creator_Id) set
statistics io 和 set
statistics,这是性能调优时查看相关cpu占用时间,IO资源数据的两个比较重要的命令
  记得Order by 语句加索引   7,读写分离  
当主数据库进行写操作时,数据要同步到从的数据库,这样才能有效保证数据库完整性
主从分离,对数据库层面就是数据同步或者是数据复制;从应用层讲就是请求的分离:增删改请求主库,查询请求从库
  8,尽量不用select * from …..   ,而要写字段名 select
field1,field2,…这条没什么好说的,主要是按需查询,不要返回不必要的列和行。
  9 任何对列的操作都将导致表扫描,
它包括数据库函数、计算表达式等,查询时要尽可能将操作移至等号右边 10
In 、or子句常会使索引失效 显而易见的,IN,OR扩大的查询范围。
11通常情况下,连接比子查询效率要高
必然的,需要子查询时,也是用临时表暂存中间结果

1,统一SQL语句的写法
对于以下两句SQL语句,程序员认为是相同的,数据库查询优化器认为是不同…

昨天与大家分享了SQL优化中的索引优化,今天给大家聊一下,在开发过程中高质量的代码也是会带来优化的

图片 1

(1)      选择最有效率的表名顺序
( 只在基于规则的优化器中有效 ) :

WITH T AS
(SELECT case when Col1 IS NULL OR Col1=N'' then Col2 else Col1 end as Code,case when Col1 IS NULL OR Col1=N'' then 1 else 0 end as Flag FROM YM  WHERE Col_076 BETWEEN '2018-07-25' AND '2018-08-03' AND Col_478=N'xx' AND Col_346 LIKE N'%dd%'),
D AS (SELECT Code,province,city  FROM Adds)
SELECT province AS 省,city as 市,COUNT(1) as 票数 FROM 
(SELECT A.DR_250 as province,A.DR_251 as city FROM T INNER JOIN TB AS A ON A.DR_203=T.Code WHERE T.Flag=0
 UNION ALL
 SELECT D.province,D.city FROM T INNER JOIN D ON D.Code=T.Code WHERE T.Flag=1
 UNION ALL
 SELECT '' AS province,'' AS city FROM T WHERE Code IS NULL OR Code=N'') AS S 
GROUP BY province,city;

网上关于SQL优化的教程很多,但是比较杂乱。整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。

现在有个SQL跑了3.5秒,想优化到1秒以内updateTMP_YQLOAD_VALIDttsettt.error_msg=tt.error_msg||’;’||chr(13)||chr(10)||’错误信息’,tt.is_operate=decode(‘N’,’Y’,’Y’,’N’)whereexists(SELECT1FROMMSC_YQEXPSCON_SUM_VIEWMC,MSC_YQEXPSCON_VIEWM,TMP_YQLOAD_VALIDT/*临时表*//*出口约桥预装载视图*/WHEREM.SECR_CNT_QUANLITYMC.CT_NUMAND(T.ERROR_MSGISNULLORT.IS_OPERATE=’Y’)andTT.ROWID=T.ROWIDandM.SECR_CNT_TYPE=T.SPCI_CNT_TYPE/*箱型*/ANDM.SECR_CNT_SIZE=T.SPCI_CNT_SIZE/*尺码*/ANDM.SHSS_ORG_ID=’8883’/*ORGID*/ANDM.SSEM_BL_NO=T.BL_NO/*SO号*/ANDMC.BL_NO=T.BL_NOANDMC.SPCI_CNT_SIZE=T.SPCI_CNT_SIZEANDMC.SPCI_CNT_TYPE=T.SPCI_CNT_TYPEANDMC.ORG_ID=’8883’/*ORGID*/ANDT.BL_STATUS=’1’/*0代表进口舱单*/)andtt.STOP_VALID=’N’

ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名,
FROM
子句中写在最后的表 ( 基础表driving table)
将被最先处理,在 FROM
子句中包含多个表的情况下 , 你必须选择记录条数最少的表作为基础表。如果有 3 个以上的表连接查询
, 那就需要选择交叉表 (intersection table) 作为基础表 , 交叉表是指那个被其他表所引用的表 .

 

SQL语句优化,简单的说就是对SQL语句进行高效率的代码编写,其原理其实与SQL索引优化一致:

(2)     WHERE
子句中的连接顺序.:

最新的写法:

建立索引其实就是减少数据库在执行时所扫描的影响行数,尽可能的避免全局扫描

ORACLE 采用自下而上的顺序解析 WHERE 子句 , 根据这个原理 , 表之间的连接必须写在其他 WHERE条件之前 , 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾 .

SELECT case when Col1 IS NULL OR Col1=N'' then Col2 else Col1 end as Code,case when Col1 IS NULL OR Col1=N'' then 1 else 0 end as Flag into #T FROM YM WHERE Col_076 BETWEEN '2018-07-25' AND '2018-08-03' AND Col_478=N'xx' AND Col_346 LIKE N'%dd%';
SELECT Code,province,city into #D  FROM Adds;
SELECT province AS 省,city as 市,COUNT(1) as 票数 FROM 
(SELECT A.DR_250 as province,A.DR_251 as city FROM #T INNER JOIN TB AS A ON A.DR_203=#T.Code WHERE #T.Flag=0
UNION ALL
SELECT #D.province,#D.city FROM #T INNER JOIN #D ON #D.Code=#T.Code WHERE #T.Flag=1
UNION ALL
SELECT '' AS province,'' AS city FROM #T WHERE Code IS NULL OR Code=N'') AS S GROUP BY province,city;
DROP table #T;
DROP table #D;

优化注意:
对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by
涉及的列上建立索引。

(3)     SELECT
子句中避免使用 ‘ * ‘ :

新的写法比原始写法性能高出太多(原语句执行会超时),最大的原因是对with语句理解有误!!!

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

ORACLE 在解析的过程中 , 会将 ‘*’ 依次转换成所有的列名 ,
这个工作是通过查询数据字典完成的 , 这意味着将耗费更多的时间

优化方案:
这里给大家说一下什么SQL语句会执行全表扫描

(4)     减少访问数据库的次数:

1.
查询条件中含有is null的select语句执行慢

ORACLE 在内部执行了许多工作 :
解析 SQL 语句 , 估算索引的利用率 , 绑定变量 , 读数据块等;

解决方法:SQL语法中使用NULL会有很多麻烦,最好索引列都是NOT
NULL的,而且最好不要给数据库留NULL,尽可能的使用NOT
NULL填充数据库.备注、描述、评论之类的可以设置为
NULL,其他的,最好不要使用NULL。;

(5)     
SQL*Plus , SQL*Forms 和 Pro*C 中重新设置 ARRAYSIZE 参数 , 可以增加每次数据库访问的检索数据量 , 建议值为
200

is null,可以建立索引,is
null查询时可以启用索引查找,但是效率还不是值得肯定,建议不要使用。
is not null 时永远不会使用索引。一般数据量大的表不要用is null查询。

(6)     使用
DECODE 函数来减少处理时间:

不要以为 NULL 不需要空间,比如:char(100)
型,在字段建立时,空间就固定了,
不管是否插入值(NULL也包含在内),都是占用
100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表
.

可以在对字段上设置默认值0,确保表中字段列没有null值,然后这样可以匹配where
字段等于0的数据:

(7)     整合简单
, 无关联的数据库访问:

 

如果你有几个简单的数据库查询语句 , 你可以把它们整合到一个查询中 ( 即使它们之间没有关系
)

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

(8)     删除重复记录 :

原因:SQL中,不等于操作符会限制索引,引起全表扫描,即使比较的字段上有索引
解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描。例如,把column<>’aaa’,改成column<’aaa’
or column>’aaa’,就可以使用索引了。

最高效的删除重复记录方法 ( 因为使用了 ROWID) 例子:

3.应尽量避免在 where
子句中使用 or
来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

DELETE  FROM  EMP E  WHERE  E.ROWID >
(SELECT MIN(X.ROWID)
FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);

select id from t where num=10 or Name = ‘admin’
可以这样查询:

(9)     
TRUNCATE 替代 DELETE :

select id from t where num = 10
union all
select id from t where Name = ‘admin’

当删除表中的记录时 , 在通常情况下 , 回滚段 (rollback segments ) 用来存放可以被恢复的信息 . 如果你没有 COMMIT 事务 ,ORACLE 会将数据恢复到删除之前的状态 ( 准确地说是 恢复到执行删除命令之前的状况 )而当运用 TRUNCATE 时 , 回滚段不再存放任何可被恢复的信息 . 当命令运行后 , 数据不能被恢复 . 因此很少的资源被调用
, 执行时间也会很短
. ( 译者按 : TRUNCATE 只在删除全表适用
,TRUNCATE 是 DDL
不是 DML)

union解释
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT
语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT
语句中的列的顺序必须相同。
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION
ALL。
使用or连接索引不生效
代码如下:
mysql> explain select id from t1 where name=1 or age=2\G;
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: in_name
key: NULL
key_len: NULL
ref: NULL
rows: 10000
filtered: 55.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
如果使用UNION连接的话查询时索引生效。
代码如下:
mysql> desc select * from t1 where name=1 union all select *from t1
where age=2\G;
*************************** 1. row
***************************
id: 1
select_type: PRIMARY 第一条语句索引生效
table: t1
partitions: NULL
type: ref
possible_keys: in_name
key: in_name
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row
***************************
id: 2
select_type: UNION 第二条语句未设定索引没有使用索引
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000
filtered: 10.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)

(10) 尽量多使用 COMMIT :

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

只要有可能 , 在程序中尽量多使用
COMMIT, 这样程序的性能得到提高 , 需求也会因为 COMMIT 所释放的资源而减少 :
COMMIT 所释放的资源 :
a. 回滚段上用于恢复数据的信息 .
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE 为管理上述 3 种资源中的内部花费

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

(11) 用 Where 子句替换 HAVING 子句:

select id from t where num between 1 and 3

避免使用 HAVING
子句 , HAVING
只会在检索出所有记录之后才对结果集进行过滤
. 这个处理需要排序 , 总计等操作 . 如果能通过 WHERE 子句限制记录的数目 ,
那就能减少这方面的开销 . ( 非 oracle 中 ) on 、where 、 having
这三个都可以加条件的子句中, on 是最先执行, where
次之, having
最后,因为 on
是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,
where也应该比
having 快点的,因为它过滤数据后才进行 sum ,在两个表联接时才用 on
的,所以在一个表的时候,就剩下 where 跟 having
比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是
where 可以使用
rushmore 技术,而
having
就不能,在速度上后者要慢如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,
where 的作用时间是在计算之前就完成的,而 having 就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。在多表联接查询时,
on 比 where 更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where 进行过滤,然后再计算,计算完后再由 having 进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里

5.模糊查询下面的查询也将导致全表扫描:

(12) 减少对表的查询:

select id from t where name like ‘%abc%’
若要提高效率,可以考虑全文检索。

在含有子查询的 SQL
语句中 , 要特别注意减少对表的查询 . 例子:

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

     SELECT
TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT

 

TAB_NAME,DB_VER FROM TAB_COLUMNS 
WHERE VERSION
= 604)

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

(13) 通过内部函数提高 SQL 效率 . :

select num from a where num in(select num from b)
用下面的语句替换:

复杂的 SQL 往往牺牲了执行效率 .
能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的

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

(14) 使用表的别名 (Alias) :

6.如果在 where
子句中使用参数,也会导致全表扫描。

当在 SQL 语句中连接多个表时 ,
请使用表的别名并把别名前缀于每个 Column 上 . 这样一来 ,
就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误 .

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

(15) 用 EXISTS 替代 I N 、 用 NOT EXISTS 替代 NOT IN :

select id from t where num = @num
可以改为强制查询使用索引:

在许多基于基础表的查询中 , 为了满足一个条件 , 往往需要对另一个表进行联接 . 在这种情况下 , 使用EXISTS( 或 NOT EXISTS)
通常将提高查询的效率 . 在子查询中 ,NOT IN 子句将执行一个内部的排序和合并 .无论在哪种情况下 ,NOT IN 都是最低效的
( 因为它对子查询中的表执行了一个全表遍历 ). 为了避免使用 NOT IN ,
我们可以把它改写成外连接 (Outer Joins) 或 NOT
EXISTS.

select id from t with(index(索引名)) where num = @num
应尽量避免在 where
子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

例子:

select id from t where num/2 = 100
应改为:

( 高效 ) SELECT * FROM EMP ( 基础表 )  WHERE EMPNO > 0  AND
EXISTS( SELECT ‘X’  FROM DEPT  WHERE DEPT.DEPTNO =
EMP.DEPTNO  AND LOC =
‘MELB’)

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

( 低效 ) SELECT * FROM EMP ( 基础表 )  WHERE EMPNO > 0  AND
DEPTNO IN(SELECTDEPTNO  FROM DEPT  WHERE LOC = ‘MELB’ )

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
应改为:

(16) 识别 ‘
低效执行 ‘
的 SQL
语句:

select id from t where name like ‘abc%’
select id from t where createdate >= ‘2005-11-30’ and createdate <
‘2005-12-1’
8.不要在 where
子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

虽然目前各种关于 SQL 优化的图形化工具层出不穷 , 但是写出自己的 SQL 工具来解决问题始终是一个最好的方法:

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

SELECT
EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND ((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2 ) Hit_radio,
ROUND (DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS
> 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY  4
DESC ;

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

(17) 用索引提高效率:

select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

索引是表的一个概念部分 , 用来提高检索数据的效率, ORACLE 使用了一个复杂的自平衡
B-tree 结构 .
通常 , 通过索引查询数据比全表扫描要快 . 当 ORACLE 找出执行查询和
Update 语句的最佳路径时 ,
ORACLE 优化器将使用索引 . 同样在联结多个表时使用索引也可以提高效率 . 另一个使用索引的好处是
, 它提供了主键(primary
key) 的唯一性验证 . 。那些 LONG 或 LONG RAW 数据类型 , 你可以索引几乎所有的列 .
通常 , 在大型表中使用索引特别有效 . 当然 , 你也会发现 , 在扫描小表时 ,
使用索引同样能提高效率 . 虽然使用索引能得到查询效率的提高 , 但是我们也必须注意到它的代价 . 索引需要空间来存储
, 也需要定期维护
, 每当有记录在表中增减或索引列被修改时 , 索引本身也会被修改
. 这意味着每条记录的 INSERT , DELETE , UPDATE 将为此多付出 4 , 5 次的磁盘 I/O . 因为索引需要额外的存储空间和处理 , 那些不必要的索引反而会使查询反应时间变慢 .。定期的重构索引是有必要的 . :

create table #t(…)

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

10.Update
语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

(18) 用 EXISTS 替换 DISTINCT :

11.对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

当提交一个包含一对多表信息 ( 比如部门表和雇员表
) 的查询时 , 避免在 SELECT 子句中使用 DISTINCT.一般可以考虑用
EXIST 替换 , EXISTS 使查询更为迅速
, 因为 RDBMS 核心模块将在 子查询的条件一旦满足后,
立刻返回结果 .
例子:

12.select count(*) from
table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

       ( 低效 ):
SELECT DISTINCT DEPT_NO,DEPT_NAME  FROM DEPT D , EMP
E

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

WHERE
D.DEPT_NO = E.DEPT_NO
( 高效 ):
SELECT DEPT_NO,DEPT_NAME  FROM DEPT D  WHERE EXISTS ( SELECT ‘X’
FROM EMP E 
WHERE E.DEPT_NO = D.DEPT_NO ) ;

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

(19) sql 语句用大写的 ;因为
oracle 总是先解析
sql 语句,把小写的字母转换成大写的再执行

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

(20) 在 java
代码中尽量少用连接符“+”连接字符串!

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

(21) 避免在索引列上使用 NOT 通常 , 

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

我们要避免在索引列上使用 NOT, NOT 会产生在和在索引列上使用函数相同的 影响 . 当 ORACLE” 遇到 ”NOT,他就会停止使用索引转而执行全表扫描 .

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

(22) 避免在索引列上使用计算.
WHERE 子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.

19.
避免频繁创建和删除临时表,以减少系统表资源的消耗。临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,
最好使用导出表。

举例 :
低效:
SELECT … FROM  DEPT  WHERE SAL * 12 > 25000;
高效 :
SELECT … FROM DEPT WHERE SAL >
25000/12;

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

(23) 用 >= 替代 >

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

高效 :
SELECT * FROM  EMP  WHERE  DEPTNO >=4
低效 :
SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于 ,
前者 DBMS 将直接跳到第一个 DEPT
等于 4 的记录而后者将首先定位到 DEPTNO=3 的记录并且向前扫描到第一个 DEPT 大于 3 的记录 .

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

(24) 用 UNION 替换 OR ( 适用于索引列
)

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

通常情况下 , 用 UNION 替换 WHERE 子句中的 OR 将会起到较好的效果 .
对索引列使用 OR
将造成全表扫描.
注意 , 以上规则只针对多个索引列有效 . 如果有 column 没有被索引 , 查询效率可能会因为你没有选择 OR 而降低 . 在下面的例子中 , LOC_ID 和 REGION 上都建有索引
.
高效 :
SELECT LOC_ID ,
LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID =
10
UNION
SELECT LOC_ID ,
LOC_DESC , REGION
FROM LOCATION
WHERE REGION =
“MELBOURNE”
低效 :
SELECT LOC_ID ,
LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID =
10 OR REGION =
“MELBOURNE”
如果你坚持要用 OR,
那就需要返回记录最少的索引列写在最前面 .

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

(25) 用 IN
来替换 OR 

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

这是一条简单易记的规则,但是实际的执行效果还须检验,在
ORACLE8i 下,两者的执行路径似乎是相同的. 

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

低效 :
SELECT …. FROM LOCATION WHERE LOC_ID = 10
OR LOC_ID = 20
OR LOC_ID =
30
高效
SELECT … FROM LOCATION WHERE LOC_IN  IN
(10,20,30);

27.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

(26) 避免在索引列上使用 IS NULL 和 IS NOT NULL

实际案例分析:拆分大的 DELETE 或INSERT 语句,批量提交SQL语句

避免在索引中使用任何可以为空的列, ORACLE 将无法使用该索引
.对于单列索引,如果列包含空值,索引中将不存在此记录
. 对于复合索引,如果每个列都为空,索引中同样不存在此记录
.  如果至少有一个列不为空,则记录存在于索引中.
举例 : 如果唯一性索引建立在表的 A 列和 B 列上 , 并且表中存在一条记录的
A,B 值为 (123,null) , ORACLE 将不接受下一条具有相同
A,B 值( 123,null
)的记录 ( 插入 ). 然而如果 所有的索引列都为空, ORACLE将认为整个键值为空而空不等于空 . 因此你可以插入 1000 条具有相同键值的记录 ,
当然它们都是空 !
因为空值不存在于索引列中 , 所以 WHERE 子句中对索引列进行空值比较将使 ORACLE 停用该索引 .

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT
查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。

低效 : ( 索引失效 )
SELECT … FROM DEPARTMENT  WHERE DEPT_CODE IS NOT NULL ;
高效 : ( 索引有效 )
SELECT … FROM DEPARTMENT  WHERE DEPT_CODE >= 0;

Apache
会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。

(27) 总是使用索引的第一个列 :

如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂了。

如果索引是建立在多个列上 , 只有在它的第一个列
(leading column) 被 where 子句引用时 , 优化器才会选择使用该索引 . 这也是一条简单而重要的规则,当仅引用索引的第二个列时
, 优化器使用了全表扫描而忽略了索引

所以,如果你有一个大的处理,你一定把其拆分,使用 LIMIT
oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例:

(28) 用 UNION-ALL 替换 UNION ( 如果有可能的话 )

 

当 SQL 语句需要 UNION 两个查询结果集合时 ,
这两个结果集合会以 UNION-ALL 的方式被合并
, 然后在输出最终结果前进行排序 . 如果用 UNION ALL 替代 UNION, 这样排序就不是必要了 .
效率就会因此得到提高 . 需要注意的是 , UNION ALL 将重复输出两个结果集合中相同记录 . 因此各位还是 要从业务需求分析使用 UNION ALL 的可行性. UNION 将对结果集合排序
, 这个操作会使用到
SORT_AREA_SIZE 这块内存 . 对于这 块内存的优化也是相当重要的 . 下面的 SQL 可以用来查询排序的消耗量

while(1){

低效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE
= ’31-DEC-95′
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE
= ’31-DEC-95′
高效 :
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95′
UNION ALL

//每次只做1000条

SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS

mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit
1000”);

WHERE TRAN_DATE

’31-DEC-95′

(29) 用 WHERE 替代 ORDER BY :

ORDER BY 子句只在两种严格的条件下使用索引 .
ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序
.
ORDER BY 中所有的列必须定义为非空 .
WHERE 子句使用的索引和 ORDER BY 子句中所使用的索引不能并列 .

例如 :
表 DEPT 包含以下列 :
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL

低效 : ( 索引不被使用 )
SELECT DEPT_CODE FROM DEPT  ORDER BY DEPT_TYPE
高效 : ( 使用索引 )
SELECT DEPT_CODE  FROM DEPT  WHERE DEPT_TYPE >
0

(30) 避免改变索引列的类型 .:

当比较不同数据类型的数据时 , ORACLE 自动对列进行简单的类型转换 .

假设 EMPNO 是一个数值类型的索引列
.
SELECT …  FROM EMP  WHERE EMPNO = ‘123′
实际上 , 经过 ORACLE 类型转换 , 语句转化为 :
SELECT …  FROM EMP  WHERE EMPNO = TO_NUMBER(‘123′)
幸运的是 , 类型转换没有发生在索引列上 , 索引的用途没有被改变
.
现在 , 假设 EMP_TYPE 是一个字符类型的索引列 .
SELECT …  FROM EMP  WHERE EMP_TYPE =
123
这个语句被 ORACLE
转换为 :
SELECT …  FROM EMP  WHERE TO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换 , 这个索引将不会被用到
! 为了避免 ORACLE 对你的 SQL 进行隐式的类型转换
, 最好把类型转换用显式表现出来 . 注意当字符和数值比较时
, ORACLE 会优先转换数值类型到字符类型

(31) 需要当心的 WHERE 子句 :

某些 SELECT 语句中的 WHERE 子句不使用索引 . 这里有一些例子 .
在下面的例子里 ,
(1) ‘!=’ 将不使用索引 . 记住 , 索引只能告诉你什么存在于表中 , 而不能告诉你什么不存在于表中 . (2) ‘||’ 是 字符连接函数 . 就象其他函数那样 , 停用了索引 . (3) ‘+’ 是数学函数 . 就象其他数学函数那样
,停用了索引 .
(4) 相同的索引列不能互相比较 , 这将会启用全表扫描
.

(32) a. 如果检索数据量超过 30%
的表中记录数 .
使用索引将没有显著的效率提高 .
b. 在特定情况下 ,
使用索引也许会比全表扫描慢 , 但这是同一个数量级上的区别 . 而通常情况下 , 使用索引比全表扫描要块几倍乃至几千倍
!

(33) 避免使用耗费资源的操作 :

带有 DISTINCT,UNION,MINUS,INTERSECT,ORDER BY 的 SQL 语句会启动 SQL 引擎
执行耗费资源的排序 (SORT) 功能 . DISTINCT
需要一次排序操作 , 而其他的至少需要执行两次排序 . 通常 , 带有 UNION, MINUS , INTERSECT 的 SQL 语句都可以用其他方式重写 . 如果你的数据库的 SORT_AREA_SIZE调配得好
, 使用 UNION , MINUS, INTERSECT 也是可以考虑的 , 毕竟它们的可读性很强

(34) 优化 GROUP BY:

提高 GROUP BY
语句的效率 ,
可以通过将不需要的记录在 GROUP BY 之前过滤掉
.
下面两个查询返回相同结果但第二个明显就快了许多 .

低效 :
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB =
‘PRESIDENT’
OR JOB =
‘MANAGER’
高效 :
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
GROUP JOB

if(mysql_affected_rows() == 0){

//删除完成,退出!
break;
}

//每次暂停一段时间,释放表让其他进程/线程访问。
usleep(50000)

}

 

参考链接:

 

这篇文章Qi号也没有完全融汇贯通,希望可以对大家有所帮助。

晚安。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图