Mysql

第04章逻辑架构

1. 逻辑架构剖析

首先MySQL是典型的C/S架构，即Clinet/Server 架构，服务器端程序使用的是 mysqld。

不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果是：客户端进程向服务器进程发送一段文本（SQL语句），服务器进程处理后再向客户端进程返回一段文本（处理结果）。

那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为例展示：

1.1 服务器处理客户端请求

下面具体展开如下：

1.2 Connectors

Connectors, 指的是不同语言中与SQL的交互。MySQL首先是一个网络程序，在TCP之上定义了自己的应用层协议。所以要使用MySQL，我们可以编写代码，与MySQL Server 建立TCP连接，之后按照其定义好的协议进行交互。或者比较方便的方法是调用SDK，比如Native C API、JDBC、PHP等各语言MySQL Connecotr,或者通过ODBC。通过SDK来访问MySQL，本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互

接下来的MySQL Server结构可以分为如下三层：

1.3 第一层：连接层

连接管理的职责是负责认证、管理连接、获取权限信息。

系统（客户端）访问MySQL服务器前，做的第一件事就是建立TCP连接。

经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。

用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限

TCP连接池

多个系统都可以和MySQL服务器建立连接，每个系统建立的连接不止一个。所以，为了解决TCP无限创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。MySQL服务器里有专门的TCP连接池限制连接数采用长连接模式复用TCP连接，来解决上述问题。

线程池

TCP连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

这些内容我们都归纳到MySQL的连接管理组件中。

1.4 第二层：服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化：如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。

如果是SELECT语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

组件名	解释
`Management Services & Utilities`	系统管理和控制工具
`SQL Interface`	SQL 接口。接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface。
`Parser`	SQL 解析器。SQL 命令传递到解析器的时候会被解析器验证和解析。
`Optimizer`	SQL 查询优化器。SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有 where 条件时，优化器来决定先投影还是先过滤。
`Cache & Buffer`	SQL 查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等。

SQL Interface: SQL接口
- 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface
- MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口
Parser: 解析器
- 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
- 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写。
Optimizer: 查询优化器
- SQL语句在语法解析之后、查询之前会使用查询优化器确定SQL语句的执行路径，生成一个执行计划。
- 这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
- 它使用“选取-投影-连接”策略进行查询。例如：
  1
  SELECT id,name FROM student WHERE gender = '女';
这个SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行gender过滤。这个SELECT查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。
Caches & Buffers：查询缓存组件
- MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
- 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
- 这个查询缓存可以在不同客户端之间共享。
- 从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

1.5 第三层：引擎层

插件式存储引擎层（ Storage Engines）真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务层通过API与存储引擎进行通信。

插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。同时开源的MySQL还允许开发人员设置自己的存储引擎。

MySQL 8.0.25 默认支持的存储引擎如下

1	show engines;

[

1.6 存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用DAS、NAS、SAN等各种存储系统。

1.7 小结

MySQL架构图本节开篇所示。下面为了熟悉SQL执行流程方便，我们可以简化如下：

[

简化为三层结构：

连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
存储引擎层：与数据库文件打交道，负责数据的存储和读取。

2. SQL执行流程

2.1 MySQL中的SQL执行流程

MySQL的查询流程：

1）查询缓存

Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在MySQL8.0之后就抛弃了这个功能。

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果查询能够直接在这个缓存中找到key,那么这个value就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。所以，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

1.1) 大多数情况查询缓存就是个鸡肋，为什么呢？

查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。

需要说明的是，在MySQL中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的鲁棒性大大降低，只有相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。因此MySQL的查询缓存命中率不高。

鲁棒性（Robustness）是指系统、程序或算法在面临不确定性、异常情况或错误输入时，依然能够正确运行或保持一定性能的能力。

同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql、 information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。以某些系统函数举例，可能同样的函数的两次调用会产生不一样的结果，比如函数NOW，每次调用都会产生最新的当前时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的！

此外，既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！对于更新压力大的数据库来说，查询缓存的命中率会非常低。

总之，因为查询缓存往往弊大于利，查询缓存的失效非常频繁。

1.2) 查询缓存的使用建议

一般建议在静态表里使用查询缓存，什么叫静态表呢？就是一般极少更新的表。比如，一个系统配置表、字典表，这张表上的查询才适合使用查询缓存。好在MySQL也提供了这种“按需使用”的方式。你可以将my.cnf参数query_cache_type设置成DEMAND,代表当sql语句中有SQL_CACHE关键词时才缓存。比如：

1 2	# query_cache_type 有3个值。 0代表关闭查询缓存OFF，1代表开启ON，2代表(DEMAND) query_cache_type=2

这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样：

1
2
3

SELECT SQL_CACHE column1, column2, ...
FROM table_name
WHERE condition;

如果缓存处于开启，也可以使用SQL_NO_CACHE来显示指明不使用缓存

查看当前mysq实例是否开启缓存机制

# MySQL5.7中
mysql> show variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

# MySQL8.0中
mysql> show global variables like 'query_cache_type';
Empty set (0.00 sec)

监控查询缓存的命中率

mysql> show status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 1       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+

运行结果解析：

Qcache_free_blocks:表示查询缓存中还有多少剩余的blocks,如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
Qcache_hits:表示有多少次命中缓存。可以通过该值来验证查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts:表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适
当的调整缓存大小。
Qcache_not_cached:表示因为query_cache_type的设置而没有被缓存的查询数量。
Qcache_queries_in_cache:当前缓存中缓存的查询数量。
Qcache_total_blocks:当前缓存的block数量。

2）解析器

在解析器中对SQL语句进行语法分析、语义分析。

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。SQL语句的分析分为词法分析与语法分析。

分析器先做词法分析。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。

MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

接着，要做“语法分析”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果语句不对，就会收到“You have an error in your SQL syntax”的错误提醒。

如果SQL语句正确，则会生成一个语法树

分析器先做“ 词法分析 ”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

接着，要做“ 语法分析 ”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否 满足 MySQL 语法 。

select department_id,job_id, avg(salary) from employees group by department_id;

如果SQL语句正确，则会生成一个这样的语法树：

下图是SQL分词分析的过程步骤:

至此解析器的工作任务也基本圆满了。接下来进入到优化器。

3）优化器

优化器的作用

在优化器中会确定SQL语句的执行路径，比如是根据全表检索，还是根据索引检索等。

经过了解析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

比如：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join)的时候，决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等。

举例：如下语句是执行两个表的join:

1 2	select * from test1 join test2 using(ID) where test1.name='zhangsan'and test2.name='mysql高级课程';

方案1：可以先从表test1里面取出name=’zhangwei’的记录的ID值，再根据ID值关联到表test2,再判断test2里面name的值是否等于’mysql高级课程’。

方案2：可以先从表test2里面取出name=’mysq1高级课程’的记录的ID值，再根据ID值关联到test1,再判断test1里面name的值是否等于zhangwei。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等。后面讲到索引我们再谈

逻辑查询优化和物理查询优化

在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。

逻辑查询优化就是通过改变SQL语句的内容来使得SQL查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对SQL语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。

物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。

4）执行器

在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行 SQL查询并返回结果。在MySQL8.0以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，调用存储引擎API对表进行的读写。存储引擎API只是抽象接口，下面还有个存储引擎层，具体实现还是要看表选择的存储引擎。

1	select * from test where id=1;

比如：表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1
2
3

调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是1，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

5）小结

SQL语句在MySQL中的流程是：SQL语句→查询缓存→解析器→优化器→执行器。

[

2.2 MySQL8中SQL执行原理

一条SQL语句会经历不同的模块，在不同的模块中，SQL执行所使用的资源（时间）不同。

下面通过命令行分析SQL语句的执行经过的模块和占用的时间。

1) 查看profiling 是否开启

select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+

show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+

profiling=0 代表关闭，需要把 profiling 打开，即设置为 1：

1	set profiling=1;

2) 多次执行相同SQL查询

1	select * from employees;

3) 查看profiles

show profiles; # 显示最近的几次查询
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00189200 | show variables like 'profiling' |
|        2 | 0.00020325 | SELECT DATABASE()               |
|        3 | 0.00064575 | show databases                  |
|        4 | 0.00051400 | show tables                     |
|        5 | 0.00125675 | show tables                     |
|        6 | 0.00047500 | select * from employees         |
|        7 | 0.00054475 | select * from employees         |
+----------+------------+---------------------------------+

4) 查看profile

显示执行计划，查看程序的执行步骤

# 默认查询最后一次的执行过程(show profiles返回的Query_ID为7)
show profile;
# 查询指定的 Query ID
show profile for query 7;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000103 |
| Executing hook on transaction  | 0.000010 |
| starting                       | 0.000010 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000044 | 
| init                           | 0.000006 | 
| System lock                    | 0.000010 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000023 |
| preparing                      | 0.000022 |
| executing                      | 0.000238 |
| end                            | 0.000004 |
| query end                      | 0.000004 |
| waiting for handler commit     | 0.000009 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000030 |
| cleaning up                    | 0.000012 |
+--------------------------------+----------+

checking permissions 权限检查
Opening tables 打开表
init 初始化
System lock 锁系统
optimizing 优化查询
preparing 准备
executing 执行

1 2	# 查询更丰富的内容 show profile cpu,block io for query 6;

除了查看cpu、io阻塞等参数情况，还可以查询下列参数的利用情况

Syntax:
SHOW PROFILE [type [, type] ... ]
	[FOR QUERY n]
	[LIMIT row_count [OFFSET offset]]

type: {
	| ALL -- 显示所有参数的开销信息
	| BLOCK IO -- 显示IO的相关开销
	| CONTEXT SWITCHES -- 上下文切换相关开销
	| CPU -- 显示CPU相关开销信息
	| IPC -- 显示发送和接收相关开销信息
	| MEMORY -- 显示内存相关开销信息
	| PAGE FAULTS -- 显示页面错误相关开销信息
	| SOURCE -- 显示和Source_function,Source_file,Source_line 相关的开销信息
	| SWAPS -- 显示交换次数相关的开销信息
}

2.3 MySQL5.7中SQL执行分析

在MySQL5.7中测试，显式开启查询缓存模式。

1）在/etc/my.cnf配置文件中开启查询缓存

1	query_cache_type=1

2）重启服务器

1	systemctl restart mysqld

3）开启查询执行计划

1	set profiling=1;

4）执行语句两次

1 2	select * from employees; select * from employees;

5）查看profiles

mysql> show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00058175 | select * from employees |
|        2 | 0.00009325 | select * from employees |
+----------+------------+-------------------------+

6）查看profile

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000037 |
| Waiting for query cache lock   | 0.000004 |
| starting                       | 0.000002 |
| checking query cache for query | 0.000087 |
| checking permissions           | 0.000009 |
| Opening tables                 | 0.000020 |
| init                           | 0.000025 |
| System lock                    | 0.000009 |
| Waiting for query cache lock   | 0.000002 |
| System lock                    | 0.000018 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000013 |
| preparing                      | 0.000012 |
| executing                      | 0.000003 |
| Sending data                   | 0.000259 |
| end                            | 0.000004 |
| query end                      | 0.000007 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000007 |
| Waiting for query cache lock   | 0.000002 |
| freeing items                  | 0.000028 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000002 |
| storing result in query cache  | 0.000003 |
| cleaning up                    | 0.000016 |
+--------------------------------+----------+

mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000043 |
| Waiting for query cache lock   | 0.000003 |
| starting                       | 0.000002 |
| checking query cache for query | 0.000005 |
| checking privileges on cached  | 0.000003 |
| checking permissions           | 0.000011 |
| sending cached result to clien | 0.000022 |
| cleaning up                    | 0.000005 |
+--------------------------------+----------+

从结果中可以看出查询语句直接从缓存中获取数据。

3. 数据库缓冲池(buffer pool)

InnoDB存储引擎是以页为单位来管理存储空间的，增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。而磁盘I/O需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。

这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行I/O的时间。这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

3.1 缓冲池 vs 查询缓存

1）缓冲池（Buffer Pool）

在InnoDB存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存

InnoDB缓冲池包括了数据页、索引页、插入缓存、锁信息、自适应Hash和数据字典信息等。

缓存池的重要性：

对于使用InnoDB作为存储引擎的表来说，不管是用于存储用户数据的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是InnoDB对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。但是各位也都知道，磁盘的速度慢的跟乌龟一样，怎么能配得上“快如风，疾如电"的CPU呢？这里，缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟。所以InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了。

缓存原则：

“位置 * 频次”这个原则，对I/O访问效率进行优化。

首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。

其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载。

缓冲池的预读特性:

缓冲池的作用就是提升 I/O 效率，而我们进行读取数据的时候存在一个“局部性原理”，也就是说我们使用了一些数据，大概率还会使用它周围的一些数据，因此采用“预读”的机制提前加载，可以减少未来可能的磁盘 I/O 操作。

2）查询缓存

查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表发生变化，查询缓存就会效，因此命中率低。

缓冲池服务于数据库整体的/O操作，它们的共同点都是通过缓存的机制来提升效率。

3.2 缓冲池如何读取数据

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。

缓存在数据库中的结构和作用如下图所示：

如果我们执行SQL语句的时候更新了缓存池中的数据，那么这些数据会马上同步到磁盘上吗？

实际上，当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘中。注意并不是每次发生更新操作，都会立即进行磁盘回写。缓冲池会采用一种叫做 checkpoint 的机制 将数据回写到磁盘上，这样做的好处就是提升了数据库的整体性能。

比如，当缓冲池不够用时，需要释放掉一些不常用的页，此时就可以强行采用checkpoint的方式，将不常用的脏页回写到磁盘上，然后再从缓存池中将这些页释放掉。这里的脏页 (dirty page) 指的是缓冲池中被修改过的页，与磁盘上的数据页不一致。

3.3 查看/设置缓冲池的大小

Mysql MyISAM存储引擎只缓存索引，不缓存数据，对应的键缓存参数为key_buffer_size

InnoDB存储引擎，可以通过查看innodb_buffer_pool_size变量来查看缓冲池的大小。

查看缓冲池的大小

show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

此时InnoDB的缓冲池大小是134217728/1024/1024=128MB

设置缓冲池的大小

1	set global innodb_buffer_pool_size = 268435456;

或者

1 2	[server] innodb_buffer_pool_size = 268435456

3.4 多个Buffer Pool实例

Buffer Pool本质是 InnoDB 向操作系统申请的一块连续的内存空间，在多线程环境下，访问Buffer Pool中的数据都需要加锁处理。在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度。所以在Buffer Pool特别大的时候，可以把它们拆分成若干个小的Buffer Pool，每个Buffer Pool都称为一个实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表。所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。

可以在服务器启动的时候通过设置innodb_buffer_pool_instances的值来修改Buffer Pool实例的个数

1 2	[server] innodb_buffer_pool_instances = 2

表明创建2个 Buffer Pool 实例

查看缓冲池的个数

1	show variables like 'innodb_buffer_pool_instances';

每个Buffer Pool实例实际占内存空间

1	innodb_buffer_pool_size/innodb_buffer_pool_instances

总共的大小除以实例的个数，结果就是每个Buffer Pool实例占用的大小。

不过也不是说 Buffer Pool 实例创建的越多越好，分别管理各个Buffer Pool也是需要性能开销的，InnDB规定：当innodb_buffer_pool_size的值小于1G的时候设置多个实例是无效的，InnoDB会默认把innodb_buffer_pool_instances的值修改为1。建议在 Buffer Pool 大于等于1G的时候设置多个Buffer Pool实例。

3.5 引申问题

Buffer Pool是MySQL内存结构中十分核心的一个组成，可以先把它想象成一个黑盒子

黑盒下的更新数据流程

查询数据的时候，会先去Buffer Pool中查询。如果Buffer Pool中不存在，存储引擎会先将数据从磁盘加载到Buffer Pool中，然后将数据返回给客户端；同理，当我们更新某个数据的时候，如果这个数据不存在于 Buffer Pool，同样会先数据加载进来，然后修改内存的数据。被修改的数据会在之后统一刷入磁盘。

这个过程看似没啥问题，实则是有问题的。假设我们修改Buffer Pool中的数据成功，但是还没来得及将数据刷入磁盘MySQL就挂了怎么办？按照上图的逻辑，此时更新之后的数据只存在于Buffer Pool中，如果此时MySQL宕机了，这部分数据将会永久地丢失；

再者，更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办？连数据持久化的保证、事务回滚都做不到还谈什么崩溃恢复？

答案：Redo Log & Undo Log

第05章存储引擎

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL Server的功能，把真实存取数据的功能划分为存储引擎的功能。所以在MySQL Server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API,获取到数据后返回给客户端就好了。

MySQL中提到了存储引擎的概念。简而言之，存储引擎就是指表的类型。其实存储引擎以前叫做表处理器，后来改名为存储引擎，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

1. 查看存储引擎

1
2
3

show engines;
# 或
show engines\G

查询结果显示，MySQL8支持9种存储引擎，分别为MEMORY、MRG_MYISAM、CSV、FEDERATED、PERFORMANCE_SCHEMA、MyISAM、InnoDB、BLACKHOLE和ARCHIVE。

Engine参数表示存储引擎名称。
Support参数表示MySQL数据库管理系统是否支持该存储引擎：YES表示支持，NO表示不支持。
DEFAULT表示系统默认支持的存储引擎。
Comment参数表示对存储引擎的评论。
Transactions参数表示存储引擎是否支持事务：YES表示支持，NO表示不支持。
XA参数表示存储引擎所支持的分布式是否符合XA规范：YES表示支持，NO表示不支持。代表着该存储引擎是否支持分布式事务。
Savepoints参数表示存储引擎是否支持事务处理的保存点：YES表示支持，NO表示不支持。也就是说，该存储引擎是否支持部分事务回滚。

2. 设置系统默认的存储引擎

查看默认的存储引擎：

show variables like '%storage_engine%'; 
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
#或
SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

修改默认的存储引擎

如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用InnoDB作为表的存储引擎。

1	SET DEFAULT_STORAGE_ENGINE=MyISAM;

或者修改my.cnf文件：

1	default-storage-engine=MyISAM

1 2	# 重启服务 systemctl restart mysqld.service

3. 设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

创建表时指定存储引擎

1
2
3

CREATE TABLE 表名(
    建表语句; 
) ENGINE = 存储引擎名称;

修改表的存储引擎

1	ALTER TABLE 表名 ENGINE = 存储引擎名称;

查看表的存储引擎

1	SHOW CREATE TABLE <table_name>;

4. 引擎介绍

4.1 InnoDB 引擎：具备外键支持功能的事务存储引擎

MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎。
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
数据文件结构：
- 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
- 表名.ibd 存储数据和索引
InnoDB是为处理巨大数据量的最大性能设计。
- 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如：.frm，.par，.trn，.isl，.db.opt等都在MySQL8.0中不存在了。
对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

4.2 MyISAM 引擎：主要的非事务处理存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
5.5之前默认的存储引擎
优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用
针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
数据文件结构：
- 表名.frm 存储表结构
- 表名.MYD 存储数据 (MYData)
- 表名.MYI 存储索引 (MYIndex)
应用场景：只读应用或者以读为主的业务

4.3 Archive 引擎：用于数据存档

archive是归档的意思，仅仅支持插入和查询两种功能（行被插入后不能再修改）
在MySQL5.5以后支持索引功能
拥有很好的压缩机制，使用zlib压缩库，在记录请求的时候实时的进行压缩，经常被用来作为仓库使用
创建ARCHIVE表时，存储擎会创建名称以表名开头的文件。数据文件的扩展名为.ARZ
根据英文的测试结论来看，同样数据量下，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%
ARCHIVE存储引擎采用了行级锁。该ARCHIVE引擎支持AUTO_INCREMENT列属性。AUTO_INCREMENT列可以具有唯一索引或非唯一索引。尝试在任何其他列上创建索引会导致错误
Archive表适合日志和数据采集（档案）类应用；适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度，但是对查询的支持较差
下表展示了ARCHIVE存储引擎功能

特征	支持
B树索引	不支持
`备份/时间点恢复`（在服务器中实现，而不是在存储引擎中）	支持
集群数据库	不支持
聚集索引	不支持
`压缩数据`	支持
数据缓存	不支持
加密数据（加密功能在服务器中实现）	支持
外键	不支持
全文检索索引	不支持
地理空间数据类型	支持
地理空间索引	不支持
哈希索引	不支持
索引缓存	不支持
`锁粒度`	行锁
MVCC	不支持
存储限制	没有任何限制
交易	不支持
`更新数据字典的统计信息`	支持

4.4 Blackhole 引擎：丢弃写操作，读操作会返回空内容

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。
但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

4.5 CSV 引擎：存储数据时，以逗号分隔各个数据项

CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引
CSV引擎可以作为一种数据交换的机制，非常有用
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
对于数据的快速导入、导出是有明显优势的。

创建CSV表时，服务器会创建一个纯文本数据文件，其名称以表名开头并带有.CSV扩展名。当你将数据存储到表中时，存储引擎将其以逗号分隔值格式保存到数据文件中。

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;

INSERT INTO test VALUES(1,'record one'),(2,'record two');

mysql> SELECT * FROM test;
+---+------------+
| i | c          |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+

创建CSV表还会创建相应的元文件，用于存储表的状态和表中存在的行数。此文件的名称与表的名称相同，后缀为CSM。

test.CSM   test.CSV
# 查看CSV文件
cat test.CSV
1,"record one"
2,"record two"

创建使用CSV引擎的表，表的字段必须声明为NOT NULL

4.6 Memory 引擎：置于内存的表

概述：

Memory采用的逻辑介质是内存， 响应速度很快，但是当mysqld守护进程崩溃的时候数据会丢失。另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

主要特征

Memory同时支持哈希（HASH）索引和B+树索引。
- 哈希索引相等的比较快，但是对于范围的比较慢很多
- 默认使用哈希(HASH)索引，其速度要比使用B型树(BTREE)索引快
- 如果希望使用B树索引，可以在创建索引时选择使用。
Memory表至少比MyISAM表要快一个数量级。
MEMORY表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size 其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
数据文件与索引文件分开存储。
- 每个基于MEMORY存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型，该文件中只存储表的结构，而其数据文件都是存储在内存中的
- 这样有利于数据的快速处理，提供整个表的处理效率。
缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。

使用Memory存储引擎的场景

目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大小。
如果数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。

4.7 Federated 引擎：访问远程表

Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

4.8 Merge引擎：管理多个MyISAM表构成的表集合

4.9 NDB引擎：MySQL集群专用存储引擎

也叫做NDB Cluster存储引擎，主要用于MySQL Cluster 分布式集群环境，类似于Oracle的RAC集群。

4.10 引擎对比

MySQL中同一个数据库，不同的表可以选择不同的存储引擎。如下表对常用存储引擎做出了对比。

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
`事务安全`		支持
`锁机制`	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持			支持
数据缓存		支持	支持		支持
`索引缓存`	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
`支持外键`		支持

5. MyISAM和InnoDB

MySQL5.5之前的默认存储引擎是MyISAM，5.5之后改为了InnoDB。

首先对于InnoDB存储引擎，提供了良好的事务管理、崩溃修复能力和并发控制。因为InnoDB存储引擎支持事务，所以对于要求事务完整性的场合需要选择InnoDB,比如数据操作除了插入和查询以外还包含有很多更新、删除操作，像财务系统等对数据准确性要求较高的系统。缺点是其读写效率稍差，占用的数据空间相对比较大。

其次对于MyISAM存储引擎，如果是小型应用，系统以读操作和插入操作为主，只有很少的更新、删除操作，并且对事务的要求没有那么高，则可以选择这个存储引擎。MyISAM存储引擎的优势在于占用空间小，处理速度快；缺点是不支持事务的完整性和并发性。

这两种引擎各有特点，当然你也可以在MySQL中，针对不同的数据表，可以选择不同的存储引擎。

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
自带系统表使用	Y	N
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源
默认安装	Y	Y
默认使用	N	Y

6.知识补充

InnoDB表的优势

InnoDB存储引擎在实际应用中拥有诸多优势，比如操作便利、提高了数据库的性能、维护成本低等。如果由于硬件或软件的原因导致服务器崩溃，那么在重启服务器之后不需要进行额外的操作。InnoDB崩溃恢复功能自动将之前提交的内容定型，然后撤销没有提交的进程，重启之后继续从崩溃点开始执行。

InnoDB存储引擎在主内存中维护缓冲池，高频率使用的数据将在内存中直接被处理。这种缓存方式应用于多种信息，加速了处理进程。

在专用服务器上，物理内存中高达80%的部分被应用于缓冲池。如果需要将数据插入不同的表中，可以设置外键加强数据的完整性。更新或者删除数据，关联数据将会被自动更新或删除。如果试图将数据插入从表，但在主表中没有对应的数据，插入的数据将被自动移除。如果磁盘或内存中的数据出现崩溃，在使用脏数据之前，校验和机制会发出警告。当每个表的主键都设置合理时，与这些列有关的操作会被自动优化。插入、更新和删除操作通过做改变缓冲自动机制进行优化。InnoDB不仅支持当前读写，也会缓冲改变的数据到数据流磁盘

InnoDB的性能优势不只存在于长时运行查询的大型表。在同一列多次被查询时，自适应哈希索引会提高查询的速度。使用InnoDB可以压缩表和相关的索引，可以在不影响性能和可用性的情况下创建或删除索引。对于大型文本和BLOB数据，使用动态行形式，这种存储布局更高效。通过查询INFORMATION_SCHEMA库中的表可以监控存储引擎的内部工作。在同一个语句中，InnoDB表可以与其他存储引擎表混用。即使有些操作系统限制文件大小为2GB，InnoDB仍然可以处理。当处理大数据量时，InnoDB兼顾CPU，以达到最大性能

InnoDB和ACID模型

ACID模型是一系列数据库设计规则，这些规则着重强调可靠性，而可靠性对于商业数据和任务关键型应用非常重要。MySQL包含类似InnoDB存储引擎的组件，与ACID模型紧密相连，这样出现意外时，数据不会崩溃，结果不会失真。如果依赖ACID模型，可以不使用一致性检查和崩溃恢复机制。如果拥有额外的软件保护，极可靠的硬件或者应用可以容忍一小部分的数据丢失和不一致，可以将MySQL设置调整为只依赖部分ACID特性，以达到更高的性能。下面讲解InnoDB存储引擎与ACID模型相同作用的四个方面。

原子方面

ACID的原子方面主要涉及InnoDB事务，与MySQL相关的特性主要包括：

自动提交设置
COMMIT语句
ROLLBACK语句
操作INFORMATION_SCHEMA库中的表数据

一致性方面

ACID模型的一致性主要涉及保护数据不崩溃的内部InnoDB处理过程，与MySQL相关的特性主要包括：

InnoDB双写缓存
InnoDB崩溃恢复

隔离方面

隔离是应用于事务的级别，与MySQL相关的特性主要包括：

自动提交设置
SET ISOLATION LEVEL语句
InnoDB锁的低级别信息

耐久性方面

ACID模型的耐久性主要涉及与硬件配置相互影响的MySQL软件特性。由于硬件复杂多样化，耐久性方面没有具体的规则可循。与MySQL相关的特性有：

InnoDB双写缓存，通过innodb_doublewrite配置项配置
配置项innodb_flush_log_at_trx_commit
配置项sync_binlog
配置项innodb_file_per_table
存储设备的写入缓存
存储设备的备用电池缓存
运行MySQL的操作系统
持续的电力供应
备份策略
对分布式或托管的应用，最主要的在于硬件设备的地点以及网络情况

InnoDB架构

缓冲池 缓冲池是主内存中的一部分空间，用来缓存已使用的表和索引数据。缓冲池使得经常被使用的数据能够直接在内存中获得，从而提高速度

更改缓存 更改缓存是一个特殊的数据结构，当受影响的索引页不在缓存中时，更改缓存会缓存辅助索引页的更改。索引页被其他读取操作时会加载到缓存池，缓存的更改内容就会被合并。不同于集群索引，辅助索引并非独一无二的。当系统大部分闲置时，清除操作会定期运行，将更新的索引页刷入磁盘。更新缓存合并期间，可能会大大降低查询的性能。在内存中，更新缓存占用一部分InnoDB缓冲池。在磁盘中，更新缓存是系统表空间的一部分。更新缓存的数据类型由innodb_change_buffering配置项管理

自适应哈希索引 自适应哈希索引将负载和足够的内存结合起来，使得InnoDB像内存数据库一样运行，不需要降低事务上的性能或可靠性。这个特性通过innodb_adaptive_hash_index选项配置，或者通过--skip-innodb_adaptive_hash_index命令行在服务启动时关闭

重做日志缓存 重做日志缓存存放要放入重做日志的数据。重做日志缓存大小通过innodb_log_buffer_size配置项配置。重做日志缓存会定期地将日志文件刷入磁盘。大型的重做日志缓存使得大型事务能够正常运行而不需要写入磁盘

系统表空间 系统表空间包括InnoDB数据字典、双写缓存、更新缓存和撤销日志，同时也包括表和索引数据。多表共享，系统表空间被视为共享表空间

双写缓存 双写缓存位于系统表空间中，用于写入从缓存池刷新的数据页。只有在刷新并写入双写缓存后，InnoDB才会将数据页写入合适的位置

撤销日志 撤销日志是一系列与事务相关的撤销记录的集合，包含如何撤销事务最近的更改。如果其他事务要查询原始数据，可以从撤销日志记录中追溯未更改的数据。撤销日志存在于撤销日志片段中，这些片段包含于回滚片段中

每个表一个文件的表空间 每个表一个文件的表空间是指每个单独的表空间创建在自身的数据文件中，而不是系统表空间中。这个功能通过innodb_file_per_table配置项开启。每个表空间由一个单独的.ibd数据文件代表，该文件默认被创建在数据库目录中

通用表空间 使用CREATE TABLESPACE语法创建共享的InnoDB表空间。通用表空间可以创建在MySQL数据目录之外能够管理多个表并支持所有行格式的表

撤销表空间 撤销表空间由一个或多个包含撤销日志的文件组成。撤销表空间的数量由innodb_undo_tablespaces配置项配置

临时表空间 用户创建的临时表空间和基于磁盘的内部临时表都创建于临时表空间。innodb_temp_data_file_path配置项定义了相关的路径、名称、大小和属性。如果该值为空，默认会在innodb_data_home_dir变量指定的目录下创建一个自动扩展的数据文件

重做日志 重做日志是基于磁盘的数据结构，在崩溃恢复期间使用，用来纠正数据。正常操作期间，重做日志会将请求数据进行编码，这些请求会改变InnoDB表数据。遇到意外崩溃后，未完成的更改会自动在初始化期间重新进行

7.阿里巴巴、淘宝用哪个

产品	价格	目标	主要功能	是否可投入生产？
Percona Server	免费	提供XtraDB存储引擎的包装器和其他分析工具	XtraDB	是
MariaDB	免费	扩展MySQL以包含XtraDB和其他性能改进	XtraDB	是
Drizzle	免费	提供比 MySQL 更强大的可扩展性和性能改进	高可用性	是

Percona为MySQL数据库服务器进行了改进，在功能和性能上较MySQL有很显著的提升。
该版本提升了在高负载情况下的InnoDB的性能、为DBA提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。
该公司新建了一款存储引擎叫Xtradb完全可以替代Innodb,并且在性能和并发上做得更好
阿里巴巴大部分mysql数据库其实使用的perconal的原型加以修改。

第06章索引的数据结构

1. 索引及其优缺点

1.1 索引概述

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

**索引的本质：**索引是数据结构。可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。

1.2 优点

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。

（2）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。

（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

1.3 缺点

（1）创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。

（2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。

（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

2. InnoDB中索引

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

2.1 InnoDB索引方案

1）表的行格式

行格式指的是存储在表中的每一行数据的物理存储格式

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表的行格式示意图：

index_demo

record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录、1表示目录项记录、2表示最小记录、3表示最大记录。
next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
各个列的值：这里只记录在index_demo表中的三个列，分别是c1、c2和c3。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

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

index_demo2

2）数据页

数据页

多条记录根据主键值，从小到大排列存放在一个数据页中

页目录

数据页内存在以主键值生成的Page Directory（页目录），从而在按照主键值进行查找时可以使用二分法来加快查询速度。

页分裂

当向一个已满的数据页插入新记录时，数据库系统会动态地将该页分裂成两个新的数据页，以便容纳新的数据记录

两个数据页之间以双向链表的方式连接

下一个数据页中用户记录的主键值大于上一个页中用户记录的主键值。

页编号

每个页有页编号，页与页之间以双向链表连接，数据页的编号不一定连续

data_page_1

data_page_2

3）目录页

为了快速定位记录所在的数据页，所以建立一个目录

目录项

每个数据页抽出一条记录，记录主键值和数据页编号等信息，作为一条目录项

目录项记录和普通的用户记录的区别：

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

目录页

记录目录项的页中也存在页目录，按照主键值进行查找时可以使用 二分法 来加快查询速度。

index_page

多个目录项记录的页

多个目录页之间以双向链表的方式连接

index_page_2

4）B+Tree

表中的数据非常多则会产生很多存储目录项记录的页，为了根据主键值快速定位一个存储目录项记录的页，那就为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录

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

innodb_index_design

BPlusTree

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第0层，之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100条记录。
如果B+树有2层，最多能存放1000×100=10,0000条记录。
如果B+树有3层，最多能存放1000×1000×100=1,0000,0000条记录。
如果B+树有4层，最多能存放1000×1000×1000×100=1000,0000,0000条记录。相当多的记录！！！

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

2.2 常见索引概念

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

聚簇索引

聚簇索引不仅是一种索引类型，也是一种数据存储方式（所有的用户记录都存储在了叶子结点），也就是所谓的索引即数据，数据即索引。

聚簇索引中的“聚簇”指的是索引中存储数据行的方式。

索引键和实际数据行存储在同一个结构中，这种结构称为聚簇

特点：

1）使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

页内的记录是按照主键的大小顺序排成一个单向链表。
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

2）B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。

优点：

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

缺点：

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

限制：

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

二级索引（辅助索引、非聚簇索引）

因为每个MySQL的表只能有一个聚簇索引，一般情况下就是该表的主键

以表中的非主键列或者列组合建立的索引即为二级索引

当非主键列作为查询或者连表、排序等操作的条件时，为了提高效率，就可以以非主键列建立二级索引
二级索引不存储完整的行数据，只包含索引列及主键信息

因为每建立一颗B+树都把所有的用户记录再拷贝一遍，太浪费存储空间了。
因为数据和索引分开存储，所以二级索引属于非聚簇索引
概念：回表 根据二级索引的B+树只能确定要查找记录的主键值，如果想查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。查询一条完整的用户记录需要使用到2棵B+树！
二级索引的目录项，包含主键值，索引列，页号，从而保证目录项的唯一性

因为二级索引列的值是可以重复的，而主键是唯一的。

联合索引

联合索引同时以多个列的大小作为排序规则，也就是同时以多个列建立索引

例如B+树按照c2和c3列的大小进行排序，这个包含两层含义：

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

以c2和c3列的大小为排序规则建立的B+树称为联合索引

联合索引可以是聚簇索引也可以是非聚簇索引，当联合索引包含主键列时，就会建立聚簇索引

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

1）根页面位置万年不动

实际上B+树的形成过程：

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

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

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

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

c1	c2	c3
1	1	‘u’
3	1	‘d’
5	1	‘y’
7	1	‘a’

如果二级索引中目录项的内容只是索引号+页号的搭配的话，那么为c2列建立索引后的B+树应该长这样：

secondary _index1

如果我们想要新插入一行记录，其中c1、c2、c3的值分别是：9、1、c，那么在修改这个为c2列建立的二级索引对应的B+树时便碰到了个大问题：由于页3中存储的目录项记录是由c2列+页号的值构成的，页3中的两条目录项记录对应的c2列的值都是1，那么我们这条新插入的记录到底应该放在页4中，还是应该放在页5中啊？答案是：对不起，懵了。

为了让新插入记录能找到自己在哪个页里，我们需要**保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。**所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

索引列的值
主键值
页号

也就是我们把主键值也添加到二级索引内节点中的目录项记录了，这样就能保证B+树每一层节点中各条目录项记录除页号这个字段外是唯一的，所以我们为c2列建立二级索引后的示意图实际上应该是这样子的：

secondary_index2

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

3）一个页面最少可以存储2条记录

一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度相当不错！这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问的存储真实数据的目录。那如果一个大的目录中只存放一个子目录是个啥效果呢？那就是目录层非常多，而且最后的那个存放真实数据的目录中存放一条记录。费了半天劲只能存放一条真实的用户记录？所以InnoDB的一个数据页至少可以存放两条记录

3. MyISAM中的索引方案

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

索引/存储引擎	MyISAM	InnoDB	Memory
B-Tree索引	支持	支持	支持

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

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。

MySQL官方，凡是写B—Tree的地方，都是B+树

3.1 MyISAM索引的原理

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

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

MyISAM_Index

这里表一共有三列，假设我们以Col1为主键，上图是一个MyISAM表的主索引（Primary key）示意。可以看出
MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主键索引和二级索引（Secondary key）在结构上没有任何区别，只是主键索引要求key是唯一的，而二级索引的key可以重复。如果我们在Col2上建立一个二级索引，则此索引的结构如下图所示：

MyISAM_Index_2

3.2 MyISAM与InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。

小结两种引擎中索引的区别：

① 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

③ InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

小结：

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助。比如：

举例1：知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有二级索引都引用主键索引，过长的主键索引会令二级索引变得过大。数据页大小固定，数据项越大，单页存储的记录数越少，B+数的层级越多。

举例2：用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时，数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

InnoDB_MyISAM_Index

4. 索引的代价

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

空间上的代价

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

时间上的代价

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

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们得学学这些索引在哪些条件下起作用的。

5. MySQL数据结构选择的合理性

从MySQL的角度讲，不得不考虑一个现实问题就是磁盘IO。如果我们能让索引的数据结构尽量减少硬盘的IO操作，所消耗的时间也就越小。可以说，磁盘的I/O操作次数对索引的使用效率至关重要。

查找都是索引操作，一般来说索引非常大，尤其是关系型数据库，当数据量比较大的时候，索引的大小有可能几个G甚至更多，为了减少索引在内存的占用，数据库索引是存储在外部磁盘上的。当我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载，那么MySQL衡量查询效率的标准就是磁盘IO次数。

5.1 Hash结构

Hash 本身是一个函数，又被称为散列函数，它可以帮助我们大幅提升检索数据的效率。

Hash 算法是通过某种确定性的算法（比如 MD5、SHA1、SHA2、SHA3）将输入转变为输出。相同的输入永远可以得到相同的输出，假设输入内容有微小偏差，在输出中通常会有不同的结果。

哈希结构效率很高，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是 O(1); (key, value)

Hash结构效率高，那为什么索引结构要设计成树型呢？

原因1：Hash索引仅能满足（=）（< >）和IN查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为 O(n)；而树型的“有序”特性，依然能够保持O(log2N)的高效率。

原因2：Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序。

原因3：对于联合索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。

原因4：对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash索引通常不会用到重复值多的列，比如列为性别、年龄的情况等。

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

索引 / 存储引擎	MyISAM	InnoDB	Memory
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索引的优点。

Adaptive_Hash_Index

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

我们可以通过innodb_adaptive_hash_index变量来查看是否开启了自适应Hash，比如：

mysql> show variables like '%adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+

5.2 二叉搜索树

如磁盘的IO次数和索引树的高度是相关的。为了提高查询效率，就需要减少磁盘IO数。为了减少磁盘IO的次数，就需要尽量降低树的高度，需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好。

二叉搜索树无法自平衡，极端情况下会退化成链表

每个非叶子节点只能有两个子节点，树的层数太多，数太高，索引效率低。

5.3 AVL树

AVL树实现了自平衡，左右两个子树的高度差不会超过1，并且左右两个子树都是一颗二叉平衡树

数据查询的时间主要依赖于磁盘I/O的次数，如果采用二叉树的形式，即使通过平衡二叉搜索树进行了改进，树的深度也是O(log2n)，当n比较大时，深度也是比较高的

针对同样的数据，如果把二叉树改成M 叉树（M>2）当数据量N大的时候，以及树的分叉树M大的时候，M叉树的高度会远小于二叉树的高度。我们需要把 树从“瘦高” 变 “矮胖”。

5.4 B-Tree

B树的英文是Balance Tree，也就是多路平衡查找树。简写为B-Tree。它的高度远小于平衡二叉树的高度。

B树的结构如下图所示：

BTree

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

一个M阶的B树（M>2）有以下的特性：

根节点的儿子数的范围是 [2,M]。
每个中间节点包含 k-1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为[ceil(M/2), M]。 ceil向上取整
叶子节点包括 k-1 个关键字（叶子节点没有孩子），k 的取值范围为 [ceil(M/2), M]。
假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i] <Key[i+1]。此时 k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …,P[k]，其中 P[1] 指向关键字小于 Key[1] 的子树，P[i] 指向关键字属于 (Key[i-1], Key[i]) 的子树，P[k]指向关键字大于 Key[k-1] 的子树。
所有叶子节点位于同一层。

上面那张图所表示的 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，那么步骤可以分为以下几步：

① 我们与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；

② 按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以我们得到指针 P2；

③ 按照指针 P2 找到磁盘块 6，关键字为（9，10），然后我们找到了关键字 9。

你能看出来在B树的搜索过程中，我们比较的次数并不少，但如果把数据读取出来然后在内存中进行比较，这个时间就是可以忽略不计的。而读取磁盘块本身需要进行 I/O 操作，消耗的时间比在内存中进行比较所需要的时间要多，是数据查找用时的重要因素。B 树相比于平衡二叉树来说磁盘 I/O 操作要少，在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低，IO次数足够少，就可以提高查询性能。

小结

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

再举例

BTree2

5.5 B+Tree

B+树也是一种多路搜索树，基于 B 树做出了改进，主流的 DBMS 都支持 B+ 树的索引方式，比如 MySQL。相比较于 B-Tree，B+Tree 适合文件索引系统。

B+树和B树的差异：

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

B+树的优势

B+树和B树有个根本的差异在于，B+树的中间节点并不直接存储数据。这种设计的好处有哪些呢？

首先，B+树的查询效率更稳定，因为B+树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况。有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。

其次，B+树的查询效率更高。这是因为通常B+树比B树更矮胖（阶数更大，深度更低），查询所需要的磁盘IO也会更少。同样的磁盘页大小，B+树可以存储更多的节点关键字。

不仅是在单个关键字的查询上，在查询范围上，B+树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，叶子节点之间会有指针连接，数据又是递增的，这使得我们范围查询可以通过指针连接查找。而在B树中则需要通过中序遍历才能完成范围查询，效率要低很多。

B树和B+树都可以作为索引的数据结构，在MySQL中采用的是B+树。

但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。

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

1、数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，超过几个G。

2、当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是：逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

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

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值，因为是估算，为了方便计算，这里的K取值为10^3。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。（这里假定一个数据页也存储10^3条行记录数据了）

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作

思考题：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

1.B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2、B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的节点，而只是叶子结点中关键字的索引。所有任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

思考题：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 索引与 B+ 树索引是在建索引的时候手动指定的吗？

针对InnoDB和MyISAM存储引擎，都会默认采用B+树索引，而非使用Hash索引。InnoDB提供的自适应Hash是不需要手动指定的。如果是Memory/Heap和NDB存储引擎，则可以选择使用Hash索引。

5.6 R树

R-Tree在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。

举个R树在现实领域中能够解决的例子：查找20英里以内所有的餐厅。如果没有R树会怎么解决？一般情况下我们会把餐厅的坐标(x,y)分为两个字段存放在数据库中，一个字段记录经度，另一个字段记录纬度。这样的话我们就需要遍历所有的餐厅获取其位置信息，然后计算是否满足要求。如果一个地区有100家餐厅的话，我们就要进行100次位置计算操作了，如果应用到谷歌、百度地图这种超大数据库中，这种方法便必定不可行了。R树就很好的解决了这种高维空间搜索问题。它把B树的思想很好的扩展到了多维空间，采用了B树分割空间的思想，并在添加、删除操作时采用合并、分解结点的方法，保证树的平衡性。因此，R树就是一棵用来存储高维数据的平衡树。相对于B-Tree，R-Tree的优势在于范围查找。

索引 / 存储引擎	MyISAM	InnoDB	Memory
R-Tree索引	支持	支持	不支持

第07章 InnoDB数据存储结构

1. 数据库的存储结构：页

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

由于InnoDB是MySQL的默认存储引擎，所以本章剖析InooDB存储引擎的数据存储结构。

1.1 磁盘与内存交互基本单位：页

InnoDB将数据划分为若干个页，InnoDB中页的大小默认为16KB。

以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，**在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page），数据库I/O操作的最小单位是页。**一个页中可以存储多个行记录。

记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次I/O操作）只能处理一行数据，效率会非常低。

1.2 页结构概述

页a、页b、页c…页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应的分组中的记录即可快速找到指定的记录。

1.3 页的大小

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

show variables like '%innodb_page_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

SQL Server中页的大小为8KB，而在Oracle中用术语"块"（Block）来表示"页"，Oracle支持的块大小为2KB, 4KB, 8KB, 16KB, 32KB和64KB。

1.4 页的上层结构

另外在数据库中，还存在着区（Extent）、段（Segment）和表空间（Tablespace）的概念。

行、页、区、段、表空间的关系如下图所示：

Extent_Segment_Tablespace

区（Extent）是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB。

段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

2. 页的内部结构

页如果按类型划分的话，常见的有数据页（保存B+树节点）、系统表、Undo页和事务数据页等。数据页是我们最常使用的页。

数据页的 16KB 大小的存储空间被划分为七个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Infimum + supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）。

页结构的示意图如下所示：

data_page_structure

这7个部分作用分别如下

data_page_structure2

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

第1部分：文件头部和文件尾部

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

File Header（文件头部）（38字节）

作用：
描述各种页的通用信息。（比如页的编号、其上一页、下一页是谁等）

大小：38字节

名称	占用空间大小	描述
`FIL_PAGE_SPACE_OR_CHKSUM`	`4`字节	页的校验和（checksum值）
`FIL_PAGE_OFFSET`	`4`字节	页号
`FIL_PAGE_PREV`	`4`字节	上一个页的页号
`FIL_PAGE_NEXT`	`4`字节	下一个页的页号
`FIL_PAGE_LSN`	`8`字节	页面被最后修改时对应的日志序列位置英文名 Log Sequence Number
`FIL_PAGE_TYPE`	`2`字节	该页的类型
FIL_PAGE_FILE_FLUSH_LSN	`8`字节	仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	`4`字节	页属于哪个表空间

FIL_PAGE_OFFSET（4字节）

每一个页都有一个单独的页号，InnoDB通过页号可以唯一定位一个页。
FIL_PAGE_TYPE（2字节）：这个代表当前页的类型。

类型名称	十六进制	描述
FIL_PAGE_TYPE_ALLOCATED	0x0000	最新分配，还没有使用
`FIL_PAGE_UNDO_LOG`	0x0002	Undo日志页
FIL_PAGE_INODE	0x0003	段信息节点
FIL_PAGE_IBUF_FREE_LIST	0x0004	Insert Buffer空闲列表
FIL_PAGE_IBUF_BITMAP	0x0005	Insert Buffer位图
`FIL_PAGE_TYPE_SYS`	0x0006	系统页
FIL_PAGE_TYPE_TRX_SYS	0x0007	事务系统数据
FIL_PAGE_TYPE_FSP_HDR	0x0008	表空间头部信息
FIL_PAGE_TYPE_XDES	0x0009	扩展描述页
FIL_PAGE_TYPE_BLOB	0x000A	溢出页
`FIL_PAGE_INDEX`	0x45BF	索引页，也就是我们所说的`数据页`

FIL_PAGE_PREV（4字节）和FIL_PAGE_NEXT（4字节）

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

page_structure3

FIL_PAGE_SPACE_OR_CHKSUM（4字节）

代表当前页面的校验和（checksum）。文件头部和文件尾部都有属性：FIL_PAGE_SPACE_OR_CHKSUM

作用：

InnoDB存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候断电了，造成了该页传输的不完整。

为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），这时可以通过文件尾的校验和（checksum 值）与文件头的校验和做比对，如果两个值不相等则证明页的传输有问题，需要重新进行传输，否则认为页的传输已经完成。

具体的：
每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在File Trailer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。这里，校验方式就是采用 Hash 算法进行校验。
FIL_PAGE_LSN（8字节）

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

File Trailer（文件尾部）（8字节）

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

第2部分：空闲空间、用户记录和最小最大记录

第二个部分是记录部分

页的主要作用是存储记录，所以“最大和最小记录”和“用户记录”部分占了页结构的主要空间。

Page_Free_Space

Free Space (空闲空间)

我们自己存储的记录会按照指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。

User Records (用户记录)

User Records中的这些记录按照指定的行格式一条一条摆在User Records部分，相互之间形成单链表。

Infimum + Supremum（最小最大记录）

记录可以比较大小吗？
是的，记录可以比大小，对于一条完整的记录来说，比较记录的大小就是比较主键的大小。比方说我们插入的4行记录的主键值分别是：1、2、3、4，这也就意味着这4条记录是从小到大依次递增。

InnoDB规定的最小记录与最大记录这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的。

Infimum_Supremum

这两条记录不是我们自己定义的记录，所以它们并不存放在页的User Records部分，他们被单独放在一个称为Infimum + Supremum的部分

Infimum_Supremum2

第3部分：页目录和页面头部

Page Directory（页目录）

为什么需要页目录
在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此在页结构中专门设计了页目录这个模块，专门给记录做一个目录，通过二分查找法的方式进行检索，提升效率。

页目录结构

① 将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录。

第1组，也就是最小记录所在的分组只有1个记录；

最后一组，就是最大记录所在的分组，会有1-8条记录；

其余的组记录数量在4-8条之间。

这样做的好处是，除了第1组（最小记录所在组）以外，其余组的记录数会尽量平分。

② 在每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为n_owned字段。

③ 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

分组可以减少减少页目录的大小

分组可以减少页目录的变动

如果每条记录的偏移都放在页目录中，增加删除记录，就会频繁更改页目录。页目录是数组结构，增删改的时间复杂度是比较高的。

一个页为16KB，假设一条记录，占200个字节，每页也有将近80条多记录。

举例1：

现在的page_demo表中正常的记录共有6条，InnoDB会把它们分成两组，第一组中只有一个最小记录，第二组中是剩余的5条记录。如下图：

page_solt2

从这个图中我们需要注意这么几点：

现在页目录部分中有两个槽，也就意味着我们的记录被分成了两个组，槽1中的值是112，代表最大记录的地址偏移量（就是从页面的0字节开始数，数112个字节）；槽0中的值是99，代表最小记录的地址偏移量。
注意最小和最大记录的头信息中的n_owned属性
- 最小记录的n_owned值为1，这就代表着以最小记录结尾的这个分组中只有1条记录，也就是最小记录本身。
- 最大记录的n_owned值为5，这就代表着以最大记录结尾的这个分组中只有5条记录，包括最大记录本身还有我们自己插入的4条记录。

用箭头指向的方式替代数字，这样更易于我们理解，修改后如下

page_solt3

举例2:添加更多记录

page_solt

页目录分组的过程

InnoDB规定：对于最小记录所在的分组只能有1条记录，最大记录所在的分组拥有的记录条数只能在1~8条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

分组是按照下边的步骤进行的：

初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

页目录结构快速查找记录过程

在一个数据页中查找指定主键值的记录的过程分为两步：

通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
通过记录的next_record属性遍历该槽所在的组中的各个记录。

因为记录之间通过单链表连接，所以只能从左向右遍历

为了能得到一个数据页中存储的记录的状态信息，比如本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等，特意在页中定义了一个叫Page Header的部分，这个部分占用固定的56个字节，专门存储各种状态信息。

名称	占用空间大小	描述
PAGE_N_DIR_SLOTS	2字节	在页目录中的槽数量
PAGE_HEAP_TOP	2字节	还未使用的空间最小地址，也就是说从该地址之后就是`Free Space`
PAGE_N_HEAP	2字节	本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）
PAGE_FREE	2字节	第一个已经标记为删除的记录的记录地址（各个已删除的记录通过`next_record`也会组成一个单链表，这个单链表中的记录可以被重新利用）
PAGE_GARBAGE	2字节	已删除记录占用的字节数
PAGE_LAST_INSERT	2字节	最后插入记录的位置
PAGE_DIRECTION	2字节	记录插入的方向
PAGE_N_DIRECTION	2字节	一个方向连续插入的记录数量
PAGE_N_RECS	2字节	该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录）
PAGE_MAX_TRX_ID	8字节	修改当前页的最大事务ID，该值仅在二级索引中定义
PAGE_LEVEL	2字节	当前页在B+树中所处的层级
PAGE_INDEX_ID	8字节	索引ID，表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF	10字节	B+树叶子段的头部信息，仅在B+树的Root页定义
PAGE_BTR_SEG_TOP	10字节	B+树非叶子段的头部信息，仅在B+树的Root页定义

PAGE_DIRECTION

假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是PAGE_DIRECTION。

PAGE_N_DIRECTION

假设连续几次插入新记录的方向都是一致的，InnoDB会把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。

3. InnoDB行格式（或记录格式）

我们平时的数据以行为单位来向表中插入数据，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。

查看MySQL8的默认行格式：

mysql> SELECT @@innodb_default_row_format;
+-------------------------------------+
| @@innodb_default_row_format         |
+-------------------------------------+
| dynamic                             |
+-------------------------------------+

也可以使用如下语法查看具体表使用的行格式：

1	SHOW TABLE STATUS LIKE '表名'\G

3.1 指定行格式的语法

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

1	ALTER TABLE 表名 ROW_FORMAT=行格式名称

3.2 COMPACT行格式

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

compact

1）变长字段长度列表

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

注意：这里面存储的变长长度和字段顺序是反过来的。比如两个varchar字段在表结构的顺序是a(10)，b(15)。那么在变长字段长度列表中存储的长度顺序就是15，10，是反过来的。

举例

CREATE TABLE record_test_table (
	col1 VARCHAR(8),
	col2 VARCHAR(8) NOT NULL,
	col3 CHAR(8),
	col4 VARCHAR(8)
) CHARSET=ascii ROW_FORMAT=COMPACT;

以record_test_table表中的第一条记录举例：因为record_test_table表的col1、col2、col4列都是VARCHAR(8)类型的，所以这三个列的值的长度都需要保存在记录开头处，注意record_test_table表中的各个列都使用的是ascii字符集（每个字符只需要1个字节来进行编码）。

列名	存储内容	内容长度（十进制表示）	内容长度（十六进制表示）
col1	zhangsan	8	0x08
col2	lisi	4	0x04
col4	songhk	6	0x06

又因为这些长度值需要按照列的逆序存放，所以最后变长字段长度列表的字节串用十六进制表示的效果就是（各个字节之间实际上没有空格，用空格隔开只是方便理解）：
06 04 08

把这个字节串组成的变长字段长度列表填入上边的示意图中的效果就是：

variable_length_fields

2）NULL值列表（NULL bitmap）

Compact行格式会把可以为NULL的列统一管理起来，存在一个标记为NULL值列表中。如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了。

为什么定义NULL值列表？

之所以要存储NULL是因为数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，虽然能达到效果，但是这样很浪费空间，所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据，格式如下：

二进制位的值为1时，代表该列的值为NULL。
二进制位的值为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表为例

INSERT INTO record_test_table(col1, col2, col3, col4) 
VALUES
('zhangsan', 'lisi', 'wangwu', 'songhk'), 
('tong', 'chen', NULL, NULL);

null_bitmap

3）记录头信息（5字节）

CREATE TABLE page_demo(
    c1 INT,
    c2 INT,
    c3 VARCHAR(10000),
    PRIMARY KEY (c1)
) CHARSET=ascii ROW_FORMAT=Compact;

INSERT INTO page_demo 
VALUES
(1, 100, 'song'), (2, 200, 'tong'), 
(3, 300, 'zhan'), (4, 400, 'lisi');

compact_head

名称	大小（单位：bit）	描述
`预留位1`	1	没有使用
`预留位2`	1	没有使用
`delete_mask`	1	标记该记录是否被删除
`mini_rec_mask`	1	B+树的每层非叶子节点中的最小记录都会添加该标记
`n_owned`	4	表示当前记录拥有的记录数
`heap_no`	13	表示当前记录在记录堆的位置信息
`record_type`	3	表示当前记录的类型，`0`表示普通记录，`1`表示B+树非叶子节点记录，`2`表示最小记录，`3`表示最大记录
`next_record`	16	表示下一条记录的相对位置

delete_mask：这个属性标记着当前记录是否被删除，占用1个二进制位。
- 值为0：代表记录并没有被删除
- 值为1：代表记录被删除掉了

被删除的记录为什么还在页中存储呢？
你以为它删除了，可它还在真实的磁盘上。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后其他的记录在磁盘上需要重新排列，导致性能消耗。所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

min_rec_mask：B+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask值为1。我们自己插入的四条记录的min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。
record_type：这个属性表示当前记录的类型，一共有4种类型的记录：
- 0：表示普通记录
- 1：表示B+树非叶节点记录
- 2：表示最小记录
- 3：表示最大记录
heap_no：这个属性表示当前记录在本页中的位置。

heap_no值为0和1的记录
MySQL会自动给每个页里加了两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。最小记录和最大记录的heap_no值分别是0和1，也就是说它们的位置最靠前

n_owned：页目录中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为n_owned字段
next_record：记录头信息里该属性非常重要，它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

比如：第一条记录的next_record值为32，意味着从第一条记录的真实数据的地址处向后找32个字节便是下一条记录的真实数据。注意，下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定Infimum记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）。

下图用箭头代替偏移量表示next_record。

compact_head2

删除操作

从表中删除掉一条记录，这个链表也是会跟着变化：

1	DELETE FROM page_demo WHERE c1 = 2;

delete_compact_head

从图中可以看出来，删除第2条记录前后主要发生了这些变化：

第2条记录并没有从存储空间中移除，而是把该条记录的delete_mask值设置为1。
第2条记录的next_record值变为了0，意味着该记录没有下一条记录了。
第1条记录的next_record指向了第3条记录。
最大记录的n_owned值从5变成了4。

添加操作

主键值为2的记录被我们删掉了，但是存储空间却没有回收，如果我们再次把这条记录插入到表中，会发生什么事呢？

1	INSERT INTO page_demo VALUES(2, 200, 'tong');

add_compact_head

直接复用了原来被删除记录的存储空间。

说明：
当数据页中存在多条被删除掉的记录时，这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后重用这部分存储空间。

4）记录的真实数据

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

列名	是否必须	占用空间	描述
row_id	否	6字节	行ID，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

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

一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

5）举例：分析Compact行记录的内部结构

CREATE TABLE mytest(
	col1 VARCHAR(10),
	col2 VARCHAR(10),
	col3 CHAR(10),
	col4 VARCHAR(10)
)ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;

INSERT INTO mytest
VALUES
('a','bb','bb','ccc'),
('d','ee','ee','fff'),
('d',NULL,NULL,'fff');

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

------------------------------------------------------------------------------------------
0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00|supremum........|
0000c080 2c 00 00 00 2b 68 00 00 00 00 00 06 05 80 00 00|,...+h..........|
0000c090 00 32 01 10 61 62 62 62 62 20 20 20 20 20 20 20|.2..abbbb|
0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00|ccc........+...|
0000c0b0 2b 68 01 00 00 00 00 06 06 80 00 00 00 32 01 10|+h...........2..|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66|deeeefff|
0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 2b 68 02 00 00|..........+h...|
0000c0e0 00 00 06 07 80 00 00 00 32 01 10 64 66 66 66 00|........2..dfff.|
------------------------------------------------------------------------------------------

第一条数据分析('a','bb','bb','ccc')

十六进制码	解释
`73 75 70 72 65 6d 75 6d`	supremum最大记录结尾
`03 02 01`	变长字段长度列表，逆序 col4的值为ccc，长度为3，col2的值为bb，长度为2， col1的值为a，长度为1
`00`	NULL标志位，第一条没有NULL值
`00 00 10`	Record Header，固定5字节长度预留位1和2、delete_mask、mini_rec_mask都是0，占4位`0` n_owned 占4位 `0` `0010` heap_no 占十三位，由于是第2条记录，0和1是最小最大记录，所以二进制 `0000 0000 0001 0` record_type 占三位，普通记录，所以二进制 `000`
`00 2C`	next_record，指向下一条记录next_record字段的末尾本条记录的next_record字段末尾距离下一条记录的next_record末尾相差44个字节所以本条记录的next_reocrd是44个字节，16进制2C
`00 00 00 2b 68 00`	RowID InnoDB自动创建，6字节
`00 00 00 00 06 05`	TransactionID
`80 00 00 00 32 01 10`	Roll Pointer
`61`	列1数据’a’
`62 62`	列2数据’bb’
`62 62 20 20 20 20 20 20 20 20`	列3数据’bb’，由于类型是char(10)，20代表空格，填充8个空格
`63 63 63`	列4数据’ccc’

注意1：InnoDB每行有隐藏列TransactionID和Roll Pointer。

注意2：固定长度CHAR字段在未能完全占用其长度空间时，会用0x20来进行填充。

注意3：Record Header的最后两个字节，这两个字节代表next_recorder，0x2c代表下一个记录的偏移量，当前记录的next_record字段末尾，加上偏移量0x2c个字节，就是下条记录next_record字段末尾。

分析有NULL值的第三条数据('d',NULL,NULL,'fff')

---------------------------------------------------------------------
03 01                       /*变长字段长度列表，逆序*/
06                          /*NULL标志位，第三行有NULL值*/
00 00 20 ff 98              /*Record Header*/
00 00 00 2b 68 02           /*RowID*/
00 00 00 00 06 07           /*TransactionID*/
80 00 00 00 32 01 10        /*Roll Pointer*/
64                          /*列1数据'd'*/
66 66 66                    /*列4数据'fff'*/
---------------------------------------------------------------------

第三行有NULL值，因此NULL标志位不再是00而是06，转换成二进制为00000110，为1的值代表第2列和第3列的数据为NULL。在其后存储列数据的部分，用户会发现没有存储NULL列，而只存储了第1列和第4列非NULL的值。

因此这个例子很好地说明了：不管是CHAR类型还是VARCHAR类型，在compact格式下NULL值都不占用任何存储空间。

next_record

第三条记录的next_record的值是ff 98，是一个负数，负的104个字节 43+44+17=104

第三条记录是最后一条记录，最后一条记录的next_record字段指向最大记录

第二条记录next_record是43，即第二条记录的next_record字段结尾距离第三条记录的next_record字段结尾，相差43个字节

第一条记录的next_record是44

第一条记录的next_record字段结尾距离最大记录的next_record字段末尾，相差17个字节

所以第三条记录的next_record的值是负104

3.3 Dynamic和Compressed行格式

VARCHAR(M)类型，最多可以存放字节数

1
2
3

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

ERROR 1118 (42000): 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个字节除了列本身的数据之外，还包括一些其他的数据，以Compact行格式为例，比如说我们为了存储一个VARCHAR(M)类型的列，除了真实数据占有空间以外，还需要记录的额外信息。

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

如果有not null属性，那么就不需要NULL值标识，也就可以多存储一个字节，即65533个字节

CREATE  TABLE  varchar_size_demo(
	c  VARCHAR(65532)          # 65533 + 2个字节的变长字段的长度 + 1NULL值标识
    ch VARCHAR(65533) NOT NULL # 65533 + 2个字节的变长字段的长度
)  CHARSET=ascii  ROW_FORMAT=Compact;

行溢出

我们可以知道一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录，这种现象称为行溢出

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

在MySQL 8.0中，默认行格式就是Dynamic，Dynamic、Compressed行格式和Compact行格式挺像，只不过在处理行溢出数据时有分歧

Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。在数据页中只存放20个字节的指针（溢出页的地址），实际的数据都存放在Off Page（溢出页）中。
Compact和Redundant两种格式会在记录的真实数据处存储一部分数据（存放768个前缀字节）。

Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。

3.4 Redundant行格式

Redundant是MySQL 5.0版本之前InnoDB的行记录存储方式，MySQL 5.0支持Redundant是为了兼容之前版本的页格式。

1	ALTER TABLE record_test_table ROW_FORMAT=Redundant;

从上图可以看到，不同于Compact行记录格式，Redundant行格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的。

字段长度偏移列表

注意Compact行格式的开头是变长字段长度列表，而Redundant行格式的开头是字段长度偏移列表，与变长字段长度列表有两处不同：

少了“变长”两个字：Redundant行格式会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表。
多了“偏移”两个字：这意味着计算列值长度的方式不像Compact行格式那么直观，它是采用两个相邻数值的差值来计算各个列值的长度。

举例：比如第一条记录的字段长度偏移列表就是：
2B 25 1F 1B 13 0C 06

因为它是逆序排放的，所以按照列的顺序排列就是：
06 0C 13 17 1A 24 25

按照两个相邻数值的差值来计算各个列值的长度的意思就是：

第一列(row_id)的长度就是 0x06个字节，也就是6个字节。
第二列(transaction_id)的长度就是 (0x0C - 0x06)个字节，也就是6个字节。
第三列(roll_pointer)的长度就是 (0x13 - 0x0C)个字节，也就是7个字节。
第四列(col1)的长度就是 (0x1B - 0x13)个字节，也就是8个字节。
第五列(col2)的长度就是 (0x1F - 0x1B)个字节，也就是4个字节。
第六列(col3)的长度就是 (0x25 - 0x1F)个字节，也就是6个字节。
第七列(col4)的长度就是 (0x2B - 0x25)个字节，也就是6个字节。

记录头信息（record header）

不同于Compact行格式，Redundant行格式中的记录头信息固定占用6个字节（48位），每位的含义见下表。

名称	大小（bit）	描述
（）	1	未使用
（）	1	未使用
deleted_mask	1	该行是否已被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	该记录拥有的记录数
heap_no	13	索引堆中该条记录的位置信息
`n_fields`	10	记录中列的数量
`1byte_offs_flag`	1	记录字段长度偏移列表中每个列对应的偏移量使用1个字节还是2个字节表示
next_record	16	页中下一条记录的绝对位置

与Compact行格式的记录头信息对比来看，有两处不同：

Redundant行格式多了n_field和1byte_offs_flag这两个属性。
Redundant行格式没有record_type这个属性。

n_fields：代表一行中列的数量，占用10位，所以MySQL一个行支持最多的列为1023。1byte_offs_flags，定义了偏移列表占用1个字节还是2个字节。当它的值为1时，表明使用1个字节存储。当它的值为0时，表明使用2个字节存储。

1byte_offs_flag的值是怎么选择的

每个列对应的偏移量可以占用1个字节或者2个字节来存储，那到底什么时候用1个字节，什么时候用2个字节呢？其实是根据该条Redundant行格式记录的真实数据占用的总大小来判断的：

当记录的真实数据占用的字节数值不大于127（十六进制0x7F，二进制01111111）时，每个列对应的偏移量占用1个字节。
当记录的真实数据占用的字节数大于127，但不大于32767（十六进制0x7FFF，二进制0111111111111111）时，每个列对应的偏移量占用2个字节。

有没有记录的真实数据大于32767的情况呢？有，不过此时的记录已经存放到了溢出页中，在本页中只保留前768个字节和20个字节的溢出页面地址。因为字段长度偏移列表处只需要记录每个列在本页面中的偏移就好了，所以每个列使用2个字节来存储偏移量就够了。
大家可以看出来，Redundant行格式还是比较简单粗暴的，直接使用整个记录的真实数据长度来决定使用1个字节还是2个字节存储列对应的偏移量。只要整条记录的真实数据占用的存储空间大小大于127，即使第一个列的值占用存储空间小于127，那对不起，也需要使用2个字节来表示该列对应的偏移量。简单粗暴，就是这么简单粗暴（所以这种行格式有些过时了）。
为了在解析记录时知道每个列的偏移量是使用1个字节还是2个字节表示的，Redundant行格式特意在记录头信息里放置了一个称之为1byte_offs_flag的属性

Redundant行格式中NULL值的处理

因为Redundant行格式并没有NULL值列表，所以Redundant行格式在字段长度偏移列表中的各个列对应的偏移量处做了一些特殊处理————将列对应的偏移量值的第一个比特位作为是否为NULL的依据，该比特位也可以被称之为NULL比特位。也就是说在解析一条记录的某个列时，首先看一下该列对应的偏移量的NULL比特位是不是为1。如果为1，那么该列的值就是NULL，否则不是NULL。

这也就解释了上边介绍为什么只要记录的真实数据大于127（十六进制0x7F，二进制01111111）时，就采用2个字节来表示一个列对应的偏移量，主要是第一个比特位是所谓的NULL比特位，用来标记该列的值是否为NULL。

CREATE TABLE record_test_table (
	c1 VARCHAR(10),
	c2 VARCHAR(10),
	c3 CHAR(10),
	c4 VARCHAR(10)
) CHARSET=ascii ROW_FORMAT=REDUNDANT;

INSERT INTO record_test_table(col1, col2, col3, col4) 
VALUES
('zhangsan', 'lisi', 'wangwu', 'songhk'), 
('tong', 'tom', NULL, NULL);

但是还有一点要注意，对于值为NULL的列来说，该列的类型是否为定长类型决定了NULL值的实际存储方式，我们接下来分析一下record_test_table表的第二条记录，它对应的字段长度偏移列表如下

A4 A4 1A 17 13 0C 06

按照列的顺序排放就是：

06 0C 13 17 1A A4 A4

我们分情况看一下：

如果存储NULL值的字段是定长类型的，比方说CHAR(M)数据类型的，则NULL值也将占用记录的真实数据部分，并把该字段对应的数据使用0x00字节填充。

第二条记录的c3列的值是NULL，而c3列的类型是CHAR(10)，占用记录的真实数据部分10字节，所以我们看到在Redundant行格式中使用0x00000000000000000000来表示NULL值。

另外，c3列对应的偏移量为0xA4，它对应的二进制实际是：10100100，可以看到最高位为1，意味着该列的值是NULL。将最高位去掉后的值变成了0100100，对应的十进制值为36，而c2列对应的偏移量为0x1A，也就是十进制的26。36 - 26 = 10，也就是说最终c3列占用的存储空间为10个字节。
如果该存储NULL值的字段是变长数据类型的，则不在记录的真实数据处占用任何存储空间。

比如record_test_table表的c4列是VARCHAR(10)类型的，VARCHAR(10)是一个变长数据类型，c4列对应的偏移量为0xA4，与c3列对应的偏移量相同，这也就意味着它的值也为NULL，将0xA4的最高位去掉后对应的十进制值也是36，36 - 36 = 0，也就意味着c4列本身不占用任何记录的实际数据处的空间。

除了以上的几点之外，Redundant行格式和Compact行格式还是大致相同的。

4. 区、段和碎片区

4.1 为什么要有区？

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的使用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，就是所谓的随机I/O。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/O是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/O。

引入区的概念，一个区就是物理位置上连续的64个页。因为InnoDB中的页的大小默认是16KB，所以一个区的大小是64*16KB=1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页的单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足以填充满整个区），但是从性能角度看，可以消除很多的随机I/O，功大于过！

4.2 为什么要有段？

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

除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点。

在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。这从一定程度上简化了DBA对于段的管理。

段其实不对应表空间中的某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

4.3 为什么要有碎片区？

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M（64*16KB=1024KB）存储空间，所以**默认情况下一个只存在几条记录的小表也需要2M的存储空间么？**以后每次添加一个索引都要多申请2M的存储空间么？这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。

为了考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB提出了一个碎片（fragment）区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页面用于段A，有些页面用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。

所以此后为某个段分配存储空间的策略是这样的：

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

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

4.4 区的分类

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

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

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

5. 表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。

表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。表空间数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间（System tablespace）、 独立表空间（File-per-table tablespace）、 撤销表空间（Undo Tablespace）和 临时表空间（Temporary Tablespace）等。

5.1 独立表空间

独立表空间，即每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间 (即：单表) 可以在不同的数据库之间进行迁移。

空间可以回收 (DROP TABLE 操作可自动回收表空间；其他情况，表空间不能自己回收) 。如果对于统计分析或是日志表，删除大量数据后可以通过：alter table TableName engine=innodb;回收不用的空间。对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

独立表空间结构

独立表空间由段、区、页组成。

真实表空间对应的文件大小

我们到数据目录里看，会发现一个新建的表对应的.ibd文件只占用了96K，才6个页面大小 (MySQL5.7中)，这是因为一开始表空间占用的空间很小，因为表里边都没有数据。不过别忘了这些.ibd文件是自扩展的，随着表中数据的增多，表空间对应的文件也逐渐增大。

MySQL8新建表对应的.ibd文件会有7个页大小，因为表结构和表数据信息存放在一块了。MySQL5.7是分开存储的。

查看InnoDB的表空间类型

show variables like 'innodb_file_per_table'
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

innodb_file_per_table=ON, 这就意味着每张表都会单词保存一个.ibd文件。

5.2 系统表空间

系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，这部分是独立表空间中没有的。

InnoDB数据字典

每当我们向一个表中插入一条记录时，MySQL校验过程如下：

先要校验一下插入语句对应的表存不存在，插入的列和表中的列是否符合。如果语法没有问题的话，还需要知道该表的聚簇索引和所有二级索引对应的根页面是哪个表空间的哪个页面，然后把记录插入对应索引的B+树中。所以说，MySQL除了保存着我们插入的用户数据之外，还需要保存许多额外的信息，比如说：

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

上述这些数据并不是我们使用INSERT语句插入的用户数据，实际上是为了更好的管理我们这些用户数据而不得以引入的一些额外数据，这些数据页称为元数据。InnoDB存储引擎特意定义了一些列的内部系统表 (internal system 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_COLUMNS、SYS_INDEXES、SYS_FIELDS这四个表尤其重要，称之为基本系统表(basic system tables) ，我们先看看这4个表的结构：

SYS_TABLES表结构

列名	描述
`NAME`	表的名称。主键
`ID`	InnoDB存储引擎中每个表都有一个唯一的ID。（二级索引）
`N_COLS`	该表拥有列的个数
`TYPE`	表的类型，记录了一些文件格式、行格式、压缩等信息
`MIX_ID`	已过时，忽略
`MIX_LEN`	表的一些额外的属性
`CLUSTER_ID`	未使用，忽略
`SPACE`	该表所属表空间的ID

SYS_COLUMNS表结构

列名	描述
`TABLE_ID`	该列所属表对应的ID。(与`POS`一起构成联合主键）
`POS`	该列在表中是第几列。
`NAME`	该列的名称
`MTYPE`	main data type主数据类型，例如INT、CHAR、VARCHAR、FLOAT、DOUBLE等。
`PRTYPE`	precise type精确数据类型，修饰主数据类型的属性，如是否允许 NULL 值,是否允许负数
`LEN`	该列最多占用存储空间的字节数。
`PREC`	该列的精度，不过这列貌似都没有使用，默认值通常为0。

SYS_INDEXES表结构

列名	描述
`TABLE_ID`	该索引所属表对应的ID。（与`ID`一起构成联合主键）
`ID`	InnoDB存储引擎中每个索引都有一个唯一的ID。
`NAME`	该索引的名称。
`TYPE`	该索引的类型，如聚簇索引、唯一索引、更改缓冲区的索引，全文索引、普通的二级索引等。
`N_FIELDS`	该索引包含的列的个数。
`SPACE`	该索引根页面所在的表空间ID。
`PAGE_NO`	该索引根页面所在的页号。
`MERGE_THRESHOLD`	如果页面中的记录被删除到某个比例，就把该页面和相邻页面合并，这个值就是这个比例

SYS_FIELDS表结构

列名	描述
`INDEX_ID`	该索引列所属的索引的ID。（与 `POS` 一起构成联合主键）
`POS`	该索引列在某个索引中是第几列。
`COL_NAME`	该索引列的名称。

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

USE information_schema;
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                      |
+--------------------------------------------+

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

附录：数据页加载的三种方式

InnoDB从磁盘中读取数据 最小单位 是数据页。而你想得到的 id = xxx 的数据，就是这个数据页众多行中的一行。

对于MySQL存放的数据，逻辑概念上我们称之为表，在磁盘等物理层面而言是按 数据页 形式进行存放的，当其加载到 MySQL 中我们称之为 缓存页。

如果缓冲池没有该页数据，那么缓冲池有以下三种读取数据的方式，每种方式的读取速率是不同的：

1）内存读取

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

2）随机读取

如果数据没有在内存中，就需要在磁盘上对该页进行查找，整体时间预估在10ms左右，这10ms中有6ms是磁盘的实际等待时间（包括了寻道和半圈旋转时间），有3ms是对可能发生的排队时间的估计值，另外还有1ms的传输时间，将页从磁盘服务器缓冲区传输到数据库缓冲区中。这10ms看起来很快，但实际上对于数据库来说消耗的时间已经非常长了，因为这还只是一个页的读取时间。

3）顺序读取

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

第04章 逻辑架构