MySQL8学习脑图思维导图模板_ProcessOn思维导图、流程图

SQL执行过程

总体流程

客户端 -> 连接器 -> 分析器 -> 优化器 -> 执行器 -> 存储引擎

Update流程

1、执行器请求存储引擎读旧数据

2、存储引擎从 BufferPool 读取数据

3、若 BufferPool 不存在，则从数据库文件加载所在的页到缓存

4、存储引擎将数据存入 undo log 的版本链中，然后返回给执行器

4、执行器修改数据后提交给存储引擎，存储引擎存入 BufferPool

5、存储引擎向 redo log buffer 中写入数据，标记状态为 prepare

6、根据持久化策略，redo log buffer 将数据写入 redo log 文件

7、执行器将 Update操作写入 binlog

8、事务提交且写入binlog成功后，将 redo log 中的 prepare 状态变更为 commit

9、Buffer Pool 中的新数据所在的页写入 Double Write Buffer 文件

10、Buffer Pool 中的新数据所在的页写入 idb 数据库磁盘文件

内部二阶段提交

过程：先写redo log，状态为prepare -> 再写 binlog -> redolog 状态变更为 commit

意义：保证 redo log 和 binlog 的一致性

索引

作用

通过数据有序存储，减少检索数据时的查找次数，从而提高检索速度

结构：B+树

特征

1、从左到右，索引值单调递增

2、一个节点的大小为一页（InnoDB Page Size，默认 16KB）

2、非叶子节点冗余存储部分的索引值；叶子节点存储全量的索引值及数据

3、叶子节点之间采用指针相连

优势

1、非叶子节点只存储索引值，所以一个节点能存储大量元素，树的高度低，有效减少检索次数，效率高

2、叶子节点之间存在相邻指针，范围查询时可利用相邻指针提升检索效率

使用方式

增删改操作

1、节点内元素增加/删除/修改

2、节点分裂/合并

3、树增高/变矮

查询操作

1、节点树向下递归

2、节点内二分查找

3、相邻指针，快速移动

类型

按叶子节点存储元素划分

聚簇索引

索引值：主键

叶子节点数据：完整列

二级索引

索引值：非主键

叶子节点数据：主键

按索引值包含的列数划分

单列索引

联合索引

查询优化

SQL语句优化

充分利用最左前缀匹配

查询字段覆盖索引，规避回表

order by、group by 尽可能利用索引

in 查询条件不要包含太多元素

避免对索引列操作导致索引失效

统计记录数使用 count(*)

分页查询

根据查询条件生成只包含主键的派生表，再与原表通过主键关联

翻页场景：把上一页最后一条记录的自增主键，放到当前页的条件中

维护额外字段，将范围查询转化为等值查询

join 不超过三张表，更多的 join 放到应用程序中

索引优化

join 在大表的连接字段加索引

使用区分度大的字段或字段组合

长字符串使用前缀索引

where 和 order by 冲突，优先 where

1-2个联合索引涵盖大部分业务场景，若干辅助索引涵盖其余场景

其他优化

在应用程序中进行复杂逻辑操作，而不是SQL中

先开发主体业务，再创建索引

大字段分表存放

开启慢SQL监测

MySQL不擅长的交给其他组件

利用trace工具判断实际执行的优化路径

技术补充

ICP 索引下推

join算法

filesort

Explain执行计划

查看方式

EXPLAIN + 原SQL语句

包含字段

id

id越大越先执行，id相同排在前面的先执行

select type

simple

简单查询

primary

复杂查询的外层语句

subquery

复杂查询 where 之前的子句

derived

复杂查询 where 或 in 之后的派生表

table

查询的表

partitions

查询的分区

type

索引使用的类型

ALL

全表扫描

NULL

无需进入执行阶段

system

使用子查询作为派生表，且子查询必然返回一条记录

const

使用唯一索引进行等值查询

eq_ref

使用唯一索引进行等值关联查询

ref

使用非唯一索引查询，或唯一索引的前缀查询

range

使用索引进行范围查询

index

查询列覆盖索引

possible_keys

可能用到的索引

key

实际使用的索引

key_len

索引的长度

ref

与索引比较的值的类型

rows

存储引擎预估的返回记录数

filtered

实际返回行数 / rows

extra

额外信息

using index

查询列覆盖索引，且 where 条件是索引前导列的精确匹配

using where; using index

查询列覆盖索引，且 where 条件是索引前导列的一个范围，或索引的非前导列。

Null

查询列未覆盖索引，且 where 条件是索引前导列的精确匹配

using index condition

查询列未覆盖索引，且 where 条件是索引前导列的一个范围，使得 ICP 索引下推技术被使用

using where

查询列未覆盖索引，且 where 条件是索引的非前导列，或非索引列。无法利用索引有序性检索

using temporary

使用临时表。无法利用索引有序性检索

using filesort

使用文件排序。无法利用索引有序性检索

事务

ACID

原子性

一个事务内的操作要么都成功，要么都不成功

通过 undo log 实现

一致性

一个事务完成后，约束状态不变

通过其他三个特性实现

隔离性

并发事务之间相互隔离，互不干扰

通过锁、MVCC实现

持久性

事务一旦提交成功，状态永久生效

通过 redo log 实现

隔离级别

读未提交

概念

一个事务可以读到其他事务未提交的内容

存在问题

脏读、脏写

读已提交（RC）

概念

一个事务可以无法读到其他事务未提交的内容，可以读到其他事务已提交的内容

解决问题

脏读、脏写

存在问题

不可重复读、幻读

可重复读（RR）

概念

如果事务A开启时，事务B尚未提交，则A无法读到B后续提交的内容

解决问题

不可重复读、幻读

串行化

概念

一个事务的读操作会阻塞其他事务的写操作，一个事务的写操作会阻塞其他事务的读写操作

解决问题

以上所有并发读写问题

存在问题

并发度低

性能优化方法

降低隔离级别

缩减事务规模

准备工作放在事务外

远程调用尽量异步、设置超时时间

事务拆分

加锁操作放在事务的靠后位置

业务代码处理异常回滚，代替事务

锁

按性能分类

悲观锁

获得锁才能修改

乐观锁

修改时比较数据是否被其他事务修改，若是，则重新获取最新数据后重试

按操作类型分类

读锁（S）

写锁（X）

意向锁（I）

按操作粒度分类

表锁

行锁

间隙锁（GAP）

只在 RR 隔离级别生效

临键锁（Next Key）

定义

行锁与间隙锁的结合

作用

解决 RR 隔离级别下，当前读的幻读问题

锁相关问题排查

查看锁等待统计情况

show status like 'innodb_row_lock%';

查看事务列表

select * from information_schema.INNODB_TRX;

查看使用中的锁

select * from performance_schema.data_locks

查看正在等待的锁

select * from `performance_schema`.data_lock_waits

查看最近一次死锁

show engine innodb status;  然后找到deadlock

查看应用程序日志

锁的优化

数据修改时，利用索引检索数据

范围查找时，减少搜索范围

控制事务大小

降低事务级别

MVCC

作用

InnoDB引擎通过MVCC机制，实现事务隔离性

关键技术

undo 版本链

为数据库中的每条记录维护一个版本链

read view

包含数据

每个事务为自己维护 read view，包含所有未提交的事务id集合 + 当前最大的事务id

更新时机

RC隔离级别：每次查询时

RR隔离级别：事务开始时

可见性算法

快照读时，根据 read view 决定当前事务对哪个事务之前的修改可见，然后从版本链中找到最近可见的版本

补充内容

事务开始时机

事务内的第一次DML或select for update 才标志真正的事务开始，此时会分配一个唯一的、递增的事务ID

关键存储空间

Buffer Pool

作用

要被读取的页从磁盘加载到 Buffer Pool，事务提交时写回磁盘，从而提升读取速度

数据结构

利用 LRU 算法构造的链表，新加入元素从中间插入，被访问时移到最顶端，最底端的数据不断被淘汰

double write buffer

作用

在页被写回数据库文件前，先顺序写一份到 double write buffer 文件。如果写入数据库文件是发生页损坏，则可以从 double write buffer 中恢复

redo log

作用

记录数据所在页的物理更改，若事务提交后，在 Buffer Pool中的数据持久化到数据库文件前崩溃，则重启后可根据 redo log 文件恢复

数据结构

一些列固定大小文件组成的环形结构，由 write_pos 和 checkpoint 指针进行标记

持久化策略

数据不会直接写入 redo log 文件，而是先写入 redo log buffer 这个内存，而后根据持久化策略写到 redo log 文件

undo log

作用

存储了一条记录的各个版本。每个事务对该记录的变更都会在 undo log 的版本链中追加一条记录。

在MVCC算法中，根据事务的可见性决定快照读的读取版本

在事务回滚时，将从 undo log 中还原 Buffer Pool 中对应的数据

binlog

关键参数

MySQL8新特性

高可用