文章目录

  • 索引分类
  • 普通索引
  • 唯一索引
  • 主键索引
  • 其它的索引分类
  • 一级索引和二级索引
  • 聚簇索引
  • 存储索引
  • 索引和 B+ 树
  • 回表查询和覆盖索引
  • 索引的创建和使用原则
  • 索引并不是越多越好
  • 避免更新聚簇索引数据列
  • 经常更新的表就避免对其进行过多的索引
  • 数据量小的表最好不要使用索引
  • 避免在不同值少的列上加索引
  • 根据业务需求建立索引
  • 数据页（Page）
  • B 树（B-Tree）
  • B+树（B+Tree）
  • 页的数量
  • 假设 B+ 树的深度为 2
  • 假设 B+ 树的深度为 3
  • 关于数据库表容量



mysql分类相加 mysql 分类_数据库

我们通常在使用 MySQL（特别是 InnoDB 引擎时），会涉及到的索引主要有单列索引（其中包括普通索引、唯一索引、主键索引）和组合索引。另外，鼎鼎有名的全文索引 InnoDB 引擎不支持，只能在 MyISAM 引擎中使用（类似的还有空间索引）。

索引分类

普通索引

MySQL 中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，普通索引的建立纯粹为了查询数据更快一点。

创建普通索引，可以在建表时使用 index 或 key 关键字：

-- SQL 语句
CREATE TABLE department
(
  `id`       BIGINT AUTO_INCREMENT COMMENT '部门ID',
  `name`     VARCHAR(15) COMMENT '部门名称',
  `location` VARCHAR(13) COMMENT '部门所在地',
  INDEX(name),
  PRIMARY KEY (`id`)
) COMMENT '部门信息表';

唯一索引

唯一索引在普通索引的基础上多了一个要求：索引列中的值必须是唯一的。

创建唯一索引，可以在建表时使用 UNIQUE INDEX 关键字：

-- SQL 语句
CREATE TABLE department
(  
  `id`       BIGINT AUTO_INCREMENT COMMENT '部门ID',  
  `name`     VARCHAR(15) COMMENT '部门名称',  
  `location` VARCHAR(13) COMMENT '部门所在地',  
  UNIQUE INDEX (name),  
  PRIMARY KEY (`id`)  
) COMMENT '部门信息表';

唯一索引有一个反常识的地方：它允许为空值，在唯一索引标识的字段中可以出现多个 NULL 。

主键索引

主键索引是大家很熟悉的索引了，它要求非空且唯一。

创建唯一索引，可以在建表时使用 PRIMARY KEY 关键字。

主键索引也被称为一级索引，它的特殊性在于一张表中一定会有主键索引，即便你没有指定！

其它的索引分类

一级索引和二级索引

有时候你会听到、看到一级索引和二级索引的概念。这不是新的索引，而是另一套叫法。

在有的书籍和资料中，会将普通索引称作二级索引，与之对应的是将主键索引叫做一级索引。

于此同时，唯一索引就被称作唯一二级索引。

聚簇索引

聚簇索引（和非聚簇索引）也不是另一种新的索引，而是概念。

简单来说，一张数据库表有且仅有一个聚簇索引，而非聚簇索引可以有多个。通常主键索引就是聚簇索引。
聚簇索引的特殊之处在于：表中的数据在磁盘上的先后排布顺序，和聚簇索引中的索引顺序是一致的。
简单来说，表中的数据在磁盘上的位置，id=1 和 id=2 的两条数据一定是紧挨着的，且有序的，即，id=2的一定是在 id=1和id=3 的中间，而 id=3 又一定是在 id=2 和 id=4 的中间的。

存储索引

有时你会听到存储索引的概念，在这个概念下有 B+ 树索引和 Hash 索引。
此索引非彼索引。这里的索引换个说法指的是数据库引擎的数据存储结构。InnoDB 引擎支持以 B+ 树的方式和 Hash 的方式去“组织”磁盘上的数据。

索引和 B+ 树

每当你创建一个索引时，InnoDB 引擎就会在磁盘上创建一个棵 B+ 树。
下图就是对表的 name 字段创建索引后，InnoDB 所创建的一颗 B+ 树（这个图画的有一点点问题，但是问题不大）。

mysql分类相加 mysql 分类_子节点_02

注意
在索引树的叶子节点所指向的磁盘上的一个 Page 中，记录的是 id 和索引列的数据。这是对应数据库中的一条记录的“不完整”记录。

另外，索引 B+ 树的叶子节点（Page）还会记录“完整”的表中数据在磁盘上的哪个 Page 上。

如果你使用了联合索引，那么索引树的叶子节点（Page）中记录的树就会都一点点。

下图就是对表的 name、age 和 sex 创建联合索引之后，InnoDB 所创建的 B+ 树。

mysql分类相加 mysql 分类_子节点_03

回表查询和覆盖索引

在上一章节中，我们看到，当你为表的某列创建索引之后，InnoDB 引擎所创建的索引 B+ 树的叶子节点（Page）中记录了表中数据的不完整信息（id + 索引列数据），以及完整信息所在磁盘的 Page 地址。

如果，你执行的是 select * 这样的查询，导致在索引 B+ 树中拿不到你所要的全部的数据，那么 MySQL 需要再做一次查询，根据索引叶子节点中所记录的完整的数据的所在位置，再做一次磁盘 IO 操作，去拿完整的数据。这个过程就叫回表查询。

如果，你执行的碰巧就是 select id, name 这样的查询，在索引 B+ 树（的叶子节点中）中正好有你想要查询的这部分字段信息，那么 MySQL 就没有必要再去查找完整的信息了。这个过程就叫覆盖索引。

索引的创建和使用原则

索引并不是越多越好

索引的创建和使用体现了 2 个性能提升准则:

  • 空间换时间；
  • 牺牲写操作，提升读操作。

因此，索引越多，磁盘空间占用越大，写操作速度越慢。所以，有多少索引字段，是一个需要权衡的问题，不是无脑加加加。

避免更新聚簇索引数据列

考虑到聚簇索引通常就是主键索引，所以这个准则也就是说不要去改动数据的 ID 。
聚簇索引直接影响着数据在磁盘上的排布顺序，你改动了数据的主键 ID，那么就意味着在磁盘上这条数据要位置，这就带来了额外的磁盘 IO 操作。

经常更新的表就避免对其进行过多的索引

前面提到过，索引是牺牲写操作时间来换取读操作时间的提升。考虑到大多数情况下读写操作的频次比例有很大的悬殊，所以通常这种牺牲是值得的。
但是，万一有个写操作功能是很频繁的，那么就不要牺牲它了。

数据量小的表最好不要使用索引

如果一张表的数据量不大，几千、几万的级别，那么进行全表扫描和通过索引查找之间可能根本就没有太大的区别。

避免在不同值少的列上加索引

因为这种情况下，索引会失效。
比如，你对『性别』列加索引，但是这一列的值的可能最多也就只有两三种。回头你在 SQL 语句中用性别作为条件查询，MySQL 真正执行的时候也不会去使用性别索引。
在经常做为查询条件的，且区别度大的列/字段上建索引：身份证号、电话号、家庭地址 … 。

根据业务需求建立索引

不要凭空想象去添加索引，要根据你的 SQL 语句中使用到了哪些字段作为查询条件来创建索引。

数据页（Page）

InnoDB 存储引擎中有页（Page）的概念，页是 InnoDB 引擎进行磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16KB ，可通过参数 innodb_page_size 将页的大小设置为 4K 、8K 、16K，在 MySQL 中可通过如下命令查看页的大小：

-- SQL 语句
show variables like 'innodb_page_size';

InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，位于同一个页中的数据会被一次性读取出来，而不是需要什么取什么。

在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。

B 树（B-Tree）

B 树是为了磁盘（或其它直接存取的辅助存储设备）而设计的一种平衡搜索树。B 树类似于红黑树，确切地说是类似于 234 树。

如果你对 红黑树/234树 很熟悉的话，下面的图很容易理解。

mysql分类相加 mysql 分类_数据_04

在 B 树中，叶子节点中只有数据，而非叶子节点中除了有数据，还有指向其子节点的指针。
模拟查找 id=29 的数据的过程：

#

操作

备注

1

根据根节点找到磁盘块1，读入内存。

磁盘 I/O 操作第 1 次

2

比较 id 发现 29 在区间（17,35），找到磁盘块1的指针P2。

3

根据 P2 指针找到磁盘块3，读入内存。

磁盘 I/O 操作第 2 次

4

比较 id 发现 29 在区间（26,30），找到磁盘块3的指针P2。

5

根据 P2 指针找到磁盘块8，读入内存。

磁盘 I/O 操作第 3 次

6

在磁盘块8中的关键字列表中找到id=29的数据。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

提示
B 树也叫 B- 树，之所以这么叫是为了和 B+ 树中相呼应。

B+树（B+Tree）

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

== 提示==
B 树的中间节点中即存储数据又存储向下的指针，在一个中间节点中，数据“挤占了”指针的空间，进而导致了一个中间节点中记录的向下的指针的数量不多。

宏观上看，这就导致了 B 树会变高。变高就意味着如果我们访问的数据在叶子节点上，那么就需要更多的“访问”次数。

B+ 树在 B 树的基础上“激进”了一把：所有的 非叶子节点/中间节点 中只记录 key 和向下的指针，不记录数据内容。这样可以大大加大每个节点存储的“向下的指针”的数量，降低 B+ 树的高度。

B+ 树相对于 B 树有几点不同：

  • 非叶子节点只存储键值信息。
  • 所有叶子节点之间都有一个链指针。
  • 数据记录都存放在叶子节点中。

将上一节中的 B 树优化，由于 B+ 树的非叶子节点只存储键值信息，假设每个磁盘块能存储 4 个键值及指针信息，则变成 B+ 树后其结构如下图所示：

mysql分类相加 mysql 分类_mysql_05

提示
上图的横向的、串联其所有 Page 的链表指针是进一步的优化，并非 B+ 树的核心概念。

这样优化的目的是为了支持范围查找和分页查询。

页的数量

我们可以基于以下的信息进行以下推算和预估计：

  • InnoDB 存储引擎中页的大小默认为 16KB ；
  • bigint 类型在 InnoDB 存储引擎中占 8 字节大小；
  • 指针大小在 InnoDB 存储引擎中占 6 字节大小；
  • 以 id + 指针 组成的键值对就占 14 字节大小。

假设一张表中的每条数据占 1K 大小，那么，一个磁盘上的 Page 中能记录的数据就有 16 条（实际上由于 Page 中还要记录一些其它的数据，所以真实情况下并没有 16 条那么多）。

基于上述信息，我们可以进行以下预估。

假设 B+ 树的深度为 2

因为 B+ 树的深度为 2 ，那么意味着其它节点都是叶子页。那么，根节点中能记录的 id+指针 键值对总共有：16384 / 14 = 1170 个。即，最多可以有 1170 个 Page 来存储数据。

由于一个 Page 中能记录 16 数据，那么，1170 个页中，总共最多可以有 1170 x 16 = 18720 条数据，大约 18 万条（不到 19 万条）。

也就是说，在 B+ 树深度为 2 的这个前提条件下，这张表中差不多可以有 18 万条数据，

假设 B+ 树的深度为 3

如果 B+ 树深度为 3 ，那么也就意味着它有 2 层中间节点。那么，根节点中能记录的中间节点有：16384 / 14 = 1170 个。同样，每个根节点中记录的叶子节点又有 1170 个，那么深度为 3 的 B+ 树总共会有 1170 x 1170 = 1368900 个叶子节点。

由于一个 Page 中能记录 16 条数据，那么 1368900 个页中，总共最多可以有 1368900 x 16 = 21902400 条数据，大约 2100 万条（不到 2200 万条）。

也就是说，在 B+ 树深度为 3 的这个前提条件下，这张表中差不多可以有 2000 万条数据，

关于数据库表容量

表面上看起来，2000W 条数据并不少了，但是我们上述的估算是基于表中一条数据只有 1k 大小进行的，而实际情况中一条数据占 1K 大小这个数量级很小。
在 MySQL 可以通过如下命令查询到表的行数据平均长度：

-- SQL 语句
SHOW TABLE STATUS LIKE '表名';

其中，有一项为 Avg_row_length ，它的值就是表的行数据平均长度。
这也是通常所说的，MySQL 单表数据量到千万是个『坎』的原因。因为在百万级情况下，表背后的 B+ 树的深度大概率不会超过 3 层，如果数据量在多，B+ 树的层次会再多加一层，这就意味着查询操作的时间会增加。
因此在数据库中，B+ 树的高度一般都在 2~4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作。
如果一张表的数据量多到要突破 4 层了，那么就必须要想办法优化表结构了。