MySQL核心知识点全景详解

MySQL 核心知识点全景详解（2026 最新版）

适用版本：本文以 MySQL 8.0+ 为主脉络，兼顾 5.7 重要差异。内容覆盖架构、存储引擎、索引、事务、MVCC、锁、日志、SQL 优化、主从复制、分库分表与数据类型选型，配合流程图与结构图帮助建立全局心智模型。

作者：一位想进大厂的树先生：Mr.Tree。

目录

一、MySQL 整体架构

1.1 逻辑分层架构

MySQL 是典型的 分层、可插拔存储引擎 架构，自上而下分为四层：

#mermaid-svg-Rp8uvtZTC3Iz7u1p{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .error-icon{fill:#552222;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .marker{fill:#333333;stroke:#333333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .marker.cross{stroke:#333333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-Rp8uvtZTC3Iz7u1p p{margin:0;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .cluster-label text{fill:#333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .cluster-label span{color:#333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .cluster-label span p{background-color:transparent;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .label text,#mermaid-svg-Rp8uvtZTC3Iz7u1p span{fill:#333;color:#333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .node rect,#mermaid-svg-Rp8uvtZTC3Iz7u1p .node circle,#mermaid-svg-Rp8uvtZTC3Iz7u1p .node ellipse,#mermaid-svg-Rp8uvtZTC3Iz7u1p .node polygon,#mermaid-svg-Rp8uvtZTC3Iz7u1p .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .rough-node .label text,#mermaid-svg-Rp8uvtZTC3Iz7u1p .node .label text,#mermaid-svg-Rp8uvtZTC3Iz7u1p .image-shape .label,#mermaid-svg-Rp8uvtZTC3Iz7u1p .icon-shape .label{text-anchor:middle;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .rough-node .label,#mermaid-svg-Rp8uvtZTC3Iz7u1p .node .label,#mermaid-svg-Rp8uvtZTC3Iz7u1p .image-shape .label,#mermaid-svg-Rp8uvtZTC3Iz7u1p .icon-shape .label{text-align:center;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .node.clickable{cursor:pointer;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .arrowheadPath{fill:#333333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-Rp8uvtZTC3Iz7u1p .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-Rp8uvtZTC3Iz7u1p .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-Rp8uvtZTC3Iz7u1p .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .cluster text{fill:#333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .cluster span{color:#333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-Rp8uvtZTC3Iz7u1p rect.text{fill:none;stroke-width:0;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .icon-shape,#mermaid-svg-Rp8uvtZTC3Iz7u1p .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .icon-shape p,#mermaid-svg-Rp8uvtZTC3Iz7u1p .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .icon-shape .label rect,#mermaid-svg-Rp8uvtZTC3Iz7u1p .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-Rp8uvtZTC3Iz7u1p .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-Rp8uvtZTC3Iz7u1p .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-Rp8uvtZTC3Iz7u1p :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

文件系统层 File System

存储引擎层 Storage Engine

服务层 Server Layer

连接层 Connection Layer

客户端 JDBC/ODBC/CLI

连接池 Connection Pool认证/鉴权/线程复用

SQL Interface 接口

Parser 解析器词法+语法+AST

Optimizer 优化器CBO 成本模型

Cache & Buffer8.0 已移除查询缓存

InnoDB

MyISAM

Memory

Archive

数据文件 .ibd

Redo Log

Undo Log

Binlog

设计要点与选型依据

• 连接层：负责 TCP 握手、身份认证（MySQL 8.0 默认 caching_sha2_password）、维护连接线程。连接数由 max_connections 控制，短连接风暴易打满连接池，建议用连接池（HikariCP/Druid）复用。
• 服务层：所有存储引擎共享。优化器基于 成本（Cost Based） 选择执行计划，而非规则。
• 存储引擎层：插件式设计，真正负责数据的存储与提取。MySQL 5.5 起默认引擎为 InnoDB。
• 优缺点对比：分层解耦带来极强扩展性（可自研引擎），但也导致服务层与引擎层语义重复（如权限、计数），跨引擎 join 有额外开销。

1.2 一条 SELECT 语句的完整执行流程

#mermaid-svg-kV5w45AvOAgCRhff{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-kV5w45AvOAgCRhff .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-kV5w45AvOAgCRhff .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-kV5w45AvOAgCRhff .error-icon{fill:#552222;}#mermaid-svg-kV5w45AvOAgCRhff .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-kV5w45AvOAgCRhff .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-kV5w45AvOAgCRhff .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-kV5w45AvOAgCRhff .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-kV5w45AvOAgCRhff .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-kV5w45AvOAgCRhff .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-kV5w45AvOAgCRhff .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-kV5w45AvOAgCRhff .marker{fill:#333333;stroke:#333333;}#mermaid-svg-kV5w45AvOAgCRhff .marker.cross{stroke:#333333;}#mermaid-svg-kV5w45AvOAgCRhff svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-kV5w45AvOAgCRhff p{margin:0;}#mermaid-svg-kV5w45AvOAgCRhff .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-kV5w45AvOAgCRhff .cluster-label text{fill:#333;}#mermaid-svg-kV5w45AvOAgCRhff .cluster-label span{color:#333;}#mermaid-svg-kV5w45AvOAgCRhff .cluster-label span p{background-color:transparent;}#mermaid-svg-kV5w45AvOAgCRhff .label text,#mermaid-svg-kV5w45AvOAgCRhff span{fill:#333;color:#333;}#mermaid-svg-kV5w45AvOAgCRhff .node rect,#mermaid-svg-kV5w45AvOAgCRhff .node circle,#mermaid-svg-kV5w45AvOAgCRhff .node ellipse,#mermaid-svg-kV5w45AvOAgCRhff .node polygon,#mermaid-svg-kV5w45AvOAgCRhff .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-kV5w45AvOAgCRhff .rough-node .label text,#mermaid-svg-kV5w45AvOAgCRhff .node .label text,#mermaid-svg-kV5w45AvOAgCRhff .image-shape .label,#mermaid-svg-kV5w45AvOAgCRhff .icon-shape .label{text-anchor:middle;}#mermaid-svg-kV5w45AvOAgCRhff .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-kV5w45AvOAgCRhff .rough-node .label,#mermaid-svg-kV5w45AvOAgCRhff .node .label,#mermaid-svg-kV5w45AvOAgCRhff .image-shape .label,#mermaid-svg-kV5w45AvOAgCRhff .icon-shape .label{text-align:center;}#mermaid-svg-kV5w45AvOAgCRhff .node.clickable{cursor:pointer;}#mermaid-svg-kV5w45AvOAgCRhff .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-kV5w45AvOAgCRhff .arrowheadPath{fill:#333333;}#mermaid-svg-kV5w45AvOAgCRhff .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-kV5w45AvOAgCRhff .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-kV5w45AvOAgCRhff .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-kV5w45AvOAgCRhff .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-kV5w45AvOAgCRhff .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-kV5w45AvOAgCRhff .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-kV5w45AvOAgCRhff .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-kV5w45AvOAgCRhff .cluster text{fill:#333;}#mermaid-svg-kV5w45AvOAgCRhff .cluster span{color:#333;}#mermaid-svg-kV5w45AvOAgCRhff div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-kV5w45AvOAgCRhff .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-kV5w45AvOAgCRhff rect.text{fill:none;stroke-width:0;}#mermaid-svg-kV5w45AvOAgCRhff .icon-shape,#mermaid-svg-kV5w45AvOAgCRhff .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-kV5w45AvOAgCRhff .icon-shape p,#mermaid-svg-kV5w45AvOAgCRhff .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-kV5w45AvOAgCRhff .icon-shape .label rect,#mermaid-svg-kV5w45AvOAgCRhff .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-kV5w45AvOAgCRhff .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-kV5w45AvOAgCRhff .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-kV5w45AvOAgCRhff :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

客户端发送 SQL

连接器: 鉴权/取权限

查询缓存8.0 已移除

解析器: 词法语法校验

预处理器: 表/列是否存在

优化器: 生成执行计划索引选择/连接顺序

执行器: 调用引擎 API

InnoDB 缓冲区/B+树检索

返回结果集

关键结论：查询缓存在 8.0 被移除，因为只要表有更新，该表所有缓存即失效，命中率极低且维护成本高。

二、存储引擎（InnoDB 深度解析）

2.1 InnoDB 与 MyISAM 对比

选型结论：除极特殊的只读、极少写、无事务需求的报表场景，一律使用 InnoDB。MyISAM 的表级锁在高并发写入下是致命瓶颈。

2.2 InnoDB 内存与磁盘结构

#mermaid-svg-zRNk5sqvHlpuZ7Lo{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .error-icon{fill:#552222;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .marker{fill:#333333;stroke:#333333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .marker.cross{stroke:#333333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-zRNk5sqvHlpuZ7Lo p{margin:0;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .cluster-label text{fill:#333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .cluster-label span{color:#333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .cluster-label span p{background-color:transparent;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .label text,#mermaid-svg-zRNk5sqvHlpuZ7Lo span{fill:#333;color:#333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .node rect,#mermaid-svg-zRNk5sqvHlpuZ7Lo .node circle,#mermaid-svg-zRNk5sqvHlpuZ7Lo .node ellipse,#mermaid-svg-zRNk5sqvHlpuZ7Lo .node polygon,#mermaid-svg-zRNk5sqvHlpuZ7Lo .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .rough-node .label text,#mermaid-svg-zRNk5sqvHlpuZ7Lo .node .label text,#mermaid-svg-zRNk5sqvHlpuZ7Lo .image-shape .label,#mermaid-svg-zRNk5sqvHlpuZ7Lo .icon-shape .label{text-anchor:middle;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .rough-node .label,#mermaid-svg-zRNk5sqvHlpuZ7Lo .node .label,#mermaid-svg-zRNk5sqvHlpuZ7Lo .image-shape .label,#mermaid-svg-zRNk5sqvHlpuZ7Lo .icon-shape .label{text-align:center;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .node.clickable{cursor:pointer;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .arrowheadPath{fill:#333333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-zRNk5sqvHlpuZ7Lo .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-zRNk5sqvHlpuZ7Lo .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-zRNk5sqvHlpuZ7Lo .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .cluster text{fill:#333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .cluster span{color:#333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-zRNk5sqvHlpuZ7Lo rect.text{fill:none;stroke-width:0;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .icon-shape,#mermaid-svg-zRNk5sqvHlpuZ7Lo .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .icon-shape p,#mermaid-svg-zRNk5sqvHlpuZ7Lo .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .icon-shape .label rect,#mermaid-svg-zRNk5sqvHlpuZ7Lo .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-zRNk5sqvHlpuZ7Lo .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-zRNk5sqvHlpuZ7Lo .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-zRNk5sqvHlpuZ7Lo :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

磁盘结构

内存结构

脏页刷盘

merge

顺序写

包含

Buffer Pool数据页/索引页缓存

Change Buffer非唯一二级索引写缓冲

Log BufferRedo Log 缓冲

Adaptive Hash Index自适应哈希索引

.ibd 表空间文件段/区/页

Redo Logib_logfile0/1

Undo Tablespace回滚/版本链

• Buffer Pool：InnoDB 性能核心，默认占物理内存 60%~80%。以 页（16KB） 为单位缓存，采用 LRU 变体（含 old 区） 防止全表扫描污染热点数据。
• Change Buffer：对 非唯一 二级索引的写操作，若数据页不在 Buffer Pool，先缓存在 Change Buffer，待后续读入时 merge，减少随机 IO。唯一索引因需立即校验唯一性，无法使用该机制。
• Redo Log：循环写、顺序写，保证已提交事务的持久性（crash-safe）。

三、索引原理与实战

3.1 为什么是 B+ 树？

索引的本质是 用空间换时间，让查找从全表扫描的 O(N) 降到 O(log N)。主流候选结构对比：

B+ 树核心优势：

#mermaid-svg-iicNYLmWkoP2F068{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-iicNYLmWkoP2F068 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-iicNYLmWkoP2F068 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-iicNYLmWkoP2F068 .error-icon{fill:#552222;}#mermaid-svg-iicNYLmWkoP2F068 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-iicNYLmWkoP2F068 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-iicNYLmWkoP2F068 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-iicNYLmWkoP2F068 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-iicNYLmWkoP2F068 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-iicNYLmWkoP2F068 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-iicNYLmWkoP2F068 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-iicNYLmWkoP2F068 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-iicNYLmWkoP2F068 .marker.cross{stroke:#333333;}#mermaid-svg-iicNYLmWkoP2F068 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-iicNYLmWkoP2F068 p{margin:0;}#mermaid-svg-iicNYLmWkoP2F068 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-iicNYLmWkoP2F068 .cluster-label text{fill:#333;}#mermaid-svg-iicNYLmWkoP2F068 .cluster-label span{color:#333;}#mermaid-svg-iicNYLmWkoP2F068 .cluster-label span p{background-color:transparent;}#mermaid-svg-iicNYLmWkoP2F068 .label text,#mermaid-svg-iicNYLmWkoP2F068 span{fill:#333;color:#333;}#mermaid-svg-iicNYLmWkoP2F068 .node rect,#mermaid-svg-iicNYLmWkoP2F068 .node circle,#mermaid-svg-iicNYLmWkoP2F068 .node ellipse,#mermaid-svg-iicNYLmWkoP2F068 .node polygon,#mermaid-svg-iicNYLmWkoP2F068 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-iicNYLmWkoP2F068 .rough-node .label text,#mermaid-svg-iicNYLmWkoP2F068 .node .label text,#mermaid-svg-iicNYLmWkoP2F068 .image-shape .label,#mermaid-svg-iicNYLmWkoP2F068 .icon-shape .label{text-anchor:middle;}#mermaid-svg-iicNYLmWkoP2F068 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-iicNYLmWkoP2F068 .rough-node .label,#mermaid-svg-iicNYLmWkoP2F068 .node .label,#mermaid-svg-iicNYLmWkoP2F068 .image-shape .label,#mermaid-svg-iicNYLmWkoP2F068 .icon-shape .label{text-align:center;}#mermaid-svg-iicNYLmWkoP2F068 .node.clickable{cursor:pointer;}#mermaid-svg-iicNYLmWkoP2F068 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-iicNYLmWkoP2F068 .arrowheadPath{fill:#333333;}#mermaid-svg-iicNYLmWkoP2F068 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-iicNYLmWkoP2F068 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-iicNYLmWkoP2F068 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-iicNYLmWkoP2F068 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-iicNYLmWkoP2F068 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-iicNYLmWkoP2F068 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-iicNYLmWkoP2F068 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-iicNYLmWkoP2F068 .cluster text{fill:#333;}#mermaid-svg-iicNYLmWkoP2F068 .cluster span{color:#333;}#mermaid-svg-iicNYLmWkoP2F068 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-iicNYLmWkoP2F068 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-iicNYLmWkoP2F068 rect.text{fill:none;stroke-width:0;}#mermaid-svg-iicNYLmWkoP2F068 .icon-shape,#mermaid-svg-iicNYLmWkoP2F068 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-iicNYLmWkoP2F068 .icon-shape p,#mermaid-svg-iicNYLmWkoP2F068 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-iicNYLmWkoP2F068 .icon-shape .label rect,#mermaid-svg-iicNYLmWkoP2F068 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-iicNYLmWkoP2F068 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-iicNYLmWkoP2F068 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-iicNYLmWkoP2F068 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

叶子节点（存数据，双向链表串联）

中间节点（非叶子，只存键）

根节点（非叶子，只存键）

10

30

50

4 7

12 18 25

33 41

55 60 70

1 4 7

12 18 25

33 41

55 60 70

3.2 聚簇索引 vs 二级索引（回表）

• 聚簇索引（Clustered Index）：InnoDB 用 主键 构建，叶子节点存 整行数据。一张表只有一个。若未显式定义主键，引擎会选唯一非空索引；若都没有，隐式生成 6 字节 row_id。
• 二级索引（Secondary Index）：叶子节点存 索引列值 + 主键值。通过二级索引查非索引列时，需 回表（用主键再查聚簇索引）。

#mermaid-svg-ACKLiAbPii6dNeQH{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-ACKLiAbPii6dNeQH .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-ACKLiAbPii6dNeQH .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-ACKLiAbPii6dNeQH .error-icon{fill:#552222;}#mermaid-svg-ACKLiAbPii6dNeQH .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-ACKLiAbPii6dNeQH .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-ACKLiAbPii6dNeQH .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-ACKLiAbPii6dNeQH .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-ACKLiAbPii6dNeQH .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-ACKLiAbPii6dNeQH .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-ACKLiAbPii6dNeQH .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-ACKLiAbPii6dNeQH .marker{fill:#333333;stroke:#333333;}#mermaid-svg-ACKLiAbPii6dNeQH .marker.cross{stroke:#333333;}#mermaid-svg-ACKLiAbPii6dNeQH svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-ACKLiAbPii6dNeQH p{margin:0;}#mermaid-svg-ACKLiAbPii6dNeQH .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-ACKLiAbPii6dNeQH .cluster-label text{fill:#333;}#mermaid-svg-ACKLiAbPii6dNeQH .cluster-label span{color:#333;}#mermaid-svg-ACKLiAbPii6dNeQH .cluster-label span p{background-color:transparent;}#mermaid-svg-ACKLiAbPii6dNeQH .label text,#mermaid-svg-ACKLiAbPii6dNeQH span{fill:#333;color:#333;}#mermaid-svg-ACKLiAbPii6dNeQH .node rect,#mermaid-svg-ACKLiAbPii6dNeQH .node circle,#mermaid-svg-ACKLiAbPii6dNeQH .node ellipse,#mermaid-svg-ACKLiAbPii6dNeQH .node polygon,#mermaid-svg-ACKLiAbPii6dNeQH .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-ACKLiAbPii6dNeQH .rough-node .label text,#mermaid-svg-ACKLiAbPii6dNeQH .node .label text,#mermaid-svg-ACKLiAbPii6dNeQH .image-shape .label,#mermaid-svg-ACKLiAbPii6dNeQH .icon-shape .label{text-anchor:middle;}#mermaid-svg-ACKLiAbPii6dNeQH .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-ACKLiAbPii6dNeQH .rough-node .label,#mermaid-svg-ACKLiAbPii6dNeQH .node .label,#mermaid-svg-ACKLiAbPii6dNeQH .image-shape .label,#mermaid-svg-ACKLiAbPii6dNeQH .icon-shape .label{text-align:center;}#mermaid-svg-ACKLiAbPii6dNeQH .node.clickable{cursor:pointer;}#mermaid-svg-ACKLiAbPii6dNeQH .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-ACKLiAbPii6dNeQH .arrowheadPath{fill:#333333;}#mermaid-svg-ACKLiAbPii6dNeQH .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-ACKLiAbPii6dNeQH .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-ACKLiAbPii6dNeQH .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-ACKLiAbPii6dNeQH .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-ACKLiAbPii6dNeQH .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-ACKLiAbPii6dNeQH .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-ACKLiAbPii6dNeQH .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-ACKLiAbPii6dNeQH .cluster text{fill:#333;}#mermaid-svg-ACKLiAbPii6dNeQH .cluster span{color:#333;}#mermaid-svg-ACKLiAbPii6dNeQH div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-ACKLiAbPii6dNeQH .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-ACKLiAbPii6dNeQH rect.text{fill:none;stroke-width:0;}#mermaid-svg-ACKLiAbPii6dNeQH .icon-shape,#mermaid-svg-ACKLiAbPii6dNeQH .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-ACKLiAbPii6dNeQH .icon-shape p,#mermaid-svg-ACKLiAbPii6dNeQH .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-ACKLiAbPii6dNeQH .icon-shape .label rect,#mermaid-svg-ACKLiAbPii6dNeQH .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-ACKLiAbPii6dNeQH .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-ACKLiAbPii6dNeQH .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-ACKLiAbPii6dNeQH :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

聚簇索引树 id

二级索引树 name

回表

回表

叶子: name=张三 → id=5

叶子: name=李四 → id=8

叶子: id=5 → 整行（张三,20,北京）

叶子: id=8 → 整行（李四,25,上海）

优化提示：SELECT * 几乎必然触发回表。只查所需列，争取 覆盖索引 才能避免回表。

3.3 覆盖索引（Covering Index）

当查询的 所有列 都包含在某个二级索引中，引擎直接从索引取数，无需回表。这是高性能查询的关键手段。

— 联合索引 (name, age)
— ✅ 覆盖索引：只需 name、age，索引已包含
SELECT name, age FROM user WHERE name = '张三';

— ❌ 需回表：address 不在索引中
SELECT name, age, address FROM user WHERE name = '张三';

3.4 联合索引与最左前缀原则

联合索引 (a, b, c) 在 B+ 树中按 a → b → c 依次排序。最左前缀原则指：查询必须从索引的 最左列开始且连续，才能有效利用索引。

3.5 索引失效的典型场景

3.6 索引设计原则

• 高选择性列优先建索引（区分度 = 去重值/总数，越接近 1 越好）。
• 优先覆盖索引，减少回表。
• 控制单表索引数量（建议 ≤ 5），避免写入时维护索引的额外开销。
• 长字符串用 前缀索引 INDEX(name(20))，权衡区分度与空间。
• 利用 索引下推（ICP，Index Condition Pushdown，5.6+）：在存储引擎层提前过滤，减少回表行数。

四、事务与 ACID

4.1 事务四大特性

4.2 隔离级别与并发问题

*MySQL 在 可重复读（RR） 下，通过 MVCC + Next-Key Lock（临键锁） 基本解决了幻读，这也是 MySQL 默认用 RR 而非 RC 的原因。

#mermaid-svg-FW0CnTGW6yywZfcl{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-FW0CnTGW6yywZfcl .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-FW0CnTGW6yywZfcl .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-FW0CnTGW6yywZfcl .error-icon{fill:#552222;}#mermaid-svg-FW0CnTGW6yywZfcl .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-FW0CnTGW6yywZfcl .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-FW0CnTGW6yywZfcl .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-FW0CnTGW6yywZfcl .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-FW0CnTGW6yywZfcl .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-FW0CnTGW6yywZfcl .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-FW0CnTGW6yywZfcl .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-FW0CnTGW6yywZfcl .marker{fill:#333333;stroke:#333333;}#mermaid-svg-FW0CnTGW6yywZfcl .marker.cross{stroke:#333333;}#mermaid-svg-FW0CnTGW6yywZfcl svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-FW0CnTGW6yywZfcl p{margin:0;}#mermaid-svg-FW0CnTGW6yywZfcl .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-FW0CnTGW6yywZfcl .cluster-label text{fill:#333;}#mermaid-svg-FW0CnTGW6yywZfcl .cluster-label span{color:#333;}#mermaid-svg-FW0CnTGW6yywZfcl .cluster-label span p{background-color:transparent;}#mermaid-svg-FW0CnTGW6yywZfcl .label text,#mermaid-svg-FW0CnTGW6yywZfcl span{fill:#333;color:#333;}#mermaid-svg-FW0CnTGW6yywZfcl .node rect,#mermaid-svg-FW0CnTGW6yywZfcl .node circle,#mermaid-svg-FW0CnTGW6yywZfcl .node ellipse,#mermaid-svg-FW0CnTGW6yywZfcl .node polygon,#mermaid-svg-FW0CnTGW6yywZfcl .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-FW0CnTGW6yywZfcl .rough-node .label text,#mermaid-svg-FW0CnTGW6yywZfcl .node .label text,#mermaid-svg-FW0CnTGW6yywZfcl .image-shape .label,#mermaid-svg-FW0CnTGW6yywZfcl .icon-shape .label{text-anchor:middle;}#mermaid-svg-FW0CnTGW6yywZfcl .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-FW0CnTGW6yywZfcl .rough-node .label,#mermaid-svg-FW0CnTGW6yywZfcl .node .label,#mermaid-svg-FW0CnTGW6yywZfcl .image-shape .label,#mermaid-svg-FW0CnTGW6yywZfcl .icon-shape .label{text-align:center;}#mermaid-svg-FW0CnTGW6yywZfcl .node.clickable{cursor:pointer;}#mermaid-svg-FW0CnTGW6yywZfcl .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-FW0CnTGW6yywZfcl .arrowheadPath{fill:#333333;}#mermaid-svg-FW0CnTGW6yywZfcl .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-FW0CnTGW6yywZfcl .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-FW0CnTGW6yywZfcl .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-FW0CnTGW6yywZfcl .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-FW0CnTGW6yywZfcl .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-FW0CnTGW6yywZfcl .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-FW0CnTGW6yywZfcl .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-FW0CnTGW6yywZfcl .cluster text{fill:#333;}#mermaid-svg-FW0CnTGW6yywZfcl .cluster span{color:#333;}#mermaid-svg-FW0CnTGW6yywZfcl div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-FW0CnTGW6yywZfcl .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-FW0CnTGW6yywZfcl rect.text{fill:none;stroke-width:0;}#mermaid-svg-FW0CnTGW6yywZfcl .icon-shape,#mermaid-svg-FW0CnTGW6yywZfcl .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-FW0CnTGW6yywZfcl .icon-shape p,#mermaid-svg-FW0CnTGW6yywZfcl .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-FW0CnTGW6yywZfcl .icon-shape .label rect,#mermaid-svg-FW0CnTGW6yywZfcl .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-FW0CnTGW6yywZfcl .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-FW0CnTGW6yywZfcl .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-FW0CnTGW6yywZfcl :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

并发事务

脏读: 读到未提交数据

不可重复读: 同查询两次结果不同（针对UPDATE/DELETE）

幻读: 同查询两次行数不同（针对INSERT）

读已提交 解决脏读

可重复读 解决不可重复读

串行化 解决幻读

五、MVCC 多版本并发控制

MVCC（Multi-Version Concurrency Control）让 读不加锁、读写不阻塞，是 InnoDB 高并发的基石。

5.1 三大核心组件

5.2 版本链可见性判断流程

#mermaid-svg-ef3G9YILiYpVrCZ7{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-ef3G9YILiYpVrCZ7 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-ef3G9YILiYpVrCZ7 .error-icon{fill:#552222;}#mermaid-svg-ef3G9YILiYpVrCZ7 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-ef3G9YILiYpVrCZ7 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-ef3G9YILiYpVrCZ7 .marker.cross{stroke:#333333;}#mermaid-svg-ef3G9YILiYpVrCZ7 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-ef3G9YILiYpVrCZ7 p{margin:0;}#mermaid-svg-ef3G9YILiYpVrCZ7 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-ef3G9YILiYpVrCZ7 .cluster-label text{fill:#333;}#mermaid-svg-ef3G9YILiYpVrCZ7 .cluster-label span{color:#333;}#mermaid-svg-ef3G9YILiYpVrCZ7 .cluster-label span p{background-color:transparent;}#mermaid-svg-ef3G9YILiYpVrCZ7 .label text,#mermaid-svg-ef3G9YILiYpVrCZ7 span{fill:#333;color:#333;}#mermaid-svg-ef3G9YILiYpVrCZ7 .node rect,#mermaid-svg-ef3G9YILiYpVrCZ7 .node circle,#mermaid-svg-ef3G9YILiYpVrCZ7 .node ellipse,#mermaid-svg-ef3G9YILiYpVrCZ7 .node polygon,#mermaid-svg-ef3G9YILiYpVrCZ7 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-ef3G9YILiYpVrCZ7 .rough-node .label text,#mermaid-svg-ef3G9YILiYpVrCZ7 .node .label text,#mermaid-svg-ef3G9YILiYpVrCZ7 .image-shape .label,#mermaid-svg-ef3G9YILiYpVrCZ7 .icon-shape .label{text-anchor:middle;}#mermaid-svg-ef3G9YILiYpVrCZ7 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-ef3G9YILiYpVrCZ7 .rough-node .label,#mermaid-svg-ef3G9YILiYpVrCZ7 .node .label,#mermaid-svg-ef3G9YILiYpVrCZ7 .image-shape .label,#mermaid-svg-ef3G9YILiYpVrCZ7 .icon-shape .label{text-align:center;}#mermaid-svg-ef3G9YILiYpVrCZ7 .node.clickable{cursor:pointer;}#mermaid-svg-ef3G9YILiYpVrCZ7 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-ef3G9YILiYpVrCZ7 .arrowheadPath{fill:#333333;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-ef3G9YILiYpVrCZ7 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-ef3G9YILiYpVrCZ7 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-ef3G9YILiYpVrCZ7 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-ef3G9YILiYpVrCZ7 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-ef3G9YILiYpVrCZ7 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-ef3G9YILiYpVrCZ7 .cluster text{fill:#333;}#mermaid-svg-ef3G9YILiYpVrCZ7 .cluster span{color:#333;}#mermaid-svg-ef3G9YILiYpVrCZ7 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-ef3G9YILiYpVrCZ7 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-ef3G9YILiYpVrCZ7 rect.text{fill:none;stroke-width:0;}#mermaid-svg-ef3G9YILiYpVrCZ7 .icon-shape,#mermaid-svg-ef3G9YILiYpVrCZ7 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-ef3G9YILiYpVrCZ7 .icon-shape p,#mermaid-svg-ef3G9YILiYpVrCZ7 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-ef3G9YILiYpVrCZ7 .icon-shape .label rect,#mermaid-svg-ef3G9YILiYpVrCZ7 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-ef3G9YILiYpVrCZ7 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-ef3G9YILiYpVrCZ7 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-ef3G9YILiYpVrCZ7 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

是 自己改的

否

是 已提交

否

不在 已提交

在 未提交

开始查询 取一行数据

DB_TRX_ID == 当前事务ID?

可见

DB_TRX_ID 小于活跃最小事务ID?

DB_TRX_ID 在活跃列表?

沿 DB_ROLL_PTR 找上一版本

• RC：每次 SELECT 都生成新 ReadView → 能读到别的事务已提交的最新值（不可重复读）。
• RR：事务内 第一次 SELECT 生成 ReadView，后续复用 → 整事务看到一致快照（可重复读）。

六、锁机制

6.1 锁分类总览

#mermaid-svg-gd5AymEUmlE7PzEx{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-gd5AymEUmlE7PzEx .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-gd5AymEUmlE7PzEx .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-gd5AymEUmlE7PzEx .error-icon{fill:#552222;}#mermaid-svg-gd5AymEUmlE7PzEx .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-gd5AymEUmlE7PzEx .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-gd5AymEUmlE7PzEx .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-gd5AymEUmlE7PzEx .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-gd5AymEUmlE7PzEx .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-gd5AymEUmlE7PzEx .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-gd5AymEUmlE7PzEx .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-gd5AymEUmlE7PzEx .marker{fill:#333333;stroke:#333333;}#mermaid-svg-gd5AymEUmlE7PzEx .marker.cross{stroke:#333333;}#mermaid-svg-gd5AymEUmlE7PzEx svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-gd5AymEUmlE7PzEx p{margin:0;}#mermaid-svg-gd5AymEUmlE7PzEx .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-gd5AymEUmlE7PzEx .cluster-label text{fill:#333;}#mermaid-svg-gd5AymEUmlE7PzEx .cluster-label span{color:#333;}#mermaid-svg-gd5AymEUmlE7PzEx .cluster-label span p{background-color:transparent;}#mermaid-svg-gd5AymEUmlE7PzEx .label text,#mermaid-svg-gd5AymEUmlE7PzEx span{fill:#333;color:#333;}#mermaid-svg-gd5AymEUmlE7PzEx .node rect,#mermaid-svg-gd5AymEUmlE7PzEx .node circle,#mermaid-svg-gd5AymEUmlE7PzEx .node ellipse,#mermaid-svg-gd5AymEUmlE7PzEx .node polygon,#mermaid-svg-gd5AymEUmlE7PzEx .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-gd5AymEUmlE7PzEx .rough-node .label text,#mermaid-svg-gd5AymEUmlE7PzEx .node .label text,#mermaid-svg-gd5AymEUmlE7PzEx .image-shape .label,#mermaid-svg-gd5AymEUmlE7PzEx .icon-shape .label{text-anchor:middle;}#mermaid-svg-gd5AymEUmlE7PzEx .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-gd5AymEUmlE7PzEx .rough-node .label,#mermaid-svg-gd5AymEUmlE7PzEx .node .label,#mermaid-svg-gd5AymEUmlE7PzEx .image-shape .label,#mermaid-svg-gd5AymEUmlE7PzEx .icon-shape .label{text-align:center;}#mermaid-svg-gd5AymEUmlE7PzEx .node.clickable{cursor:pointer;}#mermaid-svg-gd5AymEUmlE7PzEx .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-gd5AymEUmlE7PzEx .arrowheadPath{fill:#333333;}#mermaid-svg-gd5AymEUmlE7PzEx .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-gd5AymEUmlE7PzEx .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-gd5AymEUmlE7PzEx .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-gd5AymEUmlE7PzEx .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-gd5AymEUmlE7PzEx .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-gd5AymEUmlE7PzEx .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-gd5AymEUmlE7PzEx .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-gd5AymEUmlE7PzEx .cluster text{fill:#333;}#mermaid-svg-gd5AymEUmlE7PzEx .cluster span{color:#333;}#mermaid-svg-gd5AymEUmlE7PzEx div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-gd5AymEUmlE7PzEx .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-gd5AymEUmlE7PzEx rect.text{fill:none;stroke-width:0;}#mermaid-svg-gd5AymEUmlE7PzEx .icon-shape,#mermaid-svg-gd5AymEUmlE7PzEx .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-gd5AymEUmlE7PzEx .icon-shape p,#mermaid-svg-gd5AymEUmlE7PzEx .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-gd5AymEUmlE7PzEx .icon-shape .label rect,#mermaid-svg-gd5AymEUmlE7PzEx .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-gd5AymEUmlE7PzEx .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-gd5AymEUmlE7PzEx .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-gd5AymEUmlE7PzEx :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

行锁类型

按粒度

锁模式

共享锁 S Lock

排他锁 X Lock

意向共享 IS

意向排他 IX

表级锁MyISAM/意向锁

行级锁InnoDB 默认

Record Lock 记录锁

Gap Lock 间隙锁

Next-Key Lock 临键锁记录锁+间隙锁

6.2 锁兼容矩阵

• 意向锁（IS/IX） 是表级"声明"，用于快速判断能否加表锁，避免逐行扫描。
• Next-Key Lock 是 InnoDB RR 级别默认行锁算法，锁定记录本身 + 其前间隙，从而 防止幻读。

6.3 死锁与规避

死锁产生条件：互斥、持有并等待、不可剥夺、循环等待。

#mermaid-svg-XXq1Q8Gfcec9fhzT{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-XXq1Q8Gfcec9fhzT .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-XXq1Q8Gfcec9fhzT .error-icon{fill:#552222;}#mermaid-svg-XXq1Q8Gfcec9fhzT .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-XXq1Q8Gfcec9fhzT .marker{fill:#333333;stroke:#333333;}#mermaid-svg-XXq1Q8Gfcec9fhzT .marker.cross{stroke:#333333;}#mermaid-svg-XXq1Q8Gfcec9fhzT svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-XXq1Q8Gfcec9fhzT p{margin:0;}#mermaid-svg-XXq1Q8Gfcec9fhzT .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-XXq1Q8Gfcec9fhzT .cluster-label text{fill:#333;}#mermaid-svg-XXq1Q8Gfcec9fhzT .cluster-label span{color:#333;}#mermaid-svg-XXq1Q8Gfcec9fhzT .cluster-label span p{background-color:transparent;}#mermaid-svg-XXq1Q8Gfcec9fhzT .label text,#mermaid-svg-XXq1Q8Gfcec9fhzT span{fill:#333;color:#333;}#mermaid-svg-XXq1Q8Gfcec9fhzT .node rect,#mermaid-svg-XXq1Q8Gfcec9fhzT .node circle,#mermaid-svg-XXq1Q8Gfcec9fhzT .node ellipse,#mermaid-svg-XXq1Q8Gfcec9fhzT .node polygon,#mermaid-svg-XXq1Q8Gfcec9fhzT .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-XXq1Q8Gfcec9fhzT .rough-node .label text,#mermaid-svg-XXq1Q8Gfcec9fhzT .node .label text,#mermaid-svg-XXq1Q8Gfcec9fhzT .image-shape .label,#mermaid-svg-XXq1Q8Gfcec9fhzT .icon-shape .label{text-anchor:middle;}#mermaid-svg-XXq1Q8Gfcec9fhzT .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-XXq1Q8Gfcec9fhzT .rough-node .label,#mermaid-svg-XXq1Q8Gfcec9fhzT .node .label,#mermaid-svg-XXq1Q8Gfcec9fhzT .image-shape .label,#mermaid-svg-XXq1Q8Gfcec9fhzT .icon-shape .label{text-align:center;}#mermaid-svg-XXq1Q8Gfcec9fhzT .node.clickable{cursor:pointer;}#mermaid-svg-XXq1Q8Gfcec9fhzT .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-XXq1Q8Gfcec9fhzT .arrowheadPath{fill:#333333;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-XXq1Q8Gfcec9fhzT .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-XXq1Q8Gfcec9fhzT .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-XXq1Q8Gfcec9fhzT .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-XXq1Q8Gfcec9fhzT .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-XXq1Q8Gfcec9fhzT .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-XXq1Q8Gfcec9fhzT .cluster text{fill:#333;}#mermaid-svg-XXq1Q8Gfcec9fhzT .cluster span{color:#333;}#mermaid-svg-XXq1Q8Gfcec9fhzT div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-XXq1Q8Gfcec9fhzT .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-XXq1Q8Gfcec9fhzT rect.text{fill:none;stroke-width:0;}#mermaid-svg-XXq1Q8Gfcec9fhzT .icon-shape,#mermaid-svg-XXq1Q8Gfcec9fhzT .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-XXq1Q8Gfcec9fhzT .icon-shape p,#mermaid-svg-XXq1Q8Gfcec9fhzT .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-XXq1Q8Gfcec9fhzT .icon-shape .label rect,#mermaid-svg-XXq1Q8Gfcec9fhzT .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-XXq1Q8Gfcec9fhzT .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-XXq1Q8Gfcec9fhzT .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-XXq1Q8Gfcec9fhzT :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

持有 id=1 锁

持有 id=2 锁

循环等待

循环等待

事务A

等待 id=2

事务B

等待 id=1

规避方案：

• 约定 统一的加锁顺序（如永远先锁 id 小的记录）。
• 控制事务粒度，缩短持锁时间。
• 降低隔离级别到 RC（RC 下不加 Gap Lock，死锁概率大降，但需业务容忍不可重复读）。
• 设置 innodb_lock_wait_timeout，超时自动回滚。
• 死锁检测 innodb_deadlock_detect=ON（默认开），检测到后回滚代价小的事务。

七、日志系统

InnoDB 有三大日志，职责严格分离：

7.1 WAL 与两阶段提交

为保证 Redo Log 与 Binlog 逻辑一致，MySQL 采用 两阶段提交（2PC）：

Server层Binlog

InnoDB

执行器

Server层Binlog

InnoDB

执行器

#mermaid-svg-nt0JTM4Bv7pgtfIN{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-nt0JTM4Bv7pgtfIN .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-nt0JTM4Bv7pgtfIN .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-nt0JTM4Bv7pgtfIN .error-icon{fill:#552222;}#mermaid-svg-nt0JTM4Bv7pgtfIN .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-nt0JTM4Bv7pgtfIN .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-nt0JTM4Bv7pgtfIN .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-nt0JTM4Bv7pgtfIN .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-nt0JTM4Bv7pgtfIN .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-nt0JTM4Bv7pgtfIN .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-nt0JTM4Bv7pgtfIN .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-nt0JTM4Bv7pgtfIN .marker{fill:#333333;stroke:#333333;}#mermaid-svg-nt0JTM4Bv7pgtfIN .marker.cross{stroke:#333333;}#mermaid-svg-nt0JTM4Bv7pgtfIN svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-nt0JTM4Bv7pgtfIN p{margin:0;}#mermaid-svg-nt0JTM4Bv7pgtfIN .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-nt0JTM4Bv7pgtfIN text.actor>tspan{fill:black;stroke:none;}#mermaid-svg-nt0JTM4Bv7pgtfIN .actor-line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-nt0JTM4Bv7pgtfIN .innerArc{stroke-width:1.5;stroke-dasharray:none;}#mermaid-svg-nt0JTM4Bv7pgtfIN .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-nt0JTM4Bv7pgtfIN .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-nt0JTM4Bv7pgtfIN #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-nt0JTM4Bv7pgtfIN .sequenceNumber{fill:white;}#mermaid-svg-nt0JTM4Bv7pgtfIN #sequencenumber{fill:#333;}#mermaid-svg-nt0JTM4Bv7pgtfIN #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-nt0JTM4Bv7pgtfIN .messageText{fill:#333;stroke:none;}#mermaid-svg-nt0JTM4Bv7pgtfIN .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-nt0JTM4Bv7pgtfIN .labelText,#mermaid-svg-nt0JTM4Bv7pgtfIN .labelText>tspan{fill:black;stroke:none;}#mermaid-svg-nt0JTM4Bv7pgtfIN .loopText,#mermaid-svg-nt0JTM4Bv7pgtfIN .loopText>tspan{fill:black;stroke:none;}#mermaid-svg-nt0JTM4Bv7pgtfIN .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-nt0JTM4Bv7pgtfIN .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-nt0JTM4Bv7pgtfIN .noteText,#mermaid-svg-nt0JTM4Bv7pgtfIN .noteText>tspan{fill:black;stroke:none;}#mermaid-svg-nt0JTM4Bv7pgtfIN .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-nt0JTM4Bv7pgtfIN .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-nt0JTM4Bv7pgtfIN .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-nt0JTM4Bv7pgtfIN .actorPopupMenu{position:absolute;}#mermaid-svg-nt0JTM4Bv7pgtfIN .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#mermaid-svg-nt0JTM4Bv7pgtfIN .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-nt0JTM4Bv7pgtfIN .actor-man circle,#mermaid-svg-nt0JTM4Bv7pgtfIN line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-nt0JTM4Bv7pgtfIN :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

崩溃恢复时:

若 Binlog 完整则补 commit

否则回滚

1. 修改数据写入 Buffer Pool

2. 写 Redo Log(prepare 状态)

prepare 完成

3. 写 Binlog

Binlog 完成

4. 提交 Redo Log(commit 状态)

崩溃恢复判定规则：

• Redo Log 处于 prepare，且 Binlog 完整 → 提交（保证主从一致）。
• Redo Log 处于 prepare，但 Binlog 缺失 → 回滚。

八、SQL 优化与慢查询

8.1 慢查询定位三步

#mermaid-svg-fE2NRVe0Z4ppmpuD{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-fE2NRVe0Z4ppmpuD .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-fE2NRVe0Z4ppmpuD .error-icon{fill:#552222;}#mermaid-svg-fE2NRVe0Z4ppmpuD .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-fE2NRVe0Z4ppmpuD .marker{fill:#333333;stroke:#333333;}#mermaid-svg-fE2NRVe0Z4ppmpuD .marker.cross{stroke:#333333;}#mermaid-svg-fE2NRVe0Z4ppmpuD svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-fE2NRVe0Z4ppmpuD p{margin:0;}#mermaid-svg-fE2NRVe0Z4ppmpuD .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-fE2NRVe0Z4ppmpuD .cluster-label text{fill:#333;}#mermaid-svg-fE2NRVe0Z4ppmpuD .cluster-label span{color:#333;}#mermaid-svg-fE2NRVe0Z4ppmpuD .cluster-label span p{background-color:transparent;}#mermaid-svg-fE2NRVe0Z4ppmpuD .label text,#mermaid-svg-fE2NRVe0Z4ppmpuD span{fill:#333;color:#333;}#mermaid-svg-fE2NRVe0Z4ppmpuD .node rect,#mermaid-svg-fE2NRVe0Z4ppmpuD .node circle,#mermaid-svg-fE2NRVe0Z4ppmpuD .node ellipse,#mermaid-svg-fE2NRVe0Z4ppmpuD .node polygon,#mermaid-svg-fE2NRVe0Z4ppmpuD .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-fE2NRVe0Z4ppmpuD .rough-node .label text,#mermaid-svg-fE2NRVe0Z4ppmpuD .node .label text,#mermaid-svg-fE2NRVe0Z4ppmpuD .image-shape .label,#mermaid-svg-fE2NRVe0Z4ppmpuD .icon-shape .label{text-anchor:middle;}#mermaid-svg-fE2NRVe0Z4ppmpuD .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-fE2NRVe0Z4ppmpuD .rough-node .label,#mermaid-svg-fE2NRVe0Z4ppmpuD .node .label,#mermaid-svg-fE2NRVe0Z4ppmpuD .image-shape .label,#mermaid-svg-fE2NRVe0Z4ppmpuD .icon-shape .label{text-align:center;}#mermaid-svg-fE2NRVe0Z4ppmpuD .node.clickable{cursor:pointer;}#mermaid-svg-fE2NRVe0Z4ppmpuD .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-fE2NRVe0Z4ppmpuD .arrowheadPath{fill:#333333;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-fE2NRVe0Z4ppmpuD .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-fE2NRVe0Z4ppmpuD .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-fE2NRVe0Z4ppmpuD .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-fE2NRVe0Z4ppmpuD .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-fE2NRVe0Z4ppmpuD .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-fE2NRVe0Z4ppmpuD .cluster text{fill:#333;}#mermaid-svg-fE2NRVe0Z4ppmpuD .cluster span{color:#333;}#mermaid-svg-fE2NRVe0Z4ppmpuD div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-fE2NRVe0Z4ppmpuD .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-fE2NRVe0Z4ppmpuD rect.text{fill:none;stroke-width:0;}#mermaid-svg-fE2NRVe0Z4ppmpuD .icon-shape,#mermaid-svg-fE2NRVe0Z4ppmpuD .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-fE2NRVe0Z4ppmpuD .icon-shape p,#mermaid-svg-fE2NRVe0Z4ppmpuD .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-fE2NRVe0Z4ppmpuD .icon-shape .label rect,#mermaid-svg-fE2NRVe0Z4ppmpuD .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-fE2NRVe0Z4ppmpuD .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-fE2NRVe0Z4ppmpuD .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-fE2NRVe0Z4ppmpuD :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

开启慢查询日志

long_query_time 阈值采集

EXPLAIN 分析执行计划

定位 type/rows/Extra 异常

针对性建索引/改SQL/调参数

验证 QPS 与响应时间

关键配置：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; — 超过 1 秒记为慢 SQL
SET GLOBAL log_queries_not_using_indexes = 'ON';

8.2 EXPLAIN 关键字段解读

执行计划从优到劣：system > const > eq_ref > ref > range > index > ALL。

8.3 高频优化手段

九、主从复制与高可用

9.1 主从复制原理（基于 Binlog）

SQL 线程

Relay Log

从库 Slave

Binlog

主库 Master

SQL 线程

Relay Log

从库 Slave

Binlog

主库 Master

#mermaid-svg-aiiSIOwNud1x9cQp{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-aiiSIOwNud1x9cQp .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-aiiSIOwNud1x9cQp .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-aiiSIOwNud1x9cQp .error-icon{fill:#552222;}#mermaid-svg-aiiSIOwNud1x9cQp .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-aiiSIOwNud1x9cQp .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-aiiSIOwNud1x9cQp .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-aiiSIOwNud1x9cQp .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-aiiSIOwNud1x9cQp .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-aiiSIOwNud1x9cQp .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-aiiSIOwNud1x9cQp .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-aiiSIOwNud1x9cQp .marker{fill:#333333;stroke:#333333;}#mermaid-svg-aiiSIOwNud1x9cQp .marker.cross{stroke:#333333;}#mermaid-svg-aiiSIOwNud1x9cQp svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-aiiSIOwNud1x9cQp p{margin:0;}#mermaid-svg-aiiSIOwNud1x9cQp .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-aiiSIOwNud1x9cQp text.actor>tspan{fill:black;stroke:none;}#mermaid-svg-aiiSIOwNud1x9cQp .actor-line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-aiiSIOwNud1x9cQp .innerArc{stroke-width:1.5;stroke-dasharray:none;}#mermaid-svg-aiiSIOwNud1x9cQp .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-aiiSIOwNud1x9cQp .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-aiiSIOwNud1x9cQp #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-aiiSIOwNud1x9cQp .sequenceNumber{fill:white;}#mermaid-svg-aiiSIOwNud1x9cQp #sequencenumber{fill:#333;}#mermaid-svg-aiiSIOwNud1x9cQp #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-aiiSIOwNud1x9cQp .messageText{fill:#333;stroke:none;}#mermaid-svg-aiiSIOwNud1x9cQp .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-aiiSIOwNud1x9cQp .labelText,#mermaid-svg-aiiSIOwNud1x9cQp .labelText>tspan{fill:black;stroke:none;}#mermaid-svg-aiiSIOwNud1x9cQp .loopText,#mermaid-svg-aiiSIOwNud1x9cQp .loopText>tspan{fill:black;stroke:none;}#mermaid-svg-aiiSIOwNud1x9cQp .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-aiiSIOwNud1x9cQp .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-aiiSIOwNud1x9cQp .noteText,#mermaid-svg-aiiSIOwNud1x9cQp .noteText>tspan{fill:black;stroke:none;}#mermaid-svg-aiiSIOwNud1x9cQp .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-aiiSIOwNud1x9cQp .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-aiiSIOwNud1x9cQp .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-aiiSIOwNud1x9cQp .actorPopupMenu{position:absolute;}#mermaid-svg-aiiSIOwNud1x9cQp .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#mermaid-svg-aiiSIOwNud1x9cQp .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-aiiSIOwNud1x9cQp .actor-man circle,#mermaid-svg-aiiSIOwNud1x9cQp line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-aiiSIOwNud1x9cQp :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

事务提交写入 Binlog

IO 线程拉取 Binlog

写入中继日志 Relay Log

SQL 线程重放

更新从库数据

三个核心线程：主库 Binlog Dump 线程、从库 IO 线程、从库 SQL 线程。

9.2 复制模式对比

延迟监控：SHOW SLAVE STATUS 关注 Seconds_Behind_Master。大事务、从库单线程重放、网络抖动是延迟主因（可开启并行复制 slave_parallel_workers）。

十、分库分表

10.1 何时需要拆分

触发信号：单表行数 > 1000 万、单库数据量 > 500GB、QPS 高到单实例瓶颈、备份/恢复耗时过长。

10.2 拆分维度

#mermaid-svg-rcGg97mfkCdnK4wu{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-rcGg97mfkCdnK4wu .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-rcGg97mfkCdnK4wu .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-rcGg97mfkCdnK4wu .error-icon{fill:#552222;}#mermaid-svg-rcGg97mfkCdnK4wu .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-rcGg97mfkCdnK4wu .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-rcGg97mfkCdnK4wu .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-rcGg97mfkCdnK4wu .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-rcGg97mfkCdnK4wu .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-rcGg97mfkCdnK4wu .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-rcGg97mfkCdnK4wu .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-rcGg97mfkCdnK4wu .marker{fill:#333333;stroke:#333333;}#mermaid-svg-rcGg97mfkCdnK4wu .marker.cross{stroke:#333333;}#mermaid-svg-rcGg97mfkCdnK4wu svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-rcGg97mfkCdnK4wu p{margin:0;}#mermaid-svg-rcGg97mfkCdnK4wu .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-rcGg97mfkCdnK4wu .cluster-label text{fill:#333;}#mermaid-svg-rcGg97mfkCdnK4wu .cluster-label span{color:#333;}#mermaid-svg-rcGg97mfkCdnK4wu .cluster-label span p{background-color:transparent;}#mermaid-svg-rcGg97mfkCdnK4wu .label text,#mermaid-svg-rcGg97mfkCdnK4wu span{fill:#333;color:#333;}#mermaid-svg-rcGg97mfkCdnK4wu .node rect,#mermaid-svg-rcGg97mfkCdnK4wu .node circle,#mermaid-svg-rcGg97mfkCdnK4wu .node ellipse,#mermaid-svg-rcGg97mfkCdnK4wu .node polygon,#mermaid-svg-rcGg97mfkCdnK4wu .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-rcGg97mfkCdnK4wu .rough-node .label text,#mermaid-svg-rcGg97mfkCdnK4wu .node .label text,#mermaid-svg-rcGg97mfkCdnK4wu .image-shape .label,#mermaid-svg-rcGg97mfkCdnK4wu .icon-shape .label{text-anchor:middle;}#mermaid-svg-rcGg97mfkCdnK4wu .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-rcGg97mfkCdnK4wu .rough-node .label,#mermaid-svg-rcGg97mfkCdnK4wu .node .label,#mermaid-svg-rcGg97mfkCdnK4wu .image-shape .label,#mermaid-svg-rcGg97mfkCdnK4wu .icon-shape .label{text-align:center;}#mermaid-svg-rcGg97mfkCdnK4wu .node.clickable{cursor:pointer;}#mermaid-svg-rcGg97mfkCdnK4wu .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-rcGg97mfkCdnK4wu .arrowheadPath{fill:#333333;}#mermaid-svg-rcGg97mfkCdnK4wu .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-rcGg97mfkCdnK4wu .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-rcGg97mfkCdnK4wu .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-rcGg97mfkCdnK4wu .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-rcGg97mfkCdnK4wu .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-rcGg97mfkCdnK4wu .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-rcGg97mfkCdnK4wu .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-rcGg97mfkCdnK4wu .cluster text{fill:#333;}#mermaid-svg-rcGg97mfkCdnK4wu .cluster span{color:#333;}#mermaid-svg-rcGg97mfkCdnK4wu div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-rcGg97mfkCdnK4wu .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-rcGg97mfkCdnK4wu rect.text{fill:none;stroke-width:0;}#mermaid-svg-rcGg97mfkCdnK4wu .icon-shape,#mermaid-svg-rcGg97mfkCdnK4wu .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-rcGg97mfkCdnK4wu .icon-shape p,#mermaid-svg-rcGg97mfkCdnK4wu .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-rcGg97mfkCdnK4wu .icon-shape .label rect,#mermaid-svg-rcGg97mfkCdnK4wu .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-rcGg97mfkCdnK4wu .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-rcGg97mfkCdnK4wu .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-rcGg97mfkCdnK4wu :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

分库分表

垂直拆分

水平拆分

垂直分库: 按业务用户库/订单库/商品库

垂直分表: 按冷热基础信息表/扩展信息表

水平分表: 单表按分片键拆 N 张

水平分库: 多实例分担

10.3 分片策略

工程建议：引入 ShardingSphere / MyCat 等中间件做逻辑分片，业务代码尽量无感知；跨分片 JOIN、分布式事务（Seata）、全局唯一 ID（雪花算法）是必须解决的配套问题。

十一、数据类型与范式设计

11.1 类型选型原则

• 整数：用 INT/BIGINT，明确 UNSIGNED；TINYINT 存布尔（MySQL 无原生 bool）。
• 日期：优先 DATETIME（范围大、无时区）或 TIMESTAMP（4 字节、受时区影响、上限 2038）；避免使用字符串存时间。
• 字符串：定长用 CHAR，变长用 VARCHAR；超长文本用 TEXT（不进聚簇索引页）；JSON 用原生 JSON 类型（5.7+，支持索引化虚拟列）。
• 金额：禁止 FLOAT/DOUBLE（精度丢失），用 DECIMAL(M,D) 或整数分存储。

11.2 三范式与反范式

反范式权衡：严格范式导致多表 JOIN 性能损耗，在 读多写少、报表类 场景，可以有控制地冗余字段（如订单表冗余用户名），以空间换 JOIN 性能。核心是 按读写比与一致性要求做取舍。

十二、MySQL 8.0 关键新特性速览

总结：高频面试/实战速记

本文档持续可迭代：建议配合 EXPLAIN、SHOW ENGINE INNODB STATUS、sys.innodb_buffer_pool_stats 等命令在真实环境验证，理论结合 performance_schema 观测效果最佳。