MySQL 其实是 上层组件、下层存储引擎 组合起来的结构可以理解为┌─────────────────────────────────┐ │ Server 层 │ │ (连接器、查询缓存、分析器、 │ │ 优化器、执行器、内置函数等) │ └────────────┬────────────────────┘ │ 统一的 API 接口 ┌────────────▼────────────────────┐ │ 存储引擎层 │ │ (InnoDB、MyISAM、Memory 等) │ │ 负责数据的存储和提取 │ └─────────────────────────────────┘更流程化地说客户端 ↓ 连接器 ↓ SQL接口 ↓ 解析器 ↓ 优化器 ↓ 执行器 ↓ 存储引擎InnoDB需要注意MySQL 本身并不真正负责操作数据真正操作数据的是存储引擎。一条 SQL 是怎么执行的例如SELECT * FROM user WHERE id 1;MySQL 内部会经历1.连接器写 SQL 之前先要和连接器建立 TCP 连接。登录 MySQL、建立连接、校验账号密码、权限验证都是连接器负责。由于后续这个连接里的操作都使用此时读取到的权限所以修改权限后要重新连接才生效。例如mysql -uroot -p就是连接器在工作。连接空闲超过 wait_timeout 默认是8小时后会自动断开。长连接 vs 短连接短连接执行完几个SQL就断开下次再重连。频繁创建连接开销大长连接连接复用但MySQL临时使用的内存是绑定在连接对象上的长连接累积可能导致内存暴涨OOM因为不会被立即释放解决定期断开长连接或执行mysql_reset_connection重置连接状态MySQL 5.72.查询缓存8.0 已删除执行 SQL 前先看这条 SQL 有没有之前查过、结果有没有缓存。Key 是 SQL 语句Value 是结果。有就直接返回不走后面流程现在 MySQL8.0 已经彻底删掉查询缓存。任何对表的更新操作INSERT/UPDATE/DELETE都会清空该表的所有查询缓存。且对于写多读少的业务查询缓存命中率极低反而成了性能负担。所以 8.0 后彻底删掉了查询缓存。需要注意的是8.0 之后删除的是 查询缓存query cacheInnoDB Buffer Pool 仍然在发挥缓存作用缓存的是 ⌈ 页 ⌋ 数据页、索引页而不是整条 SQL 结果3.解析器做两件事词法分析识别关键字 select、from、where分析这些关键字是什么意思语法分析判断 SQL 写得合不合法语法错在这里直接报错本质上就是将 SQL 转换为 MySQL 可以理解的结构。注意分析器只检查语法不检查语义比如表是否存在、列是否存在是在执行阶段或预处理阶段检查的。4.优化器SQL 语句可能有多种执行方式优化器选代价最小、速度最快的例如SELECT * FROM user WHERE age 20 AND name Tom;优化器会决定用哪个索引多表关联顺序JOIN 顺序是否走全表扫描子查询是否转为半连接也就是说我们写的 SQL ≠ 真正的执行方式。真正的执行计划由优化器决定。这有点像编译器优化。注意它选的其实不一定真的是最优的。5.执行器先判断对表有没有权限连接器那一步获取的权限如果有权限再调用存储引擎InnoDB接口真正去磁盘 / 内存一行行拿数据。对于没有索引的查询逐条读取并比对有索引则按索引去查找满足条件的第一行再下一条直到不满足然后返回结果给客户端。存储引擎会决定是从 Buffer Pool 读页还是从磁盘读页。需要注意执行器只是调用存储引擎接口真正干活的是存储引擎它真正负责数据存储、索引、事务、锁。MySQL 是老板存储引擎是真正干活的人。如果再详细一点涉及到日志执行器 - InnoDB引擎 - 先写 redo log (prepare阶段) - 写 binlog - redo log commitredo log重做日志InnoDB 独有的物理日志用于崩溃恢复。采用 WALWrite-Ahead Logging先写日志再刷盘保证不丢数据。redo log 是循环写。binlog归档日志Server 层的逻辑日志记录语句的原始逻辑用于主从复制和数据恢复。binlog 是追加写。两阶段提交先让 redo log 处于 prepare 状态再写 binlog最后提交 redo log 。这样确保两个日志一致崩溃恢复时能确定事务状态。循环写和追加写具体是什么意思下文会讲。打个比方MySQL 服务层连接器、分析器、优化器、执行器公司老板 / 管理层InnoDB 存储引擎仓库管理员磁盘真实仓库货架内存 Buffer Pool仓库门口的临时货架缓存流程老板执行器说帮我查 id100 的数据仓库管理员InnoDB自己决定先看门口临时货架内存 BufferPool有没有这条数据有 → 直接从内存拿快没有 → 去真实仓库磁盘把数据页读出来放到内存再返回给老板 注意**执行器只发指令不碰磁盘、不管理内存缓存读内存还是读磁盘全部是 InnoDB 自己内部搞定的。再强调二者分工1. 服务层执行器做什么只干 3 件事经过优化器选定执行方案调用 InnoDB 提供的接口读一行、下一行拿到数据后做过滤、排序、聚合返回给客户端它完全不知道数据在内存还是磁盘不关心物理存储细节。2. 存储引擎InnoDB做什么真正管事的管理磁盘上的数据文件、索引文件管理内存缓冲池 Buffer Pool决定要不要缓存数据、淘汰旧数据处理事务、MVCC、行锁、redo/undo 日志响应执行器的「拿数据」请求自己从内存 / 磁盘取负责事务一致性ACIDAtomicity 原子性、Consistency 一致性、Isolation 隔离性、Durability 持久性和数据完整性外键、唯一约束等对于存储在磁盘文件的数据InnoDB 会分页存储使用缓存使用 B 树管理数据数据页通过 B 树索引组织主键索引叫聚簇索引二级索引叫非聚簇索引后面我们再复习为什么数据库不 ⌈按行连续存储⌋另外为什么 MySQL 敢说自己快因为它不是直接读写硬盘。InnoDB 内存中的Buffer Pool发挥了作用读数据先看内存里有没有有就直接给没有再从磁盘读并缓存。写数据先修改内存中的数据页标记为脏页同时记录redo log然后异步刷盘。另外InnoDB 所有数据、索引都按 16KB 一页组织管理InnoDB最小磁盘 IO 单元是页默认大小16KB。一条数据很小不会一条一次磁盘 IO而是一次性加载一整页到缓冲池。索引、行数据都是以页为单位组织存放在磁盘和内存中。总结执行器是发号施令的指挥官InnoDB 是亲自跑腿、管内存管磁盘的实干者。为什么是 InnoDB1.InnoDB 支持事务例如BEGIN; UPDATE account SET money money - 100 WHERE id 1; UPDATE account SET money money 100 WHERE id 2; COMMIT;所谓事务就是要么全成功要么全失败不会在转账的时候出现你的账户少了100元我的账户没有多100元的情况。InnoDB 通过 undo log redo log binlog 保证事务原子性和持久性。MyISAM 不支持。2.InnoDB 支持行锁例如用户A修改 id1并不会影响用户B修改 id2并发相对高但可能伴随竞态条件或死锁。而MyISAM是表锁一个人改数据整张表都可能被锁并发较差。注意InnoDB是支持行锁表锁MyISAM 只支持表锁。简单理解表锁和行锁假设同时有两个用户执行下面这条 SQLUPDATE account SET money money - 100 WHERE id 1;InnoDB如果两条 UPDATE 修改不同 id行行锁会让它们同时执行不会出现 money 减 200 的情况如果修改相同 id则第二条会等待第一条提交MyISAM整表锁所以第二条 UPDATE 必须等待第一条完成3.InnoDB 支持崩溃恢复即使突然断电数据也不容易丢因为有redo log 重做事务、undo log 回滚事务这个后续会在日志篇详细复习。4.InnoDB 使用聚簇索引暂时先记住数据和索引在一起InnoDB 主键索引存储数据页后续在索引篇详细复习优点主键查询非常快范围查询顺序存储有利于扫描缺点插入顺序不对可能造成页分裂写性能稍差二级索引查主键可能需要回表而 MyISAM 使用非聚簇索引索引指向地址5.InnoDB 支持外键MyISAM 不支持总结五个方面事务锁粒度外键可靠性索引结构自测Q1一条SQL怎么执行A1连接器用户认证、建立TCP连接 -查缓存MySQL 8.0 已删除缓存命中率太低 -解析器识别关键字、分析语法错误 -优化器类似编译器优化处理“选哪个索引”“多表查询哪个表先查”“JOIN顺序”之类的问题 -执行器调用存储引擎接口 -存储引擎对真实物理数据进行操作 -执行器组装结果集Q2什么是存储引擎A2MySQL相当于公司老板存储引擎相当于公司员工。老板有调用员工的资格可以类比为MySQL有调用存储引擎的接口。存储引擎管理磁盘/内存中的数据B树数据结构、执行来自执行器的CRUD请求、处理事务/锁/MVCC/redo log/undo log。总之存储引擎是真正干活的。Q3为什么是InnoDBA3原因一InnoDB支持事务MyISAM不支持。所谓事务就是指一段SQL语句要么都被执行、要么都不被执行。典型的应用场景是转账。如果没有事务可能会出现A的账户少了100元但他的转账对象B的账户并没有多出100元的情况。原因二InnoDB支持行锁而MyISAM使用表锁。对于InnoDBA能修改id1B也能同时修改id2并发能力强但也伴随竞态问题与死锁问题对于MyISAM一段时间内只能一个人修改因为整张表都被锁住了。原因三InnoDB支持崩溃恢复redo log 重做事务undo log 回滚事务。InnoDB 崩溃恢复主要依靠redo log 的 WAL 机制Write-Ahead Logging预写日志简称 WAL。断电重启后InnoDB 会检查 redo log将已提交但未写入磁盘的数据重放redo将未提交的事务通过 undo log 回滚保证数据一致性。原因四InnoDB支持聚簇索引暂时先记住相当于主键与数据存在一起实际上是主键索引存储数据页。原因五InnoDB 支持外键MyISAM 不支持。Q4当执行更新语句时InnoDB 为什么要先写日志而不是直接写磁盘A4如果直接写磁盘想象一个场景MySQL 的数据存在磁盘上的.ibd文件里。这个文件被分成很多个16KB 的数据页。当你执行UPDATE users SET name Tom WHERE id 100;时用户id100的数据可能在磁盘文件的第 500 个页面。紧接着你执行UPDATE orders SET status 1 WHERE order_id 999;。订单数据可能在磁盘文件的第 2000 个页面。如果你直接写磁盘磁头就要在磁盘上飞快地跳来跳去寻道寻找第 500 页写一点再寻找第 2000 页写一点。这种跳跃式的写入是缓慢的。如果是先写日志InnoDB 不会立刻去改磁盘里的那个 16KB 页面而是先把这次修改的操作记录在redo log重做日志里。redo log 是追加写的它就像一个无限延长的账本新的记录永远写在末尾。注意这和上文说的redo log 循环写 binlog 追加写不矛盾待会会说那么这究竟快在哪里呢主要有三个方面1.将同步变为异步在没有 redo log 之前为了保证数据不丢你必须在用户点击“提交”后原地等待磁头跳到第 500 页把数据写完才能返回“更新成功”。这个等待过程同步写是用户感知的卡顿。有了 redo log 之后写日志是顺序的极快。写完日志MySQL 就立刻给用户返回成功了。至于数据什么时候从内存刷到第 500 页那是后台线程的事异步写。用户不再需要为磁盘磁头的物理跳跃而等待。2.合并写入虽然磁头最终要去写第 500 页和第 2000 页但后台线程并不会来一个写一个而是采取攒一波再写的策略场景 A直接写你在 1 分钟内对第 500 页修改了 10 次。磁头就要跳过去 10 次写 10 次。场景 BWAL 模式这 10 次修改在内存里完成并顺序记入 redo log。后台线程发现第 500 页很“热”它会等这 10 次修改都完成后只跳过去一次把最终结果写入磁盘。这就把 10 次随机 IO 变成了一次随机 IO。3.磁盘调度优化IO 排序当后台线程决定要把内存中的脏页被修改过的页刷回磁盘时它手里可能积压了几百个不同位置的写入任务比如第 500 页、第 2000 页、第 800 页……。如果直接写磁头的轨迹可能是500 - 2000 - 800来回折返。 但在异步模式下MySQL 或操作系统可以对这些任务进行排序让磁头按顺序走500 - 800 - 2000。 这就像快递员送货如果来一单送一单他在城里乱跑如果把一天的单子拿在手里规划路线他只需要按顺序绕城一圈。Q5redo log 是循环写还是追加写A5补充说一下为什么上文说redo log是循环写这里又说redo log是追加写。这里需要澄清一个概念“追加写”是指一种 IO 行为模式而“循环写”是指一种空间管理策略。1.物理层面redo log 和 binlog 都是追加写无论是 redo log 还是 binlog在将数据写入磁盘那一刻磁头的动作都是**顺序追加redo Log 的追加虽然它在磁盘上表现为固定大小的一组文件比如ib_logfile0和ib_logfile1但它内部维护了一个write pos当前写入位置指针。每次写入都是从write pos开始向后顺序写入binlog 的追加它不断创建新文件binlog.000001, binlog.000002...也是一直向后追加2.空间管理策略的差异特性redo Log (重做日志)binlog (归档日志)空间策略循环写 (Circular)追加写 (Append)存储表现空间固定。写满了会回到开头覆盖旧记录。空间不固定。写满一个文件就开下一个。覆盖前提必须确保被覆盖的记录已经“落盘”刷到数据页。永不覆盖旧文件会一直保留直到被清理。功能定位崩溃恢复保证最近的数据不丢。数据归档、主从复制记录全量历史。总结binlog 是“不覆盖的追加”redo log 是“可覆盖的循环”。3.为什么 Redo Log 要设计成循环写这是为了性能与空间的平衡。redo Log 只是暂存它的任务是保证 Buffer Pool 里的脏页在还没来得及刷入.ibd文件时如果掉电了能救回来。一旦脏页被成功刷入了磁盘这段 redo log 就完成了使命变成了无用记录。避免无限膨胀数据库每天的修改量巨大如果 redo log 不循环覆盖它会迅速吞噬所有磁盘空间。Checkpoint 机制InnoDB 维护了一个checkpoint标志。write pos追着checkpoint跑。如果write pos快要追上checkpoint了表示空间满了MySQL 就会强制停下来把内存里的脏页刷一波到磁盘然后把checkpoint往前推腾出空间。Q6一条UPDATE语句和一条SELECT语句在执行流程上最主要的区别是什么涉及了哪些SELECT没有的组件A6UPDATE 在 InnoDB 层需要写redo log保证崩溃恢复在 Server 层写binlog用于主从复制和数据恢复并且为了保证两个日志一致需要两阶段提交。SELECT完全不涉及这些日志。具体流程1.执行器调用引擎获取数据2.引擎修改内存中的数据页写undo log用于回滚和MVCC3.写redo log状态为prepare4.执行器写binlog5.提交事务redo log 状态改为 commit两阶段提交完成Q7假设 MySQL 在写 binlog 的过程中突然宕机了此时 redo log 处于 prepare 状态binlog 还没写完。恢复后MySQL 会怎么处理这个事务是提交还是回滚为什么A7MySQL恢复后会把这个事务回滚。原因两阶段提交的恢复规则是如果 redo log 处于 prepare 状态就去检查对应的 binlog 是否完整。本题场景下binlog 还没写完就宕机所以 binlog 不完整。这时为了保证 redo log 和 binlog 数据一致就会回滚事务undo 掉 redo log 中 prepare 状态的数据。那如果回滚redo log 的数据不就丢失了吗对就是要让它丢失。因为 binlog 里没有这个操作如果提交了主从复制时从库会缺少这个操作导致主从数据不一致。一致性大于一切。Q8执行器在操作表之前还会做一次权限校验为什么连接器已经校验过了执行器还要再校验A8有些SQL在分析器阶段可能还不知道要操作哪些表比如存储过程中的动态SQL、某些触发器场景。分析器只做语法检查不检查表级权限。所以执行器在真正打开表执行之前必须再校验一次该用户有没有这个表的操作权限。执行器校验的其实是表级别的权限比如有没有SELECT、INSERT权限等。连接器只管能不能连进来。