数据库SQL优化大总结之 百万级数据库优化计划ITeye - 超凡娱乐

数据库SQL优化大总结之 百万级数据库优化计划ITeye

2019年04月01日11时49分17秒 | 作者: 振锐 | 标签: 数据,运用,索引 | 浏览: 1586

网上关于SQL优化的教程许多,可是比较凌乱。近来有空整理了一下,写出来跟咱们共享一下,其中有过错和缺乏的当地,还请咱们纠正弥补。

这篇文章我花费了许多的时刻查找材料、修正、排版,期望咱们阅览之后,感觉好的话推荐给更多的人,让更多的人看到、纠正以及弥补。

1.对查询进行优化,要尽量防止全表扫描,首要应考虑在 where 及 order by 触及的列上树立索引。

2.应尽量防止在 where 子句中对字段进行 null 值判别,不然将导致引擎抛弃运用索引而进行全表扫描,如:

select id from t where num is null

最好不要给数据库留NULL,尽或许的运用 NOT NULL填充数据库.

补白、描绘、谈论之类的能够设置为 NULL,其他的,最好不要运用NULL。

不要认为 NULL 不需求空间,比方:char(100) 型,在字段树立时,空间就固定了, 不论是否刺进值(NULL也包含在内),都是占用 100个字符的空间的,假如是varchar这样的变长字段, 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 Name = admin

能够这样查询:

select id from t where num = 10union allselect id from t where Name = admin

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

许多时分用 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)

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

应尽量防止在 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最初的idselect 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.Update 句子,假如只更改1、2个字段,不要Update悉数字段,不然频频调用会引起显着的功能耗费,一同带来许多日志。

14.关于多张大数据量(这儿几百条就算大了)的表JOIN,要先分页再JOIN,不然逻辑读会很高,功能很差。

15.select count(*) from table;这样不带任何条件的count会引起全表扫描,而且没有任何业务含义,是必定要根绝的。

16.索引并不是越多越好,索引当然能够前进相应的 select 的功率,但一同也下降了 insert 及 update 的功率,由于 insert 或 update 时有或许会重建索引,所以怎样建索引需求慎重考虑,视详细情况而定。一个表的索引数最好不要超越6个,若太多则应考虑一些不常运用到的列上建的索引是否有 必要。

17.应尽或许的防止更新 clustered 索引数据列,由于 clustered 索引数据列的次序便是表记载的物理存储次序,一旦该列值改动将导致整个表记载的次序的调整,会耗费适当大的资源。若运用体系需求频频更新 clustered 索引数据列,那么需求考虑是否应将该索引建为 clustered 索引。

18.尽量运用数字型字段,若只含数值信息的字段尽量不要规划为字符型,这会下降查询和衔接的功能,并会添加存储开支。这是由于引擎在处理查询和连 接时会逐一比较字符串中每一个字符,而关于数字型而言只需求比较一次就够了。

19.尽或许的运用 varchar/nvarchar 替代 char/nchar ,由于首要变长字段存储空间小,能够节约存储空间,其次关于查询来说,在一个相对较小的字段内查找功率明显要高些。

20.任何当地都不要运用 select * from t ,用详细的字段列表替代“*”,不要回来用不到的任何字段。

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.尽量防止向客户端回来大数据量,若数据量过大,应该考虑相应需求是否合理。

实践事例剖析:拆分大的 DELETE 或INSERT 句子,批量提交SQL句子

假如你需求在一个在线的网站上去履行一个大的 DELETE 或 INSERT 查询,你需求十分当心,要防止你的操作让你的整个网站中止相应。由于这两个操作是会锁表的,表一锁住了,其他操作都进不来了。

Apache 会有许多的子进程或线程。所以,其工作起来适当有功率,而咱们的效劳器也不期望有太多的子进程,线程和数据库链接,这是极大的占效劳器资源的工作,尤其是内存。

假如你把你的表锁上一段时刻,比方30秒钟,那么关于一个有很高拜访量的站点来说,这30秒所堆集的拜访进程/线程,数据库链接,翻开的文件数,或许不仅仅会让你的WEB效劳溃散,还或许会让你的整台效劳器立刻挂了。

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

 

 

(1

 

 (“delete from logs where log_date  = ’2012-11-01’ limit 1000(()  0

 (50000

 

好了,到这儿就写完了。我知道还有许多没有写到的,还请咱们弥补。后边有空会介绍一些SQL优化东西给咱们。让咱们一同学习,一同前进吧!

原创地址:

 

 

 

 

 

 

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表超凡娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章