MySQL 实战问题与避坑指南¶

MySQL 实战问题与避坑指南一句话口诀

本篇是 MySQL 专题的"工程调优总索引"——工程隐患为主，机制深挖归姊妹篇。

三条字段底线：字符集用 utf8mb4、金额用 DECIMAL 或 INT（分）、主键用 BIGINT UNSIGNED。

生产"双 1 配置"是主从不丢数据的前提：innodb_flush_log_at_trx_commit=1 + sync_binlog=1，任何异步化都是拿可靠性换 TPS。

连接池经验公式：(核数 × 2) + 磁盘数 ≈ 10~20 足矣；连接过多 = Context Switch 吃完 CPU。

遇到机制级疑问直接跳专题：本篇只给"现场速查 + 快速止损处方"，底层原因请移步对应姊妹文档。

📖 边界声明：本篇聚焦「实战场景下的已知坑点 + 快速解决方案」，以下主题请看对应专题文档，本文不重复展开：

事务四大特性 / MVCC / 隔离级别的机制 → 事务与并发控制

InnoDB 锁家族 / 间隙锁 / 死锁检测原理 → 锁机制与死锁

ALTER / pt-osc / gh-ost 在线DDL 工具的深度对比 → 在线DDL与大表变更

Binlog 格式 / 主从复制拓扑 / 半同步 / MGR → Binlog与主从复制

慢查询诊断 / EXPLAIN / 索引优化 → SQL执行与性能优化

1. 类比：实战避坑像医院的"急诊手册"¶

医院的急诊科不讲发病机理——那是内科病理课的事——只讲"见到这个症状，做什么动作"。本文就是 MySQL 的急诊科：

急诊科思维	本文对应做法
症状表 → 快速鉴别 → 处方	现象 → 根因 → 立刻可用的 SQL/配置
复杂病因（肿瘤学、免疫学）转专科门诊	机制深挖全部 📖 转姊妹文档
急救有 ABC 三步标准动作	每个坑提供：止损命令 + 防复发配置 + 复盘指标
床边常备救命药（肾上腺素等）	生产"双 1 配置"、`kill` / `pt-online-schema-change` 等
病历记录同类复发率	每个坑末尾给复盘 checklist防再犯

一句话：本文 19 个坑就是 19 张"急诊卡"——线上出问题先翻"现象"栏目定位，再按"处方"抄作业，理论问题 → 姊妹文档，工程问题 → 本文。

2. 它解决了什么问题？¶

理论知识和实战之间存在巨大鸿沟。很多问题在面试中能答出来，但在线上遇到时却不知道如何排查。本文总结了实际项目中最常踩的坑，以及对应的排查思路和解决方案。

3. 字符集与编码问题¶

坑1：emoji 表情存储失败¶

现象：用户昵称包含 emoji，插入数据库报错 Incorrect string value: '\xF0\x9F...'

根本原因：表或列使用了 utf8 字符集，而 MySQL 的 utf8 实际上是 utf8mb3，最多支持 3 字节，无法存储 4 字节的 emoji。

-- ❌ 错误：utf8 不支持 emoji（4字节字符）
CREATE TABLE user (
    nickname VARCHAR(50) CHARACTER SET utf8
);

-- ✅ 正确：使用 utf8mb4
CREATE TABLE user (
    nickname VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

-- 修复已有表
ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 同时修改连接字符集
SET NAMES utf8mb4;

注意：仅修改表还不够，还需要确保：

MySQL 服务端配置 character_set_server=utf8mb4
JDBC 连接串加 characterEncoding=UTF-8&useUnicode=true
连接池初始化时执行 SET NAMES utf8mb4

坑2：大小写不敏感导致数据重复¶

现象：用户名 Tom 和 tom 被认为是同一个用户，唯一索引冲突。

根本原因：utf8mb4_general_ci 和 utf8mb4_unicode_ci 都是大小写不敏感（ci = case insensitive）。

-- 查看当前排序规则
SHOW CREATE TABLE user;

-- ✅ 需要大小写敏感时，使用 _bin 或 _cs 排序规则
ALTER TABLE user MODIFY username VARCHAR(50) 
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

-- 或者查询时临时指定
SELECT * FROM user WHERE username COLLATE utf8mb4_bin = 'Tom';

4. 时间与时区问题¶

坑3：时间存储与读取相差 8 小时¶

现象：Java 存入的时间是 2024-01-01 10:00:00，从数据库读出来变成了 2024-01-01 02:00:00。

根本原因：MySQL 服务端时区是 UTC，而 Java 应用时区是 Asia/Shanghai（UTC+8），JDBC 连接没有指定时区。

# ✅ JDBC 连接串指定时区
spring.datasource.url=jdbc:mysql://localhost:3306/db?serverTimezone=Asia/Shanghai&useSSL=false

# 或者
spring.datasource.url=jdbc:mysql://localhost:3306/db?serverTimezone=GMT%2B8

-- 查看 MySQL 服务端时区
SHOW VARIABLES LIKE 'time_zone';
SHOW VARIABLES LIKE 'system_time_zone';

-- 临时修改（重启失效）
SET GLOBAL time_zone = '+8:00';
SET time_zone = '+8:00';

坑4：DATETIME vs TIMESTAMP 的选择¶

类型	范围	时区	存储大小	适用场景
`DATETIME`	1000-9999年	不转换，存什么取什么	8字节	业务时间（生日、预约时间）
`TIMESTAMP`	1970-2038年	自动转换为UTC存储	4字节	记录操作时间（create_time、update_time）

-- ✅ 推荐：create_time 和 update_time 用 TIMESTAMP，自动维护
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

注意：TIMESTAMP 的 2038 年问题——如果系统需要运行到 2038 年以后，应使用 DATETIME。

5. 自增主键问题¶

坑5：自增 ID 用完了¶

现象：INT 类型主键最大值约 21 亿，高并发写入的表可能几年就用完，插入报错 Duplicate entry '2147483647' for key 'PRIMARY'。

-- 查看当前自增值
SELECT AUTO_INCREMENT FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

-- ❌ 危险：INT 最大约 21 亿
id INT UNSIGNED AUTO_INCREMENT  -- 最大约 42 亿

-- ✅ 推荐：BIGINT，最大约 922 亿亿
id BIGINT UNSIGNED AUTO_INCREMENT

-- 紧急扩容（需要停写或低峰期操作）
ALTER TABLE your_table MODIFY id BIGINT UNSIGNED AUTO_INCREMENT;

坑6：自增 ID 不连续（删除后有空洞）¶

现象：删除了一些记录后，自增 ID 出现了跳跃，如 1, 2, 5, 6...

根本原因：这是正常现象。自增值只增不减，事务回滚也不会回退自增值（MySQL 8.0 之前自增值重启后可能重置）。

-- MySQL 8.0 之前：重启后自增值会重置为 max(id)+1
-- MySQL 8.0 之后：自增值持久化到 redo log，重启不会重置

-- 如果业务需要连续 ID（不推荐），可以用序列表
CREATE TABLE sequence (
    name VARCHAR(50) PRIMARY KEY,
    current_val BIGINT NOT NULL DEFAULT 0
);

建议：业务上不要依赖 ID 的连续性，ID 只是唯一标识符。

6. 大表操作问题¶

坑 7：大表加索引导致锁表¶

现象：对千万级大表执行 ALTER TABLE ADD INDEX，导致表被锁住，线上业务无法写入，持续数分钟甚至数小时。

快速处方：

试一下 Online DDL（MySQL 5.6+）：ALTER TABLE orders ADD INDEX idx_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;
生产环境建议用 pt-online-schema-change 或 gh-ost，在不锁表的前提下完成 DDL

📖 Online DDL / pt-osc / gh-ost 三者的适用场景、限制、参数调优深度对比详见在线DDL与大表变更，本文不重复。

坑 8：大表 DELETE 导致主从延迟¶

现象：一次性 DELETE FROM logs WHERE create_time < '2023-01-01' 删除几百万行，主从延迟飙升到几分钟。

快速处方：

-- ✅ 分批删除，循环直到 affected rows = 0
DELETE FROM logs WHERE create_time < '2023-01-01' LIMIT 1000;
-- 每批间略 sleep，给从库嗘息空间

📖 大事务的 binlog 落盘 / 从库回放延迟机制 / 归档表与分区表方案详见在线DDL与大表变更与 Binlog与主从复制，本文不重复展开。

7. 事务与锁的实战坑¶

坑 9：@Transactional 注解不生效¶

现象：加了 @Transactional，但方法抛异常后数据没有回滚。

快速排查清单：

序	检查点	恐急特征
1	同类内部调用（`this.xxx()`）	方法能进入，但事务未开
2	方法不是 `public`	Spring AOP 默认只代理 public
3	异常被 `try-catch` 吞掉	配合要求：抛出来或 `TransactionAspectSupport.currentTransactionStatus().setRollbackOnly()`
4	抛的是 Checked Exception	Spring 默认只回滚 `RuntimeException`，需显式 `rollbackFor = Exception.class`
5	数据库引擎不支持事务（如 MyISAM）	`SHOW TABLE STATUS` 查 Engine
6	事务传播行为错配（嵌套场景）	`NOT_SUPPORTED` / `NEVER` 会挂起或报错

// ✅ 第 4 条的正确写法
@Transactional(rollbackFor = Exception.class)
public void createOrder() throws IOException { ... }

📖 异常归属为什么只回滚 RuntimeException / 为什么同类调用绕开 AOP、以及传播行为 7 种模式的机制详见事务与并发控制 §九·坑2，本文不展开机制。

坑10：事务中调用远程接口导致长事务¶

现象：事务中调用了第三方 HTTP 接口，接口超时 30 秒，导致数据库连接被长时间占用，连接池耗尽。

// ❌ 危险：事务中调用远程接口
@Transactional
public void createOrder(OrderDTO dto) {
    orderDao.insert(dto);           // 数据库操作
    paymentService.createPay(dto);  // 调用支付服务（可能很慢！）
    inventoryService.deduct(dto);   // 调用库存服务
}

// ✅ 正确：将远程调用移到事务外
public void createOrder(OrderDTO dto) {
    // 1. 先做本地事务
    doCreateOrder(dto);
    // 2. 再调用远程服务（事务已提交）
    paymentService.createPay(dto);
    inventoryService.deduct(dto);
}

@Transactional
private void doCreateOrder(OrderDTO dto) {
    orderDao.insert(dto);
}

坑 11：FOR UPDATE 锁范围超出预期¶

现象：SELECT … FOR UPDATE 加了行锁，但其他事务插入新行时也被阻塞了。

快速处方：

查询条件用 主键 / 唯一索引等值命中：从 Next-Key Lock 退化为 Record Lock，不锁间隙
高并发写入场景考虑降为 RC 隔离级别：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;（RC 无间隙锁）
缩短事务长度，减少锁持有时间

📖 为什么 FOR UPDATE 在 RR 下会加 Next-Key Lock、「主键等值命中退化为 Record Lock」的判定规则、以及 Insert Intention Lock 与间隙锁的冲突关系详见锁机制与死锁 §锁的分类与 §间隙锁详解，本文不重复展开锁机制。

8. 连接池与连接问题¶

📖 术语家族：HikariCP 连接池参数族

字面义：连接池是预建并复用数据库连接的池子——避免"每次查询都 TCP 三次握手 + MySQL 认证"。HikariCP 是当前 Spring Boot 默认连接池，参数命名精准对应连接的生命周期关键点。 在 MySQL 场景中的含义：连接池参数设置直接决定生产下的稳定性——设小了业务排队等连接，设大了反而拖垮 MySQL（每个连接在 Server 层都是一个 THD + 线程，过多连接 = 过多 Context Switch）。 同家族成员：

参数（HikariCP）	控制阶段	典型值	踩坑
`maximum-pool-size`	池的上限	10~20（公式 `(核数 × 2) + 磁盘数`）	设 200+ 几乎必出 `Too many connections`
`minimum-idle`	常驻空闲数	与 max 相等（HikariCP 官方推荐）	设小了会频繁创建/销毁连接
`connection-timeout`	申请连接的最长等待	30s（默认）	超时 = 业务方感知为快失败
`idle-timeout`	空闲多久回收	10 min	必须小于 MySQL `wait_timeout`（默认 8h），否则取到失效连接
`max-lifetime`	连接最长存活	30 min	必须小于 MySQL `wait_timeout` 和网络设备 NAT 超时
`connection-test-query`	借出前验活	`SELECT 1`	HikariCP 5+ 默认用 JDBC4 `isValid()`，无需配置
`leak-detection-threshold`	泄漏告警	60s	排查"连接用完没归还"的利器
`keepalive-time`	空闲保活心跳	30s	解决云防火墙 / NAT 切断空闲连接

命名规律："阶段动词 + 资源 + 时间限制"三要素——connection-<动作>-<时长> / <min\|max>-<资源>。选型铁律： - MySQL 端 wait_timeout > 连接池 max-lifetime > idle-timeout；否则池子以为连接还活着，拿出来一用就 CommunicationsException - 大池不等于高性能——MySQL 推荐并发连接数 ≈ CPU 核数 × 2，再多只会 Context Switch 打架

坑12：连接池耗尽（Too many connections）¶

现象：应用报错 Communications link failure 或 Too many connections。

# ✅ HikariCP 连接池推荐配置
spring:
  datasource:
    hikari:
      maximum-pool-size: 20        # 最大连接数，根据业务调整
      minimum-idle: 5              # 最小空闲连接
      connection-timeout: 30000    # 获取连接超时（30秒）
      idle-timeout: 600000         # 空闲连接超时（10分钟）
      max-lifetime: 1800000        # 连接最大存活时间（30分钟）
      connection-test-query: SELECT 1  # 连接有效性检测

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

-- 查看最大连接数配置
SHOW VARIABLES LIKE 'max_connections';

-- 杀掉长时间空闲的连接
KILL CONNECTION <connection_id>;

坑13：连接泄漏（Connection Leak）¶

现象：连接池连接数缓慢增长，最终耗尽，重启应用后恢复。

根本原因：获取了连接但没有正确关闭（try-with-resources 没用好，或者异常路径没有关闭连接）。

// ❌ 危险：异常时连接未关闭
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
stmt.execute(sql);  // 如果这里抛异常，conn 永远不会关闭
conn.close();

// ✅ 正确：使用 try-with-resources
try (Connection conn = dataSource.getConnection();
     Statement stmt = conn.createStatement()) {
    stmt.execute(sql);
}  // 自动关闭

// ✅ HikariCP 开启连接泄漏检测
spring.datasource.hikari.leak-detection-threshold=60000  # 60秒未归还则告警

9. 数据类型选择问题¶

坑14：金额用 FLOAT/DOUBLE 导致精度丢失¶

现象：存入金额 99.99，读出来变成了 99.98999999999999。

根本原因：FLOAT 和 DOUBLE 是浮点数，无法精确表示十进制小数。

-- ❌ 危险：浮点数存金额
price FLOAT
price DOUBLE

-- ✅ 方案1：使用 DECIMAL（精确小数）
price DECIMAL(10, 2)  -- 最多10位，小数点后2位

-- ✅ 方案2：用整数存分（推荐，避免小数运算）
price_fen INT  -- 存分，99元存为 9900

-- Java 中使用 BigDecimal 处理金额
BigDecimal price = new BigDecimal("99.99");  // 注意：用字符串构造，不要用 double

坑15：VARCHAR 长度设置不合理¶

-- ❌ 过长：浪费存储，影响索引效率
name VARCHAR(1000)

-- ❌ 过短：业务扩展时需要 ALTER TABLE
name VARCHAR(10)

-- ✅ 合理估算，留有余量
name VARCHAR(100)       -- 姓名
email VARCHAR(255)      -- 邮箱（RFC 标准最长 254 字符）
phone VARCHAR(20)       -- 手机号（含国际区号）
url VARCHAR(2048)       -- URL（浏览器通常支持 2048）

-- 注意：VARCHAR(255) 和 VARCHAR(256) 的区别
-- 255 以内：长度字段用 1 字节
-- 256 以上：长度字段用 2 字节

10. 主从复制问题¶

坑16：读写分离后读到旧数据¶

现象：写入数据后立即查询，查不到刚写入的数据（主从延迟导致）。

// ❌ 问题场景：写主库，读从库，但从库还没同步
orderService.createOrder(dto);  // 写主库
Order order = orderService.getOrder(id);  // 读从库，可能读不到

// ✅ 方案1：写后立即读，强制走主库
@DS("master")  // 使用 dynamic-datasource 注解
public Order getOrderAfterCreate(Long id) { ... }

// ✅ 方案2：使用 Sharding-JDBC 的强制主库路由
HintManager.getInstance().setMasterRouteOnly();

// ✅ 方案3：业务上接受最终一致性，加重试机制

坑17：主从延迟排查¶

-- 在从库执行，查看主从延迟
SHOW SLAVE STATUS\G

-- 关键字段：
-- Seconds_Behind_Master: 从库落后主库的秒数（0 表示无延迟）
-- Slave_IO_Running: IO 线程状态（应为 Yes）
-- Slave_SQL_Running: SQL 线程状态（应为 Yes）
-- Last_Error: 最近的错误信息

-- 常见延迟原因：
-- 1. 主库大事务（大批量写入）
-- 2. 从库机器性能差
-- 3. 网络带宽不足
-- 4. 从库有慢查询阻塞 SQL 线程

11. SQL 编写常见坑¶

坑18：IN 子查询性能陷阱¶

-- ❌ 大量 IN 值时性能差（超过 1000 个值时尤其明显）
SELECT * FROM orders WHERE user_id IN (1, 2, 3, ..., 10000);

-- ✅ 方案1：分批查询
-- 每批 500 个 ID，多次查询后合并结果

-- ✅ 方案2：使用临时表 + JOIN
CREATE TEMPORARY TABLE tmp_ids (id BIGINT);
INSERT INTO tmp_ids VALUES (1), (2), ...;
SELECT o.* FROM orders o INNER JOIN tmp_ids t ON o.user_id = t.id;

坑19：UPDATE 忘加 WHERE 条件¶

现象：UPDATE orders SET status = 2 没有 WHERE 条件，全表数据被更新。

-- ✅ 防护措施1：开启安全更新模式
SET sql_safe_updates = 1;
-- 开启后，没有 WHERE 条件或 WHERE 条件不含索引列的 UPDATE/DELETE 会报错

-- ✅ 防护措施2：先 SELECT 确认影响行数
SELECT COUNT(*) FROM orders WHERE status = 1;  -- 先确认
UPDATE orders SET status = 2 WHERE status = 1;  -- 再更新

-- ✅ 防护措施3：应用层 ORM 框架限制（MyBatis-Plus）
// 全局配置禁止全表更新
GlobalConfig globalConfig = new GlobalConfig();
globalConfig.setDbConfig(new GlobalConfig.DbConfig().setUpdateStrategy(FieldStrategy.NOT_NULL));

坑20：隐式类型转换导致索引失效¶

-- 假设 phone 列是 VARCHAR 类型，且有索引
-- ❌ 传入数字，发生隐式类型转换，索引失效，全表扫描
SELECT * FROM user WHERE phone = 13800138000;

-- ✅ 正确：类型匹配，走索引
SELECT * FROM user WHERE phone = '13800138000';

-- 反过来也一样：
-- 假设 user_id 是 INT 类型
-- ❌ 传入字符串，索引失效
SELECT * FROM orders WHERE user_id = '123';

-- ✅ 正确
SELECT * FROM orders WHERE user_id = 123;

12. 常见问题¶

📖 “@Transactional 为什么不回滚 Checked Exception”「间隙锁如何防幻读」「RC / RR 的 Read View 区别」等机制级问题已在事务与并发控制与锁机制与死锁给出源码级答案，本文 Q&A 仅保留排查与选型级问题。

Q：生产环境大表加索引有哪些风险？如何安全操作？

直接 ALTER TABLE 大表会锁表，导致业务中断。安全方案：使用 ALGORITHM=INPLACE, LOCK=NONE（Online DDL），或使用 pt-online-schema-change、gh-ost 等工具，在不锁表的情况下完成 DDL 变更。

Q：@Transactional 注解有哪些常见的失效场景？

同类内部调用（绕过 AOP 代理）；2. 方法不是 public；3. 捕获异常后没有重新抛出；4. 默认只回滚 RuntimeException，checked exception 需要配置 rollbackFor = Exception.class；5. 数据库引擎不支持事务（如 MyISAM）。

Q：金额字段应该用什么类型？为什么不能用 FLOAT？

应该用 DECIMAL(M, D) 或整数（存分）。FLOAT/DOUBLE 是二进制浮点数，无法精确表示十进制小数，会产生精度误差，金融场景绝对不能使用。

Q：如何避免主从延迟导致的读到旧数据问题？

写后立即读的场景强制走主库；2. 业务上接受最终一致性，加重试；3. 减少大事务，降低主从延迟；4. 使用半同步复制，确保从库收到 binlog 后主库才提交。