MySQL 面试题

1. MySQL 慢SQL排查与索引优化

❓ 题目： 一条平时运行很快的 SQL 突然变慢了，你会从哪些方向入手排查？

💡 答案：

一条 SQL 突然变慢，排查思路分几个层面：

第一步，确认是不是偶尔慢——先用 SHOW PROCESSLIST 看当前是否有大量连接堆积，或者是否有其他长事务/DDL 操作在持有锁。
第二步，用 EXPLAIN 看执行计划是否发生了改变——MySQL 优化器可能因为统计信息不准选择了错误的索引（或者从走索引变成了全表扫描）。统计信息可以用 ANALYZE TABLE 重新收集。
第三步，检查是否出现了锁等待——SELECT * FROM performance_schema.data_locks 或 information_schema.INNODB_TRX 可以查看当前事务和锁的情况，如果 SQL 长时间处于 “Waiting for table metadata lock” 或行锁等待状态，那就是并发事务导致的。
第四步，系统层面——检查磁盘 IO 是否打满、buffer pool 命中率是否下降（SHOW ENGINE INNODB STATUS 可以看 buffer pool 的命中率和脏页比例）。还有一个容易被忽略的点：如果这条 SQL 是分页查询，可能是数据量增长后 offset 过大导致的——深分页的问题本质上是 MySQL 需要扫描并丢弃前面大量行。

追问1： EXPLAIN 执行计划中，type 字段从好到差有哪些取值？什么情况下 possible_keys 不为空但 key 为空？

从好到差大致是：system > const > eq_ref > ref > range > index > ALL。system 是表只有一行数据的特例；const 是主键或唯一索引的等值查询；eq_ref 是关联查询中驱动表的一行在被驱动表中最多匹配一行（主键或唯一键关联）；ref 是普通索引的等值匹配；range 是索引范围扫描；index 是全索引扫描，虽然比全表扫描 ALL 好但本质上也遍历了整个索引树；ALL 是全表扫描，性能最差。possible_keys 有但 key 为空，说明优化器认为有索引但用索引还不如全表扫描。常见原因：查询条件的区分度太低（比如对性别字段建索引），走索引需要回表而优化器估算回表代价大于直接全表扫描，或者是统计信息不准确导致优化器误判。

追问2： 联合索引 (a, b, c)，查询条件 WHERE b = 1 AND a > 10 AND c = 2 实际用到了索引的哪几列？为什么？

实际只用到了索引的 a 和 b 两列，c 没有用到。联合索引遵循最左前缀原则，查询会先按 a 排序过滤，然后按 b 过滤。条件是 b = 1 AND a > 10 AND c = 2，MySQL 优化器会先把它调整为 a > 10 AND b = 1 AND c = 2。a > 10 是范围查询，范围查询之后的索引列 c 就无法使用了。所以 a 走了范围扫描，b 用来做索引条件下推（ICP，在索引层面过滤），但 c 完全没能用到索引，只能在回表后再过滤。注意 b = 1 是等值，所以虽然 a 是范围，b 仍然可以参与索引过滤，只是范围列 a 之后的 c 失效。

📌 易错点 / 加分项：

能提到索引条件下推（ICP）和 Multi-Range Read（MRR）这两个优化
type=index 和 type=ALL 的区别很多候选人说不清，前者遍历索引树、后者遍历数据页
能说清楚 Extra 字段中 Using index condition、Using where、Using index 的区别

2. MySQL InnoDB B+树索引原理

❓ 题目： InnoDB 为什么选择 B+ 树作为默认索引数据结构，而不选 B 树或哈希表？

💡 答案：

InnoDB 选择 B+ 树主要基于五点考虑。与哈希表对比：哈希表只支持等值查询，不支持范围查询（BETWEEN、> <），而实际业务中范围查询非常普遍，哈希表不适合作为通用存储引擎的默认索引。与 B 树对比：B+ 树的所有数据只存储在叶子节点，非叶子节点只存索引 key，这使得非叶子节点能存更多的索引 key、出度更大、树更矮——同样的页大小，B+ 树的树高更小，磁盘 IO 次数更少；B+ 树的叶子节点用双向链表连接，范围查询只需找到起点然后顺序遍历链表，B 树因为没有叶子层的链表，范围查询需要在树的层次上反复回溯，效率低很多。另外 B+ 树的稳定性更好——所有查询最终都要走到叶子节点，查询耗时是均匀的。

追问1： 聚集索引（聚簇索引）和二级索引（辅助索引）在数据存储上有什么不同？二级索引的回表是什么过程？

聚集索引的叶子节点存储的是”完整行数据”。每张 InnoDB 表必须有一个聚集索引，如果有主键则用主键，没有主键则用第一个非空唯一索引，都没有则 InnoDB 自动生成一个 6 字节的隐藏 row_id 作为聚集索引。二级索引的叶子节点存储的是”索引列的值 + 对应的聚集索引 key（主键值）“。回表过程：通过二级索引找到主键值后，再用主键值回到聚集索引的 B+ 树中查找完整行数据，相当于一次额外的 B+ 树查找。如果查询只需要主键和索引列的值，可以直接从二级索引的叶子节点获取而不回表，这就是”覆盖索引”。

追问2： 为什么建议主键自增，而且不要用长字符串？这与 B+ 树的物理存储有什么关系？

自增主键插入时数据总是追加到 B+ 树的最右侧叶子节点。如果节点满了，分裂时原节点保留原有数据、新数据进入新节点，页面利用率高且数据连续存储，物理 IO 效率最佳。如果主键不是自增的（比如用 UUID），插入位置是随机的——经常需要在 B+ 树已有页面的中间位置插入，导致频繁的页分裂和页内数据搬迁，页空间利用率降低（页分裂往往产生约 50% 的碎片），而且数据页在磁盘上变得不连续，随机 IO 大幅增加。长字符串的问题在于：

一是比较开销大——B+ 树内部查找每次都要比较 key 大小，字符串比较比整数比较慢得多
二是存储占用大——非叶子节点存 key，key 越长每个节点能装的 key 越少，树就越高，IO 次数越多
三是二级索引叶子节点存主键值，主键越长，每个二级索引叶子节点能装的条目越少，二级索引树也越高——所以长主键的伤害是连锁反应，越大越慢

📌 易错点 / 加分项：

InnoDB 页默认 16KB，B+ 树每层能存多少数据可以估算——按 key 大小除一下就知道树高
覆盖索引（Using index）不需要回表，Extra 中看到它是好事
页分裂除影响写入性能外还会造成”数据空洞”——插入大量随机主键数据后表空间比预期大很多

3. MySQL 事务隔离级别与 MVCC

❓ 题目： MySQL InnoDB 四个事务隔离级别分别解决了什么问题？脏读、不可重复读、幻读是如何被解决的？

💡 答案：

SQL 标准定义四种隔离级别：

读未提交（Read Uncommitted）：一个事务可以读到其他事务尚未提交的修改，性能最好但会引发脏读——读到一个可能被回滚的值。
读已提交（Read Committed / RC）：一个事务只能读到其他事务已提交的修改，解决脏读；但同事务内两次相同的查询可能读到不同结果（不可重复读），因为其他事务提交了更新。
可重复读（Repeatable Read / RR）：同一事务内多次查询结果一致，解决脏读和不可重复读，这是 InnoDB 的默认级别。
串行化：所有事务串行执行，读写都加锁，解决所有并发问题但并发度极低。

InnoDB 解决脏读、不可重复读、大部分幻读的组合方案是 MVCC + 临键锁。MVCC 的核心是每行记录的两个隐藏列（trx_id：最后一次修改该行的的事务ID；roll_pointer：指向 undo log 的指针）+ ReadView（当前活跃事务 ID 的列表）。读操作通过 ReadView 判断哪些版本的 undo log 可见，从而读到合适的快照版本，避免了加锁，实现”读不加锁、写不影响读”的并发模式。

追问1： InnoDB 的 MVCC 机制是如何工作的？ReadView 在什么时机创建，不同隔离级别下创建时机有什么不同？

ReadView 包含四个核心信息：creator_trx_id（当前事务ID）、m_ids（生成 ReadView 时所有活跃事务的 ID 集合）、min_trx_id（m_ids 中的最小值）、max_trx_id（下一个即将分配的事务 ID）。RC 隔离级别下，每次快照读都会生成新的 ReadView，所以能读到其他事务新提交的数据（不可重复读的根源）。RR 隔离级别下，ReadView 仅在第一次快照读时生成，之后都复用同一个，因为 ReadView 不变，通过 undo log 回滚到的数据版本也不变，实现了可重复读。判断某行版本是否可见的规则：如果 trx_id == creator_trx_id，说明是自己改的，可见；如果 trx_id < min_trx_id，说明修改它的事务在 ReadView 创建前就提交了，可见；如果 trx_id >= max_trx_id，说明修改它的事务在 ReadView 创建后才开始，不可见，沿 roll_pointer 向上找更早的版本继续判断。

追问2： RR 隔离级别下真的完全解决幻读了吗？有没有特殊情况下 RR 依然会出现幻读？

RR 没有完全解决幻读，至少有两种情况还会发生幻读：

第一种：事务 A 执行 SELECT * FROM t WHERE id > 10（快照读、无锁），事务 B 插入一条 id=15 的数据并提交，事务 A 再次执行相同的快照读——不会幻读，因为 ReadView 没变。但如果事务 A 在两次快照读之间执行了 UPDATE t SET name = 'X' WHERE id > 10（当前读），这个 UPDATE 会看到 B 新插入的 id=15 行并将其修改，之后事务 A 再快照读就会看到 id=15 的新行——发生了幻读。
第二种：SELECT ... FOR UPDATE 加临键锁，它通过间隙锁解决了其他事务插入数据的问题。但如果事务 A 用快照读，事务 B 插入并提交，事务 A 用 SELECT ... FOR UPDATE 当前读——会读到 B 插入的新行，再次产生幻读。

所以精确的表述是：MVCC 的快照读解决了幻读，但一旦涉及当前读操作，RR 仍然可能幻读，这就是为什么还需要间隙锁的原因。

📌 易错点 / 加分项：

RC 和 RR 的默认选择是场景决定的——国内公司多用 RR，国外公司 RC 更常见
gap lock 只出现在 RR 级别，RC 下没有 gap lock，所以 RC 的插入并发度更高但会有幻读
能说清”快照读”（SELECT）和”当前读”（SELECT ... FOR UPDATE、UPDATE、DELETE）的区别说明理解深入

4. MySQL 锁的类型与死锁排查

❓ 题目： InnoDB 的行锁有哪几种类型？什么是间隙锁（Gap Lock）和临键锁（Next-Key Lock），它们的设计目的是什么？

💡 答案：

InnoDB 的行锁分为三种核心类型：记录锁（Record Lock）直接锁住索引记录本身；间隙锁（Gap Lock）锁住索引记录之间的间隙，阻止其他事务在间隙中插入新记录；临键锁（Next-Key Lock）是 Record Lock + 它之前的 Gap Lock 的组合，锁住一个”左开右闭”的区间，比如索引记录为 5,10,15，对 10 加临键锁则锁住 (5, 10] 区间。间隙锁和临键锁的设计目的是解决幻读问题——在 RR 隔离级别下，防止其他事务在锁定的范围内插入新数据。要注意 InnoDB 的所有锁都是加在索引上的，没有索引就无法精确锁住行。

追问1： 一条 UPDATE ... WHERE name = 'xxx' 且 name 列有索引，它会加什么锁？如果 name 列没有索引呢？

这取决于索引情况，这是 InnoDB 锁的一个关键坑点。如果 name 列有索引，InnoDB 会通过索引定位到匹配行，对匹配的索引记录加 Next-Key Lock。如果 name 列没有索引，InnoDB 必须在聚集索引上做全表扫描，但全表扫描途中对每一条扫描到的行都会尝试加锁，最终所有满足条件的行都会被锁住，不仅如此，全表扫描路径上的间隙也会被锁住，效果上接近锁全表。查询优化器选择全表扫描还是走索引会直接影响锁范围——同样的 SQL，执行计划变了，锁的范围就完全不一样。所以线上做 DML 操作前一定要看 EXPLAIN 确认走什么索引。

追问2： 发生了死锁你会怎么排查？SHOW ENGINE INNODB STATUS 中的哪些信息对定位死锁有用？

排查死锁最核心的工具就是 SHOW ENGINE INNODB STATUS，关注其中的 LATEST DETECTED DEADLOCK 段落。它会告诉你最近一次死锁的详细信息：涉及的事务 ID、每个事务持有哪些锁（*** HOLD THE LOCK(S)）、正在等待哪个锁（WAITING FOR THIS LOCK TO BE GRANTED）、具体的 SQL 语句是什么、以及”InnoDB 选择了回滚哪个事务来解除死锁”（WE ROLL BACK TRANSACTION (1) 或 (2)）。此外还需要配合看 information_schema.INNODB_TRX 查看当前活跃事务，INNODB_LOCKS（5.7）或 performance_schema.data_locks（8.0+）查看当前持锁情况。定位到死锁 SQL 后，常见的解决策略：

调整加锁顺序使不同事务按相同顺序获取锁
减少事务持有锁的时间（拆分大事务）
对高频并发更新操作做排队或合并
在某些场景下减少隔离级别到 RC（RC 下没有 gap lock，死锁概率降低但会有幻读）

📌 易错点 / 加分项：

RC 隔离级别没有 Gap Lock，不是 InnoDB 不支持，是锁的实现策略不同
死锁不是 InnoDB 的 bug，是并发系统中不可避免的，重点在于快速发现和解除
8.0 把锁信息移到了 performance_schema.data_locks 和 data_lock_waits，面试时能提一句表明跟上了版本

5. SQL 优化实战与执行计划

❓ 题目： 一条 SELECT * FROM orders WHERE status = 'paid' ORDER BY create_time DESC LIMIT 10 现在执行很慢，你如何分析和优化？

💡 答案：

首先用 EXPLAIN SELECT ... 看执行计划，关注 type（是否 ALL 全表扫描）、key（是否用了索引）、rows（扫描行数预估）、Extra（看是否有 Using filesort）。如果 status 列和 create_time 列都没有索引，MySQL 需要全表扫描所有订单，再排序取前 10 条，数据量一大就极慢。优化方案：建立复合索引 (status, create_time)，查询先通过索引过滤 status=‘paid’ 的所有行（这些行本身在索引中已经按 create_time 有序），直接取前 10 条即可。如果要进一步优化，结合覆盖索引——如果不需要 SELECT * 而是只查部分列，可以建立 (status, create_time, 需要查询的列) 的复合索引，避免回表。

追问1： 建了索引 (status, create_time) 后，这个查询能完全走索引吗？Extra 里可能会出现什么？

建立 (status, create_time) 后，这个查询理论上性能大幅提升，但 SELECT * 导致必须回表——索引的叶子节点只有 status、create_time 和主键值，需要拿主键回到聚集索引取完整的行。Extra 中会显示 Using index condition（ICP 优化，在索引层过滤 status=‘paid’ 的完整行后再回表）或 Using where。如果建了 (status, create_time, 其他需要查询的列) 的覆盖索引，Extra 会显示 Using index（直接从索引获取所有字段，无需回表）。另外需要注意，如果有 ORDER BY create_time DESC，MySQL 可以利用索引的逆序扫描，Extra 中会显示 Backward index scan（8.0 特性），不需要 Using filesort。

追问2： 如果订单表有几千万行数据，即使走了索引分页到后面还是很慢（比如 LIMIT 1000000, 10），怎么办？

深分页 LIMIT offset, size 当 offset 很大时，MySQL 需要扫描前面的 100 万行数据并丢弃，只保留最后 10 行，前面 100 万行的回表和过滤是白白浪费的。两种经典优化方案：

一是”延迟关联”：先通过覆盖索引定位到目标主键 ID，再回表取完整数据——SELECT * FROM orders o INNER JOIN (SELECT id FROM orders WHERE status='paid' ORDER BY create_time DESC LIMIT 1000000, 10) t ON o.id = t.id。子查询只走覆盖索引 (status, create_time, id)，不需要回表就能定位到 10 个主键 ID，然后再用这 10 个 ID 去主表查完整数据，扫描量极大缩减。
二是”游标分页”：前端将上一页最后一条记录的 create_time 和 id 传回来，查询变为 WHERE status='paid' AND (create_time < last_create_time OR (create_time = last_create_time AND id < last_id)) ORDER BY create_time DESC, id DESC LIMIT 10，充分利用索引，每次只扫描 10 行，页码越深优势越明显。游标分页的代价是不支持随机跳页，需要根据产品形态做取舍。

📌 易错点 / 加分项：

深分页慢的原因不是索引不生效，而是”扫描的行太多了”，即使索引全都覆盖也得扫描 offset 那么多行
Using filesort 不一定慢——排序数据量小时内存排序很快，数据量大时需要磁盘临时文件才慢
延迟关联和游标分页是两个不同方向的优化，前者适合需要跳页但性能要求高的场景，后者适合无限滚动的 Feed 流

6. InnoDB redo log、undo log、binlog 三者关系

❓ 题目： 请说明 InnoDB 中 redo log、undo log、binlog 三者各自的作用，以及一条 UPDATE 语句在它们三者的完整执行流程是怎样的。

💡 答案：

这三者各司其职，组合起来共同实现了 MySQL 的事务 ACID 和主从复制能力：

redo log 是 InnoDB 存储引擎层的”物理日志”，记录的是”在哪个数据页的哪个偏移量做了什么修改”，它的唯一目的是保证事务的持久性（crash-safe）——当 MySQL 崩溃重启后，redo log 能把未写入磁盘的已提交事务数据恢复回来。
undo log 也是 InnoDB 层的，但它是”逻辑日志”，记录的是”修改前的数据版本”，比如 UPDATE 操作在 undo log 中记录该行的旧值。undo log 有两个核心作用：一是事务回滚时根据 undo log 把数据恢复回旧版本；二是 MVCC 通过 undo log 构建历史版本使读操作不加锁。
binlog 是 Server 层的”逻辑日志”，记录的是”执行的 SQL 语句的反向操作信息”，不关心在哪个页上改了哪个字节，它主要用于主从复制和数据恢复——从库通过 binlog 重放主库的操作实现同步。

一条 UPDATE 语句的完整流程：执行器先通过索引找到目标行，如果该行在 buffer pool 中就直接用，否则从磁盘读到 buffer pool。InnoDB 先将原始行数据写入 undo log，记录旧版本。然后修改 buffer pool 中的数据页，将修改操作写入 redo log buffer，同时生成 binlog 写入 binlog cache（每个线程独有）。事务提交时，进入”两阶段提交”：第一阶段 prepare——将 redo log buffer 刷到 redo log 文件并标记为 prepare 状态；第二阶段——写入 binlog 文件（此时 binlog cache 刷到 binlog 文件）；最后将 redo log 中的对应事务标记为 commit 状态。这个两阶段提交保证了两份日志的一致性——无论是崩溃发生在 prepare 之后 binlog 写成功之前还是 binlog 写成功之后，都能根据 redo log 中的标记和 binlog 的内容正确恢复，避免了主从数据不一致。

追问1： 为什么需要两阶段提交？如果不这样做会发生什么？

两阶段提交的核心目的是保证 redo log 和 binlog 在事务提交时的一致性，缺一不可。假设没有两阶段提交，采用”先写 redo log 再写 binlog”的策略——如果在 redo log 写完之后、binlog 写之前崩溃，重启后 redo log 把修改恢复了，但 binlog 没有这条记录，导致主库上数据恢复了而从库没有收到这条 binlog，从库永久少了这条数据。反过来”先写 binlog 再写 redo log”——如果 binlog 写完但 redo log 没写便崩溃，重启后 redo log 没有恢复这条数据，但 binlog 已经同步给了从库，主库数据少了而从库有数据，同样不一致。两阶段提交通过 prepare 阶段和 commit 阶段加上崩溃恢复时的判断逻辑，保证了无论何时崩溃，主库和从库的数据都能走到同一个结果。

追问2： redo log 是循环写的，如果 redo log 写满了怎么办？Checkpoint 机制是如何工作的？

redo log 是固定大小的环形文件组，写满后不能再接受新的写入，MySQL 会暂停所有更新操作直到 checkpoint 推进腾出空间。Checkpoint 的核心逻辑是：redo log 中记录了对数据页的修改，当这些修改的脏页被刷到磁盘后，对应的 redo log 空间就可以被覆盖了。LSN（Log Sequence Number）是 redo log 的全局递增序号，每次写 redo log 都会推进。checkpoint 维护一个 checkpoint LSN，表示该 LSN 之前的所有 redo log 对应的脏页都已刷盘，可以被覆盖。当要写入新的 redo log 但遇到 write pos 追上 checkpoint LSN 时，说明 redo log 满了，InnoDB 会强制推进 flush list（将最早的脏页刷到磁盘），直到 checkpoint LSN 向前推进腾出足够空间。这种现象在生产中表现为数据库突然大量刷脏页、写入 TPS 骤降，需要关注 innodb_log_file_size（单个 redo log 文件大小）和 innodb_log_files_in_group（文件数量）的设置是否合理，设置太小会导致 checkpoint 频繁刷脏页拖慢性能。

📌 易错点 / 加分项：

三个 log 的层级区分——redo log 和 undo log 是 InnoDB 层的，binlog 是 Server 层的，这是必备基础
binlog 有三种格式：STATEMENT（记录 SQL 原文）、ROW（记录每行的变更）、MIXED（混合），ROW 最常用因为精确安全
innodb_flush_log_at_trx_commit=1（每次提交都刷 redo log）和 sync_binlog=1（每次提交都刷 binlog）是保证不丢数据的双参数组合

7. MySQL 主从复制延迟

❓ 题目： MySQL 主从复制延迟（Replication Lag）产生的原因有哪些？如何监控和优化？

💡 答案：

主从复制延迟的本质是从库单线程应用 binlog 的速度跟不上主库多线程并发写入的速度。延迟产生的主要原因可以从三个层面分析：

第一是主库写入量大——主库在高并发下大量事务并行提交，binlog 产生速度极快，而从库 IO 线程可以较快地把 binlog 拉取过来写入 relay log，但从库 SQL 线程是单线程串行回放事务的（MySQL 5.6 之前），速度远跟不上主库的多线程并发写入。
第二是从库硬件弱于主库——磁盘 IO 慢、CPU 核心少，或者从库正在执行较大的查询占用了资源，导致 SQL 线程回放变慢。
第三是网络延迟——跨机房甚至跨地域的主从部署下，binlog 传输本身就有时延。监控延迟主要通过 SHOW SLAVE STATUS 中的 Seconds_Behind_Master 字段，它表示从库 SQL 线程比主库落后多少秒。但这个值只是一个粗略的估计——它是从库当前时间减去 relay log 中最近执行事务的时间戳，如果从库的时钟和主库不一致可能会不准。

优化方案分几个方向：

最直接的是开启并行复制——MySQL 5.6 引入基于库级别的并行复制（不同 database 的事务可以并行回放），MySQL 5.7 引入基于逻辑时钟（Logical Clock）的并行复制（slave_parallel_type=LOGICAL_CLOCK），只要事务在主库上是在同一组提交的（prepare 阶段相互不冲突），从库就可以并行回放，这大大提升了从库的回放吞吐。
另一个方向是升级 MySQL 8.0 的基于 WriteSet 的并行复制——它比 Logical Clock 更激进，分析事务修改了哪些行，只要没有行级冲突就可以并行回放，即使不在同一组提交也可以并行。
除此之外，还可以把读业务拆到多个从库上（一主多从 + 读写分离），减少单台从库的压力——但要注意读业务能容忍多大的延迟。业务侧也可以通过”当延迟超过 N 秒时自动切换到读主库”来兜底。

追问1： 什么情况下 Seconds_Behind_Master 显示为 0 但实际上从库已经严重延迟了？

这种情况常发生在主库写入瞬时暴增然后突然停止的场景。主库在短时间内（比如 5 秒）产生大量 binlog，IO 线程很快拉取完成，但从库 SQL 线程需要花 30 秒才能回放完。此时 Seconds_Behind_Master 的计算方式是”当前时间 - relay log 中最近事务在主库上的时间戳”。如果主库停止写入，这个时间戳不再推进，从库虽然还在慢慢消化积压的 relay log，但这个计算公式不再改变——Seconds_Behind_Master 一直显示为 0，而从库实际上离追上还有几十秒。更准确的监控方法是比较主库和从库的 GTID 或 binlog file+position——看主库当前的 gtid_executed 和从库的差距，或者用 pt-heartbeat 工具（Percona Toolkit）在主库上定期写入心跳时间戳记录，从库上检查心跳时间戳与实际时间的差距，这种方式不受主库写入节奏的影响。

📌 易错点 / 加分项：

MySQL 5.7 的 LOGICAL_CLOCK 和 MySQL 8.0 的 WRITESET 的区别——前者基于事务组 commit 的时间窗口，后者基于行级冲突检测
主从延迟还有一个被忽视的原因是”大事务”——一个大事务的 binlog 写入 relay log 完成后才能开始回放，在整个大事务执行期间从库卡在中间不动
GTID（Global Transaction Identifier）是 MySQL 5.6 引入的全局事务标识，基于 GTID 的主从切换更简单可靠

8. MySQL 分库分表策略与平滑扩容

❓ 题目： 当一个单表数据量达到几千万甚至上亿时，你会如何进行分库分表？设计一个水平拆分方案需要考虑哪些核心问题？

💡 答案：

分库分表的时机通常由几个指标决定：单表行数超过千万级、单表数据量超过几十 GB、数据库的写入 QPS 超过单机能力。水平拆分方案的核心设计包含四个维度：

第一个是分片键的选择——选择一个查询中一定会带的 key 作为分片依据，通常是用户 ID、订单 ID 这类高频查询字段。分片键的选择直接决定了分片后的查询模式：如果按用户 ID 分片，查某个用户的所有订单只需要访问一个分片；但要查某个商品被哪些用户买了，就需要跨所有分片查询。
第二个是分片算法——常用的有取模（key % N）、哈希取模（hash(key) % N）、范围分片（key 在 [0-9999] 到分片 1 等）。取模的优点是分布均匀，缺点是扩容时需要重新哈希导致大量数据迁移；范围分片扩容时只需新增分片存放新范围的数据，但容易造成热点（比如按时间分片，最新时间的数据全部压在一个分片上）。
第三个是数据路由——应用层如何知道一条数据在哪个分片？可以通过配置中心维护分片规则、或者引入 ShardingSphere 这类中间件在 JDBC 层做路由透明化处理。
第四个是全局唯一 ID——分片后不能依赖数据库自增 ID 了，需要雪花算法、号段模式等分布式 ID 方案。

平滑扩容是分库分表最大的工程挑战。以取模分片为例，从 4 个分片扩容到 8 个分片，一半的数据需要迁移——key % 4 != key % 8。业界常见的做法是”双写 + 数据迁移 + 灰度切换”：先做好新的 8 分片集群，然后在应用层对写入做双写（同时写老集群和新集群），后台起数据迁移任务把历史数据按新规则同步到新集群，校验数据一致性后，逐步将读流量切到新集群，最后停止老集群的写入并下线。整个过程要求业务不停机，并且有完整的对账机制保证迁移期间数据不错不少。

追问1： 分库分表后，跨分片的查询、排序、分页怎么处理？

跨分片查询是最棘手的问题。对于带分片键的查询——直接路由到对应分片，性能不受影响。对于不带分片键的查询——需要将请求广播到所有分片，各分片分别执行后聚合结果（Scatter-Gather 模式）。跨分片排序需要在每个分片返回各自的 Top N 后，在中间件层（或应用层）再做一次归并排序，数据量大时内存压力极大。跨分片分页的体验非常糟糕——比如 LIMIT 100, 10 需要从每个分片取前 110 条、汇总 8 × 110 = 880 条、归并排序、再丢掉前 100 条取 10 条，offset 越大越是灾难。解决方案：

一是尽量避免不带分片键的查询——通过 ES 或其它索引系统维护”非分片键到分片键”的映射，先查 ES 锁定分片键，再走分片查询
二是”禁掉不带分片键的查询”——在应用层做规范，查询必须带分片键，不带的走异步流程或报表数据库
三是用”全局索引表”——单独维护一张不按业务分片键分片的索引表（比如按商品 ID 分片，但用 ES 维护商品名到 ID 的反向索引）

📌 易错点 / 加分项：

分库分表不是银弹——只有确定是数据量问题才分，如果是查询逻辑问题应该先优化 SQL/索引
唯一索引在分片后只能在单个分片内唯一，全局唯一需要用分布式 ID + 对账机制来保证
ShardingSphere 的”标准分片”和”强制分片”（Hint 路由）适用于不同场景

9. InnoDB Buffer Pool 工作机制

❓ 题目： InnoDB 的 Buffer Pool 是如何工作的？它的内存管理使用了什么数据结构？为什么 Buffer Pool 的大小对性能有决定性影响？

💡 答案：

Buffer Pool 是 InnoDB 最重要的内存结构，它缓存数据页和索引页，让读写尽量在内存中完成而不用访问磁盘。Buffer Pool 的内存管理基于”链表 + 页面”的结构：它是一片连续的大内存区域，被划分为大小为 16KB 的页（与磁盘上的 InnoDB 数据页大小一致）。三个主要的链表管理这些页——Free List（空闲页链表，存放未被使用的页）、LRU List（存放正在使用的页，按最近使用时间排序）、Flush List（存放被修改过但未刷盘的脏页）。一个新页被读入时从 Free List 取一个空闲页，插入 LRU List；如果 Buffer Pool 满了没有空闲页，从 LRU List 尾部淘汰一个最久未使用的干净页面。

Buffer Pool 大小对性能有决定性影响的原因很简单：InnoDB 的每次读取（包括通过索引查找数据）的目标就是尽可能从 Buffer Pool 命中数据页，避免磁盘随机 IO。如果 Buffer Pool 太小，频繁的页面换入换出让磁盘 IO 成为瓶颈，QPS 会断崖式下跌。通常建议将 innodb_buffer_pool_size 设置为物理内存的 60-80%，在专用数据库服务器上甚至可以到 80% 以上。但需要注意 InnoDB 的 Buffer Pool 对 LRU 做了优化——使用了”Midpoint Insertion 策略”（innodb_old_blocks_pct 默认 37%）。新读进来的页不放在 LRU 头部而是放在中点（靠近尾部 37% 的位置），只有被再次访问后才移到头部。这样做的目的是防止全表扫描等一次性大查询把真正的热数据页挤出 Buffer Pool——全表扫描的页面大多只访问一次就再也不用了，放在中点让它们被更快淘汰。

追问1： Buffer Pool 的脏页刷盘时机是什么？如果刷盘太慢会有什么后果？

脏页刷盘有多个触发时机：

一是”干净页不足”——Free List 空了需要淘汰 LRU 尾部页面，如果尾部是脏页需要先刷盘才能复用。
二是”redo log 空间不足”——redo log 是循环写的，如果 checkpoint 无法推进（因为有对应 redo log 的脏页还没刷盘），必须刷脏页推进 checkpoint。
三是”定时刷脏”——InnoDB 有一个后台线程定期检查，如果脏页比例超过 innodb_max_dirty_pages_pct（默认 75%），触发刷盘直到降到该比例以下。
四是”慢 shutdown”——InnoDB 正常关闭时会把所有脏页刷盘。如果刷盘太慢且持续有大量写入，脏页比例会逼近 100%，最终导致 InnoDB 强制同步刷盘（sharp checkpoint）——所有写入都暂停等待刷盘，数据库表现为间歇性卡顿，日志中可以看到 “Warning: difficult to find free blocks” 这类告警。调优方向是增加 Buffer Pool 大小、提高 IO 能力（SSD 性能更好）、调整 innodb_io_capacity 让刷盘的 IOPS 上限与实际磁盘能力匹配。

📌 易错点 / 加分项：

Buffer Pool 支持多实例（innodb_buffer_pool_instances），多个实例独立管理 LRU/Free/Flush List，减少并发访问的锁竞争
预热 Buffer Pool（innodb_buffer_pool_dump_at_shutdown + innodb_buffer_pool_load_at_startup）让关机时记录哪些页在 buffer 中、开机时重新加载，降低冷启动后的性能抖动
MySQL 8.0 支持动态调整 innodb_buffer_pool_size（SET GLOBAL innodb_buffer_pool_size = ...），不需要重启

10. MySQL count(*) 性能之谜

❓ 题目： SELECT COUNT(*) 和 SELECT COUNT(1) 和 SELECT COUNT(column) 的性能差异是怎样的？为什么大表 COUNT(*) 会很慢？

💡 答案：

先说结论：COUNT(*) 和 COUNT(1) 的性能在 InnoDB 中完全一样，没有任何差别。MySQL 优化器在处理 COUNT(*) 时会把它优化成 COUNT(0)（即统计行数），走的是”计数”语义而非”取值”语义，不会检查每一行的具体列是否有 NULL。COUNT(column) 则不同——它统计的是该列中非 NULL 的行数，MySQL 必须检查每一行的这个列是否为 NULL，所以如果列很大（比如长字符串），性能会略差，但整体差距不大，瓶颈不在这里。

大表 COUNT(*) 慢的核心原因是 InnoDB 不支持单独存储行数——它必须遍历索引统计数据页中的行数。这跟 MyISAM 有根本区别：MyISAM 表维护了一个 meta 行数（因为 MyISAM 不是事务型引擎，不支持并发事务的可见性，表中行数是一个全局确定的值），COUNT(*) 直接 O(1) 返回。但 InnoDB 必须通过 MVCC 支持多版本并发——不同的事务在同一时刻看到的行数可能不同（有些行被其他事务修改了但未提交），所以 InnoDB 没有缓存”总行数”，每个 COUNT(*) 必须通过遍历来处理。优化器会选择最小的索引树来遍历——比如表有主键索引和一个较小的二级索引，COUNT(*) 会走那个二级索引而不是主键索引（因为二级索引叶子节点只存索引值+主键，数据页更少）。但即便如此，大表扫描一个索引树全量也是极慢的。

追问1： 业务中确实需要频繁获取大致行数，这种场景怎么优化？

有几种策略，各有取舍：

一是 EXPLAIN SELECT COUNT(*) FROM t——通过执行计划的 rows 估算值快速获得一个近似值，误差可能达到 20-30%，但毫秒级完成。
二是用 SHOW TABLE STATUS 获取 Rows 列，同样是估算值（基于采样统计），速度快但在频繁写入时极不准确。
三是用 Redis 维护行数计数器——每次插入 INCR、删除 DECR，代价是每次写入都多一次 Redis 操作且需要处理 Redis 挂了的容错和补偿。
四是用一张统计表定期刷新——定时任务每分钟跑 SELECT COUNT(*) 写入统计表，业务查询行数从统计表中读，容忍一分钟的延迟。
五是用 MySQL 8.0 的直方图统计——虽然不会给出精确行数但可用于优化器的基数评估。总之原则是：能接受近似的就不要精确，能用定时同步的就不要实时算。

📌 易错点 / 加分项：

InnoDB 的 COUNT(*) 优化器走最小索引，面试时能说出原理说明理解了为什么”二级索引的叶子节点更小”
SHOW TABLE STATUS 中的 Rows 是采样估算，不是精确值，很多人不知道这点
在 WHERE 条件下 COUNT(*) 和 COUNT(column) 语义不同——前者统计满足条件的行数，后者统计列不为 NULL 的行数

11. MySQL 8.0 关键新特性

❓ 题目： 相比 MySQL 5.7，MySQL 8.0 引入了哪些对开发有直接影响的重要特性？

💡 答案：

MySQL 8.0 带来的几个对日常开发有重大影响的特性：

第一个是降序索引（Descending Index）——5.7 虽然语法上支持 ORDER BY col DESC 但索引本身是升序排列的，优化器需要”逆序扫描”或者额外做 filesort。8.0 真正支持了降序索引，INDEX (a ASC, b DESC) 创建的索引中 b 列就是降序存储，ORDER BY a ASC, b DESC 可以直接利用索引而无需额外排序。对于带排序的业务查询效果显著。
第二个是不可见索引（Invisible Index）——ALTER TABLE t ALTER INDEX idx_name INVISIBLE 让索引对优化器不可见但索引本身仍在维护。这解决了生产环境”删索引”的高风险问题——以前只能直接 DROP，删错了就得重建，8.0 可以先 invisible 观察一段时间确认没有性能退化再真正删除。
第三个是CTE（Common Table Expression）和递归 CTE——WITH cte AS (SELECT ...) SELECT ... FROM cte，让复杂查询可以拆分为可读的子块，递归 CTE 可以查询树形结构（比如组织架构递归查所有下级）。
第四个是窗口函数——ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) 这种在之前只能通过复杂的子查询或变量实现，8.0 原生支持了。
第五个是原子 DDL——ALTER TABLE 在 8.0 中是原子操作了，不再出现”重建到一半宕机、留下中间状态文件”的问题。
第六个是默认编码改为 utf8mb4——5.7 默认还是 latin1，8.0 的 utf8mb4 终于成了真正的 UTF-8 完整支持（包含 emoji 等 4 字节字符），无需手动配置。

追问1： 窗口函数解决了什么样的实际问题？举个日常场景。

窗口函数最典型的实用场景是”分组取每组前 N 条”和”累加统计”。比如”查询每个部门薪资最高的 3 个员工”，5.7 的做法是用子查询 SET @row_num = 0; SET @prev_dept = NULL; 然后在外层过滤 row_num <= 3——这个 SQL 极其复杂且需要维护用户变量。8.0 中一行窗口函数就能搞定：SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees) t WHERE rn <= 3。另一个场景是”计算每天累计销售额”——SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING)，非常适合做报表。窗口函数不改变结果行数，只在每行加一个计算列，这跟 GROUP BY 的本质区别是——GROUP BY 把多行折叠为一行，窗口函数保留所有行。

📌 易错点 / 加分项：

CTE 和子查询的区别——CTE 只是语法糖让 SQL 更可读，但有递归 CTE 这个子查询做不到的能力
8.0 移除了查询缓存（Query Cache）——因为它在高并发场景下失效过快反而成为性能瓶颈
EXPLAIN ANALYZE（8.0.18+）可以显示查询实际执行时间分布，比传统 EXPLAIN 更精确

12. MySQL 索引失效的典型场景

❓ 题目： 什么情况下 MySQL 虽然建了索引但不会使用它？列举实际生产中常见的索引失效场景。

💡 答案：

索引失效最常见的几个场景：

第一是最左前缀原则被破坏——联合索引 (a, b, c)，查询条件跳过 a 直接查 b = 1 AND c = 2，索引完全用不上。如果只查 a = 1 AND c = 2，则只能用到 a 这列，c 因为跳过了 b 而无法使用。
第二是索引列上使用了函数或运算——WHERE DATE(create_time) = '2026-01-01' 索引用不上，因为 MySQL 不知道 DATE(create_time) 的结果和索引 key 的映射关系。正确写法是 WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02'。常见误区包括 WHERE id + 1 = 10（应改为 id = 9）、WHERE LEFT(name, 3) = 'abc'（应改为 name LIKE 'abc%'）。
第三是隐式类型转换——WHERE phone = 13800001111 而 phone 列是 varchar 类型，MySQL 会将列转为数字再比较（应用函数在列上），索引失效。反过来如果列是 int、值是字符串 WHERE id = '123' 则一般不会失效（优化器会把字符串转为数字）。
第四是不等于和 NOT IN——WHERE status != 0 一般不走索引，因为”不等于”意味着需要扫描大部分数据，优化器认为全表扫描更快。但如果 status != 0 的数据量占比极小（比如 1%），可以用 FORCE INDEX 强制走索引。
第五是LIKE 以通配符开头——WHERE name LIKE '%abc' 不能走索引，因为 B+ 树的排序是从第一个字符开始的，第一个字符不确定就没法在树上定位。LIKE 'abc%' 可以走索引。
第六是OR 条件——WHERE a = 1 OR b = 2，如果 a 有索引 b 没有，优化器可能选择全表扫描。但如果两个条件涉及的列各有索引，8.0 引入了 Index Merge 优化，能把两个索引的结果做并集或交集。

追问1： 为什么有时候统计信息准确、查询条件也符合最左前缀，优化器还是选了全表扫描？怎么让它走索引？

这是优化器的”成本估算”问题。优化器通过统计信息（innodb_stats_persistent）估算走索引的成本和走全表扫描的成本，然后选择成本低的。如果索引区分度太低（比如一个 status 列只有三个值 0/1/2），优化器估算使用这个索引需要回表的行数接近全表行数，那全表扫描反而更快——因为顺序读比索引回表的随机读快。另一个原因是数据倾斜——统计信息可能平均分布但实际数据极度倾斜（比如某个 status 值占 99% 的行），这种情况下对占 99% 的值走索引是很慢的。解决方案：

一是用 ANALYZE TABLE 更新统计信息
二是用 FORCE INDEX 强制走索引（但要确认这样是真的更快）
三是用直方图（8.0 的 ANALYZE TABLE ... UPDATE HISTOGRAM）来捕获数据倾斜，让优化器做更精确的成本估算
另一种策略是”不要强求走索引”——优化器选全表扫描可能是对的，强制走索引反而更慢

📌 易错点 / 加分项：

隐式类型转换的方向很重要——字符串列被比作整数时索引失效（列上做转换），整数列被比作字符串一般没事
联合索引中范围查询之后的列不能用——a = 1 AND b > 10 AND c = 3，c 无法走索引
MySQL 8.0 的 EXPLAIN FORMAT=TREE 可以看到优化器选择 JOIN 顺序和索引的判断过程

13. MySQL 表设计最佳实践

❓ 题目： 在 MySQL 表结构设计时，你遵循哪些原则？主键、字段类型、范式选择上分别有什么考量？

💡 答案：

表结构设计是数据库最基础也最重要的决策，一旦上线后修改代价极大。主键设计上，强烈建议用自增整型主键——自增保证数据顺序追加到 B+ 树最右侧，页分裂最少且数据页填充率高。避免用 UUID 或业务主键——UUID 的随机性导致频繁页分裂和磁盘碎片，字符串主键会让所有二级索引的叶子节点膨胀（因为二级索引叶子存的是主键值）。如果业务上需要全局唯一标识，用分布式 ID 方案生成趋势递增的数值型主键。如果一定要 UUID，至少用 UUID_TO_BIN(uuid(), 1) 将 UUID 转为有序的 binary 存储（8.0+）。

字段类型设计上，核心原则是”最小且最合适的类型”。整型能用 TINYINT（1 字节）不选 INT（4 字节）——状态标识、布尔值、类型枚举等用 TINYINT 足够了。字符串能用 CHAR(N) 不选 VARCHAR(N) 的场景是”长度固定且短”的值——比如手机号、MD5 哈希、身份证号等，CHAR 存储效率更高。能用 DECIMAL 不选 FLOAT/DOUBLE 的场景是”金额”——浮点数有精度丢失风险，DECIMAL 用定点存储不会丢精度。对于”不涉及计算且值有限”的字段（如状态、类型），可以选 ENUM 或 TINYINT + 映射表，ENUM 的优点是存储紧凑（1-2 字节）且可读性好，但修改枚举值需要 ALTER TABLE 且依赖程序端维护值字符串。

范式选择上，OLTP 系统一般遵循 3NF——消除数据冗余，保证数据一致性（一处修改、处处生效）。但实际业务中不是越规范越好——有时候需要”反范式”来换查询性能。比如订单表里存了”商品名称”和”商品单价”，按范式这些应该从商品表中 JOIN 获取，但订单一旦生成后商品信息不应该随商品表变更而变化（你需要的是”下单那一刻的快照”），所以冗余存储反而是正确做法。另外查询频繁的维度表字段适当冗余到主表可以避免 JOIN、提升性能，代价是多占用一些存储空间且更新时需要同步多处。

追问1： VARCHAR(255) 这个数字有什么特别的意义吗？为什么很多表里都看到？

255 不是随意取的——它和 InnoDB 的存储实现有关。VARCHAR 在 InnoDB 中的存储格式是：实际数据内容 + 1 或 2 个字节的长度前缀。当 VARCHAR 的最大长度 ≤ 255 字节时，长度前缀用 1 字节存储；超过 255 字节时用 2 字节。另外由于 InnoDB 中一个索引 key 的最大长度是 767 字节（innodb_large_prefix=0 时）或 3072 字节（innodb_large_prefix=1 或 MySQL 8.0 默认），VARCHAR(255) 在 utf8mb4 下实际需要 255 × 4 + 1 = 1021 字节，在这个限制之内。所以 255 是一个在存储成本和长度前缀开销之间都不错的”适中值”。但实际设计中不应该机械地用 255——应该根据字段的实际最大长度来设，一个”收货地址”可能需要 500 甚至更长，“用户名”可能 50 就够了。

📌 易错点 / 加分项：

CHAR(N) 不管实际存了多少字符始终占用 N × 字符集宽度的空间——适合长度固定的值
utf8mb4 一个字符占 1-4 个字节——索引限制计算时要乘以 4 字节
TEXT/BLOB 列不能有默认值——且在使用临时表排序时会导致写磁盘临时表（8.0 的 TempTable 引擎有改善）

14. MySQL 连接池参数调优

❓ 题目： 一个 Spring Boot 应用的 MySQL 连接池参数如何设置？maximumPoolSize 是不是越大越好？

💡 答案：

连接池的最优配置和物理 CPU 核心数、数据库处理能力、业务查询模式三者相关。maximumPoolSize 不是越大越好——MySQL 的每个连接都是一个独立线程，当活跃连接数超过 CPU 核心数时，过多的线程上下文切换会让数据库的吞吐量不升反降。一个广泛使用的经验公式是：连接数 = ((CPU 核心数 × 2) + 有效磁盘数)，比如 8 核 CPU + 1 个 SSD，初始可以设为 16-18 个连接。但这个公式来自传统的磁盘为瓶颈的时代，现在的 SSD 和 NVMe 磁盘 IOPS 极高，真正实践中往往从 20-30 个连接开始做压力测试、观察 TPS 和响应时间，找到”增加连接数 TPS 不再上升、95% 响应时间开始恶化”的拐点。

HikariCP 的几个关键参数：

maximumPoolSize（最大连接数，默认 10 比较保守，生产通常 20-50）
minimumIdle（最小空闲连接数，最好和 max 一样，避免连接生命周期管理开销）
connectionTimeout（等待连接的超时时间，默认 30 秒太长，通常设 1-3 秒）
idleTimeout（连接空闲多久被释放，默认 10 分钟）
maxLifetime（连接最大存活时间，必须比 MySQL 的 wait_timeout 短 2-3 分钟，否则池中连接可能被 MySQL 关闭导致使用时报错）
还有一个重要配置是 leakDetectionThreshold——超过这个时间连接还没返回池中，HikariCP 会打印日志告警，这是排查”连接泄漏”的好工具

追问1： 线上突然大量爆出 “Cannot acquire connection from pool” 错误，怎么排查？

这个错误的直接含义是连接池中所有连接都在使用且超时还没等到空闲连接。排查分两个方向：

一是”连接真的不够用”——连接池太小、并发请求太多。先看 HikariCP 的 metrics（hikaricp_connections_active、hikaricp_connections_pending），如果 active 一直等于 max、pending 一直很高，说明池确实不够用，需要调大 maximumPoolSize 或优化慢查询减少连接占用时间。
二是”连接泄漏”——代码中获取了 Connection 但没在 finally 中关闭，连接一直被占用不归还。打开 HikariCP 的 leakDetectionThreshold=10000（10 秒），如果某个线程持有连接超过 10 秒没还，日志中会打印线程栈，直接定位到是哪个方法占着连接不放。另外 MySQL 侧也要同步检查——SHOW PROCESSLIST 看是否有大量 Sleep 状态的连接（说明连接没被利用）或者长时间 Running 的连接（说明 SQL 很慢）。

📌 易错点 / 加分项：

HikariCP 的默认 maximumPoolSize=10 对大多数生产系统太小了，但盲目调到 200 会让数据库线程爆炸
connectionTestQuery 在 HikariCP 中不需要手动设置——它会自动用 isValid() JDBC 方法检测连接有效性
Druid 和 HikariCP 的对比——HikariCP 性能更好，Druid 的监控功能更丰富

15. MySQL 分区表与适用场景

❓ 题目： MySQL 的表分区（Partitioning）是什么？它和分库分表有什么区别？什么场景下推荐用分区表？

💡 答案：

表分区是 MySQL 的内建功能，将一张逻辑表的物理存储拆分为多个独立的分区文件，每个分区可以有不同的存储路径。分区对应用层是透明的——你的 SQL 不需要关心数据在哪个分区，MySQL 通过分区键自动做”分区裁剪”（只扫描相关分区）。MySQL 支持按范围（RANGE）、列表（LIST）、哈希（HASH）、KEY 等分区类型。分区和分库分表的核心区别有几点：

一是透明性——分区对应用完全透明、无需修改 SQL；分库分表需要应用感知路由逻辑。
二是跨分区查询——分区表的跨分区查询由 MySQL 自己处理，分库分表的跨分片查询需要在中间件或应用层做结果聚合。
三是扩展性——分区不能跨数据库实例，始终在单机内；分库分表可以扩展到多台机器上。
四是运维——分区可以通过 REORGANIZE PARTITION 在线调整，分库分表的扩容需要数据搬迁。

分区表最适合的场景有几种：

第一是按时间范围分区的”日志/事件类表”——比如订单表按月分区，WHERE create_time >= '2026-01-01' 只会扫描 1 月分区。清理过期数据极方便：ALTER TABLE orders DROP PARTITION p202501 是 DDL 操作瞬间删除，相比 DELETE FROM orders WHERE create_time < '2025-02-01' 这种百万行 delete（产生大量 undo log、持锁时间长），分区删除是 O(1) 级别的。
第二是”冷热数据分离”——历史分区放在 HDD 上、最近分区放在 SSD 上。
第三是”大表归档”——保留最近几个月的分区在主库，旧分区可以 exchange 到归档表再移到离线存储。但分区表也有明显的坑：分区键必须是主键或唯一索引的一部分；分区数量不宜过多（几百个就很多了，几千个会严重影响性能）；跨分区的唯一约束无法保证（比如按时间分区，无法保证 ID 在所有分区中不重复——除非把 ID 也加入分区键）。

追问1： 什么情况下分区表会让性能变得更差而不是更好？

最常见的是”查询不带分区键”。比如按 create_time 分区，但查询条件是 WHERE user_id = 123，MySQL 必须扫描所有分区，这比扫描一张不分区的普通表更慢——每个分区的打开、索引读取、结果合并都有额外开销。
另一个是”分区数量过多带来元数据膨胀”——每个分区都是独立的物理文件，打开表时需要加载所有分区的元数据，几百上千个分区时 OPEN TABLE 操作本身就很慢。
还有就是”跨分区排序和分页”——ORDER BY ... LIMIT 10 如果涉及多个分区，MySQL 需要从每个分区取前 10、归并、再取最终 10 条，这个”归并”操作比单表直接取前 10 慢很多。

📌 易错点 / 加分项：

MySQL 8.0 的 ALGORITHM=INSTANT 只对 ADD COLUMN 等操作有效，分区操作通常需要 ALGORITHM=INPLACE
分区表的 INSERT 性能通常和普通表一样——因为分区裁剪是查询时生效，插入时只需要判断分区键写入对应分区
分区表不支持外键、不支持全文索引——这是很多人踩过的坑