mysql体系结构

一、MySQL 体系结构要理解存储引擎首先要知道它在 MySQL 整体架构中的位置。MySQL 从上到下分为四层1、连接层负责管理客户端连接和认证2、服务层是核心负责 SQL 解析、优化和执行3、存储引擎层负责数据的实际存取4、最底层是操作系统文件系统。存储引擎是可插拔的这是 MySQL 架构最大的特点。同一个数据库里不同的表可以用不同的存储引擎上层的 SQL 接口完全不需要关心底下用的是哪种引擎。MySQL 四层核心架构MySQL 是典型的分层解耦架构每一层职责清晰从外到内依次是表格层级核心定位你必须掌握的核心能力1. 连接层客户端入口连接管理、身份认证、线程复用2. 服务层MySQL 的「大脑」SQL 解析、优化、执行、缓存3. 引擎层MySQL 的「心脏」数据读写、事务、锁、索引InnoDB 核心4. 存储层数据的「仓库」物理存储、日志管理、数据落盘 第一层连接层最外层入口层核心作用负责客户端与MySQL Server的连接管理、身份认证是所有请求的入口。你必须掌握的核心客户端连接器支持多种接入方式Native C API、JDBCJava、ODBC、PHP/Python 等对应你工业场景中 C#/Python 脚本连接 MySQL 的底层原理。作用把不同语言的请求统一转换成 MySQL 能识别的协议。连接池Connection Pool核心功能线程复用、连接限制、内存校验、缓存管理为什么重要避免频繁创建 / 销毁线程开销极大用连接池复用已有连接提升高并发场景性能。你需要懂连接池的作用、参数配置比如max_connections最大连接数。身份认证客户端连接时校验用户名、密码、IP 权限只有认证通过才能进入下一层。 第二层服务层MySQL 的「大脑」核心中的核心这是 MySQL 最核心的一层所有 SQL 解析、优化、执行逻辑都在这里也是你必须吃透的层级。核心模块 你要掌握的重点SQL 接口SQL Interface作用接收客户端发来的 SQLDML/DDL/ 存储过程 / 视图 / 触发器等统一处理。你要懂DML增删改查、DDL建表 / 改结构、DCL权限的区别对应你日常写的 SQL 语法。解析器Parser核心工作语法解析 语义校验 权限校验流程把 SQL 字符串拆解成「语法树」检查语法是否正确比如少写逗号、关键字错误校验语义比如表 / 字段是否存在、用户有没有操作权限你要懂解析器的作用SQL 报错语法错误就是这一层抛出的。查询优化器Optimizer核心工作选择最优执行计划作用基于统计信息分析多种执行路径比如用不用索引、先连哪张表选出成本最低的方案。你要懂EXPLAIN工具就是看优化器生成的执行计划这是你调优 SQL 的核心工具。缓存Cache Buffer作用缓存查询结果、引擎相关缓存减少磁盘 IO。注意MySQL 8.0 已移除查询缓存现在主要是引擎层的缓冲池InnoDB Buffer Pool。系统管理工具包含备份恢复、主从复制、集群、权限管理、元数据管理等对应你日常的运维操作。 第三层引擎层MySQL 的「心脏」数据操作的核心这是 MySQL 最具特色的可插拔存储引擎架构也是必须 100% 掌握的层级直接决定数据的存储、事务、锁、性能。核心重点可插拔架构可以按需选择不同的存储引擎比如 InnoDB、MyISAM 等不同引擎有不同特性。你要懂如何查看 / 指定引擎show engines;、建表时engineInnoDB。核心引擎InnoDB你唯一需要重点掌握的为什么重要MySQL 5.5 默认引擎支持事务、行级锁、外键、崩溃恢复是生产环境唯一推荐的引擎。核心职责索引管理B 树索引、聚簇索引存储管理数据页、缓冲池事务管理ACID、Redo/Undo 日志锁机制行锁、表锁、悲观锁 / 乐观锁对应你之前学的for update其他引擎了解即可MyISAM不支持事务、表锁仅适合读多写少的场景已淘汰Memory数据存内存速度快断电丢失临时表用Archive压缩存储适合归档数据核心管理模块Memory, Index Storage Management对应你学的索引、缓冲池、存储结构是 InnoDB 性能调优的核心。 第四层存储层数据最终落盘的「仓库」核心作用负责数据的物理存储、日志管理把引擎层的操作落地到磁盘。你必须掌握的核心系统文件支持的文件系统NTFS、ext2/3/4、NFS 等对应你服务器的磁盘存储。核心日志文件面试 / 运维必背表格日志类型作用你要懂的核心Redo Log重做日志崩溃恢复保证事务持久性InnoDB 核心记录数据修改宕机后重放恢复数据Undo Log回滚日志事务回滚、MVCC记录数据修改前的版本回滚用实现多版本并发控制Binary Logbinlog主从复制、数据恢复记录所有写操作用于主从同步、数据备份Error Log错误日志记录启动 / 运行错误排查数据库故障Slow Query Log慢查询日志记录慢 SQL定位性能问题优化慢查询数据 / 索引文件InnoDB 数据和索引存在.ibd文件中MyISAM 分.MYD数据和.MYI索引。二、存储引擎简介sql-- 查看 MySQL 支持的所有存储引擎 SHOW ENGINES; -- 查看某张表当前使用的存储引擎 SHOW CREATE TABLE account; -- 建表时指定存储引擎 CREATE TABLE t1 (id INT, name VARCHAR(20)) ENGINE InnoDB; CREATE TABLE t2 (id INT, name VARCHAR(20)) ENGINE MyISAM; CREATE TABLE t3 (id INT, name VARCHAR(20)) ENGINE Memory;MySQL 支持十几种存储引擎但实际开发中最常用的就是三种InnoDB、MyISAM 和 Memory。三、存储引擎特点这是核心重点三大引擎的特性对比关于文件结构有个重要区别InnoDB 把数据和索引放在同一个.ibd文件里聚簇索引而 MyISAM 把表结构、数据、索引分成三个独立文件存储。InnoDB每一个表都有一个xxx.ibd文件二进制文件。查看可以使用命令行 idb2sdi account.ibd; 查看二进制文件idb2sdi.exe 文件在InnoDB逻辑存储结构MYSIAMMyISAM是MySQL早期的默认存储引擎Memory区别四、存储引擎选择实际工作中选择哪个引擎主要看业务需求用一句话总结选择原则InnoDB只要涉及增删改、需要事务、有并发写入就用 InnoDB。这是 99% 的业务场景的正确选择也是 MySQL 5.5 之后的默认引擎。MyISAM纯读场景、日志归档、数据仓库查询。但现在 InnoDB 读性能也已经很强MyISAM 的使用场景越来越少。Memory做临时缓存表、会话临时数据等但不能持久化重启就丢。实际上 MySQL 内部做排序和聚合时会自动创建内存临时表。总结一览特性InnoDBMyISAMMemory事务支持不支持不支持外键支持不支持不支持锁粒度行锁表锁表锁崩溃恢复支持弱数据丢失索引BTree 聚簇BTree 非聚簇Hash持久化磁盘磁盘内存适合场景通用默认选择只读/读多写少临时数据/缓存