MySQL 架构与 SQL 执行流程

演化版本 MySQL：MariaDB、InnoSQL 通信类型：同步、异步链接方式：长连接、短链接协议：tcp、unix socket

查看服务端连接数。

客户端链接与线程关系：客户端每产生一个会话 session，在服务端就会有一个线程去处理这个操作。

-- mysql默认的影响范围是会话级别，如果需要展示或修改全局的则需要加上globalshow global status like '%thread%';-- 非交互形式的超时时间28800，jdbc程序show global variables like '%wait_timeout%';-- 交互式的超时时间28800，客户端工具datagrip等show global variables like '%interactive_timeout%';-- 默认客户端最大连接数151个show global variables like '%max_connections%'; 
-- 查询缓存。但是这个缓存很鸡肋，如果查询语句中的大小写不一致也认为是不同语句会清除该表上一次的查询缓存。show variables like '%query_cache%';

MySQL 查询语句执行流程

select * from table where a=123

query_cache ：缓存

Parser ：语法解析

词法解析：将完整的 SQL 拆分为一个个单词语法解析：进行语法检查通过语法解析会得到一个解析树

pre process ：预处理器

对语义进行分析，判断别名是否正确、表是否存在等

optimizer ：优化器

对于一条 SQL，其执行路径是可以不一样的。当有多个索引的时候，优先走哪个索引？优化 SQL：生成、选择执行路径（也称为执行计划）（基于 cost 的优化器）

execution plan ：执行计划

// 查看实行计划explain select * from user// 返回json数据explain format=json select * from user
-- 开启 100% 展示 MySQL 语句执行的神器-Optimizer Traceshow variables like '%optimizer_trace%'-- 开启会有性能消耗set optimizer_trace='enabled=on';select * from information_schema.optimizer_trace

{  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "1051502.60"    },    "table": {      "table_name": "user",      "access_type": "ALL",      "rows_examined_per_scan": 5192648,      "rows_produced_per_join": 5192648,      "filtered": "100.00",      "cost_info": {        "read_cost": "12973.00",        "eval_cost": "1038529.60",        "prefix_cost": "1051502.60",        "data_read_per_join": "2G"      },      "used_columns": [        "id",        "username",        "password"      ]    }  }}

executor ：执行器

storage engine ：存储引擎

InnoDB 存储引擎的磁盘与内存结构

CREATE TABLE `demo`.`user_innodb`  (  `id` int(0) NOT NULL,  `username` varchar(255) NULL,  `password` varchar(255) NULL,  PRIMARY KEY (`id`)) ENGINE = INNODB;
CREATE TABLE `demo`.`user_myisam`  (  `id` int(0) NOT NULL,  `username` varchar(255) NULL,  `password` varchar(255) NULL,  PRIMARY KEY (`id`)) ENGINE = MYISAM;
CREATE TABLE `demo`.`user_memory`  (  `id` int(0) NOT NULL,  `username` varchar(255) NULL,  `password` varchar(255) NULL,  PRIMARY KEY (`id`)) ENGINE = MEMORY;-- 给每个表中增加数据SET @i = 1;INSERT INTO user (id, username, password)SELECT @i := @i + 1 AS id,       CONCAT('user', LPAD(@i, 5, '0')) AS username,       SUBSTRING(MD5(RAND()), 1, 8) AS passwordFROM   INFORMATION_SCHEMA.TABLES,       INFORMATION_SCHEMA.TABLES AS t2WHERE  @i < 5000000;

查看表类型存储结构（表类型也称为存储引擎）

各个存储引擎本质区别？

业务场景：

• 希望快速，不需要持久化

• 存储历史书，不需要索引，可以压缩

• 读写并发，要求一致性

一个表如何快速的插入数据？可以先通过创建 MyISAM 的存储引起的表，插入数据后再修改为 innodb 存储引擎。

查看存储引擎状态show engine INNODB STATUS;

MySQL 更新语句的执行流程

update user_innodb set username='huathy' where id=2056357

1. 事务开始从内存（buffer pool）或磁盘（data file）读取到包含这条数据的数据页，返回给 server 的执行器

2. server 的执行器修改数据页的改行数据值

3. 记录 username='旧值' 到到 undo log

4. 记录 username='huathy' 到 redo log

5. 调用存储引擎接口，记录数据页到 buffer pool

6. 提交事务（后续会刷脏到磁盘）

俩阶段提交思想。俩阶段提交是为了保证 redo log 和 bin log 内容双写一致性。这也是很多分布式事务的方案。

innoDB 操作磁盘数据

预读取：局部性原理（会磁盘预读相邻的数据到内存）页 16KB。操作系统也有类似的预读 4KB（4K 对其）。

InnoDB 内存缓冲区 Buffer Pool（提升读写性能）

没有保存到磁盘文件的，暂时在缓冲区的页，称为脏页。后台线程会定时的去给他刷脏到磁盘中。

redo log

要怎么防止内存中的数据重启宕机？

为此设计了日志文件：redo log（redolog 在磁盘中 ib_logfile0/1）

redolog 是顺序 IO，而存储文件是随机 IO。随机 IO 需要寻址，寻址就存在时间开销。延缓刷盘时机，大大提高性能。顺序 IO 和随机 IO：寻址有时间开销

redolog 作用：保证了书的安全性，延缓刷盘时机，提高吞吐量。

1. 为 innodb 提供了崩溃恢复的特性，实现了持久性

2. redolog 记录的是在某个数据页上做什么修改，属于物理日志。

3. redolog 的大小是固定的，前面的内容会被覆盖，一旦写满就会触发 buffer pool 到磁盘的同步，以便腾出空间来记录后面的修改。

undo log

记录事务发生之前的数据状态，发生异常时回滚，保证原子性。undo log 没有独立的表文件，而是存放在系统表的文件里面 ibdata1

MySQL 架构与内部模块

InnoDB 架构

MySQL :: MySQL 5.7 Reference Manual :: 14.4 InnoDB Architecture 

内存结构

内存缓冲区（Buffer Pool）LRU 算法回收写满内存，冷热数据分离

内存缓冲区越大，读写性能越大。在数据库专用服务器上，可以占比 80%

当内存写满后，使用 LRU 来回收。把 List 的下表存储 Map 的 Value 中，新增、访问时移动到 head，从 tail 淘汰。MySQL 对 LRU 算法进行优化，雷同 JVM 新生代老年代的思想。如果一个新数据过大，会放到冷数据区，如果没有被访问，就直接淘汰。即使加载大量内容，也不会淘汰热数据区的数据。这个数据区存放的是指向数据页的指针。

ChangeBuffer

ChangeBuffer 是 BufferPool 的一部分。如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是否重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提高更新语句的执行速度。

下面的参数表示，ChangeBuffer 占整个 BufferPool 的百分比大小。如果业务场景是写多读少的话，且大部分的索引是非唯一索引，那么可以适当调大该参数。

log buffer 刷盘时机

刷盘频繁，可靠性高，性能下降。如果机器稳定，可以减少刷盘，提高性能。

自适应 hash 索引

磁盘结构

表空间

1. 系统表空间

2. 通用表空间

3. 独占表空间 innodb 默认开启独占表空间

4. 临时表空间

双写缓冲

页备份 page back，解决部分写失效问题（16K 写到 4K，写了一半中断了）。

线程

page threadpage cleaner threadio threaderror monitor thread

服务端 binlog

binlog 是所有存储引擎都可以使用的。是以事件的形式记录了所有 DDL 和 DML 语句。记录操作而非数值，是逻辑日志。可以用来做主从复制和数据恢复。slave 请求 master 节点的 binlog。使用 binlog 恢复数据需要定时全量备份数据。

崩溃恢复时候

1. binlog 无记录，redolog 无记录：在 redulog 写之前 crash，恢复操作：回滚日志

2. binlog 无记录，redolog 状态：prepare 在 binlog 写完之前 crash，恢复操作：回滚日志

3. binlog 有记录，redolog 状态：prepare 在 binlog 写完提交事务之前的 crash，恢复操作：提交事务

4. binlog 有记录，redolog 状态：commit 正常的事务，不需要恢复