MySQL 思维笔记模板_ProcessOn思维导图、流程图

基础架构

1连接器

2查询缓存（before MySQL 8.0）

3分析器

4优化器

5执行器

日志系统

redo log（重做日志）（InnoDB引擎特有日志）

InnoDB的redo log是固定大小的

如图write pos 是当前记录的位置顺时针记录，check point 是当前要擦除的位置擦除之前需要把记录更新到数据文件。

绿色部分表示剩余的可记录空间大小，若是write pos追上checkpoint，这时候不再执行更新，等待checkponit推进。

有了redo log ，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。**MyISAM没有crash-safe功能**

binlog （归档日志）（MySQL server层特有日志）

两种日志的不同

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

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

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

update语句执行流程

update 语句执行流程

redo log “两阶段提交” prepare 和 commit

先写 redo log 后写 binlog ，先写redo log 崩溃后 会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，与原库的值不同。

先写 binlog 后写 redo log 如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，但是 binlog 里面已经记录这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的与原库的值不同。

redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

数据恢复 当需要恢复到指定的某一秒时，需要找回数据

首先，找到最近的一次全量备份，从这个备份恢复到临时库；

然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到误删表之前的那个时刻。

事务隔离 A（原子性）C（一致性）I（隔离性）D（持久性）

作用

保证一组数据库操作，要么全部成功，要么全部失败。

命令

查看事务：  show variables like 'transaction_isolation'

隔离级别于隔离性（I Isolation）

读未提交（read uncommitted）

一个事务还没提交时，它做的变更就能被别的事务看到。

读提交（read committed）

一个事务提交之后，它做的变更才会被其他事务看到。

可重复读（repeatable read）

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化（serializable）

串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

设置隔离级别

将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值。

工作原理

实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

实现

事务内每条记录在更新的时候记录回滚操作和最新的值，通过回滚，可以得到钱一个状态的值。

同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

当回滚日志中出现更早的 read-view 的时候，回滚日志会被删除。

事务的启动方式

命令

查询长（超过60s）事务 

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

set autocommit=1 ，显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。

set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。若set autocommit=1，则语句执行完毕后将自动提交。

多一次交互

autocommit 为1的情况下 begin 开启事物 commit 提交事物 commit work and chain 提交事物并开启下一个事物，减少begin的开销。

索引

常见的索引模型

哈希表

一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

缺点 

多个key可能会出现相同的hash值，即这个key的value有多个值这个数组的value数据结构是链表，是无序的。所以哈希索引做区间查询的速度是很慢的。

结论

由于缺点的限制，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

有序数组

有序数组用二分法可以快速定位。Tn=O(log(N))。

缺点 

顺序排列的数据修改的时候开销巨大。

结论

有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

搜索树（N叉树）

父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。

拓展跳表、LSM 树等数据结构也被用于引擎设计

InnoDB的索引模型

关于 InnoDB 的表结构： 1.在 InnoDB 中，每一张表其实就是多个（根据索引数量但不等于索引数量） B+ 树，即一个主键索引树和多个非主键索引树。 2.执行查询的效率，使用主键索引 > 使用非主键索引 > 不使用索引。 3.如果不使用索引进行查询，则从主索引 B+ 树的叶子节点进行遍历。

索引类型

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

聚簇索引（clustered index）  主键索引  叶子节点存的是整行数据。

基于主键查询方式，则只需要搜索主键索引这棵 B+ 树。

二级索引（secondary index） 非主键索引  叶子节点内容是主键的值。

普通索引查询方式，则需要先搜索二级索引树，得到主键的值，再到主键索引树搜索一次。这个过程称为回表。

索引维护

说明：

如果新插入的值的主键为当前表中的最大值，那么只需要在索引树中最后面插入一条数据；但若插入的值为中间值，就需要挪动这个值后面的数据，空出位置。

其次如果需要挪动的数据所在的数据页已经满了，根据B+树算法，这时需要申请一个新的数据页，用来放置需要挪动的数据，这个过程为页分裂。相反两个数据页由于数据删除导致利用率低于阈值，也会导致页合并。

覆盖索引

树搜索以及扫描流程

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；

InnoDB 的索引组织结构

1 在 k 索引树上找到 k=3 的记录，取得 ID = 300； 2 再到 ID 索引树查到 ID=300 对应的 R3； 3 在 k 索引树取下一个值 k=5，取得 ID=500； 4 再回到 ID 索引树查到 ID=500 对应的 R4； 5 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。 如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

最左前缀原则

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。 mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

联合索引先根据第一个字段排序，如果第一个字段有相同的，就按照第二个字段排序，注意，这里仅仅有相同的第一个字段情况下，才会根据第二个字段排序。

索引下推优化（index condition pushdown)

在“仅能利用最左前缀索的场景”下（而不是能利用全部联合索引），对不在最左前缀索引中的其他联合索引字段加以利用——在遍历索引时，就用这些其他字段进行过滤(where条件里的匹配)。过滤会减少遍历索引查出的主键条数，从而减少回表次数，提示整体性能。在Explain输出的Extra字段中会有“Using index condition”。即代表本次查询会利用到索引，且会利用到索引下推。

举例

市民表的联合索引（name, age） mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

无索引下推执行流程

索引下推执行流程

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

锁

全局锁

解释

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL) 加锁，unlock tables 解锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

使用场景

全库逻辑备份

方法1）库中有表使用了不支持事务的引擎，那么只能用FTWRL方法备份。

方法2）官方自带逻辑备份工具mysqldump ，使用–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。（只适用于所有的表都使用事务引擎的库）

方法3）（不推荐） set global readonly=true 让全库进入只读状态。 FTWRL在客户端异常断开之前会自动释放锁，而readonly不会。

缺点

如果主库 FTWRL，期间都不能执行更新，业务停摆。

如果从库 LTWRL，那么主库的binlog同步就会阻塞，导致主从延迟。

表级锁

表锁

语法命令 lock tables … read/write，加锁，unlock tables 解锁。

lock tables read 读锁加锁之后，其他线程无法对这个表写入。lock tables write 读写锁加锁之后，其他线程无法读写这个表。同时，本线程在未执行unlock tables之前，也无法访问其他表。（逻辑备注：我对这个表加了读锁，我就只能读这个表，因为读不会改变数据所以其他人也可以读；我对这个表加了write读写锁，那么我只能对这个表进行读写，其他人无法访问这张表。）

元数据锁（meta data lock，MDL）（after MySQL 5.5）

MDL元数据锁

不需要显示使用，在访问表时会自动加上

当对表进行增删改查DML的时候系统会自动加上MDL读锁

当对表结构信息修改DDL的时候系统自动加MDL写锁

读锁之间不互斥，可以多个线程对一张表同时DML。

读写锁，写锁之间互斥，多个线程不可同时对表DDL。

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

注意事项：

由于事务中的MDL锁是事务提交后才释放，所以如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

那如何安全的给小表加字段？

MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

但如果某个热点表一直都有长事务，比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...

MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

行锁（InnoDB支持）

行锁的实现是通过给索引上的索引项添加锁实现的，故只有当执行的脚本走索引时，innodb才会使用行锁，否则innodb只能使用元数据锁MDL。两种锁均无需显示添加。故此时id必须是主键（聚集索引）或创建了辅助索引。

两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

死锁和检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

策略

直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout（默认50s）来设置。

推荐发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

事务的隔离实现

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

事务启动的两种方式 

1 begin/start transaction 一致性视图是在执行第一个快照读语句时创建的；

2 一致性视图是在执行 start transaction with consistent snapshot 时创建的。

“视图”的两个概念

 1 view 创建视图的语法是 create view … ，它的查询方法与表一样。

2 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

“快照”与MVCC

可重复读的事务级别下，事务启动的时候给整库拍了个快照。

实现原理：

InnoDB引擎每个事务在开始的时候会向引擎事务系统申请一个都有一个专属transcation id，这个id是严格按照申请顺序递增的。

每次事务更新数据的时候，都会生成一个新的数据版本，这个版本的事务id会赋上transcation id，记为row trx_id，并保留下来供新的数据版本获取使用。（一个事务改了很多次会记录很多个row trx_id=该事务id的数据）

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

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

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

更新逻辑

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

除了 update 语句外，select 语句如果加锁，也是当前读。（先读后写的先读）

mysql> select k from t where id=1 lock in share mode;（lock in share mode 读锁（S锁共享锁）） mysql> select k from t where id=1 for update;（for update 写锁（X 锁，排他锁））

事务的可重复读能力是如何实现的？