MySQL的存储引擎、事务、锁

3703字约12分钟

database

2018-09-09

存储引擎

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

InnoDB

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

特点：

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

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

MyISAM

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

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

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

MEMORY

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

三种存储引擎的比较

事务

什么是事务？

事务就是由一组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

事务的并发问题

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

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

事务的四大特征

事务的四个基本特征：ACID

原子性（Atomicity）：一个事务必须被视为不可分割的最小工作单元。一个事务中的所有操作，要么全部成功提交，要么全部失败回滚。不可能只执行其中的一部分操作。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态，或者说从一个一致性状态转换到另一个一致性状态。不会出现查询开始时的数据跟查询到一半的数据不一样的情况。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。这意味着事务隔离分为不同级别，包括未提交读（Read uncommitted）、提交读（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性（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 中，行级锁只在存储引擎层实现，服务器层并不了解存储引擎中的锁是如何实现的。不同的存储引擎有不同的行锁实现。

死锁问题

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

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

数据库三大范式

第一范式（1NF）

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

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

符合1NF的表