MySQL的存储引擎与事务

是时候了解了解MySQL底层了。

事务

事务具有ACID四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称之为一个事务。

原子性:一个事务是一个不可分割的工作单位,事务中包括的操作要么都做,要么都不做。
一致性:事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性:一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
持久性:一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

启动-提交-回滚

BEGINSTART TRANSACTION 语句代表开启一个事务。开启事务后,就可以继续写若干条语句,这些语句都属于刚刚开启的这个事务。
COMMIT 用来提交事务,ROLLBACK 中止回滚事务。

mysql> BEGIN;
mysql> select * from stu;
mysql> COMMIT;

mysql> START TRANSACTION;
mysql> update stu set username = 'admin' where id = 5;
mysql> ROLLBACK;

ROLLBACK语句是手动的去回滚事务时才去使用的,如果事务在执行过程中遇到了某些错误而无法继续执行的话,事务自身会自动的回滚。

自动提交

mysql> SHOW VARIABLES LIKE 'autocommit';

autocommit

如果我们不显式的使用START TRANSACTION或者BEGIN语句开启一个事务,那么在命令行输入的每一条语句都算是一个独立的事务,这种特性称之为事务的自动提交。
SET autocommit = OFF;可以关闭自动提交,这样的话,我们写入的多条语句就算是属于同一个事务,直到我们显式的写出COMMIT语句来把这个事务提交掉,或者显式的写出ROLLBACK语句来把这个事务回滚掉。

隐式提交

当我们使用START TRANSACTION或者BEGIN语句开启了一个事务,或者系统变量autocommit = OFF 时,事务不会进行自动提交,但是如果我们输入了某些语句后,会触发如同输入了COMMIT语句一样的效果,这种因为某些特殊的语句而导致事务提交的情况称为隐式提交。

  1. 定义或修改数据库对象的数据定义语言(Data definition language,缩写为:DDL)。所谓的数据库对象,指的就是数据库、表、视图、存储过程等等这些东西。当我们使用CREATE、ALTER、 DROP等语句去修改这些所谓的数据库对象时,就会隐式的提交前边语句所属于的事务。
  2. 隐式使用或修改mysql数据库中的表:当我们使用ALTER USER、CREATE USER、DROP USER、 GRANT、RENAME USER、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。
  3. 事务控制或关于锁定的语句:当我们在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时,会隐式的提交上一个事务。或者当前的 autocommit系统变量的值为OFF,我们手动把它调为ON时,也会隐式的提交前边语句所属的事 务。或者使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式的提交前边语句所属的事务。
  4. 加载数据的语句:比如我们使用LOAD DATA语句来批量往数据库中导入数据时,也会隐式的提交前边语句所属的事务。
  5. 其它的一些语句:使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边语句所属的事务。

保存点

在某个位置执行savepoint SPname; 可以设定一个名为 SPname 的保存点。后通过 ROLLBACK to SPname; 语句恢复到执行保存点之前的样子。保存点通过 release savepoint SPname 释放。

隔离

当高并发访问会遇到多个事务的隔离问题,可能会出现以下:

脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据

Dirty-read

不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。

read-committed

幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

phantom-read

不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表

事务隔离级别 脏读 不可重复读 幻读 说明
读未提交(read-uncommitted) 最低的事务隔离级别,一个事务还没提交时,它做的变更就能被别的事务看到
不可重复读(read-committed) 保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该事物未提交的数据。
可重复读(repeatable-read) 多次读取同一范围的数据会返回第一次查询的快照,即使其他事务对该数据做了更新修改。事务在执行期间看到的数据前后必须是一致的。
串行化(serializable) 事务 100% 隔离,可避免脏读、不可重复读、幻读的发生。花费最高代价但最可靠的事务隔离级别。
-- 修改隔离级别
mysql> set session transaction isolation level read uncommitted;

-- 查看隔离级别
mysql> select @@transaction_isolation;

tx_isolation

存储引擎

通过 show engines 语句可以查看到数据库所支持的存储引擎。

support-engine

可见只有 InnoDB 支持事务,且当前新数据库版本8.0存储引擎默认为 InnoDB
MyISAM 是5.1之前的MySQL版本的默认存储引擎。默认存储引擎的转换,说明 InnoDBMyISAM 在应用场景、功能或效率方面等方面有优势。

MyISAM

MyISAM基于传统的ISAM类型代码,ISAMIndexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法。MyISAM不是事务安全的、不支持外键、支持表锁(读为主)、使用堆表(非聚集索引)、索引与数据分离(只缓存索引)、如果执行大量的select语句MyISAM比较适合。
MyISAM在磁盘上存储上有三个文件.frm(存储表定义) .myd(存储表数据) .myi(存储表索引)

myisam

MyISAM索引会保存该索引指向的地址,地址中存储的是行数据,也就是说它的索引和实际的数据是分开的,只不过是用索引指向了实际的数据。

InnoDB

  1. InnoDB 支持事务,MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
  2. InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败
  3. InnoDB 是聚集索引,MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上,因此 InnoDB 必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。而 MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的
  4. InnoDB 不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快
  5. InnoDB 最小的锁粒度是行锁,MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一

innodb

MyISAM会比Innodb的查询速度快,在于INNODB在做SELECT的时候,要维护的东西比MYISAM引擎多很多:innodb寻址要先走索引映射到页块,再到行,MYISAM记录的直接是文件的OFFSET,定位比INNODB要快

idx

联合索引的创建会在叶子结点上保存该数据的主键值,而不会产生一次数据全拷贝构建。联合索引找到数据后,根据主键会产生一次回表查询。回表查询:先定位主键值,再定位行记录

页是InnoDB管理存储空间的基本单位,一个页的大小默认是16KB。

SHOW GLOBAL STATUS like 'Innodb_page_size';

B+ tree

  1. 每个索引都对应一棵B+树,用户记录都存储在B+树的叶子节点,所有目录记录都存储在非叶子节点
  2. InnoDB存储引擎会自动为主键建立聚簇索引,聚簇索引的叶子节点包含完整的用户记录
  3. 可以为指定的列建立二级索引,二级索引的叶子节点包含的用户记录由索引列 + 主键组成,所以如果想通过二级索引来查找完整的用户记录的话,需要通过回表操作,也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录
  4. B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表,而且每个页内的记录(不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话,则页面和记录先按照联合索引前边的列排序,如果该列值相同,再按照联合索引后边的列排序
  5. 通过索引查找记录是从B+树的根节点开始,一层一层向下搜索。由于每个页面都按照索引列的值建立了页目录,所以在这些页面中的查找非常快。

其他细节待填充

参考

讲透MySQL索引底层原理
InnoDB和MyISAM的区别和实现
深入Mysql索引底层原理