工作中常常用到 MySQL，但对 MySQL 底层原理不够熟悉。知其然，知其所以然。所以我决定深入学习 MySQL 底层知识，并产出一些备忘笔记。本文主要介绍 MySQL 的体系结构。
MySQL 体系结构
MySQL主要划分为两个部分：Server 层和存储引擎层。
Server层：实现了大部分核心功能（内置函数、存储过程、触发器、视图等等），组成模块包括连接器、查询缓存、解析器、优化器以及执行器等等；
存储引擎层：实现数据的存储和读取，向 Server 层提供操作数据的接口。支持插拔，可选的类型包括：InnoDB、MyISAM、Memory等。最常用的是 InnoDB，它从MySQL 5.5.8后成为默认存储引擎。
下图展示 MySQL 官方文档提供的体系结构图：
下图展示的是简化后的 MySQL 的基本架构图。
下面展示使用 InnoDB 引擎的体系结构图以及 SQL 执行过程：
连接器
连接器就像是MySQL的“门神”，它负责跟客户端建立连接、获取权限、维持和管理连接。
查看连接情况
mysql> show processlist; +------+------+-----------+------+---------+-------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+-----------+------+---------+-------+----------+------------------+ | 1530 | root | localhost | NULL | Sleep | 44800 | | NULL | | 1531 | root | localhost | qds | Query | 0 | starting | show processlist | +------+------+-----------+------+---------+-------+----------+------------------+ 2 rows in set (0.03 sec)连接空闲超时会自动断开，可以通过 wait_timeout 参数控制，默认8小时；
客户端不应频繁新建连接，尽量使用长连接；
连接应该定时断开或者执行 mysql_reset_connection 重新初始化连接资源，避免占用过多内存；
数据库账号权限的变更不会影响已建立的连接，只有该连接断开重连才会生效。常见的 JDBC 连接池都带有定时重连的功能，比如 HikariPool：
private PoolEntry createPoolEntry() { try { final PoolEntry poolEntry = newPoolEntry(); // 连接最大存活时间 final long maxLifetime = config.getMaxLifetime(); if (maxLifetime > 0) { // variance up to 2.5% of the maxlifetime final long variance = maxLifetime > 10_000 ? ThreadLocalRandom.current().nextLong( maxLifetime / 40 ) : 0; final long lifetime = maxLifetime - variance; // 定时任务检查连接是否空闲，空闲的连接将会被关闭 poolEntry.setFutureEol(houseKeepingExecutorService.schedule(new Runnable() { @Override public void run() { softEvictConnection(poolEntry, "(connection has passed maxLifetime)", false /* not owner */); } }, lifetime, MILLISECONDS)); } LOGGER.debug("{} - Added connection {}", poolName, poolEntry.connection); return poolEntry; } catch (Exception e) { if (poolState == POOL_NORMAL) { LOGGER.debug("{} - Cannot acquire connection from data source", poolName, e); } return null; } }
解析器
解析器有两个功能：
- 词法分析：将SQL语句数据库表列建立关联；
- 语法分析：检测SQL语句是否符合MySQL语法规则。
优化器
优化器负责从不同的执行计划中选出效率最高的方案，包括决定表的连接顺序、选择哪个索引等等。此外还会涉及查询重写。
执行器
执行器主要是负责检查表权限，并且调用存储引擎的接口完成执行计划，写binlog日志等。
查询缓存
对于SELECT语句，在解析查询之前，服务器会先检查查询缓存。如果能在缓存中找到对应的查询，服务器可以直接返回缓存的结果，而不必进行解析、优化和执行等过程。
注意，频繁更新的表的查询缓存会频繁失效，不建议开启查询缓存。MySQL 8.0 版本后不再支持查询缓存。
存储引擎
MySQL 支持多种存储引擎，用户在新建表的时候可以自定义指定该表采用的存储引擎。用户也可以根据 MySQL 提供的接口定义，实现自定义的存储引擎。
最常用的第三方存储引擎是 InnoDB。在MySQL 5.5.8后，它成为默认存储引擎。
InnoDB 存储引擎
InnoDB 主要特点是：
- 支持行锁、支持外键，并支持非锁定的读；
- 每张表数据单独保存到一个独立的 idb 文件中；
- 通过多版本并发控制（MVCC）来实现高并发特性，并且实现 SQL 标准的4种事务隔离级别，默认为可重复读；
- 使用间隙锁（next-key locking）来避免幻读现象产生；
- 每张表的主键都采用聚簇索引，如果没有显式指定主键，则默认自动生成一个6字节的 row_id 作为主键。
MyISAM 存储引擎
MyISAM 主要特点是：
- MySQL 5.5.8 版本之前的默认存储引擎；
- 不支持事务；
- 不支持行锁，只支持表锁；
- 支持全文索引；
- 存储引擎表由 MYD 和 MYI 组成，MYD 用来存放数据文件，MYI 用来存放索引文件。
其他存储引擎
NDB 存储引擎、Memory 存储引擎、Archive 存储引擎、Federated 存储引擎、Maria 存储引擎等等。
参考资料
- MySQL实战45讲: 非常适合入门的MySQL学习资料；
- 《高性能MySQL》
- 《MySQL技术内幕（InnoDB存储引擎）》
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 duval1024@gmail.com
