知序
返回
保存草稿
发布
H1
H2
H3
1
2
3
Excalidraw 图
AI 写作
AI 优化
目录
输入内容后显示目录
Markdown
# MySQL 高频面试题集(50 题) 按主题分六章,覆盖基础、索引、事务、锁、性能优化、主从运维。 ## 目录 - [第 1 章 · 基础架构(6 题)](#第-1-章--基础架构) - [第 2 章 · 索引(12 题)](#第-2-章--索引) - [第 3 章 · 事务与 MVCC(8 题)](#第-3-章--事务与-mvcc) - [第 4 章 · 锁(8 题)](#第-4-章--锁) - [第 5 章 · 性能优化与 SQL 调优(10 题)](#第-5-章--性能优化与-sql-调优) - [第 6 章 · 主从与运维(6 题)](#第-6-章--主从与运维) ## 高频 Top 10 1. InnoDB 和 MyISAM 区别?(3) 2. B+ 树和 B 树区别?为什么用 B+ 树?(8) 3. 聚簇索引和非聚簇索引?(9) 4. 最左前缀原则?覆盖索引?索引下推?(12-14) 5. 四种隔离级别?(19) 6. MVCC 原理?(22) 7. 间隙锁、Next-Key Lock?(28-29) 8. 幻读怎么解决?(30) 9. SQL 慢了怎么优化?(39) 10. 主从延迟怎么排查?(46) --- # 第 1 章 · 基础架构 ## 1. MySQL 的整体架构? 分两层:**Server 层 + 存储引擎层**。 ``` [连接器] → [查询缓存(8.0 移除)] → [分析器] → [优化器] → [执行器] ↓ 存储引擎层(InnoDB / MyISAM / Memory...) ``` - **连接器**:管理连接、认证、权限 - **分析器**:词法、语法分析 - **优化器**:选索引、决定执行计划 - **执行器**:按计划调用存储引擎 API - **存储引擎层**:负责数据存取 ## 2. 一条 SQL 的执行流程? ``` SELECT * FROM t WHERE id = 1; 1. 客户端 → 连接器(鉴权、TCP 连接) 2. 查询缓存(8.0 已移除) 3. 分析器:词法语法分析,判断表名字段名 4. 优化器:决定用哪个索引、join 顺序 5. 执行器:先判断权限,然后调用存储引擎接口 6. InnoDB:B+ 树查找 → 返回数据给执行器 7. 执行器返回结果给客户端 ``` **写 SQL**:多了 redo log、binlog、undo log 的处理(见事务章节)。 ## 3. InnoDB 和 MyISAM 的区别? | 对比 | InnoDB | MyISAM | |------|--------|--------| | 事务 | 支持 | 不支持 | | 外键 | 支持 | 不支持 | | 行锁 | 支持 | 只有表锁 | | 崩溃恢复 | 支持(redo log) | 不支持 | | 聚簇索引 | 主键即数据 | 索引和数据分开 | | 全文索引 | 5.6+ 支持 | 支持 | | COUNT(*) | 慢(全表扫) | 快(有计数) | | 适用场景 | OLTP、写多 | 读多写少、日志 | **默认是 InnoDB**(5.5+),MyISAM 基本不用了。 ## 4. InnoDB 的内存结构?  *图:InnoDB 内存结构:Buffer Pool 及组成* ``` Buffer Pool(缓冲池,核心) ├── 数据页缓存(LRU 变种) ├── 索引页缓存 ├── 插入缓冲(Change Buffer) └── 自适应哈希索引 Log Buffer(redo log 缓冲) Additional Memory ``` **Buffer Pool**: - 默认 128MB,生产建议 **物理内存 50-75%** - 用 **改良版 LRU**:冷热分区,避免全表扫污染热数据 - 新数据先放冷区 - 停留一定时间再晋升到热区 - `innodb_old_blocks_pct` 控制冷区比例(默认 37%) - `innodb_old_blocks_time` 控制晋升时间(默认 1 秒) ## 5. InnoDB 的磁盘结构?  *图:表空间、Segment、Extent、Page 与日志文件* ``` 表空间 (.ibd 文件) ├── Segment(段) │ ├── Leaf Segment(叶子节点,数据) │ └── Non-Leaf Segment(非叶子节点,索引) ├── Extent(区)= 64 个页 = 1MB └── Page(页)= 16KB(默认) redo log: ib_logfile0, ib_logfile1(循环写) undo log: 存在系统表空间或独立 undo 表空间 binlog: 独立文件(Server 层) ``` ## 6. 为什么 InnoDB 页默认 16KB? - 和操作系统的文件读写单位(4KB)成整数倍,便于整页读写 - B+ 树三层可以支撑**千万级**数据:16KB / 14B(主键索引项)≈ 1170 个分支,三层 1170³ ≈ 16 亿行 - 太小:层级增多,IO 变多 - 太大:一次读太多用不上的数据 --- # 第 2 章 · 索引 ## 7. MySQL 有哪些索引? - **主键索引 / 聚簇索引**:叶子节点存整行数据 - **二级索引 / 非聚簇索引 / 辅助索引**:叶子节点存主键值,要**回表** - **唯一索引**:值不重复 - **普通索引**:值可重复 - **联合索引**:多列组合 - **全文索引**:文本搜索 - **前缀索引**:只索引字段前 N 字符 - **空间索引**:GEO ## 8. 为什么用 B+ 树而不是 B 树/红黑树/Hash? **B+ 树 vs B 树**: - B+ 树**数据只在叶子节点**,非叶子节点只有索引 → 单个节点能放更多索引项,树更矮 - B+ 树**叶子节点用双向链表串联** → 范围查询快 - B 树每个节点都有数据 → 范围查询要中序遍历,慢 **B+ 树 vs 红黑树**: - 红黑树是二叉树,深度大,磁盘 IO 多 - B+ 树是多叉树(千级扇出),深度小(3-4 层支撑千万数据) **B+ 树 vs Hash**: - Hash 等值查询 O(1),但**不支持范围、排序、前缀** - B+ 树 O(log N) 支持范围、排序、前缀 **MySQL 的 Hash 索引**:只有 Memory 引擎原生支持,InnoDB 的**自适应哈希索引**是内部优化,用户不能直接建。 ## 9. 聚簇索引和非聚簇索引? **聚簇索引(Clustered Index)**:数据和索引存在一起 - InnoDB 的主键索引就是聚簇索引 - 叶子节点存**整行数据** - 一张表只能有一个聚簇索引 - 没有主键时,InnoDB 会选**第一个 NOT NULL 的唯一索引**;再没有就生成隐藏的 ROW_ID **非聚簇索引(Secondary Index)**:数据和索引分开 - 叶子节点只存**主键值** - 查询时先查到主键,再去聚簇索引查完整数据(**回表**) - 一张表可以有多个 ``` 主键索引(聚簇): [1] [2] [3] ...(叶子节点直接是行数据) 二级索引: [name=Alice → id=3] [name=Bob → id=5] ... (叶子节点只有主键,查完整数据要回聚簇索引查) ``` ## 10. 什么是回表?怎么避免? **回表**:二级索引查到主键后,再到聚簇索引查完整数据的过程。 ``` SELECT * FROM users WHERE name = 'Alice'; 1. 先用 name 索引查到 id=3 2. 再用 id=3 到聚簇索引查完整行(回表) ``` **避免回表 → 覆盖索引**: ``` SELECT id, name FROM users WHERE name = 'Alice'; -- name 索引叶子节点已经有 (name, id),不用回表 ``` `EXPLAIN` 里 `Extra` 显示 **Using index** 就是用了覆盖索引。 ## 11. 索引失效的场景? 1. **隐式类型转换**:`WHERE phone = 13800138000`(phone 是 varchar),因为整数不能直接和字符串比 2. **对索引列做函数/运算**:`WHERE YEAR(created_at) = 2024`、`WHERE id + 1 = 5` 3. **LIKE 以通配符开头**:`LIKE '%abc'` 失效,`LIKE 'abc%'` 能用 4. **OR 条件一边没索引**:`WHERE a = 1 OR b = 2`,b 没索引全表扫 5. **NOT IN、!=、<>**:有时会失效(取决于优化器) 6. **IS NULL / IS NOT NULL**:可能失效(取决于版本) 7. **数据量小**:优化器判断全表扫更快,直接不用索引 8. **违反最左前缀原则**:见下一题 ## 12. 最左前缀原则? 联合索引 `(a, b, c)` 按从左到右的顺序匹配。 **能用**: ``` WHERE a = 1 WHERE a = 1 AND b = 2 WHERE a = 1 AND b = 2 AND c = 3 WHERE a = 1 AND c = 3 -- a 能用,c 用不了 ``` **用不了**: ``` WHERE b = 2 WHERE c = 3 WHERE b = 2 AND c = 3 ``` **顺序可以打乱**(优化器会重排): ``` WHERE b = 2 AND a = 1 -- 等价于 WHERE a = 1 AND b = 2,能用 ``` **范围查询后面的列失效**: ``` WHERE a = 1 AND b > 2 AND c = 3 -- a、b 能用,c 不能用索引 ``` ## 13. 覆盖索引是什么? **覆盖索引**:查询的列都在索引里,不用回表。 ```sql -- 建索引 CREATE INDEX idx_name_age ON users(name, age); -- 覆盖索引(Using index) SELECT name, age FROM users WHERE name = 'Alice'; -- 非覆盖索引(要回表查其他字段) SELECT * FROM users WHERE name = 'Alice'; ``` **优化技巧**:只查需要的列,让更多查询能用覆盖索引。 ## 14. 索引下推(ICP)是什么? **Index Condition Pushdown**(5.6+),把 WHERE 条件下推到存储引擎层,**减少回表次数**。 ```sql -- 联合索引 (name, age) SELECT * FROM users WHERE name LIKE '李%' AND age = 20; -- 没有 ICP: -- 存储引擎拿 name 匹配的所有行回表 -- Server 层再过滤 age = 20 -- 有 ICP: -- 存储引擎在索引里直接过滤 name LIKE '李%' AND age = 20 -- 只把符合的行回表 ``` `EXPLAIN` 里 `Extra` 显示 **Using index condition**。 ## 15. 前缀索引是什么?什么时候用? **前缀索引**:只对字段的前 N 字符建索引。 ```sql CREATE INDEX idx_email ON users(email(10)); -- 只索引前 10 字符 ``` **优点**:节省空间,索引更快 **缺点**: - 不能用覆盖索引(因为索引里只有前缀,不是完整值) - 前缀区分度要够(否则过滤效果差) **如何确定前缀长度**: ```sql SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) FROM users; -- 计算不同前缀数 / 总行数,比例越接近 1 越好 ``` ## 16. 什么时候应该建索引?什么时候不建? **该建**: - WHERE、ORDER BY、GROUP BY、JOIN ON 常用列 - 唯一性约束列 - 区分度高的列 **不建**: - 区分度低的列(性别、状态,只有几个值) - 表太小(几百行) - 频繁更新的列 - 索引已经够多(一般 < 5 个) ## 17. 自增主键和 UUID 哪个好? **自增主键**(推荐): - **顺序插入**:B+ 树末尾追加,不用频繁分裂 - **空间小**:8 字节 vs UUID 36 字节 - **二级索引节省空间**(叶子节点存主键) **UUID**: - 随机插入 → B+ 树频繁分裂,性能差 - 占空间大 - 分布式场景生成方便 **生产推荐**: - 单库:自增主键 - 分布式:雪花算法(Snowflake,时间戳 + 机器 ID + 序列号),兼顾顺序性和唯一性 ## 18. 为什么不建议用 SELECT * ? 1. **无法用覆盖索引**:必回表 2. **网络传输浪费**:多传不需要的字段 3. **内存占用**:缓冲区放更多数据 4. **字段变更不敏感**:表结构改了代码没改,可能返回新字段导致问题 5. **代码可读性差**:不知道到底用了哪些字段 --- # 第 3 章 · 事务与 MVCC ## 19. 四种隔离级别? | 级别 | 脏读 | 不可重复读 | 幻读 | |------|------|-----------|------| | READ UNCOMMITTED | ✓ | ✓ | ✓ | | READ COMMITTED | ✗ | ✓ | ✓ | | **REPEATABLE READ**(MySQL 默认) | ✗ | ✗ | 快照读不会,当前读可能 | | SERIALIZABLE | ✗ | ✗ | ✗ | **概念**: - **脏读**:读到别的事务**未提交**的数据 - **不可重复读**:同一事务内两次读同一行,值变了(别的事务提交了 update) - **幻读**:同一事务内两次查一个范围,行数变了(别的事务 insert/delete) ## 20. MySQL 为什么默认 RR 不是 RC? 历史原因 + 当时的复制机制需要。 **5.6 之前**:binlog 默认 STATEMENT 格式,RC 下可能导致主从数据不一致。所以默认 RR。 **现在**:binlog ROW 格式普及,RC 也没问题了。**阿里内部规范其实是 RC**,因为: - RR 用间隙锁更多,锁范围大,并发低 - RC 只锁行,更并发 但默认还是 RR,官方没动。 ## 21. ACID 是什么?怎么实现的? - **A(原子性)**:事务要么全做要么全不做 → **undo log** - **C(一致性)**:数据从一个合法状态到另一个合法状态 → 前三者的综合效果 - **I(隔离性)**:并发事务互不干扰 → **锁 + MVCC** - **D(持久性)**:提交后永久保存 → **redo log** ## 22. MVCC 是什么?怎么实现的? **MVCC(Multi-Version Concurrency Control)**:多版本并发控制,让**读不加锁**,读写不冲突。 **核心**: - **隐藏字段**:每行有 `trx_id`(修改事务 ID)和 `roll_pointer`(指向 undo log) - **undo log 链**:每次修改前,旧值存到 undo log,形成版本链 - **Read View**:事务开始时生成的一致性视图,决定能看到哪些版本 ``` 数据版本链(undo log 链): 当前版本 → V3 → V2 → V1 每个版本有 trx_id Read View: m_ids: 活跃事务 ID 列表 min_trx_id: 最小活跃 trx_id max_trx_id: 下一个要分配的 trx_id creator_trx_id: 当前事务 ID 可见性规则: trx_id < min_trx_id → 可见(已提交) trx_id >= max_trx_id → 不可见(未来的事务) min_trx_id <= trx_id < max_trx_id: 如果在 m_ids 里 → 不可见(其他活跃事务) 否则 → 可见 ``` ## 23. RC 和 RR 下 MVCC 的区别? - **RC(读已提交)**:**每次 SELECT 都生成新的 Read View** - **RR(可重复读)**:**事务第一次 SELECT 时生成 Read View,后续复用** 这就是为什么 RR 能"可重复读"。 ## 24. 快照读和当前读? **快照读**:读历史版本(MVCC),不加锁 ``` SELECT * FROM t WHERE id = 1; ``` **当前读**:读最新版本,加锁 ``` SELECT * FROM t WHERE id = 1 FOR UPDATE; -- 排他锁 SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE; -- 共享锁 UPDATE / DELETE / INSERT -- 隐式当前读 ``` ## 25. redo log 和 binlog 的区别? | 对比 | redo log | binlog | |------|----------|--------| | 层级 | InnoDB 引擎层 | Server 层 | | 格式 | 物理日志(某页某偏移改什么) | 逻辑日志(SQL 或行变更) | | 用途 | 崩溃恢复 | 主从复制、数据恢复 | | 写入 | 循环写,覆盖 | 追加写,不覆盖 | | 大小 | 固定(innodb_log_file_size × n) | 可无限 | ## 26. redo log 和 binlog 的两阶段提交? **背景**:两个日志如果只写一个,崩溃后主从数据可能不一致。 **两阶段提交(2PC)**: ``` 1. InnoDB:写 redo log,标记为 prepare 2. Server 层:写 binlog 3. InnoDB:redo log 标记为 commit ``` **崩溃恢复规则**: - redo log 是 commit → 直接用 - redo log 是 prepare + binlog 完整 → commit - redo log 是 prepare + binlog 不完整 → 回滚 这样保证主备一致。 ## 27. undo log 有什么用? **两大作用**: 1. **回滚**:事务失败时用 undo log 反向执行恢复数据 2. **MVCC**:提供历史版本链,实现快照读 **回收**:事务提交后不是立刻删,要等没有快照需要它才能删(purge 线程)。 长事务会阻塞 undo log 回收,导致**表空间膨胀**。 --- # 第 4 章 · 锁 ## 28. MySQL 的锁分类? **按范围**: - **全局锁**:`FLUSH TABLES WITH READ LOCK`,做全库备份时用 - **表锁**:`LOCK TABLES t READ/WRITE`,粒度大 - **行锁**:InnoDB 特有,粒度小 **按模式**: - **共享锁(S)/ 读锁** - **排他锁(X)/ 写锁** - **意向锁(IS、IX)**:表级标记,加行锁前先加意向锁 **行锁的三种形式**(InnoDB): - **Record Lock**:记录锁,锁单行 - **Gap Lock**:间隙锁,锁范围(开区间),不锁实际行 - **Next-Key Lock**:Record + Gap,锁前开后闭区间 ## 29. Next-Key Lock 是什么? **Record Lock + Gap Lock** 的组合,锁前开后闭的区间。 ``` 表:id = 1, 5, 10, 15 范围分段:(-∞, 1], (1, 5], (5, 10], (10, 15], (15, +∞) -- 执行 SELECT * FROM t WHERE id BETWEEN 5 AND 10 FOR UPDATE; -- 锁住 (1, 5], (5, 10], (10, 15] 这几个区间 -- 防止别人插入 id=3 / 8 / 12 ``` **作用**:防止幻读(RR 级别下)。 **退化**: - 唯一索引等值查询且命中 → 退化为 Record Lock - 不命中 → 退化为 Gap Lock ## 30. 幻读是什么?InnoDB 怎么解决? **幻读**:同一事务内,两次查同一范围,行数变了(别的事务 insert/delete 提交后)。 ``` 事务 A:SELECT * FROM t WHERE id > 10; -- 返回 3 行 事务 B:INSERT INTO t VALUES (20); -- 提交 事务 A:SELECT * FROM t WHERE id > 10; -- 如果能看到 4 行 → 幻读 ``` **InnoDB 的解决方式**(RR 级别): - **快照读(普通 SELECT)**:通过 MVCC,基于同一个 Read View,看到的行数不变 - **当前读(FOR UPDATE / UPDATE 等)**:加 **Next-Key Lock**,锁住范围,阻止别人插入 ## 31. 死锁是怎么产生的?怎么排查和避免? **死锁**:两个事务互相持有对方需要的锁。 ``` 事务 A:锁了 row 1,等 row 2 事务 B:锁了 row 2,等 row 1 → 死锁 ``` **InnoDB 处理**: - 自动检测(`innodb_deadlock_detect`) - 选择 **undo log 最少的事务** 回滚 **排查**: ```sql SHOW ENGINE INNODB STATUS\G -- 看 LATEST DETECTED DEADLOCK 部分 ``` **避免**: 1. **固定加锁顺序**:所有事务按同一顺序访问资源 2. **加索引**:减少锁范围 3. **事务尽量短**:减少持锁时间 4. **避免大事务**:拆成多个小事务 5. **合理设计索引**:避免大量间隙锁 ## 32. 更新一行不存在的数据会加锁吗? **会**,加 **Gap Lock**。 ```sql -- 表中无 id=7 的行 UPDATE t SET name = 'x' WHERE id = 7; ``` - RR 级别下,InnoDB 会加 Gap Lock 锁住 (5, 10) 这个间隙(假设 5 和 10 是相邻值) - 防止别人插入 id=7 这是很多死锁案例的根源。 ## 33. 什么情况下加表锁? **InnoDB 自动加表锁**: - `ALTER TABLE`(DDL) - 没有索引的更新(优化器选择全表扫) **显式加表锁**: ```sql LOCK TABLES t WRITE; UNLOCK TABLES; ``` **生产尽量避免**,会严重影响并发。 ## 34. 行锁锁的是什么? **锁的是索引**,不是行本身。 ```sql -- 通过索引更新 UPDATE t SET name = 'x' WHERE id = 1; -- 锁 id=1 这一行的索引 -- 没有索引的更新 UPDATE t SET name = 'x' WHERE name = 'old'; -- 优化器可能选择全表扫,锁所有行(退化为表锁) ``` ## 35. 乐观锁和悲观锁怎么用? **悲观锁**:认为会冲突,先加锁 ```sql SELECT * FROM t WHERE id = 1 FOR UPDATE; -- 加 X 锁 UPDATE t SET stock = stock - 1 WHERE id = 1; ``` **乐观锁**:假设不会冲突,更新时校验版本 ```sql -- 读取时带 version SELECT id, stock, version FROM t WHERE id = 1; -- 更新时校验 UPDATE t SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 原 version; -- 如果 affected rows = 0,说明被别人改了,重试 ``` **选择**: - 冲突多 → 悲观锁 - 冲突少 → 乐观锁,减少锁开销 --- # 第 5 章 · 性能优化与 SQL 调优 ## 36. EXPLAIN 的字段什么意思? ``` +----+-------------+-------+-------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------+ ``` **type**(查询性能,从好到差): - `system` > `const`(主键等值) > `eq_ref`(唯一索引 join) > `ref`(普通索引) > `range`(范围) > `index`(索引全扫) > `ALL`(全表扫) **Extra** 关键字: - `Using index`:覆盖索引(好) - `Using index condition`:索引下推(好) - `Using where`:Server 层过滤 - `Using filesort`:用了外部排序(不好,要优化) - `Using temporary`:用了临时表(不好,要优化) **rows**:预估扫描行数 **key**:实际用的索引 ## 37. 慢查询日志怎么用? **配置**: ```sql SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 1 秒以上算慢 SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; ``` **分析工具**:`mysqldumpslow`、`pt-query-digest`(推荐) ```bash pt-query-digest slow.log ``` 会按执行次数、总耗时排序,列出最慢的 SQL。 ## 38. LIMIT 深分页为什么慢?怎么优化? ```sql SELECT * FROM t ORDER BY id LIMIT 1000000, 10; ``` **慢的原因**: - 要扫描 100w + 10 行才能返回 10 行 - 每行都要回表 **优化**: **1. 延迟关联**:子查询只查主键,再 join 回去 ```sql SELECT * FROM t INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 10) x USING(id); ``` **2. 记录上次最大 ID**: ```sql SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 10; -- 比 OFFSET 快得多,但只能顺序翻页 ``` **3. 分页缓存 + 限制最大页数** ## 39. SQL 慢了怎么优化? **排查步骤**: 1. **EXPLAIN 看执行计划** - type 是 ALL/index → 加索引 - 有 filesort / temporary → 优化 order by / group by - rows 特别大 → 检查 where 条件 2. **看慢日志**:找到具体 SQL 和执行频率 3. **检查索引**: - WHERE / JOIN / ORDER BY 的列有没有索引 - 是否违反最左前缀 - 索引是否失效(类型转换、函数) 4. **数据层面**: - 表太大 → 分库分表、归档历史数据 - 大事务 → 拆分 - 热点数据 → 加缓存 5. **SQL 改写**: - 避免 `SELECT *` - 用 join 代替子查询(有时反过来) - 用 EXISTS 代替 IN(大表 IN 慢) - 深分页优化 6. **配置层面**: - `innodb_buffer_pool_size` 足够大 - `query_cache` 已移除,别纠结 - 连接数、线程数 ## 40. 大表怎么 DDL? 直接 `ALTER TABLE` 大表会锁表几小时,影响业务。 **方案**: **1. Online DDL**(5.6+) ```sql ALTER TABLE t ADD COLUMN c VARCHAR(100), ALGORITHM=INPLACE, LOCK=NONE; ``` - `ALGORITHM=INPLACE`:不重建表 - `LOCK=NONE`:不锁表 - 但不是所有操作都支持(加索引、加列通常支持;修改列类型通常不支持) **2. pt-online-schema-change**(Percona) - 创建新表 + 同步数据 + 切换 - 业务几乎无感知 **3. gh-ost**(GitHub) - 类似 pt-osc,基于 binlog 同步,更稳定 - 当前主流方案 ## 41. 分库分表怎么做? **分的目的**: - 单表数据量太大(> 2000w)→ 查询慢 - 单库压力大(连接数、IO、CPU) **垂直拆分**: - **垂直分库**:按业务拆(用户库、订单库、商品库) - **垂直分表**:一张表字段太多 → 拆成多张(常用字段 + 大字段) **水平拆分**: - 按某个字段 hash 或 range 分 - **水平分表**:一张表拆成多张(`order_0`、`order_1`...) - **水平分库**:一个库拆成多个 **分片键选择**: - 高频查询字段(订单选 user_id / order_id) - 数据均匀 - 避免跨片查询 **工具**: - **ShardingSphere**(Apache,主流) - **MyCat** - **Vitess**(YouTube) ## 42. 分表后遇到的难题? 1. **跨片查询**:需要广播到所有片 + 聚合 2. **分布式事务**:XA、TCC、Saga、可靠消息 3. **全局唯一 ID**:雪花算法、Leaf、UidGenerator 4. **分页**:深分页更难 5. **Join**:跨片 join 性能差,尽量避免 6. **数据迁移/扩容**:哈希分片扩容复杂(一致性哈希能缓解) **设计原则**: - 90% 查询能通过分片键定位到单片 - 少量跨片查询接受较慢 - 不支持分片键的查询 → 搜索引擎(ES)或 OLAP ## 43. 索引维护的代价? **写入放大**: - 每次 INSERT/UPDATE/DELETE 都要更新所有相关索引 - 索引越多,写入越慢 **空间占用**:每个索引都是一棵 B+ 树,占额外磁盘和内存 **经验**: - 单表索引数控制在 **5 个以内** - 定期检查冗余索引(`pt-duplicate-key-checker`) - 只读表可以多建,OLTP 表要克制 ## 44. count(\*) 为什么慢?怎么优化? **慢的原因**: - MyISAM:维护了行数,`COUNT(*)` 是 O(1) - **InnoDB 必须全表扫**(因为 MVCC,不同事务看到的行数不同) **优化**: 1. **单独维护计数表**:业务更新时同步修改 2. **Redis 计数**:INCR/DECR 3. **估算**:`SHOW TABLE STATUS LIKE 't'` 里的 Rows(不精确,但快) 4. **二级索引**:`COUNT(*)` 优化器会选最小的索引扫,比全表稍快 5. **COUNT(*) vs COUNT(1) vs COUNT(id)**:几乎一样,优化器都会优化成扫索引 ## 45. JOIN 的原理? **MySQL 主要用 Nested Loop Join**(嵌套循环): ``` for 每一行 in 外表(驱动表): for 每一行 in 内表(被驱动表): 判断 JOIN 条件 ``` **优化方式**: 1. **Index Nested Loop(INL)**:内表有索引,查找 O(log n),总 O(n log m) 2. **Block Nested Loop(BNL)**:内表无索引,把外表一批读到 join buffer,再和内表比,减少 IO 3. **Hash Join**(8.0.18+):构建 hash 表,适合大表 join 无索引 **原则**: - **小表驱动大表**:外表小的话总循环次数少 - **被驱动表加索引**:把 BNL 变成 INL - **避免 SELECT \***:减少 join buffer 占用 --- # 第 6 章 · 主从与运维 ## 46. 主从复制原理?  *图:主从复制链路:binlog 到 relay log 回放* ``` Master Slave | | ├─ 写操作 → binlog | | | └─ dump 线程 → ────→ I/O 线程 → relay log | └─ SQL 线程 → 回放 ``` **三个线程**: 1. **Master 的 dump 线程**:把 binlog 发给 Slave 2. **Slave 的 I/O 线程**:接收 binlog 写入 relay log 3. **Slave 的 SQL 线程**:读 relay log 并回放 **复制模式**: - **异步复制**(默认):Master 写完就返回,不等 Slave - **半同步复制**:Master 至少等一个 Slave 确认 - **组复制(MGR)**:基于 Paxos,强一致 ## 47. 主从延迟怎么排查和解决? **查看延迟**: ```sql SHOW SLAVE STATUS\G -- Seconds_Behind_Master: 延迟秒数 ``` **常见原因**: 1. **Slave SQL 线程单线程回放**(老版本)→ 开并行复制 2. **Master 写入太快**:大事务、批量 insert 3. **Slave 机器性能差** 4. **网络问题** 5. **Slave 执行慢查询** **解决**: 1. **开启并行复制**:`slave_parallel_type = LOGICAL_CLOCK`(5.7+)、`slave_parallel_workers = 8` 2. **大事务拆小** 3. **避免慢 SQL**(索引、分页) 4. **读写分离时用半同步** 5. **关键业务强制主库读** ## 48. 读写分离怎么做?怎么避免主从延迟问题? **实现方式**: - **应用层**:业务代码里区分读/写(Spring 的 AbstractRoutingDataSource) - **中间件**:ShardingSphere、MyCat、ProxySQL **主从延迟带来的问题**:写了主库立刻读从库可能读不到。 **解决**: 1. **强制主库读**:对强一致要求的查询直接读主库 2. **会话路由**:同一会话的读写都走主库(有状态) 3. **缓存**:写后立刻写缓存,读先查缓存 4. **半同步复制**:降低延迟但不能消除 5. **业务容忍**:接受短暂不一致,用户体验可控 ## 49. binlog 有几种格式?选哪个? **三种格式**: - **STATEMENT**:记录 SQL 语句 - 优点:日志小 - 缺点:`UUID()`、`NOW()` 等函数在主从执行可能不一致 - **ROW**:记录每行的变更 - 优点:最准确 - 缺点:日志大(一条 `UPDATE` 影响 100w 行就记 100w 条) - **MIXED**:混合,MySQL 自己决定用哪个 **推荐 ROW**(生产标准): - 准确性最重要 - 主从一致有保证 - binlog 大的问题可以通过定期清理解决 ## 50. 数据库备份恢复? **物理备份**: - **冷备**:停机拷贝 `.ibd` 文件(生产不可用) - **热备**:XtraBackup(Percona 工具),不停机 **逻辑备份**: - `mysqldump`:导出 SQL,适合小库 - `mysqlpump`:并行版 - 缺点:大库慢,恢复也慢 **Binlog 增量**: - 全量备份后,binlog 做增量 - 可以做**时间点恢复(PITR)** **生产方案**: ``` 每天凌晨:XtraBackup 全量备份 → 压缩 → 上传 S3/OSS 每分钟:binlog 异步归档 恢复: 1. 最近一次全量恢复 2. 应用 binlog 到指定时间点 ``` **定期演练**:备份不演练等于没备份。
预览
发布文章
添加摘要让读者更快了解文章内容
取消
确认发布
AI 写作助手
输入一个主题,AI 将为你生成一篇 Markdown 博客文章
AI 正在写作中...
取消
开始生成
AI 优化助手
描述你的修改需求,AI 将在原文基础上进行补充或微调
AI 正在优化中...
取消
开始优化
画布
从已有图里选
✕
图名
保存后生成 /images/excalidraw/<图名>.svg(只能用字母、数字、短横线、下划线、点)
取消
保存并插入