MySQL架构与常用存储引擎

MySQL是互联网行业中使用极其广泛的一种关系型数据库，它的架构可以在多种不同的业务场景中应用并得到很好的发挥作用，例如：Web类应用，嵌入到应用程序中，也可以支持数据仓库、内容索引、高可用的冗余系统等各种应用类型。工欲善其事，必先利其器。为了更好地应用MySQL，我们先从整体上了解MySQL架构的设计。

1、MySQL逻辑架构说明

图1.MySQL服务器逻辑架构图

上图是我画的一幅描述MySQL各组件协同工作的逻辑架构图，图中几大核心的组件有助于我们了解MySQL服务器的设计。

客户端

连接MySQL服务器端的具体工具集。如：本地客户端软件（Navicat等），或者是应用程序中的数据源集合（如JDBC、ODBC连接）。

Server

MySQL实例，提供数据存储和处理的MySQL服务器进程。

mysqld

管理MySQL实例，是一个守护进程，管理客户端的请求。

连接管理/安全认证

每个客户端连接都会在服务器进程中拥有一个线程，MySQL服务器负责缓存线程，不需要为每一个新连接重新创建线程。

当客户端连接MySQL服务器时，也会需要进行授权登录以保证安全。如居于用户名、SSL方式等。

一旦客户端连接服务器成功，服务器会继续验证该客户端是否具有执行某个特定资源（Database、Table、Select、DDL）的权限。

解析器

关系型数据库拥有一套SQL语句语法标准，解析器会对每一条SQL语句进行解析操作，如果不符合语法标准的，就直接报错。否则，为该SQL语句生成一个SQL_ID，解析后生成一个解析树（MySQL内部数据结构），转交后续步骤操作。

查询缓存

在解析查询之前，服务器会先检查查询缓存，如果能够在其中找到对应的查询，服务器就不必执行解析、优化和执行的过程，直接返回查询缓存中的结果集。

优化器

服务器本身会针对解析器解析出来的SQL进行优化处理，包括：选择合适的索引、重写查询、决定表的读取顺序等，从而创建一个有效率的执行计划。

优化器并不关心表使用了什么存储引擎，但实际上存储引擎是对优化查询是有影响的。

用户也可以通过优化SQL语句编写、创建索引等方式来人工干预执行计划，以获得最优的执行计划。可以通过explain来查看SQL的执行计划过程。

Metadata Cache

缓存对象元信息和统计信息。

Keys Cache

缓存索引结构，通常给MyISAM引擎使用。

存储引擎

存储引擎负责MySQL中的数据存储和提取，服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎的差异，使得对上层的查询过程是透明的。

2、常见存储引擎对比

图2：MyISAM与InnoDB常见对比图

 

2.1、MyISAM存储引擎

 在MySQL5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM拥有很多特性：全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且有一个致命的缺陷就是崩溃后无法安全恢复。

存储

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。

特性

加锁与并发：MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。

索引特性：支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。

MyISAM性能：MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。但MyISAM最典型的性能问题还是表锁的问题，如果你发现所有的查询都长期处于“Locked”状态，毫无疑问是表锁出了问题。

MyISAM压缩表：如果表在创建并且导入数据以后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。

2.2、InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最为广泛的存储引擎。它被设计用来处理大量的短期事务的场景（且事务大部分是正常提交，很少回滚的场景）。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。建议优先使用InnoDB引擎。

InnoDB概览

存储：InnoDB的数据存储在表空间（tablespace）中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。在MySQL4.1以后的版本，InnoDB可以将每个表的数据和索引存放在单独的文件中。

高并发：InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。

索引结构：InnoDB表是基于聚簇索引建立的，InnoDB的索引结构和其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。

事务：InnoDB是行级锁，支持事务。作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份。

2.3、Memory存储引擎

使用场景：如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory引擎是非常有用的，Memory表的速度至少比MyISAM要快一个量级。如以下场景：

1、用于查找或者映射表，如一些数据字典表。

2、用于缓存周期性聚合数据的结果。

3、用于保存数据分析中产生的中间数据。

存储：内存。不需要进行I/O操作。重启后数据会丢失。

性能：Memory表支持Hash索引，因此查找操作非常快。Memory表是表级锁，因此并发写入的性能比较低。

2.4、Archive存储引擎

Archive存储引擎只支持insert和select操作。Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM的磁盘I/O更少，但是每次select查询都要执行全表扫描。所以Archive表适合日志和数据采集类应用，这类应用做数据分析时往往需要全表扫描。或者在一些需要更加快速INSERT操作的场景也可以使用。

Archive存储引擎支持行级锁和专用的缓冲区，所以可以实现高并发地插入。但Archive引擎不是一个事务型引擎，而是一个针对高速插入和压缩做了优化的简单引擎。

2.5、Blackhole存储引擎

Blackhole引擎没有实现任何的存储机制，它会丢弃所有的插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎，在一些特殊的复制架构和日志审核时发挥作用。

2.6、CSV存储引擎

CSV引擎可以将普通的CSV文件（逗号分割值的文件）作为MySQL的表来处理，但这种表不支持索引。CSV引擎可以在数据库运行时拷入或者拷出文件。可将Excel等电子表格的中的数据存储为CSV文件，然后复制到MySQL数据目录下，就能给在MySQL中正常打开使用。CSV可以作为一种数据交换的机制，非常有用。

2.7、Merge存储引擎

Merge引擎是MyISAM引擎的一个变种，Merge表是由多个MyISAM表合并而来的虚拟表。如果将MySQL用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被抛弃。