关于数据库查询性能调优和索引优化的总结

数据库 SQL Server
本文我们主要对数据库查询性能调优和索引优化的知识进行了总结,了解这些知识有助于我们更好地提高SQL查询的性能,希望能够对您有所帮助。

数据库查询性能调优索引优化的知识是本文我们主要要介绍的内容,了解了这方面的知识有助于提高SQL查询的效率,接下来我们开始介绍这部分内容。

查询性能调优是个很大的话题,这里边涉及到的技术非常广泛,但是我们一般可以把它大致分为以下几个层次:

1.减少数据访问。相关的技术就是建立合适的索引,将全表扫描、索引扫描(scan)等耗时的操作转化为索引查找(seek)。建立正确的索引,能让数据库查询性能提升100-1000倍甚至更高,就好比一本非常厚的词典,如果没有任何索引,你要查一个东西,那可是相当费尽,需要整本书查一遍,有索引就可以直接根据索引定位了。这是最重要的改善性能的途径。

2.减少返回的数据。在网络中传输数据,带宽是有限的,如果能按需提取最少量的数据,会起到不错的作用。这里需要注意的是,在SQL中,不要出现select *,而是需要什么字段,就提取什么字段。

3.减少与数据库交互次数。网络资源有限,显然,频繁与数据库交互,也是制约性能的一个因素。一个良好的建议就是,使用存储过程,或者批处理语句,这样能减少与数据库的交互,提升一部分性能。

4.减少CPU的负荷。这里,主要是使用缓存计划。在查询中,尽量使用参数化的查询。这样的话,数据库会对查询参数进行缓存,从而复用查询计划。

5.提升硬件性能。这是***一招了,如果其他方面都已经做得非常不错了,性能瓶颈在CPU,内存和磁盘上,那采取提升硬件性能的方案就会显得比较合适了,否则还是先去优化其他的地方吧。

以上5个层次的优化带来的性能改善,是依次下降的,是一个倒置的金字塔。

下边详细讨论一下索引的知识。

百度百科上对索引的描述是:“数据库索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。”

索引,分为聚集索引(clustered index)和非聚集索引(nonclustered index)两种。

a.聚集索引

含有聚集索引的表,叫做聚集表,它的数据行的组织方式,是跟聚集索引的顺序是一致的。聚集索引覆盖的列,叫做聚集键。

用新华字典来比喻的话,正文的每一个字就是一个数据行,他们的组织顺序是根据拼音,如果拼音相同,就会根据笔画(不一定准确,见谅),因此,新华字典里的聚集索引覆盖的列就是拼音和笔画。

很容易理解的是,正文只能按照一种既定的顺序去排序,同理,在一张表里,只能有一个聚集索引,从而决定着数据行的组织方式。

b.非聚集索引

非聚集索引,用新华字典来比喻的话,就是字典正文之前的那些按拼音查找,按部首查找,按笔画查找的附录。它们描述了正文中的文字的排序位置,但是他们跟正文是分开的。非聚集索引,它跟数据的组织顺序是毫无关系的,它用一系列指针来指向数据行,从而来描述数据行的位置。

不含有聚集索引的表,叫做堆表,它的数据行组织顺序,是没有特定顺序的,类似于一堆书,增加一本书就放在这堆书的上面(在堆表中,具体实现方式可能不一样)。

聚集索引对查询性能影响非常大。聚集表中,非聚集索引是根据聚集键来定位的,而堆表中,非聚集索引是根据数据行号来定位的。这将有很大的性能区别,前者的性能大大优于后者。所以,建立合适的聚集索引,是非常必要的。一个好的建议是,使用小字段的且值唯一的列来建立索引,而且***是单列,可以是代理键。因为如果字段太大太多,用来进行排序的开销将会很大,得不偿失;如果列值不唯一,数据库会为该重复值附加4字节的信息来标识重复值,增加了不必要的开销。

通常,我们在创建表的时候会指定主键,如果不显式指定索引类型的话,将默认创建聚集索引。比如:add constraint pk_tbl primary key (sid),将创建以sid为序的聚集索引。可以显式指定主键上的索引类型,比如,add constraint pk_tbl primary key nonclustered (sid),将创建一个非聚集索引的主键。所以,在创建主键的时候,一定得小心了,有多主键的情况,要注意显式指定索引类型。

索引能大幅度提高查询和排序性能,但是,在插入,删除,以及修改了主键的操作中,是需要维护索引顺序的。如果一张频繁变更的表,是不宜建立过多的索引的,索引带来的负面性能影响,将会得不偿失。

索引优化,是一个很考究的事情,它需要找到一个平衡点。

一般来说,有以下几个建议来创建合适的索引:

1.超过300行的数据表要创建索引(无视掉)。

2.聚集索引字段不能过多,***是单字段,而且列值唯一。

3.对于数据字段特别多的表,而且这些字段有很多出现在where中,不宜在每一个字段上建立单独的索引,而是创建组合索引。组合索引中,列的顺序是很讲究的,越是选择性大而且唯一的列要放在前面,这对查询优化器优化有很大的帮助。不宜在那些大量重复的列值上建立索引,比如在一个true,false的列上建索引,是毫无意义的。

4.如果查询中,查询的字段不多,可以考虑建立覆盖索引,将字段都包含在索引里,可以仅仅访问索引就能查询到所有数据,而不用表扫描。

关于数据库查询性能调优和索引优化的知识就介绍到这里了,希望本次的介绍能够对您有所帮助。

【编辑推荐】

  1. SQL Server链接服务器访问DB2设置步骤图解
  2. 初学SQL Server存储过程的一些简单操作示例
  3. T-SQL查询中基于列的逻辑表达式的查询详解
  4. SQL Server实践性练习之创建库表及条件查询
  5. SQL Server中通过设置SET NOCOUNT来优化存储过程
责任编辑:赵鹏 来源: CSDN博客
相关推荐

2023-04-03 10:25:00

数据库性能调优

2010-05-04 17:08:24

Oracle数据库

2023-10-04 11:16:03

数据库MySQL

2011-07-08 16:02:54

HBase

2023-11-10 09:25:36

Oracle数据库

2011-04-18 13:12:01

数据库索引

2011-04-18 13:23:46

数据库查询

2013-05-21 10:06:11

数据库查询优化

2010-04-07 13:32:39

Oracle调优

2021-01-31 17:50:41

数据库查询程序员

2016-12-12 13:07:57

数据库优化SQL

2019-08-13 08:32:14

MySQL数据库性能调优

2023-07-12 08:55:16

PawSQL数据库

2018-06-07 08:54:01

MySQL性能优化索引

2010-12-10 10:17:21

关系型数据库

2022-05-10 10:02:51

openGauss性能调优数据库

2011-04-25 09:12:47

LinuxIO数据库

2011-03-10 14:40:50

2019-07-08 14:05:53

数据库JVMSQL

2023-09-11 08:38:38

Oracle数据库
点赞
收藏

51CTO技术栈公众号