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

隔离级别

RAED UNCOMMITED

使用查询语句不会加锁，可能会读到未提交的行（Dirty Read）

READ COMMITED

只对记录加记录锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近， 所以再多次使用查询语句时，可能得到不同的结果（Non-Repeatable Read）；

REPEATABLE READ

多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读（Phantom Read）；

通过行级锁+MVCC实现

SERIALIZABLE 串行化

实现

锁

加锁是加在索引上的

类型

共享锁

允许其他事务再加共享锁，不允许其他事务再加排他锁；

select…lock in share mode

排它锁

其它事务无法再对该数据加共享或者排他锁。

select…for update

种类

表级锁

是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少， 被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁分为表共享读锁与表独占写锁。

lock talbe name read

lock table name write

unlock table

行级锁

页级锁

GAP锁（间隙锁）

意向锁

死锁

事物A持久资源1的锁，事务B持有资源2的锁 事务A等待资源2的锁，事务B等待资源1的锁

insert加锁

意向排它锁+排它锁

时间戳

MVCC 多版本控制 （Multiversion Concurrency Control）

来源-根据源码佐证

https://blog.csdn.net/Waves___/article/details/105295060

最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞， MVCC只在 Read Committed 和 Repeatable Read两个隔离级别下工作

实现

隐藏字段

DB_TRX_ID

表示最近一次对本记录行作修改（insert | update）的事务ID。至于delete操作， InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted。并非真正删除。

DB_ROLL_PTR

回滚指针，指向当前记录行的undo log信息

DB_ROW_ID

随着新行插入而单调递增的行ID。理解：当表没有主键或唯一非空索引时， innodb就会使用这个行ID自动产生聚簇索引。这个DB_ROW_ID跟MVCC关系不大。

Read View

Read View主要是用来做可见性判断的, 里面保存了“对本事务不可见的其他活跃事务”。 包含如下几个变量

low_limit_id

目前出现过的最大的事务ID+1，即下一个将被分配的事务ID

up_limit_id

活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id

trx_ids

Read View创建时其他未提交的活跃事务ID列表，创建Read View时， 将当前未提交事务ID记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的

creator_trx_id

当前创建事务的ID，是一个递增的编号

Undo log

存储的是老版本数据，当一个事务需要读取记录行时，如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。

增删改查

select

读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的

insert

将当前事务的版本号保存至行的创建版本号

update

新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号

delete

将当前事务的版本号保存至行的删除版本号

常用

show engine innodb status; 查看innoDB状态，包含死锁日志

查看隔离级别

SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;

设置隔离级别

查看表状态

show table status like ‘plan_branch’\G; show table status from test like ‘plan_branch’\G;

查看锁概况

show status like 'innodb_row_lock%';

数据库版本

select version();

引擎

MyISAM

结构

tableName.frm

frame表结构

tableName.myd

myISAM data数据文件

tableName.myi

myISAM index索引文件

搜索

在index文件中查找到文件对应的指针地址，再去data文件取数据

查看正在锁和等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

InnoDB

结构

tableName.frm

frame结构文件

tableName.ibd

索引、数据集合文件

搜索

ibd本身就是b+树结构文件，data存放的就是完整数据列

ACID

Atomicity 原子性

事务一起成功或一起失败

回滚日志 undo log实现

Consistency 一致性

表示事务完成后，符合逻辑运算

Isolation 隔离性

排除其他事务对本事务的影响

Durability 持久性

事务结束后的数据不随着外界原因导致数据丢失

加锁实例分析

select * from t1 where id = 10 for update;加了几把锁

场景1：ID是主键索引

行锁，不需要gap锁

场景2：ID非主键，但属于唯一索引

两把锁

场景3：没有索引

全表加锁，并加gap锁

场景3：普通非唯一索引

id=10的行加锁，并加gap锁

所有gap锁场景都在RepeatableRead级别下

索引

概念

排好序的数据结构

结构

b+tree

hash

在=查找时很快，范围查找时很慢

二叉树

从左到右递增

缺点：单边增长

红黑树-二叉平衡树

二叉平衡树

左旋右旋

缺点：高度太高

插入

新节点除了根总为红色

B树-多叉平衡树

叶子节点具有相同的深度，叶节点的指针为空

节点元素从左到右递增

缺点

每个元素都是k-v结构，data挤占节点空间，造成高度过高

范围查询时因为data分布在所有节点，需要多次从根遍历

B+树-mysql使用

mysql默认节点大小-16KB

非叶子节点不存储data，只存储索引，会造成冗余索引

叶子节点用指针连接，提高区间访问性能（如范围查找）

叶子节点包含data和所有的元素

MyISAM中data是文件地址指针

InnoDB中data是完整数据列

分类

聚集索引

就是叶子节点包含完整的数据列

稀疏索引

如MyISAM，叶子节点只包含数据的指针

索引优化

全值匹配

用等于查询

最左前缀

从索引的最左前列开始并且不跳过索引中的列

不要在索引上做任何操作(运算、函数、类型转换等)

不能使用索引中范围条件右边的列

WHERE name= 'LiLei' AND age = 22 AND position ='manager';

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句

mysql在使用不等于（！=或者<>）的时候无法使用索引

.is null,is not null 也无法使用索引

ike以通配符开头（'$abc...'）mysql索引失效

少用or,用它连接时很多情况下索引会失效

explain

type列

MySQL决定如何查找表中的行

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

面试

为什么InnoDB表必须有主键，最好是整型自增的主键