Mysql 官网地址：MySQL
一、Mysql 体系结构
Mysql 由以下几部分组成：
- 连接池组件
- 管理服务和工具组件
- SQL接口组件
- 查询分析器组件
- 优化器组件
- 缓存组件
- 插件式存储引擎
- 物理文件
二、Mysql 存储引擎
- Mysql 支持以下存储引擎
| 存储引擎 | 优点 | 缺点 |
| InnoDB | 支持事务、行级锁、外键，InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别 InnoDB存储引擎是默认的存储引擎（5.5.8版本开始） | |
| MyISAM | 支持全文索引。 MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件 | 不支持事务、表锁设计 |
| NDB | NDB的特点是数据全部放在内存中（从MySQL 5.1版本开始，可以将非索引数据放在磁盘上），因此主键查找（primary key lookups）的速度极快 | 连接操作（JOIN）是在MySQL数据库层完成的，而不是在存储引擎层完成的。这意味着，复杂的连接操作需要巨大的网络开销，因此查询速度很慢。 |
| Memory | 将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。 MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集 | 只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型 |
| Archive | 使用行锁来实现高并发的插入操作，但是其本身并不是事务安全的存储引擎，其设计目标主要是提供高速的插入和压缩功能，如日志信息 | 只支持INSERT和SELECT操作，从MySQL 5.1开始支持索引 |
| Federated | 不存放数据，它只是指向一台远程MySQL数据库服务器上的表 | |
| Maria | 新开发的引擎，支持缓存数据和索引文件，应用了行锁设计，提供了MVCC功能，支持事务和非事务安全的选项，以及更好的BLOB字符类型的处理性能。 |
- 不同MySQL存储引擎相关特性比较
三、InnoDB存储引擎
3.1 概念及原理
InnoDB存储引擎有多个内存块，负责如下工作：
- 维护所有进程/线程需要访问的多个内部数据结构。
- 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存。
- 重做日志（redo log）缓冲
- ...
InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程称为将页“FIX”在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。
缓冲池中存储的对象如下图：
在InnoDB存储引擎中，缓冲池中页的大小默认为16KB。
3.2 关键特性
- 插入缓冲（Insert Buffer）
对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中。Insert Buffer的数据结构是一棵B+树。
Insert Buffer的使用需要同时满足以下两个条件：
❑索引是辅助索引（secondary index）；
❑索引不是唯一（unique）的。
- 两次写（Double Write）
Insert Buffer带给InnoDB存储引擎的是性能上的提升，那么doublewrite（两次写）带给InnoDB存储引擎的是数据页的可靠性。
在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。
- 自适应哈希索引（Adaptive Hash Index）
InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（AHI）。
AHI要求对这个页的连续访问模式必须是一样的，且满足如下条件：
❑以该模式访问了100次
❑页通过该模式访问了N次，其中N=页中记录*1/16
启用AHI后，读取和写入速度可以提高2倍，辅助索引的连接操作性能可以提高5倍。
- 异步IO（Async IO）
异步IO可以将多个IO合并为1个IO，以提高性能。
- 刷新邻接页（Flush Neighbor Page）
工作原理为：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。
3.3 文件
| 文件类型 | 说明 |
| 参数文件 | 配置数据库文件路径、初始化参数等 |
| 日志文件 | 用来记录MySQL实例对某种条件做出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等 |
| socket文件 | 当用UNIX域套接字方式进行连接时需要的文件 |
| pid文件 | MySQL实例的进程ID文件 |
| MySQL表结构文件 | 用来存放MySQL表结构定义文件 |
| 存储引擎文件 | 每个存储引擎都会有自己的文件来保存各种数据，如索引等 |
查看配置参数的命令：
// 查看所有参数
show variables;// 模糊查找参数
show variables like '%部分参数名%';// 查询错误日志路径
SHOW VARIABLES LIKE 'log_error';// 设置参数 慢日志输出格式为表或文件
// global 表示设置在本次mysql实例的生命周期内有效；session 表示本次会话内有效
set global log_output = 'TABLE';参数文件：
windows操作系统下，参数文件名一般为my.ini，位置一般位于数据目录的上一级目录，可通过语句 show variables like 'datadir' 查找：
慢日志查询：
将运行时间超过设定值的所有SQL语句都记录到慢查询日志文件中，参数名为：long_query_time，默认值为10秒。
参数log_output指定了慢查询输出的格式，默认为FILE，可以将它设为TABLE，然后就可以查询mysql架构下的slow_log表。
二进制日志：
二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。
错误日志文件：
一般位于数据目录下，默认文件名为“主机名.err”。错误文件不仅记录了错误的内容，也记录了警告的信息，通过一些警告也有助于DBA对于数据库和存储引擎进行优化。
存储引擎文件：
- 表空间文件
在默认配置下会有一个初始大小为10MB，名为ibdata1的文件，该文件就是默认的表空间文件。所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中。
- 重做日志文件
在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件，即重做日志文件。InnoDB存储引擎会使用重做日志恢复数据。
3.4 页结构
从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。
- 表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。
- 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。
- 区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。
- 页是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。
- 数据是按行进行存放的。
数据页结构：
| 组成部分 | 描述 |
| 文件头 | 用来记录页的一些头信息，由页类型、页偏移量、所属表空间等部分组成，共占用38字节。 |
| 页头 | 用来记录数据页的状态信息，由记录的数量、当前页属于哪个索引、最后插入记录的位置等部分组成，共占用56字节 |
| Infimun和Supremum Records | 每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值，Supremum指比任何可能大的值还要大的值。 |
| 用户记录，即行记录 | 实际存储行记录的内容，B+树索引组织的 |
| 空闲空间 | 空闲空间，是一个链表数据结构 |
| 页目录 | 存放了记录的相对位置，按照索引键值顺序存放，这样可以利用二叉查找迅速找到记录的指针 |
| 文件结尾信息 | 检测页是否已经完整地写入磁盘 |
3.5 行格式
innoDB 存储引擎的行记录格式有：Compact、Redundant、Compressed、Dynamic等。
查看行记录格式的命令：
show table status like '表名';
3.5.1 Compact 行记录格式
首部是一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的；
变长字段之后的第二个部分是NULL标志位，该位指示了该行数据中是否有NULL值，有则用1表示；
记录头信息（record header），固定占用5字节（40位），包括记录类型、下一行记录的偏移量等；
最后的部分就是实际存储每个列的数据。需要特别注意的是，NULL不占该部分任何空间，即NULL除了占有NULL标志位，实际存储不占有任何空间；
需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6字节和7字节的大小。若InnoDB表没有定义主键，每行还会增加一个6字节的rowid列。
四、索引
4.1 索引的数据结构
InnoDB存储引擎支持以下几种常见的索引：
- B+树索引
- 全文索引
- 哈希索引
| 索引数据结构 | 特点 |
| B+树索引 | 数据库中的B+树索引可以分为聚集索引（clustered inex）和辅助索引（secondary index），叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。 |
| 全文索引 | 1、全文检索（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。 2、全文检索通常使用倒排索引（inverted index）来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。 |
| 哈希索引 | 1、自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速。 2、哈希索引只能用来搜索等值的查询 3、自适应哈希索引是由InnoDB存储引擎自己控制 |
在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。
高度为2的B+树示意图：
4.2 聚集索引
聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页，每个数据页都通过一个双向链表来进行链接。
4.3 辅助索引
辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个相应行数据的聚集索引键。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
4.4 创建、删除、查看表索引
// 查看表的索引
show index from '表名';// 创建表索引
alter table 表名 add index 索引名(字段名);
create index 索引名 on 表名(字段名);// 删除表索引
alter table 表名 drop index 索引名;
drop index 索引名 on 表名;
五、锁
5.1 概念
锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问，提供数据的完整性和一致性。InnoDB存储引擎锁提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销，并可以同时得到并发性和一致性。
5.2 锁的类型
InnoDB存储引擎实现了如下两种标准的行级锁：
- 读锁：共享锁（S Lock），允许其他事务读数据，但不能写数据
- 写锁：排他锁（X Lock），不允许其他事务读和写数据
注意：对于普通的 select 语句，InnoDB存储引擎不会加锁。
按锁粒度区分：
- 行锁：锁某行数据，锁粒度小，并发性高
- 表锁：锁整个表，锁粒度大，并发性低
- 间隙锁：锁一个区间
按锁定程度分为：
乐观锁：不会真正锁某行记录，而是通过版本号控制的
悲观锁：行锁、表锁都属于悲观锁
5.3 使用方法
// 增加读锁
// 将查找到的数据加上一个S锁，允许其他事务继续获取这些记录的S锁，不能获取这些记录的X锁(会阻塞)
// 使用场景:读出数据后，其他事务不能修改，但是自己也不一定能修改，因为其他事务也可以使用//“selec...lockin share mode “*继续加读锁。
select ... lock in share mode// 增加写锁
// 将查找到的数据加上一个X锁，不允许其他事务获取这些记录的S锁和X锁
// 使用场景:读出数据后，其他事务即不能写，也不能加读锁，那么就导致只有自己可以修改数据。
select ... for update开启两个mysql实例，local 和 local2，并将配置参数 autocommit（自动提交） 设置为 OFF。
- 首先在local实例中查询 world 数据库并加读锁，不提交事务；此时到local2中，查询同一条记录，并加写锁，发现是阻塞的。
- 如果在local实例中增加了写锁，local2中查询同一条数据且增加读锁，是阻塞的。
以上两种情况下，执行普通的 select 查询，是成功的（非锁定读）。
重要结论：
- 在读已提交隔离级别下，只会对满足条件的行记录加锁（意味着可以在这些行附近新插入数据）；
- 在可重复读的隔离级别下，会在满足条件记录的附近也加锁。比如事务A执行 select * from t where a = '1' for update，事务B不能执行 insert into t (a) value (1)，这是因为在可重复读隔离级别下，为了防止事务A出现幻读，不允许插入 a = 1 的记录，但插入 a = 2 的记录是允许的
参考视频：MySQL夺命连环20问，1天掌握别人半个月刷的mysql面试内容，直接让你上高速！_哔哩哔哩_bilibili
【鲁班学院】数据库MySQL底层技术原理解析 - 完_哔哩哔哩_bilibili
参考书籍：《MySQL技术内幕++InnoDB存储引擎》
