MySQL

SQL基础#

数据库 (Database)：是表的集合

MySQL架构#

可以分为 server层和存储引擎

首先是client与连接器建立TCP连接 管理链接 进行用户认证

然后c端发送的sql 会先作为key在查询缓存里面查 但是这个缓存记录在8.0版本后就删去了

接着sql会经过server层的解析器进行词法分析语法分析 生成语法树

语法无误便会送到预处理器提前判断查询的字段中表中是否存在、* 替换成所有字段 优化器会选择合适的index 根据CBO模型选择查询成本最小的执行计划 执行器根据执行计划执行sql语句 从存储引擎读取记录返回给客户端

存储引擎!#

MySQL 存储引擎采用的是 插件式架构 ，支持多种的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库

MyISAM（有较低的存储空间和内存消耗，适用于大量读操作的场景）、MEMORY（数据全在内存，读写快，安全性低，对表大小有要求）

与MyISAM的三大区别，也是InnoDB的三大特点：

• 三个引擎中唯一一个支持事务的
• 支持行锁 并发性能好
• 外键约束

常用InnoDB的原因是支持事务，且最小锁的粒度是行级锁，redolog可以崩溃恢复

InnoDB存储引擎架构#

逻辑存储结构#

表空间 段 区 默认16KB数据页 row

InnoDB 的数据是按「数据页」为单位来读写的 B+ 树的每一个节点，在物理层面上严格对应着一个 16KB 的页，只不过存放的内容如果是非叶子节点仅用来存放目录项作为索引

页内部查找#

数据页内包含用户记录，每个记录之间用单向链表的方式组织起来，为了加快在数据页内高效查询记录，设计了一个页目录，页目录存储各个槽（分组），且主键值是有序的，于是可以通过二分查找法的方式进行检索从而提高效率。

数据页中有一个页目录，起到对用户记录的索引作用，

利用页目录查找，本将 长的单链表 $O(N)$ 遍历，转化为了 数组二分查找 $O(\log M)$ M是slot的数量+ 极短链表遍历 $O(1)$。

内存架构 Buffer Pool#

Buffer Pool Change buffer 专门用来缓存对“非唯一二级索引页”的修改操作 针对非唯一 二级索引 页(允许索引列存在重复的值) 如果DML数据不在buffer pool中 不会直接操作磁盘 先将变更存到change buffer中 在未来数据被读取时再合并 合并后再落盘

对于聚簇索引 / 唯一二级索引 当数据页不在Buffer Pool中必须立即从磁盘加载 因为 要保证数据的绝对一致性和唯一性

log buffer 自适应哈希索引

后台线程架构#

负责将数据从内存写入磁盘（落盘）

• Master Thread: 这是 InnoDB 最核心的后台线程。它有一个主循环，会以一定的频率（通常是每秒或每十秒）将 Buffer Pool 中的一部分脏页刷新到磁盘。这是最主要的刷新方式之一。

• LRU List Cleaner Thread (在较新的版本中，从 Master Thread 中分离出来): 当 Buffer Pool 的空闲空间不足，需要腾出位置给新的数据页时，这个线程会从 LRU 列表（最近最少使用）的尾部找到一些可以被淘汰的页。如果这些页是“脏页”，就必须先将它们刷新到磁盘，然后才能被重用。

• Page Cleaner Thread (从 InnoDB 1.2.x 版本开始引入): 为了进一步提升性能和扩展性，脏页的刷新工作被专门的 Page Cleaner 线程接管。它可以更智能、更高效地管理脏页的刷新，减轻 Master Thread 的负担。

• Redo Log 写满时: InnoDB 为了保证事务的持久性（ACID 中的 D），会先将修改操作写入 Redo Log。如果 Redo Log 快要写满了，系统会强制触发一次 Checkpoint，将相关的脏页从 Buffer Pool 刷新到磁盘，以便释放 Redo Log 的空间。

索引!#

是什么和优缺点#

索引本质上是mysql为了减少磁盘 I/O 次数而设计的数据结构，其本质可以看成是一种排序好的数据结构； 在 InnoDB 中主要采用 B+ 树实现

优点：

1. 加速查询 减少io
2. 保证数据唯一性
3. 加速排序和分组

缺点：

1. 维护成本：创建索引本身需要时间，特别是对大表操作时。尤其增、删、改 DML语句需要维护索引
2. 占用存储空间
3. 误用或失效

索引越多越好吗#

不是越多越好，不论是从空间、时间、优化器选择、维护成本来说都不是越多越好。

建议 单张表索引不超过 5 个！

什么字段适用索引？#

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

索引结构#

**创建的主键索引和二级索引默认使用的是 B+Tree **

• 为何采用B+树而非其他结构？ Hash 表不适合做范围查询，关系型数据库有大量的 >、<、BETWEEN 以及 ORDER BY 操作，哈希表在这些场景下只能退化为全表扫描

红黑树 ，AVL，bst都是二叉的，随着插入的元素增多，而导致树的高度变高，导致磁盘 I/O 次数过多（索引存放在磁盘上，操作系统在读取数据时按“页”为单位加载，树的每一层通常对应一次随机磁盘 I/O）

B+Tree vs B Tree#

1. 首先 B+树非叶子节点不存储数据，仅放索引（索引键和页指针），因此读取同样一数据页（16KB）的情况下，相比B树能路由的节点多很多，就使得整个树更加矮胖，减少了磁盘IO查询次数
2. 其次 B+树的叶子节点用双向链表连接，有利于范围查询，顺序 I/O；而b树则需要从根节点遍历，就涉及到多次IO查询（当然你可以中序遍历线索化b树，但是工程上非常麻烦，B+ 树之所以能做到，是因为它用“牺牲非叶子节点存储冗余索引键”的代价，换来了“底层叶子节点全量数据集合”的物理特性。）
3. 最后 B+树的冗余节点（所有非叶子节点都是冗余索引）的特性使得增删操作对 树结构的变化调整没有b树那么频繁

为什么树高和io次数成正比: 因为每一次顺着指针向下一层访问子节点，都意味着必须从磁盘加载一个全新的“数据页”到内存中

索引分类#

按照「数据结构」维度划分：B+、hash（innoDB没有）、fulltext 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。 按照「字段特性」：主键索引、唯一索引（UNIQUE，能让优化器更早停止查找）、普通索引、前缀索引 按「字段个数」分类：单列索引、联合索引。

创建的聚簇索引和二级索引默认使用的是 B+Tree 索引。

底层存储方式角度划分：#

聚簇索引/主键索引：#

索引结构和数据一起存放的索引，必须有且只能有一个

存在性保证：如果有主键则主键索引是聚簇索引，不存在主键就用第一个不包含 NULL 值唯一列作为索引，还没有的话InnoDB自动创建一个隐藏的6Byte 的自增主键rowid

二级索引/辅助索引 ：#

索引结构和数据分开存放的索引，更新代价比聚簇索引要小

叶子节点内容不是整行数据，而是主键值

故需要回表查询 除非它是覆盖索引

覆盖索引和回表#

索引结构已经包含sql所有需要查询的字段的值，无需回表

按照「字段个数」分类：#

单列索引#

联合索引#

多个字段上建立的索引，就是 联合索引

最左前缀匹配原则#

在使用联合索引时，查询条件必须从该索引定义的最左边的列开始，并且中间不能跳过任何列，否则索引将部分失效（失效指的是失去了这个字段二分查找的能力）

注意⚠️：WHERE子句中条件的顺序通常不影响结果，因为查询优化器会自动调整

e.g 联合索引a,b,c where a = x and c = y，则c索引失效，但 MySQL 5.6+ 会使用索引下推（ICP）在存储引擎层过滤 c = 3，减少回表次数。

• 联合索引范围查询（等值在前范围后）： 最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配

失效原因：

利用索引的前提是索引里的 key 是有序的！ 而联合索引的 B+Tree 是先按a 进行排序，然后再a相同的情况再按b字段排序，即a为全局有序，b为局部有序 ^5b7975

如果没有覆盖索引情况 联合索引和单列索引哪个性能好？

联合索引优势：在多条件查询（如WHERE a AND b）中更好，能一次性过滤多个列，减少回表行数和I/O开销；也适合范围查询或排序。 单列索引优势：在单一条件查询中性能相当或略优，更灵活，占用空间少；但多条件时可能需索引合并，效率较低。 尽量用联合索引

索引下推：#

存储引擎在二级索引遍历过程中，执行部分 WHERE 字句的判断条件，提前过滤掉不满足条件的记录，减少去主键树查完整数据的回表

出现了 Extra 为 Using index condition 就是用到了

前缀索引#

前缀索引是指对字符类型字段的前几个字符建立的索引 仅限于字符串类型，较普通索引会占用更小的空间

索引失效#

1. 联合索引不满足最左前缀匹配原则

^5b7975

2. 在索引列上进行表达式计算、函数、类型转换等操作

索引保存的是索引字段的原始值，只能通过把索引字段的取值都取出来。但是新版本可以生成函数处理后的索引

索引隐式类型转换——字符串不加引号 MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较 索引列字段被Mysql cast了 回到情况2

3. 索引头部模糊匹配

左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式失效 原因：B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较，

4. 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到

全表扫描了 用啥索引 解决方法可以是创建另一条件的索引 然后MySQL会对结果集进行合并

5. mysql优化器评估使用索引比全盘扫描慢

左、变、模、或、估

索引设计原则#

防止索引失效

选择合适的列：不为 NULL 的字段 区分度高的列 where查的多的数据 被频繁更新的字段应该慎重建立索引

利用覆盖索引，设置合适的联合索引

控制索引数量

事务 ！#

事务是逻辑上的一组操作，要么都执行，要么都不执行

事务有哪些特性？ ACID#

• 原子性（Atomicity）：事务是数据库的逻辑工作单位，事务里面的操作，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态

一个事务的执行不能破坏数据库数据的完整性和业务规则。在事务开始之前和事务结束以后，数据库都必须处于一个“合法”的状态

• 隔离性（Isolation）：事务在不受外部并发操作影响的独立环境下运行。多个并发事务之间相互隔离。（不同隔离级别）
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的，不会说因为宕机什么的就没了

MySQL 默认是隐式提交 当出现 START TRANSACTION 语句时，会关闭隐式提交； 当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过 set autocommit=0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。

事务ACID原理 实现机制#

事务的 ACID 分别靠什么机制保证？ 事务保证

• 原子性是通过 undo log（回滚日志） 来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证；
• 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
• 持久性是通过 redo log （重做日志）来保证的；

并行事务和隔离级别 （隔离性相关）#

并发事务带来的问题：

1. 脏读：读到其他事务未提交的数据；
2. 不可重复读：前后读取的数据不一致；
3. 幻读：前后读取的记录数量不一致。

为解决并发问题，SQL 提出了四种隔离级别，隔离级别越高，意味着性能越差

- **读未提交（_read uncommitted_）**，指一个事务还没提交时，它做的变更就能被其他事务看到；
- **读提交（_read committed_）**，指一个事务提交之后，它做的变更才能被其他事务看到；
- **可重复读（_repeatable read_）**，一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，**MySQL InnoDB 引擎的默认隔离级别**；
- **串行化（_serializable_ ）**；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

读已提交 RC 和可重复读 RR 的区别？ 定义的不同 实现方式不同，即创建 Read View 的时机不同

Mysql 默认隔离级别是 可重复读RR 但是它很大程度上避免幻读现象

四种隔离级别分别如何实现的 ？#

1， “读未提交” 级别的事务 直接读取最新的数据就好了 2， “读已提交和可重复读（RR） ” 级别的事务 通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同* 「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View 3， “串行化” 级别的事务 加读写行锁的方式来避免并行访问

MVCC/多版本并发控制#

MVCC 是一种并发控制的方法，它通过undo log维护数据的版本链，并通过一套可见性规则也就是Read View让事务只能读到它应该看到的那个历史版本

MVCC 和 ReadView 的关系？ 简单来说，MVCC是 InnoDB 实现高并发无锁读取的宏观机制，而 ReadView是这套机制中用来进行‘可见性判断’的核心数据结构。 它们是整体与局部的关系，ReadView 配合隐藏字段和 Undo Log 版本链，共同构成了 MVCC 的完整实现。

MVCC实现原理：

锁也能实现隔离 但为了极致的并发性能把 MVCC 作为其并发控制的核心机制 在需要“当前读”等强一致性场景下，依然会使用锁作为 MVCC 的补充

RR隔离级别下，readview和锁机制如何减少幻读的发生的？MySQL 如何解决幻读问题？#

第一个，快照读（普通的 SELECT）：依靠 MVCC 和 ReadView 事务在首次查询时生成全局 ReadView，并在整个事务周期内复用。如果有其他事务插入了新数据，因为readview会屏蔽其他事务的改动，当前事务也只能看到历史快照，所以新数据不可见，避免了幻读。

第二个点，当前读（所有涉及数据修改的操作 DML，以及显式加锁的查询）：依靠临键锁（Next-Key Lock）物理阻塞。 执行当前读会在查询范围内加上临键锁，不仅锁住存在的记录，还会锁住记录之间的间隙。这会直接阻塞其他事务在这个范围内的 INSERT 操作，从物理上阻止了幻影行的产生。也会阻止UPDATE 和 DELETE（防并发篡改）

例外，快照读与当前读混用会导致幻读。 如果事务 A 先执行快照读，此时事务 B 插入了一条新记录并提交。接着事务 A 执行了一次范围 UPDATE（当前读），恰好把事务 B 刚插入的记录也更新了。这会导致该记录的隐藏字段的事务 ID 变成事务 A 自己。当事务 A 再次执行快照读时，根据 MVCC‘自己修改的数据对自身可见’的规则，这条原本看不见的幻影行就会出现，真正的幻读就发生了。”

每行record的两个隐藏字段：

• trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

锁!#

全局锁#

全库的逻辑备份使用 导致业务停滞

避免加全局锁方案 备份时使用 --single-transaction 参数一致性数据备份

表级锁#

• 表锁： 共享读锁 (read) 独占写锁(write)

• 元数据锁 MySQL自动强制加的，防止其他线程改动表结构

• 意向锁：当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

意向锁是为了提高加表锁的效率。如果没有意向锁，想加表锁时需要逐行检查是否有行锁，开销巨大。有了意向锁，只需检查表级别的意向锁即可，大大降低了检测开销。

行级锁！#

MyISAM 引擎并不支持行级锁,这是innoDB的特点之一，也是优势所在（粒度小 提高了并发度）

• 记录锁，锁住的是一条记录。 有 S 锁/读锁 和 X锁/写锁 之分，满足读写互斥，写写互斥，读读共享

锁相容矩阵：

当读操作是锁定读时，才会受X锁影响，普通读（非锁定读）是不会对记录加锁的，因为它属于快照读，除非在串行化隔离级别下

什么时候加 X 锁（Exclusive Lock，排他锁）？

1. SELECT … FOR UPDATE 应用场景： 高并发下的“悲观锁”设计。例如电商扣减库存，先用 FOR UPDATE 锁住该商品行，防止“超卖”。
2. 数据变更语句（DML）自动加锁

不通过索引改数据会导致行锁升级为表锁： InnoDB 的行锁是加在索引上的。如果一条 UPDATE 或 DELETE 语句的 WHERE 条件没有走索引，MySQL 就无法通过索引快速定位记录。为了保证事务隔离性，系统不得不对聚簇索引（主键索引）中的所有记录以及它们之间的间隙全部加上锁。

• 间隙锁 是什么？为什么需要它？怎么工作的？ 间隙锁锁定的是索引记录之间的 “间隙”，用于防止幻读。在 RR（可重复读）隔离级别下，间隙锁 + 记录锁组成 Next-Key Lock，确保同一事务多次查询结果一致。

• Next-Key Lock 称为临键锁 Record Lock + Gap Lock 的两者组合，锁定一个范围，并且锁定记录本身 临键锁的触发时机： 执行修改语句或锁定读时，InnoDB会在扫描的索引记录上加记录锁（Record Lock），并在记录间的间隙上加间隙锁（Gap Lock），组合形成临键锁

当事务执行 commit 后，事务过程中生成的锁都会被释放

什么是死锁？怎么解决/预防？#

数据库死锁是多个事务在执行过程中，因竞争锁资源而造成相互等待的现象，除非外力干预打破这种循环等待，否则的话这些事务都无法继续执行

我举个例子：“事务 A 获取了记录 1 的 X 锁，想要去更新记录 2； 与此同时，事务 B 获取了记录 2 的 X 锁，想要去更新记录 1。 这个时候，事务 A 等待 B 释放记录 2 的锁，事务 B 等待 A 释放记录 1 的锁，形成闭环，死锁就产生了。”

InnoDB 是如何处理死锁的？ 主动检测死锁，事务等待图是否存在环，将主动回滚 undo 量最小的那个事务 超时机制（被动兜底）默认50s

解决方案：

1. 统一加锁顺序，比如在批量处理多个用户的资金转账时，强制要求代码先按用户 ID 进行排序，然后再依次执行 UPDATE，这样就能彻底避免交叉等待。
2. 小事务减锁持有时间，
3. 加索引减少锁范围（尽量让查询条件走唯一索引或主键索引，确保加的是记录锁），
4. 开检测做兜底

SQL优化!#

核心思路：先定位 再分析 后优化

MySQL中如何定位慢查询？如果SQL语句执行很慢，如何分析和优化？#

EXPLAIN 怎么分析执行计划、一个事务中有多条慢语句怎么排查？

定位慢查询：#

首先得开启慢查询日志。而且要设置合适的long_query_time阈值，默认 10 秒，我们生产环境设置的2秒 然后可以使用 mysqldumpslow 工具分析慢查询日志，按执行时间、执行次数排序，快速锁定最需要优化的 SQL

必要时，我也会结合 SHOW PROFILE （虽然已弃用）查看这条SQL在各个执行阶段的具体耗时分布

分析：#

拿到具体的慢SQL后，我会使用 EXPLAIN 关键字来深入分析它的执行计划。在这里，我主要会盯住四个核心字段：

• type (连接类型)： 这是看它到底扫描了多少数据的关键。我要求至少要达到 range（范围扫描）甚至 ref（非唯一索引等值查询） 级别，绝对要避免 ALL（全表扫描）。
• key 和 key_len： 确认它实际命中了哪个索引，如果是NULL要特别注意，表示没有走索引；以及联合索引到底被利用了多长，有没有发生部分失效。
• rows： 看 MySQL 预估需要扫描的行数，这个值肯定是越小越好，特别大证明过滤条件不生效
• Extra (额外信息)： 这是索引优化的线索。如果看到 Using filesort（文件排序）或者 Using temporary（临时表），这就是高优要解决的性能瓶颈；而如果能看到 Using index（覆盖索引），说明性能是非常理想的。

优化#

索引优化

• 建立合适索引：避免 ALL（全表扫描）；灭 filesort 与 temporary，给 ORDER BY 和 GROUP BY 涉及的字段加联合索引
• 防止索引失效

SQL语句优化

• 避免 SELECT *，只查必需字段，尽可能通过覆盖索引减少回表
• 优化深分页查询

检查锁竞争：使用SHOW PROCESSLIST查看是否有锁等待。

架构优化#

最后，我认为SQL优化并不是盲目加索引，因为索引本身也会消耗资源并影响写入性能。如果SQL优化已经做到了极致，但因为单表数据量过大（比如达到千万级），查询依然存在瓶颈，我就会考虑架构层面的方案。如果在逻辑层优化到了极致，但单表数据量已达千万级或遭遇高并发，我会果断升级架构，而不是盲目加索引（因为会拖垮写入性能）：

• 引入缓存层（首选）： 优先使用 Redis 对高频且允许一定延迟的热点数据做流量拦截，挡住大部分读请求。
• 读写分离与分库分表： 当流量或存储真正触达 MySQL 物理极限时，再考虑落地主从读写分离，或者进行分库分表及冷热历史数据的归档。

SQL语句优化#

• 查询读场景（DQL 优化）：

  • 规避回表： 坚决避免无脑 SELECT *，尽量只查必要的字段，利用覆盖索引在二级索引树上直接返回结果。
  • 排序与聚合： 对于 ORDER BY 和 GROUP BY，尽量让排序字段也走索引。如果不可避免出现了 Using filesort，我会视情况适当调大 sort_buffer_size（排序缓冲区）。
  • 深分页问题： 这是高频痛点。LIMIT offset, size 在 offset 极大时会扫描大量无效数据。我通常会用子查询覆盖索引定位 ID，或者在业务允许的场景下采用游标（last_id）进行范围查询。

• 增改写场景（DML 优化）：

  • 批量操作： 插入数据时，弃用单条 INSERT，改用批量插入并手动提交事务；如果是海量数据初始化，直接走 LOAD DATA。
  • 精准加锁： 在执行 UPDATE 时，WHERE 条件必须精确命中索引。因为 InnoDB 的行锁是加在索引上的，如果索引失效，行锁就会升级为表锁，这在并发场景下是灾难性的。

• 主键与结构优化（DDL 层面）：

  • 建表时，我倾向于使用自增且尽量短的数字型主键，极力避免使用 UUID。因为 InnoDB 的叶子节点是按主键顺序存放的，顺序插入能最大程度减少页分裂和页合并带来的巨大开销。”

指定索引 use select * 容易出现回表查询

插入数据 批量insert 手动提交事务 主键顺序插入性能高于乱序 大批量插入 用load 主键优化 表数据在叶子节点链表来看，是根据主键顺序存放的 即 叶子节点是有序的 故顺序插入显著减少页分裂、页合并（删多了 会合并比较”空“的页）

• 顺序插入
• 主键尽量短
• 主键顺序插入 AUTO_INCREMENT
• 尽量不用UUID或者其他自然主键 order 多字段 有前后顺序 遵循最左前缀法则 索引默认是ASC 可以在创建时指定为DESC 不可避免using filesort 可以手动增大排序缓冲区大小 group by 优化 limit 优化 覆盖查询和子查询

子查询之所以快，是因为：

1. 大部分情况下，利用 PAGE_MAX_TRX_ID 优化，直接在二级索引里就完成了可见性判断，彻底免除了回表。
2. 即便需要判断可见性，由于二级索引体积小，读取它的成本比读取挂载了大量字段的聚簇索引要低得多。

limit可以用where id>=and id<=来替代，这样非常快。之所以不这样是因为某些id可能被删掉了，不连续。但是业务中要使用分页查询的数据都要保证id连续完整。 count 优化 update优化 InnoDB行锁针对索引而不是记录 并且该索引不能失效 否则行锁升级为表锁 影响并发

深分页优化#

深度分页问题：当使用 LIMIT offset, size 且 offset 值很大时，MySQL 需要扫描回表 offset + size 条记录后丢弃前面的 offset 条，性能急剧下降 优化核心是 **“用索引直接定位，避免扫描后丢弃”

“既然前面的数据反正要丢弃，为什么不直接在索引里跳过？” 事实上，MySQL 不能“直接计数然后跳过”，主要是由于 MySQL 的架构分层 以及 InnoDB 的MVCC机制 共同决定的

解决方案：#

1. 子查询先查 id：覆盖索引省回表
2. 游标记住 last_id：直接定位无扫描
3. 业务限制最大页：性价比高最实用

追问一：游标分页有什么缺点？什么场景不适合用？ - 答：不支持跳页、需要前端保存游标状态、ID 不连续时可能漏数据。电商搜索结果页适合，但后台管理系统需要跳页就不适合。 追问二：如果排序字段不是主键 ID，而是 create_time，怎么优化？ - 答：可以用联合索引 (create_time, id)，子查询改为 SELECT id FROM orders ORDER BY create_time, id LIMIT offset, size，游标分页改为 WHERE (create_time, id) > (last_time, last_id)。 追问三：为什么子查询只查 id 就能提升性能？ - 答：因为只查主键/索引列时，MySQL 可以使用”覆盖索引”，直接在索引树上获取数据，不需要回表去聚簇索引查完整记录。

比如只看状态正常的帖子 SELECT id FROM table WHERE status = 1 ORDER BY create_time LIMIT 100000, 10

日志体系架构#

错误日志 查询日志 慢查询日志

redo log / undo log / binlog 区别？ redo log 在 InnoDB 引擎层，采用循环写的 WAL 机制，保证崩溃恢复时的持久性；undo log 记录反向操作，支持事务回滚和 MVCC 实现；binlog 在 Server 层，追加写入，用于主从复制和数据备份。三者通过 “两阶段提交” 保证一致性，是 MySQL 高可用架构的基石。

undo log / 回滚日志#

功能：事务回滚 和 MVCC

undo log 在 Insert 和 Update 时分别记什么？

• 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
• 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
• 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

redo log#

为什么不直接刷盘？/ 有了undo lpg 为什么要有redo log？ 问题：bufferpool 提高读写效率，但是他是基于内存的，不可靠。一个事务提交成功，但脏页未正常落盘，比如断电重启，持久性被打破，故有 redo log机制

redo log 的作用：

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
• 将用户感知的写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

怎么保持持久性的： WAL（write-ahead-logging）机制 MySQL 的写操作并不是立刻写到磁盘上，而是先写redo日志，然后在合适的时间再写到磁盘上。

二进制日志（BINLOG）#

记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。 作用：数据备份和主从复制

binlog 有 3 种格式类型，分别是 STATEMENT、ROW（MySQL 5.7.7 及之后的默认格式）、MIXED

redo log 和 binlog 有什么区别？为什么不能替代redo log做崩溃恢复？ binlog是 server 层的日志，没办法记录哪些脏页还没有刷盘，redolog 是存储引擎层的日志，可以记录哪些脏页还没有刷盘，这样崩溃恢复的时候，就能恢复那些还没有被刷盘的脏页数据。

redo log 和 binlog 的 “两阶段提交” 机制

• 为什么需要两阶段提交：因为 redo log 和 binlog 是两个独立的日志系统，如果不协调，可能出现：
  • redo log 写了但 binlog 没写 → 主库恢复后数据丢失，从库没收到
  • binlog 写了但 redo log 没写 → 主库恢复后数据回滚，从库却收到了
• 两阶段提交流程：
  1. Prepare 阶段：写 redo log，标记为 prepare 状态
  2. Commit 阶段：写 binlog，然后写 redo log 标记为 commit 状态
• 崩溃恢复：如果崩溃时 redo log 处于 prepare 状态，通过检查 binlog 是否完整来决定提交还是回滚。

主从复制#

作用#

数据备份 失败迁移 读写分离 降低单库读写压力

原理#

线程角度：

• 主库 Binlog Dump 线程：当从库连接主库时，主库会创建一个 Binlog Dump 线程。这个线程会读取主库的 Binlog 事件，并将其发送给从库。
• 从库 I/O 线程：从库上会启动一个 I/O Thread。它负责与主库的 Binlog Dump 线程建立连接，接收主库发送过来的Binlog事件，并将这些事件写入到从库本地的中继日志（Relay Log）中。
• 从库 SQL 线程：从库上还会启动一个 SQL Thread。它负责读取本地的中继日志（Relay Log），解析出其中的SQL语句或数据变更事件，并在从库的数据库中重放（Replay）这些操作

为什么需要 Relay Log 而不能直接从 Master 的 Binlog 执行

MySQL 读写分离#

读写分离的核心目标是分摊主库的读压力，但引入它的同时，必须解决主从复制延迟带来的数据一致性问题（C）。

主从复制的三种底层机制（性能与一致性的权衡）#

• 异步复制（默认）： 主库写完即刻返回成功，后台推从库。极高吞吐，但主库宕机易丢数据。适用普通评论、浏览流水。
• 半同步复制（大厂标配）： 强制等待至少 1 个从库返回 ACK 才算成功。牺牲几毫秒网络延迟，换取数据绝对防丢。适用订单、支付等核心交易。
• 全同步复制： 需等待所有从库落盘。存在致命“木桶效应”，极度拖垮性能。现代高并发系统基本弃用，多以 Paxos/Raft 共识协议（如 TiDB / MySQL MGR）替代。

如何解决主从延迟导致的“写后即读”数据不一致？#

（面试官必问：刚写完主库，立马去读从库，读不到怎么办？）

我通常会根据业务场景的容忍度采用三种解法：

• 强制主库路由（最通用）： 对于核心强一致性场景（如支付后查询余额），直接在代码层（或中间件）强制打上标记，将该次读请求强行路由到主库。
• 半同步复制降维： 在数据库底层采用半同步机制，极大缩短延迟窗口，缓解非核心业务的不一致概率。
• 缓存过渡法（更优雅）： 写入数据库的同时写入 Redis 缓存并设置短暂过期时间。前端查询时优先查 Redis，利用缓存的高性能填补主从同步的时间差。

读写分离下的“事务”如何平滑处理？#

核心原则： 只要事务中包含任何写操作（INSERT/UPDATE/DELETE），为了保证 ACID 原子性，该事务内的所有读写请求，必须死死绑定在主库执行，绝不能跨库拆分。

工程落地方式（结合 Java 栈）：

• 代码应用层： 通过 Spring 的 @Transactional(readOnly = true) 注解明确标记只读事务。底层配合重写 AbstractRoutingDataSource 进行数据源动态切换：只读走从库，非只读走主库。
• 代理中间件层（如 ShardingSphere）： 彻底解耦业务。中间件自动解析 SQL 语法树（AST），一旦在 BEGIN ... COMMIT 事务块内嗅探到写操作，会自动将后续的查询全部强制粘滞在主库处理。

MySQL 磁盘 I/O 很高，有什么优化的方法？#

• SQL查询效率低 检查慢查询日志并优化SQL添加必要的索引，避免全表扫描
• InnoDB缓冲池配置
• “延迟” binlog 和 redo log 刷盘的时机
• 临时表使用磁盘 复杂查询可能生成大量临时表，超出内存限制后写入磁盘。

大表问题#

“大表”的本质与性能拐点#

• 破除误区： 2000 万行并非物理死线，极限容量取决于单行数据大小。
• 性能崩塌根因（内存 vs 磁盘）： 当数据与索引总体积撑爆 InnoDB Buffer Pool 时，原先毫秒级的内存命中，将退化为昂贵的磁盘随机 I/O（频繁换页）。

调优路径（遵循 KISS 原则，由轻到重）#

1. 索引优化与缓存（千万级以内 / 单纯查询慢）

• 手段： 优先采用联合索引、覆盖索引（避免回表）。
• 挡流量： 引入 Redis 等多级缓存拦截绝大部分读请求，保护底层 DB
  2. 结构瘦身（大字段剥离）
• 手段： 将长文本、图片转存至 OSS 并配合 CDN 加速，数据库仅留 URL。
• 收益： 极致压缩单行体积，使单个数据页能容纳更多行，有效延缓 B+ 树变深。 3. 架构拆分（终极手段）
• 手段： 仅在面临存储触顶或单机并发写瓶颈时，才引入垂直拆分或水平分库分表。

为什么几十亿数据有索引依然慢？#

1. B+ 树长高（层级跃升）： 树结构从常规的 3 层膨胀至 4~5 层，拉长搜索路径。
2. 随机 I/O 剧增： 树每增高 1 层，最坏情况多 1 次磁盘寻址。若走二级索引，叠加回表操作，I/O 次数呈指数级放大。
3. 内存命中率雪崩（致命根因）： 几十上百 GB 的巨型索引无法完整驻留内存，引发高频的 Cache Miss（缺页中断）。查询被迫不断从磁盘加载数据页，彻底拖垮整体系统的 QPS 与响应延迟。

分库分表#

分库分表是应对大数据量、高并发场景的数据库拆分策略：

垂直分库：按业务模块拆分到不同数据库（用户库、订单库）。 垂直分表：将大表按列拆分成多个小表（将长文本字段拆分出去）。 水平分库：将同一张表的数据按规则（取模、范围）分布到多个数据库实例。 水平分表：将一张表的数据按规则拆分到多个结构相同的表中（可放在同一库或不同库）。

中间件怎么做的#

以客户端模式（如 ShardingSphere-JDBC）为例，核心是在应用层通过 AOP 机制或 ORM 框架的插件（比如 MyBatis 的 Interceptor）来劫持 SQL 执行流程

分库分表会带来什么问题呢#

• join 操作：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。
• 事务问题：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了
• 分布式 ID：分库之后， 数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。
• 跨库聚合查询问题：分库分表会导致常规聚合查询操作，如 group by，order by 等变得异常复杂。

分库分表后，数据怎么迁移呢？#

1. 停机迁移
2. 双写方案
3. 数据库同步工具 Canal 做增量数据迁移(原理依赖于binlog)