MySQL事务基本概念和存储引擎
基本概念
1.事务
事务即一组原子性的数据库操作序列(SQL语句),这些操作要么全做要么全都不做。数据库引擎能成功地应用一组查询的全部语句,那么就执行该组查询,否则搜由于局都不会执行
事务的特性
- 原子性(atomicity):一个事务必须背视为一组不可分割的工作单元。整个事务中的操作要么全部成功提交(commit),要不全部失败回滚(rollback)。
- 一致性(consistency):数据库总是从一个一致性状态到另一个一致性状态。因此当数据库只包含成功事务提交的结果的时候,就说数据库处于一致性状态。一致性与原子性密切相关。
- 隔离性(isolation):一个事务的执行不能被其他事务干扰。通常来说,一个事物最终提交以前,对其他事务是不可见的。
- 持久性(durability):一旦事务提交,其所做的修改就会永久保存到数据库中。
事务的ACID特性可以保证数据的正确性与完整性,但在应用逻辑中要实现这一点还是非常难的,一个兼容ACID的数据库系统,需要做很多复杂但可能用户没有察觉到的工作,才能确保ACID的实现。而实现了ACID的数据库比没有实现ACID的数据库,通常会需要更强的CPU处理能力,更大的内存和更多的磁盘空间,所以Mysql需要根据实际需求选择合适的存储引擎。
隔离级别
- READ UNCOMMITED(未提交读): 一个事务中的修改,即使没有提交对其它事务也是可见的。事务可以读取未提交的数据,也被称为脏读。除非必要,一般实际需求中很少使用。
- READ COMMITED(提交读): 一个事务提交之前的数据对其他事务不可见。一个事务中多次执行同样的查询,可能得到不同结果,所以也叫不可重复读。这也是大多数数据库系统的默认隔离级别(mysql不是)。
- REPEATABLE READ(可重复读): 一个事务中多次读取相同记录的结果是一致的。可解决脏读的问题,无法解决幻读——指某个事务在读取某个范围的记录时,另一个事务又在该范围内插入了新记录,当之前的事务再次读取该范围中的记录时,会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC)解决了幻读问题。mysql默认的隔离级别。
- SERIALIZABLE(可串行化): 最高的隔离级别,它通过强制事务串行执行避免幻读问题。简单来说,它会在读取的每一行都加锁,所以可能导致大量超时和锁争用问题,实际应用中也很少用到这个隔离级别。只有非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑该级别
隔离级别 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 | 加锁读 |
---|---|---|---|---|
READ UNCOMMITED | yes | yes | yes | no |
READ COMMITED | no | yes | yes | no |
REPEATABLE READ | no | no | yes | no |
SERIALIZABLE | no | no | no | yes |
死锁
数据库中指两个或多个事务在同一资源上相互占用,并请求对方占用的资源,从而导致恶性循环的现象。
死锁实例
事务1:
start transaction;
update table1 set close=123 where id=4;
update table2 set close=456 where id=3;
commit;
事务2:
start transaction;
update table1 set close=456 where id=3;
update table2 set close=123 where id=4;
commit;
如果凑巧,两个事务都执行到了第一条update语句,更新了一行数据,同时也锁定了该行数据,接着两个事务都尝试去执行第二条update语句,却发现该行已经被对方锁定,然后两个事务都等待对方释放锁,同时又持有对方需要的锁,则陷入死循环,除非外部因素介入才可能解除死锁。
解决死锁的方案:
- 检测到死锁的循环依赖,并返回一个错误。
- 当查询时间到达锁等待时间的设定后放弃锁请求。这种方式可能会误判死锁。
- 将持有最少行级锁排他锁的事务进行回滚。InnoDB目前处理死锁的方式。
事务日志
每次修改表的数据时,只需要修改其内存的拷贝,而将该修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的的数据本身持久到硬盘上。
事务日志采用的是追加的方式,因此写日志的操作时磁盘上一小块区域的顺序IO,而不像随机IO需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说快得多。事务日志持久化后,内存中被修改的数据在后台可以慢慢地刷回到磁盘。 目前大多数存储引擎都是这样实现的,称为预写式日志,修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志并持久画,但数据本身还没有被写回磁盘,此时系统崩溃,存储引擎重启时能自动修复这部分数据,具体的恢复方式则视存储引擎而定。
2.多版本并发控制(MVCC)
MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑, 它们一般都同时实现了多版本并发控制(MVCC)。不仅MySQL, 包括Oracle、PostgreSQL 等其他数据库系统也都实现了 MVCC, 但各自的实现机制不尽相同, 因为MVCC 没有一个统一的实现标准。
可以认为 MVCC 是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低。县然实现机制有所不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。
MVCC 的实现,是通过保存数据在某个时间点的快照来实现的。也就是说, 不管需要执行多长时间,每个事务看到的数据都是一致的。根据事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。如果之前没有这方面的概念,这句话听起来就有点迷惑。熟悉了以后会发现,这句话其实还是很容易理解的。
InnoDB的MVCC,是通过在每行记录后加上两个隐藏的列来实现的。这两个列,一行保存了行的创建时间,一行保存行的过期时间(或删除时间),当然存储的并不是实际的时间值,而是系统版本号。每开始一个事务,系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号做比较。
REPEATABLE READ级别下的MVCC具体操作(配合解决幻读):
-
SELECT
InnoDB会根据以下两个条件检查每行记录:- InnoDB只查找早于(小于或等于)当前事务版本号的数据行。这样可以确保事务读取的行,要么是事务开始前已经存在的,要么是事务自身修改过的。
- 行的删除版本要么未定义,要么大于当前事务版本号。 这可以确保事务读取到的行在事务开始之前未删除。
INSERT
InnoDB为新插入的每一行保存当前的系统版本号作为行的版本号。DELETE
InnoDB为删除的每一行保存当前的系统版本号作为删除标识。UPDATE
InnoDB为插入的记录,保存当前的系统版本号作为行的版本号,同时保存当前的系统版本号到原来的行作为删除标识。
保存这两个额外系统版本号,使大多数操作都可以不加锁,也能保证读取到符合标准的行,避免幻读。但每行记录都需要额外的存储空间,需要做更多的行检查工作和维护工作。
MVCC只在REPEATABLE READ和READ COMMITED这两个级别下工作,与另外两个级别不兼容,因为READ UNCOMMITED总是读取最新的行,而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有的行都加锁。
存储引擎概要
除非万不得已,否则不要混合使用存储引擎。
InnoDB
MySQL默认存储引擎,被设计用来处理大量的短期事务,短期事务大部分情况是正常提交的,很少会被回滚。InnoDB的性能和自动崩溃回复特性,使它在非事务存储的需求中也很流行。除非有特别的原因需要使用其他存储引擎,否则应优先考虑使用InnoDB。
- InnoDB支持外键定义,支持行级锁。
- InnoDB数据存储在表空间中,表空间是InnoDB管理的黑盒子,由一系列数据文件组成。
- InnoDB使用MVCC来支持高并发,并且实现了四个标准的隔离级别。默认级别是REPEATABLE READ(可重复读),并且通过间隙锁策略防止幻读的产生。间隙锁使InnoDB不仅仅锁定查询设计的行,还会对索引中的间隙进行锁定,以防止幻影行的插入。
- InnoDB基于聚簇索引(B+树索引叶子节点存储的是实际数据,索引项顺序与表中记录的物理顺序相同)建立。
- 适用于频繁写入(insert,delete,update)的情况。
XtraDB
Percona XtraDB 是 InnoDB 存储引擎的增强版,被设计用来更好的使用更新计算机硬件系统的性能,同时还包含有一些在高性能环境下的新特性。
- MariaDB 10.2 又重新使用了InnoDB -> 传送门
- XtraDB 存储引擎是完全的向下兼容,在 MariaDB 中,XtraDB 存储引擎被标识为”ENGINE=InnoDB”,这个与 InnoDB 是一样的,可以直接用XtraDB 替换掉 InnoDB 而不会产生任何问题。
- XtraDB 在多核CPU下能更有效地使用内存。
- 提供了比InnoDB更多的配置和性能监控参数
MyISAM
MyISAM存储引擎表由MYD(存数据)和MYI(存索引)组成,不支持事务和行级锁,不支持崩溃后的安全恢复。
- 使用非聚簇索引,B+树索引叶子节点存储的是实际数据的地址。也就是说它的索引和实际数据是分开的。
- 支持全文索引(一种基于分词创建的索引,可以支持复杂查询)
- MyISAM不支持外键。将一个包含外键的InnoDB表转为MyISAM会丢失外键。
- 查询效率高,多用于只读或者大多数情况下只读的表,适合频繁查询几乎没有写入的情况。
Aria
MySQL创建者计划用来替代MyISAM的一款引擎,MariaDB包含了该引擎。
- Aria可以说是为了解决崩溃安全恢复的MyISAM,如果发生崩溃,Aria会回滚到命令执行前的状态。
- 能对数据进行缓存(MyISAM只能缓存索引)。
- 支持对一张表的并发插入操作。
Memory
Memory存储引擎(之前称为HEAP)将表中数据存放在内存中,不需要进行磁盘IO。Memory表的结构重启后会保留,数据会丢失。
如果需要快速访问数据,并且这些数据不会被修改,重启后丢失也没关系,那么使用Memory表是非常有用的。Memory至少比MyISAM快一个数量级。
Memory在很多场景可以发挥很好的作用:
- 用于查找或者映射表,例如将邮编和地点名映射的表
- 用于缓存周期性聚合数据的结果
- 用于保存数据分析中产生的中间结果
Memory表支持Hash索引,因此查找非常快。但还是无法取代传统基于磁盘的表。
Memory表字段长度固定。使用VARCHAR也会转换成CHAR。
Memory是表级锁,并发写入性能低。
Memory表不支持BLOB和TEXT类型的列,如果包含BLOB和TEXT类型,则会转换成MyISAM表。
Memory表可作为临时表保存中间结果但不等同于自己创建的临时表。
Archive
Archive只支持INSERT和SELECT操作,MySQL5.1之前不支持索引。只允许在自增id列上加索引。
- 数据存储在arz为后缀的文件中。
- 它会缓存所有的写并利用zlib对插入操作进行压缩,所以比- MyISAM表的磁盘IO更少。
- 但每次SELECT查询都要全表扫描,所以Archive表更适合日志和数据采集应用,这类应用往往需要全表扫描。
- 一些需要更快速的INSERT操作的场合下也可以使用。
CSV
CSV存储引擎可以将普通的csv文件(逗号为分隔符的文本文件)作为MySQL的表来处理,但不支持索引。
- 所有列必须都不能为null。
- 不适合大表,不适合在线处理。
- 可以作为一种数据交换的机制,在数据库运行时拷入或者拷出文件。
- 可以将Excel等电子表格软件中的数据存储为csv文件,然后复制到MySQL数据目录下,就能在MySQL中打开使用。
Cassandra
实现SQL与Cassandra之间的数据交换。Cassandra存储引擎允许MariaDB通过标准SQL语法使用Cassandra集群。未默认安装。官方说明
- 可以从多个MariaDB实例访问同一个Cassandra集群,它们每一个都可以运行Cassandra。
- 使用MariaDB的Cassandra存储引擎在Cassandra上执行查询时需要创建一个映射表。该虚表需要使用与实际Cassandra列族相同的静态列名和类型。而动态列在MariaDB中将以blob类型的单列形式存在