【MySQL系列】02.MySQL架构&存储引擎及优化

目录

一、MySQL架构

1、MySQL的四层架构

2、SQL语句查询过程分析

3、SQL语句更新过程分析

二、存储引擎

1、存储引擎种类

①.InnoDB

②.MyISAM

③.Memory

2、存储引擎的使用

①.创建表时指定引擎和修改引擎

②.在配置文件中修改默认引擎

③.不同的存储引擎会生成不同的磁盘文件

三、总结

---

一、MySQL架构

1、MySQL的四层架构

如图所示，从上往下看，MySQL总共有四层架构，说明一下各层的功能和作用：

• 第一层：客户端连接层。该层包含了 连接处理、授权认证、安全等功能，并不是MySQL独有的，可以用多种语言多种方式实现与MySQL服务连接的客户端。

• 第二层：核心服务层。包含了查询缓存、解析器、预处理器、查询优化器等，还有全部的内置函数，以及跨存储引擎的数据库对象，如视图，触发器，存储引擎等。

• 第三层：存储引擎层。存储引擎负责着MySQL数据的提取和存储；存储引擎与核心服务之间通过API接口进行通信，且API接口可以屏蔽不同存储引擎间的差异；不同存储引擎有自己的处理数据的方式，相互间不进行通信。

• 第三层：磁盘的文件系统层。所有表的结构和数据，索引，用户操作的日志等，最终都会持久化到磁盘上存储。

2、SQL语句查询过程分析

用户通过客户端发送一条查询SQL请求MySQL服务器，这个查询过程是怎样的呢？与MySQL核心服务层的结构有关，具体过程如图所示：

大致有以下步骤：

1. 客户端发送sql查询请求MySQL服务器，首先去查询缓存中查询，如果查询缓存含有该sql查询记录和结果，则直接返回缓存的结果给客户端，否则进入下一步；
2. 接着，sql会被 解析器 和 预处理器 解析成语法树，前者进行词法解析（sql语句->一个个单词）和语法解析（据语法规则生成解析树），后者则会检查该解析树并解决解析器无法解析的语义（如表和字段是否存在，别名正确性等）并生成新的解析树；
3. 查询优化器会根据新的解析树生成执行计划，mysql基于开销最小原则，选择一个最优的执行计划（该过程可以理解为：解析树转化为最优执行计划的过程）；
4. 接着，查询执行引擎通过API接口查询存储引擎中的数据，并将结果返回给客户端，且会在查询缓存中也缓存一份。

3、SQL语句更新过程分析

sql更新过程与sql查询过程基本一致，都要经历解析、预处理、优化，最后交给执行引擎处理，区别在于执行引擎处理方式不同，这里和存储引擎的内存结构有关。

注：这里备注一个常识点 —— 操作系统和内存打交道最小的单位是页Page；操作系统和磁盘打交道，读写磁盘，最小的单位是块Block。

以InnoDB存储引擎为例，InnoDB的数据和索引都是存放在磁盘上的，如果使用sql语句发生读写数据的操作，直接去操作磁盘的话将会影响效率。因此，InnoDB利用的是一种缓冲池的内存技术，其中页就是一个最小的逻辑单位，读操作时会将从磁盘上读取到的页放到 Buffer Pool 中，Buffer Pool 缓存了数据页和索引页，下一次再读取相同的页时会先去缓冲池查询有没有，有则直接读取不再读取磁盘。而更新数据时，即写操作，会先去缓冲池修改页，为了避免脏页（即缓冲池与磁盘数据不一致）情况出现，InnoDB有专门的线程负责将缓冲池中数据同步刷新到磁盘上（后面的InnoDB调优，会提及Innodb_buffer_pool_size参数优化）。画张图来理解下这个过程吧：

这样的话就太简单了吧，假如缓冲池写满了该怎么办呢？别怕，InnoDB存储引擎会使用基于链表实现的LRU算法进行内存淘汰，从而释放出可用的内存空间。

上图中的写操作如果是第一次写的话，是修改不了不存在的页的，那怎么办呢？是通过将磁盘的页先加载到内存，再去修改页吗？这个过程会发生IO一次，想像一下写多读少的场景中采用这种方式肯定会影响性能了，此时就要提及Change Buffer（写缓存）技术了。如果该数据页不是唯一索引的话，就不用从磁盘加载索引页判断数据是不是重复了，这样就可以先把修改记录在Buffer Pool 里，从而提升了更新语句执行速度。

如果Buffer Pool在将数据同步刷入磁盘时发生了数据库宕机或者重启，则未刷入的数据会丢失，此时发生写操作甚至可能会破坏数据文件，导致数据库不可用。为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作，这也是InnoDB事务实现持久性的原理。这个文件就是磁盘的redo log（叫做重做日志），对应于/var/lib/mysql/目录下的ib_logfile0和ib_logfile1。redo log写入磁盘，它的内容主要是用于数据库崩溃的恢复。Log Buffer写入磁盘的时机，由 innodb_flush_log_at_trx_commit 参数控制的，默认是1，后面的InnoDB调优会提及该参数优化。

这里太深入了，就此打住吧，不然真的停不下来啊，在事务的ACID再分析这个redo log吧，为了更好理解上面的内容，最后贴上一张 InnoDB内存结构 和 磁盘结构图：

二、存储引擎

1、存储引擎种类

以8.0.13版本为例，通过以下命令查询MySQL支持的存储引擎：

可以试图修改默认存储引擎：

重启Navicat客户端，查询默认存储引擎：

从上面可以得出以下结论：

• MySQL（本机当前版本为8.0.13）支持的存储引擎有：InnoDB、MyISAM、Memory、MRG_MYISAM、Archive、Federated、CSV、BLACKHOLE 等；
• MySQL默认支持的存储引擎是 InnoDB。修改默认存储引擎后会生效，但重启Navicat客户端时，仍然是默认的存储引擎InnoDB；如果让默认的存储引擎始终为MyISAM，只能去修改my.ini 配置文件了；
• 从Coment注释可以看出：相对于MyISAM、Memory等存储引擎，InnoDB是支持事务、行锁、外键的。

①.InnoDB

它是MySQL5.5版本之后，默认支持的存储引擎，特点如下：

• 支持事务：提供了对事务的ACID操作，默认的事务隔离级别为可重复读 —— 通过MVCC（并发版本控制）来实现的；
• 支持外键；
• 支持行级锁：锁粒度更细，更好的支持高并发，但锁冲突概率大，容易死锁；
• 支持查询缓存管理：通过缓冲池，将索引和数据全部缓存，加快查询速度；
• 支持分区，支持表空间，表的数据文件为：.ibd(数据文件)和 .frm(表结构定义)；
• 支持在线热备：有很成熟的在线热备解决方案，灾难恢复性比较好。

InnoDB引擎适用的生产业务场景：

• 需要事务支持（如：银行业务）；
• 行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成；
• 数据更新较为频繁的场景（如：贴吧、微博等）；
• 数据一致性要求较高的业务（如：银行卡转账）；
• 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO，可以通过一些参数来设置。

InnoDB引擎调优：

InnoDB是MySQL中处理巨大数据量时的最大性能设计，CPU运行效率高于其他存储引擎，而且在数据库的复制操作中能保证master和slave数据一致性。因此，优化InnoDB引擎是很重要的内容，我们可以从内存利用、日志控制、文件IO分配和空间占用、及其他参数等方面考虑。

A. 内存利用方面

• Innodb_buffer_pool_size：用于缓存索引和数据的参数，默认分配只有8M，不能动态修改，首要优化的参数。该参数分配过大，会使得Swap占用过多，影响MySQL查询性能，该参数优化参考值为自己数据大小+10%左右，比如：数据大小为50M，可以设置Innodb_buffer_pool_size=64M。实际中，通过 show Innodb status\G; 命令查看。
• Innodb_additional_mem_pool：用于存放Innodb的内部目录，通常比较大的数据设置16Ｍ够用了，如果表比较多可以适当的增大。

B.日志控制方面

• Innodb_log_file_size：用于设置日志大小，分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系，上限为每个日值上限大小为4G，一般最好控制在几个log文件相加大小在2Ｇ以内。
• Innodb_log_files_in_group：用于设置日志组个数，一般我们可以用２－３个日值组，默认为两个。
• Innodb_log_buffer_size：用于内存中缓存事务，控制在2~8M，如果需要处理大量的text字段或blob字段，可以适当的增大。
• Innodb_flush_logs_at_trx_commit：用于控制事务的提交方式，有0，1，2共三个级别，默认1。如果要求性能高点，可以设置为０或是２，但会丢失1s的事务，需要权衡使用。

C.文件IO分配和空间占用方面

• Innodb_file_per_table：使每个Innodb的表，有自已独立的表空间。
• Innodb_file_io_threads：文件读写IO数，这个参数只在Windows上起作用，在Linux上只会等于４。
• Innodb_open_files：限制Innodb能打开的表的数据，默认300，如果库里的表特别多的情况，可以适当的增大。

D.其他参数

• Innodb_flush_method：表示Innodb和系统打交道的一个IO模型，Linux可以选择O_DIRECT表示直接写入磁盘，禁止系统Cache了，可以显著提高速度。Windows不用设置。
• Innodb_max_dirty_pages_pct：控制Innodb的脏页在缓冲中在那个百分比之下，值在范围1-100，默认为90。当Innodb的内存分配过大，致使swap占用严重时，可以适当的减小调整这个值，使达到swap空间释放出来。建义：这个值最大在90％，最小在15％。太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。

②.MyISAM

特点如下：

• 不支持事务、不支持外键、不支持行级锁；
• 支持表锁：加锁速度快，不容易发生死锁，但并发性较差；
• 支持查询缓存，只缓存索引，不缓存数据，查询速度快于InnoDB；
• 表的数据文件为：.frm(表结构定义)、.MYI(索引）、.MYD(数据)；
• 数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复。

③.Memory

它是基于内存存储数据，加快访问速度，特点如下：

• 不支持事务、不支持外键、不支持行级锁、但支持表锁；
• 支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
• 由于数据是存放在内存中，所以在服务器重启之后，所有数据都会丢失；
• 查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低。

因此，选择合适的存储引擎才是最重要的，这也是MySQL性能优化的重要部分，需考虑的因素如下：

• 使用场景是否需要支持事务，支持外键，支持高并发；
• 是否需要支在线热备；
• 高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引；
• 索引类型方面，不同存储引擎的索引类型并不太一样。

2、存储引擎的使用

①.创建表时指定引擎和修改引擎

create table t_order(id int)engine=myisam;
alter table t_order engine = innodb;

②.在配置文件中修改默认引擎

# linux:vim /etc/my.cnf   windows:my.ini文件
[mysqld] default-storage-engine=INNODB  #配置默认引擎，现在用的mysql默认基本都是InnoDB，所以其实都可以不用配置了

③.不同的存储引擎会生成不同的磁盘文件

# 使用四个不同的引擎来创建的表
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
create table t4(id int)engine=blackhole;

简单介绍下，这些后缀文件的含义和用途：

• .opt文件：用来记录该库的默认字符集编码和字符集排序规则用的。
• .frm的文件：这个文件主要是用来描述数据表结构(id,name字段等)和字段长度等信息。
• .ibd的文件：这个文件主要储存的是采用独立表储存模式时储存数据库的数据信息和索引信息。
• .MYD（MYData）的文件：从名字可以看出，这个是存储数据库数据信息的文件，主要是存储采用独立表储存模式时存储的数据信息。
• .MYI的文件：这个文件主要储存的是数据库的索引信息。
• .ibdata1文件:主要作用也是储存数据信息和索引信息，这个文件在mysql安装目录的data文件夹下。

关于数据恢复：

在进行数据恢复时，如果用的是MyISAM存储引擎，那么数据很好恢复，只要将相应.frm, .MYD, .MYI文件拷贝过去即可。但是如果是InnoDB存储引擎的话，则每一个数据表都是一个单独的文件，只将相应的.frm和.ibd文件拷贝过去是不够的，必须在你的ibd文件的tablespace id和ibdata1文件中的元信息的tablespace id一致才可以。

三、总结

这里需要重点理解，MySQL的四层架构及一条SQL语句在服务端是如何被执行的，存储引擎的类别有哪些，MyISAM/InnoDB/Memory这三种存储引擎的特点、使用场景、如何调优等，掌握了这些才是真正进入了MySQL的底层世界，更多底层的东西还要去探索和理解，如后续要梳理的锁机制、存储引擎的事务ACID及如何保证、索引底层结构和实现原理、慢查询优化及其他性能优化等等。任重而道远，加油吧！！！