数据结构">1、索引的数据结构
2、InnoDB数据存储引擎结构
3、索引的创建与设计原则
- 1. 索引的声明与使用
  - 1. 1 索引的分类
  - 1. 2 创建索引
这里
调用
- 3. 索引的设计原则
- 3.5小结

1、索引的数据结构

索引的概述

为什么使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教课书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MySQL中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。

如上图所示，数据库没有索引的情况下，数据分布在硬盘不同的位置上面，读取数据时，摆臂需要前后摆动查找数据，这样操作非常消耗时间。如果数据顺序摆放，那么也需要从1到6行按顺序读取，这样就相当于进行了6次I0操作，依旧非常耗时。如果我们不借助任何索引结构帮助我们快速定位数据的话，我们查找Col2=89这条记录，就要逐行去查找、去比较。从Col2=34开始，进行比较，发现不是，继续下一行。我们当前的表只有不到10行数据，但如果表很大的话，有上千万条数据，就意味着要做很多很多次磁盘I/0才能找到。现在要查找Col 2=89这条记录。CPU必须先去磁盘查找这条记录，找到之后加载到内存，再对数据进行处理。这个过程最耗时间的就是磁盘I/o(涉及到磁盘的旋转时间(速度较快)、磁头的寻道时间(速度慢、费时))。
假如给数据使用二叉树这样的数据结构进行存储，如下图所示

对字段Col2添加了索引，就相当于在硬盘上为Col 2维护了一个索引的数据结构，即这个二叉搜索树。二叉搜索树的每个结点存储的是（K，V)结构，key是Col 2，value是该key所在行的文件指针(地址)。比如:该二叉搜索树的根节点就是:（34,0x07)。现在对Col2添加了索引，这时再去查找Col2=89这条记录的时候会先去查找该二叉搜索树（二叉树的遍历查找)。读34到内存，89>34;继续右侧数据，读89到内存，89.== 89;找到数据返回。找到之后就根据当前结点的value快速定位到要查找的记录对应的地址。我们可以发现，只需要查找两次就可以定位到记录的地址，查询速度就提高了。|
这就是我们为什么要建索引，目的就是为了减少磁盘I/0的次数，加快查询速率。

索引及其优缺点

1、索引概述

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。

2、优点

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。
（2）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

3、缺点

（1）创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
（2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

因此，选择使用索引时，需要综合考虑索引的优点和缺点。

索引的推演由来

没有索引时的查询

先来看一个精确匹配的例子：
SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;
在实际当中可能一个表的数据量会达到几百万甚至几千万条数据，这些数据在底层中存储的时候就使用到一个基本单位：数据页。一个数据页的默认大小是16KB，所以一个表的存储可能会涉及到多个数据页才能存储完。

①在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况:

以主键为搜索条件

可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

以其他非主键列作为搜索条件

因为在数据页中并没有对非主键列建立所谓的页目录（没有顺序），所以我们无法通过二分法快速定位相应的槽。这种情况下只能从第一条记录开始依次遍历单链表中（使用单链表是保证存储空间的逻辑连续）的每条记录（全表扫描），然后对比每条记录是不是符合搜索条件。很显然这种查找的效率是非常低的。

②在很多页中查找

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤:
1．定位到记录所在的页。|
2．从所在的页内中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上面的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的。
如果一个表有一亿条记录呢？此时索引就应运而生了。

设计索引

针对以上问题，我们应该如何去设计一个索引的结构呢？下面我们以一个数据表的创建为例子简单讲解一下。

mysql> CREATE TABLE index_demo(
 ->   c1 INT,
 ->   c2 INT,
 ->   c3 CHAR(1),
 ->   PRIMARY KEY(c1)
 -> ) ROW_FORMAT = Compact;

这个新建的index_demo表中有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键，这个表使用 Compact 行格式来实际存储记录的。关于行格式后面会有详细解释，这里我们简化了index_demo表的行格式示意图：

我们只在示意图里展示记录的这几个部分:

record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、 2 表示最小记录、 3 表示最大记录、 1 暂时还没用过，下面讲。
next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。前面说过，表中相邻两条记录是通过链表进行关联的，next_record就是用来指向下条数据的指针
各个列的值：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果就是这样：

把一些记录放到页里的示意图就是：

到这里，一个数据表的基本模型就出来了。然后我们针对具体的问题设计一个简单的索引方案。

①一个简单的索引设计方案

我们在分析没有索引时根据某个搜索条件查找一些记录会遍历所有的数据页，因为各个页中的记录并没有规
律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。
所以我们需要能够快速的定位到需要查找的记录在哪些数据页中，为达到这个目的我们可以为快速定位记录所在的数据页而建立一个目录，建这个目录必须完成下边这些事：

1、下一个数据页中用户记录的主键值必须大于上一个页中用户记录的所有主键值。

假设:每个数据页最多能存放3条记录(实际上一个数据页非常大，可以存放下好多记录)。有了这个假设之后我们向index_demo表插入3条记录:

mysql> INSERT INTO index_demo VALUES(1，4，'u')，(3，9， 'd')，(5，3，'y');

这些记录的主键恰好是递增的，且已经按照主键值的大小串联成一个单向链表了，如图所示:

从图中可以看出来,index_demo表中的3条记录都被插入到了编号为10的数据页中了。此时我们再来插入一条记录:

mysql> INSERT INTO index_demo VALUES(4，4,'a ' ) ;

因为我们之前规定了一页最多只能放3条记录，所以我们不得不再分配一个新页28:

注意，新分配的数据页编号可能并不是连续的。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系（保持逻辑上的连续就行）。另外，如果将新插入的主键为4的记录插入到新开的页28中，而页10中用户记录最大的主键值是5，且页28中有一条记录的主键值是4，又因为5>4，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为4的记录的时候需要伴随着一次记录移动，也就是把主键值为5的记录移动到页28中，然后再把主键值为4的记录插入到页10中，这个过程的示意图如下:

这个过程表明了在对页中的记录进行增删改操作的过程中，我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立:下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程我们称为页分裂。

由于数据页的编号可能是不连续的，所以在向index_demo表中插入许多条记录后，可能是这样的效果:

因为这些16KB大小的页在物理存储上是不连续的，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录，每个页对应一个目录项，每个目录项包括下边两个部分:

页的用户记录中最小的主键值，我们用key来表示。
页号，我们用page_no表示。

所以我们为上边几个页做好的目录就像这样子：

以页28为例，它对应目录项2，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5。我们只需要把几个目录项在物理存储器上连续存储（比如:数组)，就可以实现根据主键值快速查找某条记录的功能了。比如:查找主键值为20的记录，具体查找过程分两步:
1．先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中(因为12< 20 < 209)，它对应的页是页9。
2．再根据前边说的在页中查找记录的方式去页9中定位具体的记录。

虽然说到现在为止一个简单的索引结构就出来了，但是会由于目录项在物理存储器上是使用数组之类进行连续存储的，会存在一些问题，比如说在实际运用的时候某个中间的页因为某些原因需要删除掉或者在某两个页之间添加新的页，这个时候它后面的页就得进行移动，这代价也太大了。所以现在的想法是将目录项也使用单向链表的方式连接起来达到逻辑上的连续即可。这样移动的代价就不存在了。
其中这些目录项构成的页就叫做目录页，跟数据页是不一样的。

②InnoDB中的索引方案

上边称为一个简易的索引方案，是因为我们为了在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储，但是这样做有几个问题:

InnoDB是使用页来作为管理存储空间的基本单位，最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。
我们时常会对记录进行增删，假设我们把页28中的记录都删除了，那意味着目录项2也就没有存在的必要了，这就需要把目录项2后的目录项都向前移动一下，这样牵一发而动全身的操作效率很差。

所以，我们需要一种可以灵活管理所有目录项的方式。我们发现目录项其实长得跟我们的用户记录差不多，只不过目录项中的两个列是主键和页号而已，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。那lnnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢?使用记录头信息里的record_type属性，它的各个取值代表的意思如下:
0∶普通的用户记录
1:目录项记录.
2∶最小记录.
3:最大记录
迭代1次：目录项纪录的页
我们把前边使用到的目录项放到数据页中的样子就是这样：

从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调目录项记录和普通的 用户记录的不同点：

目录项记录的 record_type 值是1，而普通用户记录的 record_type 值是0。
目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。
了解：记录头信息里还有一个叫min_rec_mask 的属性，只有在存储目录项记录的页中的主键值

最小的目录项记录的 min_rec_mask值为1，其他别的记录的 min_rec_mask值都是 0。

相同点：两者用的是一样的数据页，都会为主键值生成 Page Directory（页目录），从而在按照键
值进行查找时可以使用二分法来加快查询速度。

现在以查找主键为 20 的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：
先到存储目录项记录的页，也就是页30中通过二分法快速定位到对应目录项，因为 12 < 20 < 209 ，所以定位到对应的记录所在的页就是页9。
再到存储用户记录的页9中根据二分法快速定位到主键值为 20 的用户记录。

迭代2次：多个目录项纪录的页
虽然说目录项记录中只存储主键值和对应的页号，比存储用户记录需要的存储空间小多了，但是不论怎么说一个页只有16KB大小，能存放的目录项记录也是有限的，那如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，如何处理呢?
这里我们假设一个存储目录项记录的页最多只能存放4条目录项记录，所以如果此时我们再向上图中插入一条主键值为320的用户记录的话，那就需要分配一个新的存储目录项记录的页:

从图中可以看出，我们插入了一条主键值为320的用户记录之后需要两个新的数据页：

为存储该用户记录而新生成了页31 。
因为原先存储目录项记录的页30的容量已满（我们前边假设只能存储4条目录项记录），所以不得不需要一个新的页32 来存放页31 对应的目录项。

现在因为存储目录项记录的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步骤，以查找主键值为 20 的记录为例：

我们现在的存储目录项记录的页有两个，即页30和页32 ，又因为页30表示的目录项的主键值的范围是 [1, 320)，页32表示的目录项的主键值不小于 320，所以主键值为 20 的记录对应的目录项记录在页30 中。

通过目录项记录页确定用户记录真实所在的页。

在一个存储目录项记录的页中通过主键值定位一条目录项记录的方式说过了。

在真实存储用户记录的页中定位到具体的记录。

问题来了，在这个查询步骤的第1步中我们需要定位存储目录项记录的页，但是这些页是不连续的，如果我们表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢?那就为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子:

如图，我们生成了一个存储更高级目录项的页33，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在[1，320)之间，则到页30中查找更详细的目录项记录，如果主键值不小于320的话，就到页32中查找更详细的目录项记录。随着表中记录的增加，这个目录的层级会继续增加，如果简化一下，那么我们可以用下边这个图来描述它:

这个数据结构，它的名称是 B+树。

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。|
一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第О层，之后依次往上加。之前我们做了一个非常极端的假设:存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录

你的表里能存放100000000000条记录吗?所以一般情况下，我们用到的B+树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找(查找3个目录项页和一个用户记录页)，又因为在每个页面内有所谓的Page Directory(页目录)，所以在页面内也可以通过二分法实现快速定位记录。

聚簇索引、非聚簇索引、联合索引

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式(所有的用户记录都存储在了叶子节点)，也就是所谓的索引即数据，数据即索引。

特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MysQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。

优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

限制
对于MysQL数据库目前只有InnoDB数据引擎支持聚簇索引，而My[SAM并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个MysQL的表只能有一个聚簇索引。一般情况下就是该表的主键。·如果没有定义主键，Innodb会选择非空的唯一索引代替。如果没有这样的索引，Innodb会隐式的定义一个主键来作为聚簇索引。为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。

二级索引（辅助索引、非聚簇索引）
上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢?肯定不能是从头到尾沿着链表依次遍历记录一遍。
答案:我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一棵B+树，效果如下图所示:

概念：回表查询。我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。先在非聚簇索引中找到主键，然后根据主键去聚簇索引中找到要查找的数据。
也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！

为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？

如果把完整的用户记录放到非聚簇索引的叶子节点是可以不用回表。但是太占地方了因为一个表中可以有多个非聚簇索引，相当于每建立一棵B+树（每建立一个非聚簇索引）都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。
因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引(英文名secondary index )，或者辅助索引。由于我们使用的是c2列的大小作为B+树的排序规则，所以我们也称这个B+树是为c2列建立的索引。
非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

小结:聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别:
1.聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是主键数据位置。非聚簇索引不
会影响数据表的物理存储顺序。
2．一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引
目录提供数据检索。
3.使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列的大小进行排序，这个包含两层含义：

先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序

注意一点，以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

建立联合索引只会建立如上图一样的1棵B+树。
为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

InnoDB的B+树索引的注意事项

根页面位置万年不动

我们前边介绍B+树索引的时候，为了大家理解上的方便，先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点，实际上B+树的形成过程是这样的:

每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
·随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
·当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据犍值〈（也就定聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被重新分配到页a或者b中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是:一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

我们知道B+树索引的内节点中目录项记录的内容是索引列＋页号的搭配，但是这个搭配对于二级索引来说有点儿不严谨。还拿上面的index_demo表为例，假设这个表中的数据是这样的:

在上面的情况下，如果我们想新插入一行记录，其中c1、c2、c3的值分别是: .9、1、’c’，那么在修改这个为c2列建立的二级索引对应的B+树时，便碰到了个大问题:由于页3中存储的目录项记录是由c2列＋页号的值构成的，页3中的两条目录项记录对应的c2列的值都是1，而我们新插入的这条记录的c2列的值也是1，那我们这条新插入的记录到底应该放到页4中，还是应该放到页5中啊?答案是:对不起，懵了。
所以为了解决这种情况，要求内节点（非叶子节点）中目录项记录具有唯一性。即为了让新插入记录能找到自己在那个页里，我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的:
索引列的值
主键值。
页号
也就是我们把主键值也添加到二级索引内节点中的目录项记录了，这样就能保证B+树每一层节点中各条目录项记录除页号这个字段外是唯一的，所以我们为c2列建立二级索引后的示意图实际上应该是这样子的

这样我们再插入记录(9，1，’c’)时，由于页3中存储的目录项记录是由c2列＋主键＋页号的值构成的，可以先把新记录的c2列的值和页3中各目录项记录的c2列的值作比较，如果c2列的值相同的话，可以接着比较主键值，因为B+树同一层中不同目录项记录的c2列＋主键的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录，在本例中最后确定新记录应该被插入到页5中。

一个页面最少存储2条记录

MyISAM中的索引方案

B树索引适用存储引擎如表所示：（注意mysql官方认为这个B-Tree就是我们说的B+Tree）

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb和MyISAM默认的索引是Btree索引；而Memory默认的索引是Hash索引。

MyISAM引擎虽然使用的是 B+Tree 作为索引结构，但是其叶子节点的data域存放的是数据记录的地址，而不是跟InnoDB中的非叶子节点不存放数据，只存放目录项，叶子节点存放的内容跟聚簇索引与非聚簇索引有关。

1、MyISAM索引的原理

我们知道InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储:

将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合。

使用MyISAM引擎的表，在底层存储时对应三个文件，如下图中的emp3就是使用MyISAM作为引擎的表

其中sdi后缀的表存储的是表结构对应的信息（5.7版本的后缀是frm）
MYD后缀存储的是表中数据记录的。
MYI后缀文件存储的是表索引信息的。
InnoDB引擎的表中，idb后缀的文件同时存储了表索引信息以及表的数据记录信息。

这里设表一共有三列，假设我们以col1为主键，上图是一个MylSAM表的主索引 (Primary key)示意图。可以看出MyISAM的索引文件仅仅保存数据记录的地址。跟Innodb不一样，这里的索引跟数据信息是分离开的，而innodb中索引即是数据，数据即是索引。

注意，MyISAM为引擎的表中，此时表中的数据如上图所示，假如现在我们想往表中添加一条主键为3的数据记录。它不会再进行排序了，而是直接将主键为3的这条记录直接添加到主键为50这条记录的后面，只是按照添加的顺序往表中插入元素。

如果我们在Col2上建立一个二级索引，则此索引的结构如下图所示：

可以看出，在MylISAM中，主键索引和二级索引(Secondary key)在结构上没有任何区别，只是主键索引要求key是唯一的，而二级索引的key可以重复。

同样也是一棵B+Tree,data域保存数据记录的地址。因此，MyISAM中索引检索的算法为:首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

2、MyISAM 与 InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。
小结两种引擎中索引的区别：
① 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM 中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。
② InnoDB的数据文件本身就是索引文件是一起的，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
③ InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
④ MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
⑤ InnoDB要求表必须有主键（ MyISAM可以没有主键，因为它无所谓聚簇非聚簇了）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

选B+树的理由

从MySQL的角度讲，不得不考虑一个现实问题就是磁盘l0。如果我们能让索引的数据结构尽量减少硬盘的I/O作，所消耗的时间也就越小。可以说，磁盘的I/O操作次数对索引的使用效率至关重要。
查找都是索引操作，一般来说索引非常大，尤其是关系型数据库，当数据量比较大的时候，索引的大小有可能个G甚至更多，为了减少索引在内存的占用，数据库索引是存储在外部磁盘上的。当我们利用索引查询的时候不可能把整个索引全部加载到内存，只能逐一加载，那么可以认为MySQL衡量查询效率的标准就是磁盘IO次数。

1、全表遍历

就相当于从表的开头逐一遍历到表的末尾，代价非常大。

2、Hash结构

Hash本身是一个函数，又被称为散列函数，它可以帮助我们大幅提升检索数据的效率。
Hash算法是通过某种确定性的算法(比如MD5、SHA1、SHA2、SHA3)将输入转变为输出。相同的输入永远可以得到相同的输出，假设输入内容有微小偏差，在输出中通常会有不同的结果。
举例:如果你想要验证两个文件是否相同，那么你不需要把两份文件直接拿来比对，只需要让对方把 Hash 函数计算得到的结果告诉你即可，然后在本地同样对文件进行Hash函数的运算，最后通过比较这两个Hash 函数的结果是否相同，就可以知道这两个文件是否相同。
加速查找速度的数据结构，常见的有两类
⑴树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是0(log2N);
关于树，后面会详细解说，这里我们先来看看Hash结构，以及为什么不用它作为索引结构。
⑵哈希Hash，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是0(1);

采用Hash进行检索效率非常高，基本上一次检索就可以找到数据，而B+树需要自顶向下依次查找，多次访问节点才能找到数据，中间需要多次I/o操作，从效率来说 Hash 比 B+树更快。
在哈希的方式下，一个元素k处于h(k)中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域映射到哈希表T[o…m-1]的槽位上。

上图中哈希函数h有可能将两个不同的关键字映射到相同的位置，这叫做碰撞，在数据库中一般采用链接法来解决。在链接法中，将散列到同一槽位的元素放在一个链表中，如下图所示:（了解过HashMap的应该不陌生）

那为什么不用Hash作为索引结构呢？
原因1: Hash索引仅能满足等于(=)、不等于 (<>)和IN查询。如果用哈希型的索引进行范围查询，时间复杂度会退化为o(n) ; 而树型的“有序”特性，依然能够保持o(log2N)的高效率。
原因2: Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY排序的情况下，使用Hash索引还需要对数据重新排序。
原因3: 对于联合索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
原因4:对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，
效率就会降低。这是因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

Hash索引适用存储引擎如表所示：

Hash索引的适用性：
Hash索引存在着很多限制，相比之下在数据库中B+树索引的使用面会更广，不过也有一些场景采用Hash索引效率更高，比如在键值型(Key-Value)数据库中， Redis存储的核心就是Hash表。
MySQL中的Memory存储引擎支持Hash存储，如果我们需要用到查询的临时表时，就可以选择Memory存储引擎，把某个字段设置为Hash索引，比如字符串类型的字段，进行Hash计算之后长度可以缩短到几个字节。当字段的重复度低，而且经常需要进行等值查询的时候，采用Hash索引是个不错的选择。
另外，InnoDB本身不支持Hash索引，但是提供自适应 Hash 索引(Adaptive Hash Index)。什么情况下才会使用自适应Hash 索引呢 ? 如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让B+树也具备了Hash索引的优点。
举例，

采用自适应 Hash 索引目的是方便根据 SQL 的查询条件加速定位到叶子节点，特别是当 B+ 树比较深的时候，通过自适应 Hash 索引可以明显提高数据的检索效率。
我们可以通过innodb_adaptive_hash_index变量来查看是否开启了自适应 Hash，比如：

show variables like '%adaptive_hash_index';

3、二叉搜索树

如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。

1. 二叉搜索树的特点

二叉树的定义很容易理解，在二叉树的基础上加一个额外的条件，就可以得到一种被称作二叉搜索树(binary search tree)的特殊二叉树。
这个要求就是：
若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值;
若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值；
它的左、右子树也分别为二叉排序树。

2. 查找规则

查找某个节点，必须从根节点开始查找。
①、查找值比当前节点值大，则搜索右子树;
②、查找值等于当前节点值，停止搜索（终止条件);
③、查找值小于当前节点值，则搜索左子树;

3. 二叉搜索树-插入节点

要插入节点，必须先找到插入的位置。与查找操作相似，由于二叉搜索树的特殊性,待插入的节点也需要从根节点开始进行比较，小于根节点则与根节点左子树比较，反之则与右子树比较，直到左子树为空或右子树为空，则插入到相应为空的位置。

4. 二叉搜索树-遍历节点

遍历树是根据一种特定的顺序访问树的每一个节点。比较常用的二叉树遍历有前序遍历，中序遍历和后序遍历。
而二叉搜索树最常用的是中序遍历，因为这样子遍历出来的顺序是从小到大升序的。
①、中序遍历:左子树——》根节点——》右子树
②、前序遍历:根节点——》左子树——》右子树
③、后序遍历:左子树——》右子树——》根节点

二叉搜索树 - 时间复杂度分析

对于一个有序的数组（排列），使用二分查找算法的时间复杂度能达到O（logn），每次查询都能排除一半的元素。但是二分查找算法有一个缺陷，那就是必须依赖一个有序的数组。而数组的缺陷就是容量是固定的，这样在插入、删除元素的时候就毕竟麻烦，比如删除某个元素时需要将该位置后面的所有元素往前移动一步；而数组的容量是固定的，一旦数组装满了，再去添加元素的时候就需要创建一个比之前元素更大的数组，同时将原来数组的元素迁移到新数组中。这样不太灵活，既不能快速插入也不能灵活扩容。
所以我们就想让二分查找算法能够像链表一样能够灵活插入并扩容，同时还能保持二分查找的高性能。二叉搜索树就是干这个的，它即能够灵活插入元素，又能保持二分的高性能查找。

二叉搜索树的缺陷

但是普通的二叉搜索树又有一个致命的缺陷，那就是在极端的情况下二叉树会退化成链表

这样它的查找性能就大为下降，达不到每次判断淘汰一半的效果，退化为O（N）。
为了提高查询效率，就需要减少磁盘IO数。为了减少磁盘IO的次数，就需要尽量降低树的高度，需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好。

4、AVL树

自然而然，我们就会想到将其优化为一颗在插入元素时，可以自动调整树的两边平衡的AVL平衡树。

AVL树，它在插入和删除节点时，总要保证任意节点左右子树的高度差不超过1。正是因为有这样的限制，插入一个节点和删除一个节点都有可能调整多个节点的不平衡状态。

虽然平衡树解决了二叉查找树退化为近似链表的缺点，能够把查找时间控制在O(logn)，不过却不是最佳的,
MySQL数据查询的时间主要依赖于磁盘I/O的次数，如果我们采用二叉树的形式，即使通过平衡二叉搜索树进行了改进，树的深度也是0(log2n)，当n比较大时，深度也是比较高的，比如下图的情况:

每访问一次节点就需要进行一次磁盘Ⅰ/0操作，对于上面的树来说，我们需要进行5次I/O操作。虽然平衡二叉树的效率高，但是树的深度也同样高，这就意味着磁盘I/O操作次数多，会影响整体数据查询的效率。
针对同样的数据，如果我们把二叉树改成M叉树(M>2）呢?当M=3时，同样的31个节点可以由下面的三叉树来进行存储:

可以看到此时树的高度降低了，树的分叉数M越大能存储的数据量N越大，以及存储相同数据量的情况下M叉树的高度会远小于二叉树的高度(M>2)。所以我们的优化方向可以是把树从“瘦高”变“矮胖”。

5、B树

B树的英文是Balance Tree，也就是多路平衡查找树。简写为B-Tree(注意横杠表示这两个单词连起来的意思，不是减号)。它的高度远小于平衡二叉树的高度，所以它就是我们要找的将树从“瘦高”变“矮胖”的数据结构。

B树作为多路平衡查找树，它的每一个节点最多可以包括M个子节点，M称为B树的阶。每个磁盘块中包括关键字和子节点的指针。如果一个磁盘块中包括了x个关键字，那么指针数就是x+1。
对于一个100阶的B树来说，如果有3层的话最多可以存储约100万的索引数据。对于大量的索引数据来说，采用B树的结构是非常适合的。

上面那张图所表示的B树就是一棵3阶的B树。我们可以看下磁盘块2，里面的关键字为(8，12)，它有3个孩子(3，5)，(9，10)和(13，15)，你能看到(3，5)小于8，(9，10)在8和12之间，而(13，15)大于12，刚好符合刚才我们给出的特征。
然后我们来看下如何用B树进行查找。假设我们想要查找的关键字是9，那么步骤可以分为以下几步:
1．我们与根节点的关键字(17，35）进行比较，9小于17那么得到指针P1;
2．按照指针P1找到磁盘块2，关键字为(8，12)，因为9在8和12之间，所以我们得到指针P2;
3．按照指针P2找到磁盘块6，关键字为(9，10)，然后我们找到了关键字9。
你能看出来在B树的搜索过程中，我们比较的次数并不少，但如果把数据读取出来然后在内存中进行比较，这个时间就是可以忽略不计的。而读取磁盘块本身需要进行V/O操作，消耗的时间比在内存中进行比较所需要的时间要多，是数据查找用时的重要因素。B树相比于平衡二叉树来说磁盘工/О操作要少，在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低，IO次数足够少，就可以提高查询性能。
小结：

B树在插入和删除节点的时候如果导致树不平衡，就通过自动调整节点的位置来保持树的自平衡。
关键字集合分布在整棵树中,即叶子节点和非叶子节点都存放数据。搜索有可能在非叶子节点结束
其搜索性能等价于在关键字全集内做一次二分查找。

下图也是一个典型的B树，可以看出来，B树的非叶子节点会存储data数据。这也是后来区分B树B+树的主要依据。

6、B+树

B+树也是一种多路搜索树，基于B树做出了改进，主流的DBMS都支持B+树的索引方式，比如 MySQL。

B+树和B树的差异在于以下几点:
1.有k个孩子的节点就有k个关键字。也就是孩子数量=关键字数，而B树中，孩子数量=关键字数+1。
2．非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大(或最小)。
3，非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既
保存索引，也保存数据记录。
4.．所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

下图就是一棵B+树，阶数为3，根节点中的关键字1、18、35分别是子节点(1，8，14)， (18，24，31)和(35，41，53)中的最小值。每一层父节点的关键字都会出现在下一层的子节点的关键字中，因此在叶子节点中包括了所有的关键字信息。并且每一个叶子节点都有一个指向下一个节点的指针，这样就形成了一个链表。

比如，我们想要查找关键字16，B+树会自顶向下逐层进行查找:
1．与根节点的关键字(1，18，35)进行比较，16在1和18之间，得到指针P1(指向磁盘块2)
2 . 找到磁盘块2，关键字为(1，8，14)，因为16大于14，所以得到指针P3(指向磁盘块7)
3．找到磁盘块7，关键字为(14，16，17)，然后我们找到了关键字16，所以可以找到关键字16所对应的数据。
整个过程一共进行了3次I/O操作，虽然看起来B+树和B树的查询过程差不多，但是B+树和B树有个根本的差异在于，B+树的中间节点并不直接存储数据。这也是B+Tree比B-Tree更适合作为文件索引系统的主要原因之一，那这样的好处是什么呢? （为什么B+Tree比B-Tree更适合作为文件索引系统）
首先，B+树查询效率更稳定。因为B树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问字节点才能找到关键字（虽然稳定，但是有时候查询的速度是比不上B树的）。
其次，B+树的查询效率更高。这是因为通常B+树比B树更矮胖(阶数更大，深度更低)，查询所需要的磁盘I/O次数也会更少。由于B树的磁盘块中即存放了数据记录对应的主键索引又存储了数据，在同样的磁盘页大小为16KB的情况下，非叶子节点能够存储的目录项就会变少（数据占了空间）；而由于B+树的非叶子节点只存储目录项，只有叶子节点才会存储数据，这就导致了同样层数的B+树可以存储更多的节点关键字（更胖）。
不仅是对单个关键字的查询上，在查询范围上，B+树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找。而在B树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。

B 树和 B+ 树都可以作为索引的数据结构，在 MySQL 中采用的是 B+ 树。但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。

7、思考题

思考题：为了减少IO，索引树会一次性加载到内存吗？

1、数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，可能超过几个G。
2、当我们利用索引查询时候，是不可能将全部几个c的索引都加载进内存的，我们能做的只能是:逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

思考题：B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO

MySQL默认的InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3。）
也就是说一个深度为3的B+Tree索引可以最多维护10^310^310^3=10亿条记录。(这里假定一个数据页也存储10^3条行记录数据了)
但实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。因为根页的数据在一开始已经加载了所以无需加载，那么就算最大加载4级，那也就需要加载最大3次。

思考题：Hash 索引与 B+ 树索引的区别

1、Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
2、Hash索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而B+树可以。对于联合索引来说，Hash索引在计算Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
3、Hash索引不支持ORDER BY排序，因为Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDER BY排序优化的作用。同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE后面后模糊查询（比如%结尾）的话就可以起到优化作用
4、InnoDB 不支持哈希索引（但是有个自适应Hash索引）

思考题：Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？

不是的，是一开始我们创建表的时候，每次插入数据，他背后都会去维护对应索引，如果又新加的二级索引才会再创建索引

2、InnoDB数据存储引擎结构

数据库中的存储结构：页

索引结构给我们提供了高效的索引方式，不过索引信息以及数据记录都是保存在系统文件上的，确切说是存储在页结构中。另一方面，索引是在存储引擎中实现的，MySQL服务器上的存储引擎负责对表中数据的读取和写入工作。不同存储引擎中存放的文件格式一般是不同的，甚至有的存储引擎比如Memory（内存级别）都不用磁盘来存储数据。
由于InnoDB是MySQL的默认存储引擎，所以这里主要解释InnoDB存储引擎的数据存储结构。

1、磁盘与内存交互基本单位:页

InnoDB的索引的结构是B+树，它的每一个节点都是一页（非叶子节点是目录页、叶子节点是数据页），这里我们先来看看这个页的结构是什么。
首先InnoDB将数据的存储划分为若干个页，页的大小默认为16KB。
以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。虽然记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次I/O操作)只能处理一行数据，效率会非常低。
也就是说，数据库管理存储空间的基本单位是页(Page)，数据库I/O操作的最小单位是页。一个页中可以存储多个行记录。

2、页结构概述

页a、页b、页c…页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，这样在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
比如说现在已经定位到某个页上，且该页的记录数为1000条。由于页内存储的数据记录是通过维护一条单项链表来达到逻辑连续的目的，我们在查找元素时如果以常规的遍历链表的方式去查找元素的话，效率就会很低；这个时候我们可以针对这1000条记录构建一个页目录，类似于升序的数组结构，这样我们就可以使用二分法来查找元素提高效率了。具体后面还会进行讲解。

3、页的大小

不同的数据库管理系统（简称DBMS）的页大小不同。比如在MySQL的InnoDB存储引擎中，默认页的大小是16KB，可以通过下面的命令来进行查看:

show variables like '%innodb_page_size%';

了解：SQL Server中页的大小为 8KB，而在oracle中用术语’’块’’(Block)来代表”页”，Oralce支持的块大小为2KB，4KB，8KB，16K8，32KB和64KB。

4、页的上层结构

另外在数据库中，还存在区（Extent)、段(Segment)和表空间（Tablespace)的概念。行、页、区、段、表空间的关系如下图所示:

区(Extent)是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是6416KB= 1MB。
段(Segment)由一个或多个区组成，*区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页)，不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。
表空间（Tablespace)是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间，用户表空间、撤销表空间、临时表空间等。

页的内部结构

页如果按类型划分的话，常见的有数据页（保存B+树节点，这里目录页也被划分为数据页了）、系统页、Undo页和事务数据页等。数据页是我们最常使用的页。数据页的16KB大小的存储空间被划分为七个部分，分别是文件头(File Header)、页头(Page Header)、最大最小记录(Infimum+supremum)、用户记录(User Records)、空闲空间(Free Space)、页目录(Page Directory)和文件尾(File Tailer) 。
页结构的示意图如下所示:

这7个部分作用分别如下，简单梳理如下表所示:

名称	占用大小	说明
File Header	38字节	文件头，描述页的信息
Page Header	56字节	页头,页的状态信息
lnfimum+Supremum	26字节	最大和最小记录，这是两个虚拟的行记录
User Records	不确定	用户记录，存储行记录内容
Free Space	不确定	空闲记录，页中还没有被使用的空间
Page Directory	不确定	页目录，存储用户记录的相对位置
File Trailer	8字节	文件尾,校验页是否完整

三大部分

我们可以把这7个结构分成3个部分

第1部分: File Header(文件头部）和File Trailer (文件尾部)

首先是文件通用部分，也就是文件头和文件尾。

① 文件头部信息

File Header里面又包含了如下内容。不同类型的页都会以File Header作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页是谁等，所有的数据页会组成一个双链表。这个部分占用固定的，大小是38字节。

FIL_PAGE_OFFSET (4字节) 页号

每一个页都有一个单独的页号，就跟你的身份证号码一样，InnoDB通过页号可以唯一定位一个页。

FIL_PAGE_TYPE (2字节)

这个代表当前页的类型。

FIL_PAGE_PREV (4字节)和FIL_PAGE_NEXT(4字节)

InnoDB都是以页为单位存放数据的，如果数据分散到多个不连续的页中存储的话需要把这些页关联起来，FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了，保证这些页之间不需要是物理上的连续,而是逻辑上的连续。

FIL_PAGE_SPACE_OR_CHKSUM(4字节)

代表当前页面的校验和（checksum）。
什么是校验和?
就是对于一个很长的字节串来说，我们会通过某种算法来计算一个比较短的值来代表这个很长的字节串，这个比较短的值就称为校验和。在比较两个很长的字节串之前，先比较这两个长字节串的校验和，如果校验和都不一样，则两个长字节串肯定是不同的，所以省去了直接比较两个比较长的字节串的时间损耗。（类似hash算法）
可以将两个页当初两个字符串进行校验，看看两个页是否相同。
文件头部和文件尾部都有属性:FIL_PAGE_SPACE_OR_CHKSUM
作用:
InnoDB存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候断电了，造成了该页传输的不完整。
为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），这时可以通过文件尾的校验和（checksum值）与文件头的校验和做比对，如果两个值不相等则证明页的传输有问题，需要重新进行传输，否则认为页的传输已经完成。
每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在FileTrailer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。这里，校验方式就是采用Hash 算法进行校验。

FIL_PAGE_LSN(8字节)

页面被最后修改时对应的日志序列位置（英文名是:Log Sequence Number)

②File Trailer(文件尾部)(8字节)

前4个字节代表页的校验和:
这个部分是和File Header中的校验和相对应的。
后4个字节代表页面被最后修改时对应的日志序列位置,（LSN）:
这个部分也是为了校验页的完整性的，如果首部和尾部的LSN值校验不成功的话，就说明同步过程出现了问题。

第二部分

第三部分

从数据页角度看B + 树如何查询

一棵B+树按照节点类型可以分成两部分:1．叶子节点，B+树最底层的节点，节点的高度为o，存储行记录。2．非叶子节点，节点的高度大于0，存储索引键和页面指针，并不存储行记录本身。

当我们从页结构来理解B+树的结构的时候，可以帮我们理解一些通过索引进行检索的原理:
1.B+树是如何进行记录检索的?
如果通过B+树的索引查询行记录，首先是从B+树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽(slot)采用二分查找的方式先找到一个粗略的记录分组然后再在分组中通过链表遍历的方式查找记录。
2.普通索引和唯一索引在查询效率上有什么不同?
我们创建索引的时候可以是普通索引，也可以是唯一索引，那么这两个索引在查询效率上有什么不同呢?
唯一索引就是在普通索引上增加了约束性，也就是关键字唯一，找到了关键字就停止检索。而普通索引，可能会存在用户记录中的关键字相同的情况，根据页结构的原理，当我们读取一条记录的时候，不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页加载到内存中进行读取。InnoDB存储引擎的页大小为16KB，在一个页中可能存储着上千个记录，因此在普通索引的字段上进行查找也就是在内存中多几次“判断下一条记录”的操作，对于CPU来说，这些操作所消耗的时间是可以忽略不计的。所以对一个索引字段进行检索，采用普通索引还是唯一索引在检索效率上基本上没有差别。

InnoDB的数据记录行格式

我们平时的数据以行为单位来向表中插入数据，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。
InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact（紧密）、Redundant（冗余）、Dynamic（动态）和Compressed（压缩）行格式。
查看MySQL8 与 MySQL5.7的默认行格式都是Dynamic:

mysql> select @@innodb_default_row_format;
# 查询单张表行格式
mysql> show table status like 'departments' \G

Compact

由于Compact跟Dynamic两个格式差不多，所以这里先讲一下Compact
在创建或修改表的语句中指定行格式:

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

在MySQL 5.1版本中，默认设置为Compact行格式。一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。

记录的额外信息

1、变长字段长度列表

MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。
注意:这里面存储的变长长度和字段顺序是反过来的。比如两个varchar字段在表结构的顺序是a(10), b(15) 表示 a字段长度是10，b字段长度15。那么在变长字段长度列表中存储的长度顺序就是15，10，是反过来的。
以record_test_table表中的第一条记录举例:

因为record_test_table表的col1、col2、col4列都是VARCHAR(8)类型的，所以这三个列的值的长度都需要保存在记录开头处，注意record_test_table表中的各个列都使用的是ascii字符集（每个字符只需要1个字节来进行编码）。
又因为这些长度值需要按照列的逆序存放，所以最后变长字段长度列表的字节串用十六进制表示的效果就是（各个字节之间实际上没有空格，用空格隔开只是方便理解）：06 04 08

2、NULL值列表

Compact行格式会把可以为NULL的列统一管理起来，存在一个标记为NULL值列表中。如果表中没有允许存储 NULL 的列，则NULL值列表也不存在了。
为什么定义NULL值列表?
之所以要存储NULL是因为数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，虽然能达到效果，但是这样很浪费空间，所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据，格式如下:
1.二进制位的值为1时,代表该列的值为NULL。
2.二进制位的值为0时，代表该列的值不为NULL。
例如:字段a、b、c，其中a是主键，在某一行中存储的数依次是a=1、b=null、c=2。那么Compact行格式中的NULL值列表中存储:01。第一个0表示c不为null，第二个1表示b是null。这里之所以没有a是因为数据库会自动跳过主键，因为主键肯定是非NULL且唯一的,在NULL值列表的数据中就会自动跳过主键。
record_test_table的两条记录的NULL值列表就如下:

记录的真实数据

记录的真实数据除了我们自己定义的列的数据以外，还会有三个隐藏列:

实际上这几个列的真正名称其实是:DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。
事务ID和回滚指针在后面讲解。

在Windows操作系统下，可以选择通过程序UltraEdit打开表空间文件mytest.ibd这个二进制文件。内容如下:

其实就跟分析class字节码文件差不多….

区、段与碎片区

为什么要有区?

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录,然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，就是所谓的随机I/0。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/0是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/0。
这样利用了磁盘的预读特性.
引入区的概念，一个区就是在物理位置上连续的64个页。因为InnoDB 中的页大小默认是16KB，所以一个区的大小是6416KB=1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足以填充满整个区)，但是从性能角度看，*可以消除很多的随机I/O，功大于过!

这里是连续的64个页，但是具体的两个页之间还是用指针相连的。保证一大块区域连续。

数据页加载的三种方式

InnoDB从磁盘中读取数据的最小单位是数据页。
对于MySQL存放的数据，逻辑概念上我们称之为表，在磁盘等物理层面而言是按数据页形式进行存放的，当其加载到MySQL中我们称之为缓存页。
假如现在你想得到的id = xoxx的数据，该数据是这个数据页众多行中的一行。如果缓冲池中没有该页数据，那么缓冲池有以下三种读取数据的方式,每种方式的读取效率都是不同的:

情况一、内存读取

如果该数据存在于内存中，基本上执行时间在1ms左右，效率还是很高的。

情况二、随机读取

如果数据没有在内存中，就需要在磁盘上对该页进行查找，整体时间预估在10ms左右，这10ms 中有6ms是磁盘的实际繁忙时间(包括了寻道和半圈旋转时间），有3ms是对可能发生的排队时间的估计值，另外还有1ms的传输时间，将页从磁盘服务器缓冲区传输到数据库缓冲区中。也就是说这10ms中只有1ms是跟我们数据库相关的。
这10ms 看起来很快，但实际上对于数据库来说消耗的时间已经非常长了，因为这还只是一个页的读取时间。我们当然不希望时间都用去查到数据上，而是将这部分时间也充分利用起来，所以可以将其改写为顺序读取，降低在磁盘空间中查找数据的时间。

情况三、顺序读取

顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘I/O操作了。如果一个磁盘的吞吐量是40MB/S，那么对于一个16KB大小的页来说，一次可以顺序读取2560 (40MB/16KB)个页，相当于一个页的读取时间为0.4ms。采用批量读取的方式，即使是从磁盘上进行读取，效率也比从内存中只单独读取一个页的效率要高。

为什么要有段?

对于范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，但是如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了，因为叶子节点存储数据（数据页有数据）而非叶子节点不存储数据（目录页无数据）。
所以InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段( segment)，存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点。
在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。这从一定程度上简化了DBA对于段的管理。
段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

为什么要有碎片区?

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M (6416Kb=1024Kb）存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间么? 以后每次添加一个索引都要多申请2M的存储空间么? 这对于存储记录比较少的表简直是天大的浪费。
这个问题的症结在于到现在为止我们介绍的区都是非常纯粹的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。
为了考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB提出了一个碎片(fragment)区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。*碎片区直属于表空间，并不属于任何一个段。
所以此后为某个段分配存储空间的策略是这样的:

在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的
当某个段已经占用了32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。

所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。

区的分类

区大体上可以分为4种类型:

空闲的区(FREE): 现在还没有用到这个区中的任何页面。
有剩余空间的碎片区(FREE_FRAG): 表示碎片区中还有可用的页面。
没有剩余空间的碎片区(FULL_FRAG)︰表示碎片区中的所有页面都被使用，没有空闲页面。
附属于某个段的区(FSEG):每一个索引都可以分为叶子节点段和非叶子节点段。

处于FREE、FREE_FRAG以及FULL_FRAG这三种状态的区都是独立的，直属于表空间。而处于FSEG状态的区是附属于某个段的。

如果把表空间比作是一个集团军，段就相当于师，区就相当于团。一般的团都是隶属于某个师的，就像是处于FSEG的区全都隶属于某个段，而处于FREE、FREE_FRAG以及FULL_FRAG这三种状态的区却直接隶属于表空间，就像独立团直接听命于军部一样。

扩展

表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。
表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。表空间数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间 (Systemtablespace)、独立表空间(File-per-table tablespace)、撤销表空间(Undo Tablespace)和临时表空间(Temporary Tablespace）等。

独立表空间

独立表空间，即每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间(即:单表)可以在不同的数据库之间进行迁移。
空间可以回收(DROPTABLE操作可自动回收表空间;其他情况，表空间不能自己回收)。如果对于统计分析或是日志表，删除大量数据后可以通过: alter table TableName engine=innodb;回收不用的空间。对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
独立表空间结构
独立表空间由段、区、页组成。前面已经讲解过了。
真实表空间对应的文件大小
我们到数据目录里看，会发现一个新建的表对应的.ibd文件只占用了96K，才6个页面大小(MySQL5.7，在MySQL8.0中ibd文件占 7个页面大小。原因是8.0版本的.idb 还存了表结构，5.7版本存储表结构的.frm在8.0版本中取消了)，这是因为一开始表空间占用的空间很小，因为表里边都没有数据。不过别忘了这些.ibd文件是自扩展的，随着表中数据的增多，表空间对应的文件也逐渐增大。

查看InnoDB的表空间类型:

# 查看是否独立表空间
mysql> show variables like 'innodb_file_per_table';

系统表空间

系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，这部分是独立表空间中没有的。
lnnoDB数据字典
每当我们向一个表中插入一条记录的时候，MySQL校验过程如下:
先要校验一下插入语句对应的表存不存在，插入的列和表中的列是否符合，如果语法没有问题的话，还需要知道该表的聚簇索引和所有二级索引对应的根页面是哪个表空间的哪个页面，然后把记录插入对应索引的B+树中。
所以说，MySQL除了保存着我们插入的用户数据之外，还需要保存许多额外的信息，比方说:

-某个表属于哪个表空间，表里边有多少列
-表对应的每一个列的类型是什么
-该表有多少索引，每个索引对应哪几个字段，该索引对应的根页面在哪个表空间的哪个页面
-该表有哪些外键，外键对应哪个表的哪些列
-某个表空间对应文件系统上文件路径是什么
- ...

上述这些数据并不是我们使用INSERT语句插入的用户数据，实际上是为了更好的管理我们这些用户数据而不得已引入的一些额外数据，这些数据也称为**元数据**。InnoDB存储引擎特意定义了一些列的内部系统表(internalsystem table)来记录这些这些元数据:

表名	描述
`SYS_TABLES`	整个InnoDB存储引擎中所有的表的信息
`SYS_COLUMNS`	整个InnoDB存储引擎中所有的列的信息
`SYS_INDEXES`	整个InnoDB存储引擎中所有的索引的信息
`SYS_FIELDS`	整个InnoDB存储引擎中所有的索引对应的列的信息
SYS_FOREIGN	整个InnoDB存储引擎中所有的外键的信息
SYS_FOREIGN_COLS	整个InnoDB存储引擎中所有的外键对应列的信息
SYS_TABLESPACES	整个InnoDB存储引擎中所有的表空间信息
SYS_DATAFILES	整个InnoDB存储引擎中所有的表空间对应文件系统的文件路
SYS_VIRTUAL	整个InnoDB存储引擎中所有的虚拟生成列的信息

这些系统表也被称为数据字典，它们都是以**B+**树的形式保存在系统表空间的某些页面中，其中SYS_TABLES、SYS_COLUNNS、SYS_INDEXES、SYS_FIELDS这四个表尤其重要，称之为基本系统表(basic system tables)

注意:用户是**不能直接访问**InnoDB的这些内部系统表，除非你直接去解析系统表空间对应文件系统上的文件。不过考虑到查看这些表的内容可能有助于大家分析问题，所以在系统数据库**information_schema**中提供了一些以innodb_sys开头的表:

mysql> USE information_schema ;
Database changed
mysql> SHOW TABLES LIKE 'innodb_sys%';
+--------------------------------------------+
| Tables_in_information_schema (innodb_sys%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES                       |
| INNODB_SYS_VIRTUAL                         |
| INNODB_SYS_INDEXES                         |
| INNODB_SYS_TABLES                          |
| INNODB_SYS_FIELDS                          |
| INNODB_SYS_TABLESPACES                     |
| INNODB_SYS_FOREIGN_COLS                    |
| INNODB_SYS_COLUMNS                         |
| INNODB_SYS_FOREIGN                         |
| INNODB_SYS_TABLESTATS                      |
+--------------------------------------------+
10 rows in set (0.00 sec)

在information_schema数据库中的这些以INNODB_SYS开头的表并不是真正的内部系统表(内部系统表就是我们上边以SYS开头的那些表)，而是在存储引擎启动时读取这些以SYS开头的系统表，然后填充到这些以
INNODB_SYS开头的表中。以INNODB_SYS开头的表和以SYS开头的表中的字段并不完全一样，但供大家参考已经足矣。

3、索引的创建与设计原则

1. 索引的声明与使用

1. 1 索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照作用字段个数进行划分，分成单列索引和联合索引。

普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表student的字段name上建立一个普通索引，查询记录时就可以根据该索引进行查询。

唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。
例如，在表student的字段email中创建唯一性索引，那么字段email的值就必须是唯一的。通过唯一性索引，可以更快速地确定某条记录。

主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。

单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。

多列(组合、联合)索引
多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id、name和gender上建立一个多列索引idx_id_name_gender，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。
全文索引

全文索引(也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。
使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引刚中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，表student的字段information是TEXT类型,该字段包含了很多文字信息。在字段information上建立全文索引后，可以提高查询字段information的速度。全文索引典型的有两种类型:自然语言的全文索引和布尔全文索引。

·自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过50%的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

MySQL数据库从3.23.23版开始支持全文索引，但MySQL5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。在5.7.6版本，MySQL内置了ngram全文解析器，用来支持亚洲语种的分词。测试或使用全文索引时，要先看一下自己的MySQL版本、存储引擎和数据类型是否支持全文索引。
随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr、ElasticSearch等专门的搜索引擎所替代。

补充：空间索引
使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEONETRY、POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

小结：不同的存储引擎支持的索引类型也不一样
InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash索引；
MyISAM ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive ：不支持 B-tree、Hash、Full-text 等索引；

1. 2 创建索引

这里

MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在已经存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。

1. 创建表的时候创建索引

使用CREATE TABLE创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。
举例：

CREATE TABLE dept(
  dept_id INT PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR( 20 )
);
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR( 20 ) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

但是，如果显式创建表时创建索引的话，基本语法格式如下：

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL][INDEX |KEY][index_name] (col_name [length]) [ASC | DESC]

UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；
index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC或DESC指定升序或者降序的索引值存储。

1.创建普通索引

在book表中的year_publication字段上建立普通索引，SQL语句如下：

#显式的方式创建
#1创建普通的索引
CREATE TABLE book (
    book_id INT ,
    book_name VARCHAR (100) ,
    AUTHORS VARCHAR (100) ,
    info VARCHAR(100) ,
    COMMENT VARCHAR (100) ,
    year_publication YEAR,
    #声明索引
    INDEX idx_bname (book_name))
;
#通过命令查看索引
#方式l:
mysql> show  create table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `book_id` int(11) DEFAULT NULL,
  `book_name` varchar(100) DEFAULT NULL,
  `AUTHORS` varchar(100) DEFAULT NULL,
  `info` varchar(100) DEFAULT NULL,
  `COMMENT` varchar(100) DEFAULT NULL,
  `year_publication` year(4) DEFAULT NULL,
  KEY `idx_bname` (`book_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 方式2： 
show index from book;

show index from book

太好用了，必须要会

2.创建唯一索引

举例：

# 创建唯一索引
CREATE TABLE book (
    book_id INT ,
    book_name VARCHAR (100) ,
    #声明索引
    UNIQUE INDEX uk_idx_bname (book_name))
;
show index from book;

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

SHOW INDEX FROM test1 \G

3.主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法：

CREATE TABLE book (
  # 创建主键索引
  book_id INT  primary key,
  book_name VARCHAR (100)
;

删除主键索引：

ALTER TABLE student
drop PRIMARY KEY ;

修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引

4.创建组合索引

# 创建唯一索引
CREATE TABLE book (
  book_id INT ,
  book_name VARCHAR (100) ,
  author VARCHAR (100) ,
  #声明索引
  INDEX union_key_ba (book_name,author))
  ;
show index from book;

5.创建全文索引
举例1：创建表test4，在表中的info字段上建立全文索引，SQL语句如下：

CREATE TABLE test4(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(255),
    FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;

在MySQL5.7及之后版本中可以不指定最后的ENGINE了，因为在此版本中InnoDB支持全文索引。
举例2：

CREATE TABLE articles (
     id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
     title VARCHAR (200),
     body TEXT,
     FULLTEXT index (title, body)
) ENGINE = INNODB ;

创建了一个给title和body字段添加全文索引的表。
举例3：

CREATE TABLE `papers` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `title` varchar(200) DEFAULT NULL,
     `content` text,
     PRIMARY KEY (`id`),
     FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

不同于like方式的的查询：

SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;

全文索引用match+against方式查询：

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

注意点:

使用全文索引前，搞清楚版本支持情况；
全文索引比 like + % 快 N 倍，但是可能存在精度问题；
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

6.创建空间索引
空间索引创建中，要求空间类型的字段必须为非空。
举例：创建表test5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：

2.在已经存在的表上创建索引

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下： ``` ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],…) [ASC | DESC]

ALTER TABLE book ADD INDEX index_name(book_name); ALTER TABLE book ADD UNIQUE uk_idx_bname(book_name); ALTER TABLE book ADD UNIQUE mul_bid_na(book_name,author);



2.  使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],…) [ASC | DESC]

create 索引类型索引名称 on 表名(字段); create index idx_cmt on book(comment); create unique index idx_cmt on book(comment); create index idx_cmt on book(comment,author);



<a name="2cd09ab7"></a>
#### 3 删除索引

1.  使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;



2.  使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;



> 在需要大量删除表数据，修改表数据时，可以考虑先删除索引。等修改完数据之后再插入


> AUTO_INCREMENT 约束字段的唯一索引不能被删除


> 提示 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。


<a name="c608d6a8"></a>
## 2.MySQL 8. 0 索引新特性

<a name="aa076654"></a>
### 2. 1 支持降序索引
降序索引以降序存储键值。虽然在语法上，从MySQL 4版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的，直到MySQL 8.x版本才开始真正支持降序索引(仅限于InnoDB存储引擎)。

MySQL在8.0**版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率**。在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

举例：分别在MySQL 5. 7 版本和MySQL 8. 0 版本中创建数据表ts 1 ，结果如下：

CREATE TABLE ts1(a int, b int, index idx_a_b(a, b desc) ) ;


在MySQL 5. 7 版本中查看数据表ts 1 的结构，结果如下：

mysql> show create table ts1 \G * 1. row * Table: ts1 Create Table: CREATE TABLE ts1 ( a int(11) DEFAULT NULL, b int(11) DEFAULT NULL, KEY idx_a_b (a,b) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)


从结果可以看出，索引仍然是默认的升序。

在MySQL 8. 0 版本中查看数据表ts 1 的结构，结果如下：

mysql> show create table ts1 \G * 1. row * Table: ts1 Create Table: CREATE TABLE ts1 ( a int DEFAULT NULL, b int DEFAULT NULL, KEY idx_a_b (a,b DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 1 row in set (0.00 sec)


从结果可以看出，索引已经是降序了。下面继续测试降序索引在执行计划中的表现。

分别在MySQL 5. 7 版本和MySQL 8. 0 版本的数据表ts 1 中插入 800 条随机数据，执行语句如下：

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

DELIMITER // CREATE PROCEDURE ts_insert () BEGIN DECLARE i INT DEFAULT 1; WHILE i < 800 DO INSERT INTO ts1 SELECT rand() 80000, rand() 80000;

    SET i = i + 1;

END WHILE;
COMMIT;

END // DELIMITER;

调用

CALL ts_insert ();


在MySQL 5.7版本中查看数据表ts1的执行计划，结果如下：

mysql> explain select * from ts1 order by a, b desc limit 5; +——+———+—————+——————————————-+ | id | rows | filtered | Extra | +——+———+—————+——————————————-+ | 1 | 1598 | 100.00 | Using index; Using filesort | +——+———+—————+——————————————-+ 1 row in set, 1 warning (0.01 sec)


从结果可以看出，执行计划中扫描数为 1598，而且使用了Using filesort。

> 提示 Using filesort是MySQL中一种速度比较慢的外部排序，能避免是最好的。多数情况下，管理员可以通过优化索引来尽量避免出现Using filesort，从而提高数据库执行速度。


在MySQL 8.0版本中查看数据表ts1的执行计划。

mysql> explain select * from ts1 order by a, b desc limit 5; +——+————-+——-+—————+——————-+ | id | key |rows | filtered | Extra | +——+————-+——-+—————+——————-+ | 1 | idx_a_b | 5 | 100.00 | Using index | +——+————-+——-+—————+——————-+ 1 row in set, 1 warning (0.03 sec)


从结果可以看出，执行计划中扫描数为 5 ，而且没有使用Using filesort。

> 注意 降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。例如，上述查询排序条件改为order by a desc, b desc，MySQL 5.7的执行计划要明显好于MySQL 8.0。


<a name="4c90a086"></a>
### 2.2 隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持`隐藏索引（invisible indexes）`，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引）确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。`这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除`。

同时，你想验证某个索引删除之后的`查询性能影响`，就可以暂时先隐藏该索引

> 注意:
>  
> 主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。


索引默认是可见的，在使用CREATE TABLE，CREATE INDEX或者ALTERTABLE等语句时可以通过VISIBLE或者INVISIBLE关键词设置索引的可见性。

创建表时直接创建

**1.在MySQL中创建**

隐藏索引通过SQL语句INVISIBLE来实现，其语法形式如下：

CREATE TABLE tablename( propname1 type1 [ CONSTRAINT1],propname2 type2[ CONSTRAINT2], … propnamen typen, INDEX indexname ]) INVISIBLE );

create table book2( id int primary key, book_name varchar(32) );


上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引为不可见索引。

**2.在已经存在的表上创建**

可以为已经存在的表设置隐藏索引，其语法形式如下：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length] [ASC | DESC] ,…) [INVISIBLE|VISIBLE]


**3.通过ALTER TABLE语句创建**

ALTER TABLE book2 ADD index idx_name(book_name) INVISIBLE;


**4.切换索引可见状态**

已存在的索引可通过如下语句切换可见状态：

ALTER TABLE book2 alter index idx_name visible; # 切换成非隐藏索引 ALTER TABLE book2 alter index idx_name invisible; # 切换成非隐藏索引


如果将index_cname索引切换成可见状态，通过explain查看执行计划，发现优化器选择了idx_name索引。

> 注意 当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。


通过设置隐藏索引的可见性可以查看索引对调优的帮助。

**5.使隐藏索引对查询优化器可见**

> 只是有个全局的地方设置可见性，没什么用


在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关（use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes设置为off(默认)，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

（ 1 ）在MySQL命令行执行如下命令查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G * 1. row * @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on 1 row in set (0.12 sec)


在输出的结果信息中找到如下属性配置。

```properties
use_invisible_indexes=off

此属性配置值为off，说明隐藏索引默认对查询优化器不可见。

（ 2 ）使隐藏索引对查询优化器可见，需要在MySQL命令行执行如下命令：

mysql> set session optimizer_switch="use_invisible_indexes=on" ;
Query OK, 0 rows affected (0.06 sec)

SQL语句执行成功，再次查看查询优化器的开关设置。

此时，在输出结果中可以看到如下属性配置。

mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.03 sec)

use_invisible_indexes属性的值为on，说明此时隐藏索引对查询优化器可见。

3. 索引的设计原则

3. 1 数据准备

第 1 步：创建数据库、创建表

CREATE DATABASE atguigudb1;
USE atguigudb1;

#1.创建学生表和课程表
CREATE TABLE `student_info` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR( 20 ) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT( 11 ) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT= 1 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
`course_id` INT NOT NULL ,
`course_name` VARCHAR( 40 ) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT= 1 DEFAULT CHARSET=utf8;

第 2 步：创建模拟数据必需的存储函数

#函数 1 ：创建随机产生字符串函数

DELIMITER //
CREATE FUNCTION rand_string(n INT)
    RETURNS VARCHAR( 255 ) #该函数会返回一个字符串
BEGIN
    DECLARE chars_str VARCHAR( 100 ) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR( 255 ) DEFAULT '';
    DECLARE i INT DEFAULT 0 ;
    WHILE i < n DO
        SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR( 1 +RAND()* 52 ), 1 ));
        SET i = i + 1 ;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;

#函数 2 ：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT( 11 )
BEGIN
DECLARE i INT DEFAULT 0 ;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+ 1 )) ;
RETURN i;
END //
DELIMITER ;

创建函数，假如报错：

This function has none of DETERMINISTIC......

由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。

主从复制，主机会将写操作记录在bin-log日志中。从机读取bin-log日志，执行语句来同步数据。如果使用函数来操作数据，会导致从机和主键操作时间不一致。所以，默认情况下，mysql不开启创建函数设置。

查看mysql是否允许创建函数：

show variables like 'log_bin_trust_function_creators';

命令开启：允许创建函数设置：

set global log_bin_trust_function_creators= 1 ;  # 不加global只是当前窗口有效。

mysqld重启，上述参数又会消失。永久方法：
- windows下：my.ini[mysqld]加上：
```
log_bin_trust_function_creators= 1
```

linux下：/etc/my.cnf下my.cnf[mysqld]加上：
```
log_bin_trust_function_creators= 1
```

第 3 步：创建插入模拟数据的存储过程

#存储过程 1 ：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN
    DECLARE i INT DEFAULT 0 ;
    SET autocommit = 0 ;  #设置手动提交事务
    REPEAT #循环
    SET i = i + 1 ;  #赋值
    INSERT INTO course (course_id, course_name ) VALUES
    (rand_num( 10000 , 10100 ),rand_string( 6 ));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;  #提交事务
END //
DELIMITER ;

#存储过程 2 ：创建插入学生信息表存储过程

DELIMITER //
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0 ;
    SET autocommit = 0 ;  #设置手动提交事务
    REPEAT #循环
    SET i = i + 1 ;  #赋值
    INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES
    (rand_num( 10000 , 10100 ),rand_num( 10000 , 10200 ),rand_num( 1 , 200000 ),rand_string( 6 ));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;  #提交事务
END //
DELIMITER ;

第 4 步：调用存储过程

CALL insert_course( 100 );
CALL insert_stu( 1000000 );

3.2 哪些情况适合创建索引

1.字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2.频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

比如student_info数据表（含 100 万条数据），假设我们想要查询 student_id=123110 的用户信息。

3.经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

4.UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行

SQL 语句：

SELECT DISTINCT( student_id)FROM 'student_info `;

运行结果（ 600637 条记录，运行时间 0.683s）：

... 加索引语句
SELECT DISTINCT( student_id)FROM 'student_info `;

如果我们对 student_id 创建索引，再执行 SQL 语句：

运行结果（ 600637 条记录，运行时间 0.010s）：

你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照递增的顺序进行展示的。这是因

为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。因为紧挨着所以去重特别方便

6.多表 JOIN 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增

长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，

没有 WHERE 条件过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 course_id 在

student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

举个例子，如果我们只对 student_id 创建索引，执行 SQL 语句：

SELECT course_id,name，student_info.student_id, course_name
FROM student_info JOIN course
ON student_info .course_id = course.course_id
WHERE name = '462eed7ac6e791292a79' ;

运行结果（ 1 条数据，运行时间 0.189s）：

这里我们对 name 创建索引，再执行上面的 SQL 语句，运行时间为 0.002s。

7.使用列的类型小的创建索引

我们这里所说的类型大小指的就是该类型表示的数据范围的大小。

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、
BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT 就不要使用INT。这是因为:

数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

8.使用字符串前缀创建索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题:

B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

create table shop(address varchar( 120 ) not null);

alter table shop add index(address( 12 ));

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字段的散列度(选择性)会降低。 怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度：

select count(distinct address) / count(*) from shop;

通过不同长度去计算，与全表的选择性对比：

公式：

count(distinct left(列名, 索引长度))/count(*)

例如：

select count(distinct left(address, 10 )) / count(*) as sub10, -- 截取前 10 个字符的选择度
count(distinct left(address, 15 )) / count(*) as sub11, -- 截取前 15 个字符的选择度
count(distinct left(address, 20 )) / count(*) as sub12, -- 截取前 20 个字符的选择度
count(distinct left(address, 25 )) / count(*) as sub13 -- 截取前 25 个字符的选择度
from shop;

引申另一个问题：索引列前缀对排序的影响

如果使用了索引列前缀，比方说前边只把address列的前12个字符放到了二级索引中，下边这个查询可能就有点儿尴尬了:

SELECT * FROM shop
ORDER BY address  # 这个地方order by 就不准了 如果用前12个建立索引的话
LIMIT 12;

因为二级索引中不包含完整的address列信息，所以无法对前12个字符相同，后边的字符不同的记录进行排序，也
就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序。

拓展：Alibaba《Java开发手册》

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

9.区分度高(散列性高)的列适合作为索引

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

可以使用公式 select count(distinct a)/count(*) from t1计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。

拓展:联合索引把区分度高(散列性高)的列放在前面。

10.使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于”最左前缀原则”，可以增加联合索引的使用率。

11.在多个字段都要创建索引的情况下，联合索引优于单值索引

3. 3 限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因:

① 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

② 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。

③优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

3. 4 哪些情况不适合创建索引

1. 在where中使用不到的字段，不要设置索引

WHERE条件(包括GROUP BY、ORDER BY)里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。举个例子:

SELECT course_id,student_id, create_time
FROM student_info
WHERE student_id = 41251;

因为我们是按照student_id来进行检索的，所以不需要对其他字段创建索引，即使这些字段出现在SELECT 字段中。

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

如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

举例：创建表 1 ：

CREATE TABLE t_without_index(
a INT PRIMARY KEY AUTO_INCREMENT,
b INT
);

提供存储过程 1 ：

#创建存储过程

DELIMITER //
CREATE PROCEDURE t_wout_insert()
BEGIN
    DECLARE i INT DEFAULT 1 ;
    WHILE i <= 900
    DO
        INSERT INTO t_without_index(b) SELECT RAND()* 10000 ;
        SET i = i + 1 ;
    END WHILE;
    COMMIT;
END //
DELIMITER ;

#调用
CALL t_wout_insert();

创建表 2 ：

CREATE TABLE t_with_index(
a INT PRIMARY KEY AUTO_INCREMENT,
b INT,
INDEX idx_b(b)
);

创建存储过程 2 ：

#创建存储过程

DELIMITER //
CREATE PROCEDURE t_with_insert()
BEGIN
DECLARE i INT DEFAULT 1 ;
WHILE i <= 900
DO
INSERT INTO t_with_index(b) SELECT RAND()* 10000 ;
SET i = i + 1 ;
END WHILE;
COMMIT;
END //
DELIMITER ;
#调用
CALL t_with_insert();

查询对比：

你能看到运行结果相同，但是在数据量不大的情况下，索引就发挥不出作用了。

mysql> select * from t_without_index where b = 9879 ;
+------+------+
| a | b |
+------+------+
| 1242 | 9879 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t_with_index where b = 9879 ;
+-----+------+
| a | b |
+-----+------+
| 112 | 9879 |
+-----+------+
1 row in set (0.00 sec)

结论：在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。

3. 有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的”性别“字段上只有“男”与“·女”两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

举例 1 ：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

举例 2 ：假设有一个学生表，学生总数为 100 万人，男性只有 10 个人，也就是占总人口的 10 万分之 1 。

学生表 student_gender 结构如下。其中数据表中的 student_gender 字段取值为 0 或 1 ， 0 代表女性， 1 代表男性。

CREATE TABLE student_gender(
student_id INT( 11 ) NOT NULL,
student_name VARCHAR( 50 ) NOT NULL,
student_gender TINYINT( 1 ) NOT NULL,
PRIMARY KEY(student_id)
)ENGINE = INNODB;

如果我们要筛选出这个学生表中的男性，可以使用：

SELECT * FROM student_gender WHERE student_gender = 1

运行结果（ 10 条数据，运行时间 0.696s）：

MySQL干货详解进阶篇之二 - 图62

你能看到在未创建索引的情况下，运行的效率并不高。如果针对 student_gender字段创建索引呢?

SELECT * FROM student gender WHERE student_gender = 1

同样是10条数据，运行结果相同，时间却缩短到了0.052s，大幅提升了查询的效率。

其实通过这两个实验你也能看出来，索引的价值是帮你快速定位。如果想要定位的数据有很多，那么索引就失去了它的使用价值，比如通常情况下的性别字段。

在这个例子中，索引可以快速定位出男生是有用的。

4.避免对经常更新的表创建过多的索引

第一层含义: 频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

第二层含义: 避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。

5.不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

6.删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7.不要定义冗余或重复的索引

① 冗余索引

举例：建表语句如下

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR( 100 ) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR( 11 ) NOT NULL,
    country varchar( 100 ) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name( 10 ), birthday, phone_number),
    KEY idx_name (name( 10 ))
);

我们知道，通过idx_name_birthday_phone_number索引就可以对name列进行快速搜索，再创建一个专门针对name列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

② 重复索引

另一种情况，我们可能会对某个列重复建立索引，比方说这样：

CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
);

我们看到，col 1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

3.5小结

索引是一把双刃剑，可提高查询效率，但也会降低插入和更新的速度并占用磁盘空间。

选择索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能拘泥于上面的准则，在以后的学习和工作中进行不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。

MySQL干货详解进阶篇之二