数据库拜访优化规律ITeye - 凯发娱乐

数据库拜访优化规律ITeye

2019-01-10 11:15:09 | 作者: 浩邈 | 标签: 数据,索引,运用 | 浏览: 2675

一、百万级数据库优化方案


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


这个优化规律概括为5个层次:

1、  削减数据拜访(削减磁盘拜访)

2、  回来更少数据(削减网络传输或磁盘拜访)

3、  削减交互次数(削减网络传输)

4、  削减服务器CPU开支(削减CPU及内存开支)

5、  运用更多资源(添加资源)

SQL什么条件会运用索引?

当字段上建有索引时,一般以下状况会运用索引:

INDEX_COLUMN = ?

INDEX_COLUMN ?

INDEX_COLUMN = ?

INDEX_COLUMN ?

INDEX_COLUMN = ?

INDEX_COLUMN between ? and ?

INDEX_COLUMN in (?,?,...,?)

INDEX_COLUMN like ?||%(后导含糊查询)

T1. INDEX_COLUMN=T2. COLUMN1(两个表经过索引字段相关)


SQL什么条件不会运用索引?



查询条件


不能运用索引原因

INDEX_COLUMN ?

INDEX_COLUMN not in (?,?,...,?)


不等于操作不能运用索引

function(INDEX_COLUMN) = ?

INDEX_COLUMN + 1 = ?

INDEX_COLUMN || a = ?


经过一般运算或函数运算后的索引字段不能运用索引

INDEX_COLUMN like %||?

INDEX_COLUMN like %||?||%


含前导含糊查询的Like语法不能运用索引

INDEX_COLUMN is null


B-TREE索引里不保存字段为NULL值记载,因而IS NULL不能运用索引

NUMBER_INDEX_COLUMN=12345

CHAR_INDEX_COLUMN=12345


Oracle在做数值比较时需求将两头的数据转化成同一种数据类型,假如两头数据类型不一起会对字段值隐式转化,相当于加了一层函数处理,所以不能运用索引。

a.INDEX_COLUMN=a.COLUMN_1


给索引查询的值应是已知数据,不能是不知道字段值。

注:

经过函数运算字段的字段要运用能够运用函数索引,这种需求主张与DBA交流。

有时分咱们会运用多个字段的组合索引,假如查询条件中第一个字段不能运用索引,那整个查询也不能运用索引

如:咱们company表建了一个id+name的组合索引,以下SQL是不能运用索引的

Select * from company where name=?

Oracle9i后引入了一种index skip scan的索引方法来处理相似的问题,可是经过index skip scan进步功能的条件比较特别,运用欠好反而功能会更差。
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    游标笔记ITeye

    游标,数据,读取
  • 2

    SQLSERVER分页查询ITeye

    查询,代码,计划
  • 3
  • 4

    oracle暗码修正办法ITeye

    暗码,修正,用户
  • 5
  • 6
  • 7
  • 8

    oracle 树查询ITeye

    节点,相关,条件
  • 9

    Oracle衔接相关ITeye

    检查,最大,连接数
  • 10