MySQL技术内幕 - 第一章 MySQL体系结构和存储引擎

一、定义数据库和实例

数据库：物理操作系统文件或其他形式文件类型的集合。在 MySQL 数据库中，数据库文件可以是 frm、MYD、MYI、ibd 结尾的文件。当使用 NDB 引擎时，数据库的文件不可能不是操作系统上的文件，而是存放于内存之中的文件，但是定义仍然不变。

数据库实例：MySQL 数据库由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。需要牢记的是，数据库实例才是真正用于操作数据库文件的。

在 MySQL 数据库中，实例与数据库的关系通常是一一对应的，即一个实例对应一个数据库，一个数据库对应一个实例。但是，在集群情况下可能存在一个数据库被多个数据实例使用的情况。

MySQL 数据库实例在系统上的表现就是一个进程。

二、MySQL 体系结构

从概念上来说，数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合；数据库实例是程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

一种更加直白的解释是：数据库是由一个个文件组成（一般来说都是二进制的文件）的，要对这些文件执行诸如 SELECT、INSERT、UPDATE 和 DELETE 之类的数据库操作是不能通过简单的操作文件来更改数据库的内容，需要通过数据库实例来完成对数据库的操作。

MySQL 数据库的体系结构如图：

从图中可以发现，MySQL 由以下几部分组成：

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

MySQL 数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。需要特别注意的是，存储引擎是基于表的，而不是数据库。

三、MySQL 存储引擎

3.1 InnoDB 存储引擎

InnoDB 存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点是行锁设计、支持外键，并支持类似于 Oracle 的非锁定读，即默认读取操作不会产生锁。从 MySQL 数据库 5.5.8 版本开始，InnoDB 存储引擎是默认的存储引擎。

InnoDB 存储引擎将数据放在一个逻辑的表空间中，这个表空间就像黑盒一样由 InnoDB 存储引擎自身进行管理。从 MySQL4.1（包括 4.1）版本开始，它可以将每个 InnoDB 存储引擎的表单独存放到一个独立的 ibd 文件中。此外，InnoDB 存储引擎支持用裸设备（row disk）用来建立其表空间。

InnoDB 通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了 SQL 标准的 4 种隔离级别，默认为 REPEATABLE 级别。同时，使用一种被称为 next-key locking的策略来避免幻读（phantom）现象的产生。除此之外，InnoDB 储存引擎还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash,index）、预读（read ahead）等高性能和高可用的功能。

对于表中数据的存储，InnDB 存储引擎采用了聚集（clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB 存储引擎会为每一行生成一个 6 字节的 ROWID，并以此作为主键。

3.2 MyISAM 存储引擎

MyISAM 存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些 OLAP 数据库应用。在 MySQL 5.5.8 版本之前 MyISAM 存储引擎是默认的存储引擎（除 Windows 版本外）。数据库系统与文件系统很大的一个不同之处在于对事务的支持，然而 MyISAM 存储引擎是不支持事务的。究其根本，这也不是很难理解。试想用户是否在所有的应用中都需要事务呢? 在数据仓库中，如果没有 ETL 这些操作，只是简单的报表查询是否还需要事务的支持呢? 此外，MyISAM 存储引擎的另一个与众不同的地方是它的缓冲池只缓存（cache）索引文件，而不缓冲数据文件，这点和大多数的数据库都非常不同。

MyISAM 存储引擎表由 MYD 和 MYI 组成，MYD 用来存放数据文件，MYI 用来存放索引文件。可以通过使用 myisampack 工具来进一- 步压缩数据文件，因为 myisampack 工具使用赫夫曼（Huffman）编码静态算法来压缩数据，因此使用 myisampack 工具压缩后的表是只读的，当然用户也可以通过 myisampack 来解压数据文件。

在 MySQL 5.0 版本之前，MyISAM 默认支持的表大小为 4GB，如果需要支持大,于 4GB 的 MyISAM 表时，则需要制定MAX__ROWS和AVG_ROW_LENGTH 属性。从 MySQL 5.0 版本开始，MyISAM 默认支持 256TB 的单表数据，这足够满足一般应用需求。

3.3 NDB 存储引擎

NDB 存储引擎是一个集群存储引擎，类似于 Oracle 的 RAC 集群，不过与 Oracle RAC，share everything 架构不同的是，其结构是 share nothing 的集群架构，因此能提供更高的可用性。NDB 的特点是数据全部放在内存中（从 MySQL 5.1 版本开始，可以将非索引数据放在磁盘上），因此主键查找（primary key lookups）的速度极快，并且通过添加 NDB 数据存储节点（DataNode）可以线性地提高数据库性能，是高可用、高性能的集群系统。

关于 NDB 存储引擎，有一个问题值得注意，那就是 NDB 存储引擎的连接操作（JOIN）是在 MySQL 数据库层完成的，而不是在存储引擎层完成的。这意味着，复杂的连接操作需要巨大的网络开销，因此查询速度很慢。

3.4 Memory 存储引擎

Memory 存储引擎（之前称 HEAP 存储引擎）将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表。Memory 存储引擎默认使用哈希索引，而不是我们熟悉的 B+ 树索引。

虽然 Memory 存储引擎速度非常快，但在使用上还是有一定的限制。比如，只支持表锁，并发性能较差，并且不支持 TEXT 和 BLOB 列类型。最重要的是，存储变长字段（varchar）时是按照定常字段（char）的方式进行的，因此会浪费内存。

此外有一点容易被忽视，MySQL 数据库使用 Memory 存储引擎作为临时表来存放查询的中间结果集（intermediate result）。 如果中间结果集大于 Memory 存储引擎表的容量设置，又或者中间结果含有 TEXT 或 BLOB 列类型字段，则 MySQL 数据库会把其转换到 MyISAM 存储引擎表而存放到磁盘中。之前提到 MyISAM 不缓存数据文件，因此这时产生的临时表的性能对于查询会有损失。

3.5 Archive 存储引擎

Archive 存储引擎只支持 INSERT 和 SELECT 操作，从 MySQL 5.1 开始支持索引。Archive 存储引擎使用 zlib 算法将数据行（row）进行压缩后存储，压缩比一般可达 1 : 10。正如其名字所示，Archive 存储引擎非常适合存储归档数据，如日志信息。Archive 存储引擎使用行锁来实现高并发的插人操作，但是其本身并不是事务安全的存储引擎，其设计目标主要是提供高速的插人和压缩功能。

3.6 Federated 存储引擎

Federated 存储引擎表并不存放数据，它只是指向一台远程 MySQL 数据库服务器上的表。这非常类似于 SQL Server 的链接服务器和 Oracle 的透明网关，不同的是，当前 Federated 存储引擎只支持 MySQL 数据库表，不支持异构数据库表。

3.7 Maria 存储引擎

Maria 存储引擎是新开发的引擎，设计目标主要是用来取代原有的 MyISAM 存储引,擎，从而成为 MySQL 的默认存储引擎。Maria 存储引擎的开发者是 MySQL 的创始人之一的 MichaelWidenius。因此，它可以看做是 MyISAM 的后续版本。Maria 存储引擎的特点是：支持缓存数据和索引文件，应用了行锁设计，提供了 MVCC 功能，支持事务和非事务安全的选项，以及更好的 BLOB 字符类型的处理性能。

3.8 其他存储引擎

除了上面提到的 7 种存储引擎外，MySQL 数据库还有很多其他的存储引擎，包括 Merge、CSV、Sphinx 和 Infobright，它们都有各自使用的场合，这里不再介绍。

四、各种存储引擎之间的比较

下图展示了一些常用 MySQL 存储引擎之间的不同之处，包括存储容量的限制、事务支持、锁的粒度、MVCC 支持、支持的索引、备份和复制等。

特征	MyISAM	BDB	Memory	InnoDB	Archive	NDB
Storage Limits（存储限制）	No	No	Yes	64TB	No	Yes
Transactions（处理 commit, rollback, etc）		✓		✓
Locking granularity（锁粒度）	Table	Page	Table	Row	Row	Row
MVCC/Snapshot Read（快照读取）				✓	✓	✓
Geospatial support（地理空间支持）	✓
B-Tree indexes（B树索引）	✓	✓	✓	✓		✓
Hash indexes（哈希索引）			✓	✓		✓
Full text search index（全文检索索引）	✓
Clustered index（聚集索引）				✓
Data Caches（数据缓存）			✓	✓		✓
Index Caches（索引缓存）	✓		✓	✓		✓
Compressed data（数据压缩）	✓				✓
Encrypted data（数据加密 via function）	✓	✓	✓	✓	✓	✓
Storage cost（存储成本 space used）	Low	Low	N/A	High	Very Low	Low
Memory cost（内存成本）	Low	High	Medium	High	Low	High
Bulk Insert Speed（批量插入速度）	High	High	High	Low	Very High	High
Cluster database support（集群数据库支持）						✓
Replication support（复制支持）	✓	✓	✓	✓	✓	✓
Foreign key support（外键支持）				✓
Backup/Point-in-time recovery（备份/时间点恢复）	✓	✓	✓	✓	✓	✓
Query cache support（查询缓存支持）	✓	✓	✓	✓	✓	✓
Update Statistics for Data Dictionary（更新数据字典的统计信息）	✓	✓	✓	✓	✓	✓

特征	MyISAM	BDB	Memory	InnoDB	Archive	NDB
Transactions（事务处理 commit, rollback, etc）		✓		✓
Locking granularity（锁粒度）	Table	Page	Table	Row	Row	Row
MVCC/Snapshot Read（快照读取）				✓	✓	✓
B-Tree indexes（B树索引）	✓	✓	✓	✓		✓
Hash indexes（哈希索引）			✓	✓		✓
Full text search index（全文检索索引）	✓
Clustered index（聚集索引）				✓
Data Caches（数据缓存）			✓	✓		✓
Index Caches（索引缓存）	✓		✓	✓		✓
Storage cost（存储成本 space used）	Low	Low	N/A	High	Very Low	Low
Memory cost（内存成本）	Low	High	Medium	High	Low	High
Bulk Insert Speed（批量插入速度）	High	High	High	Low	Very High	High
Foreign key support（外键支持）				✓

五、连接 MySQL

本节将介绍连接 MySQL 数据库的常用方式。需要理解的是，连接 MySQL 操作是一个连接进程和 MySQL 数据库实例进行通信。从程序设计的角度来说，本质上是进程通信。如果对进程通信比较了解，可以知道常用的进程通信方式有管道、命名管道、命名字、TCP/IP 套接字、UNIX 域套接字。MySQL 数据库提供的连接方式从本质上看都是上述提及的进程通信方式。

5.1 TCP/IP

TCP/IP 套接字方式是 MySQL 数据库在任何平台下都提供的连接方式，也是网络中使用得最多的一种方式。这种方式在 TCP/IP 连接上建立一个基于网络的连接请求，一般情况下客户端（client）在一台服务器上，而 MySQL 实例（server）在另一台服务器上，这两台机器通过一个 TCP/IP 网络连接。

5.2 命名管道和共享内存

在 Windows 2000、Windows XP、Windows 2003 和 Windows Vista 以及在此之上的平台上，如果两个需要进程通信的进程在同一台服务器上，那么可以使用命名管道，Microsoft SQL Server 数据库默认安装后的本地连接也是使用命名管道。在 MySQL 数据库中须在配置文件中启用 --enable-named-pipe 选项。在 MySQL 4.1 之后的版本中，MySQL 还提供了共享内存的连接方式，这是通过在配置文件中添加 --sbared-memory 实现的。如果想使用,共享内存的方式，在连接时，MySQL 客户端还必须使用 --protocol=memory 选项。

5.3 UNIX 域套接字

在 Linux 和 UNIX 环境下，还可以使用 UNIX 域套接字。UNIX 域套接字其实不是个网络协议，所以只能在 MySQL 客户端和数据库实例在一台服务器上的情况下使用。用户可以在配置文件中指定套接字文件的路径，如 --socket/tmp/mysql.sock。