MySQL-原理篇-①架构

学习核心

• MySQL逻辑架构
• SQL的执行过程（SQL底层原理拆解）
  • 一条SQL语句的执行过程（结合MySQL逻辑架构拆解）
• 存储引擎分类&应用
  • 常见的存储引擎分类
  • Innodb VS MyISAM （存储文件、对索引的支持、对日志的支持（涉及事务控制、故障恢复场景）、对并发的支持（涉及锁粒度、并发性能）、内存利用度的对比）
  • MyISAM的场景应用（在什么场景下选用MyISAM）
  • InnoDB存储引擎
    • InnoDB存储的底层原理、数据处理原理
    • 索引相关

学习资料

• 高性能MySQL第三版
• MySQL官方文档-MySQL引擎分类
• MySQL是怎样运行的：从根儿上理解MySQL

逻辑架构

1.MySQL架构

逻辑架构

​ MySQL 是典型的 C/S 架构，即 Client / Server 架构，服务器端程序使用的 mysqld

​ 执行流程概述：客户端"进程"向服务器"进程"发送一段文本（SQL语句），服务器"进程"处理后再向客户端"进程"发送一段文本（处理结果）

​ 一个进程必定包含多个线程

​ MySQL逻辑架构可分为三层：连接层、服务层、引擎层

• 【1】连接管理：由连接器处理连接，进行身份认证、权限校验（进行用户名、密码以及权限等校验）
• 【2】解析和优化：解析SQL语句，生成一个解析树，随后对SQL语句进行优化处理
• 【3】存储引擎：调用存储引擎查询结果（文件系统会先将数据加载到缓冲池中），存储引擎有MyISAM、InnoDB、Memory等
• 【4】文件系统：包括错误日志、查询日志、配置文件等

完整架构图参考：

​

一般将MySQL的逻辑架构分为两层：Server层、存储引擎层。

​ Server 层：负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接池，执行器、优化器、解析器、预处理器、查询缓存等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现；

​ 存储引擎层：负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。

2.一条SQL的执行过程

SELECT语句的执行过程

​ 可以结合上述MySQL架构进行理解分析：

【1】连接管理：客户端通过连接器进行身份、权限校验，校验通过则放行（连接到MySQL服务）

【2】查询和优化：连接成功后向SQL接口发送SQL语句请求，SQL接收到请求会进入当前步骤：查询缓存=》SQL解析=》SQL查询优化

• 查询缓存：针对查询语句，MySQL会优先从查询缓存（Query Cache）中查找，如果缓存命中则直接返回结果，如果没有则进入下一步
  • 缓存中存储的key-value分别对应：SQL语句-检索结果
• 解析器：由解析器进行SQL解析操作（词法解析、语法解析），生成语法树。当词法分析和语法分析出错时，分析器会抛出异常（如语法结构出错、出现了无法识别的字符等）
  • 词法解析：MySQL 会根据输入的字符串识别出关键字出来
  • 语法解析：根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足语法规则，以构建出 SQL 语法树
• 优化器：由优化器进行查询优化，生成执行计划，然后交给执行器去执行
  • 在表中存在多个索引的时候，决定使用哪个索引
  • 在一个语句中有多表关联（join）的时候，决定各表的连接顺序

【3】执行引擎：执行器拿到执行计划之后会调用存储引擎执行操作、获取数据并响应给客户端

（1）连接器

​ ==连接器：==通过连接器建立连接，进行身份、权限校验，其核心流程说明如下

• 与服务端进行TCP三次握手建立连接
• 用户名、密码校验，校验不通过则报错
• 用户名、密码校验通过，连接器会获取该用户的权限然后保存起来，后续用户在此连接的任何操作都是基于连接开始时获取到的权限进行判断
  • 当一个用户已经建立连接，即使管理员中途修改了用户权限，也不会影响已经存在连接的权限。

# 命令行连接
mysql -u root -p
# 连接的过程需经过TCP三次握手，MySQL基于TCP协议传输，如果MySQL服务没有启动会报错，如果正常启动则进行账号校验
-- 输入密码进行校验（如果用户名或密码错误则提示Access denied for user）

查看MySQL服务被多少个客户端连接了？

 # 查看MySQL服务被多少个客户端连接
 show processlist;
 
 # output
 Id	User	Host	db	Command	Time	State	Info
70240	db_noob_demo	127.0.0.1:2050	db_noob_demo	Query	0	starting	show processlist

空闲连接会一直占用吗？

​ 可通过查看Command列确认连接状态，如果为Sleep则意味着该用户连接完 MySQL 服务就没有再执行过任何命令，即这是一个空闲的连接

​ MySQL定义了空闲连接的最大空闲时长，由wait_timeout参数控制(默认8h：28880s)，如果空闲连接超过了这个时间限制，连接器会自动将其断开

# 查看空闲连接的最大空闲时长
show variables like '%wait_timeout%';

# 可手动断开空闲的连接
kill connection +id

​ 当一个处于空闲状态的连接被服务端主动断开后，这个客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

MySQL 的连接数有限制吗？

​ MySQL 服务支持的最大连接数由 max_connections 参数控制，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”

# 查看MySQL 服务支持的最大连接数
show variables like '%max_connections%';

怎么解决长连接占用内存的问题？

• MySQL的长连接和短连接
  • 长连接：可以减少建立连接和断开连接的过程，所以一般建议使用长连接。但是也要注意长连接占用内存的问题，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象

// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

• 解决长连接占用内存的问题
  • 方式1：定期断开长连接
  • 方式2：客户端主动重置连接
    • MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

（2）查询缓存

​ MySQL8.0对查询缓存的改造：查询缓存往往弊大于利（命中率太低），因为只要有对表的更新，就会导致表上的所有查询缓存被清空。所以，MySQL8.0 版本直接将查询缓存删掉了。下述简单以几个案例说明大多数情况下查询缓存比较鸡肋

• 命中率太低：查询缓存时根据key来查找value值（key的校验规则很严格，它要求SQL语句必须完全一致（空格、注释、大小写等必须完全匹配），否则就当新的SQL语句进行处理）
• 针对一些函数应用时同样的语句可能返回不同的结果：例如select now(); 可能针对同样的SQL语句，预期是两次返回的结果不同，但基于缓存机制就可能存在第二次查询的结果是第一次缓存的内容，就会导致读取到了”脏数据“
• 存在查询缓存失效的场景：例如对表进行了insert、update、delete、truncate、alter、drop等语句操作，就会使得缓存清空（失效），因此对于一些更新频率比较高的表而言，查询缓存的命中率会非常低

​ 什么时候推荐使用查询缓存？

​ 一般建议在静态表（极少更新的表）中使用查询缓存，例如一个系统配置表、字典表等，MySQL提供了按需使用的方式，可以将my.cnf参数query_cache_type设置为DEMAND，代表当SQL语句中有SQL_CACHE关键词的时候才使用缓存：

# query_cache_type有3个值：0代表关闭OFF、1代表开启ON、2代表DEMAND（按需使用）
query_cache_type=2

# 查询配置值（mysql5.7.44可查询，MySQL8.0之后已经取消掉查询缓存，也就没有这个配置）
show variables like '%query_cache_type%'

# 按需使用触发
select SQL_CACHE * from test where id=5;
# 不走查询缓存
select SQL_NO_CACHE * from test where id=5;

# 查看查询缓存相关参数
show status like '%Qcache%';

Variable_name	Value	说明
Qcache_free_blocks	1	表示查询缓存还有多少剩余的blocks 如果该值较大，则说明查询缓存中的内存碎片过多，可能在一定的时间整理
Qcache_free_memory	33537320	查询缓存的内存大小 DBA可通过该参数判断当前系统的查询内存是否够用进而进行调整
Qcache_hits	0	命中次数
Qcache_inserts	0	插入次数（多少次未命中后插入）
Qcache_lowmem_prunes	0	记录多少条查询因为内存不足而移除查询缓存（通过这个值可适当调整缓存大小）
Qcache_not_cached	124230	表示因为query_cache_type的设置而没有被缓存的查询数量
Qcache_queries_in_cache	0	当前缓存的查询数量
Qcache_total_blocks	1	当前缓存的block数量

（3）解析器

# SQL语句
select username,ismale
from userinfo
where age > 20;

【1】词法解析：识别关键字

关键字	非关键字	关键字	非关键字
select	username，ismale	from	userinfo

==【2】语法解析：==根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足语法规则，构建SQL语法树（参考示例如下）

​ 如果输入的SQL语句语法不对，就会抛出相关异常信息提示

（4）执行SQL

​ 经过解析器后，进入执行 SQL 查询语句的流程了，由执行器选择相应的引擎执行SQL操作，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

• prepare 阶段，预处理阶段；
• optimize 阶段，优化阶段；
• execute 阶段，执行阶段；

执行阶段拆解

预处理器：以select * from test;SQL语句为例，其在预处理阶段主要做了如下操作

• 检查 SQL 查询语句中的表或者字段是否存在；
• 将 select * 中的 * 符号，扩展为表上的所有列；

​ 如果test表不存在则会抛出异常提示（结合MySQL8.0源码查看其抛出的堆栈异常是在get_table_share()函数中报错，该函数是在prepare阶段调用的）

​ MySQL5.7判断表或者字段是否存在的工作是在词法分析&语法分析之后，prepare 阶段之前做的（并不是解析器）；正因为 MySQL 5.7 代码结构不好，所以 MySQL 8.0 代码结构变化很大，后来判断表或字段是否存在的工作就被放入到 prepare 阶段做了

优化器：经过预处理阶段之后，还需通过优化器为SQL查询语句制定一个执行计划（例如表中有多个索引时，优化器会基于查询成本考虑来决定使用哪个索引）

​ 可通过explain来查看SQL语句的执行计划，执行计划中的key表示执行计划过程中使用了哪个索引；如果key为null则说明没有使用索引，就会执行全表扫描（type=ALL）（这种查询方式效率最低）

执行器：获取到执行计划，则由执行器与存储引擎进行交互（交互以记录为单位），执行SQL语句获取响应数据并返回客户端。此处可结合三种方式（主键索引查询、全表扫描、索引下推）来理解其执行过程

执行方式拆解

主键索引查询

​ 以select * from product where id = 1;语句为例进行分析，该查询语句使用了主键索引、等值查询，主键ID具有唯一性。于是优化器决定选用访问类型为const进行查询，即使用主键索引查询一条记录，则执行器与存储引擎的执行流程说明如下（结合源码理解）：

• 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录。
• 存储引擎通过主键索引的 B+ 树结构定位到 id = 1的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；
• 执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合则跳过该记录。
• 执行器查询的过程是一个 while 循环，所以还会再查一次，但是这次因为不是第一次查询了，所以会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了。

​

​ 总结：执行器执行查询会依次根据索引检索满足条件的记录，随后判断记录是否符合查询条件，符合则发送、不符合则跳过

全表扫描

​ 以select * from product where name = 'iphone';语句为例进行分析，该查询语句没有使用到索引，因此优化器选用ALL方式进行查询（全表扫描），此时执行器与存储引擎的执行流程说明如下（结合源码理解）：

• 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 all，这个函数指针被指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录；
• 执行器会判断读到的这条记录的 name 是不是 iphone，如果不是则跳过；如果是则将记录发给客户的（Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录）。
• 执行器查询的过程是一个 while 循环，所以还会再查一次，会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 all，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；
• 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server层） 返回了读取完毕的信息；
• 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

​ 总结：执行器执行查询会依次检索表中的数据，如果符合查询条件则发送，不符合则跳过，直到所有的记录检索完成

索引下推

​ 索引下推是MySQL5.6中推出的查询优化策略，索引下推可减少二级索引在查询时的回表操作，从而提高查询效率（因为它将Server层负责的部分事情交给存储引擎层去处理了）

​ 以select * from t_user where age>20 and reward = 100000;为例（此处对age、reward字段建立了联合索引（age，reward）），联合索引遇到范围查询（>、<）就会停止匹配，即此处age字段可以用到联合索引，而reward无法利用索引。

• 在没有使用索引下推（MySQL5.6之前的版本），执行器和存储引擎的执行流程说明如下：

  • Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，即定位到 age > 20 的第一条记录；
  • 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
  • Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；
  • 随后继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层，以此类推，直到存储引擎把表中的所有记录读完

• 使用了索引下推（MySQL5.6之后的版本），执行器和存储引擎的执行流程说明如下：

  • Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，即定位到 age > 20 的第一条记录；
  • 存储引擎定位到二级索引后，先不执行回表操作，而是先判断该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。
  • Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。以此类推，直到存储引擎把表中的所有记录读完

​ 结合上述执行过程分析，使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作

​ 可通过explain关键字分析SQL执行计划，进而确认是否引入了索引下推。如果执行计划的Extr部分中有Using Index condition则说明使用了索引下推

（5）SELECT 语句执行过程总结

• 【1】连接器：建立连接、管理连接、校验用户身份（账号校验、权限校验）
  • 建立连接：基于TCP协议构建连接，根据业务场景选择连接方式（短连接、长连接）
  • 账号校验：用户输入用户名密码进行校验，校验失败则抛出异常提示
  • 权限校验：用户账号校验通过则获取到其相关权限并存储，后续所有基于本次连接的SQL操作的访问权限都是基于此（期间如果权限变更也不会影响已经建立了的连接权限）
• 【2】SQL解析：
  • 查询缓存：校验查询缓存是否命中（匹配key：SQL语句），如果命中直接返回结果（对应value），如果未命中则进入下一步
    • MySQL8升级版本中已经取消掉查询缓存概念
  • 解析器：通过词法解析、语法解析构建语法树，如果解析出错则抛出异常
    • 词法解析：解析关键字（select、from）
    • 语法解析：根据语法规则解析语法，构建相应的语法树
• 【3】SQL执行：先后经历预处理、优化、执行三个阶段
  • 预处理阶段：预处理器会判断table是否存在，并将*拆解为对应的字段
    • MySQL5.7前table是否存在的校验是在词法分析和语法分析之后、预处理之前（并不是解析器中）
    • MySQL5.7后table是否存在的校验是在执行的预处理阶段中进行判断
  • 优化阶段：优化器会根据SQL语句定义生成SQL执行计划（选择最优效率），可通过explain关键字查看SQL语句的执行计划
  • 执行阶段：执行器获取到SQL执行计划之后会与存储引擎进行交互，获取响应数据返回给客户端（可结合多种执行方式分析其流程）
    • 主键索引查询
    • 全表扫描查询
    • 索引下推（MySQL5.6之后的版本引入索引下推，以减少联合索引检索场景的回表次数）

UPDATE 语句 的执行过程

​ 在数据库中的update操作实际上包括了更新、插入、删除。要理解UPDATE语句的执行原理，首先需要掌握几个基本概念

缓冲池：buffer pool

​ InnnoDB 的数据都是放在磁盘上的，InnoDB 操作数据有一个最小的逻辑单位，叫做页（索引页和数据页）。对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了。InnoDB 使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫 Buffer Pool。下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。

​ 修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候，称之为脏页。InnoDB 里面有专门的后台线程把 BufferPool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

​ BufferPool 是 InnoDB 里面非常重要的一个结构，它的内部又分成几块区域

和查询语句执行过程很像，连接器，分析器，优化器，执行器，不过和查询不同的是，更新流程还涉及到日志模块（binlog归档日志，redolog重做日志）。

redolog

​ 场景引入：何为粉板记账？例如赊账的时候一般做法是直接翻账本，然后将赊账记录直接修改；而基于这个场景就会导致每次都要去翻找账本找到对应的人再计算随后修改结果，效率极为低下。但如果借助粉版则可将本次赊账动作记录下来，待到打烊之后再将结果写回账本。

​ 类似地，如果MySQL的每一次更新操作都要写进磁盘，则需磁盘找到对应记录然后更新，整个IO操作、查找成本都很高，为了解决这个问题，MySQL的设计者采用类似“酒店掌柜粉板”的思路来提升更新效率。而粉版和账本配合的整个过程，则为MySQL的WAL技术（Write-Ahead Logging）：其关键在于先写日志后写磁盘

​ 当有一条记录需要更新的时候，Innodb引擎就会先把记录写到redolog里面，并更新内存，这个时候更新就完成了，同时innodb引擎会在适当的时候（一般是在系统比较空闲的时候），将这个操作记录更新到磁盘里面。

​ 基于上述场景，思考一个问题，如果出现“粉板满了”的情况该如何处理？例如某日的要处理的东西不多则可等空闲的时候处理，但是如果某日要处理的内容非常多，则需要先更新一部分数据到账本中，随后腾出粉板空间。基于redolog，InnoDB可以保证及时数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe（即只要记录在粉板上的数据，就算数据库异常重启，也可根据粉板记录去恢复数据）

​ innoDb的redolog是固定大小的，从头开始写，往复循环，写满的时候就必须要更新库里，删除部分记录才能继续写入。

binlog

​ redolog日志是innodb引擎特有的日志，其他引擎无法使用，server层自己的日志称为 binlog（归档日志），但是binlog并不具有cash-safe能力。（MySQL自带引擎是MyISAM，它是没有carsh-safe能力的，而binlog只能用于归档，InnoDB是另一个公司以插件形式引入到MySQL扩展应用的）

binlog & redolog的区别

• redo是innodb引擎特有的，binlog是mysql的server层实现的，所有引擎都能用
• redolog是循环写的，binlog是可以追加写入，追加写是指binlog文件写到一定大小后，会切换到下一个，并不会覆盖之前的日志
• redo是物理日志，记录的是 “某个数据越上做了什么修改”，binlog是逻辑日志，有两种格式statment格式记录sql语句，row格式记录更新前后行的内容

基于上述对redolog和binlog的概念理解，详细看下执行器在执行update语句的流程，update T set c=c+1 where ID=2;

• 执行器先找引擎取id=2这一行，引擎直接用树搜索找到这一行，如果这一行本来就在内存中，就直接返回给执行，否则需要先从磁盘读入内存，然后再返回
• 执行器拿到引擎给的行数据，把这个值加上1，比如原来是n，现在就是n+1得到新的行数据，再调用引擎接口写入这行新数据
• 引擎将这行数据更新到内存中，同时将这个更新操作记录到redolog里面，此时redolog处于prepare状态。然后告知执行器执行完成了，随时可以提交事务
• 执行器生成这个操作的binlog，并把binlog写入磁盘
• 执行器调用引擎的提交事务接口，引擎把刚刚写入redolog改成提交状态，更新完成

​ 最后三步就是两阶段提交，其目的和事务类似就是为了让两个日志文件对应的保持一致，恢复的数据保持一致。如果有任意一个日志在生成工程中出现异常那么两个日志里关于更新这次都将不能成功生成，这次更新操作也会失败回滚。

存储引擎

1.存储引擎分类

​ 文件系统中，MySQL将每个数据库（schema）保存为数据目录下的子目录，创建表时MySQL会在数据库子目录下创建一个和表同名的.frm文件保存报的定义。因为MySQL使用文件系统的目录和文件夹来保存数据库和表的定义，其大小写敏感性和具体的平台相关（windows中大小写不敏感，unix中大小写敏感）。不同的存储引擎保存数据和索引的方式不同，但表的定义是在MySQL服务层中统一处理。

# 查看数据表信息
show table status like 'user'				

列	值	说明
Name	user	表名
Engine	InnoDB	存储引擎类型（旧版本中列名为Type）
Version	10
Row_format	Dynamic	行的格式 对于MyISAM表可选值为Dynamic、Fixed、Compressed
Rows	8	表中的行数 对于MyISAM和其他一些存储引擎该值是精确的，对于InnoDB引擎该值是估值
Avg_row_length	2048	平均每行包括的字节数
Data_length	16384	表数据大小（单位：字节）
Max_data_length	0	表数据的最大容量（该值和存储引擎有关）
Index_length	32768	索引大小（单位：字节）
Data_free	0	对于MyISAM表其表示已分配但目前没有使用的空间 （这部分空间包括之前删除的行以及后续可被INSERT利用到的空间）
Auto_increment	NULL	下一个Auto_increment的值
Create_time	2024-06-24 08:49:46	创建时间
Update_time	2024-06-24 08:49:46	修改时间
Check_time	NULL	使用check table命令或者myisamchk工具最后一次检查表的时间
Collation	utf8mb4_general_ci	默认字符集和字符排序规则
Checksum	NULL	如果启用，保存的是整个表的实时校验和
Create_options	/	创建表时指定的其他选项
Comment	/	保存表的额外信息 对于MyISAM表，保存的是表创建的时候带的注释 对于InnoDB表，保存的是InnoDB表空间的剩余空间信息

# 查看MySQL提供的引擎类型
show engines;

# 查看默认的存储引擎
show variables like '%storage_engine%';

# 引擎相关命令
show create table table_name：查看一张表的存储引擎
create table .... ENGINE=InnoDB：创建表时指定存储引擎
alter table table_name ENGINE=MyISAM：修改一张表的存储引擎
mysql_convert_table_fromat --user=user_name --password=user_pwd --engine=MyISAM database_name; # 批量修改一个库的所有表的存储引擎

2.MyISAM VS InnoDB

​ MyISAM引擎是MySQL官方基于早期的ISAM引擎改良而来的，它是一款“苗根正红”的引擎，由于其不错的性能表现，再加上丰富的特性支持（全文索引、压缩机制、空间索引/函数等），在MySQL5.5版本之前，也一直是MySQL默认的存储引擎。

​ 但随着时间慢慢推移，MySQL官方开始主推使用InnoDB作为表的引擎，到了MySQL5.6及以后版本中，选择InnoDB作为MySQL默认的存储引擎

案例准备：分别创建MyISAM、InnoDB不同存储引擎的数据表，查看其文件系统结构（MySQL5.7.44）

# InnoDB
CREATE TABLE t_innodb (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# MyISAM
CREATE TABLE t_myisam (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

# mac中查看mysql服务器安装路径
which mysql
-- /opt/homebrew/opt/mysql@5.7/bin/mysql
# 查看mysql服务实际安装目录（输出mysql启动时的默认选项，其中可能包括配置文件路径）
mysql --verbose --help | grep -A 1 'Default options'

# 查看数据文件存储位置
SHOW VARIABLES LIKE 'datadir';

​ 进入到数据文件存储位置，然后分别查看创建的表关联的文件内容

• MyISAM：t_myisam表在本地生成3个磁盘文件

  • t_myisam.frm：存储表结构信息
  • t_myisam.MYD：存储表的行数据
  • t_myisam.MYI：存储表的索引数据

• InnoDB：t_innodb表在本地生成两个磁盘文件

  • t_innodb.frm：存储表结构信息
  • t_innodb.ibd：存储表的行数据和索引数据

索引支持的对比

​ 结合上述基于两种存储引擎类型分别创建的表，其关联的磁盘文件存储不同，因此决定了其支持的索引类型（结合索引定义来进行理解）

• MyISAM：只支持非聚簇索引
• InnoDB：支持聚簇索引

聚簇索引 VS 非聚簇索引

• 聚簇索引：也被称为聚集索引、簇类索引；逻辑上连续且物理空间上的连续
• 非聚簇索引：也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引；逻辑上的连续，物理空间上不连续

​ 结合数组和链表这两种存储方式的区别来理解何为逻辑或物理上的连续：

• 数组是物理空间上的连续，存储的所有元素都会按序存放在同一块内存区域中
• 链表是逻辑上的连续，存储的所有元素可能不在同一块内存，元素之间以指针连接

​ 索引大部分都是使用B+Tree结构存储，所以在磁盘中数据是以树结构存放的，所以连续并不是指索引节点，而是指索引数据和表数据，也就是说聚簇索引中，索引数据和表数据在磁盘中的位置是一起的，而非聚簇索引则是分开的，索引节点和表数据之间，用物理地址的方式维护两者的联系

​ 一张表中只能存在一个聚簇索引，一般都会选用主键作为聚簇索引，其他字段上建立的索引都属于非聚簇索引，或者称之为辅助索引、次级索引。但也不要走进一个误区，虽然MySQL默认会使用主键上建立的索引作为聚簇索引，但也可以指定其他字段上的索引为聚簇索引，一般聚簇索引要求索引必须是非空唯一索引才行

如何理解MyISAM只支持非聚簇索引

​ 结合聚簇索引的定义，其要求逻辑上连续且物理空间上的连续，但是基于MyISAM存储引擎创建的表，其行数据和索引数据是分别存储在不同的磁盘文件中，也就注定了它无法支持聚簇索引。

不支持聚簇索引的好处

​ 对于MyISAM而言，但不支持聚簇索引也有好处，也就是无论走任何索引，都只需要一遍查询即可获得数据，而InnoDB引擎的表中，如果不走聚簇（主键）索引查询数据，走其他索引的情况下，都需要经过两遍（回表）查询才能获得数据。

​ 但这并不意味着MyISAM引擎查询数据就一定比InnoDB快，《高性能MySQL》中有一句话明确指明：“不要轻易相信「MyISAM比InnoDB快」之类的经验之谈，这个结论往往不是绝对的”

日志机制的对比（涉及事务机制、故障恢复场景）

事务机制场景：MyISAM不支持事务机制、InnoDB支持事务机制

​ InnoDB引擎中有两个自己专享的日志：undo-log、redo-log。

​ InnoDB在MySQL启动后，会在内存中构建一个undo_log_buffer缓冲区，同时在磁盘中也有相应的undo-log日志文件。使用InnoDB存储引擎的表，可以借助undo-log日志实现事务机制，支持多条SQL组成一个事务，可以保证发生异常的情况下，组成这个事务的SQL到底回滚还是提交，以确保事务的一致性。

​ 而MyISAM并未设计类似的技术，在启动时不会在内存中构建undo_log_buffer缓冲区，磁盘中也没有相应的日志文件，因此MyISAM并不支持事务机制。当出现业务异常的时候只能通过在客户端做事务补偿（例如用户在下订单付钱之后出现异常导致无法执行后面的新增订单、物流信息，则需要在客户端中记录一下，随后向MySQL发送一条反SQL来确保数据的一致性，可以理解为是一种补偿机制）

故障恢复场景：MyISAM不支持故障恢复、InnoDB支持故障恢复

​ InnoDB在启动时，同样会在内存中构建一个redo_log_buffer缓冲区，在磁盘中也会有相应的redo-log日志文件，所以当一条或多条SQL语句执行成功后，不论MySQL在何时宕机，只要这个事务提交了，InnoDB引擎都能确保该事务的数据不会丢失，也就以此保障了事务的持久性

​ 基于InnoDB引擎中redo-log日志的存在，只要事务提交，机器断电、程序宕机等各种灾难情况，都可以用redo-log日志来恢复数据。

​ 而MyISAM引擎同样没有redo-log日志，所以并不支持数据的故障恢复

对并发场景的支持（涉及锁粒度、并发性能）

• MyISAM 仅支持表锁，对并发场景的支持较差（并发线程操作通过锁表来限制）
• InnoDB 支持表锁、行锁，对并发场景的支持较好（锁的粒度小，并发冲突的概率低，并发支撑就越高）

MyISAM为什么不支持行锁？

​ 反向思考，当MyISAM如果支持行锁，在一些特定场景下会出现什么问题，从而理解其为什么不可以支持行锁

​ 以select * from t_student;为例（sid、name、sex、height），使用MyISAM引擎，并对sid创建了主键索引、对name创建普通索引、对sex、height创建联合索引（先忽略此处索引的合理性，结合案例理解为什么MyISAM不能实现行锁）

​ t_student表中存在3个索引，则在本地.MYI索引文件中会存在3棵B+树，且由于MyISAM不支持聚簇索引，因此3个索引是平级的，每棵B+树的索引键，都直接指向.MYD数据文件中的行数据地址。假设MyISAM要实现行锁，当要对一行数据加锁时，可以锁定一棵树中某一个数据，但无法锁定其他树的行数据

​ 例如执行select * from zz_students where sid = 1 for update; 其会走主键索引命中数据，假设此时对主键索引树ID=1的数据进行加锁；与此同时执行select * from zz_students where name = "noob" for update;，这条SQL会走name普通索引查询数据，此时又对普通索引树上的数据加锁（如果这两个SQL语句恰好查询的是同一条数据，就会造成问题）

​ 即MyISAM如果想要实现行锁，就会存在一个问题，**在基于不同索引查询数据时，可能会导致一行数据上加多个锁！**这样又会导致多条线程同时操作一个数据，所以又会因为多线程并发执行的原因，造成脏读、幻读、不可重复读这系列问题出现。因此MyISAM无法支持行锁，针对读写场景需要通过锁表阻塞等待（当一条SQL在写数据的时候，就算其他SQL是读数据也要阻塞等待，反之也是如此），对并发场景的支持较差

​ 分析InnoDB引擎，因为支持聚簇索引，表中就算没有显式定义主键，内部依旧会用一个隐藏列来作为聚簇索引的索引字段，所以InnoDB表中的索引，是有主次之分的，所有的次级索引，其索引值都存储聚簇索引的索引键，因此想要对一行数据加锁时，只需要锁定聚簇索引的数据即可

-- 通过主键索引查询数据
select * from zz_students where sid = 1 for update;
-- 通过普通索引查询数据
select * from zz_students where name = "noob" for update;

​ 通过主键索引查询的SQL语句，会直接定位到聚簇索引的数据，然后对ID=1的数据加锁。而第二条通过普通索引查询数据的SQL语句，经过查询后会得到一个值：ID=1，然后会拿着这个ID=1的值再去回表，在聚簇索引中再次查询ID=1的数据，找到之后发现上面已经有线程加锁了，当前线程就会阻塞等待上一个线程释放锁

​ 因为MyISAM引擎不支持聚簇索引，所以无法实现行锁，出现多条线程同时读写数据时，只能锁住整张表。而InnoDB由于支持聚簇索引，每个索引最终都会指向聚簇索引中的索引键，因此出现并发事务时，InnoDB只需要锁住聚簇索引的数据即可，而不需要锁住整张表，因此并发性能更高。且InnoDB不仅仅只满足于此，为了提升读-写并存场景下的并发度，InnoDB引擎又基于undo-log日志的版本链+事务快照，又推出了MVCC多版本并发控制技术，因此对于读-写共存的场景支持并发执行

内存利用度的对比

​ InnoDB几乎将内存开发到了极致，虽然InnoDB不像Memory引擎那样完全基于内存运行，但它将所有能够在内存完成的操作，全部都放在了内存中完成，无论是读写数据、维护索引结构也好，记录日志也罢，各类操作全部都在内存完成

​ 而MyISAM引擎过于依赖MySQL Server，对缓冲池、异步IO技术开发度不够

3.MyISAM的场景应用

统计总数的优化

​ 针对统计需求，例如统计订单数、平台用户总数、会员数.....各类需求，基本上都会在数据库中执行count()操作，对于count()统计行数的操作，在MyISAM引擎中会记录表的行数，也就是当执行count()时，如果表是MyISAM引擎，则可以直接获取之前统计的值并返回，可通过show table status like '%t_myisam%';获取表状态（仅限于统计全表数量，不加where限定）

​ InnoDB中统计全表数据时，会触发全表扫描，一行一行地统计表行数。但是如果统计中加了where条件筛选，此时MyISAM和InnoDB的工作机制是一样的

​

删除数据/表的优化

​ 当使用delete命令清空表数据时，MyISAM会直接重新创建表数据文件，而InnoDB则是一行行删除数据，因此对于清空表数据的操作，MyISAM比InnoDB快上无数倍。

​ 针对MyISAM引擎的表，对于delete过的数据不会立即删除，而且先隐藏起来，后续定时删除或手动删除。这样设计的好处在于提供了后悔的余地，当误删一张表的大量数据时，只要手速够快，手动将本地的.MYD、.MYI文件拷贝出去，就可以直接基于这两个数据文件恢复数据，而不需要通过日志或第三方工具修复数据

​

CRUD速度

读数据

​ InnoDB支持聚簇索引，因此整个表数据都会和聚簇索引一起放在一颗B+树中存储，就算当你没有定义主键时，InnoDB也会定义一个隐式字段ROW_ID来作为聚簇索引字段，这也就意味着：在InnoDB的表中，这个聚簇索引你不要也得要！可以借助聚簇索引来实现行级别的锁，但也要接受索引维护的成本。当查询数据时，如果在基于非聚簇索引查找数据，就算查到了也需要经过一次回表才能得到数据，同时插入数据、修改数据时，都需要维护聚簇索引和非聚簇索引之间的关系

​ 而在MyISAM引擎中，所有已创建的索引都是非聚簇索引，每个索引之间都是独立的，在索引中存储的是直接指向行数据的地址，而并非聚簇索引的索引键，因此无论走任何索引，都仅需一次即可获得数据，无需做回表查询

写数据

​ 写数据时，MyISAM也不需要维护不同索引之间的关系，毕竟每个索引都是独立的，因此MyISAM在理论上，读写数据的效率会高于InnoDB引擎

查询场景中MyISAM真的比InnoDB快吗

​ 结合下述官网测试图示分析：

​ ==如果是基于单个客户端连接的读写性能分析：==自然是MyISAM远超于InnoDB引擎，因为InnoDB需要维护聚簇索引会带来额外的性能牺牲

​ ==如果是基于多个客户端连接的读写性能分析：==随着连接数的增加，工作线程会不断增加，CPU使用核数也会不断增加，而InnoDB的性能会逐步上升，但MyISAM引擎基本上没有太大变化，基本上从头到尾一直都很低，这是由于锁机制导致的。MyISAM只支持表锁，因此无论多少个客户端连接同时到来，对于同一张表都只能允许一条线程进行操作，并发冲突很大，进而导致吞吐量严重下降。除非多个连接都是在读数据，才不会相互排斥。而反观InnoDB引擎，由于支持行锁，并发冲突很小，在高并发、多连接的场景中，性能会更加出色

MyISAM 引擎的适用场景

​ 结合MyISAM引擎的特性而言，它适用于一些不需要事务、并发冲突低、读操作多的表，例如文章表、帖子表、字典表...。

​ 但实际上这种表在一个系统中占比很少，但有一种场景时，特别适合使用MyISAM引擎，即MySQL利用主从架构，实现读写分离时的场景，一般从库会承载select请求，而主库会承载insert/update/delete请求。读写分离的场景中，从库的表结构可以改为MyISAM引擎，因为基于MyISAM的索引查询数据，不需要经过回表查询，速度更快。

• 主库：承载insert、update、delete请求
• 从库：承载select请求
  • 引入读写分离机制，从库上只有读操作不会有任何外部写请求，因此支持并发读取
  • 针对从库数据更新，从库数据是由主库复制而来，一般限定只有后台的少数线程执行写入操作，造成的冲突不会太大，不会因为表锁引起大量阻塞

InnoDB存储引擎

👻1.一行记录的存储格式

​ 结合InnoDB存储引擎理解一行记录的存储格式，确认以下4个问题：

• 【1】MySQL的 NULL 值是怎么存放的？
  • MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分
  • NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间
• 【2】MySQL 怎么知道 varchar(n) 实际占用数据的大小？
  • MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小
• 【3】varchar(n) 中 n 最大取值为多少？
  • 一行记录最大能存储 65535 字节的数据，其包含「变长字段长度列表所占用的字节数」和「NULL值列表所占用的字节数」（即storage overhead部分）
  • n 最大值计算公式：65535 -「变长字段长度列表所占用的字节数」（单字段或多字段）-「NULL值列表所占用的字节数」（每超出8个字段允许为NULL则+1个字节，以此类推）
    • 「变长字段字节数列表所占用的字节数」与字段类型大小定义和字段数相关
      • 当字段类型大小定义小于等于255 则占1个字节，当字段类型大小定义大于255 则占2个字节
      • 「变长字段字节数列表所占用的字节数」则为每个字段占用的变长字段长度总和
    • 「NULL值列表所占用的字节数」：1个字节可容纳8个NULL字段定义，以此类推
    • 最大可定义字节数MAX与N的转化：如果字符集为一个字符占用一个字节，则MAX与N相同；如果字符集是一个字符占用3个字节（例如utf8），则MAX需要除于3得到N值
• 【4】行溢出后，MySQL 是怎么处理的？
  • 如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中
  • Compact：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页
  • Compressed 和 Dynamic：采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中

记录的存储

MySQL 数据存储在哪里？

​ MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。通过show variables like 'datadir';查看文件目录在哪个位置，会在该目录下存放以数据库命名的目录，里面存储着数据库相关的磁盘文件。

• db.opt：存储当前数据库默认字符集和字符校验规则

• 创建t_innodb表（存储引擎为InnoDB）其在本地的磁盘文件是

  • t_innodb.frm：存储表结构信息
  • t_innodb.ibd：存储表的行数据和索引数据
    • 表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，该值默认值为1，则每一张表的数据都存放在一个独立的 .ibd 文件

表空间文件的结构是怎样的？

​ InnoDB存储引擎的逻辑存储结构：表空间由段（segment）、区（extent）、页（page）、行（row）组成

行：数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。4种不同类型的行格式：Compact、Redundant、Dynamic、Compressed

页：InnoDB 的数据是按「页」为单位来读写的，即当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存

• 表中的记录存储在数据页中
• 记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低
• 默认每个页的大小为 16KB，即最多能保证 16KB 的连续存储空间
• 页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中
• 页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的

区：为解决大数据量场景下B+树组织数据在物理存储上的不连续性，引入区概念，将连续的64页划分为一个区使其逻辑和物理位置都相邻，进而减少随机I/O

​ InnoDB存储引擎是通过B+树来组织数据的，B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。要解决这个问题可以让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高

​ MySQL的解决方式是：在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了

段：表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等

• 索引段：存放 B + 树的非叶子节点的区的集合；
• 数据段：存放 B + 树的叶子节点的区的集合；
• 回滚段：存放的是回滚数据的区的集合（例如事务机制中MVCC利用回滚段实现多版本查询数据）

行格式

​ 行格式（row_format）：一条记录的存储结构，InnoDB提供了4种行格式：Redundant、Compact、Dynamic和 Compressed 行格式

• Redundant：MySQL5.0之前使用的行格式，非紧凑型，现基本没人用
• Compact：紧凑的行格式，设计的初衷是为了让一个数据页中可以存放更多的行记录（MySQL5.1版本之后，行格式默认设置为Compact）
• Dynamic、Compressed：两者都是紧凑的行格式，和Compact差不多，是基于Compact的改进版（MySQL5.7之后，行格式默认设置为Dynamic）

​ 此处学习以Compact为主，且Dynamic、Compressed是基于Compact的改进，因此掌握Compact也就能更好理解这两种行格式

Compact 行格式

​ 一条完整记录分为：记录的额外信息、记录的真实数据两个部分

记录的额外信息：变长字段长度列表、NULL 值列表、记录头信息

（1）记录的额外信息

【表长字段长度列表】

变长字段长度列表：首先理解varchar、char的区别，其中char是定长的、varchar是变长的，变长字段实际存储的数据长度（大小）是不固定的。因此在存储数据的时候，也要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候根据这个「变长字段长度列表」去读取对应长度的数据。其他 TEXT、BLOB 等变长字段也是这么实现的。

# 创建数据表用于案例分析
CREATE TABLE `t_user_innodb` (
  `id` int(11) NOT NULL,
  `name` VARCHAR(20) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

# 构建测试数据
INSERT INTO `t_user_innodb` (`id`, `name`, `phone`, `age`)
VALUES
	(1, 'a', '123', 18),
	(2, 'bb', '1234', NULL),
	(3, 'ccc', NULL, NULL);

​ 结合上述案例分析三条记录的行格式中的【变长字段长度列表】是怎样存储的，分析每个变长字段占用字节数，这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放

• 记录1分析：
  • name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
  • phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；
  • age 列和 id 列不是变长字段（此处不涉及，可暂时忽略）
• 记录2分析：
  • name 列的值为 bb，真实数据占用的字节数是 2 字节，十六进制 0x02；
  • phone 列的值为 1234，真实数据占用的字节数是 4 字节，十六进制 0x04；
• 记录3分析：
  • name 列的值为 ccc，真实数据占用的字节数是 3 字节，十六进制 0x03；
  • phone 列的值为 null ，NULL是不会存放在行格式中记录的真实数据部分，因此【变长字段长度列表】不需要保存值为NULL的变长字段的长度

为什么【变长字段长度列表】的信息要按照逆序进行存放？

​ 设计理念：「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

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

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

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

​ 变长字段字节数列表不是必须的，「变长字段长度列表」只出现在数据表有变长字段的时候

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

【NULL值列表】

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

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

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

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

​ 三条记录的NULL值填充完毕之后，行格式参考如下：

每个数据表的行格式都有【NULL值列表】吗？

​ 【NULL值列表】非必须，当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表

​ 【NULL值列表】的空间并不是固定1字节，例如当一条记录中有9个字段都是NULL，则会创建2字节空间的【NULL值列表】，以此类推

​ 在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间），但实际上还是要结合业务场景去调整

【记录头信息】

​ 记录头信息中包含的内容有很多，由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思

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

此处关注核心内容：

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

（2）记录的真实数据

​

​ 记录的真实数据部分除了定义的字段，还存在3个隐藏字段分别为row_id、trx_id、roll_pointer

• row_id（非必需，占用6个字节）：如果建表的时候指定了主键或者唯一约束列，则无row_id 隐藏字段。如果既没有指定主键或唯一约束， InnoDB 会为记录添加 row_id 隐藏字段

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

• roll_pointer（必需的，占用 7 个字节）：这条记录上一个版本的指针

trx_id、roll_pointer为MySQL的MVCC机制提供了必要支持，可结合MVCC的实现进一步理解

（3）行溢出问题

​ MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。

​ 如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下，InnoDB 的数据都是存放在 「数据页」中。但是当发生行溢出时，溢出的数据会存放到「溢出页」中。当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

​ Compact行格式发生行溢出后的处理：

​ Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中

char和varchar

varchar(n) 中的n最大取值？注意选用字符集的影响

​ MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节

​ varchar(n)字段类型的n代表最多存储的字符数量（非字节大小），因此还要结合数据库表设定的字符集来计算（例如ascii字符集中1个字符占用1个字节，则此时varchar(100)意味着最大能允许存储100字节的数据）

​ 存储字段类型varchar(n) 的数据时，会拆分为3个部分进行存储：

• 真实数据
• 真实数据占用的字节数
• NULL标识（如果不允许为NULL，则这部分不需要）

单字段场景 varchar(n) 拆解

​ 以下述SQL语句为例进行拆解：

# 错误
CREATE TABLE test ( 
`name` VARCHAR(65535)  NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

# SQL 执行提示错误：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



# 正常构建
CREATE TABLE test ( 
`name` VARCHAR(65532)  NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

​ 结合SQL执行错误提示分析，一行数据的最大字节数是 65535（不包含 TEXT、BLOBs 这种大对象类型），其中包含了 storage overhead。此处的 storage overhead 其实指的是【变长字段长度列表】、【NULL值列表】，即65535实际上是包括这部分的内容的，因此在计算varchar(n) 中的n的最大值时，要去掉storage overhead 的部分，才是字段允许存储的最大值。

​ 结合SQL定义，分析storage overhead 占用的字节数

• 允许name字段为NULL，所以此处【NULL值列表】占用1字节
• 【变长字段长度列表】占用字节数是根据不同情况来区分的
  • （1）如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；
  • （2）如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；
  • 此处 65535 大于 255 符合条件（2），且只有一个变长字段定义，因此【变长字段长度列表】占用2字节（如果有多个变长字段则为2*n）

​ 结合上述说明：在数据表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532，测试时可调整VARCHAR(n)中的n值进行校验，确认是否可正常构建表。

多字段场景 varchar(n) 拆解

​ 如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535

# 多字段：构建错误=》35535-2*2-1=65530 因此此处满打满算一行数据的varchar中总的n最大为65530，拆到平均每个字段可设定的n为32765
CREATE TABLE test ( 
`name` VARCHAR(32765)  NULL,
`remark` VARCHAR(32766)  NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;


# 多字段：一个字段少于255、一个字段多于255：65535 - 2 - 1 -1 = 65531，例如此处一个为255的话，另一个最大为65276
CREATE TABLE test ( 
`name` VARCHAR(255)  NULL,
`remark` VARCHAR(65276)  NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

# 多字段：设定了9个字段可为NULL，则此处【NULL值列表】会占用2个字节: 65535 - 1*8 - 2 - 2 = 65523 （8个字段变长）
CREATE TABLE test ( 
`name` VARCHAR(255)  NULL,
`remark` VARCHAR(255)  NULL,
`f1` VARCHAR(255)  NULL,
`f2` VARCHAR(255)  NULL,
`f3` VARCHAR(255)  NULL,
`f4` VARCHAR(255)  NULL,
`f5` VARCHAR(255)  NULL,
`f6` VARCHAR(255)  NULL,
`f7` VARCHAR(63484)  NULL  -- 满打满算调整为65383可通过
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

char VS varchar 定义

​ CHAR(N) 用来保存固定长度的字符，N 的范围是 0 ~ 255，牢记，N 表示的是字符，而不是字节

​ VARCHAR(N) 用来保存变长字符，N 的范围为 0 ~ 65536， N 表示字符

​ 如果超出限定存储范围，则考虑使用更大的字符类型TEXT、BLOB来存储（两者的最大存储长度为4G），其中BLOB没有字符集属性（二进制存储），MySQL数据库中，绝大部分场景使用类型VARCHAR基本足够

数据页

​ MySQL支持多种存储引擎，不同存储引擎存储数据的方式不同。在InnoDB存储引擎中记录是按行存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。InnoDB 的数据是按「数据页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存

（1）页 核心概念

​ 页是InnoDB管理存储空间的基本单位，一个页的大小一般是16KB。InnoDB为了不同的目的而设计了许多种不同类型的页，比如存放表空间头部信息的页，存放Insert Buffer信息的页，存放INODE信息的页，存放undo日志信息的页等。聚焦存放表中记录的那种类型的页，官方称这种存放记录的页为索引（INDEX）页，而这些表中的记录就是我们日常口中所称的数据，也可以将这种存放记录的页称为数据页

​ 数据页代表的16KB大小的存储空间可以被划分为多个部分，不同部分有不同的功能：

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

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

​ 回顾一下行格式的定义，其中关注delete_mask（记录是否删除）、next_record（指向下一条记录的相对位置） 部分

​ InnoDB规定了两条伪记录：最小记录、最大记录，都是由5字节大小的记录头信息和8字节大小的一个固定部分组成。这两条记录并不是自定义记录，因此不存放在页的User Records部分，而是存放在Infimum + Supremum部分。当插入表数据的时候，记录按照主键从小到大的顺序和Infimum + Supremum部分一起形成了一个单链表

• 单链表组合：
  • 头节点：最小记录（Infimum）=》其 next_record 指向页User Records部分中自定义的主键最小的记录
  • 中间节点：页User Records部分由自定义的数据按照主键从小到大的顺序进行排列连接
  • 尾节点：最小记录（Supremum），由页User Records部分中自定义的主键最大的记录的 next_record 指向 Supremum

​ ==当删除第2条记录时：==记录不会被真正删除，而是将delete_mask设置为1（标识该记录被删除），并将记录的next_record置为0（可以理解为断开其与单链表的连接），结合下图图示分析，去除掉第2条记录，按照原定的规则重新维护了一条新的单链表，即不论如何怎么对页中的记录做增删改操作，InnoDB始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的

​ 在上述操作中，删除了主键值为2的记录，但存储空间并没有回收，如果重新将这条记录插入到表中，进一步分析存储结构会如何变化。如下图所示，InnoDB并没有因为新纪录的插入而为它申请新的存储空间，而是直接复用了原来被删除记录的存储空间。当数据页中存在多条被删除掉的记录时，这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后重用这部分存储空间

（2）页目录（Page Directory）

​ 基于上述页核心概念介绍，可以知道InnoDB在页中是通过维护一个单链表来管理数据的（页中的数据按照主键从小到大进行排序）。因此当需要根据主键值查找页中的某条记录时该如何操作？

​ 最笨拙的方法：循环遍历单链表进行检索（从Infimum出发沿着链表一直往后找），因为链表是按照主键大小排序的，因此如果一直往后找找不到，当发现某一个节点的主键值大于当前查找的主键值则可以停止查找操作（后面的主键值依次递增更加不满足条件）

​ 基于上述这种思路，如果在页中存储的数据记录数较少的情况下使用并没有什么问题，但是如果页中存储了大量的数据，这种遍历查找的方式对性能来说存在一定损耗。因此InnoDB的设计者则基于书目录的概念找到设计灵感，为这些记录也定制了一个类似的目录，其构建思路如下：

• 【1】将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组
• 【2】每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录
• 【3】将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory（即页目录）。页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的

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

​ 插入多条数据，查看分组情况图示如下：

​ 优化后的查找方式：基于上述分组，在一个数据页中查找指定主键值的记录的过程分为两步：先确定槽、然后跟踪槽对应的记录

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

（3）B+树索引（快速查询的秘籍）

​ 基于页核心概念，可知InnoDB的各个数据页可以组成一个双向链表，每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

​ 其中页a、页b、页c ... 页n 这些页可以不在物理结构上相连，只要通过双向链表相关联即可。

​ 在了解B+树索引检索之前，首先理解没有索引的查找场景。

没有索引的查找

​ 以搜索条件为对某个列精确匹配的情况为例进行分析（所谓精确匹配，就是搜索条件中用等于=连接起的表达式），类似SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

在一个页中的查找

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

• 以主键为搜索条件
  • 基于页目录机制，在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
• 以其他列作为搜索条件
  • 对非主键列的查找并没有建立所谓的页目录机制，这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件

在多个页中的查找

​ 大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：

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

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中的查找则根据上述单页查找的思路。因为要遍历所有的数据页，所以这种方式显然是超级耗时的，

基于索引的查找（简易索引方案：根据主键ID的索引查找方式）

​ 首先理解没有索引的查找场景，其效率低下主要体现在多页查找中由于无法快速定位记录所在页，从而导致不得不遍历每个页来定位数据。进一步思考，如果可以像是单页查找的”页目录“机制这样，给每个页也编排一个目录，让其可以根据目录快速定位记录所在页

​ 由于新分配的页的编号可能并不是连续的（页在存储空间中可能并不挨着，只是通过链表维护关联），通过限定规则下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值（这个过程可以称为页分裂），例如插入多条数据后，其可能出现如下效果：

​ 进一步给页指定目录，每个页对应一个目录项，存储两部分的内容：

• key：页的用户记录中最小的主键值
• page_no：页编号

​ 则其做好的目录参考如下：

​ 将这些目录项在物理存储器上连续存储，比如把他们放到一个数组里，就可以实现根据主键值快速查找某条记录的功能，也就实现了针对数据页的简单目录，即索引

​ 基于构建好的索引其根据主键值的查找思路如下：例如要查找主键ID为20的记录

• 定位主键ID为20的记录所在目录项（根据key判断，12<20<209，因此定位其在目录项3），然后跟踪到页编号为9
• 找到页编号为9的页记录，然后执行单页查找操作（参考上述的单页查找思路）

​

​ 综上所述：多页检索场景中通过构建索引为多页指定一个目录，便于快速定位数据所在的某页，随后进一步执行单页检索操作（单页检索中的检索优化又可涉及其内部的页目录的概念）

（4）👻InnoDB中的索引方案（B+树的由来）

​ 基于上述建议的索引方案，在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储，但是这样做有几个问题：

• 目录项的存储需要占据空间：InnoDB是使用页来作为管理存储空间的基本单位，也就是最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的
• 目录项具有一定的维护成本：在对记录管理的同时，相应需要维护目录项来确保其顺序。例如将上述页28中的记录都删除了，则页28和其对应目录项2也没有存在必要，对于页数据的连接而言是调整链表指针指向，但对于目录项的维护而言其需要将目录项2后的所有目录项向前移动一下，具有一定的维护成本

​

​ 为了解决上述场景问题，InnoDB的设计者提供了一种灵活管理所有目录项的方式。分析上述目录项的定义，其实这些目录项和用户记录类似，只不过目录项中存储的值是页中最小主键值和对应页号。因此选择复用了之前存储用户记录的数据页来存储目录项（为了和用户记录作区分，这些用来表示目录项的记录称为目录项记录），InnoDB通过记录头信息中的record_type属性来区分一条记录是用户记录还是目录项记录：

• 0：普通的用户记录
• 1：目录项记录
• 2：最小记录
• 3：最大记录

​ 基于上述构建思路分析，例如分配了一个页编号为30的页专门存储目录项记录：

​ 结合图示可以分析，用户记录和目录项记录主要区别说明如下：

• record_type值不同
  • 目录项记录为1
  • 用户记录为0
• 存储内容不同
  • 目录项记录只有key、page_no两个列
  • 用户记录是用户自定义的内容，可能包含很多列

​ 因此，基于上述思路构建的索引，分析根据主键ID查找ID为20的记录，其检索流程说明如下：

• 先在存储目录项的页中根据二分法定位到对应的目录项（涉及单页检索）
• 然后根据查找到的目录项获取到其页编号，然后根据这个页编号到指定的页中检索数据

​ 类似地，当数据量比较大的情况下，一页内存并不足够存放所有的目录项，类似地可扩展新的目录项页来存储更多的内容

​ 则其检索流程也是类似地，先定位记录所在的目录项页，然后确认其目录项关联的页编号，再去用户记录链表中检索指定页记录即可。那么如果数据量继续扩大的场景下，目录项页的检索又面临着和前面多页检索的一模一样的问题（因为目录项页存储空间不连续，如果有很多目录项页的情况下，无法快速定位其所在的目录项页，则检索效率就会很低），继续思路扩展，思考是不是可以参考上述思路，构建一个更高层的目录来维护多个目录项页的索引（可以理解为无限套娃模式开启）

​ 以此类推，继续放大数据量，则可以知道随着表中记录的增加，这个目录的层级会继续增加，则简化一下上述层级关系会发现它很像一个树层级，可以理解为这是一种组织数据的形式（一种数据结构），此处将其称为B+树

​ 至此也就可以理解MySQL中InnoDB引擎中B+树存储的节点到底是什么：

• 叶子节点：存储用户记录页（数据页），即用户数据
• 非叶子节点：存储目录项记录页，也就是所谓的索引

​ 思考一个问题，这么一颗B+树到底可以容纳多少数据：假设每个叶子结点代表的数据页可以存储100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，来分析不同层时B+树的存储容量：

• 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100条记录
• 如果B+树有2层，最多能存放1000×100=100000条记录
• 如果B+树有3层，最多能存放1000×1000×100=100000000条记录
• 如果B+树有4层，最多能存放1000×1000×1000×100=100000000000条记录

​ 可以看到，基于这种数据结构的存储支持，一般情况下B+树不会超过4层，那么基于这种情况通过主键去查找某条记录，最多只需要查找4个页面（3个目录项页、1个用户记录页），且针对单页面内又有所谓的Page Directory（页目录），在单页内也可通过二分法实现快速定位记录，其查找效率大大提高。

（5）聚簇索引、二级索引、联合索引

聚簇索引

​ 基于上述对B+树的介绍，可以理解B+树本身就是一个目录（索引），它具有两个特点：

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

  • 页内的记录是按照主键的大小顺序排成一个单向链表

  • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表

  • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表

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

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

具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。聚簇索引并不需要在MySQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

二级索引

​ 结合上述内容分析，聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。如果想要以别的列作为搜索条件的话又该如何处理？（类似地，构建相应的B+树即可）

​ 通过构建不同排序规则的B+树，进而复刻上面的检索思路，但B+树中存储的内容可能有点不同

​ 例如以c2列的大小作为数据页，页中记录的排序规则，再创建一棵B+树，效果如下所示：

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

所以如果现在想通过c2列的值查找某些记录的话就可以使用到这个B+树。以查找c2列的值为4的记录为例：

• 确定目录项记录页
  • 定位到所在目录项记录页为页42
• 根据目录项记录页定位用户记录页
  • 定位到对应用户记录页为页34、页35
• 在对应用户记录页中进行检索
  • 根据二分法检索到叶子节点数据，由于叶子结点数据只存储了c2列和主键列的值，因此还需要根据获取到的主键值去聚簇索引中再查找一遍完整的用户记录

​ 结合上述分析，通过二级索引只能获取到要查找记录的主键值和索引列值，如果希望查找完整的用户记录，还需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程也称为回表。即根据c2列的值查询一条完整的用户记录需要使用到2棵B+树。这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引（英文名secondary index），或者辅助索引

​ ==思考：为什么二级索引不像聚簇索引那样叶子节点存储的是完整的用户数据呢？==虽然这样设计可以省略回表操作，但考虑到存储的问题，如果每创建一棵B+树（构建索引）都要把所有用户数据copy一份，是非常浪费存储空间的。

​

联合索引

​ 类似地，可以同时将多个列的大小作为排序规则，即同时为多个列建立索引，例如想让B+树按照c2和c3列的大小进行排序，其包含两层含义：

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

​ 基于c2、c3构建的索引示意图如下

• 目录项记录：每条目录项记录都由c2、c3、页号三部分组成（按照c2、c3的先后的排序规则（先c2排序，如果c2相同按照c3排序）进行排序）
• 叶子节点记录：每个叶子结点的用户记录由c2、c3、主键c1组成

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

（6）B+ 树索引的构建

​ B+树的形成过程分析如下：根页面万年不动窝

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

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

如何理解内节点中目录项记录的唯一性

​ B+树索引的内节点中目录项记录的内容是索引列 + 页号的搭配，但是这个搭配对于二级索引来说有点儿不严谨，因为引用的索引列可能并不具备唯一性，从而无法保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的，从而可能导致通过索引列 + 页号可能会定位到同层的不同节点进而造成矛盾（不知道要操作的数据到底是哪个节点对照的数据）。因此对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的（索引列的值、主键号、页号），即把主键值也添加到二级索引内节点中的目录项记录，以此确保B+树每一层节点中各条目录项记录除页号这个字段外是唯一的

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

​ 一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度也是非常高效！这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录。

​ 那如果一个大的目录中只存放一个子目录是什么效果呢？那就是目录层级非常非常非常多，而且最后的那个存放真实数据的目录中只能存放一条记录，这种设计反而鸡肋，也就变相说明InnoDB的一个数据页至少可以存放两条记录

（7）扩展：MyISAM的索引方案

​ 结合前面的案例介绍可知InnoDB中索引即数据，即聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储（结合MyISAM的文件存储拆分为3个文件以更好地理解这句话）：将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录，可以通过行号而快速访问到一条记录

• 使用MyISAM存储引擎的表会把索引信息另外存储到索引文件中
• MyISAM会单独为表的主键创建一个索引，在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录
  • 这一点和InnoDB是完全不相同的，在InnoDB存储引擎中，只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引
• MyISAM支持对其它的列分别建立索引或者建立联合索引，原理和InnoDB中的索引差不多，不过在叶子节点处存储的是相应的列 + 行号，这些索引也全部都是二级索引

​ MyISAM的行格式有定长记录格式（Static）、变长记录格式（Dynamic）、压缩记录格式（Compressed）。

​ 定长记录格式：即一条记录占用存储空间的大小是固定的，这样就可以轻松算出某条记录在数据文件中的地址偏移量。

​ 但是变长记录格式就不行了，MyISAM会直接在索引叶子节点处存储该条记录在数据文件中的地址偏移量。通过这个可以看出，MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里边儿找记录，虽然说也不慢，但还是比不上直接用地址去访问。

Buffer Pool

（1）Buffer Pool 核心概念

​ MySQL 的数据是存储在磁盘里的，但如果每次都从磁盘里面读取数据的话其性能是极差的。要想提升查询性能，可以通过引入缓存实现，当数据从磁盘中取出后，缓存内存中，下次查询同样的数据的时候，直接从内存中读取。因此InnoDB存储引擎设计了一个缓冲池（Buffer Pool）来提高数据库的读写性能。

引入缓冲池后：

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取
• 当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘

Buffer Pool 的大小

​ Buffer Pool 是在 MySQL 启动的时候，向操作系统申请的一片连续的内存空间，默认配置下 Buffer Pool 只有 128MB

​ 可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小，一般建议设置成可用物理内存的 60%~80%

Buffer Pool 缓存什么

​ InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。

​ 在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

​ 所以，MySQL 刚启动的时候，可以观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系。

​ Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等。为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等。控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页，而控制块和缓存页之间的灰色部分被称为碎片空间

为什么会存在碎片空间？

​ 每一个控制块都对应一个缓存页，那在分配足够多的控制块和缓存页后，可能剩余的那点儿空间不够一对控制块和缓存页的大小，自然就用不到，这点没有利用到的内存空间就被称为碎片空间。如果将 Buffer Pool 的大小设置的刚刚好的话，也可能不会产生碎片

查询一条记录的时候就只需要缓冲一条记录吗？

​ 按行读取的效率是非常慢的，因此在查询一条记录的时候，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，因为，通过索引只能定位到磁盘中的页，而不能定位到页中的一条记录。将页加载到 Buffer Pool 后，再通过页里的页目录去定位到某条具体的记录。

（2）如何管理Buffer Pool ？

Buffer Pool 中有三种页和链表用于管理数据：

• Free Page（空闲页）：表示此页未被使用，位于 Free 链表
• Clean Page（干净页）：表示此页已被使用，但是页面未发生修改，位于LRU 链表
• Dirty Page（脏页）：表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表

为了进一步提高读写效率，InnoDB对简单的LRU算法做了相应的优化，进而解决简单LRU算法无法解决的预读失效和Buffer Pool污染问题

• 所谓预读失效，是由于MySQL的预读机制（将访问页相邻的页也一并读取出来），但可能存在预读页一直没有访问到的情况，就会导致一直没有访问到的预读页占据表头，而淘汰了一些真实的热点数据，进而导致预读失效
  • 通过将LRU链表按照一定的占比拆分为young区域和old区域
    • 预读页只进入old区域，插入old区域头部；只有当页真正被访问才将页插入young区域的头部
    • 如果预读页一直没被访问，则从old区域移除，不影响young区域的热点数据
• 所谓Buffer Pool污染问题，是由于在Buffer Pool空间比较有限的场景下，一次性读取大量数据，导致Buffer Pool中的页都被替换，大量热点数据被冲掉了。当需要再次访问这些热点数据的时候，由于缓存未命中就会导致产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程称之为Buffer Pool污染
  • 要解决Buffer Pool污染问题，实际上就是避免young区域的热点数据大量被刷掉，因此可以考虑提高进入young区域的门槛，例如增设条件：只有满足被访问、在old区域的停留时间超出某个时间间隔
• 此外MySQL还对young区做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会

如何管理空闲页？=》通过Free空闲链表管理空闲页

​ Buffer Pool 通过Free空闲链表管理空闲页，进而提高读性能

​ Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。

​ 当从磁盘读取数据的时候，如果是通过遍历这一片连续的内存空间来找到空闲的缓存页的方式其效率太低了。所以，为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）

​ Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

​ Free 链表节点是一个一个的控制块，而每个控制块包含着对应缓存页的地址，所以相当于 Free 链表节点都对应一个空闲的缓存页。

​ 有了 Free 链表后，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除

如何管理脏页？=》通过Flush链表管理脏页

​ Buffer Pool 通过Flush链表管理脏页，进而提高写性能。

​ 在更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘

​ 同理，为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页

如何提高缓存命中率？=》通过LRU链表

​ Buffer Pool 的大小是有限的，对于一些频繁访问的数据希望可以一直留在 Buffer Pool 中，而一些很少访问的数据则希望可以在某些时机可以淘汰掉，从而保证 Buffer Pool 不会因为满了而导致无法再缓存新的数据，同时还能保证常用数据留在 Buffer Pool 中。要实现这个思路，最容易联想到的就是 LRU（Least recently used）算法

​ LRU算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的。那么，当空间不够了，就淘汰最久没被使用的节点，从而腾出空间。

• 简单LRU算法的应用

  • 当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部

  • 当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点

但简单的 LRU 算法并没有被 MySQL 使用，因为简单的 LRU 算法无法避预读失效和Buffer Pool污染这两个问题

预读失效

MySQL的预读机制和预读失效场景

​ MySQL 的预读机制：程序是有空间局部性的，靠近当前被访问数据的数据，在未来很大概率会被访问到。所以，MySQL 在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO

​ 但是可能这些被提前加载进来的数据页，并没有被访问，相当于这个预读是白做了，即所谓的预读失效

​ 如果使用简单的LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，还需要把末尾的页淘汰掉。如果说这些被附带进来的预读页一直没有被访问到，就会出现一个很奇怪的问题，不会被访问的预读页却占用了 LRU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存命中率

如何解决预读失效而导致的缓存命中率的降低？=》改进LRU算法：引入old区域和young区域概念

​ 在大部分场景下，预读机制的局部性原理还是成立的，不能因为存在预读失效现象就否认其作用而将其去掉

​ 要避免预读失效带来影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长

​ MySQL改进了LRU算法，将LRU链表划分为两个区域：old区域和young区域，young 区域在 LRU 链表的前半部分，old 区域则是在后半部分，并按照一定比例分配占比。old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pct 参数来设置，默认是 37，代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37

​ 划分了这两个区域之后，预读页只需要加入到LRU链表的old区域，而当页真正被访问的时候才会将访问页加入young区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，也就不会影响 young 区域中的热点数据

​

Buffer Pool污染

Buffer Pool污染概念核心

​ 当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。

​ 注意， Buffer Pool 污染并不只是查询语句查询出了大量的数据才出现的问题，即使查询出来的结果集很小，也会造成 Buffer Pool 污染。例如在一个数据量非常大的表中执行语句select * from t_user where name like "%noob%";。可能这个查询出来的结果就几条记录，但是由于这条语句会发生索引失效，所以这个查询过程会走全表扫描，读取时发生如下的过程：

• 从磁盘读到的页加入到 LRU 链表的 old 区域头部；
• 当从页里读取行记录时，也就是页被访问的时候，就要将该页放到 young 区域头部；
• 接下来拿行记录的 name 字段和字符串 xiaolin 进行模糊匹配，如果符合条件，就加入到结果集里；
• 如此往复，直到扫描完表中的所有记录

经过这一番折腾，原本 young 区域的热点数据都会被替换掉，结合下述案例理解

怎么解决出现 Buffer Pool 污染而导致缓存命中率下降的问题？

​ 类似前面这种全表扫描的查询，很多缓冲页其实只会被访问一次，但是它却只因为被访问了一次而进入到 young 区域，从而导致热点数据被替换了。LRU链表中young区域就是热点数据，因此需要考虑提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。

​ MySQL通过限定进入young区域的条件来提高门槛：增加了一个停留在 old 区域的时间判断，其实现思路如下：

​ 当在对某个处在 old 区域的缓存页进行第一次访问时，就在其对应的控制块中记录下来这个访问时间：

• 判断后续的访问时间与第一次访问的时间两者的时间间隔

  • 如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；

  • 如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；

• 这个间隔时间是由 innodb_old_blocks_time 控制的，默认是 1000 ms

​ 即进入young区的条件被限定为：只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，进而解决了 Buffer Pool 污染的问题

​ 此外，MySQL 针对 young 区域其实做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会

脏页什么时候会被刷入磁盘？

​ 引入了 Buffer Pool 后，当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。

​ 思考一种场景：如果在脏页还没有来得及刷入到磁盘时，MySQL 宕机了，不就丢失数据了吗？

​ 针对上述问题，InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力

下面几种情况会触发脏页的刷新：

• 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
• Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
• MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
• MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

​ 在开启了慢 SQL 监控后，如果发现**「偶尔」会出现一些用时稍长的 SQL**，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。

如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小

Change Buffer

（1）change buffer 核心

​ change buffer的引入场景：在更新数据页的时候，判断该数据页是否在内存中

• 如果数据页在内存中就直接更新
• 如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了
  • 在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性

​ change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

​ 显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，更新语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率

什么条件下可以使用change buffer？

​ 对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

​ 因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用

​ change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%

普通索引的所有场景，使用change buffer都可以起到加速作用吗？

​ 结合上述概念分析，在普通索引中使用change buffer，可以将更新操作进行缓存，进而减少磁盘IO交互成本，从而提升更新操作效率。

​ change buffer 的主要目的是将记录的变更动作进行缓存，而真正进行数据更新操作是在merge过程中。因此可以理解为在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大（因为避免了很多次和磁盘IO交互的成本）

​ 因此对于写多读少的业务模型（如账单类、日志类的系统）来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好

​ 反过来思考，如果一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用

（2）普通索引、唯一索引的场景选择

普通索引 VS 唯一索引

​ 假设在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的 SQL 语句：select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

​ 由于身份证号字段比较大，一般不建议用作主键。因此此处索引构建场景有两种思路：要么给 id_card 字段创建唯一索引，要么创建一个普通索引（如果业务代码层需确保不会输入重复的身份证号，这两个索引构建选择逻辑上都是正确的），那么如何来选择这两种索引呢？选择的依据是什么？=》可以结合这两种索引对查询语句和更新语句的性能影响来分析

​ select id from T where k=5

查询过程: 从根节点检索索引查找到对应的数据页编号，然后根据数据页编号定位到页，在页内部通过二分法定位记录（此处查找的是id直接可以在叶子节点获取不需要回表）

• 普通索引：查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录
• 唯一索引：对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索

​ 这两个查找的不同性能差距会有多少呢？=》微乎其微

​ InnoDB的数据是以数据页为单位来读写的，当需要读取一条记录的时候会将其所在的页也一并从磁盘中读取出来（以页为单位，将其整体读入内存）。且结合MySQL的预读机制，还会将邻近的几页也一并读取出来。如果以查找k=5的记录来分析，其所在的页和邻近也也会被加载到内存中，此时如果需要查找下一个记录则只需要进行一次指针查找和一次计算操作（因为前面的前置条件是这个k具有唯一性，因此此处只是基于普通索引的检索规则多做了一次判断，当判断到不满足条件的记录就会停止，因此会多校验一次），针对这个过程的细化分析如下：

​ 如果k=5这个记录和其下一个记录刚好在同一页，则执行一次指针查找和一次计算操作

​ 如果k=5这个记录恰好是当页的最后一个，则还需要读取下一个数据页，然后再执行一次指针查找和一次计算操作（这种情况出现的概率会很低，对于整型字段，一个数据页可以放近千个 key）

​ 但实际上不管怎样往极端方向靠，这些情况的影响对于平均性能差异而言，其操作成本基本可以忽略不计，可以理解为这两种索引的选择在基于唯一字段的检索（查询）场景中性能基本大差不差。

更新过程：为了理解普通索引和唯一索引对更新语句性能的影响这个问题，首先理解change buffer的引入，然后结合案例拆解分析（根据不同情况分析），以插入新纪录（4,400）为例，分析InnoDB处理流程

• 如果要更新的记录所在目标页在内存中，InnoDB处理流程分析如下

  • 普通索引：找到 3 和 5 之间的位置，插入新值，语句执行结束
  • 唯一索引：找到 3 和 5 之间的位置，判断到没有冲突，插入新值，语句执行结束

• 如果要更新的记录所在目标页不在内存中，InnoDB处理流程分析如下

  • 普通索引：将更新操作记录在change buffer，语句执行结束
  • 唯一索引：需要确保唯一性，判断冲突，因此需要将数据页读到内存中判断，无冲突则插入新值，语句执行结束

​ 结合上述案例拆解分析，只有普通索引才有可能用到change buffer。因为将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的

​ 基于此场景分析，在大数量表的更新场景下，普通索引的change buffer机制对更新操作速度的提升是非常显著的，因此当有一个类似历史数据的库，并且出于成本考虑用的是机械硬盘时，那应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度

​ 一个真实的场景案例：某位DBA负责的某个业务的库内存命中率突然从 99% 降低到了 75%，整个系统处于阻塞状态，更新语句全部堵住。而探究其原因后，发现这个业务有大量插入数据的操作，而导致这个问题的原始在于其把其中的某个普通索引改成了唯一索引

（3）change buffer VS redo log

带chanage buffer 的更新过程：涉及 内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）

以插入 语句insert into t(id,k) values(id1,k1),(id2,k2);为例

• 记录所在数据页page在内存中，直接更新内存
• 记录所在数据页page没有在内存中，则在change buffer区域记录下相应的修改信息
• 将上述两个动作记录在redo log中

​ 基于上述步骤，一个事务完成。执行这条更新语句的成本在于写了两处内存和一处磁盘（两次操作合在一起写了依次磁盘），而且还是顺序写的

带change buffer 的读过程

以执行 select * from t where k in (k1, k2)语句为例

• 如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关，直接从内存中读取

​ 结合上述图示分析：

• 读page1的时候，可以直接从内存返回
• 读page2的时候，需要将page2从磁盘读入内存中，然后应用change buffer中的操作日志更新数据版本，然后返回正确的结果（即只有真正需要读page2的时候，才会将数据页读入内存，即完成磁盘IO交互）

​ 综上，如果简述change buffer和redo logo这两个机制在提升性能上的收益，redo logo主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

MySQL引擎的选择

​ 核心关注MyISAM、InnoDB这两款主流的存储引擎，当然MySQL中还有其他很多存储引擎例如Memory引擎，这款引擎在MySQL启动之后会完全基于内存工作，对比Redis这类K-V数据库，Memory引擎则是关系型的内存引擎，在部分场景下具备其适配性。

​ MySQL能够崛起的根本原因，也在于它的引擎是支持可拔插式的，并且同一个数据库中，对于不同业务属性的表，可以选用、设置不同的存储引擎，这样能够集百家之长。相较于SQL Server、Oracle等这类数据库，功能更加多样化。

​ 但并非所有MySQL引擎都具备优良的特性，不同引擎之间对数据的存储方式、查询数据的速度、支持的并发度也不同，虽然MySQL的可拔插式引擎，造就了MySQL特性的多样化，但其中各类引擎也参差不齐，所以如若对各款引擎没有太过深入的研究，最好还是根据业务在InnoDB、MyISAM两者之间做抉择

​ 虽然MySQL的一个数据库中支持使用多种存储引擎，但也不要盲目使用，毕竟使用的存储引擎越多，对于每个引擎可分配的资源也就越少，拿典型的内存资源为例，如果一个库中使用了七八种引擎，那内存资源需要划分给这七八个引擎，这必然会导致各引擎之间相互影响，从而降低MySQL的整体吞吐量