索引的分类

从存储结构上划分

BTree 索引（B+tree，B-tree)
哈希索引
FULLINDEX 全文索引
RTree

索引是存储引擎快速找到记录的一种数据结构，例如 MyISAM 引擎和 Innodb 引擎都使用 B+ Tree 作为索引结构，但二者在底层实现还是有些不同的。

Innodb 索引和数据存储在同一个文件
MyISAM 索引文件和数据文件是分离的

从应用层次上来划分

外键索引 InnoDB 是 MySQL 目前唯一支持外键索引的内置引擎。

外键成本：外键每次修改数据时都要求在另一张表多执行一次查找，当然外键在相关数据删除和更新上比在应用中维护更高效。

从应用层次角度对索引进行分类

普通索引（INDEX）：最基本的索引
唯一索引（UNIQUE INDEX）：索引列的值必须唯一，但允许有空值
主键索引 (PRIMARY KEY)：是一种特殊的唯一索引，不允许有空值
单列索引
组合索引（联合索引）：联合索引最多只能包含16列
全文索引 (FULLTEXT INDEX)
空间索引（SPATIAL INDEX）

下面对上述索引进行必要的说明。

创建唯一索引（UNIQUE INDEX）的列值必须唯一，但是允许值为空。如果创建的唯一索引中包含多个字段，也就是复合索引，则索引中包含的多个字段的值的组合必须唯一。
主键索引是特殊类型的唯一索引，与唯一索引不同的是，主键索引不仅具有唯一性，而且不能为空，而唯一索引中的列的数据可能为空。
创建全文索引时，对列的数据类型有一定的限制，只能为定义为 CHAR，VARCHAR 和 TEXT 数据类型的列创建全文索引。全文索引不支持对列的局部进行索引。
MySQL 支持在 GEOMETRY 数据类型的字段上创建空间索引。

从表记录的排列顺序和索引的排列顺序是否一致来划分

聚簇索引（聚集索引，一级索引）：表记录的排列顺序和索引的排列顺序一致
非聚簇索引（非聚集索引，二级索引，普通索引）：表记录的排列顺序和索引的排列顺序不一致

聚集索引在叶子节点存储的是表中的数据，非聚集索引在叶子节点存储的是主键和索引列。

比较项	聚集索引	非聚集索引
别名	聚簇索引，一级索引	非聚集索引，二级索引，普通索引
表记录的排列顺序和索引的排列顺序是否一致	一致	不一致
叶子节点的存储内容	表中的数据	主键和索引列

聚簇索引

聚簇索引也被称为一级索引或聚集索引。

优点

聚集索引表记录的排列顺序和索引的排列顺序一致，只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到，所以查询效率较高。

缺点

新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

对于InnoDB存储引擎来说

如果表设置了主键，则主键就是聚簇索引
如果表没有主键，则会默认第一个 NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
以上都没有，则会默认创建一个隐藏的 row_id 作为聚簇索引
由此可见，InnoDB 必须要有至少一个聚簇索引

聚集索引在叶子节点存储的是行记录。

非聚簇索引

非聚簇索引，也叫做非聚集索引或二级索引或普通索引。除聚簇索引外的索引，即非聚簇索引。

非聚集索引在叶子节点存储的内容是

InnoDB 存储引擎中，非聚集索引在叶子节点存储的是聚集索引的值
MyISAM 存储引擎中，非聚集索引在叶子节点存储的是记录指针

索引的逻辑顺序与磁盘上行的物理存储顺序不同，非聚集索引在叶子节点存储的是主键和索引列。

当我们使用非聚集索引查询数据时，需要拿到叶子上的主键再去表中查到想要查找的数据，需要扫描两次索引 B+树，这个过程被称为「回表」。

`MyISAM`和`InnoDB`

在MySQL中，最常用的两个存储引擎是MyISAM和InnoDB，它们对索引的实现方式是不同的。

MyISAM : data 存的是数据地址。索引是索引，数据是数据。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚簇索引

图 3 MyISAM 引擎存储结构示意图

InnoDB: data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚簇索引。

图 4 InnoDB引擎结构示意图

1.2 `MyISAM`与`InnoDB`两种存储引擎的区别

MyISAM是非事务安全的，而InnoDB是事务安全的
MyISAM锁的粒度是表级的，而InnoDB支持行级锁
MyISAM支持全文类型索引，而InnoDB不支持全文索引
MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM
MyISAM表保存成文件形式，跨平台使用更加方便
MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择MyISAM存储引擎
InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择InnoDB存储引擎。

2 Mysql 存储原理

索引页的结构

首先看下InnoDB的逻辑存储结构，如下图所示：

TableSpace可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。InnoDB存储引擎有一个共享的空间ibdata1，即所有的数据都存放在这个空间内，如果用户启用了参数innodb_file_per_table，则每张表一个单独的表空间。表空间由各种Segement组成，常见Segment比如数据段、索引段、回滚段等。

Segement由一个个Extent组成。Extent是由连续的Page组成的空间。Page是InnoDB磁盘管理的最小单位，下面着重了解下Page。这片博客中的图比较形象：从MySQL InnoDB物理文件格式深入理解索引，有兴趣可以看看这篇文章，写的比较深刻。

更详细的Page结构字段描述如下图所示：

在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表，如下图所示：

在InnoDB中，数据会存储到磁盘上，在真正处理数据时需要先将数据加载到内存，表中读取某些记录时，InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB，也就是说，当需要从磁盘中读数据时每一次最少将从磁盘中读取16KB的内容到内存中，每一次最少也会把内存中的16KB内容写到磁盘中。

3 `InnoDB` 数据页结构

页是 `InnoDB`管理存储空间的基本单位，一个页的大小默认是`16KB`。在`Mysql`数据库终端可以使用如下`sql`语句查询页的大小:

SHOW GLOBAL STATUS like 'Innodb_page_size'

页结构：

图 5 InnoDB存储引擎数据页结构

名称	中文名	占用空间	简单描述
File Header	文件头部	38字节	页的一些通用信息
Page Header	页面头部	56字节	数据页专有的一些信息
Infimum+Supremum	最小记录和最大记录	26字节	2个虚拟的行记录
User Records	用户记录	不确定	实际存储的行记录内容
Free Space	空闲空间	不确定	页中尚未使用的空间
Page Directory	页面目录	不确定	页中的某些位置的相对位置
File Trailer	文件尾部	8字节	校验页是否完整

表 1 InnoDB存储引擎数据页结构字段说明

4 InnoDB行格式

一行记录可以以不同的格式存在InnoDB中，行格式分别是Compact、Redundant、Dynamic和Compressed行格式

Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了。
由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

Redundant 行格式我这里就不讲了，因为现在基本没人用了，这次重点介绍 Compact 行格式，因为 Dynamic 和 Compressed 这两个行格式跟 Compact 非常像。

所以，弄懂了 Compact 行格式，之后你们在去了解其他行格式，很快也能看懂。

我们可以在创建或修改表的语句中指定行格式：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

4.1 COMPACT行格式

可以看到，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。

接下里，分别详细说下

4.2 记录的额外信息

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是：

变长字段长度列表
Null值列表
记录头的信息

变长字段长度列表

Mysql支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

CHAR是一种固定长度的类型，VARCHAR则是一种可变长度的类型。 VARCHAR(M)，M代表最大能存多少个字符。(MySQL5.0.3以前是字节，之后就是字符)

为了展示「变长字段长度列表」具体是怎么保存「变长字段的真实数据占用的字节数」，我们先创建这样一张表，字符集是 ascii（所以每一个字符占用的 1 字节），行格式是 Compact，t_user 表中 name 和 phone 字段都是变长字段：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` VARCHAR(20) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

现在 t_user 表里有这三条记录：

接下来，我们看看看看这三条记录的行格式中的「变长字段长度列表」是怎样存储的。

先来看第一条记录：

name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；
age 列和 id 列不是变长字段，所以这里不用管。

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放（等下会说为什么要这么设计），所以「变长字段长度列表」里的内容是「 03 01」，而不是「01 03」。

同样的道理，我们也可以得出第二条记录的行格式中，「变长字段长度列表」里的内容是「 04 02」，如下图：

第三条记录中 phone 列的值是 NULL，NULL 是不会存放在行格式中记录的真实数据部分里的，所以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度。

为什么「变长字段长度列表」的信息要按照逆序存放？

这个设计是有想法的，主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

同样的道理， NULL 值列表的信息也需要逆序存放。

如果你不知道什么是 CPU Cache，可以看这篇文章 (opens new window)，这属于计算机组成的知识。

每个数据库表的行格式都有「变长字段字节数列表」吗？

其实变长字段字节数列表不是必须的。

当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了，因为没必要，不如去掉以节省空间。

所以「变长字段长度列表」只出现在数据表有变长字段的时候。

Null值列表

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

二进制位的值为1时，代表该列的值为NULL。
二进制位的值为0时，代表该列的值不为NULL。

另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

还是以 t_user 表的这三条记录作为例子：

接下来，我们看看看看这三条记录的行格式中的 NULL 值列表是怎样存储的。

先来看第一条记录，第一条记录所有列都有值，不存在 NULL 值，所以用二进制来表示是酱紫的：

但是 InnoDB 是用整数字节的二进制位来表示 NULL 值列表的，现在不足 8 位，所以要在高位补 0，最终用二进制来表示是酱紫的：

所以，对于第一条数据，NULL 值列表用十六进制表示是 0x00。

接下来看第二条记录，第二条记录 age 列是 NULL 值，所以，对于第二条数据，NULL值列表用十六进制表示是 0x04。

最后第三条记录，第三条记录 phone 列和 age 列是 NULL 值，所以，对于第三条数据，NULL 值列表用十六进制表示是 0x06。

我们把三条记录的 NULL 值列表都填充完毕后，它们的行格式是这样的：

每个数据库表的行格式都有「NULL 值列表」吗？

NULL 值列表也不是必须的。

当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。

所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。

「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？

「NULL 值列表」的空间不是固定 1 字节的。

当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

记录头信息

除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它有5个固定的字节组成。5个字节也就是40个二进制，不同的位代表不同的意思，如图：

表 2 记录头信息字段说明

这里说几个比较重要的：

delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer，我们来看下这三个字段是什么。

row_id

如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。

trx_id

事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。

roll_pointer

这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

如果你熟悉 MVCC 机制，你应该就清楚 trx_id 和 roll_pointer 的作用了，如果你还不知道 MVCC 机制，可以看完这篇文章 (opens new window)，一定要掌握，面试也很经常问 MVCC 是怎么实现的。

表 3 三个隐藏列说明

实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

4.3 行溢出数据

VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用

首先在Mysql数据库终端控制台执行如下sql脚本：

CREATE TABLE varchar_size_demo(
    c VARCHAR(65535)
    ) CHARSET=ascii ROW_FORMAT=Compact;

执行后会发现控制台报以下错误：

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

报错信息表达的意思是：MySQL对一条记录占用的最大存储空间是有限制的，除BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。这个65535个字节除了列本身的数据之外，还包括一些其他的数据，比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

真实数据
变长字段真实数据的长度
NULL值标识

如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为变长字段的长度占用2个字节，NULL值标识需要占用1个字节。

没有NOT NULL属性：

CREATE TABLE varchar_size_demo(
     c VARCHAR(65532)
    ) CHARSET=ascii ROW_FORMAT=Compact;

执行结果：

Query OK, 0 rows affected (0.02 sec)

有Not Null属性:

CREATE TABLE varchar_size_demo(
     c VARCHAR(65533) not null
    ) CHARSET=ascii ROW_FORMAT=Compact;

执行结果：

Query OK, 0 rows affected (0.02 sec)

说明VARCHAR类型的列有NOT NULL属性时最多可存储65533个字节的数据

记录中的数据太多产生的溢出

一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录。

在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。

4.4 Dynamic和Compressed行格式

这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储一部分数据，而是把所有的数据都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。另外，Compressed行格式会采用压缩算法对页面进行压缩

索引为何要选择B+Tree？

1、B+Tree结构为何会存在叶节点呢？

其实在之前的数据结构中，从来没有叶节点的这个概念出现，每个节点信息在整棵树结构中只会存储一份，但为什么B+树中会用叶节点，同时冗余一份节点信息呢？因为你从前面的B+Tree结构中，也能明显观测到2、3、4、5节点都会出现了两次。在这里如果想要搞明白为什么要冗余节点，你得想明白一个问题：

能不能将所有的索引数据、表数据全部放入到一个节点中存储呢？这样树的高度永远为1呀，是不是只需要经过一次磁盘IO啊？

其实乍一听似乎有道理，实则是行不通的，因为一次磁盘IO读取的数据量是有限制的，如果将所有的数据全放入到一个节点中存储，那一次磁盘IO只能读取节点的一部分数据，将整个节点读完，本质上就和之前走一次全表没区别了。

理解这个点之后，再来看看抛出的问题：B+Tree为何会有叶节点冗余数据呢？

因为B+Tree的每个节点大小会有限制，所以如果将数据存储在叶节点上，会导致单个树节点存的索引键很少。但如果树的叶节点不存实际的行数据，就代表单个节点可以存更多的索引键，单个节点存的越多也就代表着树的高度会越小，树的高度越小就等价于查询时会发生的磁盘IO次数越少，IO次数越少就相当于数据检索速度会更快，到这里相信大家应该能明白为什么会有叶节点冗余索引键了。

但索引中除开索引键外，也必须要存数据，如果不存数据索引就失去了意义，因此B+tree最下面一排的叶子节点，其中就会存储对应的索引键与行数据/聚簇索引字段值。

一句话来概述，B+Tree的叶节点仅是作为一个“过渡者”的角色，主要是为了提升索引效率的，实际的数据会保存在最下面的叶子节点中，叶节点中仅有一个指针指向罢了。

2、千万级别的表B+Tree会有多高？

搞清楚B+Tree的一些疑惑后，此时来倒推一个问题，MySQL中一张千万级别的数据表，如果基于自增ID的主键字段建立B+树索引，那此时树会有多高呢？有人或许会认为，虽然B+Tree结构很优异，但千万级别的表至少有1000W条数据，再怎么样应该也有几十、几百的树高吧？但实际上答案会让你大吃一惊。

想要科学的弄懂这个问题，那必须建立在实际的依据上来计算，想要计算出树高，首先得有三个值：
①索引字段值的大小。
②MySQL中B+Tree单个节点的大小。
③MySQL中单个指针的大小。

如何计算索引字段值的大小呢？

这点要依据字段所使用的数据类型来决定。假设此时表的自增ID，创建表时使用的int类型，int类型在计算机中占4Bytes，那此时基于ID字段建立主键索引时，B+Tree每个节点的索引键大小就为4Bytes。

如何得知MySQL中B+树单个节点的大小呢？

对于索引单个节点的容量是多少呢？在MySQL中默认使用引擎的一页大小作为单节点的容量，假设此时表的存储引擎为InnoDB，就可以通过下述这条命令查询：

SHOW GLOBAL STATUS LIKE "Innodb_page_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+

从上述查询结果来看，InnoDB引擎的一页大小为16384Bytes，也就是16KB，此时也就代表着B+Tree的每个节点容量为16KB。

MySQL中的指针是多大呢？

一般来说，操作系统的指针为了方便寻址，一般都与当前的操作系统位数对应，例如32位的系统，指针就是32bit/4Bytes，64位的操作系统指针则为64bit/8Bytes，但由于64bit的指针寻址范围太大，目前的计算机根本用不上这么大的寻址范围，因此在MySQL-InnoDB引擎的源码中，单个指针被缩小到6Bytes大小。

千万级别的索引树高计算

从上述三条可得知：单个索引节点容量为16KB，主键字段值为4B，指针大小为6B，一个完整的索引信息是由主键字段值+指针组成的，也就是4+6=10B，那此时先来计算一下单个节点中可存储多少个索引信息呢？

16KB / 10B ≈ 1638个。

那此时来计算一下，对于一颗高度为2的B+树，根节点可存储1638个叶子节点指针，也就代表着B+Tree的第二层有1638个叶子节点，因为叶子节点要存储实际的行数据，假设表中每行数据为1KB，这也就是代表着一个叶子节点中可存储16条行数据，那么一颗高度为2的B+树可存储的索引信息为：1638 * 16 = 26208条数据。

再来算算树高为3的B+树可以存多少呢？因为最下面一排才是叶子节点，此时树高为3，也就代表着中间一排是叶节点，只存储指针并不存储数据，而每个节点可容纳1638个索引键+指针信息，因此计算过程是：1638 * 1638 * 16 = 42928704条。

是不是很令你惊讶？树高为3的B+Tree，竟然可以存储四千多万条数据，也就代表着千万级别的表，走索引查询的情况下，大致只需要发生三次磁盘IO即可获取数据。

当然，上述的这个数据是基于主键为int类型、表的一行数据为1KB来计算的，实际情况中会不一样，因为主键有可能是bigint类型或其他类型，而一行数据也可能不仅仅只有1KB。因此对于一张实际的千万级别表，它的主键索引实际树高有多少，你结合主键的数据类型以及一行数据的大小，也可以计算出来，它同时不会太高。
对实际的千万表索引树高感兴趣的，我提供一个计算公式：索引键大小=索引字段类型所占的空间、一行表数据大小=所有表字段的类型+隐藏字段（20Bytes）所占大小总和，得到这两个值之后，再套入前面的例子中既可得知。

看到这里，对于索引凭啥那么快？为啥能够提升查询性能？相信大家也有了答案，毕竟索引树高才是个位数，发生的磁盘IO次数也那么少，检索数据的速度不快才来了个鬼~

不过B+Tree中的每个索引页中，还会存储页头（页号、指针、伪记录等）、页目录、页尾等信息，大概一共占用128Bytes左右，因此想要真正的计算出来接近实际情况的索引树高，还需要把这点考虑在内~

3、MySQL索引底层的真正结构

以为B+Tree就是索引的终点了嘛？实则不然，MySQL的追求可不止于此，虽然B+Tree已经特别特别优秀了，但B+Tree的叶子节点之间是单向指针组成的链表结构，这对于倒排序查询时，显然并不友好，因为只有单向指针，那么只能先正序获取数据后再倒排一次，因此MySQL真正的索引结构还对B+Tree做了变种设计！

啥意思呢？也就是MySQL在设计索引结构时，对于原始的B+Tree又一次做了改造，叶子节点之间除开一根单向的指针之外，又多新增了一根指针，指向前面一个叶子节点，也就是MySQL索引底层的结构，实际是B+Tree的变种，叶子节点之间是互存指针的，所有叶子节点是一个双向链表结构。

这样做的好处在于：即可以快速按正序进行范围查询，而可以快速按倒序进行范围操作，在某些业务场景下又能进一步提升整体性能！

1.5.4、前缀索引为何能提升索引性能？

这个问题是在之前的《索引初识篇》中抛出的问题，到这里答案也就呼之欲出了，因为前缀索引可以选用一个字段的前N个字符来创建索引，相较于使用完整字段值做为索引键，前缀索引的索引键，显然占用的空间更少，一个索引键越小，代表一个B+Tree节点中可以存储更多的索引键，等价于树高会越小，也就代表磁盘IO更少，检索数据时自然效率更高。

(1). 为什么 InnoDB 表必须有主键，并且推荐使用整型的自增主键？

A. 为什么要有主键？

　mysql 底层就是用 B+Tree 维护的，而 B+Tree 的结构就决定了必须有主键才能构建 B+Tree 树这个结构。每个表在磁盘上，是单独的一个文件。索引和数据都在其中，文件是按照主键索引组织的一个 B+TREE 结构。假如没有定义主键，MySQL 会在挑选能唯一标识的字段作为索引；假如找不到，会生成一个默认的隐藏列作为主键列。

B. 为什么用整型主键？

假如使用类似 UUID 的字符串作为主键，那么在查找时，需要比较两个主键是否相同，这是一个相比整型比较非常耗时的过程。需要一个字符，一个字符的比较，自然比较慢。

C. 为什么用自增主键？

　① 后面的主键索引总是大于前面的主键索引，在做范围查询时，非常方便找到需要的数据。

　② 在添加的过程中，因为是自增的，每次添加都是在后面插入，树分裂的机会小；而 UUID 大小不确定，分裂机会大，需要重新平衡树结构，性能损耗大。

(2). 为什么非主键索引结构叶子节点存储的是主键值，而不是全部数据？

　① 节省空间：指向主键的节点，不用再存储一份相同的数据；（否则的话，如果建立多个非主键索引，每个上面都存储的完整数据，非常占用空间）

　② 数据一致性：如果修改索引 15 的数据，那只要修改主键的 data，而如果非主键的 data 也存一份的话，那得修改两份，这样就涉及到事务一致性的问题，耗时，性能低。

(3). 为什么希望使用覆盖索引？

　如果非聚集索引中能索引覆盖，那么我们只需要遍历非聚集索引这个 B+Tree 从其中的 Key 里拿到索引值就可，只需要遍历一棵树。如果不能索引覆盖，需要先遍历非聚集索引，然后拿到 data 中存储的主键值，再去聚集索引中遍历查找数据，相比索引覆盖的话，IO 次数更多，性能相对低。

索引实战

索引实战学习的基础，首先应该学会分析SQL的执行，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，下面我们学习下EXPLAIN。

explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句。不展开讲解，大家可自行百度这块知识点。

使用格式：

EXPLAIN SQL...;

Look一下EXPLAIN 查询结果包含的字段（v5.7）

mysql> explain select * from student;
++-+-++--+---+--+-+--+--+--+---+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
++-+-++--+---+--+-+--+--+--+---+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
++-+-++--+---+--+-+--+--+--+---+

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

Extra 探究

本打算展开讲一下Extra的常见的几个值：Using index，Using index condition，Using where，其中Using index 表示使用了覆盖索引，其它的都不好总结。翻阅网上众多博文，目前暂未看到完全总结到位的，且只是简单的查询条件下也是如此。我本打算好好总结一番，发现半天过去了，坑貌似越来越大，先打住，因为我目前没太多时间研究…

我这有个简单的表结构，有兴趣的同学可以多尝试总结（注意玩总结的，不能只考虑简单查询的情况）

create table student(
 id int auto_increment primary key,
 name varchar(255) not null,
 c_id int,
 phone char(11),
 guardian varchar(50) not null,
 qq varchar(20) not null,
 index stu_class_phone (name,c_id,phone),
 index qq (qq)
)engine=innodb charset=utf8;

这里有我的一个比对关键项表，或许对有心探索的同学有点帮助，估计看了也有点懵，建议先尝试后再回头看我这个表。

key_len讲解

我也稍微讲解一下网文中鲜有提及key_len字节长度计算规则，

mysql> explain select * from student where name='Joe';
++-+-++--+-+-+-+---+--+--+---+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
++-+-++--+-+-+-+---+--+--+---+
|  1 | SIMPLE      | student | NULL       | ref  | stu_class_phone | stu_class_phone | 767     | const |    1 |   100.00 | NULL  |
++-+-++--+-+-+-+---+--+--+---+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from student where name='Joe' and c_id=2;
++-+-++--+-+-+-+-+--+--+---+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra |
++-+-++--+-+-+-+-+--+--+---+
|  1 | SIMPLE      | student | NULL       | ref  | stu_class_phone | stu_class_phone | 772     | const,const |    1 |   100.00 | NULL  |
++-+-++--+-+-+-+-+--+--+---+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from student where name='Joe' and c_id=2 and phone='13500000000';
++-+-++--+-+-+-+---+--+--+---+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref               | rows | filtered | Extra |
++-+-++--+-+-+-+---+--+--+---+
|  1 | SIMPLE      | student | NULL       | ref  | stu_class_phone | stu_class_phone | 806     | const,const,const |    1 |   100.00 | NULL  |
++-+-++--+-+-+-+---+--+--+---+
1 row in set, 1 warning (0.00 sec)

如果表结构未限制某列为非空，那么MySQL将会使用一个字节来标识该列对应的值是否为NULL；限定非空，不止not null，还有primary key等隐含非空约束。
字符串类型括号内的数字并不是字节数，而是字符长度，一个字符占几个字节与建表选用的字符集有关，如果表使用的是utf8字符集，那么一个字符占3个字节；注意，对于可变长字符串类（varchar）型的实际占用字节数，除了需要考虑设置了非空与否的那个字节，还要使用2个字节来记录字符串的长度。定长字符串类型（char）则不用额外字节记录长度
整数类型括号内的数字无论是什么，都不影响它实际的字节数，int就是4个字节。int(xx)，xx只是填充占位符，一般配合zerofill使用，只是一种标识，没有多大用处。

观察三次Explain 的查询结果，留意key_len与where搜索键的微妙关系，如果type列的值是ref时，ref列的值标识索引参考列的形参。

首先，我们看到key列为

stu_class_phone

，说明该查询使用了stu_class_phone索引，这是一个组合索引

（name,c_id,phone）

。看下这三个字段的结构声明与实际字节计算：

name varchar(255) not null
, （占767字节）

①255字长（utf8字符集，一个字长3字节）255*3=765 √
②是否非空已限定非空（not null
）那就不额外占1字节
③字符串长度 str_len占2字节√

c_id int
,（占5字节）

①是否非空未限定非空那将额外占1字节 √
②int 占4字节√

phone char(11)
,（占36字节）

①11字长（utf8字符集，一个字长3字节）11*3=33√

int(xx) xx无论是多少 int永远4字节 xx只是填充占位符（一种标识一般配合zerofill使用的）

组合索引满足最左前缀原则就会生效，我们看到三次Explain 的查询中stu_class_phone索引都生效了，第一次采用name构建索引树，第二次采用name+c_id构建索引树，第三次采用name+c_id+phone构建索引树。第一次：key_len就是name的存储字节数，767；第二次：key_len就是name+c_id的存储字节数，767+5=772；第三次：255 * 3 + 2 + 5 + 11 * 3 + 1 = 806

我们再看一条执行计划：

mysql> explain select * from student where name='Joe' and phone ='13500000000';
++-+-++--+-+-+-+---+--+--+---+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra                 |
++-+-++--+-+-+-+---+--+--+---+
|  1 | SIMPLE      | student | NULL       | ref  | stu_class_phone | stu_class_phone | 767     | const |    1 |    50.00 | Using index condition |
++-+-++--+-+-+-+---+--+--+---+

为什么不是255 * 3 + 11 * 3 + 1 +2=801；却是767？我们看下ref为const，说明只有一个索引键生效，明显就是name，因为不符合最左前缀原则，phone列被忽视了；也可能是mysql做了优化，发现通过name和phone构建的索引树对查询列（* 表示全部列）并没有加快了查询速率，自行优化，减少键长。

拓展：优秀的索引是什么样的？

键长短
精度高

比如，在保证查询精度的情况下，两个索引的key_len分别为10字节和100字节，数据行的量也一样（大数据量效果更佳），100字节索引检索的时间会比10字节的要多；再者，一个磁盘页能存储的10字节的索引记录的量是100字节的10倍。

最左前缀原则

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先（查询条件精确匹配索引的左边连续一列或几列，则构建对应列的组合索引树），在检索数据时也从联合索引的最左边开始匹配。

为了方便讲解，我写了点个人理解的概念声明，如下图：

mysql> create table t(
    -> a int not null,
    -> b char(10) not null,
    -> c int not null,
    -> d varchar(20) not null,
    -> index abc(a,b,c)
    -> )engine=innodb charset=utf8;

mysql> insert into t values(1,'hello',1,'world');
mysql> insert into t values(2,'hello',2,'mysql');

以下均为筛选条件不包含主键索引情况下：（主键索引优先级最高）

只要筛选条件中含有组合索引最左边的列但不含有主键搜索键的时候，至少会构建包含组合索引最左列的索引树。（如：index(a)）

查询列都是组合索引列且筛选条件全是组合索引列时，会构建满列组合索引树（index(a,b,c) ）【覆盖索引】

筛选条件包含普通搜索键但没包含组合索引列最左键，不会构建组合索引树

如果筛选条件全是组合索引最左连续列作为搜索键，将构建连续列组合索引树。（比如：index(a,b)却不能index(a,c)）

MySQL查询优化器会优化and连接，将组合索引列规则排号。（比如：b and a 等同于 a and b）

参考：

https://juejin.cn/post/7090477794027077640

https://cloud.tencent.com/developer/article/2114556

https://juejin.cn/post/7151275584218202143

https://xie.infoq.cn/article/7ac5b27de23c55a8699a06d0e

https://zhuanlan.zhihu.com/p/635230263

https://zhuanlan.zhihu.com/p/500248981

posted on 2024-06-24 15:16 xuanm 阅读(149) 评论(0) 编辑收藏举报

刷新页面返回顶部

众妙之门

公告

从存储结构上划分

从应用层次上来划分

从表记录的排列顺序和索引的排列顺序是否一致来划分

聚簇索引

非聚簇索引

`MyISAM`和`InnoDB`

1.2 `MyISAM`与`InnoDB`两种存储引擎的区别

2 Mysql 存储原理

索引页的结构

3 `InnoDB` 数据页结构

页是 `InnoDB`管理存储空间的基本单位，一个页的大小默认是`16KB`。在`Mysql`数据库终端可以使用如下`sql`语句查询页的大小:

4 InnoDB行格式

4.1 COMPACT行格式

4.2 记录的额外信息

4.3 行溢出数据

4.4 Dynamic和Compressed行格式

索引为何要选择B+Tree？

1、B+Tree结构为何会存在叶节点呢？

2、千万级别的表B+Tree会有多高？

如何计算索引字段值的大小呢？

如何得知MySQL中B+树单个节点的大小呢？

MySQL中的指针是多大呢？

千万级别的索引树高计算

3、MySQL索引底层的真正结构

1.5.4、前缀索引为何能提升索引性能？

(1). 为什么 InnoDB 表必须有主键，并且推荐使用整型的自增主键？

A. 为什么要有主键？

B. 为什么用整型主键？

C. 为什么用自增主键？

(2). 为什么非主键索引结构叶子节点存储的是主键值，而不是全部数据？

(3). 为什么希望使用覆盖索引？

索引实战

explain

Extra 探究

key_len讲解

最左前缀原则

众妙之门

公告

从存储结构上划分

从应用层次上来划分

从表记录的排列顺序和索引的排列顺序是否一致来划分

聚簇索引

非聚簇索引

MyISAM和InnoDB

1.2 MyISAM与InnoDB两种存储引擎的区别

2 Mysql 存储原理

索引页的结构

3 InnoDB 数据页结构

页是 InnoDB管理存储空间的基本单位，一个页的大小默认是16KB。在Mysql数据库终端可以使用如下sql语句查询页的大小:

4 InnoDB行格式

4.1 COMPACT行格式

4.2 记录的额外信息

4.3 行溢出数据

4.4 Dynamic和Compressed行格式

索引为何要选择B+Tree？

1、B+Tree结构为何会存在叶节点呢？

2、千万级别的表B+Tree会有多高？

如何计算索引字段值的大小呢？

如何得知MySQL中B+树单个节点的大小呢？

MySQL中的指针是多大呢？

千万级别的索引树高计算

3、MySQL索引底层的真正结构

1.5.4、前缀索引为何能提升索引性能？

(1). 为什么 InnoDB 表必须有主键，并且推荐使用整型的自增主键？

A. 为什么要有主键？

B. 为什么用整型主键？

C. 为什么用自增主键？

(2). 为什么非主键索引结构叶子节点存储的是主键值，而不是全部数据？

(3). 为什么希望使用覆盖索引？

索引实战

explain

Extra 探究

key_len讲解

最左前缀原则

`MyISAM`和`InnoDB`

1.2 `MyISAM`与`InnoDB`两种存储引擎的区别

3 `InnoDB` 数据页结构

页是 `InnoDB`管理存储空间的基本单位，一个页的大小默认是`16KB`。在`Mysql`数据库终端可以使用如下`sql`语句查询页的大小: