首页  思维导图  详情

MySQL

2022-04-12 09:29:28   27  举报





AI智能生成

MySQL思维导图

MySQL

作者其他创作

大纲/内容

架构

连接器

管理连接，权限验证

查询缓存

之前执行过的语句会以kv的形式缓存在内存中

分析器

词法分析（解析SQL语句中的关键字），语法分析（判断SQL语句是否符合语法）

优化器

选择最优的查询路径（决定如何使用索引）

执行器

校验权限，调用存储引擎，返回结果

存储引擎

MyISAM

5.5.5以前的默认存储引擎

插入数据快

空间利用率高

不支持事务

InnoDB

5.5.5之后的默认存储引擎

支持事务、外键

支持崩溃修复能力和并发控制

Memory

所有数据都在内存中，速度快

数据安全性差

日志

undo log回滚日志

InnoDB引擎特有的

用于事务回滚和展示旧版本

逻辑日志，记录的是sql语句的相反逻辑，比如原本的SQL是“给id=2这一行的c字段加1”，那这里就是“给id=2这一行的c字段减1”，相当于将修改撤销

redo log重做日志

InnoDB引擎特有的

物理日志，记录的是“在某个数据页上做了什么修改”

记录redo log视为数据已经更新

循环写的，空间固定会用完，write pos是当前日志写入点，check point是擦除点，数据被更新到磁盘时擦出。
当write pos追上check point时，事务无法提交，需要等待check point推进

binlog归档日志

属于Server层

逻辑日志，记录的是sql语句的原始逻辑，比如“给id=2这一行的c字段加1”

用来做数据备份

索引

主流索引数据结构

哈希表

适用于只有等值查询场景，由于不是有序的，范围查询时需全表扫描

有序数组

可以使用二分查找，使用与查询场景

新增或删除效率低，需要移动后面所有的数据

二叉查找树

时间复杂度O(logN)

会有一边过长而退化成线性查找的问题

平衡二叉树

查找时于二叉查找树相同

增删改时，通过左旋右旋来维护树的平衡

缺点是每个节点都只能存储一条数据，并且如果数据量大，树的高度就会过高，导致磁盘IO次数变多，性能下降

B树

B树是线性数据结构和树的结合

B树通过多数据节点大大降低了树的高度，每个节点都能存储多个数据，使得查询的IO次数更少

每个节点都存储了索引和数据

每个节点里面的数据之间是链表结构

B+树

B+树所有数据均在叶子节点上，非叶子节点只是一个索引

B+树所有数据即叶子节点形成有序链表，便于范围查询

MySQL的InnoDB的索引模型就是B+树

索引类型

主键索引（聚簇索引）

以主键构建的一颗B+ 树，叶子节点的数据是一行完整的数据

非主键索引（二级索引）

以普通索引构建的一颗B+ 树，叶子节点的数据是主键，当通过普通索引查询到主键时，需要回表查询到该主键对应的数据

覆盖索引

通过查询非主键索引就可以获取到想要查询的数据，而不需要回表操作

可以减少树的搜索次数，显著提升查询性能

最左前缀原则

索引下推

在不使用索引下推的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件

在使用索引下推的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数

事务

特性

原子性（A）

事务是数据库的逻辑工作单位，事务中的操作要么都成功，要么都失败

一致性（C）

事务执行的结果必须是从一个一致性状态变到另一个一致性状态

隔离性（I）

数据库中的一个事务的执行不能被其它事务干扰，即一个事务内的操作及使用的数据对其它事务是隔离的

多个事务同时执行会出现的问题

脏写

事务B去修改了事务A修改过的值，但是此时事务A还没提交，这时事务A回滚，导致事务B的修改丢失了，在事务B看来我明明更改了值，但值却没变

脏读

事务B去查询事务A修改过的值，但是此时事务A还没提交，这时事务A回滚，此时事务B再去查询发现跟之前的数据不一样了
（事务B可以读到事务A修改过但还没提交的数据，此时事务A一旦回滚，事务B再次读就读不到了，这就是脏读）

不可重复读

事务A查询一个值，然后事务B更新了该值并提交之后，事务A再次查询发现值不一样了

幻读

事务A根据条件查询多个值，然后事务B插入或删除记录，导致事务A再次查询时发现结果个数不一样了
（一个事务用一样的SQL多次查询，结果每次查询都会发现查到了一些之前没看到的数据或少了一些数据）

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的，因此，幻读只在“当前读”下才会出现

会导致数据与日志不一致

隔离级别

读未提交（read uncommitted）

一个事务可以读到另一个事务修改后还未提交的数据（X1=2，X2=2，X3=2）

读提交（read committed）

一个事务只能读到另一个事务修改后提交的数据，还未提交的数据其它事务是看不到的（X1=1，X2=2，X3=2）

Oracle默认隔离级别

可重复读（repeatable read）

一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据是一致的，不管其它事务对数据如何修改。（X1=1，X2=1，X3=2）

MySQL默认隔离级别

串行化（serializable）

对于同一行数据，写会加写锁，读会加读锁，写写和读写时会导致锁冲突，后访问的事务会阻塞住

每种隔离级别可以解决的并发问题

MySQL的可重复读隔离级别也可以解决幻读问题，因为引入了间隙锁

实现

多版本并发控制MVCC

undo log多版本链

ReadView机制

当前读

会读取数据的当前版本，并加锁，为了并发控制

若当前版本已经被加锁且不兼容，则阻塞等待

X锁：update，delete，select for update

S锁：select in share mode

快照读

读数据的特定历史版本

不需要加行锁

MVCC到底能不能解决幻读？？

1.a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作）， 2.a事务再select出来的结果在MVCC下还和第一次select一样， 3.接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的）， 4.a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了

原因是前面的UPDATE语句执行之后，会将当前记录上存储的事务信息更新为当前的事务，而当前事务所做的任何更新，对本事务所有SELECT查询都变的可见，因此最后输出的结果是UPDATE执行后更新的所有记录

持久性（D）

事务一旦提交，则其对数据库中数据的改变就是永久的

锁

类型

全局锁

对整个数据库实例加锁，让整个库处于只读状态

使用场景：做全库逻辑备份

表级锁

表锁（数据锁）

元数据锁MDL

行锁

共享锁（S）

其它事务可以读，但不能写

排他锁（X）

其它事务不能读取，也不能写

间隙锁（gap锁）

可重复读隔离级别下才生效，用来解决幻读问题

与行锁合称 next-key lock

加锁时以next-key为基本单位，是前开后闭区间（间隙加下一个行记录）

查找过程中扫描过的范围才加锁

唯一索引等值查询，没有间隙锁，只加行锁

索引等值查询最右一个扫描到的不满足条件值不加行锁

索引覆盖且只加S锁时，不锁主键索引

死锁

互相等待对方的锁释放

问题

有一张表t，表里有两个字段，id和a，id是主键，现在里面有一条记录（1,1）。现在要执行update t set=1 where id=1;
那此时会有3种情况：
1、更新都是先读后写的，mysql读出数据，发现a的值原本就是1，不更新，直接返回，执行结束。
2、mysql调用InnoDB引擎提供的“修改为(1,1)”这个接口，但是引擎发现值与原来一样，不更新，直接返回。
3、InnoDB认真执行了“把值修改成(1,1)”这个操作，该加锁的加锁，该更新的更新。
实际情况是以上哪一种？

可以看到事务二在更新的时候阻塞住了，说明是第三种情况，事务一对该条记录加锁了，导致事务二无法获取锁阻塞了。

优化

order by

全字段排序

sort_buffer

mysql为排序开辟的内存

sort_buffer_size

内存大小

如果要排序的数据量小于该值，排序就在内存中完成，如果大于，就要用磁盘临时文件来辅助排序

将所需字段全部读取到sort_buffer中

rowid排序

max_length_for_sort_data