mysql 设置mvcc_MySQL架构及MVCC

MySQL结构

MySQL架构图便于理解MySQL。

MySQL架构

最上层连接线程处理，是提供给客户端的，如连接处理，授权认证，安全。

第二层是MySQL的核心服务，包括查询解析，分析，优化，缓存以及所有内建函数(日期，时间，数学和加密函数等)，所有跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。

第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。每个存储引擎都有它的优势和劣势，服务器通过API与存储引擎通信。这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，只是简单的响应上层服务器的请求。

优化与执行

MySQL会解析查询，并创建解析树对其进行优化，包括重写查询，表的读取顺序，选择合适的索引。我们一般使用explain请求优化器解释SQL执行过程。优化器不关心表使用的是什么存储引擎，但存储引擎对优化查询是由影响的，优化器会请求存储引擎提供容量或某个操作的开销信息，以及表数据的统计信息。

对于SELECT语句，在解析查询之前，服务器会先检查查询缓存，如果能找到对应的查询，服务器就不必再执行查询解析，优化和执行的整个过程了，直接返回结果集即可。

并发控制

只要存在多个SQL同时读写数据，就会存在并发问题，MySQL是通过锁机制解决并发问题的。

读写锁

锁一共有两种类型，读锁和写锁，读锁是共享的，互不阻塞，写锁是排他的，一个写锁会阻塞其他写锁和读锁，这样设计是出于安全的考虑，保证用户执行写入的时候，其他用户不能读取和写入统一资源。

写锁比读锁优先级更高，因此写锁请求可能插入到读锁前面。

使用锁也需要消耗资源，包括获得锁，检查锁，释放锁等。所以需要在资源消耗(性能)和数据安全之间寻求平衡。MySQL提供了多种选择，每种存储引擎都可以实现自己的锁策略。在所有锁策略里面，最重要的是下面两种策略。

表锁

表锁是MySQL中最基本的锁策略，并且是开销最小的锁。表锁会锁定整张表，一个用户在对表进行写操作前，需要先获取锁，获取到锁后会阻塞其他用户对表的读写操作。

在特定的场景中，表锁策略性能很好，例如READ LOCAL表锁支持某些类型的并发写操作。

服务器会管理自己的锁，MySQL本身还是会使用表锁来实现不同的目的。比如服务器会为ALTER TABLE语句使用表锁，而忽略存储引擎本身的锁机制。

行级锁

行级锁最大程度上的支持了并发处理，但也带来了最大的所开销。存储引擎InnoDB实现了行级锁，行级锁只在存储引擎实现，MySQL服务器层没有实现。

事务

最基础也是最重要的：事务和隔离级别。学习Hibernate会说，学习MyBatis会说，学习Spring事务也会说，感觉耳朵都起茧子了。附一个自己写过的文章：SPRING事务

死锁

如果多个事务尝试以不同的顺序锁定资源时，就会产生死锁，多个事务同时锁定同一资源时，也会产生死锁。为了解决这个问题，数据库实现了各种死锁检测和死锁超时机制。越复杂的系统，越能检测到死锁，比如InnoDB存储引擎，会立即返回一个错误。死锁发生以后，只有部分或完全回滚一个事务，才能打破死锁。对于事务型系统，死锁是无法避免的，所以必须考虑如何处理死锁。大多数情况下再次执行因死锁回滚的事务即可。

事务日志

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表数据时，只需要修改其内存拷贝，再将该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

事务日志采用的是追加的方式，因此只在磁盘一小块区域顺序I/O，所以速度很快。事务日志持久之后，内存中的被修改的数据可以慢慢的刷新到磁盘。

如果数据的修改已经记录到日志并持久化，但数据本身还没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式视存储引擎而定。

MySQL中的事务

MySQL提供了两种事务型的存储引擎：InnoDB 和 NDB Cluster。

自动提交

MySQL默认采用自动提交模式。如果不显示的开始一个事务，则每个查询都被当做一个事务执行提交操作。

show variables like 'autocommit'; -- 查询当前模式

set autocommit = 0 ; -- 关闭自动提交，1或者ON为打开，0或者OFF为关闭

在自动提交关闭时，所有的查询都是在一个事务中，直到显式的执行COMMIT或者ROLLBACK，该事务结束，同时又开始了一个新事务。

如果修改非事务型的表，如MyISAM，不会又任何影响，这类表没有事务概念，相当于一直启用autocommit。

设置隔离级别

MySQL默认的隔离级别为REPEATABLE-READ，可以通过以下命令设置隔离级别，新的隔离级别在下一个事务开始时生效。

select @@global.tx_isolation; -- 查询当前隔离级别

set session transacton isolation level read committed; -- 只改变当前会话隔离级别

set global transacton isolation level read uncommitted; -- 设置当前系统的隔离级别

也可以在配置文件中设置整个数据库的隔离级别，MySQL识别4个ANSI隔离级别，InnoDB支持所有隔离级别。

MVCC

MySQL的大多数事务存储引擎实现都不是简单的行级锁，一般是实现了多版本并发控制(MVCC)。可以认为MVCC是行级锁的一个变种，它在很多情况下避免了加锁操作，因此开销更低，在进行读操作时不需要阻塞，写操作只锁定必要的行。

MVCC的实现

MVCC的实现，是通过保存数据在某个时间点的快照实现的。这样就保证了不管事务不管运行多长时间，在同一个事务中看到的数据是一致的。

不同存储引擎的MVCC实现是不同的，典型的有乐观锁并发控制和悲观并发控制。在InnoDB的MVCC，是乐观锁并发控制，通过在每行记录后面保存两个隐藏的列来实现的。这两个列保存了行创建时的系统版本号和删除时的删除版本号，每开始一个新的事务，系统版本号都会自动递增。系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

InnoDB在REPEATABLE READ隔离级别下，不同具体操作如下：

SELECT

查找的数据必要符合下面两个条件：

查找系统版本号小于等于当前事务版本号的数据行，这样可以确保事务读取的行要么是事务开始前已经存在的行，要么是事务自身插入或修改的

删除版本号未指定或大于当前事务版本号的数据行，确保数据读取到的行在事务开始前未被删除。

INSERT

新插入的每一行保存当前事务版本号

DELETE

将当前事务版本号保存为删除版本号

UPDATE

原有的数据行的删除版本号记录为当前事务版本号，然后插入一条新的记录

MVCC的总结

保存了这两个版本号，使大多数读操作都不用加锁，这样设计操作简单，提升了性能，保证了只会读取到符合标准的行。不足的地方是每行记录都需要额外的存储空间，需要做更多的检查工作，以及额外的维护工作。

MVCC只在REPEATABLE READ和READ COMMIT两个隔离级别下工作，对于READ UNCOMMITTED和SERIALIZABLE不兼容，前者只读取最新的数据行，后者对读取的所有数据行都加锁。

存储引擎

InnoDB

InnoDB是MySQL默认的存储引擎，也是最重要，使用最广泛的存储引擎，它被设计用来处理短期事务，另外InnoDB还有性能优势和自动崩溃恢复特性。在日常开发中，除非有特别的原因，否则优先考虑InnoDB。

特性

1.InnoDB采用MVCC来支持高并发，实现了四个标准的隔离级别。默认级别是REPEATABLE READ，通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及到的行，还会对索引中的间隙进行锁定，防止幻影行的插入。

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

3.InnoDB支持在线热备份。

4.InnoDB在崩溃的情况下发生损坏的概率低，恢复速度快。

MyISAM

在MySQL5.1版本之前，MyISAM是默认的存储引擎，它提供了大量的特性，包括全文索引，压缩，空间函数等，但它不支持事务和行级锁，最重要的是崩溃后无法安全恢复。

特性

MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加读锁，写入时对表加写锁。

MyISAM引擎设计简单，数据以紧密格式存储，在某些场景下性能很好。

其他内建引擎

Archive、CVS、Memory、Merge等等。

除了内建引擎外，还有一些第三方的存储引擎，以插件的形式供MySQL使用。