数据库的索引简介

本文讨论的索引以InnoDB为例。如有错误欢迎批评指正。

1 什么是索引

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。[1]

形象化地说，一本《现代汉语词典》的目录就是这本书的索引。
读者根据目录项的名称和页码可以快速查找到所需的内容。
在实体字典中，目录单独成页的，也需要纸张。
一本《现代汉语词典》可以有多个目录，比如根据拼音构成的拼音索引，根据部首的部首索引。而根据不同的索引可以查到同一个字。

2 为什么用索引

在上文提到，书的目录可以方便读者快速查找到所需的内容。即索引可以提高检索表的速度。

  索引结构的优缺点

• 优点：
  （1）提高查询速度
  （2）加速表和表之间的连接
• 缺点：
  （1）占用磁盘空间
  （2）降低表保存的速度
           这也是由于索引需要额外的物理存储造成的。每次对表进行修改（比如 DELETE、UPDATE 以及 INSERT 操作）后，若有索引，则索引也需要跟着被自动修改，相对于没有索引的表，就增加了表最终被保存所需要的时间。
           所以被选作索引的字段要对记录有区分度，索引中的值不能有较大重复或者不能重复。

3 索引的使用

（1）索引的类型

唯一索引、主键索引和聚集索引

• 聚集索引，

聚集索引，是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同
一个表只能有一个聚集索引，因为一个表的物理顺序只有一种
如果某索引不是聚集索引，则表中的行物理顺序与索引顺序不匹配，
与非聚集索引相比，聚集索引有着更快的检索速度
聚集索引也称为聚簇索引（Clustered Index），聚类索引，簇集索引[2]

还是拿《现代汉语词典》举例，词典中每个被解释的汉字的出现顺序，是按照A,B,C,D,E……的顺序排列的，而拼音索引的顺序也是按照A,B,C,D,E……顺序编排的，所以，拼音索引就是词典的聚集索引。
聚集索引和非聚集索引的使用[3]：

• 唯一索引（unique）

索引值唯一，允许有空值，且允许多个空值
创建：在创建 UNIQUE 约束时，默认情况下将创建唯一非聚集索引[4]

• 主键索引

在某一字段被指定为主键（Primary Key)，之后，引擎自动为该表创建主键的索引。主键索引满足主键的性质，即唯一性和实体完整性约束（不能为空）

（2）字段和索引的关系

单列索引：一个索引只包含一个字段。一个表可以有多个单列索引。
组合索引：一个索引有多个字段
注意：
在一个字段上可以创建多个不同命索引。
为了提高检索的准确性，一般在能够对记录进行区分的字段上创建索引，而键就有区分记录的作用，所以又引出了——键和索引的关系

（3）键和索引的关系

键和索引都是基于字段的，
但是键是逻辑结构，不另外存储，
而索引是物理结构，需要另外占用空间存储。

• 主键和索引的关系

        （1）主键索引是创建主键的时候系统自动创建的索引，
        （2）主键要求不重复，不为空
        （3）主键属于索引，是索引的一种

（4）对索引的操作

    1）创建索引

• 建表是指定属性为索引

create table bookInfo
(
	id int not null auto_increment, 
	bookname varchar(50) not null,
	publisher varchar(20) not null,
	unique index index_id(id )
)

建表bookInfo时，指定字段id为索引，索引名称为index_id,并设置索引为唯一性索引。

• 事后指定某一或者某几个属性为索引
   ~alter table
  通过alter table的方式，添加表users中的username字段的索引

alter table users add index (username)

         ~create index
         通过create index的方式，在表users的id字段上创建名称为indexID的索引

create index indexID on users(id);

         通过create index的方式，在表users的id字段上创建名称为indexID的唯一性索引

create unique index indexID on users(id);

         通过create index的方式，在表Pserson的LastName字段上创建名称为PersonIndex的降序索引

CREATE INDEX PersonIndex ON Person (LastName DESC)

         通过create index的方式，在表Pserson的LastName和FirstName字段上创建名称为PersonIndex的组合索引

CREATE INDEX PersonIndex ON Person (LastName, FirstName)

         ~alter table add index和create index的区别
         
         alter table

• 一次可以创建多个索引

ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), 
ADD INDEX(LastName,FirstName);

• 可以不指定索引的名称，但必须指定对应属性的名称
• only ALTER TABLE supports the use of PRIMARY KEY.
  
  create table
• 必须指定索引名称
• 一次只能创建一个索引

    2）查看索引

查看users表中的所有索引

show index from users;

在创建表时，id字段被设置为主键，username和email字段被设置为唯一。

那这三个字段被自动创建索引，且都是唯一的（non_unique=0表示唯一）
在sql front总运行查看users表的语句，结果显示如下：

    3）删除索引

删除users表中的indexID索引

drop index indexID on users;

    4）修改已有索引

对于MySQL 5.7及以上版本,可以执行以下命令[5]：

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name

对于MySQL 5.7以前的版本，可以执行下面两个命令：

ALTER TABLE tbl_name DROP INDEX old_index_name
ALTER TABLE tbl_name ADD INDEX new_index_name(column_name)

4 索引的数据结构

SQL 优化的一个重要原则是减少磁盘 I/O 次数，磁盘 I/O 次数也是评价索引结构的优劣的指标之一.
二叉排序树的结构满足 左子树<父亲节点<右子。在二叉排序树上进行元素查找，查找的次数不超过树的高度h.
平衡二叉树，是在二叉排序树的基础上，每个父节点的左右子树的高度差的绝对值不超过1。这一特点使得同样的元素序列，经过平衡处理后，树的高度h比二叉排序树降低，从而减少了元素查找时的比较次数。

啊，这个图自己画的有点问题。
B树和B+树都是平衡树，但是，是m叉树，不是二叉树、

（1）B树

基本概念

• 节点：即树的节点

• 关键字：每个节点内含有的元素（值按照升序排列）

• 阶m：在B树中，所有节点里孩子节点（子树）个数（分支树）的最大值。从查找效率考虑，m≥3. 子树的个数==子节点的个数 ==该节点的分支数

• 分支：一个节点的子节点（子树）的个数

数量关系

• 某节点的关键字个数+1=某节点的分支数
• 各节点的关键字个数取值区间[ ⌈m/2⌉-1,m-1]
• 对应的，各节点的分支个数 [ ⌈m/2⌉,m]

特点：

• 所有叶子节点在同一层，且叶节点不带任何信息。

示意图

B树相对于平衡二叉树有什么好处？

• 降低IO次数

B树的每个节点可以存储多个关键字，每个节点的分支由2变为m(m≥3)，从而相对于平衡树，B树可以进一步降低整个树的高度h，从而降低查找的次数.

• 利用局部性原理

B树的每个节点中关键字的值是相近的。这也利用了程序的局部性原理，即时间上，程序在访问一个区域后，在近期该区域大概率会被再次访问；空间上，未来要访问的区域与当前访问的区域的地址相近。从而可以减少相邻时间查询的时间。

（2）B+树

InnoDB中默认索引的数据结构是B+树

• 数量关系：

各节点关键字个数=该节点的分支数

• 特点：

  - 所有非叶子节点的关键字值是其下一级节点关键字的最大值集合。
  - 所有叶节点包含树中的所有关键字，以及关键字指向的相应记录的指针。即B+树中的关键字是有重复的。
  - B+树的根节点被一个头指针指向。
  - B+树的叶子节点层，用链表串连起来，用一个头指针指向关键字最小的叶子节点。

• 示意图

• 在B+树(InnoDB)上查询：

t(id PK, name KEY, sex, flag);
表中有四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

select * from t where name=‘lisi’;

在以主键id作为聚集索引的索引树中，叶子节点存储记录 [9]。
会先通过 name 辅助索引定位到 B + 树的叶子节点得到 id=5，再通过聚集索引定位到行记录。

在B+树（MyISAM）上查询

叶子节点存储索引对应的记录地址。而记录单独保存。

B+树相对于B树的优点

• 降低IO次数

-由于B+树的特点1——所有非叶子节点的关键字值是其下一级节点关键字的最大值集合，也就是所有数据信息全部存储在叶子节点里，这样，整个树的每个非叶子节点不再像B树那样存储指向的记录，在节点大小相同的情况下，节点余出来的空间就可以存更多的关键字，又一次降低了树的高度，减少了查找元素的次数，降低了IO次数，提高了效率。

• 查询次数稳定

B+树的查询效率是非常稳定的，因为所有信息都存储在了叶子节点里面，从根节点到所有叶子节点的路径是相同的。

• 适用于范围查询

另外，B+树的各个叶子节点串联成单链表，在进行范围/区间查询时，找到叶节点的下界后，可以顺着 链表找到上界。

5 参考文献及拓展阅读

参考文献：

[1] 索引-百度百科
https://baike.baidu.com/item/%E7%B4%A2%E5%BC%95/5716853?fr=aladdin
[2] 聚集索引 -百度百科https://baike.baidu.com/item/%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95/11041381?fr=aladdin
[3]聚集索引和非聚集索引（整理）-何时使用聚集索引或非聚集索引
https://www.cnblogs.com/aspnethot/articles/1504082.html
[4] 创建聚集索引-Microsoft
https://docs.microsoft.com/zh-cn/sql/relational-databases/indexes/create-clustered-indexes?view=sql-server-ver15#Implementations
[5] 修改索引名称（mysql）
https://www.cnblogs.com/xphdbky/p/7054395.html
[6] 数据库索引背后的数据结构
https://www.cnblogs.com/songwenjie/p/9414960.html
[7] 是什么影响了数据库索引选型？
https://www.cnblogs.com/songwenjie/p/9435249.html
[8] 《数据结构教程（第四版）》李春葆等 p276
[9] 1 分钟了解 MyISAM 与 InnoDB 的索引差异
https://blog.csdn.net/andong154564667/article/details/82180067

拓展阅读：
[10] 不同的存储引擎支持的索引类型
https://blog.csdn.net/u013915286/article/details/105379400
[11] 数据库—四种存储引擎
https://blog.csdn.net/weixin_30955617/article/details/94844335