《高性能MySQL》阅读笔记之一

关键词

MySQL 逻辑架构、读写锁与锁粒度、死锁、2PL两阶段锁定协议、ACID、四种隔离级别、脏读、不可重复读和幻读、MVCC、InnoDB与MyISAM。

MySQL 逻辑架构

mysql

第一层：不是 MySQL 独有，包括连接处理、授权认证、安全等。
第二层：大多数 MySQL 的核心服务功能都在该层，包括查询解析、分析、优化、缓存以及所有的内置函数，以及所有的跨存储引擎功能（存储过程、触发器、视图等）。
第三层：包含存储引擎，存储数据，提供读写接口。

读写锁与锁粒度

读写锁

读锁：也称共享锁，多个用户可以在同一时刻读取同个资源。

写锁：也称排他锁，写锁会阻塞其他写锁与读锁，保证只有同一时刻只有一个用户能执行写入。

锁粒度

表锁：锁定整张表。

行级锁：行级锁只在存储引擎层实现，MySQL 服务器层没有实现，InnoDB 中锁粒度默认为行锁。

事务

ACID

ACID 表示原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），是事务必须要满足的四个特性。

原子性：一个事务是一个不可分割的最小工作单元，整个事务要么全部完成，要么全部失败回滚，不可能只执行其中的一部分操作。
一致性：开启事务前后数据的完整性必须保持一致。
隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性：一旦事务提交，则其所做的修改就会永久保存至数据库中，此时即使数据库崩溃，修改的数据也不会丢失。

隔离级别

READ UNCOMMITTED 读未提交：事务的修改即使没有被提交，对其他事务也是课件的。其他事务可能会读取到未提交的数据，如果此时该事务失败回滚，其他事务读到的数据就是回滚前的脏数据，也被称为脏读。

READ COMMITTED 读已提交：这是大多数数据库默认的隔离级别，但 MySQL 不是。一个事务从开始直到提交前，所做的任何修改对其他的事务都是不可见的，也叫不可重复读，一个事务范围内两个相同的查询，可能会得到不同的结果。

REPEATABLE READ 可重复读：MySQL 默认的隔离级别，存在幻读问题：当某个事务在读取某个范围内的记录时，另一个事务在此范围内插入新的记录，此时之前的事务再次读取该范围内的记录时，就会产生幻影行。InnoDB 与 XtraDB 通过 MVCC 解决了该问题。
SERIALIZABLE 可串行化：最高隔离级别，强制事务串行执行，避免了幻读问题，会在读取的每一行数据都加上锁。

MySQL 中可以通过SET TRANSACTION ISOLATION LEVEL来设置隔离级别，新的隔离级别会在下一个事务开始时生效，或者在配置文件中设置整个数据库的隔离级别。

总结：

事务隔离级别	脏读	不可重复读	幻读
读未提交（READ UNCOMMITTED）	是	是	是
读已提交（READ COMMITTED)	否	是	是
可重复读（REPEATABLE READ）	否	否	是
可串行化（SERIALIZABLE）	否	否	否

不可重复读和幻读的区别

(1) 不可重复读是读取了其他事务更改的数据，针对 Update 操作，读取的是同一条数据。

解决：使用行级锁，锁定该行，事务 A 多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

(2) 幻读是读取了其他事务新增的数据，针对 Insert 操作（DELETE应该也算幻读），读取的是同一片数据。

解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

死锁

死锁的解决办法：

死锁检测：检测到死锁的循环依赖，立即返回一个错误。
死锁超时机制：当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常不太好。

InnoDB 处理死锁的方式：将持有最少行级排他锁的事务进行回滚。

死锁发生后，只有部分会完全回滚其中一个事务，才能打破死锁。

间隙锁

间隙锁（next-key locking）可以防止幻读。

例如，假设 table 表中存在 110 条数据：

1	select * from table where id > 100 for update;

此时 InnoDB 不仅会对符合条件的 id = 101~110 的记录加锁，还会对 id > 110 的记录（即使这些记录不存在）间隙加锁。如果不使用间隙锁，此时若有其他事务插入了 id > 100 的任何记录，那么原事务再次执行上述语句时就会产生幻读。

MySQL 中的事务

自动提交

MySQL 默认采用自动提交（AUTO COMMIT）模式，即每个查询都被当做一个事务执行。

可以通过SHOW VARIABLES LIKE 'AUTOCOMMIT'命令来查看，1 或者 ON 表示启用，0 或者 OFF 表示禁用。

2PL 两阶段锁定协议

InnoDB 采用的是两阶段锁定协议，在事务的执行过程中，只有 COMMIT 和 ROLLBACK 是解锁阶段，其余过程都是加锁阶段，并且所有的锁都会在同一时刻释放。

MVCC 多版本并发控制

大多数的数据库都不使用简单的行级锁机制来实现事务，一般都与MVCC配合使用。MVCC 可以在大多数情况下代替行级锁，使用 MVCC，能降低系统开销。通过 MVCC ，可以实现ACID 中的隔离性，保证多个事务读取的值不会互相影响。

MVCC 是通过保存数据在某个时间点的快照来实现的。不同存储引擎的 MVCC 实现是不同的，典型的有乐观并发控制和悲观并发控制。

InnoDB 中的 MVCC 实现属于乐观锁（实际上大多数的乐观锁也都是基于 Version 机制实现）。

InnoDB 中的 MVCC 实现机制

InnoDB 中的 MVCC 是通过在每行记录后的两个隐藏列实现的，一个列保存了行的创建时间，一个保存了行的过期时间(或删除时间)，存储的并不是实际时间，而是系统版本号。

每开始一个新事物，系统版本号都会递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行对比。下面是可重复读（REPEATABLE READ）隔离级别下的操作：

SELECT

InnoDB 会选择同时满足以下 2 个条件的数据作为查询结果返回。

只查找版本号早于当前事务版本的数据行，这样可以保证读取的数据是在此次事务开始前就存在的，或者是此次事务中修改过的。
查找行的删除版本要么未定义，要么大于当前的事务版本号，这样可以保证此次事务读取到的数据，在事务开始前未被删除。

第 2 点的例子：

# 事务A
select * from table;
.... B事务执行
select * from table;

# A事务第一次执行select的结果
id  name  create_version  delete_version
1	 a         1                null
2	 b		   1                null

A 事务的执行过程中执行了 B 事务：

# 事务B
delete from table where id = 1; 
# 执行事务B后的数据
id  name  create_version  delete_version
1	 a         1                2
2	 b		   1                null

# 事务A
select * from table;
.... B事务执行
select * from table;

# A事务第二次执行select的结果
id  name  create_version  delete_version
1	 a         1                2
2	 b		   1                null

INSERT

InnoDB 会把当前系统版本号作为新插入行的行版本号。

UPDATE

InnoDB 执行 UPDATE 操作实际上是新插入了一行记录而不是覆盖，会把当前系统版本号为新插入行版本号，并保存当前系统版本号作为原来的行的删除版本号。

例子：

# 执行UPDATE前
id  name  create_version  delete_version
1	 a         1                null

update table set name= 'b' where id = 1;

# 指定UPDATE后
id  name  create_version  delete_version
1	 a         1                1
1	 b         2                null

由于旧数据并不真正的删除，所以必须对这些数据进行清理，Innodb会开启一个后台线程执行清理工作，具体的规则是将行版本号小于当前系统版本的行删除，这个过程叫做 purge。

DELETE

InnoDB 会把当前系统版本号作为删除行的行删除版本号。

总结：SELECT 不会更新版本号，是快照读（历史版本）；INSERT、UPDATE 和 DELETE 会更新版本号，是当前读（当前版本）。

保存的额外两个系统版本号，使得大多数的读操作都可以不用加锁，不足之处在于需要额外的存储空间，而且需要做更多的行检查工作和一些额外工作。

MVCC 只在读未提交（READ UNCOMMITTED）和可重复读（REPEATABLE READ）两个隔离级别下工作，其他两个隔离级别与 MVCC 不兼容，原因是读未提交（READ UNCOMMITTED）总是读取最新的行，而不是符合当前事务版本的行，可串行化（SERIALIZABLE）则对所有读取的行都加锁。

MySQL 存储引擎

InnoDB

InnoDB 表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，但是它的二级索引中必须包含主键列，所以如果主键列很大的话，其他所有索引都会很大。因此，若表中索引较多，主键应当尽可能的小。

MyISAM

MyISAM 不支持事务和行级锁，且崩溃后无法安全恢复。

MyISAM 的特性：

加锁和并发：对整张表加锁，体现在读取时对读到的表加共享锁，写入时则加排他锁。但在表有读请求时，也可以在表中插入新的记录（被称为并发插入，CONCURRENT INSERT）。
修复：手工或自动执行检查和修复操作。
索引：支持全文索引。
延迟更新索引键：如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是写入内存中的键缓冲区。只有在清理键缓冲区或者关闭表的时候才会写入磁盘。这种方式可以极大地提升写入性能，但在数据库崩溃时会造成索引损坏。

InnoDB 和 MyISAM 区别

InnoDB 支持事务和外键，MyISAM 不支持。
InnoDB 支持表锁、页锁、行锁，MyISAM 只支持表锁。
InnoDB 采用聚簇索引，索引与数据一同顺序存储，MyISAM 采用非聚簇索引，索引和数据分开随机存储。

选择合适的引擎

如果不在乎可扩展能力和并发能力，也不在乎崩溃后的数据丢失问题，却对 InnoDB 的空间占用过多比较敏感，可以选择 MyISAM。