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

日志

redo log

WAL机制，当对内存数据页进行修改后，写完redo log就可以直接返回

将随机写优化为顺序写，提高修改效率

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

redo log为环状的，保证了最大使用空间

建议使用4个1G文件作为redo log

使用两阶段提交保证redo log和bin log的一致性

在客户端执行commit命令的时候，会先写redo log为prepare状态，再写bin log，最后将redo log置为commit状态

崩溃恢复的时候，redo log为prepare状态，只要bin log存在就将其置为commit状态，然后将redo log中的改动应用到内存

bin log中会有xid，用来连接bin log和redo log对用的内容

innodb_flush_at_trx_commit参数控制刷磁盘

0 表示每次只刷到os_cache中就不管了

1 表示每次提交事务都需要flush os_cache

2 表示每次提交事务刷到os_cache上，每个1s触发一次flush

0基本不使用，2有可能会丢失1s的事务，1可以保证数据的完整性，保证提交后的事务不会丢失

使用组提交优化flush次数，将redo log的flush放到写完bin 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回退版本时使用

buffer pool

sort buffer

join buffer

redo buffer

undo buffer

change buffer

在不影响数据一致性的前提下，非唯一索引（唯一索引需要读出数据判断是否违反唯一约束）

当要修改的磁盘页不在内存中时，innodb会先将数据页改动记录到change buffer中，并记入redo log中（所以崩溃后可以依靠redo log进行恢复）

当下次查询的时候，再将磁盘中的数据读入内存，并应用change buffer的修改

通过减少随机读，提高增删改的效率（正常改的时候需要先将数据页读入内存，修改后再写入磁盘）

刷脏页

1. buffer pool满了之后，有新的数据页需要读入内存，会触发刷脏页

2. redo log写指针追到了头指针，会触发刷脏页

3. 在系统空闲的时候，定时任务刷脏页

4. 在正常关闭数据库的时候，会将脏页刷到磁盘

后两种情况不影响查询效率，尽量减少前两种刷脏页情况

适当调大redo log大小

适当调节磁盘io能力告诉mysql，innodb_io_capacity

淘汰策略

改进版LRU

将buffer pool分为3/8和5/8，分别为old和new

新加入内存的数据页先进入old，超过1s再访问，再进入new，old和new也都使用LRU进行淘汰

高可用

结构

主从

双主

冷备

延迟备份

防止误操作，可以设置为延迟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可以看到优化后的语句

基本架构

查询缓存

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

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

基本流程

select

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

2.分析器对语法分析

3.优化器根据语法树进行优化

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

update

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

order

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

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

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

数据量小的情况下，直接用sort buffer进行排序即可

数据量大的情况下，需要用到临时文件，进行归并排序

如果有limit，并且数量比较小，mysql会采用堆排序，取前几个直接返回

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

group

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

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

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

count

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

count(*)

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

count(1)

不取对应的数据行，在每一行返回1，肯定不为null，按行累加，性能比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大小优化速度

索引

数据结构

为什么不用平衡二叉树

太高了

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

太小了

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

B树

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

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

innodb使用B+树

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

与mysiam索引区别

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

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

常见规则

建立索引规则

每个表不超过5个索引

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

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

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

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

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

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

建立联合索引时

经常用的优先

区分度高的优先

长度短的优先

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

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

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

字符串上建立索引

可以使用前缀索引，减小索引使用空间

可以考虑倒序建立索引，比如身份证号

可以考虑使用hash索引，建立单独一列crc32，快速定位

使用索引规则

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

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

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

隐式转换

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

字符集转换

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

覆盖索引优化

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

索引下推优化

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

事务

隔离级别

读未提交

读已提交

可重复读

串行化

锁

锁是加在索引上的

分为共享读锁和排他写锁

读提交模式

只锁定访问到的行，并在语句执行结束，会释放不需要的行

可重复读模式

加锁基本单位是next-key lock

只在访问到的索引上加锁

当使用唯一索引时，等值查询会将next-key lock退化为record lock

范围查询时，当最后访问到的值与右边界不相等时，退化为gap lock

当使用唯一索引时，范围查询需要查到不满足条件的第一个值为止

record写锁之间会相互冲突，但是gap锁之间不冲突，容易造成死锁

死锁

尽量避免长事务

尽量使用同一顺序访问需要加锁的资源

尽量一次申请所有需要加锁资源

如果业务允许，可以降低隔离级别减少死锁

mysql会自动检测死锁，回滚持有资源少的那个

MVCC

多版本并发控制

在写的同时，允许读操作，提高并发度

read view

当前未提交事务id列表

未提交事务中最小的事务id

下一个未使用的事务id

生成时机

读已提交

每次快照读前生成

可重复读

在开启事务的第一个读写语句生成

实现原理

在每一行有隐藏列，当前行的事务id，上一个版本指针

旧版本存在undo log中，当行事务id小于当前所有read view中的最小事务id时，说明可以删除了

读取每一行的时候，判断当前行的事务id

小于read view最小事务id时，正常返回

大于read view下一个未使用的事务id时，说明事务在生成快照后生成，利用版本指针回滚

大于read view最小事务id，如果在未提交事务列表则回滚，不在则正常返回