【系统性能优化】（一）数据库索引

柔光的暖阳◎ 2022-08-09 15:57 191阅读 0赞

面对这个信息量越来越大的社会，我们的电脑硬盘也变的越来越大，各种企业级数据库的信息也越来越多，那么如何从存放大量的数据的数据库里快速查找我们想要的东西呢？  
  
       其中的一个方案就是合理的使用索引。  
       索引是各种关系数据库系统最常见的一种逻辑单元，是关系数据库系统举足轻重的重要组成部分，对于提高检索数据速度有着至关重要的作用，索引的原理是根据索引值得到行指针，然后快速定位到数据库记录..（如下图：）

![Center][]

创建索引是提高检索效率最有效的方法之一，索引把表中的逻辑值映射到安全的RowID，能快速定位数据的物理地址，可以大大加快数据库的查询速度，一个建有合理索引的数据库应用系统可能比一个没有建立索引的数据库应用系统效率高几十倍，但并不是索引越多越好，在那些经常需要修改的数据列上建立索引，将导致索引B树的不断重组，造成系统性能的下降和存储空间的浪费。对于一个大型表建立的索引，有时并不能改善数据查询速度，反而会影响整个数据库的性能。这主要是和SGA(system global area)的数据管理方式有关，Oracle在进行数据块高速缓存管理时，索引数据比普通数据具有更高的驻留权限，在进行空间竞争时，Oracle会先移出普通数据，对建有索引的大型表进行数据查询时，索引数据可能会用完所有的数据块缓存空间，Oracle不得不频繁地进行磁盘读写来获取数据，所以，在对一个大型表进行分区之后，还可以根据相应的分区建立分区索引。  
       Oracle提供了另一种方法来提高查询速度，就是聚簇(Cluster)。所谓聚簇，简单地说就是把几个表放在一起，按一定公共属性混合存放。聚簇根据共同码值将多个表的数据存储在同一个Oracle块中，这时检索一组Oracle块就同时得到两个表的数据，这样就可以减少需要存储的Oracle块，从而提高应用程序的性能。对于逻辑结构的优化，还应将表数据和索引数据分开表空间存储，分别使用独立的表空间。因为如果将表数据和索引数据放在一起，表数据的I/O操作和索引的I/O操作将产生影响系统性能的I/O竞争，降低系统的响应效率。将表数据和索引数据存放在不同的表空间中，并在物理层面将这两个表空间的数据文件放在不同的物理磁盘上，这样就可以避免这种竞争了。

常见索引介绍

1、B树索引   
       这是最常见的索引,几乎所有的关系型数据库系统都支持B树结构的索引,也是被最多使用的,其树结构与二叉树比较类似,根据行id快速定位到行。大部分数据库默认建立的索引就是这种索引。B树索引在检索高基数数据列(高基数列是指该列有很多不同的值,该列所有不同值的个数之和与该列所有值的个数之和的比成为列基数)时提供了比较好的性能，B树索引是基于二叉树的，由分支块和叶块组成。在树结构中，位于最底层的块成为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列(关键字)范围和另一索引快的地址。  
  
  
     2、聚集索引   
       没错,这是sqlserver里很重要的一个索引，也叫群集索引。聚集索引是相对于常规索引而言的，oracle也有类似的索引，不过叫聚簇索引，注意,虽然聚簇和聚集仅有一字之差，但是oracle的聚簇索引和sqlserver的聚集索引还是有很多的不同的，oracle的聚簇索引可以针对多表，根据多个表相同列的不同值，将相关数据聚集在周围。sqlserver聚集索引也有类似的意思，但是只能针对单表。在oracle里，聚簇”是oralce内部的一个对象，就像基本表，视图，触发器这些概念一样。聚簇索引就是对聚簇进行的索引。但在sqlserver里，聚集索引直接作用在表上，因此不可以将二者混淆。  
  
     3、非聚集索引  
       非聚集索引是一种典型的B树索引,每个叶块只包含两种数据,一种是索引项,一种是该索引项所在行的行指针,当查询的数据匹配该索引项数据的时候,将会取出对应的行指针,取得该行的数据.如果要根据键值从大型SQL Server 表提取具有良好选择性的少数几行，非聚集索引最有用。B树的底部或叶级包含组成该索引的列中的所有数据。当用非聚集索引检索表中与键值匹配的信息时，将搜索整个索引B树，直到在索引叶级找到一个与键值匹配的值。  
  
       在非聚集索引中，叶级节点仅包含参与索引的数据以及快速找到相关数据页上其它行数据的指针。最糟糕的情况是，从非聚集索引中获得的每一行都要求一个额外的不连续磁盘I/O才能检索行数据。最好的情况是，所需要的行有许多都位于相同的数据页，因此在提取每个数据页时可检索多行。  
  
    如果是聚集索引，索引的叶级节点是表的实际数据行。因此，检索表数据时不需要指针跳动。基于聚集索引的范围扫描执行情况很好，因为聚集索引的叶级（即表的所有行）在物理上按照组成聚集索引的列顺序排列在磁盘上。  
  
    4、覆盖索引

覆盖索引是非聚集索引的一个特例。覆盖索引的定义是在选择条件和WHERE谓词上均满足SQL查询的所有列的基础上建立的非聚集索引。覆盖索引可以节省大量的I/O，因此可极大地改善查询的性能。但是有必要在新建索引（以及与它相关的B树索引结构维护）所需要的代价和覆盖索引所带来的I/O性能增益之间进行权衡（权衡利弊，看得失）。如果覆盖索引对于SQL Server上经常运行的查询或查询组极其有利，那么创建覆盖索引是值得的。

5、位图索引  
       这个不是sqlserver的索引，它是oracle的。它不是B树结构的索引，位图索引相对于B-tree索引来说,它的存储结构是不一样的，通常在B-tree索引中，索引条目和行之间有一对一的关系。对于位图索引，一个索引条目使用一个位图同时指向许多行。这对于基本上只读的低基数(数据只有很少的几个截然不同的值)数据是合适的。比如说，一个person表，有个性别字段sex，Y代表男，N代表女，对于有几百万行数据的表来说，位图索引是一个非常好的选择。它可以迅速的扫描出来，而不用象对B树索引那样的查找。

总结：

“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。所以说，我们要建立一个“适当”的索引体系，特别是对聚合索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥。在实际的开发中，会遇到很多意想不到的情况，最好是多测试一些方案，找出哪种方案效率最高、最为有效。

[Center]: /images/20220731/1be8b54762794f43a7437be89eff69ce.png