理解MySQL架构与概念.docx

1、理解MySQL架构与概念写在前面：最早接触的MySQL是在三年前，那时候MySQL还是4.x版本，很多功能都不支持，比如，存储过程，视图，触发器，更别说分布式事务等复杂特性了。但从5.0(2005年10月)开始，MySQL渐渐步入企业级数据库的行列了；复制、集群、分区、分布式事务，这些企业级的特性，使得现在的MySQL，完全可以应用于企业级应用环境(很多互联网公司都用其作为数据库服务器，尽管节约成本是一个因素，但是没有强大功能作后盾，则是不可想象的)。虽然，MySQL还有很多不足，比如，复制、分区的支持都十分有限、查询优化仍需要改进，但是MySQL已经是一个足够好的DBMS了，更何况它是ope

2、nsource的。这段时间没有事，出于好奇，略微的研究了一下MySQL，积累了一些资料，欲总结出来。这些资料打算分为两部分，上部主要讨论MySQL的优化，其中主要参考了MySQL Manual和High Performance MySQL，如果有时间，以后在下部分析一下MySQL的源码。如果你是MySQL高手，希望你不吝赐教；如果你是新手，希望对你有用。第一章、MySQL架构与概念1、MySQL的逻辑架构最上面不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包

3、括存储过程、触发器和视图等。最下面是存储引擎，它负责存取数据。服务器通过storage engine API可以和各种存储引擎进行交互。1.1、查询优化和执行(Optimization and Execution)MySQL将用户的查询语句进行解析，并创建一个内部的数据结构分析树，然后进行各种优化，例如重写查询、选择读取表的顺序，以及使用哪个索引等。查询优化器不关心一个表所使用的存储引擎，但是存储引擎会影响服务器如何优化查询。优化器通过存储引擎获取一些参数、某个操作的执行代价、以及统计信息等。在解析查询之前，服务器会先访问查询缓存(query cache)它存储SELECT语句以及相应的查询结

4、果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。1.2、并发控制MySQL提供两个级别的并发控制：服务器级(the server level)和存储引擎级(the storage engine level)。加锁是实现并发控制的基本方法，MySQL中锁的粒度：(1) 表级锁：MySQL独立于存储引擎提供表锁，例如，对于ALTER TABLE语句，服务器提供表锁(table-level lock)。(2) 行级锁：InnoDB和Falcon存储引擎提供行级锁，此外，BDB支持页级锁。InnoDB的并发

5、控制机制，下节详细讨论。另外，值得一提的是，MySQL的一些存储引擎（如InnoDB、BDB）除了使用封锁机制外，还同时结合MVCC机制，即多版本两阶段封锁协议(Multiversion two-phrase locking protocal)，来实现事务的并发控制，从而使得只读事务不用等待锁，提高了事务的并发性。注：并发控制是DBMS的核心技术之一(实际上，对于OS也一样)，它对系统性能有着至关重要的影响，以后再详细讨论。1.3、事务处理MySQL中，InnoDB和BDB都支持事务处理。这里主要讨论InnoDB的事务处理(关于BDB的事务处理，也十分复杂，以前曾较为详细看过其源码，以后有机会

6、再讨论)。1.3.1、事务的ACID特性事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性(Jim Gray在事务处理：概念与技术中对事务进行了详尽的讨论)。(1)原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。(2)一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。(3)隔离性（Isolation）：数据库系统提供一定的隔离机制，保证

7、事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。(4)持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。1.3.2、事务处理带来的相关问题由于事务的并发执行，带来以下一些著名的问题：(1)更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题最后的更新覆盖了由其他事务所做的更新。(2)脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致

8、状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做脏读。(3)不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。(4)幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。1.3.3、事务的隔离性SQL2标准定义了四个隔离级别。定义语句

9、如下：SET TRANSACTION ISOLATION LEVELREAD UNCOMMITTED |READ COMMITTED |REPEATABLE READ |SERIALIZABLE 这与Jim Gray所提出的隔离级别有点差异。其中READ UNCOMMITTED即Jim的10（浏览）；READ COMMITTED即20，游标稳定性；REPEATABLE READ为2.99990隔离(没有幻像保护)；SERIALIZABLE隔离级别为30，完全隔离。SQL2标准默认为完全隔离(30)。各个级别存在问题如下：隔离级脏读不可重复读幻象读读未提交(Read uncommitted)可能

10、可能可能读提交(Read committed)不可能可能可能可重复读(Repeatable read)不可能不可能可能可串行化(Serializable)不可能不可能不可能各个具体数据库并不一定完全实现了上述4个隔离级别，例如，Oracle只提供READ COMMITTED和Serializable两个标准隔离级别，另外还提供自己定义的Read only隔离级别；SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL 支持全部4个隔离级别，其默认级别为Rep

11、eatable read，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读。国产数据库DM也支持所有级别，其默认级别为READ COMMITTED。1.3.4、InnoDB的锁模型InnoDB的行级锁有两种类型：(1)共享锁(shared lock，S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。(2)排它锁(exclusive lock，X)：允许获得排它锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。此外，InnoDB支持多粒度加锁(multiple granularity locking)，从而允许对记录和表同时加锁。为此，Inno

12、DB引入意向锁(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的锁相容性矩阵：如果一个事务请求的锁模式与

13、当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。意向锁只会阻塞其它事务对表的请求，例如，LOCK TABLES WRITE，意向锁的主要目的是表明该事务将要或者正在对表中的记录加锁。使用封锁机制来进行并发控制，一个比较重要的问题就是死锁。来看一个死锁的例子：例1-1Session 1Session 2mysql CREATE TABLE t (i INT) ENGINE = InnoDB;Query OK, 0 rows affected (0.22 sec)mysql INSERT INTO t (i) VALUES(1);Query OK, 1

14、 row affected (0.08 sec)mysql START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;+-+| i|+-+|1 |+-+1 row in set (0.01 sec)mysqlmysql START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql DELETE FROM t WHERE i = 1;等待mysql DELETE FROM t WHER

15、E i = 1;等待ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionQuery OK, 1 row affected (0.00 sec)1.3.5、一致性非阻塞读一致性读是MySQL的重要特点之一，InnoDB通过MVCC机制表示数据库某一时刻的查询快照，查询可以看该时刻之前提交的事务所做的改变，但是不能看到该时刻之后或者未提交事务所做的改变。但是，查询可以看到同一事务中之前语句所做的改变，例如：例1-2Session 1Session 2mysql select *

16、from t;Empty set (0.00 sec)mysql INSERT INTO t (i) VALUES(1);Query OK, 1 row affected (0.00 sec)mysql select * from t;+-+| i |+-+| 1 |+-+1 row in set (0.00 sec)mysql set autocommit = 0;Query OK, 0 rows affected (0.01 sec)mysql update t set i=3;Query OK, 1 row affected (0.00 sec)Rows matched: 1Change

17、d: 1Warnings: 0mysql select * from t;+-+| i |+-+| 3 |+-+1 row in set (0.00 sec)mysql set autocommit = 0;Query OK, 0 rows affected (0.00 sec)mysql select * from t;+-+| i|+-+|1 |+-+1 row in set (0.00 sec)mysqlcommit;Query OK, 0 rows affected (0.06 sec)mysql select * from t;+-+| i|+-+|1 |+-+1 row in se

18、t (0.00 sec)mysqlcommit;Query OK, 0 rows affected (0.00 sec)mysql select * from t;+-+| i|+-+|3 |+-+1 row in set (0.00 sec)如果事务的隔离级别为REPEATABLE READ（默认），同一个事务中的所有一致性读都是读的事务的第一次读操作创建的快照。你可以提交当前事务，然后在新的查询中即可看到最新的快照，如上所示。如果事务的隔离级别为READ COMMITTED，一致性读只是对事务内部的读操作和它自己的快照而言的，结果如下：例1-3Session 1Session 2mysql

19、 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Query OK, 0 rows affected (0.01 sec)mysql set autocommit = 0;Query OK, 0 rows affected (0.00 sec)mysql select * from t;+-+| i|+-+|3 |+-+1 row in set (0.00 sec)mysql SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Query OK, 0 rows affecte

20、d (0.01 sec)mysql set autocommit = 0;Query OK, 0 rows affected (0.00 sec)mysql select * from t;+-+| i|+-+|3 |+-+1 row in set (0.00 sec)mysql update t set i=5;Query OK, 1 row affected (0.00 sec)Rows matched: 1Changed: 1Warnings: 0mysql select * from t;+-+| i|+-+|3|+-+1 row in set (0.00 sec)mysql comm

21、it;Query OK, 0 rows affected (0.06 sec)mysql select * from t;+-+| i|+-+|5|+-+1 row in set (0.00 sec)注意，session 2发生了不可重复读。当InnoDB在READ COMMITTED 和REPEATABLE READ隔离级别下处理SELECT语句时，一致性读是默认的模式。一致性读不会对表加任何锁，所以，其它连接可以同时改变表。假设事务处于REPEATABLE READ级别，当你正在进行一致性读时，InnoDB根据查询看到的数据给你一个时间点。如果其它的事务在该时间点之后删除一行，且提交事务，

22、你不会看到行已经被删除，插入和更新操作一样。但是，InnoDB与其它DBMS的不同是，在REPEATABLE READ隔离级别下并不会造成幻像。一致性读不与DROP TABLE 或者 ALTER TABLE一起工作。在nodb_locks_unsafe_for_binlog变量被设置或者事务的隔离级别不是SERIALIZABLE的情况下，InnoDB对于没有指定FOR UPDATE 或 LOCK IN SHARE MODE的INSERT INTO . SELECT, UPDATE . (SELECT), 和CREATE TABLE . SELECT语句使用一致性读，在这种情况下，查询语句不会对

23、表中的元组加锁。否则，InnoDB将使用锁。1.3.6、SELECT . FOR UPDATE和SELECT . LOCK IN SHARE MODE的加锁读(locking read)在一些场合，一致性读并不是很方便，此时，可以用加锁读。InnoDB支持两种加锁读：(1) SELECT . LOCK IN SHARE MODE：对读取的元组加S锁。(2) SELECT . FOR UPDATE：在扫描索引记录的过程中，会阻塞其它连接的SELECT .LOCK IN SHARE MODE和一定事务隔离级别下的读操作。InnoDB使用两阶段封锁协议，事务直到提交或回滚时才会释放所有的锁，这都是系

24、统自动执行的。此外，MySQL支持LOCK TABLES和UNLOCK TABLES，但这些都是在服务器层实现的，而不是在存储引擎。它们有用处，但是不能取代存储引擎完成事务处理，如果你需要事务功能，请使用事务型存储引擎。来考虑locking read的应用，假设你要在表child插入一个新的元组，并保证child中的记录在表parent有一条父记录。如果你用一致性读来读parent表，确实可以将要插入的child row的parent row，但是可以安全的插入吗？不，因为在你读parent表时，其它连接可能已经删除该记录。（一致性读是针对事务内而言的，对于数据库的状态，它应该叫做“不一致性读

25、”）此时，就可以使用SELECT LOCK IN SHARE MODE，它会对读取的元组加S锁，从而防止其它连接删除或更新元组。另外，如果你想在查询的同时，进行更新操作，可以使用SELECT . FOR UPDATE，它读取最新的数据，然后对读到的元组加X锁。此时，使用SELECT . LOCK IN SHARE MODE不是一个好主意，因为此时如果有两个事务进行这样的操作，就会造成死锁。注：SELECT . FOR UPDATE仅在自动提交关闭(即手动提交)时才会对元组加锁，而在自动提交时，符合条件的元组不会被加锁。1.3.7、记录锁(record lok)、间隙锁(gap lock)和后码

26、锁(next-key lock)InnoDB有以下几种行级锁：(1)记录锁：对索引记录(index records)加锁，InnoDB行级锁是通过给索引的索引项加锁来实现的，而不是对记录实例本身加锁。如果表没有定义索引，InnoDB创建一个隐藏的聚簇索引，然后用它来实现记录加锁（关于索引与加锁之间的关系的详细介绍请看下一章）。(2)间隙锁：对索引记录之间的区间，或者第一个索引记录之前的区间和最后一个索引之后的区间加锁。(3)后码锁：对索引记录加记录锁，且对索引记录之前的区间加锁。默认情况下，InnoDB的事务工作在REPEATABLE READ的隔离级别，而且系统变量innodb_locks_

27、unsafe_for_binlog为关闭状态。此时，InnoDB使用next-key锁进行查找和索引扫描，从而达到防止“幻像”的目的。Next-key锁是记录锁和间隙的结合体。当InnoDB查找或扫描表的索引时，对它遇到的索引记录加S锁或者X锁，所以，行级锁(row-level lock)实际上就是索引记录锁(index-record lock)；此外，它还对索引记录之前的区间加锁。也就是说，next-key锁是索引记录锁，外加索引记录之前的区间的间隙锁。如果一个连接对索引中的记录R持有S或X锁，其它的连接不能按照索引的顺序在R之前的区间插入一个索引记录。假设索引包含以下值：10, 11,13

28、和20，则索引的next-key锁会覆盖以下区间(“(”表示不包含，“”表示包含)：(negative infinity, 10(10, 11(11, 13(13, 20(20, positive infinity)对于最后一个区间，next-key锁将锁住索引最大值以上的区间，上界虚记录(“supremum” pseudo-record)的值比索引中的任何值都大，其实，上界不是一个真实的索引记录，所以，next-lock将对索引的最大值之后的区间加锁。间隙锁对查询唯一索引中的唯一值是没有必要的，例如，id列有唯一索引，则下面的查询仅对id=100的元组加索引记录锁(index-record

29、lock)，而不管其它连接是否在之前的区间插入元组。SELECT * FROM child WHERE id = 100;如果id没有索引，或者非唯一索引，则语句会锁住之前的空间。例1-4Session 1Session 2mysql create unique index i_index on t(i);Query OK, 0 rows affected (0.19 sec)Records: 0Duplicates: 0Warnings: 0mysql select * from t;+-+| i |+-+| 4 | 10 |+-+2 rows in set (0.00 sec)mysql set autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql select i from t where i =10 lock in share mode;+-+| i |+-+| 10 |+-+1 row in set (0.00 sec)mysql insert into t(i) values(9);Query OK, 1 row affected (0.03 sec)mysql select * from t;+-