MySQL 原理 + 运维急救指南：从一条 SQL 看透数据库，特殊故障不再慌

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

凌晨 3 点，告警群里弹出：
  "生产数据库 CPU 100%！"

早上 9 点，你发现：
  "为什么一条 SELECT 跑了一分钟？"

下午 2 点，业务反馈：
  "订单状态不一致！"

深夜 11 点，老板来电：
  "数据库主从挂了！"

你看着屏幕上满屏的 SQL 语句，想找一个准确的排查方向，
却发现自己对 MySQL 的原理"似懂非懂"。

1
2
3
4
5
6
7
8

第一条线（原理）：
  一条 SELECT 怎么从你敲下回车那一刻，
  一路穿过连接器、分析器、优化器、执行器、InnoDB，
  最终把数据交到你手里。

第二条线（实战）：
  运维中 10 类最常见的"诡异故障"，
  每一种告诉你：现象、原因、排查思路、解决方案。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

你（客户端）
   │
   │ 寄一个包裹："SELECT * FROM user WHERE id=1"
   │
   ▼
┌─────────────────────────────────────────┐
│  快递站：MySQL                           │
│                                          │
│  大门口：连接器（验证身份证）             │
│      │                                   │
│      ▼                                   │
│  客服台：分析器（"你这个包裹要寄到哪？"）│
│      │                                   │
│      ▼                                   │
│  路线规划：优化器（"走哪条路最快？"）    │
│      │                                   │
│      ▼                                   │
│  仓库工人：执行器（"去货架取货！"）      │
│      │                                   │
│      ▼                                   │
│  货架本身：InnoDB（"数据存放在哪"）     │
└─────────────────────────────────────────┘
   │
   ▼
你收到包裹："id=1, name=小明"

1
2
3

你：SELECT * FROM user WHERE id=1
                         ↑
                建立连接（Connection）

1
2
3

1. 验证身份（用户名 + 密码）
2. 验证权限（这个用户能 SELECT user 表吗？）
3. 建立连接（分配线程、内存）

1
2
3
4
5
6
7
8
9

# 查看当前连接
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  5 | app  | 10.0.0.1  | shop | Query   |    0 | init  | SHOW PROCESSLIST |
|  8 | app  | 10.0.0.1  | shop | Sleep   |  120 |       |                  |
| 10 | app  | 10.0.0.1  | shop | Query   |    3 |       | SELECT * FROM...  |
+----+------+-----------+------+---------+------+-------+------------------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

分析器做的事情：

1. 词法分析
   SELECT → 关键字
   * → 通配符
   FROM → 关键字
   user → 表名
   WHERE → 关键字
   id=1 → 条件表达式

2. 语法分析
   检查语法对不对（你写的 SQL 是否符合 MySQL 语法规范）
   如果写错了，会报错：
     ERROR 1064 (42000): You have an error in your SQL syntax

1
2
3
4
5
6
7
8

SQL 写好了，但"怎么执行最快"还没定。
比如 SELECT * FROM user WHERE id=1 AND name='张三'

可能的执行方式：
  A. 先用 id=1 找到用户（id 有索引），再判断 name='张三'
  B. 先用 name='张三' 找到用户（name 有索引），再判断 id=1

哪个更快？优化器决定。

1
2
3
4
5

1. 决定用哪个索引
2. 决定表的连接顺序（多表 JOIN 时）
3. 决定 SQL 的执行计划

→ 优化器输出一个"执行计划"（EXPLAIN）

1
2
3
4
5
6
7
8

-- 看 SQL 的执行计划
EXPLAIN SELECT * FROM user WHERE id=1;

+----+-------------+-------+-------+-------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | PRIMARY     | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+-------------+---------+---------+-------+------+-------+

1
2
3
4
5
6

拿到执行计划后，执行器"真正执行"：

1. 打开表
2. 调用 InnoDB 引擎读数据
3. 逐行判断 WHERE 条件
4. 返回结果给客户端

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 查看执行器工作状态
mysql> SHOW STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_read_first         | 1     |
| Handler_read_key           | 1     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 1     |
+----------------------------+-------+

1
2
3
4
5
6
7

InnoDB 的核心组件：
  ├── Buffer Pool（缓冲池）：内存中的"数据货架"
  ├── Undo Log（回滚日志）：事务回滚用
  ├── Redo Log（重做日志）：崩溃恢复用
  ├── Binlog（归档日志）：主从复制用
  ├── B+ 树索引：数据组织方式
  └── 行锁 / 间隙锁：并发控制

1
2
3
4
5

表空间 (Tablespace)
  └── 段 (Segment)
        └── 区 (Extent) ：1MB = 64 个页
              └── 页 (Page) ：16KB（InnoDB 最小 IO 单位）
                    └── 行 (Row) ：真正存数据

1
2
3
4
5
6
7

# 查看 innodb_page_size
mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| innodb_page_size | 16384 |
+---------------+-------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT * FROM user WHERE id=1;

没有索引：
  → 全表扫描，逐行检查
  → 最坏情况读 100 万行（每次读 16KB 一页）
  → 共读 ~16GB 的 IO
  → 慢！

有索引（主键索引）：
  → B+ 树查找
  → 3 层 B+ 树（约 100 万行）只需 3 次 IO
  → 共读 48KB
  → 快！

1
2
3
4
5
6
7
8
9

根节点（Root）：存 key 和指针
  │
  ├── 内部节点（Internal）：存 key 和指针
  │      │
  │      ├── 叶子节点（Leaf）：存数据
  │      ├── 叶子节点（Leaf）：存数据
  │      └── 叶子节点（Leaf）：存数据
  │
  叶子节点之间通过指针连起来（这就是"范围查询快"的原因）

1
2
3
4
5
6
7
8
9

聚簇索引（Clustered Index）：
  - 主键索引就是聚簇索引
  - 数据行就存在叶子节点
  - 一张表只有一个聚簇索引

非聚簇索引（Secondary Index）：
  - 普通索引（KEY index_name）
  - 叶子节点存主键值，不存完整数据
  - 查询需要"回表"：先查非聚簇索引拿到主键，再去聚簇索引查完整数据

1
2
3
4
5

SELECT * FROM user WHERE id=1
  → 主键索引，叶子节点就是数据 → 不需要回表

SELECT * FROM user WHERE name='小明'
  → 普通索引，叶子节点是 id=1 → 回表去聚簇索引拿完整数据

1
2

InnoDB 把磁盘上的"页（16KB）"读到内存的 Buffer Pool 里缓存。
下次再读同一页，直接从内存读，速度提升 ~1000 倍（内存 vs 磁盘）。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Buffer Pool 的工作机制：

读取数据：
  1. 先查 Buffer Pool
  2. 命中 → 直接返回（速度极快）
  3. 未命中 → 从磁盘加载到 Buffer Pool，再返回

修改数据：
  1. 修改 Buffer Pool 中的页（内存中修改）
  2. 标记为"脏页"（dirty page）
  3. 后台线程异步刷回磁盘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 查看 Buffer Pool 状态
mysql> SHOW ENGINE INNODB STATUS\G

BUFFER POOL AND MEMORY
----------------------
Buffer pool size   8192   ← 页数（每页 16KB = 128MB）
Free buffers       1024
Database pages     7000
Old database pages 2580
Modified db pages  15    ← 脏页数

1
2
3
4
5
6
7
8
9

# my.cnf 推荐配置
[mysqld]
# 服务器内存的 50-70% 分配给 Buffer Pool
innodb_buffer_pool_size = 8G
# 多实例（高并发用）
innodb_buffer_pool_instances = 4
# 预热（重启时自动加载热点数据）
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

1
2
3
4
5
6

事务回滚用：
  BEGIN;
  UPDATE user SET name='李四' WHERE id=1;
  -- 此时 InnoDB 在 Undo Log 里记下：id=1 原来 name='张三'
  ROLLBACK;
  -- 事务回滚时，从 Undo Log 恢复 name='张三'

1
2
3
4
5
6
7

崩溃恢复用：
  BEGIN;
  UPDATE user SET name='李四' WHERE id=1;
  -- InnoDB 把"id=1, name=张三 → 李四"这个修改记到 Redo Log
  -- 然后修改 Buffer Pool 中的页
  COMMIT;
  -- 标记 Redo Log 已提交

1
2
3
4
5

崩溃发生时的恢复：
  1. MySQL 重启
  2. 扫描 Redo Log
  3. 把已提交但还没刷到磁盘的数据，重新刷回磁盘
  4. 保证数据不丢

1
2
3
4
5

# Redo Log 配置
[mysqld]
innodb_log_file_size = 1G          # 每个日志文件大小
innodb_log_files_in_group = 2      # 日志文件数量（共 2G Redo Log）
innodb_flush_log_at_trx_commit = 1 # 事务提交立刻刷 Redo Log

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

= 1（最安全）：
  每次事务提交，都把 Redo Log 立即刷到磁盘
  优点：即使断电，0 数据丢失
  缺点：性能最差（每次提交多一次 IO）

= 2（性能好，安全性略低）：
  事务提交时，Redo Log 写入 OS 缓存
  每秒刷一次磁盘
  优点：性能好
  缺点：断电可能丢失最后 1 秒的数据

= 0（性能最好，最不安全）：
  每秒刷一次 Redo Log 到磁盘
  优点：性能最佳
  缺点：断电可能丢失最后 1 秒的数据

1
2
3
4

主从复制用：
  主库：所有变更都写 Binlog
  从库：拉取主库的 Binlog，重放到自己机器上
  → 实现数据同步

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 查看 Binlog 格式
mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

-- 三种格式对比
ROW    ：记录每行的具体变更（最安全，数据一致性好，默认推荐）
STATEMENT：记录 SQL 语句（最小，但有不一致风险）
MIXED  ：混合模式（MySQL 自动选择）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

事务提交时，三个日志是怎么配合的？

  BEGIN;
  UPDATE user SET name='李四' WHERE id=1;
  
  COMMIT 触发以下流程：
    1. 写 Redo Log（prepare 状态）
    2. 写 Binlog
    3. 写 Redo Log（commit 状态）
    
  → 这就是"两阶段提交"

崩溃发生在哪一步？
  1 之前：数据不变
  1 后、2 之前：Redo Log prepare 状态，丢弃这个事务
  2 后、3 之前：Binlog 有记录，Redo Log 没有 commit → 提交事务
  3 之后：完整提交

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查看 Binlog 内容
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 查看 Binlog 文件列表
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
| mysql-bin.000002 |     10240 |
+------------------+-----------+

1
2
3
4
5
6
7
8

A 原子性（Atomicity）   ：事务要么全成功，要么全失败
                          → 靠 Undo Log
C 一致性（Consistency）  ：事务前后数据合法
                          → 靠应用层 + 数据库约束
I 隔离性（Isolation）    ：事务之间互不干扰
                          → 靠锁 + MVCC
D 持久性（Durability）   ：事务一旦提交，数据不丢
                          → 靠 Redo Log + Binlog

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

ANSI SQL 标准定义的 4 个隔离级别（由弱到强）：

READ UNCOMMITTED（读未提交）
  → 能读到其他事务未提交的数据（脏读）
  → 几乎不用

READ COMMITTED（读已提交，RC）
  → 只能读到已提交的数据
  → Oracle / PostgreSQL 默认

REPEATABLE READ（可重复读，RR）
  → 同一事务中，多次读同样数据结果一致
  → MySQL InnoDB 默认 ⭐

SERIALIZABLE（串行化）
  → 强制串行执行
  → 性能最差，最安全

1
2
3
4
5

                 脏读    不可重复读    幻读
READ UNCOMMITTED  有      有          有
READ COMMITTED    无      有          有
REPEATABLE READ   无      无          有 (InnoDB 通过 MVCC + 间隙锁避免)
SERIALIZABLE      无      无          无

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

MVCC = 每行数据有多个版本
      读不加锁，读写不冲突

具体实现：
  每行有两个隐藏字段：
    DB_TRX_ID : 创建或修改该行的事务 ID
    DB_ROLL_PTR: 指向 Undo Log 的指针
  
  一致性读：
    每次读都基于"读视图（Read View）"
    只读在 Read View 之前已提交的数据
    不读未提交的数据

1
2
3
4
5
6
7

-- 事务 A（RR 隔离）
BEGIN;
SELECT * FROM user WHERE id=1;  -- 读到 name='张三'
-- 此时事务 B 已经修改并提交了 name='李四'
SELECT * FROM user WHERE id=1;  -- 仍然读到 name='张三'（可重复读）
COMMIT;
-- 事务 A 结束后再读，读到 name='李四'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

行锁（Record Lock）：
  锁定单行数据
  InnoDB 默认行级锁
  
间隙锁（Gap Lock）：
  锁定一个范围，但不包括记录本身
  例如：WHERE id BETWEEN 10 AND 20，锁定 (10, 20) 区间
  
Next-Key Lock（行锁 + 间隙锁）：
  默认加锁方式
  既锁当前行，也锁前面的间隙
  防止"幻读"

表锁：
  LOCK TABLES user WRITE;
  整张表加锁，性能差
  MyISAM 默认是表锁

1
2
3
4
5
6
7
8

事务 1:                        事务 2:
BEGIN;                         BEGIN;
UPDATE user SET ... WHERE id=1;
                                UPDATE user SET ... WHERE id=2;
UPDATE user SET ... WHERE id=2;
                                UPDATE user SET ... WHERE id=1;
                                -- 死锁！等待对方释放
-- 死锁！等待对方释放

1
2

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

1. 按固定顺序加锁
   （所有事务都先锁 id=1 再锁 id=2）

2. 减小事务范围
   （大事务拆成小事务）

3. 减少并发
   （降低同时运行的写入事务数）

4. 设置锁等待超时
   SET innodb_lock_wait_timeout = 5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- MySQL 8.0+
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 查谁在锁谁
SELECT 
    blocking_trx_id,
    blocking_pid,
    waiting_trx_id,
    waiting_pid,
    waiting_query
FROM performance_schema.data_lock_waits
JOIN performance_schema.threads ...

1
2
3
4

-- MySQL 5.7
SELECT * FROM information_schema.INNODB_TRX\G
SELECT * FROM information_schema.INNODB_LOCKS\G
SELECT * FROM information_schema.INNODB_LOCK_WAITS\G

1
2
3
4

1. 读写分离：主库写，从库读 → 提升读性能
2. 数据备份：从库是主库的实时备份
3. 高可用：主库挂了，从库切换顶替
4. 数据分析：报表、统计在从库跑，不影响主库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

主库（Master）                  从库（Slave）
    │                              │
    │ 1. 主库写数据 → 写 Binlog    │
    │                              │
    │ 2. Binlog Dump Thread       │
    │   把 Binlog 推给从库         │
    │ ────────────────────────────→│
    │                              │
    │                       3. Slave IO Thread
    │                          接收 Binlog，写到 Relay Log
    │                              │
    │                       4. Slave SQL Thread
    │                          读 Relay Log，重放到从库
    │                              │

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

主从复制三线程：
  Master Binlog Dump Thread：
    - 主库启动一个线程
    - 负责把 Binlog 推给从库
    
  Slave IO Thread：
    - 从库启动一个线程
    - 接收主库 Binlog，写入本地 Relay Log
    
  Slave SQL Thread：
    - 从库启动一个线程
    - 读取 Relay Log，执行 SQL 重放

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

原因：
  - 主库高并发写入
  - 从库单线程重放（MySQL 5.7 之前）
  - 网络延迟
  - 从库硬件差

解决方案：
  1. MySQL 5.7+ 开启多线程复制
     SET GLOBAL slave_parallel_workers = 8;
     
  2. MySQL 8.0 开启 writeset 多线程
     SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;
  
  3. 升级到 MySQL 8.0+（默认就是并行复制）

1
2
3
4
5

传统复制：基于 Binlog 文件名 + 位点（容易出错）
GTID 复制：基于全局事务 ID（更可靠）
  - 每个事务有唯一 ID
  - 主从自动协商从哪同步
  - 自动跳过已执行的事务

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# 主库配置
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin

# 从库配置
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin

# 从库 CHANGE MASTER TO
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;  # GTID 模式

1

ERROR 1040 (HY000): Too many connections

1
2

应用连接没释放（没显式调用 close() 或自动断连）
或某条慢查询把所有连接都占着

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 1. 查看连接总数
mysql> SHOW STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 998   |  ← 已经接近上限 1000
+-------------------+-------+

-- 2. 查看每个连接在干什么
mysql> SHOW PROCESSLIST;

-- 3. 看连接配置
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+

-- 4. 查看连接池使用情况
mysql> SHOW STATUS LIKE 'Max_used_connections%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Max_used_connections         | 1001  | ← 曾经爆满过
| Max_used_connections_time    | 14:23:01|
+------------------------------+-------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 紧急：临时提高上限
SET GLOBAL max_connections = 2000;

-- 根本：排查没释放的连接
-- 1. 应用代码检查连接池配置（确保 close 释放）
-- 2. 设置超时
SET GLOBAL wait_timeout = 300;       -- 空闲连接 5 分钟超时
SET GLOBAL interactive_timeout = 300;

-- 3. 配置连接池大小
-- HikariCP: maximumPoolSize = 20~50

1

某条原本很快的 SQL，今天突然跑了几十秒

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 1. 看是不是索引失效
EXPLAIN SELECT * FROM user WHERE name='小明';

-- 输出 type=ALL → 全表扫描！
+----+-------------+-------+------+---------------+------+---------+------+------+----------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra    |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 100万 | Using where|
+----+-------------+-------+------+---------------+------+---------+------+------+----------+

-- 2. 看表是不是被锁
SELECT * FROM information_schema.INNODB_LOCK_WAITS\G

-- 3. 看是不是 Buffer Pool 命中率下降
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_requests       | 1000000|
| Innodb_buffer_pool_reads               | 50000 | ← 磁盘读
+---------------------------------------+-------+
-- 命中率 = (读请求 - 磁盘读) / 读请求 = 95% 正常，低于 90% 异常

1
2
3
4
5
6
7
8

-- 1. 添加合适的索引
CREATE INDEX idx_name ON user(name);

-- 2. 分析表，更新统计信息
ANALYZE TABLE user;

-- 3. 强制走索引（极端情况）
SELECT * FROM user FORCE INDEX (idx_name) WHERE name='小明';

1
2

ERROR 1114 (HY000): The table 'user' is full
ERROR 1030 (HY000): Got error 28 from storage engine

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# 1. 查磁盘
df -h
# /dev/sda1       100G   100G    0G  100% /data

# 2. 查大文件
du -sh /var/lib/mysql/* | sort -h | tail -20

# 3. 查 Binlog
ls -lh /var/lib/mysql/mysql-bin.*
# -rw-r----- 1 mysql mysql 1.1G mysql-bin.000001
# -rw-r----- 1 mysql mysql 1.1G mysql-bin.000002
# ...

# 4. 查数据目录
du -sh /var/lib/mysql/ibdata1   # 系统表空间
du -sh /var/lib/mysql/your_db/* # 各库文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# 1. 紧急清理 Binlog（保留最近 7 天）
mysql> PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

# 2. 或立即清理到指定 Binlog
mysql> PURGE BINARY LOGS TO 'mysql-bin.000050';

# 3. 防止 Binlog 无限增长，配置自动过期
cat /etc/my.cnf
[mysqld]
expire_logs_days = 7          # MySQL 5.7
binlog_expire_logs_seconds = 604800  # MySQL 8.0（7 天）

# 4. 收缩 ibdata1（系统表空间）
# 这条不能在线做！
# MySQL 8.0+ 使用独立表空间 innodb_file_per_table=1（默认）

# 5. 删表前先 TRUNCATE（释放空间）
# 直接 DROP TABLE 不一定释放磁盘空间给 OS
TRUNCATE TABLE temp_data;

1
2

Last_SQL_Error: Could not execute Write_rows event
Duplicate entry '12345' for key 'PRIMARY'

1
2

-- 在从库上
mysql> SHOW SLAVE STATUS\G

1
2
3
4

Slave_IO_Running: Yes         ← IO 线程正常
Slave_SQL_Running: No        ← SQL 线程挂了！
Last_SQL_Error: ...           ← 看错误信息
Seconds_Behind_Master: NULL   ← 落后秒数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 情况 1：主键冲突
-- 跳过这条错误（数据一致性自己负责！）
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE SQL_THREAD;

-- 情况 2：中继日志损坏
STOP SLAVE;
CHANGE MASTER TO RELAY_LOG_FILE='...', RELAY_LOG_POS=...;
START SLAVE;

-- 情况 3：彻底重新同步
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO MASTER_HOST='...', MASTER_AUTO_POSITION=1;
START SLAVE;

-- 情况 4：GTID 跳过错误
SET GTID_NEXT = 'uuid:transaction_id';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

1
2
3
4
5
6

# Percona Toolkit 工具
# 检查数据一致性
pt-table-checksum --host=master --user=root --password=xxx --databases=your_db

# 修复不一致
pt-table-sync --execute --sync-to-master --host=slave --databases=your_db

1

从库落后主库 1 小时（Seconds_Behind_Master: 3600）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 查看延迟时间
mysql> SHOW SLAVE STATUS\G
Seconds_Behind_Master: 3600

-- 查看复制性能
mysql> SHOW STATUS LIKE 'Slave%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Slave_running              | ON       |
| Slave_retried_transactions | 0        |
| Slave_lag_time             | 00:00:00 |  ← MySQL 8.0 才有
+----------------------------+----------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 1. 开启并行复制（MySQL 8.0 默认开启）
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_parallel_type = LOGICAL_CLOCK;

-- 2. MySQL 8.0 writeset 并行
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;

-- 3. 拆分大事务
-- 一个事务包含 100 万行 UPDATE → 延迟巨大
-- 拆成 100 个事务，每个 1 万行 → 延迟降低

-- 4. 升级从库硬件
-- 从库 IO 慢 → 换 SSD
-- 从库 CPU 忙 → 加核

1

ERROR 144 (HY000): Table './shop/order' is marked as crashed

1
2
3
4
5
6
7
8

# 1. 检查哪些表损坏
mysqlcheck --all-databases --check

# 2. 详细检查
myisamchk --check /var/lib/mysql/shop/order.MYI

# InnoDB 表一般会自动恢复
# 但极端情况下可能有问题

1
2
3
4
5
6
7
8
9

# 1. MyISAM 表（简单粗暴）
myisamchk -r /var/lib/mysql/shop/order.MYI

# 2. InnoDB 表（重启数据库自动恢复）
# 如果恢复失败，进入强制恢复模式
cat /etc/my.cnf
[mysqld]
innodb_force_recovery = 1   # 从 1 开始试，最高 6
# 6 表示完全忽略错误，但可能丢数据

1
2
3
4
5
6
7

# innodb_force_recovery 取值：
1 (SRV_FORCE_IGNORE_CORRUPT)：忽略检查到的 corrupt 页
2 (SRV_FORCE_NO_BACKGROUND)：阻止主线程的运行
3 (SRV_FORCE_NO_TRX_UNDO)：不执行事务回滚
4 (SRV_FORCE_NO_IBUF_MERGE)：不执行插入缓冲合并
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)：不查看 undo log
6 (SRV_FORCE_NO_LOG_REDO)：不执行 redo log 前滚

1

数据库里有 user 表，但 user.ibd 文件被删了

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 1. 立即停止数据库
systemctl stop mysqld

# 2. 不允许再用 user 表（备份 .frm 文件，丢弃 .ibd）

# 3. 重启数据库
systemctl start mysqld

# 4. 重建表
mysqldump --skip-lock-tables --skip-extended-insert \
  --no-create-info -uroot -p your_db user > user_data.sql

mysql -uroot -p your_db < create_table.sql  # 重新建表
mysql -uroot -p your_db < user_data.sql    # 导入数据

1
2
3
4
5

# 1. 从 Binlog 恢复数据（最差情况）
mysqlbinlog mysql-bin.000001 | mysql -uroot -p

# 2. 从备份恢复（推荐）
# 用 xtrabackup / mysqldump 定期备份

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 1. 看错误日志（最重要）
tail -100 /var/log/mysqld.log
tail -100 /var/log/mysql/error.log

# 2. 常见错误信息：
# "Can't start server: Bind on TCP/IP port: Address already in use"
#   → 端口被占用，netstat -lnp | grep 3306 找出占用进程
# "Permission denied" 
#   → datadir 权限不对，chown -R mysql:mysql /var/lib/mysql
# "Table 'mysql.plugin' doesn't exist"
#   → mysql 初始化没完成，mysql_install_db

# 3. 检查端口
netstat -lnp | grep 3306
lsof -i :3306

# 4. 检查磁盘
df -h /var/lib/mysql

# 5. 检查内存
free -h
# 可能 OOM 被杀
dmesg | grep -i "killed process"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 重置 root 密码（如果忘记）
# 1. 停掉 mysqld
systemctl stop mysqld

# 2. 跳过授权启动
mysqld --skip-grant-tables --skip-networking &

# 3. 修改密码
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

# 4. 重启
systemctl restart mysqld

1
2

应用反馈："订单处理卡住了"
数据库中："事务 12345 一直未提交"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 1. 看所有活跃事务
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 12345
                 trx_state: RUNNING
               trx_started: 2026-07-25 14:23:01
                 trx_wait_started: NULL
       trx_mysql_thread_id: 8
               trx_query: UPDATE order SET status='paid' WHERE id=10001

-- 2. 看这个事务持有什么锁
mysql> SELECT * FROM information_schema.INNODB_LOCKS WHERE LOCK_TRX_ID = 12345;

-- 3. 看是不是阻塞了别人
mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 1. 如果是 KILL 应用的连接
-- 找到 trx_mysql_thread_id (如 8)
KILL 8;

-- 2. 如果是开发错误（事务中包含了非 DB 操作）
-- 重构应用代码：
--   BAD:
--     BEGIN;
--     UPDATE order SET ...;
--     sleep(10);  -- 业务逻辑里 sleep，事务一直开着！
--     UPDATE order SET ...;
--     COMMIT;
--   GOOD:
--     sleep(10);  -- 把 sleep 放事务外
--     BEGIN;
--     UPDATE order SET ...;
--     UPDATE order SET ...;
--     COMMIT;

-- 3. 设置超时机制
SET GLOBAL innodb_lock_wait_timeout = 10;

1

"今天误删了一个重要的表，怎么办？"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# 1. 不要慌，停止写入（避免覆盖 Binlog）

# 2. 找最近的备份
# 用 mysqldump / xtrabackup / 快照 等

# 3. 用 Binlog 恢复（精确到秒）
mysqlbinlog --start-datetime='2026-07-25 14:00:00' \
             --stop-datetime='2026-07-25 14:30:00' \
             mysql-bin.000001 | mysql -uroot -p

# 4. 用 flashback 工具回滚（针对 DELETE 误操作）
# GitHub: binlog2sql / MyFlash
python binlog2sql.py -u root -p xxx \
  --start-file='mysql-bin.000001' \
  --start-position=12345 \
  --stop-position=67890 \
  --sql-type=DELETE \
  --flashback

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

# 1. 启用 Binlog（必做）
[mysqld]
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30   # Binlog 至少保留 30 天

# 2. 启用 GTID（推荐）
gtid_mode = ON
enforce_gtid_consistency = ON

# 3. 定期全量备份
# xtrabackup 每天全备
0 3 * * * /usr/local/bin/xtrabackup_full.sh

# 4. Binlog 备份到独立存储
0 4 * * * rsync -av /var/lib/mysql/mysql-bin.* backup@backup-server:/mysql-bin/

# 5. 限速 DROP / TRUNCATE（运维侧）
-- 应用代码不开放 DROP 权限，使用专用账户
-- 主库禁用 DROP 权限：REVOKE DROP ON *.* FROM 'app'@'%'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 记录超过 1 秒的

-- 2. 看慢查询
mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_query_log_file | /var/lib/mysql/slow.log          |
+---------------------+----------------------------------+

-- 3. 用 mysqldumpslow 分析
mysqldumpslow -t 10 /var/lib/mysql/slow.log

-- 4. 用 pt-query-digest 分析（更专业）
pt-query-digest /var/lib/mysql/slow.log > report.txt

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 1. WHERE 字段建索引
-- BAD: SELECT * FROM user WHERE name='小明'
-- GOOD: ALTER TABLE user ADD INDEX idx_name (name);

-- 2. 联合索引顺序：左前缀原则
-- 索引 (a, b, c) 可以加速：
--   WHERE a = ?           ✓
--   WHERE a = ? AND b = ? ✓
--   WHERE a = ? AND b = ? AND c = ? ✓
--   WHERE b = ?           ✗ （无法使用索引）

-- 3. 索引选择性
-- 区分度高的列放前面
-- 性别（0/1）放最后，姓名（百万种）放前面

-- 4. 避免 SELECT * 
-- 只查需要的列，减少回表

-- 5. 函数/运算在 WHERE 上索引失效
-- BAD: WHERE YEAR(create_time) = 2026
-- GOOD: WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

[mysqld]
# === 基础配置 ===
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
server-id = 1

# === 连接 ===
max_connections = 1000
max_user_connections = 800
wait_timeout = 300
interactive_timeout = 300

# === 缓冲池（最重要） ===
innodb_buffer_pool_size = 服务器内存的 60-70%
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

# === 日志 ===
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 14

# === 慢查询 ===
slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON

# === 文件 ===
innodb_file_per_table = 1
innodb_open_files = 65535

# === 复制（主库） ===
server-id = 1
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON

# === 复制（从库） ===
server-id = 2
relay_log = relay-bin
log_slave_updates = ON
read_only = ON

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- 1. 当前线程
SHOW FULL PROCESSLIST;

-- 2. 实时等待事件（MySQL 8.0）
SELECT * FROM performance_schema.events_statements_history 
ORDER BY EVENT_ID DESC LIMIT 10;

-- 3. 各表 IO 情况
SELECT * FROM sys.io_global_by_file_by_latency;

-- 4. 各表占用空间
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    DATA_LENGTH / 1024 / 1024 AS data_mb,
    INDEX_LENGTH / 1024 / 1024 AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY DATA_LENGTH DESC
LIMIT 20;

-- 5. 未使用索引
SELECT * FROM sys.schema_unused_indexes;

-- 6. 慢查询 Top 10
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 10;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

□ 备份是否成功
  $ ls -lh /backup/mysql/

□ 主从延迟是否正常
  $ mysql -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind

□ Binlog 是否堆积
  $ ls /var/lib/mysql/mysql-bin.* | wc -l

□ Buffer Pool 命中率
  $ mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%';"

1
2
3
4
5
6
7
8

□ 慢查询分析
  $ pt-query-digest /var/lib/mysql/slow.log

□ 磁盘空间预测
  $ du -sh /var/lib/mysql/

□ 表膨胀检查
  $ mysql -e "SHOW TABLE STATUS\G" | grep -E "Data_free"

1
2
3
4
5
6
7

□ 全量备份演练（恢复测试）

□ Binlog 远程备份验证

□ 主从切换演练

□ 慢查询 review 和 SQL 优化

1
2
3
4
5
6
7

任何 UPDATE/INSERT/DELETE：
  1. 先写 Undo Log（用于回滚）
  2. 再写 Redo Log（prepare）
  3. 再写 Binlog
  4. Redo Log commit

崩溃时根据这三个日志的状态决定是否恢复事务

1
2
3
4
5
6

读数据：
  1. 先查 Buffer Pool（内存）
  2. 命中直接返回
  3. 不命中从磁盘加载

→ Buffer Pool 命中率应该 > 95%

1
2
3
4

主库写 Binlog
从库 IO Thread 拉 Binlog 写到 Relay Log
从库 SQL Thread 读 Relay Log 重放
→ 三步完成主从同步

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

遇到任何 MySQL 故障，按这个顺序：

1. 看现象
   - 错误信息
   - 监控指标
   - 性能数据

2. 找原因
   - 看慢查询
   - 看锁等待
   - 看磁盘/内存/CPU
   - 看主从状态

3. 解决问题
   - 临时止血（KILL 慢查询 / 扩容 / 重启）
   - 根本修复（加索引 / 调配置 / 修代码）
   - 预防复发（监控告警 / 限流 / 重构）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

原理线：
  一条 SELECT 怎么从你敲下回车那一刻，
  一路穿过连接器、分析器、优化器、执行器、InnoDB，
  最终把数据交到你手里。
  → 懂了原理，故障排查就有"思路"。

实战线：
  运维中 10 类最常见的"诡异故障"，
  每一种告诉你：现象、原因、排查思路、解决方案。
  → 有了预案，告警来了不慌张。

1
2
3
4
5

1. 数据存放：InnoDB 以 16KB 页为单位，B+ 树索引组织
2. 内存优化：Buffer Pool 把磁盘读写转化为内存读写
3. 事务保证：Redo Log + Undo Log + Binlog + 两阶段提交
4. 并发控制：MVCC + 锁（行锁、间隙锁、Next-Key Lock）
5. 数据复制：Binlog 推送，Relay Log 重放

1
2
3
4
5
6

"Buffer Pool 命中率多少？"
"是不是有慢查询？"
"是不是有死锁？"
"主从延迟是多少？"
"Binlog 是否在疯狂增长？"
"磁盘是不是快满了？"