理解MySQL

理解MySQL——架构与概念写在前面：最早接触的MySQL是在三年前，那时候MySQL还是4.x版本，很多功能都不支持，比如，存储过程，视图，触发器，更别说分布式事务等复杂特性了。但从5.0(2005年10月)开始，MySQL渐渐步入企业级数据库的行列了；复制、集群、分区、分布式事务，这些企业级的特性，使得现在的MySQL，完全可以应用于企业级应用环境(很多互联网公司都用其作为数据库服务器，尽管节约成本是一个因素，但是没有强大功能作后盾，则是不可想象的)。虽然，MySQL还有很多不足，比如，复制、分区的支持都十分有限、查询优化仍需要改进，但是MySQL已经是一个足够好的DBMS了，更何况它是opensource的。这段时间没有事，出于好奇，略微的研究了一下MySQL，积累了一些资料，欲总结出来。这些资料打算分为两部分，上部主要讨论MySQL的优化，其中主要参考了《MySQLManual》和《HighPerformanceMySQL》，如果有时间，以后在下部分析一下MySQL的源码。如果你是MySQL高手，希望你不吝赐教；如果你是新手，希望对你有用。第一章、MySQL架构与概念1、MySQL的逻辑架构最上面不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。最下面是存储引擎，它负责存取数据。服务器通过storageengineAPI可以和各种存储引擎进行交互。1.1、查询优化和执行(OptimizationandExecution)MySQL将用户的查询语句进行解析，并创建一个内部的数据结构——分析树，然后进行各种优化，例如重写查询、选择读取表的顺序，以及使用哪个索引等。查询优化器不关心一个表所使用的存储引擎，但是存储引擎会影响服务器如何优化查询。优化器通过存储引擎获取一些参数、某个操作的执行代价、以及统计信息等。在解析查询之前，服务器会先访问查询缓存(querycache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。1.2、并发控制MySQL提供两个级别的并发控制：服务器级(theserverlevel)和存储引擎级(thestorageenginelevel)。加锁是实现并发控制的基本方法，MySQL中锁的粒度：(1)表级锁：MySQL独立于存储引擎提供表锁，例如，对于ALTERTABLE语句，服务器提供表锁(table-levellock)。(2)行级锁：InnoDB和Falcon存储引擎提供行级锁，此外，BDB支持页级锁。InnoDB的并发控制机制，下节详细讨论。另外，值得一提的是，MySQL的一些存储引擎（如InnoDB、BDB）除了使用封锁机制外，还同时结合MVCC机制，即多版本两阶段封锁协议(Multiversiontwo-phraselockingprotocal)，来实现事务的并发控制，从而使得只读事务不用等待锁，提高了事务的并发性。注：并发控制是DBMS的核心技术之一(实际上，对于OS也一样)，它对系统性能有着至关重要的影响，以后再详细讨论。1.3、事务处理MySQL中，InnoDB和BDB都支持事务处理。这里主要讨论InnoDB的事务处理(关于BDB的事务处理，也十分复杂，以前曾较为详细看过其源码，以后有机会再讨论)。1.3.1、事务的ACID特性事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性(JimGray在《事务处理：概念与技术》中对事务进行了详尽的讨论)。(1)原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。(2)一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。(3)隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。(4)持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。1.3.2、事务处理带来的相关问题由于事务的并发执行，带来以下一些著名的问题：(1)更新丢失（LostUpdate）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。(2)脏读（DirtyReads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做脏读。(3)不可重复读（Non-RepeatableReads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。(4)幻读（PhantomReads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。1.3.3、事务的隔离性SQL2标准定义了四个隔离级别。定义语句如下：SETTRANSACTIONISOLATIONLEVEL[READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE]这与JimGray所提出的隔离级别有点差异。其中READUNCOMMITTED即Jim的10（浏览）；READCOMMITTED即20，游标稳定性；REPEATABLEREAD为2.99990隔离(没有幻像保护)；SERIALIZABLE隔离级别为30，完全隔离。SQL2标准默认为完全隔离(30)。各个级别存在问题如下：隔离级脏读不可重复读幻象读读未提交(Readuncommitted)可能可能可能读提交(Readcommitted)不可能可能可能可重复读(Repeatableread)不可能不可能可能可串行化(Serializable)不可能不可能不可能各个具体数据库并不一定完全实现了上述4个隔离级别，例如，Oracle只提供READCOMMITTED和Serializable两个标准隔离级别，另外还提供自己定义的Readonly隔离级别；SQLServer除支持上述ISO/ANSISQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL支持全部4个隔离级别，其默认级别为Repeatableread，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读。国产数据库DM也支持所有级别，其默认级别为READCOMMITTED。1.3.4、InnoDB的锁模型InnoDB的行级锁有两种类型：(1)共享锁(sharedlock，S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。(2)排它锁(exclusivelock，X)：允许获得排它锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。此外，InnoDB支持多粒度加锁(multiplegranularitylocking)，从而允许对记录和表同时加锁。为此，InnoDB引入意向锁(intentionlocks)，意向锁是针对表的：(1)意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。(2)意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。例如，SELECT...LOCKINSHAREMODE加IS锁，SELECT...FORUPDATE加IX锁，意向锁的规则如下：(1)事务在对表T中的记录获取S锁前，先要获取表T的IS锁或者更强的锁；(2)事务在获取表T中记录的X锁前，先要获取表T的IX锁。InnoDB的锁相容性矩阵：如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。意向锁只会阻塞其它事务对表的请求，例如，LOCKTABLES…WRITE，意向锁的主要目的是表明该事务将要或者正在对表中的记录加锁。使用封锁机制来进行并发控制，一个比较重要的问题就是死锁。来看一个死锁的例子：例1-1Session1Session2mysqlCREATETABLEt(iINT)ENGINE=InnoDB;QueryOK,0rowsaffected(0.22sec)mysqlINSERTINTOt(i)VALUES(1);QueryOK,1rowaffected(0.08sec)mysqlSTARTTRANSACTION;QueryOK,0rowsaffected(0.00sec)mysqlSELECT*FROMtWHEREi=1LOCKINSHAREMODE;+------+|i|+------+|1|+------+1rowinset(0.01sec)mysqlmysqlSTARTTRANSACTION;QueryOK,0rowsaffected(0.00sec)mysqlDELETEFROMtWHEREi=1;等待…mysqlDELETEFROMtWHEREi=1;等待…ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransactionQueryOK,1rowaffected(0.00sec)1.3.5、一致性非阻塞读一致性读是MySQL的重要特点之一，InnoDB通过MVCC机制表示数据库某一时刻的查询快照，查询可以看该时刻之前提交的事务所做的改变，但是不能看到该时刻之后或者未提交事务所做的改变。但是，查询可以看到同一事务中之前语句所做的改变，例如：例1-2Session1Session2mysqlselect*fromt;Emptyset(0.00sec)mysqlINSERTINTOt(i)VALUES(1);QueryOK,1rowaffected(0.00sec)mysqlselect*fromt;+------+|i|+------+|1|+------+1rowinset(0.00sec)mysqlsetautocommit=0;QueryOK,0rowsaffected(0.01sec)mysqlupdatetseti=3;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysqlselect*fromt;+------+|i|+------+|3|+------+1rowinset(0.00sec)mysqlsetautocommit=0;QueryOK,0rowsaffected(0.00sec)mysqlselect*fromt;+------+|i|+------+|1|+------+1rowinset(0.00sec)mysqlcommit;QueryOK,0rowsaffected(0.06sec)mysqlselect*fromt;+------+|i|+------+|1|+------+1rowinset(0.00sec)mysqlcommit;QueryOK,0rowsaffected(0.0