mysql

一、Mysql 架构

结构：Server层、存储引擎层俩部分

1.Service层

概述：Server层包含连接器、查询缓存、分析器、优化器、执行器等涵盖Mysql的大多数核心服务，sql语句的解析、优化、缓存查询（官方文档mysql5.7.20弃用，8.0版本删除）以及所有的内置函数（日期、时间，加密函数等）所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器视图等 。

2.存储引擎层

概述： 存储引擎层负责数据的存储和提取，架构模式是插件式，支持InnoDB、MyISAM、Memory等多个存储引擎

1.2.1InnoDB

InnoDB是Mysql的默认事务型引擎，支持事务，行锁，支持mvcc多版本并发控制，数据和索引都存储在.Ibd文件里 且都缓存在内存中

1.2.2MyISAM

不支持事务，表锁，并发低 mysql8.0废弃，数据文件的扩展名为：.MYD 索引文件的扩展为：.MYI
其他的引擎：Memory、CSV等

二、 Mysql事务和事务的隔离级别

概述：事务应该具备的四个属性ACID：

原子性，undo log 保证原子性

持久性，redo log 保证持久性

隔离性，锁、mvccc 保证隔离性

一致性，以上三个保证一致性

2.1事务并发引发的问题

脏读、幻读、不可重复读

2.2Mysql中的隔离级别

未提交读、已提交读、可重复读、可串行化

Mysql在可重复读的隔离级别下基本解决了幻读问题
1.快照读 ----->mvcc
2.当前读------>间隙锁解决

三、Mysql索引

B+树索引、哈希索引

3.1B+树

二叉查找树 ->平衡二叉树->B树 演化而来的
概述：B+树上的叶子结点存储关键字以及相应记录的地址，叶子结点以上各层作为索引使用。所有的中间节点都只存放关键字和下一节点的引用，数据只存放在底层的叶子节点

特性：
1.相同节点数量的情况下，B+树高度远低于平衡二叉树；
2.非叶子节点只保存索引信息和下一层节点的指针信息，不保存实际数据记录；
3.每个叶子页（LeafPage）存储了实际的数据，比如上图中每个叶子页就存放了 4 条数据记录，当然可以更多，叶子节点由小到大（有序）串联在一起，叶子页中的数据也是排好序的；
4.索引节点指示该节点的左子树比这个索引值小，而右子树大于等于这个索引值。

注意：叶子节点中的数据在物理存储上完全可以是无序的，仅仅是在逻辑上有序（通过指针串在一起）

3.2.InnoDb中的索引

3.2.1聚簇索引/聚集索引

概述：将表的主键用来构造一颗B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是索引即数据，叶子节点就是数据页，Innobd中的主键索引是一种聚簇索引，RowId

3.2.2辅助索引/二级索引

概述：辅助索引也称二级索引、非聚簇索引、他的叶子节点并不包含行记录的全部数据，他也是一个B+树，它存放的是索引列和主键列

回表：通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引 并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引） 来找到一个完整的行记录（回表是一个随机IO 一个耗时操作）

mysql一个查询优化措施：MRR：多范围读取

那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方 式去执行查询呢？？

3.2.3联合索引/复核索引

概述：将表上的多个列组合起来 进行索引我们称之为联合索引或者复合索引，比如 index(a,b)就是将 a,b 两个 列组合起来构成一个索引。建立联合索引只会建立 1 棵 B+树

3.2.4覆盖索引/索引覆盖

概述：覆盖索引并不是索引类型的一种，InnoDB存储引擎支持覆盖索引，即从辅 助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录，使用覆盖索 引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索 引，因此可以减少大量的 IO 操作

3.2.5自适应哈希索引

概述：在 InnoDB 存储引擎内部自己去监控索引表，如果监控到某个索引经常 用，那么就认为是热数据，然后内部自己创建一个 hash 索引，如果下次又查询到这个索引， 那么直接通过 hash 算法推导出记录的地址，直接一次就能查到数据。

索引的代价

3.3.高性能创建索

1.索引列的类型尽量小
2.索引选择性和前缀索引
3.只为用于搜索、排序或分组的列创建索引
4…多列索引
5.冗余和重复索引

3.4高性能使用索引策略

1.不在索引列上做任何操作
2.尽量全值匹配
3.最左前缀原则
4.范围条件放在最后
5.覆盖索引尽量用
6.不等于要慎用
7.Null、Not有影响慎用
8.使用索引扫描来做排序和分组
9.优化Count查询
10.等等

四、InnoDB引擎底层

Buffer pool 、、Double Write Buffer(双写缓冲区/双写机制)、自适应哈希索引
概述：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，Innodb中一页的大小是16kb，一般情况下，一次 最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新 到磁盘中

4.1Buffer Pool

概述：InnoDB 存储引擎在处理客户端的请求，会把完整的页数据全部加载到内存中，但是读取磁盘速度很慢，InnoDB为了缓存磁盘中的页，在Mysql服务启动的时候向操作系统申请了一块连续的内存，也就是Buffer Pool 默认128M “” innodb_buffer_pool_size"

（1）缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；
（2）缓冲池通常以页(page)为单位缓存数据；
（3）缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；
（4）InnoDB对普通LRU进行了优化：
将缓冲池分为老生代和新生代，进入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题
页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

4.2双写缓存区/双写机制

它是一种特殊的flush技术，带给InnoDb存储引擎的是数据页的可靠性。doublewrite buffer 是 InnoDB 在表空间上的 128 个页,（2 个区 大小2MB），虽然叫双写缓冲区，但是这个缓冲区不仅在内存中有，更多的是属于mysql的系统表空间，属于磁盘文件的一部分

作用：在把页写到数据文件之前，InnoDb先把他们写道双写缓冲区，在这里写完之后，才会把页数据写入到数据文件的适当位置，如果在写页的过程中意外发生崩溃，innoDb在稍后的恢复过程中在双写缓冲区找到完好的page副本用于恢复
存在的原因：
InnoDB 的页大小一般是 16KB，，将 数据写入到磁盘是以页为单位进行操作的。而操作系统写文件是以 4KB 作为单位 的，那么每写一个 InnoDB 的页到磁盘上，操作系统需要写 4 个块。极端情况下不能保证这一操作的原子性，16k数据 在写入4k时发生了系统断电或崩溃，只有一部分写成功，产生部分写入的问题，形成一个断裂的页

总结：提高 innodb 把缓存的数据写到 硬盘这个过程的安全性；间接的好处就是，innodb 的事务日志不需要包含所有数 据的前后映像,而是二进制变化量，这可以节省大量的 IO。

为什么redo不行呢？**

五、事务的底层原理和mvcc

概述：在事务的实现机制上，MySQL 采用的是 WAL（Write-ahead logging，预写式 日志）机制来实现的，所有的修改都先被写入到日志，然后再被写道数据文件，通常包括redo、undo 俩部分

5.1 redo log

概述：redo log属于MySQL存储引擎InnoDB的事务日志，MySQL 为了防止缓存页中的数据在更新后出现数据丢失的现象，引入了 redo log 机制

日志格式

• type：该条 redo 日志的类型，redo 日志设计大约有 53 种不同的类型日志。
• space ID：表空间 ID。
• page number：页号。
• data：该条 redo 日志的具体内容。

写入方式----Mini-Transaction
MySQL 把对底层页面中的一次原子访问的过程称之为一个 Mini-Transaction，比如说修改一次 Max Row ID 的值算是一个 Mini-Transaction，向某个索引对应的 B+树中插入一条记录的过程也算是一个 Mini-Transaction。一个所谓的 Mini-Transaction 可以包含一组 redo 日志，在进行崩溃恢复时这 一组 redo 日志作为一个不可分割的整体

5.2.1redo log buffer

16M ->innodb_log_buffer_size

概述：为了解决磁盘速度过慢的问题而引入了 Buffer Pool。同理， 写入 redo 日志时也不能直接直接写到磁盘上，在服务器启动时就向操作 系统申请了一大片称之为 redo log buffer 的连续内存空间，也就是日志缓冲区，内部又可以划分为许多 redo log block（512字节），向redo log buffer 中写入 redo 日志的过程是顺序的。
当一条 SQL 更新完 Buffer Pool 中的缓存页后，就会记录一条 redo log 日志写入一个叫做 redo log buffer 的缓存中，写入的 redo log 日志，最终实际上是先写入在 redo log buffer 的 redo log block 中，然后在某一个合适的时间点，将这条 redo log 所在的 redo log block 刷入到磁盘中。

5.2.2 redo日志的刷盘时机

1.redo log buffer 空间不足时 16
2.事务提交时 配置的持久性参数 innodb_flush_log_at_trx_commit 来决定是否刷盘
3.后台有一个线程，大约每秒都会刷新一次redo log buffer 中的 redo 日志到磁 盘
4.正常关闭服务器时等等。

5.2.3Mysql宕机如何保证数据不丢失

1.ySQL 中，只有当 innodb_flush_log_at_trx_commit 参数设置为 1 时，才不会出现数据丢失情况，当设置为 0 或者 2 时，可能会出现数据丢失
2.redo log 用到了WAL机制，也就是先写日志，并保证日志先落盘，才能算事务提交成功,.redo log 日志标记为 prepare 状态和 commit 状态，它是属于二阶段事务提交来保证了事务的持久性，redo log 在进行数据重做时，只有读到了 commit 标识，才会认为这条 redo log 日志是完整的，才会进行数据重做，此时redo log的事务状态是prepare，还未真正提交成功，要等bin log日志写入磁盘完成才会变更为commit，事务才算真正提交完成。

5.2 undo log

概述：为了保证事务的原子性，每当我们要一条记录做改动时insert、delete、update 都需要把回滚时所需要的东西记录下来，为了回滚而记录的这些东西称之为撤销日志 undo日志

5.3mvcc

多版本并发控制，主要是为了提 高数据库的并发性能

MVCC 实现原理主要是依赖记录中的隐式字段，undo 日志 ，Read View 来 实现的。

5.3.1版本链

**trx_id：**每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事 务 id 赋值给 trx_id 隐藏列。
roll_pointer： 每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修 改前的信息。

对该记录每次更新后，都会将旧值放到一条 undo 日志中，就算是该记录的 一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成 一个链表，我们把这个链表称之为版本链

版本链的头节点就是当前记录最新的 值。另外，每个版本中还包含生成该版本时对应的事务 id。于是可以利用这个记录的版本链来控制并发事务访问相同记录的行为，那么这种机制就被称之为多版本并发控制(Mulit-Version Concurrency Control MVCC)

5.3.2ReadView

对事务隔离级别中的读已提交和可重复读来说，都必须保证读取到已经提交了事物的修改，他俩在不可重复读和幻读上的区别就是判断版本链中的那个版本时是可见的。为此innoDb提出了一个ReadView概念
结构
1.m_ids：生成 ReadView 时当前系统中活跃的读写事务的事务 id 列表
2.min_trx_id：生成 ReadView 时当前系统中活跃a的读写事务中最小的事 务 id，也就是 m_ids 中的最小值。
3.max_trx_id：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值
4.creator_trx_id：表示生成该 ReadView 的事务的事务 id

有了这个 ReadView，这样在访问某条记录时，只需要按照下边的步骤判断 记录的某个版本是否可见
1.如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同， 意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。（可见）

2.如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明 生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当 前事务访问。 （可见）

3.如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不 可以被当前事务访问。（不可见）

4.如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间(min_trx_id < trx_id < max_trx_id)，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的， 该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经 被提交，该版本可以被访问。

读已提交隔离级别：
— 每次读取数据前都生成一个 ReadView
新的Readview活跃的事务ID列表更新，可以读取已经提交了事务的数据 ，
可重复读隔离级别：
— 在第一次读取数据时生成一个 ReadView
只生成一次，Readview中活跃的事务ID一直都是第一次的，读取不到已经提交的 ，解决了不可重复读

六Mysql底层执行

6.1 Mysql查询成本

I/O成本、 CPU成本
对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL 规定读取一个页面花费的成本默认是 1.0，读取以及检测一条记录是否符合搜索 条件的成本默认是 0.2。1.0、0.2 这些数字称之为成本常数，这两个成本常数我 们最常用到，当然还有其他的成本常数。 注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 0.2。

6.2 单表查询的成本

1.根据搜索条件，找出所有可能使用的索引
2.计算全表扫描的代价（聚簇索引占用的页面数，该表中的记录）
3.计算使用不同索引执行查询的代价(范围区间的数量，需要回表的记录数)
4.对比各种执行方案，找出成本最低的那一个

6.3基于索引统计数据成本计算

有时候使用索引执行查询时会有许多单点区间，比如使用 IN 语句就很容易 产生非常多的单点区间
index dive ：确定一个单点区间对应的二级索引记录的条数有多 少，需要我们去计算。就是先获取索引对应的 B+树的区间最左记录和区间最右 记录，然后再计算这两条记录之间有多少记录（估算），MySQL 把这种通过直接访问索引对应的 B+树来计 算某个范围区间对应的索引记录条数的方式称之为 index dive。
show variables like ‘%dive%’;

6.4EXPLAIN 输出成本

explain format = json 查询语句

6.5连接查询的成本

连接查询的总成本 = 一次驱动表成本+从驱动表查出的记录数 * 一次被驱动表的成本

6.6MySQL 的查询重写规则

对于一些执行起来十分耗费性能的语句，MySQL 还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，
1.条件化简
2.外连接消除
3.子查询优化

七、Mysql性能优化

八、Mysql中的锁