查询性能调优和索引优化那些事

时间:2022-05-26 21:15:40

下边的一篇关于数据库查询性能调优的笔记,有问题请大家一起指正。

注意:这篇文章,是以一个数据库开发人员的角度去写的,非专业DBA,仅供参考。

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

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

查询语句性能调优,下篇会继续讲。