mysql 思维导图模板_ProcessOn思维导图、流程图

如何查询

mysql架构图

子主题

架构中模块详解

客户端

连接器

语句：mysql -h$ip -P$port -u$user -p

建立连接、获取权限、维持和管理连接

一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限

建立连接的过程通常是比较复杂的

MySQL 在执行过程中临时使用的内存是管理在连接对象里面的,长连接会导致内存占用过大，会被系统强行杀掉（OOM）

定期断开

mysql 5.7后执行mysql_reset_connection清除内存

查询缓存

执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中

表更新会导致查询缓存清空

设置query_cache_type=DEMAND关闭查询缓存，用SQL_CACHE显示使用查询缓存

mysql8.0已经删除查询缓存

分析器

词法分析

识别出里面的字符串分别是什么，代表什么

语法分析

语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法

优化器

表里面有多个索引的时候，决定使用哪个索引

在一个语句有多表关联（join）的时候，决定各个表的连接顺序

执行器

先判断一下你对被操作表有没有执行查询的权限

执行流程

没有索引

调用引擎接口取表中第一行，判断是否符合条件，不符合跳过，符合存入结果集

调取下一行数据，判断，直到取到最后一行

将结果集返回客户端

有索引

取满足条件的第一行

循环取下一行满足条件数据

将结果集返回客户端

存储引擎

插件式，5.5.5版本后默认InnoDB

如何更新

WAL

(write-Ahead Logging):先写日志，再写磁盘

更新流程图

流程

执行器找到这一行，如果在内存中就返回，不在内存中，就先从磁盘读取存入内存，在返回给执行器

执行器更新数据，再将结果返回给引擎接口

引擎将数据更新到内存中，将操作记录到redo log中，此时redo log处于prepare状态，然后告知执行器完成，随时可以提交事务

执行器生成这个操作的binlog,并将binlog写入到磁盘中

执行器调用引擎提交事务接口，引擎将redo log的prepare 状态变为 commit

redo log

重做日志

有固定大小，循环写

write pos 是当前记录的位置，一边写一边后移

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘

crash-safe

有了redo log, InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失

binlog

归档日志

和redo log对比

redo log是InnoDB特有，bin log所有存储引擎都能用

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

redo log 循环写，binlog追加写

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘

binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

两阶段提交

目的是为了让两份日志之间的逻辑一致

事务隔离

事务定义

事务就是要保证一组数据库操作，要么全部成功，要么全部失败；事务支持是在引擎层实现

事务特点

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

隔离级别

读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到

读提交（read committed）：一个事务提交之后，它做的变更才会被其他事务看到

可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

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

多版本并发控制（MVCC）

同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）

实现方式（举例可重复读）

示意图

每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。即上述多版本并发控制

当没有事务再需要用到这些回滚日志时，回滚日志会被删除；当系统里没有比这个回滚日志更早的 read-view 的时候

长事务

缺点

事务提交之前，库中可能利用到的回滚记录都必须保留，会导致占用大量存储空间

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小

长事务还占用锁资源，也可能拖垮整个库

事务启动方式

显示启动 begin 或 start transaction 提交：commit 回滚：rollback

set autocommit=0

将线程自动提交关掉

只要线程开始执行命令，事务就开始，且不会关闭

关闭操作：主动执行commit或者rollback或者断开连接

如果是长连接，就导致了意外的长事务。

如何避免

set autocommit=1

begin 显式启动的事务

执行 commit work and chain，则是提交事务并自动启动下一个事务

程序开发的角度明确地知道每个语句是否处于事务中

业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间

确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。

监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill

Percona 的 pt-kill 这个工具不错，推荐使用

在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题

索引

目的

索引的出现其实就是为了提高数据查询的效率

常见模型

哈希表

一种以键 - 值（key-value）存储数据的结构

多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表

优点

新增时速度很快

哈希表这种结构适用于只有等值查询的场景

缺点

不是有序的，所以哈希索引做区间查询的速度是很慢的

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

有序数组

优点

有序数组在等值查询和范围查询场景中的性能就都非常优秀

缺点

插入数据成本很高

有序数组索引只适用于静态存储引擎

搜索树

二叉树示意图

二叉树的特点：每个节点的左儿子小于父节点，父节点又小于右儿子；查询和搜索时间复杂度都是O(log(N))；不适用数据库设计中，因为数据要落磁盘，二叉树的数据块太多，磁盘访问次数多，累计时间长

N叉树

读写性能和适配磁盘访问模式使其成为广泛使用的数据库引擎设计中

举例

InnoDB索引模型

B+树

索引类型

主键索引（聚簇索引）

主键索引的叶子节点存的是整行数据

非逐渐索引（二级索引）

非主键索引的叶子节点内容是主键的值

区别

索引维护

示意图

如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录

如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置

如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂；原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

是否需要自增主键

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

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

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，业务逻辑字段可能字节长，二级索引占用更多空建

适用业务字段做为主键场景（KV 场景）

只有一个索引

该索引必须是唯一索引

重建索引

重建非主键索引 alter table T drop index(k); alter table add index(k)

对于主键索引，不论删除还是创建，都会将整个表重建，一般使用 alter table T engine=InnoDB

子主题

覆盖索引

解决场景

一张表索引结构

查询语句：select * from T where k between 3 and 5

查询流程

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

查询值已经在索引树上了就不需要回表，索引已经覆盖了我们的查询需求，称之为覆盖索引

创建联合索引就可以利用到覆盖索引提高查询速度

最左前缀原则

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

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

如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。这时候，我们要考虑的原则就是空间了

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

锁

全局锁

概念

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)

影响

阻塞线程，包含数据更新语句（数据的增删改DML）、数据定义语句（包括建表、修改表结构等DDL）和更新类事务的提交语句

全库逻辑备份方法

全局锁

影响

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆

如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

mysqldump 

参数 –single-transaction

导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

需要存储引擎支持可重复读隔离级别

与set global readonly=true区别

在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用

在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高

表级锁

表锁

lock tables … read/write; unlock tables 主动释放锁，也可以在客户端断开的时候自动释放

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

元数据锁（meta data lock MDL）

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。MDL等到事务提交才会释放

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

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性

example 给小表加字段导致整个库挂了

示意图

session A 先启动，这时候会对表 t 加一个 MDL 读锁

session B 需要的也是 MDL 读锁，因此可以正常执行

session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放

之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满

如何安全给小表加字段

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁

在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务

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

行锁

两阶段锁

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

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

死锁和死锁检测

发生死锁示意图

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

发生死锁后策略

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置

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

策略优劣

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，对于在线服务来说，这个等待时间往往是无法接受的。

超时时间设置太短的话，会出现很多误伤。

主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的，检测死锁耗费大量CPU

如何解决热点行更新导致性能问题

一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。

另一个思路是控制并发度，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

通过将一行改成逻辑上的多行来减少锁冲突

再讲事务隔离

MVCC原理

概念

两个“视图”概念

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样

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

可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

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

行状态变更示意图

每行数据有多个版本，数据更新版本计为row trx_id,和事务transaction id 相等。旧版本通过redo log获取。参考行状态变更示意图，虚线箭头时undo log; 除了当前版本，其他版本都是计算出来。

秒级创建快照

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

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。该数组可能不是连续的，有些事务已经提交了。

读取数据时，需对比row trx_id和上述数组

row trx_id >= 高水位说明这个版本是将来的事务完成的，不可见，根据redo log,计算前一组视图

row trx_id < 低水位说明这个版本是已提交事务，可见

数值在高低水位之间

若数值在其中，说明事务未提交，不可见

数值不在其中，说明事务已提交，可见

一个例子

执行流程

快照创建时间点

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动

第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；

如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令

第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。

结果

事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1

知识点

事务B读取到C更新数据是因为：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）

若要事务A也能读取到结果3，需要讲select加锁，当前读

select k from t where id=1 lock in share mode; S锁，共享锁

select k from t where id=1 for update; X锁，排他锁

如果事务C添加事务，那么事务B就要等待C释放锁

示意图

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；

对于读提交，查询只承认在语句启动前就已经提交完成的数据；

普通索引和唯一索引

查询对比

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索

InnoDB 的数据是按数据页为单位来读写的，要多做的那一次“查找和判断下一条记录”的操作，几乎没有影响

change buffer

用于缓存更新操作,除了内存中有拷贝，也会被写入到磁盘中

change buffer中操作应用于原数据页过程称为merge

访问数据页触发merge

后台定期merge

数据库正常关闭也会执行merge

减少读磁盘，避免占内存;更新性能的提升是会很明显的

change buffer 用的是 buffer pool 里的内存;可以通过参数 innodb_change_buffer_max_size 来动态设置

应用场景

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好

假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价

更新对比

对于唯一索引，必须先把数据读到内存中，判断是否唯一，然后更新内存

普通索引，如果在内存中，直接更新内存，如果不在，则记录在change buffer

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

总结

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

change buffer 和redo log对比

在更新数据时，写入change buffer更改也会被写道redo log中

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写，写磁盘变成写log），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。(读数据到内存中的操作)

分支主题