mysql的架构、并发和事务简介

mysql的架构很灵活,通过配置可以适应于不同的使用场景,但同时也会带来选择上的困惑,所以需要了解mysql的设计思想,以便做出合理的选择。
mysql最重要 与众不同的设计是它的存储引擎架构,这种架构设计将查询处理(query Processing)及其它系统任务(Server Task)和其它
数据存储/提取相分离,这种存储与处理相分离的设计使得在使用时可以根据性能,特性,以及其它需求来选择合适的数据存储方式。

1.mysql的逻辑架构
下面先给出mysql服务器逻辑架构图,然后再针对其中具体模块进行分析.

第一层的连接管理不是mysql独有的,大多数基于网络的客户端/服务器的工具或者服务都有类似的架构,比如连接处理,授权,认证等。
每个客户端连接都会在服务端拥有一个线程,这个连接上的查询只会在这个线程上执行,这个线程只能轮流在某个CPU核心或CPU中运行,服务器会负责缓存线程,不需要为每一个新建的连接新建或销毁线程。
当客户端连接到mysql服务器上去时,服务器需要对其进行认证。认证基于用户名 原始主机信息和密码,如果使用了安全套接字(SSL)方式进行连接,那还可以使用X.509证书进行认证。一旦客户单连接成功,服务器还会继续验证该客户是否具有执行特定查询的权限(如是否允许客户端对world数据库的country表进行SELECT操作)。
第二层是mysql中比较核心的一层,所有mysql的核心服务功能都在这一层提供:包括查询解析 分析 优化 缓存以及所有的内置函数(日期，数字 加密等);另外所有跨存储引擎的功能都在这一层实现:存储过程 触发器 视图等。
具体来说，mysql会解析查询,并建立内部数据结构(解析树),然后对其进行各种优化,包括重写查询 决定表的读取顺序以及选择合适的索引等,用户可以通过特殊的关键字提示优化器,影响它的决策过程;也可以请求优化器解释(explain)优化过程中的各个因素,使用户可以知道服务器是如何进行优化决策的,并提供一个参考基准。便于用户重构查询和schema 修改相关配置,使应用尽可能高效的运行。优化器并不关心表使用的是什么存储引擎,但是存储引擎对优化器是有影响的。优化器可能向存储引擎请求容量信息或某个具体操作的开销信息以及表数据的统计信息等。对于SELECT语句,在解析语句之前会先检查查询缓存(Query Cache),如果能在其中找到对应的查询,则服务器则不会执行解析 优化和执行的整个过程,而是会直接返回查询缓存中的结果集。
第三层包含了存储引擎.存储引擎负责mysql中数据的存储和提取。和Linux下的各种文件系统一样,每种存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信,这些接口屏蔽了不同存储引擎的差别,使得这些差异对上层查询过程透明。
存储引擎API包含了几十个底层函数,用于执行诸如:”开始一个事务”或者”根据主键提取一行记录”等操作,存储引擎不会去解析SQL(InnoDB会去解析外键定义,因为mysql服务器本身没有实现该功能),不同存储引擎之间也不会互相通信,只是简单的响应上层服务器的请求。

2.并发控制
无论何时,只要多个查询在同一时刻修改数据,就会产生并发问题。
1) 读写锁
如果同时对数据进行修改,看起来确实是会有并发问题,并且可以对数据进行加锁,防止同时对数据进行修改;但是如果在读取数据的同时,也在对数据进行修改,那么也可能产生读取失败或读取到脏数据的情况,所以在这种情况下也要进行加锁.对于这种很经典的问题,解决方法就是进行并发控制,在处理并发读写时经常使用两种锁组成的系统:共享锁和排它锁,也叫读锁和写锁.读锁是共享的,也就是说多个线程可以同时读取同一资源进行读取,互不干扰;而写锁是排他的,会阻塞所有的读锁和写锁,这样可以防止一个用户正在写入资源的时候,其它用户进行读取。
2) 锁粒度
加锁可以解决共享资源的安全性问题,但是问题在于加锁之后会减弱系统的并发性;为了加强并发性,所以需要降低锁的粒度,如果为了修改表中的一行数据就锁定了一张表,那么显然并发性就很低了,如果只锁定需要修改的那一行数据,则并发程度就会高很多。最理想的情况就是只锁定需要修改的那部分数据,在共享资源一定的时候,加锁的粒度越小则并发的程度就越高。
问题是数据库对锁的管理也是需要资源的,锁的状态管理:无论是加锁,检查是否已加锁,加锁操作,释放锁等操作都会消耗系统资源和耗费时间,如果锁的层次和数量过多那么可能导致系统过慢。
所谓锁的策略就是在锁的开销和数据安全性之间需求平衡。一般商用数据库在这上面都没有提供选择,一般使用的就是表上的行级锁,但是mysql因为底层提供了多种引擎所以也就提供了多种可供选择的锁策略。下面介绍下表锁和行锁:
表锁(table lock):
顾名思义,表锁就是对整张表都进行锁定,这是mysql中最基本的锁策略,也是开销最小的锁策略。如果一个线程对表加了锁那么它会阻塞后面所有用户的读写操作,比如插入 删除 修改某条数据或者读取某条数据的操作都会被阻塞。只有在没有写锁的情况下,其它线程才能获取读锁。写锁的优先性是高于读锁的,一个写锁的请求是可能会被放到读锁队列的前面,而相反的情况是不会出现的。表锁的效率虽然低,但是在Alter table这种情况下却是必要的,对于mysql而言,虽然底层的存储引擎实现了各种锁策略,但是在alter table情况下,服务器会默认使用表锁而忽略存储引擎的锁设定。
行级锁(row lock):
行级锁即只对需要修改的数据行进行加锁,这样可以实现最大程度的并发同时也会导致最大的锁开销。对于mysql而已,服务器层面没有实现任何
行级锁,所有实现都是依赖于存储引擎,比如InnoDB等。

3.事务
事务可以看成一组原子性的sql集合或者说是工作单元,如果存储引擎能对数据库执行全部的sql,那么整组sql都会执行,如果其中有某条sql因为崩溃或其它原因不能执行,那么整组sql都不会执行。也就是说事务内的所有sql语句要么都能执行,要么都不能执行。
一个简单的例子：假设一个银行的数据库有两张表：支票表和储蓄表。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么需要至少三个步骤：
1. 检查支票账户的余额高于200美元
2. 从支票账户余额中减去200美元
3. 在存储账户余额中增加200美元
上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。
可以START TRANSACTION语句开始一个事务，然后要么用COMMIT提交事务将修改的数据持久保留，要么使用ROLLBACK撤销所有的修改。事务SQL的样本如下：

1 START TRANSACTION
2 SELECT balance FROM checking WHERE customer_id=102332;
3 UPDATE checking SET balance = balance-200 WHERE customer_id=102332;
4 UPDATE savings  SET balance =balance+200.00 WHERE    customer_id=102332;
5 COMMIT

单纯所谓的原子操作并不能解释事务的全部，比如上面如果在执行第四条语句时服务器崩溃，那用户可能会损失200美元；如果在执行第三条语句到第四条语句之间时，突然有个进程要删除掉支票账户上的所有余额，那么银行就会无缘无故损失200美元。
所以并不是说保证一组语句都能成功执行或者都不执行就是事务的全部,还必须要考虑事务中用到的行被其它请求的影响,更严谨的来说:
一个运行良好的事务系统,必须具有ACID的特征,ACID代表原子性 一致性 隔离性和持久性。
1) 原子性
一个事务必须是一个最小的不可分割的最小工作单元,事务中包含的一组sql语句要么全都执行成功,要么执行失败全部回滚,不可能存在一部分语句执行成功,一部分语句执行失败的情况。
2) 一致性
数据库总是从一个一致的状态转移到另一个一直的状态.以上面银行账户为例,数据库不会存在支票账户中减少200没有,而存储账户中没有增加200美元这种不一致的状态。
3) 隔离性
通常来说,一个事务的修改在未被提交之前对其它事务是不可见的。以前面的例子来说,如果第三条sql被执行之后,第四条sql未被执行之前,有另外一个程序需要汇总支票账户中的余额,那么其看到的余额并不会被减去200美元.注意隔离性是分级别的。
4) 持久性
一旦事务被提交,则其修改会被永久保存到数据库中去,即使这时候数据库崩溃了。实际上,持久性也是分不同级别的,不可能做到100%。正如锁的粒度越小,系统开销越大,这种额外事务的安全性会导致系统额外的开销,包括cpu 硬盘等,mysql的灵活性在于既可以选择支持事务的引擎来保证逻辑的完整,也可以通过选择不支持事务的引擎来提高系统的性能。
下面是事务隔离性更具体的讨论：
隔离级别:mysql定义了四种隔离级别,每种隔离级别都规定了一个事务做的修改,那些是在事务内和事务间是可见的,那些是不可见的.低级别的隔离通常可以执行更高的并发,并且开销也更低(隔离级别依赖于存储引擎的实现,不同存储引擎对应的隔离级别定义会有所不同)。
未提交读 read uncommitted: 在这种隔离级别中,事务中的修改,即使没有提交,对其它事务也是可见的。这种事务可以读取到未提交数据的情况称为脏读。这种隔离级别是最低的,但是开销却不比其它高级别的隔离小多少,所以实际上很少使用。
提交读 read committed:事务对数据的修改,只有在事务提交之后对其它事务才是可见的,这样就解决了脏读的问题。这样可以实现前面说的一个事务的修改在未提交前对其他事务是不可见的,这种隔离级别是很多数据库默认的隔离级别。这个级别也叫做不可重复读,因为两次执行同样的查询,可能得到不一样的结果。
可重复读 repeatable read: 能保证在同一事务中多次读取同一记录的结果是一致的。可重复读隔离级别可以解决掉前面的脏读问题,也是mysql的默认隔离级别。但是还是会面临幻读的问题,所谓的幻读就是当前事务在读取某个范围内的数据时,另一个事务又向该范围内插入了新的记录,当之前的事务再次读取这个范围内数据时,会产生幻行。InnoDB和XtraDB引擎通过多版本并发控制解决了幻读问题。
可串行化 serializable: 这是最高的隔离级别,通过强制事务串行执行,避免了幻读问题。具体来说,会给事务中读取的每一行数据都加上锁,这样会导致大量的超时和锁争用问题,在实际中很少用到这个级别,只有在对数据一致性需求非常强,并且接受没有并发的情况下才使用。各个隔离级别归纳如下：

隔离级别	脏读	不可重复读	幻读	加锁读
read uncommitted	YES	YES	YES	NO
read committed	NO	YES	YES	NO
repeatable	NO	NO	YES	NO
serializable	NO	NO	NO	YES

死锁
死锁指的是两个或多个事务占有了同一组资源,并且相互请求锁定对方占有的资源,这时候就会陷入恶性循环导致死锁。多个事务试图以不同的顺序锁定资源时可能会发生死锁,多个事务试图同时锁定一个资源时也可能发生死锁。下面是一个两个事务因为执行语句顺序相反而导致死锁的示例：

START TRANSACTION
UPDATE StockPrice SET close =42 WHERE stock_id=12;
UPDATE StockPrice SET close = 23 WHERE stock_id=13;
COMMIT;

START TRANSACTION
UPDATE StockPrice SET close = 34 WHERE stock_id=13;
UPDATE StockPrice SET close = 45 WHERE stock_id=12;
COMMIT;

考虑这样一种情况：如果两个事务都执行了第一条UPDATE语句，更新了一行数据，
同时也锁定了该行数据，接着每个事务都尝试去执行第二条UPDATE语句，却发现该行已被对方锁定，然后两个都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环，这种情况只有外部因素介入才能解除死锁。
加锁的顺序是由存储引擎控制的,所以同样顺序的sql语句在一些存储引擎上可能会死锁而在另外一些不会,总的来说,死锁的原因分为两类:1是真正的数据冲突方式,这是很难避免的;2是完全由于存储引擎的实现导致的。解决死锁的一般思路就是对导致死锁的事务进行全部或部分的回滚。数据库系统实现了各种死锁检测和死锁超时机制,越是复杂的系统,这方面的设计越精细。一般来说有两种策略,第一种是检测到死锁之后就报错,另一种是在加锁的请求达到超时时间之后放弃加锁,相对来说第一种处理方法好点;InnoDB处理死锁的机制是检测到死锁之后,就将持有最少行排他锁的事务回滚(这是最简单的一种回滚机制)。
事务日志
事务日志指的是在应用程序发送sql修改数据时,并不是直接去修改该数据库中的数据而是将其修改记录作为日志持久化到磁盘上去.因为事务日志采用追加的形式写入磁盘,所以只需要顺序IO而不需要随机IO,可以大大的提高效率.持久化的事务日志可以在后台慢慢刷入到数据库中去,并且如果数据库崩溃,重新启动时也通过从事务日志中将还没刷入数据库的修改重新加入数据库。
mysql中的事务
mysql默认提供了两种支持事务的引擎:InnoDB和NDB cluster,下面介绍几个特点
1) 关于autocommit
mysql默认是自动提交,通过show variables like ‘autocommit’可以看到当前数据库的autocommit设置,1或on是打开自动提交;如果把autocommit设置为0,则意味着当前所有的语句都是在一个大事务中处理,直到显式的进行commit或rollback.这里需要注意对一些表来说,比如MyISAM和内存表来说是没有事务概念的,所有修改都会默认提交到真实数据源上去,设置autocommit对其无效。另外还有一些会造成很大量数据修改的操作,比如alter table或lock tables等语句都会在执行这些语句之前,自动执行commit操作。可以通过set transaction isolation level来设置事务的隔离级别,设置会在下一个事务开始时生效。
2) 在事务中使用混合存储引擎
mysql中的事务是由底层的存储引擎支持,所以如果在mysql中混合了支持事务的引擎(比如InnoDB)还有不支持事务的存储引擎(MyISAM),那么进行事务回滚的时候,就会出现一些表进行了回滚,另外一些表上没有,并且mysql不会报错,只会给一个警告。
3) 隐式锁定和显式锁定
InnoDB使用的是两阶段锁定协议,在事务执行过程中随时可以执行锁定,只有在commit和rollback的情况下才会释放锁,并且是在同一时刻释放所有的锁。这种不需要在sql语句中加锁,而是由InnoDB根据事务的隔离级别进行加锁的情况称为隐式加锁;InnoDB也支持通过特定的语句进行显式锁定,另外mysql服务器层也支持通过lock tables和unlock tables语句进行加锁,这些都属于显式锁定的情况.值得注意的是如果在InnoDB这种支持行级锁的引擎上使用lock tables这种表锁,对性能是很大的浪费。
4) 多版本并发控制
大多数mysql存储引擎都不是实现简单的行级锁,而是实现了多版本并发控制(MVCC),其它数据库引擎也是类型.可以认为MVCC是一种变形的行级锁,但在大多数情况下避免了加锁操作,因而提高了效率;并且大多数引擎都能做到非阻塞读,并且锁定时也只会锁定必要的行.具体的实现是为每个事务保留某个时间点数据的快照,后面都从这个快照进行数据的读取,也就能避免不可重复读和幻读的情况了,这种版本控制导致不同事务在同一时刻看到同一表的数据是不一致的.不同存储引擎对MVCC的实现是不同的,简单的来说有乐观并发控制和悲观并发控制.下面介绍InnoDB多版本并发控制机制的简化版:InnoDB为每行记录后面保存了两个隐藏的列,第一个列记录了这条记录的创建时间,第二个列记录了这条记录失效(删除)的时间,当然实际上记录的不是时间戳而是InnoDB中的系统版本号,可以将它们分别称为行创建版本号和行删除版本号;每新建一个事务这个版本号就会加1.当一个开始的时候,系统版本号加1,然后事务用此时的系统版本号作为自己的事务版本号;下面看下在repeatable隔离级别下,具体的操作逻辑。
SELECT
InnoDB会根据下面两个条件对数据进行检查:
首先是只会查找行创建版本号不大于当前事务版本号的数据,这样可以保证读取到的数据要么是在事务开始之前就修改过的数据,要么是在事务本身进行修改过的数据然后行删除版本号要么不存在,要么就大于当前事务版本号,这样可以保证读取到的行在事务开始之前是存在的。
INSERT
会将当前系统版本号作为行创建版本号插入数据库
DELETE
会将当前系统版本号作为行删除版本号
UPDATE
将当前系统版本号作为行创建版号,将原先的行创建版本号作为行删除版本号
这样通过两个版本号,就可以避免大多数情况下的加锁操作,在保证了只读到必要行的基础上,简化了操作提升了性能;缺点就是需要为每列数据额外维护一系列版本,增加了存储开销和需要更多的检查开销。
MVCC只在read committed和repeatable两个隔离级别下工作,与其它隔离级别不兼容,对人read uncommitted,总是读取最新的行,不需要版本控制;对于serializable对所有读取的行都加锁。