数据库索引（索引的优缺点、创建原则、B树与B+树、聚簇索引与非聚簇索引）

晓之木初

已于 2024-06-10 16:40:49 修改

阅读量1k

点赞数

分类专栏：数据库文章标签：数据库 b树

于 2019-10-06 19:34:49 首次发布

本文链接：https://blog.csdn.net/u014454538/article/details/88917492

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1. 数据库索引的相关知识

（1）索引的定义及特点

考虑如下情况，假设数据库中一个表有 $10^6$ 条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描。最坏的情况下，如果所有数据页都不在内存，需要读取 $10^4$ 个页面。如果这 $10^4$ 个页面在磁盘上随机分布，需要进行 $10^4$ 方次I/O。假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。
如果对之建立B-Tree索引，则只需要进行 $log100(10^6)$ =3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。

索引定义： 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。即索引的本质：索引是数据结构。
数据库索引是用于提高数据库表的数据访问速度的。具有以下特点：

避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和数据页，而不是查询所有数据页。而且对于非聚集索引，有时不需要访问数据页即可得到数据。
聚集索引可以避免数据插入操作，集中于表的最后一个数据页面。
在某些情况下，索引可以避免排序操作。

索引虽然提高了数据的查询速度，但是为表设置索引要付出代价的：

增加了数据库的存储空间
在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

（2）索引的优缺点

① 优点——创建索引可以大大提高系统的性能

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

② 缺点——创建索引可以消耗存储空间，减慢数据库写入速度

增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

③ 索引的创建原则

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

④ 不适合索引的列

一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

（3）常见的查询算法

索引是数据结构和算法的结合，常见的查询算法有：

① 顺序查找

最基本的查询算法当然是顺序查找（linear search），也就是对比每个元素的方法，不过这种算法在数据量很大时效率是极低的。
数据结构：有序或无序队列

复杂度：O(n)

//顺序查找
int SequenceSearch(int a[], int value, int n)
{
    int i;
    for(i=0; i<n; i++)
        if(a[i]==value)
            return i;
    return -1;
}

② 二分查找（binary search）

比顺序查找更快的查询方法应该就是二分查找了，二分查找的原理是查找过程从数组的中间元素开始，如果中间元素正好是要查找的元素，则搜素过程结束；如果某一特定元素大于或者小于中间元素，则在数组大于或小于中间元素的那一半中查找，而且跟开始一样从中间元素开始比较。如果在某一步骤数组为空，则代表找不到。
数据结构：有序数组

复杂度：O( $l o g n$ )

//二分查找，递归版本
int BinarySearch2(int a[], int value, int low, int high)
{
    int mid = low+(high-low)/2;
    if(a[mid]==value)
        return mid;
    if(a[mid]>value)
        return BinarySearch2(a, value, low, mid-1);
    if(a[mid]<value)
        return BinarySearch2(a, value, mid+1, high);
}

③ 二叉排序树查找

二叉排序树的特点是：

若它的左子树不空，则左子树上所有结点的值均 小于等于 它的根结点的值；
若它的右子树不空，则右子树上所有结点的值均 大于等于 它的根结点的值；
它的左、右子树也分别为二叉排序树。

搜索的原理：
1. 若b是空树，则搜索失败；
2. 若x 等于 b的根节点的数据域之值，则查找成功；
3. 若x 小于 b的根节点的数据域之值，则查找左子树；
4. 若x 大于 b的根节点的数据域之值，则查找右子树。
数据结构：二叉排序树
时间复杂度： O( $log2^N$ )

④ 哈希散列法(哈希表)

其原理是首先根据key值和哈希函数创建一个哈希表（散列表），然后根据键值，通过散列函数，定位数据元素位置。
数据结构：哈希表
时间复杂度：几乎是O(1)，取决于产生冲突的多少。

⑤ 分块查找

分块查找又称索引顺序查找，它是顺序查找的一种改进方法。其算法思想是将n个数据元素按块有序划分为m块（m ≤ n）。每一块中的结点不必有序，但块与块之间必须按块有序；即第1块中任一元素的关键字都必须小于第2块中任一元素的关键字；而第2块中任一元素又都必须小于第3块中的任一元素，依次类推。
算法流程：
（1）先选取各块中的最大关键字构成一个索引表；
（2）查找分两个部分：先对索引表进行二分查找或顺序查找，以确定待查记录在哪一块中；然后，在已确定的块中用顺序法进行查找。
这种搜索算法每一次比较都使搜索范围缩小一半。它们的查询速度就有了很大的提升，复杂度为O(√n)。

总结： 每种查找算法都只能应用于特定的数据结构之上。例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）。所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

（4）二叉排序树实例

在这里插入图片描述

一种可能的索引方式： 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O( $log2^N$ )的复杂度内获取到相应数据。

2. B树和B+树

① 关于B树：数据库索引一二事（一）–B树结构

在这里插入图片描述

基于上述图片的层级结构，如果我们需要查询数据是29的数据时，发生了些什么呢？

1.找到磁盘块1，加载到内存中（IO一次）。

2.查到在磁盘块3上，加载磁盘块3到内存（IO一次）。

3.查到在磁盘块8上，加载到内存中（IO一次）。
总共3次IO操作就查询到了数据，如果没有这个结构，那么最差的情况需要多少次呢？估计需要O（n）次IO操作吧，这就是B树结构的好处，查询次数是指数级别的降低。

② 关于B+树：数据库索引一二事（二）–B+树结构

B树和B+树的差别：

（1）叶子节点的不同：

B+树的叶子节点包含全部关键字信息，以及这些关键字的指针，而且叶子节点本身按照大小顺序链接。
B树的叶子节点只包含当前节点的信息，没有全部的信息，而且叶子节点也没有按照顺序链接。

（2）非叶子节点：

B+树的非叶子节点可以看做是索引部分，只存储最大或者最小关键字，无法获得具体的数据。
B树的非叶子节点包含了全部信息，可以获得全部数据。
在这里插入图片描述

③ 为什么B+树比B树更适合做文件索引？（数据库索引采用B+树的主要原因是什么？）

B+树的磁盘读写代价更低。 B+树的内部结点并没有指向关键字具体信息的指针，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多。相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素。
B+树的查询效率更加稳定。
① B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在。其性能等价于在关键字全集内做一次二分查找。
② 而在B+树中，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路。所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
B+树的数据遍历更加方便。
① 不同于B树只适合随机检索，B+树同时支持随机检索和顺序检索。
② B+树的叶子节点使用指针顺序连接在一起，只需要遍历叶子节点就可以实现整棵树的遍历。
③ 而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

3. 常见问题

（1）数据表为什么会使用主键？

我们平时建表的时候都会为表加上主键，在某些关系数据库中，如果建表时不指定主键，数据库会拒绝建表的语句执行。事实上，一个加了主键的表，并不能被称之为「表」。
一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐，跟我认知中的「表」很接近。
如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构。换句话说，就是整个表就变成了一个索引。
一定要记住：加了主键以后，整个表变成了一个索引，也就是所谓的「聚簇索引」。这就是为什么一个表只能有一个主键， 一个表只能有一个「聚簇索引索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。
上图就是带有主键的表（聚簇索引）的结构图。其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段，最下面部分是真正表中的数据。
```
select * from table where id = 1256;
```
首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。这里不讲解平衡树的运行细节，
但是从上图能看出，树一共有三层，从根节点至叶节点只需要经过三次查找就能得到结果。如下图
查找次数：n是记录总数，底数是树的分叉数，结果就是树的层次数。当有一亿条数据的时候，用计算公式表示就是 $log10^N$ ，N表示记录数：100000000。结果就是查找次数，这里的结果从亿降到了个位数。利用索引会使数据库查询有惊人的性能提升。
$查找次数=log树的分叉树^{记录总数}$

（2）聚簇索引(Clustered Indexes)——InnoDB

① 什么是聚簇索引？

聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。
因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB（MySQL的支持）。

② InnoDB的特点一： InnoDB的数据文件本身就是索引文件。

在InnoDB中，使用B+Tree作为索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
在InnoDB里，叶子节点data域保存了完整的数据记录。而MyISAM索引文件和数据文件是分离的，索引文件（叶子节点）仅保存数据记录的地址。
注意： 索引的key就是数据表的主键
聚簇索引示意图：

在这里插入图片描述

③ InnoDB的特点二： InnoDB的辅助索引data域存储相应记录主键的值而不是地址。

InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

（3）非聚簇索引(Nonclustered Indexes)——MyISAM

① MyISAM的主索引

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意，可以看出MyISAM的索引文件仅仅保存数据记录的地址。

② MyISAM的辅助索引

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

（4）InnoDB和MyISAM的区别

应用场景：

如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
MyISAM管理非事务表，它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择

存储结构：

都是用B+树，但是InnoDB的叶子节点存储的是完整的数据，MyISAM的叶子节点存储的是数据的地址。
非聚簇索引和聚簇索引的区别在于: 通过聚簇索引可以查到需要查找的数据，而通过非聚簇索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据，如下图：

深入浅出数据库索引原理
 数据库索引原理及优化
 数据库索引的实现原理（面试问题：请说出数据库索引实现原理）
数据库索引一二事（三）–索引的底层结构

晓之木初

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
1
评论
数据库索引（索引的优缺点、创建原则、B树与B+树、聚簇索引与非聚簇索引）

1. 数据库索引的相关知识（1）索引的定义及特点考虑如下情况，假设数据库中一个表有10610^6106条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描。最坏的情况下，如果所有数据页都不在内存，需要读取10410^4104个页面。如果这10410^4104个页面在磁盘上随机分布，需要进行10410^4104方次I/O。假设磁盘每次I/O时间为10ms(...
复制链接

扫一扫

专栏目录