尝试搞懂 MySQL（一）

最新推荐文章于 2023-08-07 17:00:39 发布

Hush..

最新推荐文章于 2023-08-07 17:00:39 发布

阅读量407

点赞数

分类专栏：工作文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/weixin_43911865/article/details/127939197

版权

工作专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、MySQL 基础架构

先上个 MySQL 逻辑架构图

可以看出，整个架构分为两层：server 层和存储引擎层。其中：

server 层：连接器、查询缓存、分析器、优化器、执行器等；
存储引擎层：插件式，支持 InnoDB、MyISAM、Memory 等多个存储引擎，负责数据的存储和提取。

整个查询流程：

建立连接 -> 查询缓存 -> 分析器（词法分析、语法分析）-> 优化器（索引选择、执行计划选择）-> 执行器（操作引擎，返回结果）

查询缓存：key 是查询的语句，value 是查询的结果。但 ⚠️ MySQL 8.0 版本后已经没有查询缓存功能了。

二、日志模块

1. redo log（重做日志）-> InnoDB 引擎特有，位于存储引擎层

WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志 redo log，再写磁盘。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

crash-safe：数据库发生异常重启，之前提交的记录都不会丢失。

2. binlog（归档日志）-> 位于 server 层，所有引擎都可以使用

两种日志的区别：

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

一条 update 语句的执行过程：

3. 两阶段提交

目的：让两份日志之间的逻辑一致，避免写完 redo log 后 bin log 还没写但数据库挂了。

实现：先写 redo log，并使其处于 prepare 状态 -> 再写 bin log -> 再使 redo log 变为 commit 状态

如何将数据库恢复到任意一个时间点的状态：

首先，找到最近的一次全量备份，从这个备份恢复到临时库；
然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到误删表之前的那个时刻。

相关参数

innodb_flush_log_at_trx_commit = 1 -> 每次 redo log 都直接持久化到磁盘
sync_binlog = 1 -> 每次事务的 binlog 都持久化到磁盘

三、事务

1. 概念及实现

经典八股：

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；
而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

相关参数：

show variables like 'transaction_isolation';

2. 事务启动方式

显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。可能会导致长事务。
commit work and chain：提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。

3. 事务的隔离

MySQL 中的视图概念：

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

MySQL 中的快照：

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

视图 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。

当前事务的一致性视图（read-view） = 视图数组 + 高水位

视图数组：InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况：

a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；

b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

四、索引 -> 存储引擎层

1. 相关概念

以 InnoDB 为例，其使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

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

查询过程：

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

也就是说，基于非主键索引的查询需要多扫描一棵索引树。

问题：为什么建表规范里要求一定要有自增主键 NOT NULL PRIMARY KEY AUTO_INCREMENT ？

从性能角度看：

自增主键的插入数据模式，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

从存储角度看：

每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

2. 覆盖索引

如何建立多个字段的联合索引：
ALTER TABLE 'table_name' ADD INDEX ('col1', 'col2', 'col3');

定义：根据某个索引查询另一字段时，若该字段的值已经在这个索引上，此时可以直接提供查询结果，不需要回表，即为覆盖索引。

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

3. 最左前缀原则

建立联合索引时，索引项是按照索引定义里面出现的字段顺序排序的。

只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

问题：在建立联合索引的时候，如何安排索引内的字段顺序？

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

第二原则是，考虑空间。name 字段是比 age 字段大的，可以创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

4. 索引下推 -> 减少回表次数

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

索引下推示意图：

如何执行：

InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

5. 普通索引和唯一索引

5.1 查询过程：select id from T where k=5

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

二者性能几乎相同

5.2 更新过程

前置概念：

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。

情况1：这个记录要更新的目标页在内存中

对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

情况2：这个记录要更新的目标页不在内存中

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

明显加了唯一索引的情况2，插入操作更慢。

所以，唯一索引用不上 change buffer 的优化机制。

五、锁

1. 全局锁

定义：对整个数据库实例加锁。

方法：Flush tables with read lock (FTWRL)

典型使用场景：做全库逻辑备份。

官方自带的逻辑备份工具： mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

不支持事务的引擎，只能使用 FTWRL 。

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

原因有二：

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议使用。
二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

2. 表级锁

有两种表锁：普通表锁和元数据锁（meta data lock，MDL)。

语法： lock tables t1 read, t2 write; MDL 不需要显式使用，在访问一个表的时候会被自动加上。

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

如何安全地给小表加字段？

在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

ALTER TABLE tbl_name NOWAIT add column ...

ALTER TABLE tbl_name WAIT N add column ...