MySQL知识体系梳理

过往对MySQL的认识比较零碎东一块西一块经常忘记。最近花了点时间重新梳理了下试图从整体的视角理解它形成体系化的知识这样既有助于记忆也能够提升技术水平。内容是基于过往的工作经验结合智能助手(Qwen3.5、Kimi2.5、Gemini3Flash)的帮助最终整理出来有些知识点仅仅是提了一下后续会继续优化下细节另外内容是个人的理解有些地方可能有不对的地方欢迎大家指出。一. 整体视角在进入正题之前先简要介绍一下数据库的演变常见的数据库包括如下类型1. 关系数据库典型代表是MySQL2. NoSql内存数据库包括KV存储、文档数据库3. NewSql数据库TiDB, Oceanbase, 结合关系数据库和Nosql特点既支持事务又支持水平扩展4. 垂直领域数据库搜索、向量、时序数据库二.模块介绍接下来详细介绍下我对MySQL的理解采用问答的方式先说一下MySQL 是什么有什么特点MySQL 是一个基于磁盘的关系数据库支持事务、支持崩溃恢复高可用数据高可靠。2.1. 整体架构1. MySQL 架构是什么MySQL采用分层架构分为server层和存储引擎层。2. server层负责什么功能server层包含连接器、分析器、优化器执行器。连接器负责管理客户端连接、身份认证、权限验证每个连接对应一个线程默认采用One-Thread-Per-Connection 模型8.0版本移除了查询缓存因为表任何更新都会导致该表所有缓存失效高并发下性能反而下降。分析器负责词法分析、语法分析生成解析树。优化器基于成本模型Cost-Based Optimization, CBO从多种可能的执行路径中选择最优执行计划。主要的优化任务包括索引选择JOIN 顺序重排等等.执行器根据优化器生成的执行计划调用存储引擎 API 获取数据并完成最终结果组装。3. 存储引擎层负责什么功能存储引擎负责数据CRUD, 索引管理事务支持并发控制。4. server层功能能否放到存储引擎层实现比如优化器和执行器技术上是可行的现代很多数据库已经采用了计算靠近存储的思想。对于MySQL来说如果将优化器、执行器下放到引擎层则不同引擎之间需要重复实现如果不同引擎实现不一致效果也不一样。另外优化某个优化器bug需要更新所有引擎成本大。4. 一条查询语句是如何执行的与server层建立连接分析器进行词法、语法分析、优化器生成执行计划执行器负责调用存储引擎读写数据。5. 为什么MySQL并发数远低于redisMySQL对于每一个连接都新增一个线程处理redis使用单线程IO多路复用。首先线程创建开销大包括线程自身资源堆栈其次调度开销大导致cpu片上缓存tlb失效等等。另外,MySQL操作比较重涉及到多个锁、磁盘IO、读写文件(undolog, redolog, binlog)操作而redis是基于内存操作。2.2. 数据存储1. 存储引擎底层实现有哪些B树LSM树B树对磁盘IO很友好。LSM适合写多读少场景因为是顺序追加写写入性能很高。除此之外还有哈希索引、倒排索引、列式存储等等。2. InnoDB存储引擎特点支持事务行锁、间隙锁、临键锁支持崩溃恢复。3. InnoDB存储引擎如何存储数据使用B树存储数据叶子节点存储行数据非叶结点仅存储索引和下级节点指针。4. B树特点4.1 仅叶子节点存放数据非叶节点存储索引树的深度少。4.2 叶子节点指针连接范围查询效率高5. B树与B树、红黑树等区别为什么不使用其他树B树和B树是多叉树红黑树是二叉树B树和B树因为是多叉树树深度较低多适用于磁盘操作能够显著降低磁盘IO。相比B树B树任何节点既可以存储数据也可以存储索引树深度比B树高。6. B树通常深度是多少怎么估算取决于页大小、主键大小、数据量决定。非叶结点存储索引项索引项里面包含索引值和指向下一层的指针。单个页能存放的数据量称之为扇出率如果扇出率为1000则1层深度存放1000以内元素2层则可以存放1000^2百万条记录。3层则可以达到10亿级别。如果索引项长度比较大则单个页存放的索引条目有限如此就会加深B树深度。2.3. 索引1. InnoDB索引是如何实现的InnoDB支持哈希索引和B树索引。2. 索引有哪些类型主键(聚簇)索引二级(非聚簇)索引。3. 什么是主键索引(聚簇索引)什么是二级索引(非聚簇索引、联合索引)主键索引内容包含行记录二级索引存储的是主键ID联合索引是一个多列索引。4. 什么是回表通过二级索引查询内容的时候如果不满足覆盖索引则需要回表查询行内容。回表最大的问题在于会产生随机读带来性能下降。5. 最左索引前缀是什么对于一个多列索引如果仅使用该索引的前几个列也能够命中索引比如对于联合索引a_b_c如果查询条件仅包含a或者a,b则也可以复用该索引。需要注意的是对于一个多列索引只要任意一个列使用了范围查询则该列后面的字段无法使用。6. 索引下推优化ICP是什么这个主要用于利用索引中包含的信息提前进行过滤减少回表数量。比如存在索引idx_a_b, 查询语句是select * from t where a 5 and b10;这时候只会使用索引idx_a_b中的a进行判断假设查询到1000条记录此时需要回表读取1000次。如果使用ICP存储引擎会检查索引中b选项是否满足条件如不满足则提前过滤从而减少回表数量。7. 索引失效场景最常见的常见包括1. 对索引列使用函数。2. 隐式类型转换。3.不满足最左索引前缀。8. 如何建立高效的索引1. 主键尽可能短、连续选择区分度大的列放在前面将范围查询列放到最后。主键太长会造成二级索引占空间比较大树深度变深。主键不连续容易导致页分裂这会带来性能下降其次索引列的区分度要大否则索引效果会不好比如性别字段只有2个选项查询指定性别都会扫描一半数据。2.4. 事务与并发控制1. 什么是事务介绍一下ACID事务是一组操作要么全完成要么全部完成完成后数据不会丢失。 事务的特点是ACIDA表示原子性C表示一致性I表示隔离性D表示持久化。2. 隔离级别有哪些?隔离级别包括读未提交读提交可重复读串行化。3. 脏读、不可重复读、幻读是什么问题又是如何解决的脏读是指读到其它事务未提交的数据。不可重复读是指在同一个事务中前后两次读取到了同一行不同的数据。幻读是指在同一个事务中前后两次读后一次读到了新的行。脏读和不可重复读是通过调整隔离级别来解决。innodb在可重复读隔离级别下解决了快照读的幻读问题但如果混合使用快照读和当前读仍然会出现幻读问题。比如使用A事务使用快照读读取数据B事务新增记录并提交A事务再使用当前读就会出现幻读。4. InnoDB事务是如何实现的通过undolog版本链一致性视图。5. 介绍MVCCundolog版本链、一致性视图可见性判断undolog记录了行的不同版本每个版本包括该行对应的事务ID指向上一个版本的指针通过指针连接形成一个版本链。一致性视图是指一个事务在事务期间能够看到的版本。对于读提交每次查询都会新建一致性视图对于可重复读,会在事务启动的时候创建一致性视图在事务期间会一直使用该视图。一致性视图里面包含如下内容当前活跃的事务ID列表minTransID 等于最小事务ID, maxTransID 等于最大的事务ID1, 可见性判断规则如下1. 如果当前版本对应的事务ID小于minTransID说明是之前提交的可见。2. 如果当前版本对应的事务ID大于maxTransID说明是未来事务提交的不可见。3. 如果当前版本对应的事务ID等于自身可见。4. 如果当前版本对应的事务ID在minTransIDmaxTransID之间则检查是否在活跃事务列表之间如果在说明还未提交不可见。 如果不在说明在启动之前已经提交可见。6. 只读长事务的影响只读长事务会导致undolog膨胀因为undolog需要一直保留防止只读长事务访问旧版本数据。undolog过大占用磁盘空间严重情况下导致磁盘满进而数据库不可用。另外只读长事务查询性能会很差如果某个行更新了很多次导致undolog版本链过长每次查询的时候需要遍历整个版本链才能找到可见的数据。7. 读写长事务的影响只读长事务的影响在读写长事务场景下同样存在不仅如此对于读写长事务因为更改数据会加锁导致其它事务等待如果自身频繁修改某一行数据导致该行版本链特别长会影响其它查询业务性能。如果更改了大量数据生成大量的binlog会一次性发送给从库从库执行也需要一段时间带来主从延迟。如果回滚会产生回滚风暴。8. 可重复读隔离级别下幻读是如何解决的在可重复读隔离级别下InnoDB引擎解决的是快照读的幻读问题这也是SQL标准定义的幻读问题。但如果混用快照读和当前读还是会出现幻读问题。比如在同一个事务之中先使用快照读后使用当前读。9. 可重复读隔离级别下会出现不可重复读吗会出现比如先使用快照读在使用当前读。10. MySQL有哪些锁分别介绍下功能全局锁表锁元数据锁意向锁行锁、间隙锁临键锁。其中全局锁表锁元数据锁是server层InnoDB锁包括行锁、间隙锁临键锁、意向锁。11. 为什么需要意向锁使用表锁或者不行吗如果没有意向锁一个事务想对整张表加锁它必须先确认这张表里有没有别的事务持有行锁 如果没有意向锁唯一的检查办法就是遍历这张表的所有行逐行检查有没有被锁——这个开销随着表的行数线性增长代价太大。意向锁就是为了让这个检查变成O(1)操作。2.5. 高可用与高可靠日志与复制1. 有哪些日志各自的作用是什么undolog, 记录行的不同版本形成版本链配合MVCC实现事务。redolog, innodb引擎独有记录物理页的修改一段循环buffer, 追加写,三种刷盘策略(1.只写内存。2.只写页缓存。 3.每个事务都刷盘。 在性能和可靠性性之间做取舍)。 主要用于崩溃恢复。binlog, server层独有记录逻辑日志有不同的记录格式行可能出现主从不一致语句内容可能太大mixed结合行语句模式的优点, 主要用于主从复制。刷盘策略也有三种(1.每个事务都刷。 2.每N个事务刷一次。 3.只写内容由操作系统负责刷盘)。relaylog中转日志2. redolog和binlog如何保持一致通过两阶段提交先写redolog prepare 状态后更新binlog再写redolog commit状态3. 如何实现崩溃恢复重启后检查redolog和binlog如果redolog已经commit或者binlog完整则提交事务。否则回滚。4. 能否使用binlog做崩溃恢复binlog主要用于数据复制记录的是逻辑日志无法实现崩溃恢复。5. 能否使用redolog做数据复制redolog用于崩溃恢复记录的是物理日志循环写无法用于主从同步。6. 数据复制策略都有哪些从早期的异步复制到半同步复制再到组复制MGR异步复制是主库写完binlog之后返回客户端成功异步复制到从机存在数据丢失的可能性。半同步复制是主库写完binlog之后不返回客户端而是等从机确认之后在提交。确保了至少有两份存储。如果从库超时则降级为异步复制。MGR是MySQL 分布式部署的一种存在两种拓扑模式单主或者多主每次写需要超过半数以上。7. 主库延迟增加有哪些原因1. 从库配置低2. 大事务3. 从库高负载2.6. 性能优化1. 常见卡顿原因都有哪些1.1 大事务1.2 死锁锁竞争1.3 慢查询2. 如何优化索引2.1 主键尽可能短减少索引大小。2.2 索引字段区分度要大区分度小等同于全表扫描。2.3 尽可能使用覆盖索引利用索引进行范围查询。3. 海量数据如何存储分库分表和内置的分区有什么区别分区是单机数据库内部功能对应用完全透明存在单点故障受限于单机性能支持事务跨分区查询。分库分表一般由应用层或中间件控制需改代码或引入中间件水平扩展。4. 什么是深分页为什么会出现这个问题如何解决这个问题指的是offset偏移过大每次都要扫描大量无效行数据。 核心问题在于MVCC需要检查每一行数据可见性。优化手段1. 游标查询 2.延迟关联 3.覆盖索引5. MySQL有哪些缓存Buffer Pool 是 InnoDB 在内存中缓存数据页Data Pages和索引页Index Pages 的区域。Change Buffer 用于缓存对非唯一二级索引Secondary Index的 INSERT/UPDATE/DELETE 操作当对应的索引页不在 Buffer Pool 中时延迟合并Merge到索引页。作用是将随机 I/O 转化为顺序 I/O大幅提升写入性能。以下操作会导致buffer pool缓存命中率降低1. 全表扫描全表扫描会将大量冷数据页加载到 Buffer Pool。2. 大结果集排序或临时表这些操作会挤占buffer pool空间3. 大事务4. 冗余、重复索引挤占空间5. 刚启动bufferpool为空可以预热热点页。2.7. 三方协同1. 使用redis作缓存如何保存数据一致性因为涉及到两个不同的存储要想实现强一致就得引入分布式共识协议如paxos或者raft这样性能会下降。且在CAP约束下存在可用性问题。一般会采用最终一致性通过延迟双删或者异步更新、定时同步等机制保证最终一致性。2.8. 其它1. 如何实现分布式锁借助唯一索引来实现需要考虑单点故障问题可以参考redlock写多个实例来解决。如何解决时钟漂移问题即因为gc或者其它原因锁过期了但业务侧不知道可以参考martin kleppmann 的建议业务侧加版本号机制。2. 分布式ID利用自增ID处于性能考虑可以调整步长或者预生成buffer池。三.参考资料1. 《MySQL 45讲》