MySQL

【MySQL】索引
【MySQL】执行流程和缓冲池
【MySQL】redo log
【MySQL】undo log
【MySQL】事务

本文参考

1. MySQL 是怎样运行的：从根儿上理解 MySQL
2. 尚硅谷康师傅的 MySQL课程

通过索引的生成推演可以更加清楚的认识索引，在认识索引的结构之后，索引相关的问题就迎刃而解了。最后对比一下，在 MyISAM 和 InnoDB 两种搜索引擎下的索引的异同

1、为什么使用索引

没有索引的时候是怎么查找记录的

以搜索条件为对某个列精确匹配的情况为例：

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

当数据量较少时，在一个页中查找

• 以主键为搜索条件
  可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
• 以其他列为搜索条件
  只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件，效率非常低

当数据量非常多时，在多个页中查找

1. 定位到记录所在的页
2. 从所在的页内中查找相应的记录

不论是根据主键列或者其他列的值进行查找，由于并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中使用单页面查找方式去查找指定的记录，这种方式非常耗时。

2、索引的优缺点

索引（Index）是帮助MySQL高效获取数据的数据结构

2.1、优点

• 提高数据检索的效率，降低数据库的IO成本
• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
• 加速表和表之间的连接
• 减少查询中分组和排序的时间

2.2、缺点

• 创建索引和维护索引要耗费时间
• 索引需要占磁盘空间，如果有大量的索引，索引文件就可能比数据文
  件更快达到最大文件尺寸
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度

在突发插入频繁的情况下，由于索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，可以先删除表中的索引，然后插入数据，插入完成后再创建索引

3、InnoDB 中的索引

3.1、索引的推演

建一个表

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

2个INT类型的列
1个CHAR(1)类型的列
规定了c1列为主键
表使用Compact行格式来实际存储记录

简化的行格式示意图如下：

将行格式示意图竖起来

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

建立一个索引：将数据按主键的大小串联成一个单向链表

插入数据 =》移动记录 =》页分裂

• 假设一个页只能存储三条记录，实际上页能存储很多数据
• 下一个数据页中的用户记录的主键值必须大于上一页中用户记录的主键值

再插入一条主键为 4 的数据，为维持索引规则，进行了记录移动，这个过程称为页分裂

给所有的页建立一个目录

由于数据页不是连续的，如果想要从这么多页中根据主键值快速定位某些记录所在的页，我们需要给页做一个目录：

• 每个页就是一个目录项
• key：页的用户记录中最小的主键值
• page_no：页号
  

建好目录，这个目录就是索引

如下图，就可以实现根据主键对记录进行快速查找。比如：查找主键值为 20 的记录，具体查找过程分两步：

1. 先从目录项中根据二分法快速确定出主键值为 20 的记录在 目录项3 中（因为 12 < 20 <
   209 ），它对应的页是页9。
2. 再根据前边说的在页中查找记录的方式去页9中定位具体的记录。

当数据量更大，需要多个目录页

用更高层级的目录页管理目录页

简化图描述索引目录层级

3.2、索引常见概念

聚簇索引

所有的用户记录都存放在叶子结点，数据即索引。索引即数据

特点

1. 页内的记录是按照主键的大小顺序排成一个单向链表
2. 存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
4. B+树的叶子节点存储的是完整的用户记录,存储了所有列的值（包括隐藏列）

二级索引

使用记录c2列的大小进行记录和页的排序

1. 页内的记录是按照c2列的大小顺序排成一个单向链表
2. 存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表
3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表
4. B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值
5. 目录项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配

回表

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

联合索引

以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。包含两层含义：

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

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

根页面位置不动

B+树的形成过程

• 当为某个表创建一个 B+树索引的时候，都会为这个索引创建一个根节点页面。在最开始表中没有数据时，根节点中既没有用户记录，也没有目录项记录
• 随后向表中插入用户记录时，先把用户记录存储到根节点中
• 当根节点中的空间用完时继续插入记录，此时会将根节点的所有记录复制到一个新页 a，然后对页 a 进行页分裂，得到新页 b。此时新插入的记录就会被分到页 a 或页 b，根节点升级为存储目录项记录的页

内节点中目录项记录的唯一性

• 聚簇索引中目录项记录的内容：索引列+页号
• 二级索引中目录项记录的内容：索引列+主键+页号

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

目录项记录和用户记录的异同

不同点

相同点

1. 都会为主键生成 Page Direction
2. 在按照主键查找的时候，使用二分法来加速查询

InnoDB主键索引B+树的高度为什么一般不超过三

一个高度为 3 的 B+ 树大概可以存放 1170 × 1170 × 16 = 21902400 行数据，已经是千万级别的数据量了。大多数项目也就是这个量级的数据了，再大的……也该拆分拆分了

InnoDB 索引的数据结构：B+树

1. 存放用户记录和目录项记录的数据页都存放在索引中，这些数据页也称为节点
2. B+树的用户数据都存放在最底层的节点上，称为叶子结点，其余的节点称为非叶子节点，最上层的节点称为根节点

4、MyISAM 中的索引

4.1、MyISAM索引的原理

MyISAM 将索引和数据分开存储

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号快速访问到一条记录
• 由于在插入数据的时候并没有刻意按照主键大小排序，所以不能使用二分法进行查找
• 使用MyISAM存储引擎的表会把索引信息另外存储到索引文件。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录
• 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引
• 可以对其它的列分别建立索引或者建立联合索引，原理和InnoDB中的索引差不多，不过在叶子节点处存储的是相应的列 + 行号。这些索引也全部都是二级索引

4.2、MySQL ⚔ InnoDB

索引结构对比：