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

基本架构

查询缓存

相对一般业务来说弊大于利，每个更新操作都会将整个表的缓存清除

5.6之后默认关闭，8.0之后完全移除

基本流程

select

1.连接器收到客户端请求建立连接，并检查是否有执行权限

2.分析器对语法分析

3.优化器根据语法树进行优化, 选择索引

4.执行器根据优化器结果，调用引擎接口，获取第一条引擎认为符合条件的，执行器判断，继续取下一行

update

查询步骤同上，执行器拿到符合条件的行后，进行更新，调用引擎写接口

order

如果要求对当前使用的索引列进行排序，从引擎拿到的就是有序的

可以直接返回给客户端（一种优化手段：合理建立索引）

如果引擎返回到执行器的数据无序

排序类型

快速排序

1. 从表中获取满足WHERE条件的记录

2. 对于每条记录，将记录的主键+排序键(id, order_col)取出放入sort buffer

3. 如果sort buffer可以存放所有满足条件的(id, order_col)对，则进行排序；(快速排序算法)
否则sort buffer满后，进行排序并固化到临时文件中。(归并排序算法)

4. 循环执行上述过程，直到所有满足条件的记录全部参与排序

5. 扫描排好序的(id, order_col)对，并利用id去捞取SELECT需要返回的列(回表)

优化排序

不进行二次回表, 在排序时, 直接将所有需要返回的列加入sort_buffer

max_length_for_sort_data
只有当排序元组小于max_length_for_sort_data时，才能利用优化排序方式，否则只能用常规排序方式

优化队列排序

limit场景使用(堆排序算法)

可以适当调大sort buffer，优化排序性能

group

如果从引擎拿到的有序，遍历一遍结果即可得到结果

所以可建立适当索引进行优化

如果从引擎拿到的无序，需要用到临时表（小则内存表，大则磁盘表），将group by的列当作唯一键，
并对结果进行排序返回，如果不需要排序，可以order by null 免于排序直接返回

count

由于MVCC机制，同时存在的每个session可能返回不同的结果，所以不能像Myisam那样直接记录整表数量直接返回

count(*)

innodb对此进行了优化，不取值，肯定不为null，按行累加

优先使用len最小的索引累加

count(1)

会被自动转换为count(*)

count(主键)

需要取出主键列，肯定不为null，按行累加
但由于是聚族索引, 主键索引的一页数据包含主键+所有行字段, 普通索引的一页数据只包含普通索引字段+主键, 因此普通索引遍历速度远快于主键索引的遍历速度

count(字段)

会取出字段列进行非空比较

如果不允许为null，直接按行累加

如果允许为null，需要先判断是否为null，对非null行累加

性能

count(*) > count(1) > count(主键) > count(字段)

join

join字段有索引

Index Nested-Loop Join(NLJ)

从驱动表取出一行数据，直接利用匹配表的索引树匹配, 然后回表

Batched Key Access(BKA)

依赖于Multi Range Read(MRR)，回表的时候先做排序，通过将随机读，转换为尽量顺序读

对NLJ方案进行的优化，默认开启，从驱动表取出一批数据，放到join buffer，到匹配表进行MRR连接

join字段无索引

Block Nested-Loop Join(BNL)

从驱动表取出符合条件的行，放到join buffer，取出join列，到被驱动表进行全表匹配，如果buffer放不下，需要分多次进行（Block名字由来，分块）

优化

构建适当索引，尽量走BKA方案

如果为冷查询，并且可以过滤掉很多数据，建索引有点浪费，可以考虑使用临时表，将数据插入到临时表中，建立索引进行连接

原则

尽量选取小表作驱动表

可以适当调节join buffer大小优化速度

索引

优缺点

优点

1. 减少服务器对数据的大量扫描

2. 将随机IO转换为顺序IO

3. 避免服务器反复进行排序操作以及使用临时表

缺点

1. 虽然查询变快, 但是数据更新速度变慢

2. 过多的索引会生成过多的索引文件占用磁盘

数据结构

为什么不用平衡二叉树

太高了

树高太高了，取一次数据需要访问磁盘次数太多

太小了

硬盘一页为4k，平衡二叉树不能很好利用磁盘特性

B树

一个磁盘块可以放多个度，树高也降下来了，可以有效利用磁盘特性

数据和索引在同一个节点上

innodb使用B+树

相比B树，数据都存放在叶子节点上，一个磁盘块上可以存放更多的度，可以有效降低树高，减少磁盘访问次数

与mysiam索引区别

mysiam索引也使用B+树，但其叶子节点上存放数据的指针，辅助索引同样存放数据指针

innodb主键索引叶子节点存放具体的数据，辅助索引叶子节点存放对应的主键

常见规则

建立索引规则

每个表不超过5个索引

索引太多，会导致插入更新操作变慢

每个联合索引不超过5个字段

联合索引字段太多，区分度就不是很高了，反而会浪费空间

修改频繁的列不要建立索引

更新数据会更新索引，会导致数据库性能下降

区分度低的列不要建立索引

建立联合索引时

经常用的优先

区分度高的优先

长度短的优先

主键索引最好使用递增整数

整数：由于innodb辅助索引叶子节点存放的是主键，主键长度越小，辅助索引查找越快

递增：由于innodb主键索引上数据按主键递增排序，如果插入顺序不是递增，会导致页分裂，降低插入数据效率

使用索引规则

范围查询右边的列将不能使用索引

like的%在前面，不会使用索引（最左匹配原则）

查询的时候，对列的那一边使用函数，会导致不走索引
（mysql认为使用函数会改变索引顺序）

隐式转换

比如查询条件为字符串列=数值，a=1，mysql会将字符串
转换为数值进行比较，导致索引列使用了函数

字符集转换

比如a、b两列分别使用utf8和utf8mb4字符集，当查询条件是a=b
的时候，会将utf8强制转换为utf8mb4，导致索引列使用了函数

覆盖索引优化

当索引中包含了查询条件的所有列，将直接返回，不去回表

索引下推优化

当使用范围查询，对于索引中该列后面的列，mysql会优化直接使用索引判断是否符合条件

事务

事务是由存储引擎实现, mysql中innodb实现了事务机制, 其中事务隔离级别为可重复读RR

特性

1. 原子性

假如事务失败, 通过undo_log回滚

undo log属于逻辑日志，它记录的是sql执行相关的信息.

2. 持久性

由于数据库的的读写会先写入Buffer Pool, 然后定期刷新到磁盘, 从而提高效率.
当系统宕机会导致数据丢失, 这时可以使用redo_log来实现恢复

redo log采用的是WAL（Write-ahead logging，预写式日志），
所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求

3. 隔离性

保证事务执行尽可能不受其他事务影响；
InnoDB默认的隔离级别是RR，RR的实现主要基于
锁机制(保证只能同时存在一个写操作)、
数据的隐藏列(记录当前行数据的版本号、删除时间、指向undo log)、
undo log、
类next-key lock机制(范围查询时,会对查出来的记录进行版本号标记)

脏读

事务A修改了记录a且未提交,
事务B读取记录a时发现版本不对, 从undo_log中读取未修改前的值返回

不可重复读(数据变化)

事务A第一次读了记录a,
事务A执行期间, 事务B修改了记录a并完成提交,
事务A第二次读记录a时, 发现数据版本与第一次读不一致, 从undo_log中读取旧版本数据

幻读(数据增多)

事务A第一次范围查询, 查询出3个记录,并记录当前记录的版本号
事务A执行期间, 事务B新增了符合该范围查询的记录,并完成提交,
事务A,第二次查询时发现存在版本号与第一次的版本号不一致(next-key lock), 则利用undo_log回滚数据, 保证与第一次查询相同

4. 一致性

MVCC(多版本并发控制)

假设同一份数据，既有读事务访问，又有写事务操作，实际上，写事务会新建一个新的数据版本，而读事务访问的是旧的数据版本，直到写事务提交，读事务才会访问到这个新的数据版本

实现方式

数据记录的多个版本同时保存在数据库

使用undo_log动态构造(mysql的innodb使用该实现)

实现原理

在每一行有隐藏列，当前行的创建事务id，删除事务id, 上一个版本指针(undo_log, 同一条记录可能会存在多个版本, 呈链表结构)

读取每一行的时候

判断记录是否被修改

当前行的创建事务id > 当前事务id, 说明记录在事务开启前已被修改了, 需要从undo_log回滚

判断记录是否被删除

当前行的删除事务id < 当前事务id, 说明记录在事务开启前已被删除, 则过滤

日志

redo log

WAL机制(先写日志, 后改表)

redo log存放的是数据页的改动

bin log

格式

statement

直接记录执行的语句，有可能造成主从的数据不一致，比如delete limit 1，主从如果选择了不同的索引，将删除不同的数据

row

记录中有执行前的值和执行后的值，对于数据恢复提供了极大的便利，但是会急剧增大bin log大小，比如delete 10w行数据，就需要存10w语句

mixed

前两种模式的混合，对于mysql认为不会导致数据不一致的语句，使用statement，否则使用row格式

每个线程都有一个bin log buffer，因为事务是不能被打断的

使用sync_binlog参数控制flush

0表示每次只写到os_cache中就返回

1表示每次都执行flush

2-N表示集齐N个事务一起flush

使用组提交优化flush次数，将bin log的flush放到flush redo log之后

undo log

用来存放数据的历史版本，用于MVCC回退版本时使用

高可用

结构

主从

双主

冷备

延迟备份

防止误操作，可以设置为延迟1小时，这样可以保证1小时内发现的误操作及时修正

判活机制

select 1

缺点：当增删改查堵住的时候，select 1也会正常返回

select 表

缺点：当磁盘空间满的时候，select表可以正常返回

update 表

缺点：响应慢的情况，不容易检测到

开启innodb performance

缺点：开启会损失一部分性能

可以用update表+部分innodb performance

读写分离遇到问题

主从延迟导致刚写入的直接查从库查不到

直接查主库

sleep

先判断主从延迟，如果有延迟走主库

数据安全

crash safe

通过bin log和redo log

数据完整性

数据一致性

explain

id

id大的先执行，如果id一样，从上往下执行

select_type

SIMPLE等

table

涉及的表

type

system、const

可以转换为常数查询

eq_ref

主键或者唯一索引等值查询

ref

普通索引等值查询

range

范围查询

index

对索引进行全索引扫描

all

全表扫描

从上到下性能逐渐下降，最要求应该在range

possible_keys

可能用到的索引

key

实际用到的索引

key_len

索引的长度

ref

rows

预估读取的行数

extra

using where

using index

覆盖索引

using filesort

需要排序

...

扩展

explain后使用show warings可以看到优化后的语句

数据结构

hash算法

hash函数,散列分布

复杂度O(1)

精确查找快, 不支持范围查找

二叉树查找树

定义

1. 左子树所有节点的值均小于他的根节点的值

2. 右子树所有节点的值均大于他的根节点的值

3. 子树也符合以上规则

缺点

数据不平衡

平衡二叉树

定义

1. 二叉查找树的定义

2. 左右两个子树的高度差的绝对值不超过1(子高平衡)

缺点

几乎每次插入/删除节点都会影响二叉树的平衡

红黑树

定义

1. 每个结点要么是红的，要么是黑的

2. 根结点是黑的

3. 每个叶结点，即空结点（NIL）是黑的

4. 如果一个结点是红的，那么它的俩个儿子都是黑的

5. 对每个结点，从该结点到其子孙结点的所有路径上包含相同数目的黑结点(黑高平衡)

B树(平衡多路查找树)

m阶的B树定义

1. 根节点至少有2个节点

2. 每个中间节点都包含k-1个元素和k个孩子, 其中 m/2 <= k <= m

3. 每个叶子节点都包含k-1个元素, 其中 m/2 <= k <= m

4. 所有叶子节点在同一层上

5. 每个节点的元素从小到大排列

优缺点

横向扩展, 不会增加深度

特点

1. m阶B树节点最多有m个子树, m-1个元素

2. m阶B树节点最少有m/2个子树, m/2 - 1 个元素

3. 数据即存在叶子节点, 也存在中间节点

为了磁盘或其它存储设备而设计的一种多叉平衡查找树, 多用于做文件系统的索引

因为文件系统和数据库一般都是存在电脑硬盘上的，如果数据量太大的话不一定能一次性加载到内存中。
但是B树可以多路存储, 刚好可以对应数据存储的页.

B+树

m阶的B+树定义

1. 根节点至少有2个子女

2. 每个中间节点都至少包含ceil(m / 2)个孩子，最多有m个孩子

3. 每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m

4. 所有的叶子结点都位于同一层

5. 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划

特点

1. m阶B树最多有m个子树, m-1个元素

2. 每个中间节点至少包含ceil(m/2)个子节点

3. 每个叶子节点都有左右2个指针，指向左右的下一个数据数据，形成一个有序的双向链表

4. 只有叶子节点才会有data，其他都是主键索引

优缺点

横向扩展, 不会增加深度