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

基础架构

service

连接器

管理连接，权限验证

查询缓存

查询缓存以key-value的形式存在内存中,key是查询语句

查询缓存失效频繁，只要有对表的更新，则该表所有的缓存都会失效

mysql提供按需使用方式,通过设置query_cache_type为DEMAND并在查询时显式指定使用缓存select SQL_CACHE * from T where ID=10

MySQL 8.0 版本直接将查询缓存的整块功能删掉了

分析器

词法分析，语法分析

优化器

执行计划生成，返回结果

执行器

操作引擎，返回结果

执行前会执行权限验证

引擎

存储数据，提供读写接口

日志

redolog

innodb引擎

保持持久化

WAL(write ahead logging)

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了

文件大小固定,循环写,写满后会覆盖

crash safe 保证及时数据库异常重启，之前提交的记录不会丢失

物理日志，记录在某数据页上作了什么修改

innodb_flush_log_at_trx_commit 设置为1时，每次事务的redo log都直接持久化到磁盘

binlog

sever层

逻辑日志，记录语句的原始逻辑

大小不固定，可追加写入，写到一定大小后切换文件

sync_binlog设置为1时，每次事务的binglog都持久化到磁盘

主从复制时，slave去拉取master的binlog在本地执行；伪装成slave，来接收binlog，然后解析数据，做业务处理，比如监听数据改动，做业务监控，缓存清理等。

三种格式

statement

基于SQL语句

row

基于内容

mix

混合

更新一行数据的流程

执行器通过引擎获取要执行的行

执行器拿到这一行数据，对数据更新，再调用引擎接口写入这行数据

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

执行器生成这个操作的binglog，并把binlog写入磁盘

执行器调用引擎的提交事务接口，引擎吧刚写入的redo log改成提交状态，更新完成

undolog

innodb引擎

主要目的是记录一个事物的改动，这样如果这个事物rollback的话，可以根据undo log来恢复到上一个状态，实现事物的一致性

实现MVCC

事务

隔离性ACID

脏读

不可重复读

幻读

使用当前读时，数据不一致

隔离级别

读未提交

读已提交(oracle默认)

oracle修改成mysql，注意修改事务隔离级别，以便保持一致

可重复读（mysql默认）（通过间隙锁防止幻读）

快照读针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

mysql的innodb比较特殊，RR即解决了幻读问题，主要通过GAP锁实现

串行化（从mvcc 退化为当前读）

从MVCC并发控制退化为基于锁的并发控制。部分快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。 Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

show variables like 'transaction_isolation'

事务隔离的实现

MVCC（多版本并发控制）

consistent read view 一致性读视图

用于支持RC与RR级别

row trx_id

undo log回滚日志

可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图

读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图

undolog

事务的开启

start transaction并不会立即开启事务，会在头一条语句执行时才开启事务

start transaction with consistent snapshot可以立即开启事务

长连接

应避免使用

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

set autocommit=0，这个命令会将这个线程的自动提交关掉

锁

一致性锁定读（当前读）

隐式锁

insert , update delete （隐式x锁）

显式锁

select * from ?where? lock in share mode（显示s锁）

select * from ?where? for updaa(显示x锁

锁的类型

共享锁（S）

允许多个事务去读一行，阻止其他事务获得相同数据集的排他锁

排它锁（x）

允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

意向共享锁（IS）

意向排他锁（IX）

查看最近一次死锁信息

SHOW ENGINE INNODB STATUS;

对于mysql而言，事务机制更多是靠底层的存储引擎来实现，因此，mysql层面只有表锁，而支持事务的innodb存储引擎则实现了行锁(记录锁)，gap锁，next-key锁

索引

一种有序的数据结构

按照物理结构

聚集索引

主键索引

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

若没有主动定义主键，则第一个唯一索引会被作为主键。若没有唯一索引，会自动添加一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列

索引中的数据物理存放地址和索引的顺序是一致的

一个表只能包含一个聚集索引

非聚集索引

数据和索引分开单独存放在不同的文件中，索引文件不存储数据，而是存储数据的地址

数据库表中记录的物理顺序与索引顺序可以不相同。一个表中可以包含多个非聚集索引

普通索引

唯一索引

全文索引

仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。

组合索引

为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

非主键索引

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

回表

基于非主键索引查询时需要先查找到主键值，再到主键索引去查找记录

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

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

数据结构

Btree索引

以B/B+树的数据结构存储，应用范围广

Hash索引

mysql只有MEMORY存储引擎显示支持哈希索引。

通过Hash算法计算索引位置，效率高，一次定位。

哈希表结构在等值查询场景比较快

但需要解决Hash冲突。不支持范围检索

innodb为什么选择B+树

哈希表，不支持范围查询

有序数组

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

更新插入成本太高

红黑树/二叉搜索树

树太高，增加io次数

子主题

b树

每个节点都存储数据

比b+树高，会增加io次数

B+的叶子节点中有双向链表关联，范围查找更快

b+树

只有叶子节点存储数据

每个叶子节点之间通过指针关联

提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率

聚集索引叶子节点存储整行数据

非聚集索引叶子节点存储主键值

节点的大小为1页（16k）倍数最好

Mysql的基本存储结构是页

各个数据页可以组成一个双向链表

而每个数据页中的记录又可以组成一个单向链表

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

跳表、LSM 树（todo）

索引优化

为什么使用自增主键

记录按顺序写入，减少b+数的分裂过程

int/bigint类型最大8字节，好过很多字符串类型做主键，主键长度短，可以节省非主键索引空间

在只有一个索引，且该索引唯一的情况下，也可使用该key值作索引，减少回表次数

覆盖索引

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

联合索引

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

最左前缀原则

索引项是按照索引定义里面出现的字段顺序排序

索引下推

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

避免回表

基于非主键索引查询时需要先查找到主键值，再到主键索引去查找记录

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

错选索引

优化器选择索引的依据

扫描行数

analyze table t 重新统计

强制使用索引 select * from t force index(a)

引擎

myisam

表锁

不支持外键

不支持事务

支持全文索引；innodb不支持但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

是非聚集索引，innodb是聚集索引

innodb支持crash-safe

innodb

关键特性

插入缓冲

插入的时候会先到缓冲池查找，如果有，则加入；如果不在，先放入insert buffer对象中，然后以一定频率进行insert buffer和辅助索引页叶子结点的合并操作

条件

非聚集索引

非唯一索引

非聚集索引的插入与更新，

why

非聚集索引的插入无序，需要离散访问，插入性能变低

双写

目的

解决部分写失败问题

原理

1.缓冲池刷脏页的时候，先写入到内存的double write buffer

2. 在写入到磁盘的共享表空间（2M），每次写入1Mb

3. 同步到本地磁盘