MySQL 专题：索引、事务、日志、备份恢复、MVCC 与性能优化

发表于2019-12-27|数据库

|浏览量:

MySQL 是后端核心考点。把索引、执行计划、事务隔离、三大日志连成一条链，慢 SQL 与并发问题才看得懂。

推荐学习顺序

索引结构 → EXPLAIN 执行计划 → SQL 执行链路 → 隔离级别与 MVCC → redo/undo/binlog → 备份恢复与规范优化。

核心主题地图

索引：B+ 树、最左前缀、覆盖索引、回表、索引下推。
事务：ACID、四种隔离级别、幻读与间隙锁。
日志：redo 保证崩溃恢复，undo 支撑回滚与 MVCC，binlog 服务复制与 PITR。
性能：慢查询、连接池、规范建表、避免隐式转换。

实践方法

每学一章做一个小实验：故意写错索引看 type 变化；开两个事务验证 repeatable read；用 binlog 做一次时间点恢复演练。

常见误区

只背八股不做 EXPLAIN；忽略 buffer pool 与磁盘 IO；把分库分表当性能万能药而不先优化 SQL。

工具链配套

慢日志、slow_query_log、performance_schema、sys schema、PMM/Grafana 形成闭环。Schema 变更用 gh-ost；备份用 XtraBackup；复制延迟看 Seconds_Behind_Master 与并行复制。

面试串联话术

从一条 UPDATE 语句切入：优化器选索引 → InnoDB 加行锁 → 写 undo/redo → 提交写 binlog → 主从复制。

实践复习清单

索引-事务-日志-执行计划四线合练；一条 UPDATE 全链路口述；慢 SQL 三板斧：explain、索引、改写；备份恢复演练年度执行。

总结与自测

MySQL 学习主线五节点；一条 UPDATE 链路；慢 SQL 三步；备份恢复是否演练。能白板画出索引-事务-日志关系，说明真的入门了。

原理延伸

InnoDB 的核心竞争力在于把 B+ 树索引、缓冲池、redo/undo 与 MVCC 组合成可预测的 OLTP 引擎。读路径尽量走缓冲池与覆盖索引，写路径则要在行锁、日志刷盘与复制延迟之间取平衡。排查问题时不要只盯单条 SQL，还要看事务边界、锁等待、buffer pool 命中率与磁盘 fsync 延迟。性能优化永远遵循「先正确、再可测、后优化」：用慢日志和 EXPLAIN 定位瓶颈，用规范设计减少回表与锁竞争，用架构手段（读写分离、缓存、分片）承接流量，而不是在未测量前堆参数。

文章作者: Dai Wei

文章链接: https://www.daiwei.biz/knowledge/database/mysql/overview/

版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Dai Wei！

MySQL 索引 InnoDB

扫码打赏

微信
支付宝

相关推荐

MySQL隐式转换造成索引失效

WHERE 明明有索引却全表扫描，经常是列类型与常量类型不一致触发隐式转换。机制说明当比较两侧类型不同时，MySQL 按转换规则把一侧转为另一侧。若对索引列做函数或转换，优化器往往无法使用该索引，因为索引存的是原始值顺序。典型场景123456789-- phone 为 VARCHAR，常量写数字 → 列被转数字，索引失效SELECT * FROM user WHERE phone = 13800138000;-- 正确写法SELECT * FROM user WHERE phone = '13800138000';-- 对列做函数同样失效SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';-- 改为范围查询SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'; 排查步骤EXPLAIN 看 type&#x...

InnoDB存储引擎对MVCC的实现

MVCC 让读写少冲突：快照读不加锁，当前读才走锁。理解 Read View 是读懂 repeatable read 的关键。三要素隐藏列：DB_TRX_ID（最后修改事务 ID）、DB_ROLL_PTR（回滚指针）。 undo log：保存旧版本链，支持回滚与一致性读。 Read View：事务启动时（或首次一致性读）生成，判断行版本可见性。可见性规则（简化）比较行的 trx_id 与 Read View 的 min/max 及活跃事务列表：若版本对当前事务不可见，沿 undo 链找更早版本。快照读 vs 当前读1234-- 快照读（普通 SELECT）SELECT * FROM account WHERE id = 1;-- 当前读（加锁读）SELECT * FROM account WHERE id = 1 FOR UPDATE; RR 下快照读可避免不可重复读；幻读靠间隙锁/next-key lock 在当前读场景防护。实践建议短事务、避免长查询占旧版本导致 undo 堆积；监控 History list length。版本链长度频...

MySQL索引详解

索引是 InnoDB 性能的第一杠杆。理解 B+ 树与聚簇/二级索引差异，设计才不会拍脑袋。 B+ 树为何适合磁盘多路平衡、叶子链表便于范围扫；非叶只存键减少 IO。InnoDB 聚簇索引叶子存整行，二级索引叶子存主键值需回表。最左前缀与覆盖索引联合索引 (a,b,c) 可支持 a、a+b、a+b+c 前缀查询；覆盖索引指 SELECT 列全在索引内，Extra 出现 Using index。 123CREATE INDEX idx_user_status ON orders(user_id, status, created_at);-- 覆盖列尽量放进索引SELECT user_id, status, created_at FROM orders WHERE user_id=1 AND status=1; 索引下推 ICP5.6+ 在存储引擎层先过滤二级索引条件，减少回表次数。设计原则高选择性列在前；控制宽度；避免重复索引；监控 duplicate index。前缀索引与区分度INDEX(name(10)) 要算 distinct prefix 选择性。过...

字符集详解：字符集是什么？怎么用？

乱码和 emoji 存不进去，根因往往在字符集链路某一段不一致。搞清「字符集 vs 编码」比背命令更重要。核心概念字符集是字符集合；编码是字符与字节的映射规则。计算机只认字节，显示什么取决于解码规则是否一致。ASCII 用 7 位有效位；中文体系经历 GB2312/GBK/GB18030；Unicode 统一码点，UTF-8/UTF-16 是常见编码实现。 MySQL 里的 utf8 陷阱MySQL 的 utf8 实际是阉割版 UTF-8，最多三字节，无法存 emoji 与部分生僻字。生产应默认 utf8mb4，并在表、列、连接层保持一致。 12CREATE DATABASE app DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;SET NAMES utf8mb4; 全链路一致客户端、连接、服务器、库、表、列六级字符集任一不一致都可能乱码。排查时从 SHOW VARIABLES LIKE 'character%' 与 SHOW CREATE TABLE 入手，...

数据库知识体系：SQL、MySQL、Redis、MongoDB 与 Elasticsearch

后端工程师的数据库能力往往决定系统上限。本文把关系型、缓存与 NoSQL 串成一条可执行的学习主线。学习主线怎么排建议按「通用概念 → SQL 基本功 → MySQL 内核 → Redis 工程化 → 按需补 NoSQL」推进。先搞清 ACID、范式、索引与事务，再进入执行计划与日志；缓存部分重点理解穿透/击穿/雪崩与一致性策略，而不是死记命令。各模块要抓什么 SQL：会写不等于会优化，连接、子查询与聚合的语义要能和执行计划对应。 MySQL：索引、隔离级别、MVCC、redo/undo/binlog 是一条链，慢 SQL 排查从这里串。 Redis：数据结构选型、持久化、阻塞排查、集群分片是面试与线上并重的高频区。 MongoDB/ES：按岗位补充，文档模型与倒排索引各自解决不同检索场景。实践建议每学一个主题，用一个小例子验证：建表加索引看 EXPLAIN；开两个会话验证隔离级别；用 Redis 实现缓存并故意制造击穿场景。只看不练，面试和排障都容易卡壳。常见误区把「会用 ORM」当成「懂数据库」。索引越多越好...

MySQL日期类型选择建议

存时间看起来 trivial，却影响时区、范围、索引与跨系统交换。类型选对，后面少很多 bug。类型对比 DATETIME：8 字节，范围 1000-9999，与时区无关存字面量，推荐业务时间戳。 TIMESTAMP：4 字节，2038 上限，受 session time_zone 影响，自动 ON UPDATE 方便。 BIGINT：存 Unix 毫秒，跨语言一致，展示需应用层格式化。 12345CREATE TABLE event ( id BIGINT PRIMARY KEY, occurred_at DATETIME(3) NOT NULL COMMENT '业务发生时间 UTC+8 存字面量', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); 选型建议金融/订单用 DATETIME(3) 或 BIGINT 毫秒；日志归档可用 BIGINT 节省比较成本；全球化产品统一 UTC 存储、本地化展示。跨时区团队规范存储 UTC、展示 local 是通用做法。数据库 server ...

评论

数据加载中