# Mysql 实战45讲

## 一条SQL查询语句执行过程

### MYSQL 基本架构

![image-20211101161431695](.\mysql实战45讲.assets\image-20211101161431695.png)

存储引擎的选择，默认，innodb

连接器，长连接，短连接，爆内存，权限认证（拍照机制）

查询缓存，key-value映射。key是语句，value是查询结果

分析器，提取字段，比如把select后面跟着的东西提取出来。参数提取完成后，执行语法分析，看看sql语句是否满足。

优化器，选择最后的查询语句。比如在表里面有多个索引的时候，决定使用哪个索引

执行器，先判断权限是否满足，然后执行语句

## 日志系统，sql更新语句执行过程

**查询语句的那一套流程，更新语句也是同样会走一遍。**

**更新语句会涉及两个重要的日志模块：redo log 和binlog**

**redo log 和 binlog 区别**

**更新语句执行流程**

**两阶段提交机制**

### redo log

引擎层，是InnoDB引擎特有的日志。

参考酒店老板，账本，粉笔板，赊欠事件。

当赊欠的人少（更新操作较少），酒店老板就对账本完成赊欠的记录操作（即数据更新），而当赊欠的人很多，老板忙不过来的时候，会先把操作记录在粉笔板上，等闲下来再针对粉笔板上的记录，进行账本的更新。
这里把赊账记录在粉笔板上，就相当于服务器将更新操作，记录在redo log 中。redo log 是物理日志，记录的是“在某个数据页上做了什么修改”。
而当老板持续一段时间都特别忙，粉笔板上写满了赊欠的人，写不下后，老板会停止“把赊欠记录写在粉笔板”这一行为，会把粉笔板上的记录与账本中核对，进行更新操作。然后后粉笔版清除，这样就能继续记了。
所以，当一段时间mysql更新语句的操作量都非常大的话，后面会有一段时间，mysql的吞吐量急剧减小（执行redo log 的日志更新），这就涉及到一个问题：更新不实时，会有影响的吧！

### binlog

Server层的日志，binlog(归档日志)

binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

### redo log 与 binlog的区别

这两种日志有以下三点不同。

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

###  *redo log 两阶段提交机制*

## 事务隔离

**事务一致性**

**ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）**

# 索引



## mysql B+ 树

**一个表有多少个索引就有多少个B+树，mysql 中的数据都是按顺序保存在 B+ 树上的**（所以说索引本身是有序的）。

MySQL的存储结构

表存储结构

单位：表>段>区>页>行

在数据库中， 不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说存储空间的基本单位是页。

一个页就是一棵树B+树的节点，数据库I/O操作的最小单位是页，与数据库相关的内容都会存储在页的结构里。

B+树索引结构

在一棵B+树中，每个节点为都是一个页，每次新建节点的时候，就会申请一个页空间

同一层的节点为之间，通过页的结构构成了一个双向链表

非叶子节点为，包括了多个索引行，每个索引行里存储索引键和指向下一层页面的指针

叶子节点为，存储了关键字和行记录，在节点内部(也就是页结构的内部)记录之间是一个单向的表

B+树页节点结构

有以下几个特点

将所有的记录分成几个组， 每组会存储多条记录，

页目录存储的是槽(slot)，槽相当于分组记录的索引，每个槽指针指向了不同组的最后一个记录

我们通过槽定位到组，再查看组中的记录

页的主要作用是存储记录，在页中记录以单链表的形式进行存储。

单链表优点是插入、删除方便，缺点是检索效率不高，最坏的情况要遍历链表所有的节点。因此页目录中提供了二分查找的方式，来提高记录的检索效率。

B+树的检索过程

我们再来看下B+树的检索过程

从B+树的根开始，逐层找到叶子节点。

找到叶子节点为对应的数据页，将数据叶加载到内存中，通过页目录的槽采用二分查找的方式先找到一个粗略的记录分组。

在分组中通过链表遍历的方式进行记录的查找。

为什么要用B+树索引

数据库访问数据要通过页，一个页就是一个B+树节点，访问一个节点相当于一次I/O操作，所以越快能找到节点，查找性能越好。

B+树的特点就是够矮够胖，能有效地减少访问节点次数从而提高性能。

下面，我们来对比一个二叉树、多叉树、B树和B+树。

二叉树

二叉树是一种二分查找树，有很好的查找性能，相当于二分查找。

但是当N比较大的时候，树的深度比较高。数据查询的时间主要依赖于磁盘IO的次数，二叉树深度越大，查找的次数越多，性能越差。

最坏的情况是退化成了链表，如下图

为了让二叉树不至于退化成链表，人们发明了AVL树(平衡二叉搜索树)：任何结点的左子树和右子树高度最多相差1

多叉树

多叉树就是节点可以是M个，能有效地减少高度，高度变小后，节点变少I/O自然少，性能比二叉树好了

B树

B树简单地说就是多叉树，每个叶子会存储数据，和指向下一个节点的指针。

例如要查找9，步骤如下

我们与根节点的关键字 (17，35)进行比较，9 小于 17 那么得到指针 P1；

按照指针 P1 找到磁盘块 2，关键字为(8，12)，因为 9 在 8 和 12 之间，所以我们得到指针 P2；

按照指针 P2 找到磁盘块 6，关键字为(9，10)，然后我们找到了关键字 9。

B+树

B+树是B树的改进，简单地说是：只有叶子节点才存数据，非叶子节点是存储的指针；所有叶子节点构成一个有序链表

例如要查找关键字16，步骤如下

与根节点的关键字 (1，18，35) 进行比较，16 在 1 和 18 之间，得到指针 P1(指向磁盘块 2)

找到磁盘块 2，关键字为(1，8，14)，因为 16 大于 14，所以得到指针 P3(指向磁盘块 7)

找到磁盘块 7，关键字为(14，16，17)，然后我们找到了关键字 16，所以可以找到关键字 16 所对应的数据。

B+树与B树的不同：

B+树非叶子节点不存在数据只存索引，B树非叶子节点存储数据

B+树使用双向链表串连所有叶子节点，区间查询效率更高，因为所有数据都在B+树的叶子节点，但是B树则需要通过中序遍历才能完成查询范围的查找。

B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定

B+树查询效率更高，因为B+树矮更胖，高度小，查询产生的I/O最少。

这就是MySQL使用B+树的原因，就是这么简单！

## 数据结构

*索引的出现是为了提高查询效率，但是实现索引的方式却有很多种，所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多，这里我先给你介绍三种常见、也比较简单的数据结构，它们分别是**哈希表、有序数组和搜索树**。*

搜索树规定其每个节点的键必须大于其左子树中的任何一个键且小于其右子树中的任何一个键

下图中InnoDB 索引模型 B+树 就是属于搜索树

## InnoDB 索引模型 

**一个表有多少个索引就有多少个B+树，mysql 中的数据都是按顺序保存在 B+ 树上的**（所以说索引本身是有序的）。

### 主键索引与非主键索引区别（回表）

**回到主键索引树搜索的过程，我们称为回表**

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

```mysq
mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;
```

- 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

- 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

### 页分裂与页合并

![img](mysql实战45讲.assets/dcda101051f28502bd5c4402b292e38d.png)

以上图为例

1. 如果新插入的数据ID是700，就要在R5的记录后插入一个新记录
2. 如果新插入的数据ID是400，需要逻辑上挪动后面的数据，空出位置
3. 如果R5所在的数据页已经满了，就需要申请一个新的数据页，然后挪动部分数据过去
   - 除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
   - 当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

### 自增主键

**自增主键防止页分裂，逻辑删除并非物理删除防止页合并**

为什么说自增主键防止页分裂？

- mysql在底层是以页为单位存放数据，一个页有多行数据
- 索引是有序的，插入数据的时候，为了确保有序，mysql会将数据插入到合适的位置。当往一个快满或已满的数据页中插入数据时，新插入的数据会将数据页写满，mysql 就需要申请新的数据页，并且把上个数据页中的部分数据挪到新的数据页上。这就造成页分裂
- 如果主键是自增id，当前页写满了就重新申请新页，不会有数据挪动

为什么说逻辑删除防止页合并？

逻辑删除但物理内存中当前页中仍保留数据，自然不会合并

**所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。**

什么场景适合用业务字段做主键呢？

1. 只有一个索引
2. 该索引必须是唯一索引

经典KV场景，key-value

这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。（回表

### 覆盖索引

https://juejin.cn/post/6844903967365791752

```mysql

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
```

首先要知道，非主键索引中，存放的是主键。
主键索引，存放的是所有数据。
以上图为例，执行`select * from T where k between 3 and 5`时，就会先找索引k ，k=3，然后找到对应的主键，再拿主键去找*

![image-20220705222943242](mysql实战45讲.assets/image-20220705222943242.png)

再这个栗子中，要查的数据，即 *  只有主键索引中有，所以不得不回表。

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 是主键已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。

也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

`select * from T where ID between 100 and 500  ` 这种就不需要回表

**由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。**

### Mysql key 与 index 区别

https://blog.csdn.net/liangwenmail/article/details/86703646

实际使用好像没区别？

*那就把key当index用吧*

### 最左前缀原则 ？![img](mysql实战45讲.assets/89f74c631110cfbc83298ef27dcd6370.jpg)

![image-20220705231802812](mysql实战45讲.assets/image-20220705231802812.png)

有点像ES的那种，综合搜索语法，根据用户的输入，筛选出符合规范的数据，然后找到主键，回表。

### 索引下推

https://blog.csdn.net/luxiaoruo/article/details/106637231

```mysql

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
```



简单来讲就是，没开启索引下推的话，Server层会把where条件中在组合索引的字段（这里是age）全部推送到引擎层，引擎层根据索引的断桥原则匹配数据拿主键，不管 age 字段 (实际上Server层已经把这个组合索引中的字段推送到引擎层了)，直接去查主键，然后再主键的所有行中，看筛选是否成立

开启所有下推的话，Server层会把where条件中在组合索引的字段全部推送到引擎层，引擎层根据索引的断桥原则匹配数据拿主键，然后进行age的筛选，拿到最终匹配的主键关键字再返回Server层，Serer层进行剩余where条俊的筛选，即ismale字段的筛选

**什么情况下才能用索引下推呢？**

1. ICP 用于访问方法是 range/ref/eq_ref/ref_or_null，且需要访问表的完整行记录。
2. ICP适用于 InnoDB 和 MyISAM 的表，包括分区的表。
3. 对于 InnoDB 表，ICP只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。对于 InnoDB 的聚簇索引，完整的记录已经读进 InnoDB 的缓存，使用 ICP 不能减少 I/O 。
4. ICP 不支持建立在虚拟列上的二级索引（InnoDB 支持在虚拟列上建立二级索引）。
5. 引用子查询、存储函数的条件没法下推，Triggered conditions 也没法下推。

### 聚簇索引 与 非聚簇索引

**聚簇索引 特征**

1. 索引必须是唯一索引
2. 叶子节点处存储的是整行数据

**非聚簇索引 特征**

1. 索引值必须为可不唯一？
2. 叶子节点处储存的索引行跟主键

InnoDB中，聚簇索引不一定是主键，但主键一定是聚簇索引。

聚簇索引不一定是主键的原因是：聚集索引可能是第一个不允许为 null 的唯一索引，如果也没有这样的唯一索引，InnoDB 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引。

## 小结

1. 一张表有多个索引
2. 一个索引对应一张B+树
3. 树结点的key值就是某一行的主键，value是该行的其他数据。新建索引就是新增一个B+树，查询不走索引就是遍历主B+树。

# 全局锁和表锁

**根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表锁和行锁三大类**

### 全局锁

全局锁主要用在逻辑备份过程中。

逻辑备份过程中，加全局锁，可读不可写，会产生两个问题：

- 如果在主库上备份，备份期间数据无法更新，业务上基本就得停摆
- 如果在从库上备份，备份期间从库无法执行主库同步过来的binlog，会导致主从延迟

那么如何解决以上问题呢？

对于全部是InnoDB引擎的库，官方自带的逻辑备份工具是 mysqldump ，使用 -single-transaction 参数，导数据之前就会启动一个事务，来确保拿到一致性使徒。由于MVCC的支持，在此过程中数据是正常更新的。

*但是single-transaction 方法只适用于所有的表使用事务引擎的库*

对于不支持事务的引擎，备份只能通过FTWRL方法。正因此，DBA要求业务开发使用InnoDB替代MyISAM

FTWRL(Flush tables with read lock)
当你需要让整个库处于只读状态的时候，可以使用这个命令，使用后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

**FTWRL 比 set global readonly=true 更安全**

1. global的影响更大
2. 整个库设置成`readonly`后如果客户端发生异常，数据库会保持一直保持`readonly`的状态，风险较大

### 表级锁

#### 表锁

语法：lock tables ... read/write

可以用`unlocks tables`主动释放，也可以断开连接时自动释放。

注意：lock tables 不仅仅是限制别的线程的读写，对于本线程接下来的操作也会限制。

lock tables t1 read, t2 write;
```
1、lock table t1 read 当前线程只能对t1表read ,不能write，当前线程对其它表不能read,write, 其它线程对表t1的DML操作都会metalock锁等待，对其它表没影响 
2、lock table t1 write write带read权限，当前线程可以对指定表t1进行read ,write ，对其它表不行。 其它线程不能对表t1read,write ,其它表可以正常操作。
```



#### MDL（metadata lock）

MDL的作用是，保证读写的正确性。是隐式执行的

MySQL 5.5 中引入 MDL，当对一个表进行增删改查的操作时，加MDL读锁；要对表做结构变更操作时，加MDL读锁

- 读锁之键不互斥
- 读写锁之键、写锁之键是互斥的。

![img](mysql实战45讲.assets/7cf6a3bf90d72d1f0fc156ececdfb0ce.jpg)

*注意：MDL锁的例子。因为sessionB加了MDL读锁，导致后面的sessionC阻塞。如果sessionB一直没有完成select，那么sessionC申请写锁被阻塞，将会导致后面的sessionD等申请读锁都被阻塞。*

### 如何安全的给小表加字段

1. 解决长事务。长事务不提交，会一直占用MDL。可以考虑kill掉长事务
2. 如果是更新频繁的热点表，不得不加字段的话。在alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。



# 事务到底是否是隔离的