MySQL存储引擎、数据库锁、事务

最新推荐文章于 2024-10-17 21:19:58 发布

tjjloveworld

最新推荐文章于 2024-10-17 21:19:58 发布

阅读量188

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43223463/article/details/108507411

版权

MySQL存储引擎、数据库锁、事务

存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎

InnoDB（B+树）

是MySQL默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎
实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+间隙锁(Next-Key Locking)防止幻影读
主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升
内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等
支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取
InnoDB底层存储结构为B+树，B树的每个节点对应innodb的一个page，page大小是固定的，一般设为16k。其中非叶子节点只有键值，叶子节点包含完成数据。

适用场景：
1）经常更新的表，适合处理多重并发的更新请求
2）支持事务
3）可以从灾难中恢复（通过bin-log日志等）
4）外键约束。只有他支持外键
5）支持自动增加列属性auto_increment

MyISAM

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些
ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。在设计之初就预想数据组织成有固定长度的记录，按顺序存储的。ISAM是一种静态索引结构
缺点是它不支持事务处理
设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它
提供了大量的特性，包括压缩表、空间数据索引等
不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)
可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的
如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作

比较

事务：InnoDB是事务型的，可以使用Commit和Rollback语句
并发：MyISAM只支持表级锁，而InnoDB还支持行级锁
外键：InnoDB支持外键
备份：InnoDB支持在线热备份
崩溃恢复：MyISAM崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢
其它特性：MyISAM 支持压缩表和空间数据索引

TokuDB（Fractal Tree-节点带数据）

TokuDB底层存储结构为Fractal Tree，Fractal Tree的结构与B+树有些类似，在Fractal Tree中，每一个child指针除了需要指向一个child节点外，还会带有一个Message Buffer，这个Message Buffer是一个FIFO的队列，用来缓存更新操作
例如，一次插入操作只需要落在某节点的Message Buffer就可以马上返回了，并不需要搜索到叶子节点。这些缓存的更新会在查询时或后台异步合并应用到对应的节点中
TokuDB在线添加索引，不影响读写操作，非常快的写入性能，Fractal-tree在事务实现上有优势。他主要适用于访问频率不高的数据或历史数据归档

Memory

Memory(也叫HEAP)堆内存：使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。 Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多

数据库锁

封锁粒度

MySQL中提供了两种封锁粒度：行级锁以及表级锁
应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高
但是加锁需要消耗资源，锁的各种操作(包括获取锁、释放锁、以及检查锁状态)都会增加系统开销。因此封锁粒度越小，系统开销就越大
在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡

封锁类型

1. 读写锁

排它锁(Exclusive)，简写为X锁，又称写锁
共享锁(Shared)，简写为S锁，又称读锁

有以下两个规定：

一个事务对数据对象A加了X锁，就可以对A进行读取和更新。加锁期间其它事务不能对A加任何锁
一个事务对数据对象A加了S锁，可以对A进行读取操作，但是不能进行更新操作。加锁期间其它事务能对A加S锁，但是不能加X锁

锁的兼容关系如下：

-	X	S
X	×	×
S	×	√

2. 意向锁

使用意向锁(Intention Locks)可以更容易地支持多粒度封锁
在存在行级锁和表级锁的情况下，事务T想要对表A加X锁，就需要先检测是否有其它事务对表A或者表A中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的
意向锁在原来的X/S锁之上引入了IX/IS，IX/IS都是表锁，用来表示一个事务想要在表中的某个数据行上加X锁或S锁。有以下两个规定：
一个事务在获得某个数据行对象的S锁之前，必须先获得表的IS锁或者更强的锁
一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁
通过引入意向锁，事务T想要对表A加X锁，只需要先检测是否有其它事务对表A加了X/IX/S/IS锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务T加X锁失败

各种锁的兼容关系如下：

-	X	IX	S	IS
X	×	×	×	×
IX	×	√	×	√
S	×	×	√	√
IS	×	√	√	√

解释如下：

任意IS/IX锁之间都是兼容的，因为它们只是表示想要对表加锁，而不是真正加锁
S锁只与S锁和IS锁兼容，也就是说事务T想要对数据行加S锁，其它事务可以已经获得对表或者表中的行的S锁

封锁协议

1. 三级封锁协议

一级封锁协议

事务T要修改数据A时必须加X锁，直到T结束才释放锁
可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖

T₁	T₂
lock-x(A)
read A=20
	lock-x(A)
	wait
write A=19	.
commit	.
unlock-x(A)	.
	obtain
	read A=19
	write A=21
	commit
	unlock-x(A)

二级封锁协议

在一级的基础上，要求读取数据A时必须加S锁，读取完马上释放S锁
可以解决读脏数据问题，因为如果一个事务在对数据A进行修改，根据1级封锁协议，会加X锁，那么就不能再加S锁了，也就是不会读入数据

T₁	T₂
lock-x(A)
read A=20
write A=19
	lock-s(A)
	wait
rollback	.
A=20	.
unlock-x(A)	.
	obtain
	read A=20
	unlock-s(A)
	commit

三级封锁协议

在二级的基础上，要求读取数据A时必须加S锁，直到事务结束了才能释放S锁
可以解决不可重复读的问题，因为读A时，其它事务不能对A加X锁，从而避免了在读的期间数据发生改变

T₁	T₂
lock-s(A)
read A=20
	lock-x(A)
	wait
read A=20	.
commit	.
unlock-s(A)	.
	obtain
	read A=20
	write A=19
	commit
	unlock-X(A)

2. 两段锁协议

加锁和解锁分为两个阶段进行
可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度

lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但是它还是可串行化调度

lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

MySQL隐式与显示锁定

MySQL的InnoDB存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定

InnoDB 也可以使用特定的语句进行显示锁定：

SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;

行级锁

行级锁是一种排他锁，防止其他事务修改此行；在使用以下语句时，Oracle会自动应用行级锁：

INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新
使用COMMIT或ROLLBACK语句释放锁

表级锁

表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行。事务是一个不可分割的工作逻辑单元
事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚

事务必须具备以下四个属性，简称ACID属性：