编程导航会员会员
登录/注册

特训营刷题知识整理（MySQL）

[TOC]

MySQL

引擎

名称说明
InnoDB默认引擎行锁，锁索引记录，支持事务、回滚，大多数时候可以用这个引擎；
MyISAM老版本（5.5-）的默认引擎，表锁，不支持事务、回滚，适合大量读极少数写入场景；
MEMORY表锁，基于内存，速度快但是无持久化（重启丢失），用来做临时表、会话缓存
Archive行锁，只支持INSERT/SELECT，不支持索引，压缩率高，日志、历史订单等场景常用

InnoDB的组件

内存组件（性能核心）

组件名称作用
InnoDB Buffer Pool缓存池，缓存数据页、索引页、undo页、插入缓存，是减少磁盘IO的核心
redo log buffer缓存redo log（事务日志），减少redolog刷盘次数
自适应哈希索引AHI缓存索引页构建哈希索引(自动为高频访问的索引也页生成哈希表)
索结构缓存LSC缓存行/表锁元数据，减少锁管理内存开销

磁盘组件（数据持久化核心）

组件名称作用
表空间存储数据和索引的核心磁盘区域：系统表、独立表、临时表
redo Log File记录数据修改的物理日志
Undo Log记录 修改前状态，用于事务回滚 MVCC
Binlog记录所有数据修改逻辑日志 ，主从复制、数据恢复
双鞋缓冲区解决数据页刷盘部分写失效问题，保障数据页的完整性

功能组件（调度/并发/事务控制）

组件名称作用
后台线程BT异步处理刷盘、清洗、监控，避免阻塞用户线程
事务管理器控制事务的ACID特性，管理事务开始、提交、回滚
锁管理器管理行锁/表锁/意向锁，保障并发访问的一致性
MVCC读不加锁，提升并发性能，解决幻读问题

索引

分几个角度分类索引：数据结构、InnoDB存储方式、索引性质

数据结构：

名称说明
B+树InnoDB和MyISAM默认索引方式，多层平衡树，叶子结点用双向链表串联，技能快速定位单条数据也能高效做范围查询
哈希通过hash函数直接算出数据位置，效率高，但是不支持范围查询和排序。内存引擎默认使用哈希索引
全文索引文本分词倒排索引，适合对TEXT类型做关键字搜索
空间索引基于R树实现，处理坐标等多为数组，支持区域查询、距离计算

InnoDB存储方式：

  • 聚簇索引：主键索引，叶子结点存储真实数据行，可以单主键、联合主键，未设置主键引擎会自动生成隐藏6字节自增ID作为主键
  • 非聚簇索引：二级索引，叶子结点存储索引值+主键值，不存储具体数据

索引性质 ：

  • 主键索引：唯一非空
  • 唯一索引：唯一允许空
  • 普通索引：无唯一索引，用于加速查询
  • 联合索引：多列组合形成索引，遵循最左前缀原则
  • 全文索引：全文搜索
  • 空间索引：GIS数据使用

数据结构的比较

B+树

特性：

  1. 矮多叉树寻址磁盘IO少；
  2. 非叶子结点只存key和指针节省空间；
  3. 叶子结点双向链表串联适合范围查询（MySQL对B+的优化：非叶子结点也有链表相连）

B树

特性：节点包含数据行+子节点指针

B*树

特性：B+树的变性，强化节点空间利用率，减少分裂次数

  1. 继承B+树特性，非叶子结点只存索引，叶子结点存数据行相互间有 双向链表
  2. 增加兄弟节点指针，非叶子结点链表相连，用于放标节点间数据转移
  3. B+树节点满会直接分裂，B*树会优先向兄弟节点借空间，借不到在分裂
  4. 非叶子结点最小节点数从1/2提升到2/3，提高空间利用率

选择B+树的原因

B树节点存储数据，导致节点存储效率低，树高会更高，磁盘 IO会更多，并且B树的范围查询性能差，节点之间独立，范围查询需要多次回溯、遍历不同分支，效率低。B树更适合内存数据库可以忽略IO开销的场景，在查询单条数据性能优秀

B+树多叉树，非叶子结点只存索引，存储效率高，叶子结点双向链表串联，方便范围查询、寻址

B*树侧重的是空间利用率，相比B+树在时间和空间的选择上倾向空间，而这个空间利用率的提升并不是没有代价的，节点分裂先遍历兄弟节点、判断、移动数据时间成本远大于B+树直接分裂的空间成本；节点填充率也不是越高越好，节点填充率越高，节点的合并、数据迁移触发频率更高，填充率越低节点越松散，合并、迁移概率越低

综上，B+树与B树、B*树的比较，是性能、时间、空间上最合适的数据结构选择

一条查询的流程

根据查询sql条件定位到主键索引、二级索引，从根向下一次顺序查询

  1. 垂直查找：二分查找定位叶子结点
  2. 业内查找：二分查找定位靶数据行
  3. 水平查找：顺序遍历（链表）定位范围数据

计算一棵B+树的数据存储量

计算公式：

  • 聚簇索引：单叶子结点存储数据行数 * 阶层m ^ (树高-1)
  • 非聚簇索引：（二级索引+主键索引）单叶子节点占用 * 阶层m ^ (树高-1)

InnoDB默认单页大小为16KB，固定开销154KB，预留空间约15KB

**示例：**聚簇索引假设存储表结构

▼
mysql
复制代码
CREATE TABLE tablename (
  id INT PRIMARY KEY,       -- 4 字节
  name VARCHAR(10),         -- 最多 30 字节（10×3）
  age TINYINT,              -- 1 字节
  create_time DATETIME      -- 8 字节
);单条数据43字节，每条记录需要固定开销27字节（事务字段6+回滚指针7+变长字段2+NULL标识位1+记录头11）合计单条数据 占用70字节

计算

  1. 叶子结点可存储数据量：15KB/70B = 214条数据
  2. 非叶子结点存储量，取阶数m = 15KB / （主键大小BIGINT8B + 垂直指针8B）= 937
  3. 三层B+树可存储数据量约为 937^2*214 = 1.8亿的数据量

页的物理结构

部分名称固定占用空间核心作用可变
File Header（文件头）38B标识页的类型、归属表空间、水平指针等，是页的「身份证」❌
Page Header（页头）56B记录页的内部状态（如页中记录数、B + 树层级、空闲空间位置等）❌
Infimum + Supremum26B页内的「哨兵记录」，标记记录的最小 / 最大值，避免边界判断❌
User Records（用户记录）存储真正的业务数据（聚簇索引的行数据、二级索引的 关键字+主键），数据水平指针，非叶子结点的垂直指针（即索引字段本身）✅
Free Space（空闲空间）页内未使用的空间，用于新增记录，空闲空间耗尽会触发页分裂✅
Page Directory（页目录）>26B存储用户记录的「槽位指针」，加速页内记录查找✅
File Trailer（文件尾）8B校验页的完整性（防止页损坏）❌

不同数据类型主键占用量

主键类型占用字节
TINYINT1
SMALLINT2
MEDIUMINT4
BIGINT8
CHAR(n) utf8n*3(utf8),n*4(utf8m64)
VARCHAR(n)实际长度+1~2
DATE3
DATETIME8

页的指针们

  • 垂直指针：位于数据行，核心路由指针，位于非叶子结点（即索引信息本身）
  • 页水平指针：位于页文件头，双向链表指针，用于高效范围（翻页）查询
  • 数据水平指针：数据行，用于指向上一行和下一行

索引的管理

索引并不是建好了就完了，后续要根据实际业务场景管理索引的生命周期（建、查、改、删）

索引并不是没有代价的，索引的本质是用空间换时间，每个索引都是一颗B+树，会占用磁盘空间，并且会影响数据的写入效率（写入操作要维护索引树）

建索引的注意事项：

  1. 不要大量建索引，占空间并且增加写入成本（写入频繁的表谨慎建索引）
  2. 区分度高的字段适合做索引（SELECT COUNT(DISTINCT col) / count(1) ）越接近1越好
  3. 大字段不要建索引不得已情况下考虑前缀索引
  4. 多字段使用联合索引
  5. 排序、分组字段需要索引，不然会扫表重排

索引的优化

索引的失效

  • 不符合最左前缀原则

  • 索引列运算、函数嵌套

  • OR关键字链接字段

    两个索引用or相连，如果要走索引他会先走A索引的B+树，拿到主键id；再走B索引的B+树拿到主键id；两次查询的主键id去重合并；再回表走聚簇索引查业务数据。

    这个过程包含2次B+树查询+内存去重+批量回表，效率远低于全表扫描，MySQL的优化器会判定全表扫描更划算，放弃两个单列索引

    只有在两个字段是联合索引的情况下OR才会索引生效

  • 数据类型的隐式转换，varchar = int 实际隐式的调用了CAST函数

  • 优化器判断问题、取反操作(使用!= 或 <> 、NOT IN 或者 NOT EXISTS、IS NOT NULL) 放弃索引的阈值：查询数量超过表15~30%，优化器会放弃索引选择扫全表

    B+树是有序的单向区间索引，优化器是成本优先原则，而!=是筛选出除了某个值之外的所有数据，B+树天生不擅长查反向、离散的全集

    假设强制走索引，取非值，首先要在索引查询<x;再索引查询>x;再合并去重；最后拿着所有id批量回表走聚簇索引。这个成本是2次完整的B+树索引+内存合并计算+回表聚簇索引，成本远高于1次磁盘全扫IO

  • ORDER BY 非主键、非覆盖索引，扫全表

事务

事务是什么

事务是数据库操作的一个逻辑单元，由一组SQL语句组成，要么全部执行，要么全部回滚，保证数据的一致性和完整性

事务的特性：原子性、一致性、隔离性、持久性 ACID

事务的隔离级别

名称性能
读未提交性能最好，可靠性差
读已提交RCoracle、postgreSQL、SQLServer默认
可重复度RRMySQL默认级别
串行读无并发能力最安全

RC和RR的区别：

RC每次查询都会重新生成ReadView，别的事务提交了下次查询就能看到

RR每次查询不会重新生成ReadView，会复用事务内第一个生成的ReadView，即便别的事务提交了也看不到

为什么MySQL默认隔离级别是RR，而现在主流大厂已倾向选读已提交？

  1. 选择RR作为默认是为了兼容历史版本，早期MySQL主从复制binlog的格式只有statement，RC在同事务中会出现前后不一致的情况，RR读快照可以保障事务内的数据一致性

  2. RC对比RR在并发能力上有很大的优势。RR引入的间隙锁、临键锁会大大增加死锁的概率，并且快照读需要维护更多的undolog版本链，会加大内存消耗。而RC存在的问题可以通过更小的成本规避

    1. binlog现在已经默认row，已不存在主从不一致问题
    2. 大多数业务场景并不刚需统一事务内读取数据不变，即便有也可以通过SELECT FOR UPDATE手动加锁

事务的核心组件

  • UndoLog：回滚日志，存储于表空间中，原子性保障 记录物理变更的反向操作，支持事务回滚和MVCC的核心工具 每条数据都有两个隐藏字段trx_id记录最后修改这条数据的事务ID，roll_point指向rundolog中的上一个版本，多个版本会形成一条版本链

    undolog的持久化

    undo不是直接刷盘，先写入内存Undo缓冲，在通过redolog间接持久化，最终刷入磁盘表空间

  • RedoLog：重做日志，独立存储的物理日志文件先写日志在写磁盘，保障持久性。WAL机制 配置innodb_flush_log_at_trx_commint 1：每次刷盘，最安全，性能差； 0：每秒1次，宕机丢失 1秒数据； 2：写到操作系统缓存，服务器宕机会丢失数据库宕机不会丢

    WAL: Write-Ahead Logging，先写日志在写数据。

    执行流程

    1. 事务执行时，将行为写入内存redologbuffer，同时更新内存中BufferPool，生成脏页
    2. 事务提交时，InnoDB将redologbuffer的内容刷入磁盘redolog文件，只要日志落盘成功，事务持久化完成，即便此时宕机redolog日志已经存在
    3. 后台刷新：内存中的脏页BufferPool，由后台线程异步将脏页刷到磁盘中 这也是为什么事务提交成功了但是磁盘的idb文件并没有实时更新的原因

    redolog重写是如何定位断点的？ redolog的循环写机制，ib_logfile0和ib_logfile1组成环形结构，存在两个指针

    • writepos：当前写（redolog）指针
    • checkpoint：脏页刷盘指针（重写定位点）

    重启通过checkpoint定位断点

  • 锁+MVCC：隔离性

事务的生命周期

假设有一个update操作

  1. 开启事务，修改数据 查询目标行 -> 加排他锁 -> 加载所在页到BufferPool 内存中，修改内存中的数据（脏页）

  2. 生成Undolog、Redolog undo表中记录旧值；记录事务id、指向undolog的指针 为支持后续ROLLBACK或一致性读 构造redolog，描述将某页某偏移量改为新值->写入redologbuffer

  3. 执行commit 第一阶段(prepare)：redolog写入磁盘记录为prepare；记录事务状态为TRX_PREPARE；binlog(如开启)写入文件 第二阶段(commit)：事务SQL写入binlog(如开启)、调用fsync()刷盘​ -> InnoDB引擎提交，将redolog标记为commit -> 释放行锁 -> 返回成功给客户端

  4. 后台异步刷脏页 提交完成后不会立刻将脏页写入磁盘，由后台现成逐步将脏页刷回数据文件

    这就是为什么即使提交成功，磁盘的.ibd文件可能还没更新的原因

    ▼
    mermaid
    复制代码
    flowchart TD
    A["[ BEGIN ]"] --> B["执行 DML → 获取行锁"]
    B --> C["修改 Buffer Pool 中的数据页"]
    C --> D["生成 Undo Log（记录旧值）"]
    D --> E["生成 Redo Log（写入 redo log buffer）"]
    E --> F["COMMIT 触发"]
    
    F --> G["Phase 1: Prepare"]
    G --> H["→ 写 redo log 并 fsync（PREPARE 状态）"]
    
    H --> I["Phase 2: Commit"]
    I --> J["→ 写 binlog 并 fsync"]
    J --> K["→ 写 redo log commit 标记并 fsync"]
    K --> L["→ 释放锁"]
    L --> M["→ 返回客户端成功"]
    
    M --> N["[ 后台线程异步刷脏页到磁盘 ]"]
    N --> O["[ 宕机? → 启动时进行 Crash Recovery ]"]
    
    %% 样式优化（可选，让阶段块更醒目）
    style G fill:#e1f5fe,stroke:#01579b,stroke-width:2px
    style I fill:#f3e5f5,stroke:#4a148c,stroke-width:2px

事务提交的两个阶段

事务二阶段提交的原因 因为redolog和binlog属于不同层，在redolog写入binlog过程中如果发生宕机，可能出现数据一致性问题，所以需要二阶段提交

  • prepare：redolog写盘，记录stat 为prepare
  • commit：binlog写盘，redolog 记录stat为commit

事务的回滚和异常中断

  1. 事务执行失败/取消，触发ROLLBACK执行，redolog只负责恢复已提交的修改，所以回滚操作redolog不参与，已存在的未提交redo后续会被覆盖掉无需管理
  2. 事务异常终止
    • 运行时异常（锁超时/主键冲突） Undolog 触发回滚，执行反向操作撤销所有修改； Redolog丢弃所有未提交记录
    • MySQL宕机重启 重启后InnoDB会先重做，后回滚
      1. 先重做redolog，恢复到宕机前状态，重放所有Redolog（定位Checkpoint LSN，逐行重放）
      2. 执行undolog，回滚未提交事务（扫描事务表空间，筛选出所有未提交事务，根据事务ID找到所有undolog记录，执行undolog回滚，标记事务已终止，清理相关undolog）
      3. 对已提交事务的redo重放完成持久化；未提交事务就不用管他后续会被新日志素钙

MVCC

多版本并发控制，让 读写操作互不阻塞

写入操作时，MySQL不会立即覆盖原有数据，而是生成新版本的记录，每个记录保留对应的版本号和时间戳，串联形成版本链

读操作，可以 根据事务启动时间去版本链上找到自己需要读取的版本数据

ReadView可见性判断

  1. creator_trx_id：当前事务id
  2. m_ids：活跃的事务ID（已启动，未提交）
  3. min_trx_id：活跃id中最小值
  4. max_trx_id：下一个将被分配的事务ID

快照读和当前读

  • 快照读就是普通SELECT走MVCC，读的是历史快照 不加锁
  • 当前读是SELECT FOR UPDATE，读最新版本，且加锁

分析、优化

SQL查询分析、优化

SQL分析

EXPLAIN sql 查看SQL执行效率 MySQL5.6+ 支持 EXPLAIN FORMAT = JSON:更详细的JSON格式信息 MySQL8新增 EXPLAIN ANALYZE:真正执行查询并给出实际执行时间、行数

▼
mysql
复制代码
EXPLAIN SELECT id,name FROMT table;
字段名称说明
type访问类型：性能从高到低列：const>eq ref>ref>range>index>ALL
key索引字段，显示NULL 则没走索引 possible_keys只是候选
rows预估扫描行数，越小越好
extrausing index 覆盖索引；using filesort 说明排序无法用索引得额额外排序；using temporary说明用了临时表

SQL优化 // TODO细化实现

调优的核心：减少I/O，避免无效计算

步骤：

  1. 定位慢SQL

    ▼
    mysql
    复制代码
    SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE '%log_output%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- 1. 开启慢日志总开关
SET GLOBAL slow_query_log = ON;
-- 2. 设置输出方式：同时写入文件+表（推荐，兼顾备份和查询）
SET GLOBAL log_output = 'FILE,TABLE';
-- 3. 设置慢SQL阈值（1秒，可按需调整）
SET GLOBAL long_query_time = 1;
-- 4. 可选：记录管理类慢SQL（ALTER/CREATE等）
SET GLOBAL log_slow_admin_statements = ON;
-- ！！！注意开启之后需要重新连接数据库开启新的session生效！！！

-- 执行一条耗时超过1秒的SQL（SLEEP(2)强制耗时2秒）
SELECT SLEEP(2), mysql.user.* FROM mysql.user;

-- 查询慢sql
SELECT 
  start_time,
  query_time,
  LOCK_TIME,
  rows_examined,
  rows_sent,
  CONVERT(sql_text USING utf8mb4) AS sql_text  -- 核心：BLOB转utf8mb4字符串
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 10;

  2. 分析执行计划 EXPAIN slow sql

  3. 调优

    • 索引优化 合理设计联合索引 ，避免/减少回表
    • SQL查询优化 规范、优化业务SQL写法，如禁止SELECT * 、分页方式等
    • 架构优化 数据库分库分表、读写分离、前置redis缓存/Caffeine本地缓存、消息队列 // TODO

索引的监听和优化

工具

  • MySQL自带工具

    ▼
    mysql
    复制代码
    -- 索引详情清单
SELECT
  TABLE_NAME 表名,
  INDEX_NAME 索引名,
  COLUMN_NAME 索引字段,
  SEQ_IN_INDEX 字段在索引中的顺序,
  INDEX_TYPE 索引类型,
  NON_UNIQUE 是否非唯一索引(0=唯一/主键,1=普通)
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'schema_name' 
  AND TABLE_NAME = 'table_name'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
-- 碎片率、索引大小，根据碎片率划定阈值处理
SELECT
  TABLE_NAME 表名,
  DATA_LENGTH/1024/1024 数据大小_MB,
  INDEX_LENGTH/1024/1024 索引大小_MB,
  DATA_FREE/1024/1024 碎片大小_MB,
  CONCAT(ROUND(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH)*100,2),'%') 碎片率
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'schema_name' 
  AND TABLE_NAME = 'table_name';
-- 优化方式
-- 方式1：重建表+索引，清理碎片
OPTIMIZE TABLE 你的表名;
-- 方式2：分析表，更新索引统计信息，轻量优化
ANALYZE TABLE 你的表名;
-- 方式3：MySQL5.7+ 视图查询
-- 1. 排查僵尸索引
sys.schema_unused_indexes
-- 2. 排查低效索引
SELECT
  OBJECT_NAME 表名,
  INDEX_NAME 索引名,
  COUNT_FETCH 使用次数,
  SUM_ROWS_EXAMINED 总扫描行数,
  SUM_ROWS_RETURNED 总返回行数,
  CONCAT(ROUND(SUM_ROWS_RETURNED/SUM_ROWS_EXAMINED*100,2),'%') 命中效率
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE TABLE_SCHEMA = 'schema_name' 
  AND TABLE_NAME = 'table_name'
  AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_FETCH ASC;
-- 3. 排查冗余索引
sys.schema_redundant_indexes;

  • 三方工具：Prometheus + Grafana + mysqld_exporter 实时监听数据库性能 //TODO

分页的优化

分页的实现

  1. 基于行号 LIMIT 偏移量,数量 = LIMIT 数量 OFFSET 偏移量

    ▼
    mysql
    复制代码
    SELECT * FROM T LIMIT 1000000,1000;
SELECT * FROM T LIMIT 1000 OFFSET 1000000

  2. 基于主键游标分页

    ▼
    mysql
    复制代码
    SELECT * FROM T WHERE id > 
(SELECT ID FROM TABLE WHERE NAME = 'X' ORDER BY ID LIMIT 1000000,1)
LIMIT 1000

  3. 两步式游标分页（常用）

    ▼
    mysql
    复制代码
    SELECT t.* FROM TABLE t JOIN (SELECT ID FROM TABLE WHERE ID >1000000 LIMIT 1000)tmp ON t.id = tmp.id

分页实现的优劣分析

  1. 基于行号，直接使用LIMIT 偏移量, 数量的写法会扫描前偏移量条数据丢弃，浪费大量 IO，只适合用于小表，小数量情形使用

    MySQL不支持跳过偏移量，都是需要遍历偏移量的，偏移量大的时候就造成性能浪费；

    OFFSET是基于行数的分页，在分页查询时有数据的变更（增、删、改）会出现翻页时数据 重复出现/数据丢失的情况

  2. 基于主键，要求必须使用主键字段，必须唯一、有序、自增；分页需求是下一页不支持跳表

    非主键字段分页使用联合索引+主键,通过联合索引定位时间，在用主键筛选

    ▼
    mysql
    复制代码
    idx_createtime_id(create_time,id)
SELECT ID FROM T WHERE CREATE_TIME > 'DATE' AND ID > 1000 LIMIT 1000

    不支持跳表怎么办 ：

    1. 我就不跳了，只做上一页下一页😁
    2. 先查询第N页的1st id，二次查询id> 1stid，虽然还是有一次 OFFSET 但是因为只查询1条数据开销较小，如果N特别大还是不太行

    为什么不使用BETWEEN AND ？ 因为存在删除情况，id并不一定绝对连续，只适合在主键绝对连续的场景

  3. 两步式和基础主键游标本质是同一套高性能逻辑的不同落地形式，当查询需要过滤ID 关联其他表时，两步式更优，因为前者的执行顺序是先关联再过滤，而后者是先过滤再关联

    ▼
    MYSQL
    复制代码
    -- 基础写法：需先查主表，再关联其他表（可能扫描更多数据）
SELECT t.id,t.name,o.order_no 
FROM table t
LEFT JOIN order o ON t.id=o.user_id
WHERE t.id>1000 LIMIT 1000;

-- 两步式JOIN：先精准过滤1000个ID，再关联（仅关联1000条数据）
SELECT t.id,t.name,o.order_no 
FROM table t
JOIN (SELECT id FROM table WHERE id>1000 LIMIT 1000) tmp ON t.id=tmp.id
LEFT JOIN order o ON t.id=o.user_id;

优化方案

  • 子查询优化：先子查询二级索引快速定位id，再根据id聚簇索引获取数据
  • 游标分页：每次查询都返回当前页最大id，下次查询以这个id作为起点，但是只能连续翻页不能跳页
  • 搜索引擎：Elasticsearch // TODO

翻页跳表查询的实现

  • 主键严格自增、连续 游标获取id，join查询详情

    ▼
    mysql
    复制代码
    -- 第一步：游标分页查id，无OFFSET，无IO浪费
SELECT id FROM table WHERE id>999000 LIMIT 1000;
-- 第二步：JOIN查详情，高性能
SELECT t.* FROM table t JOIN (SELECT id FROM table WHERE id>999000 LIMIT 1000) tmp ON t.id=tmp.id;

  • 主键非连续，存在断层 建分页锚点表，查询锚点表获取maxid，二次查询详情

    ▼
    mysql
    复制代码
    CREATE TABLE page_anchor (
    page_num INT PRIMARY KEY COMMENT '页码',
    start_id BIGINT NOT NULL COMMENT '该页的起始主键ID'
);
-- 用游标分页查每一页的起始id，插入锚点表
INSERT INTO page_anchor (page_num, start_id)
SELECT @page := @page +1 AS page_num, id AS start_id
FROM table, (SELECT @page :=0) t
WHERE id>0 LIMIT 每页条数 OFFSET 0; -- 循环执行，直到所有数据处理完

-- 跳第1000页，先查锚点表的起始id（毫秒级，主键查询）
SELECT start_id FROM page_anchor WHERE page_num=1000;
-- 再用游标分页查详情，无OFFSET，无IO浪费
SELECT t.* FROM table t JOIN (SELECT id FROM table WHERE id>start_id LIMIT 1000) tmp ON t.id=tmp.id;

日志

binlog：binlog主要职责是主从复制和数据恢复 binlog的三种格式

  • statement，记录原始SQL，日志量小，同步时会导致主从不一致情况，如now()的执行
  • row格式记录行变更前后完整数据，日志量大但是安全
  • mixed，自动判断，普通 用statement，有风险的用row，但是不是很智能

redolog：保障宕机后数据不丢 见事务的核心组件

undolog：支持事务回滚和MVCC的核心 见事务的核心组件

维度/名称binlogredologundolog
层级Server层InnoDB引擎InnoDB引擎
日志类型逻辑日志物理日志逻辑日志
写入方式追加写循环写，固定大小链式存储
核心作用主从、数据恢复崩溃恢复事务回滚，MVCC
事务相关提交后写入执行中持续写入执行中写入

锁

粒度区分

  • 表锁：锁全表，适合写入情形极少场景
  • 行锁：锁行
    • 记录锁：锁行，根据索引锁定，无索引会锁全表
    • 间隙锁：锁区间，禁止INSERT操作，但是不禁UPDATE/DELETE 插入意向锁：等待间隙的提示锁，当对间隙操作等待间隙锁时会生成
    • 临键锁：记录锁+间隙锁，MVCC防止幻读的核心技术 间隙锁只能锁已知范围区间，无法对nextkey进行闭合，所以需要临键锁锁定nextkey

模式区分

  • 共享锁S：允许多事务同时读
  • 排他锁X：独占锁，禁止其他读写FOR UPDATE

元数据锁 分读锁（共享锁）、写锁（排他锁），

防止DDL和DML操作的冲突，对表结构的更改和对数据的操作(SELECT/INSERT/UPDATE/DELETE)相互排斥锁定，保障数据的一致性

意向锁

表锁，用于快速判断是否可以上锁的标记锁

自增锁

为了自增列在高并发INSERT情况下正常分配递增值

谓词锁

空间索引无NextKey这类的绝对排序概念，表锁

乐观锁

先操作，后校验，全程不使用数据库的锁机制

对冲突预期乐观，等到真正需要更新时检查数据是否 被更改

  1. 在数据表中维护veresion
  2. 读取数据时读取version
  3. 事故更新数据只有获取的version和读取version一致才写入
  4. 如果version不一致则，则说明数据已被其他书屋修改

悲观锁

直接加锁

对冲突预期悲观，假设冲突一定发生，在操作数据钱先获取锁

异常情况

脏读、不可重复读、幻读

名称描述方案
脏读指读取到未提交的数据，可能因为事务回滚等丢失MVCC快照读，不会出现脏读情况
不可重复读单事务中对数据的更改，导致事务中前后读取不一致RR级别下，ReadView在第一次读取时就固定了，后续的查询不会重建ReadView
幻读读取数据之后，对标有插入/删除操作，导致读取数量count1不一致快照读不存在幻读情况；当前读可以用间隙锁锁住范围；
但是先快照读，再当前读可能会出现幻读情况

死锁

指两个或多个事务相互持有对方的锁，事务中有对对方锁定表的写入操作，相互等待对方锁的释放导致的死循环

InnoDB引擎默认开启自动检测``innodb_deadloc_detect，当检测到死锁引擎会自动选择一个代价小的事务回滚，释放它持有的锁；

锁等待时间innodb_lock_timewait默认50s，超过时间会自动放弃事务

死锁的处置

  • 自动处理：又Innodb引擎自动检测回滚事务、释放锁；锁等待超期回滚并释放锁

  • 手动干预

    ▼
    mysql
    复制代码
    
SHOW ENGINE INNODB STATUS -- 获取死锁日志片段

-- 1. 查看当前锁信息
-- MySQL8 之前
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INTOFMATION_SCHEMA.INNODB_LOCK_WAITS;
-- MySQL8.0+
SELECT * FROM PERFORMANCE_SCHEMA.DATA_LOCKS;
SELECT * FROM PERFORMANCE_SCHEMA.DATA_LOCK_waits;

-- 2. 找到事务和线程的对应关系
SELECT TRIX_ID, TRX_STATE, TRX_STARTED, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX

-- 3. kill导致阻塞的线程
KILL <pid>

死锁的规避

  1. 拆分大事务
  2. 固定加锁顺序，避免循环依赖
  3. 降低隔离级别
  4. 合理建索引
  5. 调整锁等待时间

主从延迟

主从延迟是必然的，无法完全消除，只能尽可能规避

**原因：**主库写binlog -> dump线程推送变更事件 -> 从库拉取数据 -> 从库I/O写relaylog -> SQL线程重放。在这个流程上任何一个环节的时延都可能导致主从延迟

处置方案

  • 关键业务强制走主库
  • 延迟感知，写操作后记录时间戳，短期内读强制走住库，过了延迟窗口时间再走从库
  • 二次查询，从库查询不到二次查询主库
  • 缓存前置：写入主库的同时写入缓存

并行复制优化

早期从库只有一个SQL线程串行重放，主库写得快从库跟不上。5.6开始引入并行复制，不同库的事务可以并发执行。5.7引入LOGICAL_CLOCK，可以同一group commit的事务可以并发。8.0引入WriteSet，只要更新的行不冲突就可以并发执行

复制模式主库返回时机性能数据可靠性
异步复制写完binlog立刻返回最高最差，主库挂了数据可能丢失
同步复制等待所有从库确认最差最高
半同步复制等至少N个从库确认折中折中

高可用

高可用的核心目标是最大限度减少数据库故障时间和保证数据一致性和服务连续性

主从复制

主库负责所有写入操作，将修改记录到binlog，主从延迟

从库

  • IO线程链接主库，拉取binlog写入本地relay log
  • SQL线程读取relaylog，重放SQL语句，同步主库数据

流程

▼
mermaid
复制代码
flowchart TD 
A[主库Master] --> B[写入数据并记录Binlog]
B --> C[Binlog Dump线程]
C --> D[从库IO线程] 
D --> E[从库RelayLog]
E --> F[从库SQL线程]
F --> G[从库执行RelayLog,同步数据]

读写分离

基于主从复制的性能扩展，主库负责写，从库负责读，分摊数据库压力，提升并发处理能力

▼
mermaid
复制代码
graph TD
A[应用程序/中间件] --> B{读写请求路由}
B -->|写请求| C[Master]
B -->|读请求| D[Slave1]
B -->|读请求| E[Slave2]
C --> F[同步数据到从库1/2]

自动故障切换

方案核心原理优点缺点
MHA（MySQL High Availability）监控主库状态，主库故障时自动选择最新的从库提升为主库，同步剩余 binlog，更新其他从库的主库地址自动切换（10~30 秒）、数据丢失少、无需额外硬件配置复杂、仅支持 MySQL 5.7 及以下（官方停更）
Keepalived + VIP用 VRRP 协议实现 VIP（虚拟 IP）漂移，主库故障时 VIP 自动切换到从库切换速度快（秒级）、实现简单仅解决 IP 漂移，不保证数据一致性（需配合半同步复制）
MySQL MGR（Group Replication）多节点组成集群，每个节点有完整数据副本，自动选主，故障时自动切换，支持读写分离官方原生支持、强一致性、自动切换、易维护对网络要求高、性能略低于主从复制
▼
mermaid
复制代码
graph TD
A[MGR集群3节点] --> B{Primary}
B -->|写请求| C[Primary]
B -->|读请求| D[Secondary1]
B -->|读请求| E[Secondary2]
C --> F[事务广播到所有节点]
F --> G[所有节点验证事务一致性]
G --> H[多数节点确认后事务提交]

分布式（分库分表+多集群）

//TODO

主库master的选举策略

  • 手动选举

    无高可用工具应急 s1：停止所有从库复制进程

    ▼
    mysql
    复制代码
    -- 停止主从复制（IO线程+SQL线程）
STOP SLAVE;
-- 查看复制状态（确认停止成功）
SHOW SLAVE STATUS\G;
-- 成功标志：Slave_IO_Running: No，Slave_SQL_Running: No

    s2：选举最优库：

    数据同步最完整：Seconds_Behind_Master = 0（无延迟） > 延迟秒数最小；

    复制位置最新：Exec_Master_Log_Pos 最大（表示同步到原主库 binlog 的最新位置）；

    硬件性能最优：CPU / 内存 / 磁盘 IO 最好的从库（保证新主库的业务承载能力）；

    业务负载最低：当前读写压力最小的从库（减少切换后性能波动）。

    s3：提升最优库为主库

    ▼
    mysql
    复制代码
    -- 重置主从复制关系（清除原relay log和复制配置）
RESET MASTER; -- 重置binlog，生成新的binlog文件（供其他从库同步）
RESET SLAVE ALL; -- 清除所有从库复制配置

-- 修改MySQL配置，开启读写（若从库配置了read_only=1）
SET GLOBAL read_only = OFF; -- 临时生效，重启后失效
-- 永久生效需修改my.cnf：read_only = 0

    s4：其他从库指向新主库

    ▼
    mysql
    复制代码
    -- 清除原复制配置
RESET SLAVE ALL;

-- 配置新主库信息（替换为新主库的IP、端口、账号、密码）
CHANGE MASTER TO
  MASTER_HOST='新主库IP',
  MASTER_PORT=3306,
  MASTER_USER='复制账号',
  MASTER_PASSWORD='复制密码',
  MASTER_LOG_FILE='新主库的binlog文件名', -- 新主库执行RESET MASTER后的binlog名
  MASTER_LOG_POS=154; -- 新主库binlog的起始位置（默认154）

-- 启动复制进程
START SLAVE;

-- 检查复制状态（确认同步成功）
SHOW SLAVE STATUS\G;
-- 成功标志：Slave_IO_Running: Yes，Slave_SQL_Running: Yes，Seconds_Behind_Master: 0

    s5：修改业务配置指向新主库（nacos更新配置）

  • 自动选举 //TODO

    • MGR
    • MHA
    • PorxySQL+Keepalived
    • 云托管

配置项详解

//TODO

版本迭代区分

//TODO

术语定义

最左前缀原则

SQL查询的索引必须从左到右匹配，如果第一个条件非索引字段会导致索引失效； 联合索引abc顺序优化器会自行优化； MySQL8有 SkipScan优化，特定场景下可以绕过最左匹配

SkipScan 核心原理

联合索引（如 idx (a,b,c)）的 B + 树按最左列 a 排序；传统查询必须带 a 才能走索引，否则全表扫描。Skip Scan 的逻辑是：

  1. 枚举最左列 a 的所有不同值（如 a=1、a=2）；
  2. 对每个 a 值，在 (a,b,c) 索引上执行子范围扫描（如 a=1 AND b=xxx、a=2 AND b=xxx）；
  3. 合并所有子范围结果，等效于 “跳过 a 列直接查 b/c”，但复用现有联合索引。

本质是把一次全表扫描拆成多次小范围扫描，当最左列基数低时，效率显著高于全表扫描。

SkipScan 不是 “打破” 最左匹配，而是在低基数最左列场景下的优化 —— 通过枚举最左列值，将无最左条件的查询拆成多次范围扫描，复用联合索引，避免全表扫描。核心是最左列低基数 + 覆盖索引 + 单表查询，高基数最左列时建议添加适配索引，而非依赖 SkipScan。

回表

回表指的是二级索引需要二次通过主键索引查询获取其他字段，因为B+树非叶子节点只有索引和主键值。

如何减少/避免回表

  • 覆盖索引 条件项、查询项都在索引内，不需要回表
  • 索引下推 MySQL5.6引入的优化，在联合索引场景中，条件中的字段为索引字段即便未生效，仍然可以在索引层进行过滤
  • 避免/减少 SELECT * 这种必然引起回表的查询
面试题
面试通关营
0个评论
全部评论
点击登录，快来和大家讨论吧～
表情
图片
暂无评论
LoaderLand
LoaderLand
等级vip
内容推荐
更多
面试官皱眉：“你懂RAG测评吗？” 我：“何止懂？我直接用Ragas把系统的幻觉率干到了0”，他愣了…
6
Prompt调优是什么
4
经典面试题：618大促多渠道同时扣减同一仓库库存，你会怎么做？
13
面经分享
7
面试官问我"设计一个秒杀系统"，我聊了40分钟
8
作者分享
更多
特训营刷题知识整理（Redis）-未完成
7
下载 APP