数据库的索引及其原理

最新推荐文章于 2024-06-11 14:07:28 发布

温文吻问h

最新推荐文章于 2024-06-11 14:07:28 发布

阅读量373

点赞数

分类专栏：数据库

原文链接：https://baike.baidu.com/item/%E7%B4%A2%E5%BC%95/5716853#1

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

索引的创建语句非常简单，然鹅，会创建索引和能够恰到好处的使用索引并不是一回事。数据库在开发过程中用的很多，索引也用的很多，但一直都是知其然而不知其所以然。在这篇文章里面整理一下索引的方方面面。

1. 数据库为什么需要索引？什么是索引？

如果没有索引我们查询数据库的时候就需要全表扫描。在数据量比较大的情况下效率将会非常低。通过索引能从表中直接找到一个特定的记录而不必扫描整张表。

索引是为了加速对表中数据的检索而创建的一种数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。SQL标准中没有涉及索引，但商用关系数据库管理系统一般都支持索引机制，只是不同的关系数据库管理系统支持的索引类型不尽相同。

2.索引的优缺点

优点

大大加快数据的检索速度;
创建唯一性索引，保证数据库表中每一行数据的唯一性;
加速表和表之间的连接，在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

索引需要占物理空间。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

3.索引类型

根据数据库功能，可以在数据库中创建四种索引：普通索引、唯一索引、主键索引和聚集索引。

(1) 普通索引

最基本的索引类型，没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表)；
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表)；
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) )；

(2) 唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

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

对某个列建立UNIQUE索引后，插入新记录时，数据库管理系统会自动检查新纪录在该列上是否取了重复值，在CREATE TABLE 命令中的UNIQE约束将隐式创建UNIQUE索引。

创建唯一索引的几种方式：

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表); ；
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；

(3) 主键索引

简称为主索引，数据库表中一列或列组合（字段）的值唯一标识表中的每一行。该列称为表的主键。

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

提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键索引。

(4) 候选索引

与主索引一样要求字段值的唯一性，并决定了处理记录的顺序。在数据库和自由表中，可以为每个表建立多个候选索引。

(5) 聚合索引(clustered index):

innodb存储引擎表是索引组织表，表中数据按照主键顺序存放。其聚集索引就是按照每张表的主键顺序构造一颗B+树，其叶子结点中存放的就是整张表的行记录数据，这些叶子节点称为数据页。

聚集索引的存储并不是物理上连续的，而是逻辑上连续的，叶子结点间按照主键顺序排序，通过双向链表连接。多数情况下，查询优化器倾向于采用聚集索引，因为聚集索引能在叶子结点直接找到数据，并且因为定义了数据的逻辑顺序，能特别快的访问针对范围值的查询。

聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。由于表里的数据只能按照一颗B+树排序，因此一张表只能有一个聚簇索引。

在Innodb中，聚簇索引默认就是主键索引。如果没有主键，则按照下列规则来建聚簇索引:

没有主键时，会用一个非空并且唯一的索引列做为主键，成为此表的聚簇索引;
如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

由于主键使用了聚簇索引，如果主键是自增id，那么对应的数据也会相邻地存放在磁盘上，写入性能较高。如果是uuid等字符串形式，频繁的插入会使innodb频繁地移动磁盘块，写入性能就比较低了。

(6) 非聚集索引(也称为辅助索引)

在非聚集索引中，数据库表中记录的物理顺序与索引顺序不相同。一个表中只能有一个聚集索引，但表中的每一列都可以有自己的非聚集索引。非聚集索引的叶子节点不包含行记录的全部数据，叶子结点除了包含键值以外，每个叶子结点中的索引行还包含一个书签，该书签就是相应行的聚集索引键。

下图可以表示聚集索引和非聚集索引的关系

当通过辅助索引来寻找数据时，innodb存储引擎会通过辅助索引叶子节点获得指向主键索引的主键，既然后再通过主键索引找到完整的行记录。

例如在一棵高度为3的辅助索引树中查找数据，那需要对这颗辅助索引树进行3次IO找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次IO访问来得到最终的数据页。

创建的索引，如联合索引、唯一索引等，都属于非聚簇索引。

(7) 联合索引

联合索引是指对表上的多个列进行索引。

如图，联合索引(年龄, 姓氏,名字)，叶节点上data域存储的是三个关键字的数据。且是按照年龄、姓氏、名字的顺序排列的。因此，如果执行的是：

select * from STUDENT where 姓氏='李' and 名字='安';

或者：select * from STUDENT where 名字='安';

那么当执行查询的时候，是无法使用这个联合索引的。因为联合索引中是先根据年龄进行排序的。如果年龄没有先确定，直接对姓氏和名字进行查询的话，就相当于乱序查询一样，因此索引无法生效。因此查询是全表查询。

如果执行的是：select * from STUDENT where 年龄=1 and 姓氏='李';

那么当执行查询的时候，索引是能生效的，从图中很直观的看出，age=1的是第一个叶子节点的前6条记录，在age=1的前提下，姓氏=’李’的是前3条。因此最终查询出来的是这三条，从而能获取到对应记录的地址。

如果执行的是：select * from STUDENT where 年龄=1 and 姓氏='黄' and 名字='安';那么索引也是生效的。

而如果执行的是：select * from STUDENT where 年龄=1 and 名字='安';

那么，索引年龄部分能生效，名字部分不能生效。也就是说索引部分生效。

因此我对联合索引结构的理解就是B+Tree是按照第一个关键字进行索引，然后在叶子节点上按照第一个关键字、第二个关键字、第三个关键字…进行排序。

最左原则：而之所以会有最左原则，是因为联合索引的B+Tree是按照第一个关键字进行索引排列的。

https://www.2cto.com/database/201802/721844.html

(8) 覆盖索引

即从辅助索引中就可以得到查询所需要的所有字段值，而不需要查询聚集索引中的记录。覆盖索引的好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

例如有联合索引（age，name），如果如下：

select age，name from user where age=？

就能使用覆盖索引了。

覆盖索引的另一个好处是对于统计问题，例如：

select count(*) from user

innodb存储引擎并不会选择通过查询聚集索引来进行统计。由于user表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作。

如果对于name列做了索引，那么：

select id,name from user

也会使用覆盖索引，因为name在索引上，而id（主键）在其索引树的子节点上保存。

https://yq.aliyun.com/articles/692067

4.MySQL B+Tree索引和Hash索引的区别?

Hash索引和B+树索引的特点：

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;
B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问;

为什么不都用Hash索引而使用B+树索引？

Hash索引仅仅能满足"=","IN"和""查询，不能使用范围查询,因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样；
Hash索引无法被用来避免数据的排序操作，因为Hash值的大小关系并不一定和Hash运算前的键值完全一样；
Hash索引不能利用部分索引键查询，对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用；
Hash索引在任何时候都不能避免表扫描，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要回表查询数据；
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+树索引高。

补充：

1.MySQL中，只有HEAP/MEMORY引擎才显示支持Hash索引。

2.常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

B+树索引和哈希索引的明显区别是：

3.如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

4.如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
同理，哈希索引没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

5.哈希索引也不支持多列联合索引的最左匹配规则；

6.B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

7.在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。

5.操纵索引

(1) 维护和使用

数据库管理系统自动完成维护和自动选择是否使用索引以及使用哪些索引。

(2) 创建索引

SQL3没有提供创建索引的方法，各个数据库厂商均提供了创建索引的工具，其不尽相同。SQL语言使用CREATE INDEX 语句建立索引，其一般格式是：

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED] INDEX <索引名> ON <表名>(<列名>[ASC|DESC] [, <列名>[ASC|DESC]...])；
说明：与表一样，索引也需要有唯一的名字，且基于一个表来建立，可以根据表中的一列或者多列，当列的顺序都是升序默认可不必标出，当属性列有按照降序排列的，所有属性的升序降序都不要标明。例子：创建表时同时创建索引降序排序(sname 字段上普通索引降序）
```
create table tbl1 (
    id int unique, sname varchar(50),
    index tbl1_index_sname(sname desc)
);
```
UNIQUE——建立唯一索引。

CLUSTERED——建立聚集索引。

NONCLUSTERED——建立非聚集索引。

ASC——索引升序排序。

DESC——索引降序排序。

(3) 修改索引

对于已经建立的索引，如果需要对其重新命名，可以使用ALTER INDEX 语句。其一般格式为

ALTER INDEX <旧引索名字> RENAME TO<新引索名>

(4) 删除索引

当某个时期基本表中数据更新频繁或者某个索引不再需要时，需要删除部分索引。SQL语言使用DROP INDEX 语句删除索引，其一般格式是：DROP INDEX<索引名>

删除索引时，DBMS不仅在物理删除相关的索引数据，也会从数据字典删除有关该索引的描述。

6. 哪些列适合建立索引、哪些不适合建索引？

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

一般来说，应该在这些列上创建索引：

（1）在经常需要搜索的列上，可以加快搜索的速度；

（2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

（3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

（4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

（5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

（6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

对于有些列不应该创建索引：

（1）对于那些在查询中很少使用或者参考的列不应该创建索引。

这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

（2）对于那些只有很少数据值的列也不应该增加索引。

这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

（3）对于那些定义为text, image和bit数据类型的列不应该增加索引。

这是因为，这些列的数据量要么相当大，要么取值很少。

（4）当修改性能远远大于检索性能时，不应该创建索引。

这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

什么样的字段适合建索引

唯一、不为空、经常被查询的字段

7.B树和B+树的区别

B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。
B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)

8.为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

1.B+的磁盘读写代价更低

B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2.B+tree的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

联合索引在B+ 树上的存储方式及查找过程： https://juejin.im/post/6844904073955639304

温文吻问h

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据库的索引及其原理

索引的创建语句非常简单，然鹅，会创建索引和能够恰到好处的使用索引并不是一回事。数据库在开发过程中用的很多，索引也用的很多，但一直都是知其然而不知其所以然。在这篇文章里面整理一下索引的方方面面。1. 数据库为什么需要索引？什么是索引？如果没有索引我们查询数据库的时候就需要全表扫描。在数据量比较大的情况下效率将会非常低。通过索引能从表中直接找到一个特定的记录而不必扫描整张表。索引是为了加速...
复制链接

扫一扫