理解MySQL――架构与概念

写在前面：最早接触的MySQL是在三年前，那时候MySQL还是4.x版本，很多功能都不支持，比如，存储过程，视图，触发器，更别说分布式事务等复杂特性了。但从5.0(2005年10月)开始，MySQL渐渐步入企业级数据库的行列了；复制、集群、分区、分布式事务，这些企业级的特性，使得现在的MySQL，完全可以应用于企业级应用环境(很多互联网公司都用其作为数据库服务器，尽管节约成本是一个因素，但是没有强大功能作后盾，则是不可想象的)。虽然，MySQL还有很多不足，比如，复制、分区的支持都十分有限、查询优化仍需要改进，但是MySQL已经是一个足够好的DBMS了，更何况它是opensource的。这段时间没有事，出于好奇，略微的研究了一下MySQL，积累了一些资料，欲总结出来。这些资料打算分为两部分，上部主要讨论MySQL的优化，其中主要参考了《MySQL Manual》和《High Performance MySQL》，如果有时间，以后在下部分析一下MySQL的源码。如果你是MySQL高手，希望你不吝赐教；如果你是新手，希望对你有用。

第一章、MySQL架构与概念
1、MySQL的逻辑架构

 最上面不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。
中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。
最下面是存储引擎，它负责存取数据。服务器通过storage engine API可以和各种存储引擎进行交互。

1.1、查询优化和执行(Optimization and Execution)
MySQL将用户的查询语句进行解析，并创建一个内部的数据结构——分析树，然后进行各种优化，例如重写查询、选择读取表的顺序，以及使用哪个索引等。查询优化器不关心一个表所使用的存储引擎，但是存储引擎会影响服务器如何优化查询。优化器通过存储引擎获取一些参数、某个操作的执行代价、以及统计信息等。在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
 1.2、并发控制
MySQL提供两个级别的并发控制：服务器级(the server level)和存储引擎级(the storage engine level)。加锁是实现并发控制的基本方法，MySQL中锁的粒度：
(1)    表级锁：MySQL独立于存储引擎提供表锁，例如，对于ALTER TABLE语句，服务器提供表锁(table-level lock)。
(2)    行级锁：InnoDB和Falcon存储引擎提供行级锁，此外，BDB支持页级锁。InnoDB的并发控制机制，下节详细讨论。
另外，值得一提的是，MySQL的一些存储引擎（如InnoDB、BDB）除了使用封锁机制外，还同时结合MVCC机制，即多版本两阶段封锁协议(Multiversion two-phrase locking protocal)，来实现事务的并发控制，从而使得只读事务不用等待锁，提高了事务的并发性。
注：并发控制是DBMS的核心技术之一(实际上，对于OS也一样)，它对系统性能有着至关重要的影响，以后再详细讨论。

1.3、事务处理
MySQL中，InnoDB和BDB都支持事务处理。这里主要讨论InnoDB的事务处理(关于BDB的事务处理，也十分复杂，以前曾较为详细看过其源码，以后有机会再讨论)。
1.3.1、事务的ACID特性
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性(Jim Gray在《事务处理：概念与技术》中对事务进行了详尽的讨论)。
(1)原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
(2)一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
(3)隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
(4)持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
1.3.2、事务处理带来的相关问题
由于事务的并发执行，带来以下一些著名的问题：
(1)更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。
(2)脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
(3)不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
(4)幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
1.3.3、事务的隔离性
SQL2标准定义了四个隔离级别。定义语句如下：
SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED |
READ COMMITTED  |
REPEATABLE READ  |
SERIALIZABLE ]
这与Jim Gray所提出的隔离级别有点差异。其中READ UNCOMMITTED即Jim的10（浏览）；READ COMMITTED即20，游标稳定性；REPEATABLE READ为2.99990隔离(没有幻像保护)；SERIALIZABLE隔离级别为30，完全隔离。SQL2标准默认为完全隔离(30)。各个级别存在问题如下：

各个具体数据库并不一定完全实现了上述4个隔离级别，例如，Oracle只提供READ COMMITTED和Serializable两个标准隔离级别，另外还提供自己定义的Read only隔离级别；SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL 支持全部4个隔离级别，其默认级别为Repeatable read，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读。国产数据库DM也支持所有级别，其默认级别为READ COMMITTED。

1.3.4、InnoDB的锁模型
InnoDB的行级锁有两种类型：
(1)共享锁(shared lock，S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
(2)排它锁(exclusive lock，X)：允许获得排它锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
此外，InnoDB支持多粒度加锁(multiple granularity locking)，从而允许对记录和表同时加锁。为此，InnoDB引入意向锁(intention locks)，意向锁是针对表的：
(1)意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
(2)意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
例如，SELECT ... LOCK IN SHARE MODE加IS锁，SELECT ... FOR UPDATE加IX锁，意向锁的规则如下：
(1)事务在对表T中的记录获取S锁前，先要获取表T的IS锁或者更强的锁；
(2)事务在获取表T中记录的X锁前，先要获取表T的IX锁。
InnoDB的锁相容性矩阵：

 如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。意向锁只会阻塞其它事务对表的请求，例如，LOCK TABLES …WRITE，意向锁的主要目的是表明该事务将要或者正在对表中的记录加锁。使用封锁机制来进行并发控制，一个比较重要的问题就是死锁。
来看一个死锁的例子：
例1-1