一、MySQL架构介绍

一、MySQL存储引擎

MySQL数据库及其分支版本主要的存储引擎有InnoDB、MyISAM、Memory等。简单地理解，存储引擎就是指表地类型以及表在计算机上的存储方式。存储引擎的概念是MySQL的特色，使用的是一个可插拔存储引擎架构，能够在运行的时候动态加载或者卸载这些存储引擎。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。

在MySQL中的存储引擎有很多种，可以通过SHOW ENGINES语句来查看。

下面重点关注InnoDB、MyISAM、MEMORY这3种。

1、InnoDB存储引擎

（1）InnoDB是事务型数据库的首选引擎，支持事务ACID，简单地说就是支持事务完整性、一致性。
（2）InnoDB支持行级锁。行级锁可以在最大程度上支持并发，以及类似Oracle的一致性读、多用户并发。
（3）InnoDB是为处理巨大数据量的最大性能设计，InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。
（4）InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按照主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。
（5）InnoDB支持崩溃数据自修复。InnoDB存储引擎中就是依靠redo log来保证的。当数据库异常崩溃后，数据库重新启动时会根据redo log进行数据恢复，保证数据库恢复到崩溃前的状态。

2、MyISAM存储引擎

（1）MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用。
（2）其锁定机制是表级锁定，虽然可以让锁定的实现成本很小，但是也同时大大降低了其并发性能。
（3）不仅会在写的时候阻塞读取，MyISAM还会再读取的时候阻塞写入，但读本身并不会阻塞另外的读。
（4）只会缓存索引：MyISAM可以通过key_buffer缓存，以大大提高访问性能减少磁盘I/O，但是这个缓冲区只会缓存索引，而不会缓存数据。
（5）适用于不需要事务支持（不支持）、并发相对较低（锁定机制问题）、数据修改相对较少（阻塞问题）、以读为主这类场景。

3、MEMORY

MEMORY存储引擎是MySQL中一类特殊存储引擎，使用存储在内存中的内容来创建表，而且所有数据也存放在内存中。
（1）每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构，数据文件则存储在内存中。
（2）MEMORY默认使用哈希索引，速度比使用B型树索引快。如果想用B型树索引，可以在创建索引时指定。
（3）MEMORY存储引擎是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，那么所有数据都会消失。

在实际工作中，选择一个合适的存储引擎是比较复杂的问题。每种存储引擎都有自己的优缺点，不能笼统地说谁比谁好。如果需要对事务地完整性比较高（比如银行），要求实现并发控制（比如售票），那么选择InnoDB有很大地优势。如果表主要是用于插入记录和读出记录，那么选择MyISAM能实现处理高效率。如果需要很快地读写速度，对数据地安全性要求较低，可以选择MEMORY，它对表地大小有要求，不能建立太大地表。

二、MySQL逻辑结构

MySQL数据库的逻辑架构简单的如图所示。

MySQL逻辑架构整体分为3层：

• 第一层是客户端层，所包含的并不是MySQL独有的技术，它们都是服务于C/S程序或者是这些程序所需要的，诸如连接处理、身份验证、安全性等功能均在这一层处理。
• 第二层是SQL层（SQL Layer），因为这是MySQL的核心部分，通常也叫作核心服务层。在MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断、SQL解析、执行计划优化、Query cache的处理以及所有内置的函数、存储过程、视图、触发器等。
• 第三层是存储引擎层（Storage Engine Layer），是底层数据存取操作实现的部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据，类似Linux的众多文件系统。每个存储引擎都有自己的优势劣势，通过存储引擎API来与它们交互，这些API接口隐藏了各个存储引擎不同的地方。对于查询层尽可能透明。

虽然看起来MySQL架构好像比较简单，但是实际上每一层中都含有各自的很多小模块，尤其是第二层SQL Layer，结构蛮复杂的，如图所示。

我们简单地做如下剖析：
（1）Connectors：指的是不同语言中与SQL的交互。
（2）Management Services & Utilities ：管理服务和工具组件，从备份和恢复的安全性、复制、集群、管理、配置、迁移和元数据等方面管理数据库。
（3）Connection Pool：连接池，是为解决资源的频繁分配、释放所造成的问题而为数据库连接建立的一个“缓冲池”。原理是预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需要从“缓冲池”中取出一个，使用完毕之后再放回去。它的作用是进行身份验证、线程重用、连接限制、管理用户的连接、线程处理等需要缓存的需求。
（4）SQL Interface（SQL接口）：接受用户的SQL命令，并返回用户需要查询的结果。比如select form就是调用SQL Interface。
（5）Parser：解析器，验证和解析SQL命令。SQL命令传递到解析器的时候会被解析器验证和解析，并生成一颗对应的解析树。在这个过程中，解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等。
（6）Optimizer：查询优化器。SQL语句在查询执行之前，会使用查询优化器对查询进行优化，得出一个最优的策略。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到其中最好的执行计划。
用一个例子就可以理解，比如“select uid,name from user where gender=1”。这个select查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；这个select查询先根据uid和name进行属性投影，而不是将属性全部取出来以后再进行过滤；将这两个查询条件联接起来生成最终查询结果。
（7）Cache和Buffer：主要功能是将客户端提交给MySQL的select类query请求的返回结果集缓存到内存中，与该query的一个hash值做一个对应。该query所取数据的基表发生任何数据的变化之后，MySQL会自动使该query的Cache失效。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的，比如表缓存、记录缓存、key缓存、权限缓存等。
（8）Pluggable Storage Engines：可插拔存储引擎。MySQL区别其他数据库的最重要特点就是其插件式的存储引擎接口模块，这个可以说是MySQL数据库中最有特色的一个特点了。目前，各种数据库产品中只有MySQL可以实现底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，根据MySQL AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制，这种访问机制就称为存储引擎。正是因为它成功地将各种数据处理高度抽象化才成就了今天MySQL可插拔存储引擎的特色。。每个存储引擎开发者都可以按照自己的意愿来进行开发，存储引擎是基于表的。MyISAM存储引擎的查询速度快，有较好的索引优化和数据压缩技术，但是它不支持事务。InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。Memory使用存储在内存中的数据来创建表，而且所有的数据也存储在内存中。
（9）File System：数据存储在运行于裸设备的文件系统上，支持的文件类型有EXT3、EXT4、NTFS、NFS。
（10）File&Logs：数据文件以及redo、undo等各种日志文件。

三、MySQL物理文件体系结构

MySQL在Linux系统中的数据文件目录如图所示，可以归结为：如下几类。

（1）binlog二进制日志文件：不管使用的是哪一种存储引擎，都会产生binlog。如果开启了binlog二进制日志，就会有若干个二进制日志文件，如mysql-bin.00001、mysql-bin.00002、mysql-bin.00003等。binlog记录了MySQL对数据库执行更改的所有操作。

在MySQL5.1之前，所有的binlog都是基于SQL语句级别的。应用这种格式的binlog进行数据恢复时，如果SQL语句带有rand或uuid等函数，可能导致恢复出来的数据与原始数据不一致。从MySQL5.1版本开始，MySQL引入了binlog_format参数。这个参数有可选值statement和row：statement就是之前的格式，基于SQL语句来记录；row记录的则是行更改情况，可以避免之前提到的数据不一致的问题。做MySQL主从复制，statement格式的binlog可能会导致主备不一致，所以要使用row格式。我们还需要借助mysqlbinlog工具来解析和查看binlog中的内容。如果需要用binlog来恢复数据，标准做法是用mysqlbinlog工具把binlog中的内容解析出来，然后把解析结果整个发给MySQL执行。
（2）redo重做日志文件：ib_logfile0、ib_logfile1是InnoDB引擎特有的、用于记录InnoDB引擎事务的日志，它记录每页更改的物理情况。首先要搞明白的是已经有binlog了为什么还需要redo log，因为两者分工不同。binlog主要用来做数据归档，但是并不具备崩溃恢复的能力，也就是说如果系统突然崩溃，重启后可能会有部分数据丢失。InnoDB将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行恢复操作，这个文件就是redo log file.redo log的存在可以完美解决这个问题。默认情况下，每个InnoDB引擎至少有一个重做日志组，每组下至少有两个重做日志文件，例如前面提到的ib_logfile0和ib_logfile1。重做日志组中的每个日志文件大小一致且循环写入，也就是说先写ib_logfile0,写满了之后写iblogfile1,一旦ib_logfile1写满了，就继续写ib_logfile0。当innodb log设置过大的时候，可能会导致系统崩溃后恢复需要很长的时间；当innodb log设置过小的时候，在一个事务产生大量日志的情况下，需要多次切换重做日志文件。
（3）共享表空间和独立表空间：在MySQL 5.6.6之前的版本中，InnoDB默认会将所有的数据库InnoDB引擎的表数据存储在一个共享表空间ibdata1中，这样管理起来很困难，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。为了优化上述问题，在MySQL 5.6.6之后的版本中，独立表空间innodb_file_per_table参数默认开启，每个数据库的每个表都有自己独立的表空间，每个表的数据和索引都会存在自己的表空间中。即便是innodb表指定为独立表空间，用户自定义数据库中的某些元数据信息、回滚（undo）信息、插入缓存（change buffer）、二次写缓存（double write buffer）等还是存放在共享表空间，所以又称为系统表空间。

这个系统表空间由一个或多个数据文件组成，默认情况下其包含一个叫ibdata1的系统数据文件，位于MySQL数据目录下。系统表空间数据文件的位置、大小和数目由参数innodb_data_home_dir和innodb_data_file_path启动选项控制。

当开启独立表空间参数innodb_file_per_table选项时，该表创建于自己的数据文件中，而非创建于系统表空间中。这样的好处在于在drop或者truncate时空间可以被收回至操作系统用作其他用途，而且进行表单空间在不同实例间移动，而不必处理整个数据库表空间。

参数innodb_file_per_table独立表空间选项开启，同时通过“show variables like ‘innodb_data%’;”命令可以查询共享表空间的文件信息。
（4）undo log：undo log是回滚日志，如果事务回滚，则需要依赖undo日志进行回滚操作。MySQL在进行事务操作的同时，会记录事务性操作修改数据之前的信息，就是undo日志，确保可以回滚到事务发生之前的状态。innodb的undo log存放在ibdata1共享表空间中，当开启事务后，事务所使用的undo log会存放在ibdata1中，即使这个事务被关闭，undo log也会一直占用空间。为了避免ibdata1共享表空间暴涨，建议将undo log单独存放。innodb_undo_directory参数指定单独存放undo表空间的目录，该参数实例初始化之后不可直接改动（可以通过先停库，修改配置文件，然后移动undo表空间文件的方式去修改该参数）；innodb_undo_tablespaces参数指定单独存放的undo表空间个数，推荐设置为大于等于3；innodb_undo_logs参数指定回滚段的个数，默认为128个。MySQL5.7引入了新的参数innodb_undo_log_truncate这个参数开启后可在线收缩拆分出来的undo表空间。

（5）临时表空间：存储临时对象的空间，比如临时表对象等。参数innodb_temp_data_file_path可以看到临时表空间的信息。

MySQL5.7对于InnoDB存储引擎的临时表空间做了优化。在MySQL5.7之前，InnoDB引擎的临时表都保存在ibdata里面，而ibdata的贪婪式磁盘占用导致临时表的创建与删除对其他正常表产生非常大的性能影响。

在MySQL5.7中，对于临时表做了下面两个重要方面的优化：MySQL5.7把临时表的数据从共享表空间里面剥离出来，形成自己单独的表空间，参数为innodb_temp_data_file_path;MySQL5.7中把临时表的相关检索信息保存在系统信息表中：information_schema.innodb_temp_table_info。在MySQL5.7之前的版本中，想要查看临时表的系统信息没有太好的办法。

虽然InnoDB临时表有自己的表空间，但是目前还不能自己定义临时表空间文件的保存路径，只能是继承innodb_data_home_dir。此时如果想要拿其他的磁盘（比如内存盘）来充当临时表空间的保存地址，只能用老办法做软链。

需要注意的一点就是：有时执行SQL请求时会产生临时表，极端情况下可能导致临时表空间文件暴涨，所以为了避免以后再出现类似的情况，一定要限制临时表空间的最大上限，超过上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可以借此机会进行优化）。

（6）error.log：错误日志记录了MySQL Server每次启动和关闭的详细信息，以及运行过程中所有较为严重的警告和错误信息。可以用log-error[=file_name]选项来开启MySQL错误日志，该选项指定保存错误日志文件的位置。
（7）slow.log：如果配置了MySQL的慢查询日志，MySQL就会将运行过程中的慢查询日志记录到slow_log文件中。MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL。参数slow_query_log_file指定慢查询日志文件的存放路径；参数long_query_time的默认值为10，意思是运行10s以上的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要我们手动来设置这个参数。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库。
（8）general_log：参数general_log=off表明没有启用通用查询日志，如果配置了通用查询日志，将记录建立的client连接和运行的语句。参数general_log_file表明通用查询日志位置及名字，MySQL将运行过程中的所有SQL都记录在此文件中。
（9）数据库路径：可以看到系统数据库mysql、sys、performance_schema、information_schema和用户自定义的数据库test。

系统数据库包括以下几种：

• information_schema系统数据库，提供了数据库的元数据信息，是数据库的数据，比如数据库的名字和数据库中的表名、字段名、字段类型等，可以说是数据库的数据字典信息。这个库中的信息并非物理地保存在表中，而是动态地去读取其他文件得到的。例如，上面一开始提到的共享表空间，用户数据中的对象（比如表结构等）就都保存在共享表空间中，information_schema库中的一些信息可以认为是直接映射到共享表空间中的信息的。
• performance_schema系统数据库，是数据库性能相关的信息的数据，记录的是数据库服务器的性能参数，保存历史事件汇总信息，为MySQL服务器性能评估提供参考信息。
• sys系统数据库，可以根据sys库中的数据快速了解系统的运行信息，方便地查询出数据库的信息，在性能瓶颈、自动化运维等方面都有很大的帮助。sys库中的信息通过视图的方式将information_schema和performance_schema库中的数据结合起来，可以得到更加直观和容易理解的信息。
• mysql系统数据库，存储系统的用户权限信息及帮助信息。新建的用户、用户的权限信息等都存储在mysql库中。例如在修改MySQL的root密码时，要先调用mysql这个系统库再执行用户、授权等操作。

用户数据库：用户数据库实际上是一个目录，保存了数据库中的表以及数据信息。对于InnoDB引擎的表，InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。例如，member表分别对应两个文件：一个是member.frm,存储的是表结构的信息，另一个是member.ibd，存储的是表中的数据。另外，还有一个db.opt文件，是用来记录该库的默认字符集编码和字符集排序规则的。