MySQL数据库的体系架构

MySQL数据库的体系架构如下图所示：

从上图中可以看出，MySQL主要分为以下几个组件：

• 连接池组件
• 管理服务和工具组件
• SQL接口组件
• 分析器组件
• 优化器组件
• 缓冲组件
• 插件式存储引擎
• 物理文件

下表显示了各种存储引擎的特性：

其中最常见的两种存储引擎是MyISAM和InnoDB

刚接触MySQL的时候可能会有些惊讶，竟然有不支持事务的存储引擎，学过关系型数据库理论的人都知道，事务是关系型数据库的核心。但是在现实应用中（特别是互联网），为了提高性能，在某些场景下可以摈弃事务。下面一一介绍各种存储引擎：

MyISAM存储引擎

MyISAM是MySQL官方提供默认的存储引擎，其特点是不支持事务、表锁和全文索引，对于一些OLAP系统，操作速度快。

每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。这里特别要注意的是MyISAM不缓存数据文件，只缓存索引文件。

InnoDB存储引擎

InnoDB存储引擎支持事务，主要面向OLTP方面的应用，其特点是行锁设置、支持外键，并支持类似于Oracle的非锁定读，即默认情况下读不产生锁。InnoDB将数据放在一个逻辑表空间中（类似Oracle）。InnoDB通过多版本并发控制来获得高并发性，实现了ANSI标准的4种隔离级别，默认为Repeatable，使用一种被称为next-key locking的策略避免幻读。

对于表中数据的存储，InnoDB采用类似Oracle索引组织表Clustered的方式进行存储。

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索
引。

NDB存储引擎

NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC，但它是Share Nothing的架构，因此能提供更高级别的高可用性和可扩展性。NDB的特点是数据全部放在内存中，因此通过主键查找非常快。

关于NDB，有一个问题需要注意，它的连接(join)操作是在MySQL数据库层完成，不是在存储引擎层完成，这意味着，复杂的join操作需要巨大的网络开销，查询速度会很慢。

Memory (Heap) 存储引擎

Memory存储引擎（之前称为Heap）将表中数据存放在内存中，如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。

Archive存储引擎

正如其名称所示，Archive非常适合存储归档数据，如日志信息。它只支持INSERT和SELECT操作，其设计的主要目的是提供高速的插入和压缩功能。

Federated存储引擎

Federated存储引擎不存放数据，它至少指向一台远程MySQL数据库服务器上的表，非常类似于Oracle的透明网关。

Maria存储引擎

Maria存储引擎是新开发的引擎，其设计目标是用来取代原有的MyISAM存储引擎，从而成为MySQL默认的存储引擎。

InnoDB是事务安全的存储引擎，设计上借鉴了很多Oracle的架构思想，一般而言，在OLTP应用中，InnoDB应该作为核心应用表的首先存储引擎。InnoDB是由第三方的Innobase Oy公司开发，现已被Oracle收购，创始人是Heikki Tuuri，芬兰赫尔辛基人，和著名的Linux创始人Linus是校友。

InnoDB体系架构

上面是InnoDB的一个简图，简单来说，InnoDB是由一系列后台线程和一大块内存组成。

后台线程

默认情况下，InnoDB的后台线程有7个 —— 4个IO thread, 1个master thread, 1个lock monitor thread, 一个error monitor thread

内存

InnoDB的内存主要有以下几个部分组成：缓冲池 (buffer pool)、重做日志缓冲池(redo log buffer）以及额外的内存池（additional memory pool），如下图所示：

其中缓冲池占最大块内存，用来缓存各自数据，数据文件按页（每页16K）读取到缓冲池，按最近最少使用算法（LRU）保留缓存数据。

缓冲池缓冲的数据类型有：数据页、索引页、插入缓冲、自适应哈希索引、锁信息、数据字典信息等，其中数据页和索引页占了绝大部分内存。

日志缓冲将重做日志信息先放入这个缓冲区，然后按一定频率（默认为1s）将其刷新至重做日志文件。

Master 后台线程

InnoDB的主要工作都是在一个单独的Master线程里完成的。Master线程的优先级最高，它主要分为以下几个循环：主循环（loop）、后台循环（background loop）、刷新循环（flush loop）、暂停循环（suspend loop）。

先来看看主循环，下面是它的伪代码：

[plain]  view plain copy print ?

1. void master_thread() (  
2. loop:  
3. for (int i =0; i <10; i++){  
4.     do thing once per second  
5.     sleep 1 second if necessary  
6. }  
7. do things once per ten seconds  
8. goto loop;  
9. }  

其中每秒一次的操作包括：

• 刷新日志缓冲区（总是）
• 合并插入缓冲（可能）
• 至多刷新100个脏数据页（可能）
• 如果没有当前用户活动，切换至background loop （可能）

和Oracle类似，即使事务未提交，也会每秒刷新重做日志缓冲区。

其中每10秒一次的操作包括：

• 合并至多5个插入缓冲（总是）
• 刷新日志缓冲（总是）
• 刷新100个或10个脏页到磁盘（总是）
• 产生一个检查点（总是）
• 删除无用Undo 页 （总是）

接着来看后台循环，若当前没有用户活动或数据库关闭时，会切换至该循环执行以下操作：

• 删除无用的undo页（总是）
• 合并20个插入缓冲（总是）
• 跳回到主循环（总是）
• 不断刷新100个页，直到符合条件跳转到flush loop（可能）

如果flush loop中也没有什么事情可做，边切换到suspend loop，将master线程挂起。

InnoDB存储引擎有三大特性非常令人激动，它们分别是插入缓冲、两次写和自适应哈希，本篇文章先介绍第一个特性 - 插入缓冲（insert buffer）

在上一篇《MySQL - 浅谈InnoDB存储引擎》中，我们可以看到在InnoDB的内存中有单独一块叫“插入缓冲”的区域，下面我们详细来介绍它。

非聚集索引写性能问题

为了阐述非聚集索引写性能问题，我们先来看一个例子：

mysql>create table t (
           id int auto_increment,
           name varchar(30),
           primary key (id));


我们创建了一个表，表的主键是id，id列式自增长的，即当执行插入操作时，id列会自动增长，页中行记录按id顺序存放，不需要随机读取其它页的数据。因此，在这样的情况下（即聚集索引），插入操作效率很高。

但是，在大部分应用中，很少出现表中只有一个聚集索引的情况，更多情况下，表上会有多个非聚集的secondary index （辅助索引）。比如，对于上一张表t，业务上还需要按非唯一的name字段查找，则表定义改为：

mysql>create table t (
           id int auto_increment,
           name varchar(30),
           primary key (id),
           key (name));

这时，除了主键聚合索引外，还产生了一个name列的辅助索引，对于该非聚集索引来说，叶子节点的插入不再有序，这时就需要离散访问非聚集索引页，插入性能变低。

插入缓冲技术机制

为了解决这个问题，InnoDB设计出了插入缓冲技术，对于非聚集类索引的插入和更新操作，不是每一次都直接插入到索引页中，而是先插入到内存中。具体做法是：如果该索引页在缓冲池中，直接插入；否则，先将其放入插入缓冲区中，再以一定的频率和索引页合并，这时，就可以将同一个索引页中的多个插入合并到一个IO操作中，大大提高写性能。回忆一下在《 MySQL - 浅谈InnoDB存储引擎 》中提到的master thread主循环其中的一项工作就是每秒中合并插入缓冲（可能）。

这个设计思路和HBase中的LSM树有相似之处，都是通过先在内存中修改，到达一定量后，再和磁盘中的数据合并，目的都是为了提高写性能，具体可参考《HBase LSM树》，这又再一次说明，学到最后，技术都是相通的。

插入缓冲的启用需要满足一下两个条件：
1）索引是辅助索引（secondary index）
2）索引不适合唯一的
如果辅助索引是唯一的，就不能使用该技术，原因很简单，因为如果这样做，整个索引数据被切分为2部分，无法保证唯一性。

插入缓冲带来的问题

任何一项技术在带来好处的同时，必然也带来坏处。插入缓冲主要带来如下两个坏处：
1）可能导致数据库宕机后实例恢复时间变长。如果应用程序执行大量的插入和更新操作，且涉及非唯一的聚集索引，一旦出现宕机，这时就有大量内存中的插入缓冲区数据没有合并至索引页中，导致实例恢复时间会很长。
2）在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认情况下最大可以占用1/2，这在实际应用中会带来一定的问题。

今天我们来介绍InnoDB存储引擎的第二个特性 - 两次写（doublewrite），如果说插入缓冲是为了提高写性能的话，那么两次写是为了提高可靠性，牺牲了一点点写性能。

部分写失效

想象这么一个场景，当数据库正在从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。这时是无法通过重做日志恢复的，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

两次写机制

从上面分析我们知道，在部分写失效的情况下，我们在应用重做日志之前，需要原始页的一个副本，两次写就是为了解决这个问题，下面是它的原理图：

两次写需要额外添加两个部分：

1）内存中的两次写缓冲（doublewrite buffer），大小为2MB

2）磁盘上共享表空间中连续的128页，大小也为2MB

其原理是这样的：

1）当刷新缓冲池脏页时，并不直接写到数据文件中，而是先拷贝至内存中的两次写缓冲区。

2）接着从两次写缓冲区分两次写入磁盘共享表空间中，每次写入1MB

3）待第2步完成后，再将两次写缓冲区写入数据文件

这样就可以解决上文提到的部分写失效的问题，因为在磁盘共享表空间中已有数据页副本拷贝，如果数据库在页写入数据文件的过程中宕机，在实例恢复时，可以从共享表空间中找到该页副本，将其拷贝覆盖原有的数据页，再应用重做日志即可。

其中第2步是额外的性能开销，但由于磁盘共享表空间是连续的，因此开销不是很大。可以通过参数skip_innodb_doublewrite禁用两次写功能，默认是开启的，强烈建议开启该功能。

哈希索引是一种非常快的等值查找方法（注意：必须是等值，哈希索引对非等值查找方法无能为力），它查找的时间复杂度为常量，InnoDB采用自适用哈希索引技术，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”（详见《MySQL - 浅谈InnoDB体系架构》中内存构造）建立哈希索引。

之所以该技术称为“自适应”是因为完全由InnoDB自己决定，不需要DBA人为干预。它是通过缓冲池中的B+树构造而来，且不需要对整个表建立哈希索引，因此它的数据非常快。

InnoDB官方文档显示，启用自适应哈希索引后，读和写性能可以提高2倍，对于辅助索引的连接操作，性能可以提高5被，因此默认情况下为开启，我们可以通过参数innodb_adaptive_hash_index来禁用此特性。

这里介绍的日志文件都是MySQL数据库本身的文件，和具体用什么存储引擎无关。

错误日志

MySQL的错误日志类似于Oracle的alert.log，默认情况下以.err结尾，DBA在遇到问题时，首先应该查询该日志获得错误信息。

查询日志

查询日志记录了所有的数据库请求，即时这些请求没有得到正确的执行。

慢查询日志

慢查询日志用于记录运行时间比较长的SQL语句，可以通过参数long_query_time来设置该阀值。默认情况下，MySQL并不启动慢查询日志，可以通过设置log_slow_queries为ON启动它。

另一个和慢查询日志相关的参数是log_queries_not_using_indexes，该参数为ON表示如果运行的SQL语句没有使用索引，就将其记录到慢查询日志中。

慢查询日志主要用于协助DBA进行SQL语句的优化。

二进制日志

二进制日志记录了所有数据库的更改操作（SELECT和SHOW不包含在里面），二进制文件在默认情况下没有启动，需要手动指定参数启动。

二进制日志主要有以下两个作用：

1）恢复（recovery）：当一个数据库从全备文件恢复后，我们可以通过二进制日志进行point-in-time恢复。

2）复制（replication）：可以利用它实行从数据库的实时同步。

开启二进制文件会影响性能（根据官方文档显示，开启二进制文件使得数据库性能下降1%左右），但考虑到它带来的好处，这些性能损失还是可以接受的。

MySQL一个显著的特点是其可插拔的存储引擎，因此MySQL文件分为两种，一种是和MySQL数据库本身相关的文件，一种是和存储引擎相关的文件。本文主要介绍和InnoDB存储引擎相关的文件。

表空间文件

InnoDB在存储上也模仿了Oracle的设计，数据按表空间进行存储，但是和Oracle不一样的是，Oracle的表空间是个逻辑的概念，而InnoDB的表空间是个物理的概念。

你可以通过参数innodb_data_file_path来设置默认的表空间文件，所有基于InnoDB存储引擎的表都会存储在该文件内。如果你想基于每个表单独生成一个表空间文件，可以设置参数innodb_file_per_table为ON，这样表的数据、索引和插入缓冲等消息存储在单独的表空间文件中，但其余信息还是存储在默认的表空间文件中。

下图显示了表空间文件的存储方式：

上图中主要有以下几种存储文件：

1）表结构定义文件(.frm)：MySQL里每个表和每个视图都有一个对应的.frm文件用于记录表和视图的定义。注意：该文件和存储引擎无关，属于MySQL数据库本身的文件。

2）默认表空间文件(ibdata)

3）单独表空间文件(.ibd)

重做日志文件

重做日志文件对InnoDB存储引擎至关重要，记录了事务日志，如果数据库由于宕机导致实例失败，重新启动时，就可以利用重做日志恢复到宕机前的一致性状态。

MySQL的重做日志和Oracle的很类似，通过循环的方式覆盖重用，下图显示了一个拥有3个重做日志文件的重做日志文件组：

这里有个疑问，同样是记录事务日志，InnoDB的重做日志文件和MySQL自身的二进制文件有什么区别？它们的区别主要有以下三点：

1）首先，范围不同。二进制文件记录所有与MySQL相关的日志记录，包括InnoDB，MyISAM，Heap等存储引擎的日志。而InnoDB的重做日志只记录InnoDB相关的事务日志。

2）其次，内容不同。二进制文件记录的是关于一个事务的具体操作内容，而InnoDB的重做日志记录每个数据页（page）更改的物理情况。

3）写入的时间不同。二进制文件在事务提交之前记录，在事务进行过程中，不断有重做日志条目写入重做日志文件中。

InnoDB存储引擎中的表非常像Oracle中的索引组织表，每张表必须得有主键，如果表在创建时没有显示定义主键，则根据以下原则自动创建主键：

1）如果有非空的唯一索引，则该索引所在的列为主键；

2）如果不符合上述条件，自动创建一个6个字节的指针为主键。

InnoDB存储引擎的逻辑存储结构和Oracle几乎一样，从大到小分别为：表空间、段、区、页，它们的关系如下图所示：

表空间

在上一篇《MySQL InnoDB文件介绍》中，我们知道InnoDB有一个默认的表空间，如果我们启用了参数innodb_file_per_table，则针对每张可以单独放在表空间里。这里需要注意的是，即时启用了innodb_file_per_table，也并不是表中所有的数据都单独放在自己的表空间里，单独表空间只存放数据、索引和插入缓冲，其它如Undo、系统事务信息、二次写缓冲等还是存放在默认共享表空间里。

段

表空间有若干各段组成，常见的有数据段、索引段、回滚段等。前面提到InnoDB中的表是索引组织表，因此数据段也称为leaf node segment，索引段也称为non-leaf node segment。

区

每64个连续的页组成区，因此区大小正好为1M。

页

页是InnoDB磁盘管理的最小单位，固定大小为16K，不可以更改（也许通过更改源码可以修改固定大小）。

行

InnoDB表中数据按行存储。

InnoDB和大多数行式数据库一样，记录以行的格式存储，它提供了两种格式：Compact和Redundant

Compact

Compact格式是在MySQL5.0时才被引入，它是新的行格式，其设计目标是高效存放数据，示意图如下：

1）变长字段长度列表。按列的逆序放置，当列长度小于255字节，用1字节表示，若大于255字节，用2个字节表示，至多为2字节（这也很好解释了InnoDB中varchar的最大长度为什么是65535，因为2个字节为16位，2的16次方-1）。注意，对于固定长度字段（如char），是不需要记录其长度的。

2）NULL标志位。指示该行中的列是否为NULL，1表示NULL。

3）记录头信息。固定占用5个字节（40位），每位代表的含义如下表所示：

4）最后就是实际存储的该行每列的数据了，注意：NULL不在该部分中占用存储。

5）此外还有两个隐藏部分，分别存放事务ID和回滚指针，大小分别为6字节和7字节，如果表没有定义主键，每行还会自动增加6字节的ROWID。

Redundant

Redundant是MySQL5.0之前InnoDB的行记录存储格式，其示意图如下：

1）字段长度偏移列表。同样按列的逆序放置，当列长度小于255字节，用1字节表示，若大于255字节，用2个字节表示。

2）记录头信息。固定占用6个字节（48位），每位含义见下表：

3）最后部分是实际存储各个列数据。

4）和Compact一样，还有两个隐藏部分，分别存放事务ID和回滚指针，大小分别为6字节和7字节，如果表没有定义主键，每行还会自动增加6字节的ROWID。

Redundant和Compact最大的不同是对Char类型NULL值的处理，Redundant会实际存储NULL值，占Char类型固定长度空间，而Compact不占用空间，这也是Compact能寸更多记录的原因之一。注意：对于varchar类型，不管是Compact还是Redundant都不存储NULL值。

Compressed和Dynamic

InnoDB Plugin引入了新的文件格式，称为Barracuda（之前的Compact和Redundant称为Antelope），拥有两种权限的记录格式Compressed和Dynamic。这里就不详细介绍它们了，有兴趣的童鞋可以自行研究下。

在实际工作中，常听到初学者说：“大表分区肯定可以提高查询性能”。其实不然，在你完全不了解应用的情况下，盲目的建立分区不但不能够提高查询性能，还有可能导致查询性能下降。因此，在决定是否使用分区之前，必须了解当前的应用环境。

大体上来说，数据库的应用分为两种：OLTP和OLAP。OLTP是指在线事务处理，比如淘宝的购物网站等；OLAP是指在线分析系统，如数据仓库、数据集市等。实际应用中，也有可能出现同一个数据库系统即用于OLTP，也用于OLAP。

对于OLAP应用来说，分区确实能够有效地提高查询性能，因为OLAP通常需要扫描大量的数据（执行计划为全表扫描），分区可以有效地减少扫描的数据量（即分区剪枝 partition elimination）。举个例子，假设有张大表存放了5年的数据，你的应用只需要其中1个月的数据，在没分区之前，需要扫描所有5年的数据，但有了分区之后（假设以时间戳为分区键），只需要扫描1个月的数据即可。而且对于OLAP应用来说，分区还有一个好处就是易于管理，如果你需要对历史数据进行迁移，有分区就非常方便。

但对于OLTP应用来说，分区要非常小心。因为OLTP通常指需要获得很小的一部分数据，通过索引访问是最高效的。根据B+树的原理可知，即使表再大，B+树的高度一般也就是2~3层，因此索引的查询性能不会随着数据量的增大而下降很多。假设分区了，通过分区键索引确实有可能减少IO次数，但如果通过非分区键索引查询呢？试想一下，如果把一张大表分为100个分区，则所以通过非分区键的索引每次都需要在100个分区里查找，性能将会急剧下降！

因此，对于分区是否能够提高查询性能的问题，一定要根据具体的应用决定。切不可盲目分区！

B+树是一种经典的数据结构，由平衡树和二叉查找树结合产生，它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有的记录节点都是按键值大小顺序存放在同一层的叶节点中，叶节点间用指针相连，构成双向循环链表，非叶节点（根节点、枝节点）只存放键值，不存放实际数据。下面看一个2层B+树的例子：

保持树平衡主要是为了提高查询性能，但为了维护树的平衡，成本也是巨大的，当有数据插入或删除时，需采用拆分节点、左旋、右旋等方法。B+树因为其高扇出性，所以具有高平衡性，通常其高度都在2~3层，查询时可以有效减少IO次数。B+树索引可以分为聚集索引(clustered index)和非聚集索引（即辅助索引，secondary index）。

聚集索引

InnoDB表时索引组织表，即表中数据按主键B+树存放，叶子节点直接存放数据，每张表只能有一个聚集索引。

辅助索引

辅助索引（也称非聚集索引）是指叶节点不包含行的全部数据，叶节点除了包含键值之外，还包含一个书签连接，通过该书签再去找相应的行数据。下图显示了InnoDB存储引擎辅助索引和聚集索引的关系：

从上图中可以看出，辅助索引叶节点存放的是主键值，获得主键值后，再从聚集索引中查找整行数据。举个例子，如果在一颗高度为3的辅助索引中查找数据，首先从辅助索引中获得主键值（3次IO），接着从高度为3的聚集索引中查找以获得整行数据（3次IO），总共需6次IO。一个表上可以存在多个辅助索引。

索引组织表 VS 堆表

MyISAM中的表是以堆表的方式进行存储，堆表没有主键，因此没有聚集索引，辅助索引叶节点不是返回主键值，而是返回行标志符（ROWID），通过ROWID再去查找相应的行。

很显然，对于堆表来说，通过辅助索引访问更快(IO更少)，但是如果在OLTP应用下，表中数据经常被修改，辅助索引中的ROWID可能需要经常更新，如果更新影响到物理地址的更改，这种开销比索引组织表要大得多。

因此，索引组织表还是堆表，这取决于你的应用，如果你的应用是OLAP，数据更新很少，堆表更好一些。

复合索引

复合索引是指对表上的多个列做索引，下面是一个复合索引的例子：

[sql]  view plain copy print ?

1. alter table t add key idx_a_b(a,b);  

下图是B+树结构：

很显然，对于where a = xxx and b=xxx 这样的语句是可以使用这个复合索引的。现在看看对单个列的情况，where a = xxx也是可以使用该复合索引，因为a列在复合索引中也是有序的，但对于where b =xxx 这样的语句是无法使用该复合索引，因为它是无序的。

转 http://blog.youkuaiyun.com/u010415792/article/details/9078635