Jerry的知识索引

外观

MySQL的存储引擎、事务、锁

约 3703 字大约 12 分钟

database

2018-09-09

存储引擎

存储引擎是数据库的底层软件组织。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，可以获得其各自特定的功能。

InnoDB

InnoDB 是 MySQL 默认的存储引擎，它是面向在线事务处理(OLTP)的应用，被设计用来处理大量的短期（short-lived）事务。

特点：

  1. 采用 MVCC（多版本并发控制）来支持高并发
  2. 支持事务，实现了 4 个事务隔离级别，默认级别是可重复读
  3. 通过间隙锁（next-key locking）防止幻读，间隙锁使得 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入
  4. 聚簇索引，主键查询性能高，但二级索引（非主键索引）必须包含主键列，如果主键列很大，其他索引都会很大
  5. 存储格式平台独立
  6. 行锁设计
  7. 支持外键
  8. 支持一致性非锁定读（默认情况下读取操作不会产生锁）
  9. 提供了插入缓冲，二次写，内存自适应哈希索引，预读等高性能和高可用的功能
  10. 支持真正的热备份

一句话概括：InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

MyISAM

MyISAM 存储引擎表由 .MYD 和 .MYI 组成，.MYD用来存放数据文件，.MYI用来存放索引文件，特点：

  • 不支持事务
  • 表锁设计，读取共享锁，写入排他锁。但是在读取查询的同时也能插入记录（并发插入）
  • 支持全文索引
  • 压缩
  • 空间函数（GIS）
  • 它的缓冲池只缓冲索引文件，而不缓冲数据文件
  • 崩溃后无法安全修复

一句话概括：MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。如果表经常读取，且不需要事务，MyISAM是合适的选择。

MEMORY

将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据库都将消失，它非常适合存储临时数据的临时表．默认采用哈希索引。

三种存储引擎的比较

Engine

事务

什么是事务？

事务就是由一组SQL语句组成的逻辑处理单元，一个事务中的SQL语句组，要么全部执行，要么全部不执行。

START transaction;
SELECT ... FROM ...
UPDATE checking SET ...
DELETE savings FROM ...
COMMIT;

事务的自动提交（AUTOCOMMIT）

如果不显式开始一个事务（begin 或 start transaction 语句），则每个查询都被当作一个事务执行提交操作。

SHOW VARIABLES LIKE 'AUTOCOMMIT';
SET AUTOCOMMIT = 1; // 1 或 ON 表示启用，0 或 OFF 表示禁用

在实践中，建议不要将 AUTOCOMMIT 设为 0，那样连接将保持长事务，直到你手动commit。下面的语句用于查询超过60s的事务：

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started)) > 60

事务的并发问题

  1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
  2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
  3. 幻读：事务A在读取某个范围内的记录时，事务B又在该范围内插入新的记录，事务A再次读取该范围的记录时会产生幻行。（两次不一致）

不可重复读侧重于记录被修改，幻读侧重于新增或删除了记录。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务的四大特征

事务的四个基本特征：ACID

  1. 原子性（Atomicity）：一个事务必须被视为不可分割的最小工作单元。一个事务中的所有操作，要么全部成功提交，要么全部失败回滚。不可能只执行其中的一部分操作。
  2. 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态，或者说从一个一致性状态转换到另一个一致性状态。不会出现查询开始时的数据跟查询到一半的数据不一样的情况。
  3. 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。这意味着 事务隔离分为不同级别，包括未提交读（Read uncommitted）、提交读（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  4. 持久性（Durability）: 事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

事务的ACID特性可以确保银行不会弄丢你的钱，因此很重要。

事务的四种隔离级别（Isolation level）

事务的隔离级别规定了哪些修改在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离并发程度高，开销低。

未提交读(read uncommitted)

事务中的修改，即使未提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也称为脏读（Dirty Read）。该级别性能不必其他级别好太多，带来的问题却不少，不建议使用。

提交读(read committed)

只有提交了，其他事务才能读到。即一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候叫做不可重复读，因为同一事务自己两次执行同样的查询，期间可能有其他事务修改并提交了数据，因此两次查询可能会得到不一样的结果。

可重复读(repeatable read)

解决了脏读问题，该级别保证了在同一个事务中多次读同样记录的结果是一致的，理论上无法解决幻读问题。(MySQL InnoDB通过 MVCC 解决幻读问题)。

该级别是 MySQL 的默认隔离级别。

可串行化(serializable)

它通过强制事务串行执行，避免了前面说的幻读的问题。该级别用得较少。

如何改变事务的隔离级别

锁（并发控制）

当数据库表同时被多个事务读写时，一个事务正在读取，另一个事务将记录删除了，将会导致不可预期的结果。因此，引入锁的概念来解决并发控制问题。

锁的分类

共享锁和排他锁

共享锁（shared lock）也叫做读锁，如果一个事务对数据对象A加了共享锁，其他事务只能读而不能写，直至当前事务释放该锁。

排他锁（exclusive lock）也叫做写锁，如果一个事务对数据对象A加了排他锁，其他事务不能再对A加锁，包括读和写，直至当前事务释放该锁。

乐观锁和悲观锁

悲观锁假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。而乐观锁假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

乐观锁 不能 解决脏读的问题。例如，事务T1更新了一行记录内容，但并没有提交修改。事务T2读取更新后的行，然后T1执行回滚操作。T2读取的行就无效了。

锁的粒度

加锁也需要消耗资源，数据库的目标是存取数据，而不是花费大量的时间来管理锁。很多商业数据库通常都是在表上加行锁，但 MySQL 不同的存储引擎实现了不同的锁策略和锁粒度,提供了多种选择。

在 MySQL 中， MySQL有三种级别的锁定：

表锁（table lock）

实现逻辑简单，带来的系统负面影响最小。获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发程度大打折扣。

页锁（page lock）

锁定颗粒度介于行级锁定与表级锁之间。页级锁定和行级锁定一样，会发生死锁。

行级锁（row lock）

锁定颗粒度很小，发生锁定资源争用的概率也最小。能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。但由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。最容易发送死锁。

在 MySQL 中，行级锁只在存储引擎层实现，服务器层并不了解存储引擎中的锁是如何实现的。不同的存储引擎有不同的行锁实现。

死锁问题

  1. 解决一：InnoDB 存储引擎一旦检测到死锁的循环依赖，就会立即返回一个错误。具体做法是，将持有最少行级排他锁的事务进行回滚。
  2. 解决二：当查询超时自动放弃锁请求，这种方式不太友好。

锁的行为是存储引擎相关的，有些死锁是真正的数据冲突，而有些是存储引擎的实现导致的。

数据库三大范式

第一范式（1NF）

属性不可分。当关系模式R的所有属性都不能再分解为更基本的数据单位时，称R是满足第一范式的。

例如，某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就有必要要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。而“省份”、“城市”这些属性就是不可再分的了。

符合1NF的表

第二范式（2NF）

符合1NF，并且表中的每列都和主键相关。也就是说，在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

符合2NF的表

第三范式（3NF）

符合2NF，并且，消除传递依赖。即每一列数据都和主键直接相关，而不能间接相关。

符合3NF的表

参考：

  • https://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html
  • https://www.zhihu.com/question/24696366/answer/29189700

范式化的优缺点

  • 优点：更新操作快，没有冗余数据（意味着更少的GROUP BY和 DISTINCT），表数据通常较少可以更好地放进内存因此执行操作更快
  • 缺点：需要关联，同时可能使某些索引失效

《高性能MySQL》中提到，实际中经常需要混用范式化和反范式化，不要极端地完全去范式化。

事务日志

事务日志用于提高事务效率。

存储引擎修改表数据时，只需修改内存拷贝，然后把修改记录持久在硬盘的事务日志中，而不用每次都把修改的数据本身持久到硬盘中。之后把再内存修改的数据再慢慢刷回磁盘中。

MySQL binlog 和 redo log

redo log

物理日志，是 InnoDB 引擎特有的，记录的是“在某个数据页上做了什么修改”这样的操作。

使用了 WAL(Write-Ahead Logging) 技术，即先写日志，再写磁盘。所以能支持崩溃修复（crash-safe）。

redo log 是循环写的，重复利用空间。且采用追加写操作，是在硬盘中的一小块区域内顺序I/O，而不需要在多个地方随机I/O，所以速度快。

binlog

逻辑日志，是 Server 层日志，存储引擎无关，记录的是“给ID=2这一行的c字段加1”这样的操作。

binlog是追加写的，适合归档保存，通常也用来恢复误删的数据。

MySQL事务有两阶段提交，即一个 update 语句，需要依次经过 redo log prepare、binlog、 redo log commit 三个步骤，保证两份日志的一致性。

多版本并发控制（MVCC）

MVCC 是行锁的变种，使用 非阻塞读操作 在很多情况下避免了加锁，写操作也只锁定必要的行，因而开销更低。

MVCC 是通过保存数据在某个时间点的快照来实现的。不管事务执行多少时间，每个事务看到的数据都是一致的。

InnoDB 的 MVCC 实现

在每行记录后面保存两个隐藏列。一个保存行的创建时间，一个保存行的过期时间（删除时间）。这里的时间，指的是系统版本号（事务启动时分配的ID），不是真正的时间。每开始一个事务，系统版本号都会递增。

  • SELECT：只查找早于当前版本的数据行，删除时间要么未定义，要么大于当前版本号。
  • INSERT：为插入的行保存当前版本号。
  • DELETE：为删除的行保存当前版本号，作为删除标志。
  • UPDATE：先插入一行新记录作为新版本号，再保存当前版本号到原来的行作为行删除标志。

MVCC的好处是读操作简单，性能好，不足是需要额外的存储空间和行检查、维护工作。

MVCC 只在提交读（READ COMMITTED）和可重复读（REPEATABLE READ）两个隔离级别下工作。

参考：

  • 《高性能MySQL》
贡献者: jerrysheh