最上层的服务井不是MySQL所独有的， 大多数基于网络的客户端／服务器的工具或者 服务都有类似的架构。 比如连接处理、 授权认证、 安全等等。
第二层架构是MySQL比较有意思的部分。 大多数MySQL的核心服务功能都在这一层，包括查询解析、 分析、 优化、 缓存以及所有的内置函数（例如， 日期、 时间、 数学和加密函数）， 所有跨存储引擎的功能都在这一层实现：存储过程、 触发器、 视图等。
第三层包含了存储引擎。 存储引擎负责MySQL中数据的存储和提取。 和GNU/Linux下的各种文件系统一样， 每个存储引擎都有它的优势和劣势。 服务器通过API与存储引擎 进行通信。 这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。 存储引擎API包含儿十个底层函数， 用千执行诸如 “开始一个事务” 或者 ”根据主键提取一行记录” 等操作。 但存储引擎不会去解析SQL注 1, 不同存储引擎之间也不会相互通信， 而只是简单地响应上层服务器的请求。
1.1.1连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程， 这个连接的查询只会在这个单独的 线程中执行， 该线程只能轮流在某个CPU核心或者CPU中运行。 服务器会负责缓存线程， 因此不需要为每一个新建的连接创建或者销毁线程注
当客户端（应用） 连接到MySQL服务器时，服务器需要对其进行认证。 认证基于用户名、原始主机信息和密码。 如果使用了安全套接字(SSL)的方式连接， 还可以使用X.509 证书认证。 一且客户端连接成功， 服务器会继续验证该客户端是否具有执行某个特定查询的权限（例如， 是否允许客户端对world数据库的Country表执行SELECT语句）。
1.2.1读写锁
这里先不讨论锁的具体实现，描述一下锁的概念如下：读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。
在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL会通过锁定防止其他用户读取同一数据。大多数时候，MySQL锁的内部管理都是透明的。
1.2.2锁粒度
在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。
每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。
表锁(table lock)
表锁是MySQL中最基本的锁策略， 并且是开销最小的策略。 表锁非常类似于前文描述 的邮箱加锁机制：它会锁定整张表。 一个用户在对表进行写操作（插入、 删除、 更新等）前， 需要先获得写锁， 这会阻塞其他用户对该表的所有读写操作。 只有没有写锁时， 其他读取的用户才能获得读锁， 读锁之间是不相互阻塞的。
在特定的场景中， 表锁也可能有良好的性能。 例如，READ LOCAL表锁支持某些类型的并发写操作。 另外， 写锁也比读锁有更高的优先级， 因此一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到锁队列中读锁的前面， 反之读锁则不能插入到写锁的前 面）。
行级锁(rowlock)
行级锁可以最大程度地支持井发处理（同时也带来了最大的锁开销）。众所周知，在 InnoDB和XtraDB,以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层（如有必要，请回顾前文的逻辑架构图）没有实现。服务器层完全不了解存储引擎中的锁实现。
1.3 事务
在理解事务的概念之前， 接触数据库系统的其他高级特性还言之过早。事务就是一组原子性的SQL查询， 或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句， 那么就执行该组查询。如果其中有任何一条语旬因为崩溃或其他原因无法执行， 那么所有的语句都不会执行。也就是说， 事务内的语句， 要么全部执行成功， 要么全部执行失败。
可以用START TRANSACTION语句开始一个事务， 然后要么使用COMMIT提交事务将修改的数据持久保留， 要么使用ROLLBACK撤销所有的修改。事务SQL的样本如下：
除非系统通过严格的ACID测试， 否则空谈事务的概念是不够的。ACID表示原子性(atomicity) 、一致性(consistency) 、隔离性(isolation) 和持久性(durability) 。一个运行良好的事务处理系统， 必须具备这些标准特征。
原子性(atomicity)
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功， 要么全部失败回滚，对千一个事务来说， 不可能只执行其中的一部分操作，这就是事务的原子性。
一致性(consistency)
数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中，一致性确保了， 即使在执行第三、四条语句之间时系统崩溃， 支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。
隔离性(isolation)
通常来说， 一个事务所做的修改在最终提交以前，对其他事务是不可见的。在前面的例子中， 当执行完第三条语句、第四条语句还未开始时， 此时有另外一个账户汇总程序开始运行，则其看到的支票账户的余额井没有被减去200美元。后面我们讨论隔离级别(Isolation level)的时候，会发现为什么我们要说“通常来说” 是不可见的。
持久性(durability)
一且事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。持久性是个有点模糊的概念， 因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障， 而有些则未必。而且不可能有能做到100%的持久性保证的策略
1.3.1隔离级别
隔离性其实比想象的要复杂。在 SQL 标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别 的隔离通常可以执行更高的并发，系统的开销也更低。
READ UNCOMMITTED (未提交读）
在READ UNCOMMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读(Dirty Read)。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED 不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。
READ COMMITTED (提交读）
大多数数据库系统的默认隔离级别都是READ COMMITTED (但MySQL 不是）。READCOMMITTED 满足前面提到的隔离性的简单定义： 一个事务开始时，只能“ 看见” 已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepe atableread), 因为两次执行同样的查询，可能会得到不一样的结果。
REPEATABLE READ (可重复读）
REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC, Multiversion Concurrency Control)解决了幻读的问题。本章稍后会做进一步的讨论。可重复读是MySQL 的默认事务隔离级别。
SERIALIZABLE (可串行化）
SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有井发的情况下，才考虑采用该级别。
1.3.2死锁
死锁是指两个或者多个事务在同一资源上相互占用， 井请求锁定对方占用的资源， 从而导致恶性循环的现象。 当多个事务试图以不同的顺序锁定资源时， 就可能会产生死锁。多个事务同时锁定同一个资源时， 也会产生死锁。InnoDB 目前处理死锁的方法是， 将持有最少行级排他锁的事务进行回滚（这是相对比较简单的死锁回 滚算法）。
1.3.3事务日志
事务日志可以帮助提高事务的效率。 使用事务日志， 存储引擎在修改表的数据时只需要修改其内存拷贝， 再把该修改行为记录到持久在硬盘上的事务日志中， 而不用每次都将修改的数据本身持久到磁盘。 事务日志采用的是追加的方式， 因此写日志的操作是磁盘 上一小块区域内的顺序1/0, 而不像随机1/0需要在磁盘的多个地方移动磁头， 所以采用 事务日志的方式相对来说要快得多。 事务日志持久以后， 内存中被修改的数据在后台可以慢慢地刷回到磁盘。 目前大多数存储引擎都是这样实现的， 我们通常称之为预写式日 志 (Write-Ahead Logging), 修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志井持久化， 但数据本身还没有写回磁盘， 此时系统崩溃， 存储引擎在重启时能够自动恢复这部分修改的数据。 具体的恢复方式则视存储引擎而定。
1.3.4 MySQL中的事务
MySQL 提供了两种事务型的存储引擎： InnoDB 和NOB Cluster。另外还有一些第三方存储引擎也支持事务， 比较知名的包括XtraDB 和PBXT。后面将详细讨论它们各自的一些特点。
自动提交(AUTOCOMMIT)
MySQL 默认采用自动提交(AUTO C OMMIT) 模式。也就是说， 如果不是显式地开始一个事务， 则每个查询都被当作一个事务执行提交操作。在当前连接中， 可以通过设置AUTOCOMMIT 变扯来启用或者禁用自动提交模式：
在事务中混合使用存储引擎
MySQL服务器层不管理事务， 事务是由下层的存储引擎实现的。 所以在同一个事务中， 使用多种存储引擎是不可靠的。
如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表），在正常提交的情况下不会有什么问题。
但如果该事务需要回滚， 非事务型的表上的变更就无法撤销，这会导致数据库处千不一致的状态，这种情况很难修复， 事务的最终结果将无法确定。 所以， 为每张表选择合适的存储引擎非常重要。
隐式和显式锁定
InnoDB采用的是两阶段锁定协议(two-phase locking protocol)。 在事务执行过程中，随时都可以执行锁定， 锁只有在执行COMMIT 或者ROLLBACK的时候才会释放，并且所有的 锁是在同一时刻被释放。 前面描述的锁定都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。
1.4多版本并发控制
MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。 基于提升井发性能的考虑，它们一般都同时实现了多版本并发控制(MVCC)。
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。 也就是说，不管需要执 行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
InnoDB的MVCC, 是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号(system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。
SELECT
InnoDB会根据以下两个条件检查每行记录：
a. InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等千事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
b. 行的删除版本要么未定义，要么大千当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果。
INSERT
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE
InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，井且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。
MVCC只在REPEATABLE READ 和READ COMMITTED 两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容注4 , 因为READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。
1.5.1 lnnoDB存储引擎
InnoDB的数据存储在表空间( tablespace)中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。
InnoDB采用MVCC来支持高井发， 并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ( 可重复读），并且通过间隙锁( next-key locking)策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的，我们会在后面的章节详细讨论聚簇索引。InnoDB的索引结构和MySQL的其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。不过它的二级索引(secondar y ind ex , 非主键索引） 中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。InnoDB的存储格式是平台独立的，也就是说可以将数据和索引文件从Intel平台复制到PowerPC或者Sun SPARC平台。
1.5.5选择合适的引擎
除非万不得已， 否则建议不要混合使用多种存储引擎， 否则可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。存储引擎层和服务器层的交互已经比较复杂， 更不用说混合多个存储引擎了。至少， 混合存储对一致性备份和服务器参数配置都带来了一些困难。
如果应用需要不同的存储引擎， 请先考虑以下几个因素。
事务
如果应用需要事务支持， 那么InnoDB(或者XtraDB)是目前最稳定并且经过验证的选择。如果不需要事务， 并且主要是SELECT和INSERT操作， 那么MyISAM 是不错的选择。一般日志型的应用比较符合这一特性。
备份
备份的需求也会影响存储引擎的选择。如果可以定期地关闭服务器来执行备份， 那么备份的因素可以忽略。反之，如果需要在线热备份，那么选择InnoDB就是基本的要求。
崩溃恢复
数据量比较大的时候，系统崩溃后如何快速地恢复是一个需要考虑的问题。相对而言，MylSAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢。因此，即使不需要事务支持，很多人也选择InnoDB引擎，这是一个非常重要的因素。
特有的特性
最后，有些应用可能依赖一些存储引擎所独有的特性或者优化，比如很多应用依赖聚簇索引的优化。另外，MySQL中也只有MyISAM支持地理空间搜索。如果一个存储引擎拥有一些关键的特性，同时却又缺乏一些必要的特性，那么有时候不得不做折中的考虑，或者在架构设计上做一些取舍。某些存储引擎无法直接支持的特性，有时候通过变通也可以满足需求。
日志型应用
利用MySQL内置的复制方案将数据复制一份到备库，然后在备库上执行比较消耗时间和CPU的查询。这样主库只用千高效的插入工作，而备库上执行的查询也无须担心影响到日志的插入性能。当然也可以在系统负载较低的时候执行报表查询操作，但应用在不断变化，如果依赖这个策略可能以后会导致问题。
网友评论