MySQL 实战笔记：安装、事务、索引、锁与高性能优化

MySQL 是互联网业务中使用最广泛的关系型数据库之一。掌握它不能只停留在“会写 CRUD”，还要理解事务隔离、索引结构、锁机制以及高性能架构设计。本文从实战出发，结合常见坑点与优化建议，帮你建立系统性的 MySQL 知识地图。

一、高层次思想：MySQL 在解决什么问题？

关系型数据库的核心价值可以概括为三句话：

1. 持久化：让数据安全地落在磁盘上，程序重启不丢失。
2. 一致性：通过事务与约束，保证数据始终符合业务规则。
3. 高效访问：通过索引、缓存、分区分表等手段，在海量数据中快速读写。

graph TD
    A[业务应用] -->|SQL| B[MySQL Server]
    B --> C[SQL 解析 / 优化]
    C --> D[执行引擎]
    D --> E[存储引擎 InnoDB/MyISAM]
    E --> F[Buffer Pool 缓存]
    E --> G[Redo / Undo Log]
    E --> H[磁盘数据文件]

1.1 MySQL 架构分层

graph LR
    A[连接层] --> B[服务层]
    B --> C[存储引擎层]
    B --> D[查询缓存 / 解析器 / 优化器]
    C --> E[InnoDB 支持事务/行锁]
    C --> F[MyISAM 高速查询/表锁]

1.2 Buffer Pool：InnoDB 的内存缓存

Buffer Pool 是 InnoDB 在内存中开辟的一块区域，用来缓存数据页和索引页。它是 MySQL 性能的核心，绝大多数读写操作都先发生在 Buffer Pool 中。

为什么需要 Buffer Pool？

磁盘的读写速度比内存慢几个数量级。如果每次查询都读磁盘，性能会极差。Buffer Pool 把热数据放在内存里，大幅减少磁盘 I/O。

Buffer Pool 的工作流程：

sequenceDiagram
    participant App as 应用
    participant BP as Buffer Pool
    participant Disk as 磁盘

    App->>BP: 查询 id=1
    BP-->>App: 命中，直接返回
    Note over BP: 数据页已在内存

    App->>BP: 查询 id=2
    BP->>Disk: 未命中，从磁盘加载
    Disk-->>BP: 返回数据页
    BP-->>App: 返回数据

写数据时的流程：

1. 先把数据页读到 Buffer Pool（如果不在）。
2. 在 Buffer Pool 中修改数据页，此时这个页变成脏页（Dirty Page）。
3. 写 Redo Log（WAL）。
4. 返回成功给应用。
5. 后台线程（Page Cleaner）异步把脏页刷回磁盘。

Buffer Pool 的内部结构：

注意：脏页是异步刷盘的，所以提交成功不代表数据页已经落盘。InnoDB 通过 Redo Log 保证即使崩溃也能恢复。

二、安装、启动与基础配置

2.1 启动 MySQL 服务

# Windows
net start MySQL

# Linux/macOS
sudo systemctl start mysql

2.2 修改 root 密码

ALTER user 'root'@'localhost' IDENTIFIED BY 'root';

2.3 创建数据库时务必指定编码

CREATE DATABASE `test` /*!40100 COLLATE 'utf8mb4_bin' */;

避坑指南 #1：默认的 utf8 在 MySQL 中其实是阉割版（最多 3 字节），无法存储 Emoji 等 4 字节字符。生产环境请统一使用 utf8mb4。

2.4 JDBC 连接串必须带编码与时区

jdbc:mysql://127.0.0.1:3306/crmeb_java?serverTimeZone=UTC&useUnicode=true&characterEncoding=utf8

MySQL 8 推荐写法：

jdbc:mysql://127.0.0.1:3306/db_name?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=utf8mb4

避坑指南 #2：MySQL 8 默认启用 SSL，但旧版驱动连接时可能报错，可显式设置 useSSL=false（内网环境）。时区必须指定，否则会出现时间差 8 小时的问题。

2.5 开启通用查询日志

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/tmp/mysql.log';
SHOW VARIABLES LIKE "general_log%";

避坑指南 #3：通用日志会记录所有 SQL，性能开销极大。仅用于临时排查问题，生产环境不要长期开启。

三、存储过程：谨慎使用

存储过程（Stored Procedure）是把一组 SQL 语句预编译后存储在数据库内部，通过 CALL 调用的程序单元。它能减少网络往返、封装固定逻辑，但在现代工程实践中已不再被推荐作为首选方案。

为什么不推荐重度使用存储过程？

适合使用存储过程的场景

• 数据迁移与 ETL：一次性、数据库内部的数据清洗、转换任务。
• 简单的批量操作：例如按固定规则批量归档历史数据。
• 强依赖数据库原子性的脚本：需要利用数据库事务保证一致性的短时脚本。

更推荐的替代方案

graph LR
    A[复杂业务逻辑] --> B[应用层 / Service 层实现]
    A --> C[ORM / MyBatis 等持久层框架]
    B --> D[便于测试、调试、版本控制]
    C --> D

• 应用层处理：把复杂逻辑放在 Java/Go/Python 等服务层，配合事务注解保证一致性。
• 批量 SQL：使用 INSERT ... SELECT 或 LOAD DATA 完成批量导入。
• 定时任务：使用调度框架（如 Quartz、XXL-JOB）替代存储过程做周期化数据处理。

避坑指南 #4：存储过程调试困难、不利于迁移，现代微服务架构中建议慎用。批量数据处理优先考虑在应用层分批插入，或使用 INSERT ... SELECT。

四、事务：ACID 与隔离级别

4.1 事务的 ACID 特性

graph TD
    A[事务] --> B[Atomicity 原子性]
    A --> C[Consistency 一致性]
    A --> D[Isolation 隔离性]
    A --> E[Durability 持久性]
    B --> F[Undo Log]
    E --> G[Redo Log]

4.2 Undo Log 与 Redo Log

4.2.1 Redo Log：崩溃恢复与“何时返回成功”

InnoDB 使用 WAL（Write-Ahead Logging） 保证持久性：事务提交前先把修改写到 Redo Log，真正的脏页可以延迟异步刷回磁盘。核心流程如下：

sequenceDiagram
    participant App as 应用/客户端
    participant MySQL as MySQL Server
    participant BP as Buffer Pool
    participant Redo as Redo Log Buffer / 文件
    participant Disk as 磁盘数据文件

    App->>MySQL: BEGIN
    App->>MySQL: UPDATE user SET age=20 WHERE id=1
    MySQL->>BP: 读取数据页并修改 age=20
    MySQL->>Redo: 写入 Redo Log：<br/>"将 id=1 的 age 改为 20"
    Note over Redo: WAL：先写日志，再写磁盘
    App->>MySQL: COMMIT
    MySQL->>Redo: 按策略刷新 Redo Log
    MySQL-->>App: 返回 Commit 成功
    Note over BP,Disk: 脏页由后台线程异步刷盘

Redo 写完后，MySQL 才告诉应用写完成了吗？

答案是：默认配置（innodb_flush_log_at_trx_commit = 1）下，是的。 MySQL 把 Redo Log fsync 到磁盘后，才会返回 COMMIT 成功。

关键结论：默认情况下，应用收到 COMMIT 成功，就意味着对应事务的 Redo Log 已经持久化到磁盘。此后即使 MySQL 立刻崩溃，重启时也能根据 Redo Log 恢复这条已提交事务；如果崩溃发生在 fsync 之前，则该事务会被回滚或视为未提交。

崩溃恢复流程：

flowchart TD
    A[MySQL 崩溃后重启] --> B[加载磁盘 Redo Log]
    B --> C[找到上一次 Checkpoint LSN]
    C --> D[扫描 Checkpoint 之后的 Redo 记录]
    D --> E{Redo 记录对应页<br/>是否需要重放?}
    E -->|Redo LSN > 页 LSN| F[重放该条 Redo]
    F --> G[恢复已提交事务的修改]
    E -->|已落盘，无需重放| H[跳过]
    G --> I[恢复完成，数据库可用]
    H --> I
    J[崩溃时 Redo 尚未 fsync] --> K[事务视为未提交<br/>不会恢复]

注意：Redo Log 是物理逻辑日志，恢复时按 LSN 顺序重放。InnoDB 会对比 Redo 记录与数据页上的 LSN，避免对已经落盘的页重复应用，保证幂等性。

4.2.2 Undo Log：回滚是如何实现的？

Redo 记录“新值”用于崩溃后重做；Undo 记录“旧值”用于事务回滚和 MVCC。

Undo Log 回滚流程：

flowchart TD
    A[事务开始 BEGIN] --> B[执行 UPDATE / DELETE]
    B --> C[生成 Undo Log<br/>记录修改前的旧值]
    C --> D[修改 Buffer Pool 数据页]
    D --> E{事务结果}
    E -->|ROLLBACK| F[读取本事务的 Undo Log]
    F --> G[用旧值覆盖 Buffer Pool 中的修改]
    G --> H[释放事务持有的锁]
    H --> I[事务结束，修改被撤销]
    E -->|COMMIT| J[Redo Log 持久化后提交]
    J --> K[Undo Log 标记为可清理]
    K --> L[Purge 线程异步回收]
    L --> M[事务结束]

Undo Log 的两个核心作用：

1. 事务回滚：ROLLBACK 或语句失败时，InnoDB 沿着 Undo Log 把修改逐项撤销，恢复数据到事务开始前的状态，保证原子性。
2. MVCC 一致性读：RR/RC 级别下，普通 SELECT 不加锁，而是通过 Undo Log 链构造事务开始时可见的数据版本（ReadView），实现无锁并发读。

注意：Undo Log 的修改本身也受到 Redo Log 保护。崩溃恢复时，Undo Log 页也会通过 Redo Log 重建，否则回滚能力会丢失。

注意：事务提交后，Undo Log 不会立即删除。如果其他事务还需要用它构造快照，Undo Log 会继续保留，直到没有事务依赖该旧版本后，Purge 线程才会回收。长事务会导致 Undo 堆积、表空间膨胀。

4.3 事务的并发问题

避坑指南 #5：不可重复读侧重“修改”，幻读侧重“新增或删除”。InnoDB 默认的可重复读通过 MVCC + 间隙锁（Gap Lock）在很大程度上解决了幻读。

4.4 四种隔离级别

graph LR
    A[读未提交<br>Read Uncommitted] --> B[读已提交<br>Read Committed]
    B --> C[可重复读<br>Repeatable Read]
    C --> D[串行化<br>Serializable]
    style C fill:#e1f5e1

避坑指南 #6：MySQL 默认是可重复读（RR），但很多互联网项目会改为读已提交（RC），因为 RC 锁粒度更小、并发更高。但改为 RC 后，需要业务层自行处理不可重复读问题。

五、索引类型

索引是 MySQL 高性能的基石。理解索引类型与适用场景，比单纯记住语法更重要。

graph TD
    A[MySQL 索引] --> B[普通索引]
    A --> C[唯一索引]
    A --> D[主键索引]
    A --> E[组合索引]
    A --> F[全文索引]
    D --> G[聚簇索引：数据与索引在一起]
    B --> H[二级索引：叶子节点存主键]

5.1 普通索引

CREATE INDEX index_name ON table(column(length));

5.2 唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length));

5.3 主键索引

主键索引是一种特殊的唯一索引，一张表只能有一个主键，且不允许为空。

5.4 组合索引

CREATE INDEX idx_name_age ON user(name, age);

避坑指南 #7：组合索引遵循最左前缀原则。idx_name_age 对 name、name+age 有效，但对单独 age 无效。

5.5 全文索引

适用于 LIKE '%关键词%' 场景，MyISAM 和 InnoDB 5.6+ 都支持。

六、锁机制与 MVCC

6.1 共享锁与排他锁

graph LR
    A[共享锁 S] -- 兼容 --> B[共享锁 S]
    A -- 互斥 --> C[排他锁 X]
    C -- 互斥 --> D[排他锁 X]

避坑指南 #8：只有 INSERT / UPDATE / DELETE 会自动加排他锁；普通 SELECT 不加锁。如果需要保证读取的是最新已提交数据并防止被修改，应显式使用 SELECT ... FOR UPDATE。

6.2 MVCC 实现可重复读

sequenceDiagram
    participant T1 as 事务 A
    participant DB as InnoDB
    participant T2 as 事务 B

    T1->>DB: START TRANSACTION<br>创建 ReadView
    T2->>DB: UPDATE row SET x=2<br>并提交
    T1->>DB: SELECT x<br>仍读取快照中的 x=1
    Note over T1,DB: 事务 A 始终使用开始时的 ReadView

MVCC 不是复制一份数据，而是通过 ReadView + 数据行隐藏字段 + Undo Log 版本链，来判断当前事务应该看到哪个版本的数据。

6.2.1 数据行的两个隐藏字段

InnoDB 每行数据都有两个隐藏字段：

• DB_TRX_ID：最后修改这行的事务 ID。
• DB_ROLL_PTR：回滚指针，指向这行数据在 Undo Log 中的上一个版本。

6.2.2 ReadView 里面有什么？

ReadView 是一个轻量的结构，不保存任何实际数据，只保存生成时刻的事务状态：

6.2.3 可见性判断算法

对于查询到的一行数据，取出它的 DB_TRX_ID，按以下规则判断：

1. DB_TRX_ID == creator_trx_id：当前事务自己修改的，可见。
2. DB_TRX_ID < min_trx_id：在 ReadView 创建前已提交，可见。
3. DB_TRX_ID >= max_trx_id：在 ReadView 创建后才启动的事务修改的，不可见。
4. min_trx_id <= DB_TRX_ID < max_trx_id：
   • DB_TRX_ID 在 m_ids 列表中 → 事务未提交，不可见。
   • DB_TRX_ID 不在 m_ids 列表中 → 事务已提交，可见。

如果当前版本不可见，就沿着 DB_ROLL_PTR 到 Undo Log 链中找上一个版本，重复判断，直到找到可见版本。

flowchart TD
    A[SELECT 读取一行] --> B[获取 DB_TRX_ID]
    B --> C{可见性判断}
    C -->|可见| D[返回当前版本]
    C -->|不可见| E[沿 DB_ROLL_PTR 读 Undo Log 上一个版本]
    E --> C

6.2.4 一个具体例子

假设 id=1 的行初始值为 x=1，事务时间线如下：

事务 30 生成的 ReadView：

• m_ids = [10, 20]
• min_trx_id = 10
• max_trx_id = 31
• creator_trx_id = 30

查询时，当前行最新版本的 DB_TRX_ID = 20：

• 20 在 m_ids 中 → 未提交 → 不可见。
• 沿 Undo Log 找到上一个版本，DB_TRX_ID = 10。
• 10 在 m_ids 中 → 未提交 → 不可见。
• 再沿 Undo Log 找到初始版本，DB_TRX_ID = 5（假设为初始插入）。
• 5 < min_trx_id 10 → 已提交 → 可见。

所以事务 30 读到的是 x=1。

6.2.5 RR 与 RC 的区别

• RR（可重复读）：ReadView 在事务的第一次 SELECT 时创建，之后整个事务复用该 ReadView。因此同一个事务内多次查询结果一致。
• RC（读已提交）：每次 SELECT 都会创建新的 ReadView，所以能看到其他事务已经提交的最新数据。

避坑指南 #9：RR 级别下，事务开始后创建一致性视图，后续查询都基于该快照。FOR UPDATE 可以读到最新数据，同时解决第二类更新丢失问题。

七、MySQL 优化四大方向

graph LR
    A[MySQL 优化] --> B[设计层面]
    A --> C[功能层面]
    A --> D[架构层面]
    A --> E[SQL 层面]
    B --> B1[存储引擎 / 字段类型 / 范式]
    C --> C1[索引 / 缓存 / 分区分表]
    D --> D1[主从复制 / 读写分离 / 负载均衡]
    E --> E1[慢 SQL 分析 / 执行计划]

7.1 存储引擎选择

避坑指南 #10：MySQL 5.5+ 默认引擎是 InnoDB，新项目无脑选 InnoDB。除非有非常明确的只读/归档场景，否则不要用 MyISAM。

7.2 字段类型选择原则

7.3 数据库三大范式

graph LR
    A[1NF 原子性] --> B[2NF 消除部分依赖]
    B --> C[3NF 消除传递依赖]

避坑指南 #11：不要过度范式化。互联网业务为了查询性能，常采用逆范式设计，允许适当冗余，用空间换时间。

7.4 功能层面优化：索引、缓存、分区分表

功能层面优化是在不改变核心业务表结构的前提下，通过索引、缓存、分区分表等手段提升性能。

索引优化

索引是最常用的功能优化手段。核心原则：

• WHERE、JOIN、ORDER BY 字段建立索引。
• 覆盖索引：查询字段都在索引中，避免回表。
• 最左前缀：组合索引按使用频率和字段顺序设计。
• 避免索引失效：如函数操作索引列、隐式类型转换、前模糊 LIKE、OR 半边无索引等。

缓存策略

• 查询缓存：MySQL 8.0 已移除，不建议依赖。
• 应用层缓存：Redis / Memcached 缓存热点数据，减少数据库压力。
• 注意缓存一致性：写数据库后要及时失效或更新缓存，避免脏读。

分区分表

当单表数据量过大（通常超过 500 万 - 1000 万行，或单表超过 10GB）时，需要考虑拆分。主要分为两种：

分区（Partition）

分区是 MySQL 提供的原生功能，一张逻辑表由多个物理分区组成，每个分区独立存储。

常见分区类型：

创建分区表示例（RANGE）：

CREATE TABLE user_log (
    id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    action VARCHAR(50),
    created_at DATETIME NOT NULL
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

分区表的增删改查：

-- 查询整个表（和普通表一样）
SELECT * FROM user_log WHERE created_at >= '2024-01-01';

-- 指定分区查询（强制扫描某个分区）
SELECT * FROM user_log PARTITION (p2024) WHERE action = 'login';

-- 插入数据（MySQL 自动根据分区键路由）
INSERT INTO user_log VALUES (1, 10086, 'login', '2024-06-01 10:00:00');

-- 更新数据（如果修改分区键，可能需要跨分区移动行）
UPDATE user_log SET created_at = '2025-01-01' WHERE id = 1;

-- 删除整个分区的数据（比分批 DELETE 快得多）
ALTER TABLE user_log DROP PARTITION p2022;

分区表的关键点：

• 分区键必须包含在唯一索引/主键中。例如主键是 (id)，那么不能按 created_at 分区，除非主键改成 (id, created_at)。
• 查询要命中分区键才能裁剪。如果 WHERE 里没有分区键，MySQL 会扫描所有分区，反而更慢。
• 分区数量不宜过多。MySQL 官方建议单表分区数控制在 1000 以内，过多会消耗文件描述符和内存。

分表（Sharding）

分表是把一张逻辑表拆成多张物理表，通常需要配合分库分表中间件（如 ShardingSphere、MyCat）或应用层路由。

水平拆分策略：

分库分表带来的问题：

• 分布式事务：跨库事务无法靠单机 ACID 保证。
• 跨库 JOIN：需要在应用层聚合，或反范式冗余字段。
• 全局唯一 ID：不能用自增主键，需要雪花算法、号段等方案。
• 分页排序：ORDER BY + LIMIT 跨多表后合并复杂。
• 数据迁移：扩容时需要平滑迁移历史数据。

避坑指南 #14：不要为了拆分而拆分。大多数业务先做好索引、SQL 优化、加缓存，单表撑到千万级没问题。拆分成本：分区 < 分表 < 分库。优先考虑分区；分区也撑不住时，再考虑分库分表。

7.5 SQL 层面优化：慢 SQL 分析与执行计划

SQL 层面的优化收益往往最高，因为一条烂 SQL 可以把整个数据库拖垮。

如何发现慢 SQL

• 慢查询日志：slow_query_log = ON，long_query_time 设置阈值。
• performance_schema：MySQL 内置性能监控。
• pt-query-digest：Percona Toolkit 分析慢日志。
• 应用层 APM：SkyWalking、Pinpoint 等追踪 SQL 耗时。

EXPLAIN 看执行计划

EXPLAIN SELECT * FROM `order` WHERE age > 18 ORDER BY created_at LIMIT 10;

关键字段：

type 详解：

type 表示 MySQL 访问表的方式，可以理解为“查资料时翻书的方式”。

各 type 详细说明与示例：

1. system：表本身就是常量

表里只有 0 条或 1 条数据，MySQL 把它当成常量处理。

SELECT * FROM system_config;

如果 system_config 只有一条记录，EXPLAIN 可能看到 type = system。实际业务表中很少见。

2. const：精确命中唯一值

通过主键或唯一索引做等值查询，最多返回 1 行。

-- id 是主键
SELECT * FROM user WHERE id = 10086;

-- phone 是唯一索引
SELECT * FROM user WHERE phone = '13800138000';

这是单表查询里最好的 type。

3. eq_ref：JOIN 中的 const

多表 JOIN 时，被驱动表通过主键或唯一索引与驱动表匹配，每次匹配最多返回 1 行。

SELECT o.*, u.name
FROM `order` o
JOIN user u ON o.user_id = u.id;

假设 user.id 是主键，order 表的每一行去 user 表查都是精确命中 1 行，此时 user 表的 type = eq_ref。

4. ref：普通索引等值查询

通过普通索引（非唯一索引）做等值查询，可能返回多条。

-- status 是普通索引
SELECT * FROM `order` WHERE status = 'PAID';

status = 'PAID' 可能对应很多订单。这是生产环境最常见的良好 type。

5. range：索引范围扫描

使用索引做范围查询。

-- age 有索引
SELECT * FROM user WHERE age > 18;

-- create_time 有索引
SELECT * FROM `order`
WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31';

-- id 是主键
SELECT * FROM user WHERE id IN (1, 2, 3, 4);

-- phone 有索引
SELECT * FROM user WHERE phone LIKE '1380%';

注意：LIKE '1380%' 走 range；LIKE '%1380' 或 LIKE '%1380%' 会退化成 ALL。

6. index：全索引扫描

遍历整个索引树，而不是全表扫描数据。

-- name 是索引列
SELECT name FROM user;

-- 按索引列排序
SELECT id FROM user ORDER BY id;

因为索引通常比实际数据小，读索引比读整行快。但如果查询列不在索引里或需要大量回表，index 也会很慢。

7. ALL：全表扫描

没有用到索引，逐行读取整张表。

-- phone 没有索引
SELECT * FROM user WHERE phone = '13800138000';

-- 对索引列使用函数
SELECT * FROM `order` WHERE DATE(create_time) = '2024-01-01';

-- 前模糊 LIKE
SELECT * FROM user WHERE phone LIKE '%1380%';

生产环境出现 type = ALL 通常需要优化。

简单记忆口诀：

system/const 是精确，一行数据最省事；
eq_ref 是 JOIN 版 const，主键关联一对一；
ref 普通索引等值查，可能返回好几行；
range 范围扫描用得广，BETWEEN IN 都能上；
index 全扫索引比表强；
ALL 全表扫描要提防。

生产环境建议：

一般原则：单表查询尽量做到 ref 或更好，范围查询 range 可接受，尽量避免 ALL 和大量数据的 index。

危险的 Extra：

• Using filesort：需要额外排序，常见于无索引的 ORDER BY。
• Using temporary：需要创建临时表，常见于 GROUP BY、DISTINCT。
• Using index：好现象，使用了覆盖索引。
• Using where：在存储引擎返回数据后再过滤。

典型慢 SQL 写法与严重踩坑案例

案例 1：SELECT *

SELECT * FROM `order` WHERE user_id = 10086;

问题：读取不需要的列，增加网络传输和 Buffer Pool 占用，容易触发回表。

优化：

SELECT order_id, status, amount FROM `order` WHERE user_id = 10086;

案例 2：前模糊 LIKE 导致索引失效

SELECT * FROM user WHERE phone LIKE '%1380%';

问题：% 在开头无法使用 B+ 树索引，会全表扫描。

优化：

• 改为后缀匹配：LIKE '1380%'（如果有索引仍可用）。
• 使用全文索引或 Elasticsearch。

案例 3：OR 条件半边无索引

SELECT * FROM user WHERE phone = '13800138000' OR email = 'a@b.com';

问题：如果 email 没有索引，MySQL 可能放弃索引，直接全表扫描。

优化：

SELECT * FROM user WHERE phone = '13800138000'
UNION ALL
SELECT * FROM user WHERE email = 'a@b.com';

或给 email 也建索引。

案例 4：隐式类型转换

SELECT * FROM user WHERE phone = 13800138000;

问题：phone 是 varchar，MySQL 会先把所有行的 phone 转成整数再比较，索引失效。

优化：

SELECT * FROM user WHERE phone = '13800138000';

案例 5：函数操作索引列

SELECT * FROM `order` WHERE DATE(create_time) = '2024-01-01';

问题：对索引列使用函数，DATE(create_time) 不是索引本身，索引失效。

优化：

SELECT * FROM `order`
WHERE create_time >= '2024-01-01'
  AND create_time < '2024-01-02';

案例 6：大 OFFSET 深分页

SELECT * FROM `order` ORDER BY id LIMIT 1000000, 10;

问题：MySQL 需要先扫描 1000010 行，再扔掉前面的 1000000 行，越往后越慢。

优化（延迟关联）：

SELECT o.*
FROM `order` o
JOIN (SELECT id FROM `order` ORDER BY id LIMIT 1000000, 10) tmp ON o.id = tmp.id;

或记录上次位置：

SELECT * FROM `order` WHERE id > ? ORDER BY id LIMIT 10;

案例 7：IN 子句元素过多

SELECT * FROM user WHERE id IN (1, 2, 3, ..., 50000);

问题：IN 元素过多会导致解析和执行效率下降，甚至超出限制。

优化：分批查询，或把 ID 写入临时表再 JOIN。

案例 8：大事务长时间持有锁

START TRANSACTION;
-- 大量更新操作
UPDATE huge_table SET ... WHERE ...;
-- 业务逻辑处理
-- 几分钟后才 COMMIT

问题：长时间持有行锁/间隙锁，阻塞其他事务，Undo Log 堆积，主从延迟放大。

优化：拆分为小批量事务，减少单次锁定范围。

线上最严重的几个踩坑

避坑指南 #15：上线任何 SQL 前，先用 EXPLAIN 检查执行计划。看到 type = ALL、Extra = Using filesort 或 Using temporary 时，必须优化后再上线。

八、高性能架构设计

8.1 主从复制 + 读写分离

graph LR
    A[写请求] --> B[主库 Master]
    B -->|binlog 同步| C[从库 Slave 1]
    B -->|binlog 同步| D[从库 Slave 2]
    E[读请求] --> C
    E --> D

避坑指南 #12：主从复制存在延迟。如果对实时性要求极高（如支付后立刻查询余额），读请求应强制走主库，或在业务层做延迟容忍设计。

8.2 分区分表

九、用户权限与数据导出

9.1 创建新用户并授权

-- 创建用户
CREATE USER 'goose_vod'@'%' IDENTIFIED BY 'xxxx';

-- 授权
GRANT ALL PRIVILEGES ON `ad_db`.* TO `goose_vod`@`%`;

-- 刷新权限
FLUSH PRIVILEGES;

避坑指南 #13：生产环境请遵循最小权限原则，不要给业务账号 ALL PRIVILEGES。例如只读账号只授予 SELECT。

9.2 导出数据库

mysqldump -h xxxx-rds.xxxx.us-east-1.rds.amazonaws.com \
  -u goose_vod -p appstore_db > ~/database_dump.txt

十、避坑清单速查表

十一、总结

MySQL 的学习路径可以概括为：

graph LR
    A[熟练 SQL 与安装配置] --> B[理解事务与隔离级别]
    B --> C[掌握索引设计与优化]
    C --> D[理解锁与 MVCC]
    D --> E[掌握主从复制与读写分离]
    E --> F[持续分析慢 SQL 与执行计划]

高性能 MySQL 没有银弹，核心在于：合理的表结构设计、精准的索引、恰到好处的事务隔离、以及能够水平扩展的架构。希望这份笔记能成为你日常开发与面试复习的速查手册。

参考

• MySQL 5.7 安装教程
• MySQL 优化四大方面
• MySQL 锁与事务详解

• 数据库

• MySQL
• 数据库
• SQL
• 索引
• 事务
• 性能优化

方糖猫（大可）

18 年后端架构，现在独立做 AI 产品。这里记录 AI 视频、AI Agent 与工程实践。