MySQL底层原理笔记

工作中常常用到MySQL，但对MySQL底层原理不够熟悉。
知其然，知其所以然。所以决定深入学习MySQL底层知识，并产出一些备忘笔记。

MySQL基本架构

MySQL主要划分为两个部分：server和存储引擎。

Server层：实现了大部分核心功能（内置函数、存储过程、触发器、视图等等），组成模块包括连接器、查询缓存、解析器、优化器以及执行器等等；
存储引擎层：实现数据的存储和读取，向Server层提供操作数据的接口。支持插拔，可选的类型包括：InnoDB、MyISAM、Memory等。最常用的是InnoDB，它从MySQL 5.5.5后成为默认版本。

下图展示的是MySQL的基本架构图。

MySQL架构图.jpg

连接器就像是MySQL的“门神”，它负责跟客户端建立连接、获取权限、维持和管理连接。

查看连接情况
```sql
mysql> show processlist;

- 连接空闲超时会自动断开，可以通过wait_timeout参数控制，默认8小时；
- 客户端不应频繁新建连接，尽量使用长连接；
- 连接应该定时断开或者执行mysql_reset_connection重新初始化连接资源，避免占用过多内存。

### 解析器
解析器有两个功能：
- **词法分析**：将SQL语句数据库表列建立关联；
- **语法分析**：检测SQL语句是否符合MySQL语法规则。

### 优化器
优化器负责从不同的执行计划中选出效率最高的方案，包括决定表的连接顺序、选择哪个索引等等。此外还会涉及查询重写。


### 执行器
执行器主要是负责检查表权限，并且调用存储引擎的接口完成执行计划，写binlog日志等。

### 查询缓存
对于SELECT语句，在解析查询之前，服务器会先检查查询缓存。如果能在缓存中找到对应的查询，服务器可以直接返回缓存的结果，而不必进行解析、优化和执行等过程。

注意，频繁更新的表的查询缓存会频繁失效，不建议开启查询缓存。MySQL 8.0 版本后不再支持查询缓存。


## 日志系统
日志系统可以分为两块，binlog和redo log。在MySQL初期，默认存储引擎是MyISAM，但它并没有crash-safe能力，所以server层的binlog仅用于归档。

后来另外一个公司开发了InnoDB引擎，它借助redo log日志实现了crash-safe能力。

因此由于历史原因，现在的MySQL日志系统由binlog和redo log两部分组成。

### binlog（归档日志）
- binlog是MySQL server层的逻辑日志，可以被所有存储引擎使用；
- binlog日志常用于数据备份和恢复；
- binlog可以是ROW 或 STATEMENT两种格式。ROW格式下binlog日志记录的是行数据变化前和变化后的内容；而STATEMENT格式下，binlog日志记录的是执行的SQL语句；
- 仅凭binlog日志是不能确保MySQL crash-safe的；
- binlog日志是顺序追加写到磁盘文件，文件达到一定大小后会切换到下一个，因此不会发生数据覆盖；
- 重要主库推荐配置 *sync_binlog=1* ，可以使得MySQL每次提交事务都会将binlog日志同步到磁盘上，保证服务器崩溃的时候不会丢失数据；

### redo log（重做日志）
- WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘数据；
- redo log是InnoDB引擎特有的物理日志；
- redo log记录的是某个数据页做了什么修改；
- 凭借redo log可以确保InnoDB的crash-safe；凭借redo log和binlog实现的两阶段提交可以保障MySQL的crash-safe；
- redo log日志是固定大小的循环写，超过文件大小会擦除老日志；
- 重要主库推荐配置 *innodb_flush_log_at_trx_commit=1* ，可以使得每次提交事务都会将buffer刷到磁盘，保证服务器崩溃的时候不会丢失数据；

  ![redo log循环写.png](https://pic-bed-sz.oss-cn-shenzhen.aliyuncs.com/blog%2Fmysql%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0%E4%B9%8B%E4%B8%80%20%E5%9F%BA%E7%A1%80%E7%AF%87%2Fredo%20log%E5%BE%AA%E7%8E%AF%E5%86%99.png)

### 两阶段提交
![两阶段提交.png](https://pic-bed-sz.oss-cn-shenzhen.aliyuncs.com/blog%2Fmysql%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0%E4%B9%8B%E4%B8%80%20%E5%9F%BA%E7%A1%80%E7%AF%87%2F%E4%B8%A4%E9%98%B6%E6%AE%B5%E6%8F%90%E4%BA%A4.png)

两阶段提交过程：
- 1.新数据更新到内存；
- 2.新数据写入redo log，并处于prepare状态；
- 3.写入binlog；
- 4.提交事务，使redo log 更新为commit状态。

crash-safe保障原理：
- 如果在步骤2之前数据库宕机，因为redo log 和binlog都没写入，内存数据丢失，所以不影响一致性；
- 如果在步骤3之前数据库宕机，因为redo log存在且处于prepare状态，但检查binlog发现不存在，则回滚事务。所以不影响数据一致性；
- 如果在步骤4之前数据库宕机，因为redo log存在且处于prepare状态，而检查binlog发现存在，则执行步骤4。所以事务成功，也不影响数据一致性；
- 如果在步骤4之后数据库宕机，因为redo log存在且处于commit状态。所以不影响数据一致性。

binlog和redo log的联系：

> 它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：如果碰到commit状态的redo log，就直接提交；如果碰到只有 parepare状态的redo log，就拿着XID去binlog找对应的事务。

### 延伸阅读
- [分布式理论(三) - 2PC协议](https://juejin.im/post/6844903621495095309)
- [05 | 分布式事务：如何保证多个系统间的数据是一致的？](https://time.geekbang.org/column/article/207508)

## 事务

### ACID概念
ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）
### 隔离性和隔离级别
SQL标准里定义了四种事务隔离级别：
- **读未提交**：一个事务还没提交时，它做的变更就能被别的事务看到；
- **读提交**：一个事务提交之后，它做的变更才会被其他事务看到；
- **可重复读**：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的；
- **串行化**：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

四种隔离级别区别：

|隔离级别|脏读可能性|不可重复读可能性|幻读可能性|加锁读|
| -- | -- | -- | -- | -- |
|读未提交|Y|Y|Y|N|
|读提交|N|Y|Y|N|
|可重复读|N|N|Y|N|
|串行化|N|N|N|Y|

### MySQL中的事务
MySQL提供了两种事务型存储引擎：InnoDB和NDB Cluster，而MyISAM并不支持事务。

#### MVCC与事务隔离实现
四种隔离级别的在InnoDB实现：
- **读未提交**：直接返回记录上的最新值，没有视图概念；
- **读提交**：在每个SQL语句开始执行的时候创建视图；
- **可重复读**：在事务启动时创建视图，整个事务存在期间都用这个视图；（注意，InnoDB利用MVCC已经在可重复读隔离级别下避免了幻读问题！！注意和上边的表格内容区分清楚！！！）
- **串行化**：隔离级别下直接用加锁的方式来避免并行访问。
- 
> 这个所谓的视图其实是通过多版本并发控制（MVCC）来实现。（详情见《高性能MySQL》第三版 1.4 多版本并发控制）

#### 事务的启动

事务启动方式：
- 1.显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
- 2.set autocommit=0，这个命令会将这个线程的自动提交关掉（注意，仅影响当前线程。）。这意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。一般建议总是使用 set autocommit=1, 然后通过显式语句的方式来启动事务。

长事务会导致数据库空间，并且占用所资源，生产中应该尽量避免长事务。查询长事务方法：
```sql
// 查询超过60秒的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

查看修改事务隔离级别

MySQL默认的事务隔离级别是可重复读（REPEATABLE READ）。

可以通过sql查看MySQL事务隔离级别：

show variables like "%isolation%";

MySQL提供了SET TRANSACTION语句，该语句可以改变单个会话或全局的事务隔离级别。语法格式如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：

SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session (包括当前session）不受影响；
如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

任何用户都能改变会话的事务隔离级别，但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。具体见：08 | 事务到底是隔离的还是不隔离的？

-理解MySQL的锁机制和事务原理

常见索引模型

模型优点缺点哈希表等值查询和范围查询都非常高效插入数据成本高，只适合静态数据存储引擎二叉搜索树查询和插入都很高效树高过大，实际生产中要多次读取磁盘，查询效率不高 N叉树查询和插入都高效、且树高较低 –

InnoDB索引模型

InnoDB使用B+树索引模型，每一个索引都是一棵独立的B+树；
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。注意B+树的节点都对应着InnoDB的一个数据页，默认大小16K。也就是说叶子节点里包含着多行数据；
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
InnoDB索引模型图.png
页分裂：插入新数据的时候，B+树节点所在的数据页满了之后，节点会发生分裂，需要申请新的数据页来保存新节点，这个过程叫做页分裂。 –> 页分裂会使得空间利用率下降，并降低性能。
页大小：InnoDB页大小（innodb_page_size）默认值为16kb，可以通过调整页大小来间接改变B+树分叉数量
自增主键：考虑到页分裂的性能影响，使用自增主键要比其他业务字段作为主键ID要更优。因为自增主键的每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
不必使用自增主键的场景：如果该表只有一个索引，且该索引为唯一索引，则适合让该索引直接作为主键索引。这样子可以避免生成两棵索引树以节省空间，并且避免回表以增加查询性能。
回表：使用非主键索引查询数据的时候，如果所查数据包含了主键以外的数据，则需要回到主键索引进行二次查询，这个过程称为回表。 –> 因此应该尽量使用主键索引查询。
覆盖索引：当二级索引已经“覆盖”了查询需求的时候，该索引可称为覆盖索引。使用覆盖索引可以回表操作，减少搜索B+树的次数。因此，可以为高频查询建立覆盖索引。
最左前缀原则：只要查询条件能够满足联合索引的最左前缀，则可以使用该联合索引来加速检索。因此，建立联合索引时候，需要合理安排字段顺序，充分利用最左前缀原则来减少索引数量。
索引下推优化（index condition pushdown)：MySQL5.6之后引入了索引下推，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
Change Buffer:当对二级索引数据页进行变更，而这些数据页又不在buffer pool中的时候，会使用Change Buffer来缓存这些变更。这些INSERT, UPDATE, 或者 DELETE 操作（DML）带来的变更会在这些数据页被其他读操作加载进缓存的时候被合并。简单来讲，Change Buffer是用来”懒合并”，将非必需的磁盘读写延后。因此适合写多读少的业务情景。另外，唯一索引无法使用ChangeBuffer，因此一般推荐优先使用普通索引。

MySQL误判索引解决办法

对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决；例如，使用delete来删除数据，会导致统计信息不准。
对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。其中全局锁和表锁都是Server层支持的，而行锁是引擎层实现的，InnoDB支持行锁，而MyISAM不支持行锁。

加全局锁办法：flush table with read lock； （解锁使用unlock table;)会使得整个数据库实例处于只读状态。一般用在MySIAM引擎下进行全库逻辑备份，而在InnoDB下一般不采用这种办法，应该使用mysqldump –single-transaction 通过事务来获得一致性视图，而不用加全局锁。
不要使用 set global readonly=true； 来加全局锁。因为readonly值会被用在其他用途，并且当客户端发生异常，readonly值会持久化到数据库上，导致数据库锁不能释放。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MySQL支持表锁，一般使用 lock tables [tablename] read/write 进行加锁。该命令的加锁会对所有的线程生效。
MDL（metadata lock)是另一类表锁，不需要显式使用，在访问一个表的时候会被自动加上。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。因此，MDL锁作用是防止增删改数据（DML)和加字段等修改表结构的操作（DDL）互相冲突。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。因此，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
行锁的算法可以分为三种：Record Lock（单行记录上的锁）、Gap Lock(间隙锁，锁定一个范围不包含记录本身)和Next-Key Lock(锁定一个范围并包含记录本身)。具体参考：MySQL探秘(七):InnoDB行锁算法
InnoDB通过给索引项加锁来实现行锁，如果没有索引，则通过隐藏的聚簇索引来对记录加锁。如果操作不通过索引条件检索数据，InnoDB 则对表中的所有记录加锁，实际效果就和表锁一样。
当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时，InnoDB存储引擎会将Next-Key Lock降级为Record Lock，即只锁住索引本身，而不是范围。如果是范围查询，则边界的Next-Key Lock也有可能不会降级。21 | 为什么我只改一行的语句，锁这么多？
InnoDB对于辅助索引使用Next-Key Lock锁，也就是说不仅会锁住辅助索引值所在的范围以及记录本身，还会将其下一键值加上Gap LOCK。
Next-Key Lock本质是Record Lock + Gap Lock；
Next-Key Lock是前开后闭区间

两个“原则”、两个“优化”和一个“bug”。

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

意向排他锁

InnoDB支持多粒度锁，允许行锁和表锁共存。而意向锁属于一种表锁，意向锁互相之间不会排斥，但意向锁和表级的X锁存在互斥关系。具体参考：详解 MySql InnoDB 中意向锁的作用

死锁检测策略

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。但注意，如果打开死锁检测，事务执行过程中发生阻塞，都会进行检测，从而CPU性能损耗。
尽管会带来性能损耗，但正常来说都应该开启死锁检测。
减少死锁的主要方向，就是控制访问相同资源的并发事务量。

1.如何避免长事务对业务的影响？
这个问题，我们可以从应用开发端和数据库端来看。
- 首先，从应用开发端来看：确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
- 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
- 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。
其次，从数据库端来看：
- 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
- Percona 的 pt-kill 这个工具不错，推荐使用；
- 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
- 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更>大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。
2.如果针对InnoDB表进行索引重建，对二级索引执行：
```
  alter table T drop index k;
  alter table T add index(k);
```
以及对主键索引执行：
```
  alter table T drop primary key;
  alter table T add primary key(id);
```
这两者有啥异同，或者存在什么问题？
- 索引可能因为删除或页分裂，而使得数据页存在空洞，重建索引会重新创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
- 对二级索引使用该SQL重建是可行的合理的。
- 对于主键索引，无论是删除主键还是新建主键，都会导致整个表发生重建。而应该使用alter table T engine=InnoDB； 语句进行主键重建。

3.字段k上有二级索引，现有如下两个功能相同的SQL语句，哪个更优：
```
  select * from T where k in(1,2,3,4,5)
  select * from T where k between 1 and 5
```
SQL1需要搜索5次，并回表5次；SQL2只需要搜索1次，并回表5次。SQL充分利用了B+树叶子节点顺序排序的特点，可以加速范围查询。
4.如何解决热点行更新导致的性能问题？
- 1、如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
- 2、控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
- 3、将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高。
5.MySQL出现抖动原因
很有可能在刷脏页。刷脏页触发原因有：
- redo log写满；
- 内存不足（其实是buffer pool）的时候淘汰了脏页；
- MySQL在认为空闲的时段，会自行刷脏页；
- MySQL关闭前会刷脏页；
  刷脏页的策略调优：
- 磁盘能力。能力越大，脏页刷的越快。参数：innodb_io_capacity；
- 脏页比例上限。参数innodb_max_dirty_pages_pct，默认值:75%。生产中应该调控innodb_io_capacity使得脏页比例不要接近innodb_max_dirty_pages_pct 。查看脏页比例方法：
```
  set global show_compatibility_56=on;
 select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
 select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
 select @a/@b;
```
- 刷相邻脏页。机械磁盘建议打开（1），SSD磁盘可以关闭（0）。参数：innodb_flush_neighbors
6.删除了数据，但是表文件没有缩小？
表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：
- 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起，此时drop表不会回收表空间。
- 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中，此时drop表会回收表空间。
删除数据记录不一定回收空间，一般只是标记为可复用，此时数据页会留下空洞的，等待新的数据插入复用这些空洞。

主动回收表空间的方法：
- 使用alter table t engine = InnoDB；该命令会使用Online DDL重建新表（MySQL5.6+），因此可以在业务低峰期执行。；
- 使用analyze table t 。该命令会对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
- 使用optimize table t 相当于重建表+重新统计索引信息。
7.count效率

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)

MySQL实战45讲: 非常适合入门的MySQL学习资料；
《高性能MySQL》
《MySQL技术内幕（InnoDB存储引擎）》

MySQL基本架构

查看修改事务隔离级别

常见索引模型

InnoDB索引模型

MySQL误判索引解决办法

意向排他锁

死锁检测策略

Recommend

再读Spring源码之三常用扩展点

《代码整洁之道》

Spring Security 学习之使用篇

Spring Security 学习之原理篇

抓虫笔记-ExceptionInInitializerError导致线程池中的线程异常被吞

深入理解Java文件输入输出流和文件描述符

真香，微信公众号支付完整流程案例

支付宝微信一码付上线了，欢迎体验

云原生时代高性能Java框架—Quarkus（一）

mysql索引原则【收藏】

About Joyk