详解mysql体系结构和存储引擎

概述

之前整理的一些mysql方面内容，适合做备忘，因为我基本不会去记这些概念性的东西，大家做个了解就可以了。

---

一、定义数据库和实例

1、数据库：

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

2、实例：

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

这两个词有时可以互换使用， 不过两者的概念完全不同。 在MySQL数据库中， 实例与数据库的关系通常是一一对应的， 即一个实例对应一个数据库， 一个数据库对应一个实例。 但是， 在集群情况下可能存在一个数据库被多个数据实例使用的情况。

MySQL被设计为一个单进程多线程架构的数据库， 这点与SQL Server比较类似， 但与Oracle多进程的架构有所不同（ Oracle的Windows版本也是单进程多线程架构的） 。 这也就是说， MySQL数据库实例在系统上的表现就是一个进程。

3、mysql配置文件

当启动实例时， MySQL数据库会去读取配置文件， 根据配置文件的参数来启动数据库实例。这与Oracle的参数文件（ spfile） 相似， 不同的是， Oracle中如果没有参数文件， 在启动实例时会提示找不到该参数文件， 数据库启动失败。 而在MySQL数据库中， 可以没有配置文件， 在这种情况下， MySQL会按照编译时的默认参数设置启动实例。 用以下命令可以查看当MySQL数据库实例启动时， 会在哪些位置查找配置文件。

MySQL数据库是按/etc/my.cnf→/etc/mysql/my.cnf→/usr/local/mysql/etc →～/.my.cnf的顺序读取配置文件的。 可能如果几个配置文件中都有同一个参数，MySQL数据库以哪个配置文件为准？MySQL数据库会以读取到的最后一个配置文件中的参数为准。 在Linux环境下， 配置文件一般放在/etc/my.cnf下。 在Windows平台下， 配置文件的后缀名可能是.cnf， 也可能是.ini。

从概念上来说， 数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合； 数据库实例是程序， 是位于用户与操作系统之间的一层数据管理软件，已经为大家精心准备了大数据的系统学习资料，从Linux-Hadoop-spark-......，需要的小伙伴可以点击 用户对数据库数据的任何操作， 包括数据库定义、 数据查询、 数据维护、 数据库运行控制等都是在数据库实例下进行的， 应用程序只有通过数据库实例才能和数据库打交道。

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

---

二、MySQL体系结构

 

MySQL由以下几部分组成：

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

MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。 MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持， 这些标准与存储引擎本身无关， 可能是每个数据库系统本身都必需的， 如SQL分析器和优化器等， 而存储引擎是底层物理结构的实现， 每个存储引擎开发者可以按照自己的意愿来进行开发。

需要特别注意的是， 存储引擎是基于表的，而不是数据库。

---

三、MySQL存储引擎

1、InnoDB存储引擎

 

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

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

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

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

InnoDB存储引擎是MySQL数据库最为常用的一种引擎， 而Facebook、 Google、 Yahoo！ 等公司的成功应用已经证明了InnoDB存储引擎具备的高可用性、 高性能以及高可扩展性。

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、NDB存储引擎

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

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

注意 MySQL NDB Cluster存储引擎有社区版本和企业版本两种， 并且NDB Cluster已作为Carrier Grade Edition单独下载版本而存在。

4、Memory存储引擎

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

虽然Memory存储引擎速度非常快， 但在使用上还是有一定的限制。 比如， 只支持表锁， 并发性能较差， 并且不支持TEXT和BLOB列类型。 最重要的是， 存储变长字段（ varchar） 时是按照定长字段（ char） 的方式进行的， 因此会浪费内存（ 这个问题之前已经提到， eBay的工程师IgorChernyshev已经给出了patch解决方案） 。此外有一点容易被忽视， MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集（ intermediate result） 。 如果中间结果集大于Memory存储引擎表的容量设置，已经为大家精心准备了大数据的系统学习资料，从Linux-Hadoop-spark-......，需要的小伙伴可以点击 又或者中间结果含有TEXT或BLOB列类型字段， 则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘中。 之前提到MyISAM不缓存数据文件， 因此这时产生的临时表的性能对于查询会有损失。

5、Archive存储引擎

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

6、Federated存储引擎

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

7、Maria存储引擎

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

8、其他存储引擎

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

四、不同MySQL存储引擎相关特性比较

 

---

如果大家要学习mysql的话建议先从体系结构入手，一步一步去做深入，概念性东西也需要了解下，这里重点掌握innodb引擎和myisam引擎。

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！！