MySQL架构基础篇 ---《高性能MySQL》读书笔记

• MySQL逻辑架构
• 锁与事务
• • 锁级别
  • 死锁
  • 事务的ACID
  • 事务的隔离级别
  • 事务中混合使用存储引擎
  • MVCC-多版本并发控制
• 存储引擎
• • InnoDB

第一层做一下安全认证，授权处理之类的，大多数C/S工具都会有这样的结构。
第二层是大多数MySQL核心功能的所在，包括查询解析，分析，优化，以及所有的内置函数（例如日期，时间，加密函数），所有的跨存储引擎的功能也都在这一层实现（例如存储过程，触发器，视图）
第三层是存储引擎层，mysql有不同的存储引擎，服务器通过API和存储引擎通信，这样就屏蔽了不同存储引擎之间的差异。存储因此API包含几十个底层函数，用于执行诸如‘开始一个事务’或者‘根据主键提取一行记录’等操作，存储引擎不会解析SQL，不同存储引擎之间也不会相互通信，而只是简单的响应上层服务器的请求。

mysql默认的锁策略是表级锁，也是开销最小的策略。同时mysql一些存储引擎如InnoDB和XtraDB也实现了行级锁，最大程度的支持并发，但是相应的锁开销也最大。注意是存储引擎层实现的，上传服务器层完全不了解，它只是调API而已。如果采用了InnoDB引擎，那就是行级锁了。

事务带来更高的安全性的同时也带来了更高的开销。对于事务死锁，比如事务A要锁定行1,行2，事务B要锁定行2,行1，如果事务A先锁定1，事务B先锁定2，此时就会出现死锁，对于这种死锁数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前的处理方式是将持有最少行级排他锁的事务进行回滚。

事务的ACID 4要素：原子性，一致性，隔离性，持久性。

• Atomicity(原子性)：一个事务内的所有操作，要么全部完成，要么全部失败，不会结束在中间的某个环节。事务在执行过程中发生错误，会恢复到事务执行前的状态，就像这个事务从未执行过一样。
• Consistency(一致性)：事务的执行不能破坏数据库的完整性，即写入的数据要符合预设的规则。包括但不限于主键唯一性，列完整性（类型长度符合要求），外键约束，用户自定义的完整性。
• Isolation(隔离性)：隔离性是指事务之间相互干扰的程度，一个事务对另一个事务的干扰程度不能超过定义的隔离级别。
• Durability(持久性)：事务执行完毕后，对数据的修改是永久的，即使数据库发生故障也不会丢失。

隔离级别isolation：事务之间的隔离级别。数据更新时因为并发的问题会导致各种各样的问题：

1. 更新丢失.多个事务对同一条数据操作。在事务A未提交时,事务B进行操作,此时后提交的事务就会覆盖掉先提交的事务所做的更新,造成了更新丢失。如银行账户.用户A账户中有10元钱。同时受到用户B转账的10元和用户C转账的20元。此时如果我们使用sql.1{UPDATE USERACCOUNT SET ACCOUNT = 20 WHERe USERNAME = A}与sql.2{UPDATE USERACCOUNT SET ACCOUNT = 30 WHERe USERNAME = A}.此时无论A先提交还是B先提交都会导致先提交的那个事务所做的更改被覆盖。事实上，很多数据库都会默认在底层加锁来避免这种现象发生。

2. 脏读。一个事务查询到另一个事务未提交的数据，如果再查询之后另一个事务的更新操作被回滚就会出现脏读现象。如银行账户，用户A想买了价值1000元的商品。此时用户A余额为900元。于是就用户B给用户A转账100元。在转账事务未提交的时间，用户A看到自己账户以及变为1000元就去付款。此时B给A的转账事务被回滚。那么A付款时就会出现余额不足的清况。脏读是非常危险的。因为其读取到一个未提交的数据。一旦这个未提交的数据被回滚。那么以此查询为基础的之后任何操作在链式反应下全部都会得到期望之外的结果。

3. 不可重复读。一个事务对同一条数据的两次查询返回了不同的结果。同样以账户为例。在一个事务T里面，用户A第一次查询账户余额为1000元。查询之后用户B转账给用户A100元。此时在事务T里面再次查询用户A的余额得到为1100元。在同一个事务里面对同一条数据的两次查询获得了不同的结果就产生了不可重复读。

4. 幻读。在同一个事务中，条件相同的两次查询得到不同的结果。比如说在事务T中。查询所有姓A的客户的数量，获得结果为999位，此时又有一位A姓客户注册了，那么在事务T中再次查询姓A的客户的数量时获得结果为1000.此时便出现了幻读现象。

不可重复读和幻读都必须是在同一个事务中内的。不同的是不可重复读是对同一条数据的查询。幻读是相同条件下的一批数据的查询。

MySQL提供了4种事务隔离级别

• READ_UNCOMMITTED：读未提交。此种隔离级别下未提交的的数据可以被查询到。会出现脏读，不可重复读，幻读。
• READ_COMMITTED：读已提交。此种隔离级别下未提交的数据将不可被查询到。只有已提交的数据才可查询到。此种隔离级别不会出现脏读，但是会出现不可重复读和幻读。
• REPEATABLE_READ：可重复读。此种隔离级别下不会出现脏读和不可重复读，但是会出现幻读。
• SERIALIZABLE：序列化。最高的隔离级别。此种情况下脏读，不可重复读，幻读都不会出现。

隔离级别的提升势必会带来性能的下降。因为要花费更多的资源到事务隔离上。

事务仅在支持事务的引擎上才有用，但是事务由存储引擎实现，所以在同一个事务中使用多种存储引擎是不可靠的。如果事务中涉及了事务型表（如InnoDB）和非事务型表（如MyISAM表），正常提交情况下是没有问题的，但是在异常情况下，事务型表可以回滚，非事务型却无法执行回滚操作，破坏事务的原子性。因此如果在非事务表上产生了回滚操作，MySQL会发出一个警告：某些非事务型的表上的变更不能被回滚。正常提交下则不会有任何提示或报错。
mysql目前支持事务的引擎有InnoDB和NDB Cluster。还有一些第三方的引擎如XtraDB和PBXT。
MySQL默认采用自动提交的事务策略，可以通过AUTOCOMMIT变量控制启动和禁用。这个对于非事务引擎是没用的，毕竟非事务引擎都没事务。如MyISAM表。
事务所加的锁属于隐式加锁，InnoDB可以通过特定的SQL语句进行显示锁定。显示锁定的使用要慎重，尽量避免使用，因为严重影响性能，而且一般你都没有InnoDB自带的行级锁做的更好。

MySQL的大部分事务型引擎实现的都是行级锁，基于并发性能考虑，一般都实现了多版本并发控制(MVCC)，不仅MySQL，Oracle，PgSQL等系统也都实现了MVCC,只不过实现机制可能不同。MVCC是行级锁的变种，避免了大多数情况下的锁操作，因此开销更低，虽然机制不同，但是MVCC一般都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC是通过数据快照实现的，即整个事务期间，事务能观察到的数据都是一致的，都是事务开始时数据的快照。这就有一种可能，两个开始时间不同的事务，在同一时间观察到数据是不一致的。因此MVCC只在REPEATABLE_READ和READ_COMMITTED两个级别下工作，READ_UNCOMMITTED和SERIALIZABLE都和MVCC不兼容，这是因为READ_UNCOMMITTED总是读最新的数据，而不是符合事务版本的行，而SERIALIZABLE是对所有读取的行都加锁。
对于MVCC，InnoDB引擎下会维护一个事务版本号，每开启一个新事务，事务版本号都会自动递增并赋予这个事务，InnoDB通过在每行记录后面添加两个隐藏行来实现的，这两个隐藏的数据行一个存储过期版本号，一个存储创建版本号。在REPEATABLE_READ隔离级别下，MVCC对于不同SQL类型的操作是这样的：

• SELECT:只查询创建版本号小于或等于当前事务的事务版本号，且删除版本号未定义或大于当前事务的事务版本号的行。
• INSERT:新插入一行，并将当前事务的事务版本号赋值给该行的创建版本号
• DELETE:设置删除行的删除事务版本号为当前事务的事务版本号
• UPDATE:复制目标行并插入，并将当前事务的事务版本号赋值给新行的创建版本号，然后设置原目标行的删除事务版本号为当前事务的事务版本号。

通过这两个事务版本号，可以避免大多数读操作的加锁操作，操作简单，性能好，确定时需要一些额外的空间和维护工作。

文件系统中，MySQL将每一个数据库（也可以称为schema）保存为数据目录下的一个子目录，创建表时会在子目录下创建一个与表名相同的.frm文件保存表的定义。数据库中大小写敏感性与平台相关，如window平台大小不敏感，那么数据也不敏感。
可以用SHOW TABLE STATUS命令查看表信息，会包含占据空间大小，类型，时间等各种各样的数据。可以用ALTER TABLE tableName ENGINE = InnoDB修改存储引擎。
MySQL5.1及之前的默认引擎是MyISAM，MySQL5.1之后默认的引擎是InnoDB。在引擎选择上，除非你要使用InnoDb没有的特性，且没有其它方式替代，否则都应该优先选择InnoDB引擎。因此就只看InnoDB引擎了。

MySQL5.5之前使用的是InnoDB，MySQL5.5使用的是InnoDB plugin.这是因为Oracle收购了Sun公司，将InnoDB变成自己的了，因此MySQL5.5之前的版本可能使用的是旧的InnoDB，可以换成InnoDB plugin，性能好很多哦。除这一段之外本文所有的InnoDB其实都指的是InnoDB plugin。
InnoDB支持事务，InnoDB采用MVCC支持高并发，而是实现了4个标准的隔离级别，默认的隔离级别是REPEATABLE_READ(可重复读),并且通过间隙锁（next-key locking）策略避免了幻读。
InnoDB表基于聚簇索引建立，聚簇索引对查询有很高的性能，不过他的二级索引必须包含主键列,因此如果一个表的索引很多的话可以适当的减小主键内容，这样能减少索引的大小。InnoDB的数据文件和索引文件是平台独立的，也就是说可以把Intel平台的文件复制到PowerPC平台继续使用。
InnoDB有很多优化，比如磁盘读取数据时的可预测性预读，能够自动创建hash索引已加锁读操作的自适应哈希索引（adaptive hash index）,以及能够快速插入操作的插入缓冲去（insert buffer）等。后面细说。
InnoDB可以通过一些机制和工具实现真正的热备份，无需限制读写。MySQL其它存储引擎都不支持。

参考资料：《高性能MySQL》

PS：
【JAVA核心知识】系列导航 [持续更新中…]
关联导航：MySQL数据类型选择与设计
欢迎关注…