深入理解数据库索引

最新推荐文章于 2024-09-09 16:07:55 发布

Java架构何哥

最新推荐文章于 2024-09-09 16:07:55 发布

阅读量3k

点赞数 12

分类专栏： # MySQL 文章标签：数据库索引原理数据库索引优缺点数据库索引存储结构索引的定义

本文链接：https://blog.csdn.net/CSDN2497242041/article/details/87147659

版权

MySQL 专栏收录该内容

19 篇文章 9 订阅

订阅专栏

前言：数据库和数据库索引这两个东西是在服务器端开发领域应用最为广泛的两个概念，熟练使用数据库和数据库索引是后端开发人员在行业内生存的必备技能。数据库索引是用来提高数据库表的数据查询速度的。

一、索引出现背景

在数据库系统的使用过程当中，数据的查询是使用最频繁的一种数据操作。最基本的查询算法当然是顺序查找（linear search），遍历表然后逐行匹配行值是否等于待查找的关键字，其时间复杂度为O(n)。但时间复杂度为O(n)的算法规模小的表，负载轻的数据库，也能有好的性能。但是数据增大的时候，时间复杂度为O(n)的算法显然是糟糕的，性能就很快下降了。

好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

二、数据库索引定义和意义

2.1、索引定义

数据库索引：是对数据库表中要查询的字段建立索引其实就是把该字段按照一定的方式排序的结构。

建立的索引只对该字段有用。如果查询的字段改变，那么这个索引也就无效。与在表中搜索所有的行相比，索引用指针指向存储在表中指定列的数据值，然后根据指定的次序排列这些指针，有助于更快地获取信息。通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引将占用磁盘空间，并且影响数据更新的速度。但是在多数情况下，索引所带来的数据检索速度优势大大超过它的不足之处。

2.2、索引意义

数据库索引是用于提高数据库表的数据查询速度的。

数据库索引的特点：

a）避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和数据页，而不是查询所有数据页。而且对于非聚集索引，有时不需要访问数据页即可得到数据。

b）聚集索引可以避免数据插入操作，集中于表的最后一个数据页面。

c）在某些情况下，索引可以避免排序操作。

三、数据库索引创建方法

创建索引有多种方法，这些方法包括直接创建索引的方法和间接创建索引的方法。

直接创建索引，例如使用CREATE INDEX语句或者使用创建索引向导。

间接创建索引，例如在表中定义主键约束或者唯一性键约束时，同时也创建了索引。

虽然，这两种方法都可以创建索引，但是，它们创建索引的具体内容是有区别的:

使用CREATE INDEX语句或者使用创建索引向导来创建索引，这是最基本的索引创建方式，并且这种方法最具有柔性，可以定制创建出符合自己需要的索引。在使用这种方式创建索引时，可以使用许多选项，例如指定数据页的充满度、进行排序、整理统计信息等，这样可以优化索引。使用这种方法，可以指定索引的类型、唯一性和复合性，也就是说，既可以创建聚簇索引，也可以创建非聚簇索引，既可以在一个列上创建索引，也可以在两个或者两个以上的列上创建索引。
通过定义主键约束或者唯一性键约束，也可以间接创建索引。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚簇索引。同样，在创建唯一性键约束时，也同时创建了索引，这种索引则是唯一性的非聚簇索引。因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。
当在表上定义主键或者唯一性键约束时，如果表中已经有了使用 CREATE INDEX语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。

四、数据库索引提高查询速度原理

数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

索引就像书的目录，通过书的目录就准确的定位到了书籍具体的内容。通过目录查找书的内容自然是要比一页一页的翻书找来的快，同样数据库使用索引后，通过索引定位到数据比直接一条一条的查询来的快。

为什么索引会增加查询速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。

那么在任何时候都应该加索引么？这里有几个反例：

如果每次都需要取到所有表记录，无论如何都必须进行全表扫描了，那么是否加索引也没有意义了。
对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义。
对于记录比较少的表，增加索引不会带来速度的优化反而浪费了存储空间，因为索引是需要存储空间的，而且有个致命缺点是对于update/insert/delete的每次执行，字段的索引都必须重新计算更新。所以并不是任何情况下都改建立索引的。

注意：表记录太少，不适合建立索引

如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。

五、数据库索引的种类

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，将数据存储与索引放到了一块，找到索引也就找到了数据；而非聚簇索引就不一样了，将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。（提示：尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。）

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

主键索引

数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别，如字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据

索引列

可以基于数据库表中的单列或多列创建索引。多列索引可以区分其中一列可能有相同值的行。如果经常同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。例如，如果经常在同一查询中为姓和名两列设置判据，那么在这两列上创建多列索引将很有意义。

检查查询的WHERE和JOIN子句。在任一子句中包括的每一列都是索引可以选择的对象。对新索引进行试验以检查它对运行查询性能的影响。考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。

检查某列中唯一数据值的数量，并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型。

六、数据库索引优缺点

6.1、索引优点

建立数据库索引的可以加快对表中记录的查找或排序。通过建立索引可以极大地提高在数据库中获取所需信息的速度，同时还能提高服务器处理相关搜索请求的效率

创建索引可以大大提高系统的性能：

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

6.2、索引缺点

既然创建索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？

因为为表设置索引要付出代价的：

1）在数据库中创建的索引需要占用一定的物理存储空间，如果要建立聚簇索引，那么需要的空间就会更大；

2）在数据库建立过程中需花费较多的时间去建立并维护索引，这种时间随着数据量的增加而增加；

3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

七、数据库索引的存储结构

数据库索引的存储结构就是B+树。

如图所示，在B+Tree的每个叶子节点有一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。B+树做这个优化的目的是为了提高区间访问的性能，例如图中如果要查询key为从10到65的所有数据记录，当找到10后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

1.n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

2.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字

总结：事物都是有两面的，索引能让数据库查询数据的速度上升，而使写入数据的速度下降，原因很简单的，因为平衡树这个结构必须一直维持在一个正确的状态，增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构，因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。