​​说说MySQL的架构体系！

最近群里一位兄弟在面试中被问到：「MySQL的架构体系是什么」。

虽然他搞java开发好几年了，也一直使用的是MySQL数据库，但是面对这个问题依然是一脸懵逼，还以为面试官要问索引、慢查询、性能优化之类的（因为这些都是网上找点面试题背过了）。

但这位面试官不按套路出牌，这位兄弟当场就是脸红耳赤的，心想nnd居然会这么问。其实面试中面试官的问题有千千万，有的问题确实背背面试题就能应对，但不是所有的面试题咱们都能背下来的。

今天我们就来聊聊MySQL的架构体系，尽管咱们是java开发人员，但是在日常开发过程中也会经常和MySQL数据库打交道。如果公司有DBA能干点事还稍微好点，如果是没有DBA或者DBA没什么卵用的情况下，我们还是很有必要了解MySQL的整个体系的，况且在面试中遇到了也是一个加分项。

想要知道一条SQL是怎么查询的，只要对MySQL整个体系搞清楚了，才能说出个123。

所以于情于理，我们很有必要学习一下MySQL的架构体系的。

平时，和小伙伴们聊天的时候，经常会把MySQL当做我们开发的一个软件系统，既然是软件系统，那么就有个架构图，以及架构是如何分层的，每一层的功能是什么。

下面我们就来看看MySQL的整体架构图。

MySQL架构图

再来看看我们开发的系统架构图：

其实还是蛮相似的。都有分层的概念。既然我们开发的软件系统能进行分层，那么MySQL能分层吗？

答案是：能，下面我们就来聊聊MySQL的分层情况以及每一层的功能。

架构图分层

上面的架构图我们可以对其进行拆分，并做简要的说明。

连接层

与客户端打交道，上面已经写明了能支持的的语言。客户端的链接支持的协议很多，比如我们在 Java 开发中的 JDBC。

服务层

连接池

主要是负责存储和管理客户端与数据库的链接，一个线程负责管理一个连接。自从引入了连接池以后，官方报道：当数据库的连接数达到128后，使用连接池与没有连接池的性能是提升了n倍（反正就是性能大大的提升了）。

连接建立完成后，就可以执行select语句了。执行逻辑就会先来到缓存模块。

缓存

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果会以key-value对的形式存储在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key（命中），那么这个value就会被直接返回给客户端。

如果在缓存中未命中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。这里可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的某一条数据更新，这个表上所有的查询缓存都会被清空。

因此可能很费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。

比如：一个系统配置表，那这张表上的查询才适合使用查询缓存。

好在MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。

「注意」：MySQL 8.0版本直接将查询缓存的整块功能删掉了，标志着MySQL8.0开始彻底没有缓存这个功能了。

解析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。

做完了词法分析以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

如果我们在拼写SQL时候，少了或者写错了某个字母，，就会收到“You have an error in your SQL syntax”的错误提醒。

比如下面这个案例：

错误在于WHERE关键字中差了一个E。

同样，我们使用的SQL如果某个字段不存在。

一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容，仅供参考，有时候这个提示也不是非常靠谱。

经过分析器对SQL进行了分析，并且没有报错。那么此时就进入优化器中，对SQL进行优化。

优化器

优化器主要是在我们的数据库表中，如果存在多个多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序 。

比如说：

SELECT a.id, b.id FROM t_user a join t_user_detail b WHERE a.id=b.user_id and a.user_name='田维常' and b.id=10001

它会在条件查询上进行优化处理。

优化器处理完成过后，此时就已经确定了SQL的执行方案。然后继续进入执行器中。

执行器

首先，肯定是要判断权限，就是有没有权限执行这条SQL。工作中可能会对某些客户端进行权限控制。

比如说：生产环境中，对于大部分开发人员都只开查询权限，没有增删改权限（部分小公司除外）。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

存储引擎层

存储引擎的概念是MySQL里面才有的，不是所有的关系型数据库都有存储引擎这个概念 。

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(Table Type，即存储和操作此表的类型)。

MySQL5.5版本(mysql 版本 < 5.5版本) 以前，默认使用的存储引擎是MyISAM 。

MySQL5.5版本(mysql 版本 >= 5.5版本) 以后，默认使用的存储引擎是InnoDB 。

​

下面对部分相对使用多的引擎进行一个对比：

在实际项目中，大多数使用InnoDB，然后是MyISAM，至于其他存储引擎使用的非常至少。

MyISAM 与 InnoDB 引擎的区别

Mysql5.5 版本之前默认的存储引擎就是 MyISAM 存储引擎，MySQL 中比较多的系统表使用 MyISAM 存储引擎，系统临时表也会用到 MyISAM 存储引擎，但是在 Mysql5.5 之后默认的存储引擎就是 InnoDB 存储引擎了。

两个主要原因：

第一个原因是MyISAM 是表级锁定，限制了数据库读/写的性能；

另外一个原因是MyISAM 不支持事务，基于以上两点，InnoDB 引擎可以锁到行。

​

如何在两种存储引擎中进行选择？

是否有事务操作？有，InnoDB。

​

是否存储并发修改？有，InnoDB。

​

是否追求快速查询，且数据修改较少？是，MyISAM。

​

是否使用全文索引？如果不引用第三方框架，可以选择MyISAM，但是可以选用第三方框架和InnDB效率会更高。

系统文件存储层

系统文件存储层主要是负责将数据库的数据和日志存储在系统的文件中，同时完成与存储引擎的之间的打交道，是文件的物理存储层。

比如：数据文件、日志文件、pid文件、配置文件等。

数据文件

「db.opt文件」：记录这个数据库的默认使用的字符集和校验规则。

「frm文件」：存储于边相关的元数据信息，包含表结构的定义信息等，每一张表都会有一个frm文件与之对应。

「MYD文件」：MyISAM存储引擎专用的文件，存储MyISAM表的数据信息，每一张MyISAM表都有有一个.MYD文件。

「MYI文件」：也是MyISAM存储引擎专用的文件，存放MyISAM表的索引相关信息，每一张MyISAM表都有对应的.MYI文件。

「ibd文件和ibdata文件」：存放InnoDB的数据文件（包括索引）。InnoDB存储引擎有两种表空间方式：独立表空间和共享表空间。

独享表空间使用ibd文件来存放数据，并且每一张InnoDB表存在与之对应的.ibd文件。

共享表空间使用ibdata文件，所有表共同使用一个或者多个.ibdata文件。

「ibdata1文件」：系统表空间数据文件，存储表元数据、Undo日志等。

「ib_logfile0、ib_logfile0文件」：Redo log日志文件。

日志文件

错误日志：默认是开启状态，可以通过命令查看：

show variables like '%log_error%';

二进制日志binary log：记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行耗时；但是不记录查询select、show等不修改数据的SQL。主要用于数据库恢复和数据库主从复制。也是大家常说的binlog日志。

show variables like '%log_log%';//查看是否开启binlog日志记录。show variables like '%binllog%';//查看参数show binary logs;//查看日志文件

慢查询日志：记录查询数据库超时的所有SQL，默认是10秒。

show variables like '%slow_query%'；//查看是否开启慢查询日志记录。show variables '%long_query_time%'；//查看时长

通用查询日志：记录一般查询语句；

show variables like '%general%'；

配置文件

用于存放MySQL所有的配置信息的文件，比如：my.cnf、my.ini等。

「pid文件」

pid文件是mysqld应用程序在Linux或者Unix操作系统下的一个进程文件，和许多其他Linux或者Unix服务端程序一样，该文件放着自己的进程id。

「socket文件」

socket文件也是Linux和Unix操作系统下才有的，用户在Linux和Unix操作系统下客户端连接可以不通过TCP/IP网络而直接使用Unix socket来连接MySQL数据库。

SQL查询流程图

总结

MySQL整个系统我们可以看成是我们日常开发的软件系统，也有接入层，专门对接外面客户端的，和我们系统的网关就很像，缓存也就类似我们业务代码中使用的缓存，解析器可以理解为业务系统中参数解析以及参数校验，优化层可以当做我们开发代码优化的手段，然后存储引擎就相当于我们的持久层，文件系统相当于整个业务系统中的数据库。

可能比喻不是非常的恰当，但是希望大家能领略轻重的含义，目的只有一个，那就是让大家能轻松掌握MySQL的整体情况。​​​​

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；