MySQL 实战问题与避坑指南

核心问题：MySQL 在实际项目中有哪些常见的坑？如何排查和解决？

---

它解决了什么问题？

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

---

一、字符集与编码问题

坑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;

注意：仅修改表还不够，还需要确保： 1. MySQL 服务端配置 character_set_server=utf8mb4 2. JDBC 连接串加 characterEncoding=UTF-8&useUnicode=true 3. 连接池初始化时执行 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';

---

二、时间与时区问题

坑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：自增 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 只是唯一标识符。

---

四、大表操作问题

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

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

-- ❌ 危险：直接 ALTER 大表，会锁表
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- ✅ 方案1：MySQL 5.6+ 支持 Online DDL（InnoDB），大部分操作不锁表
ALTER TABLE orders ADD INDEX idx_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;

-- ✅ 方案2：使用 pt-online-schema-change（推荐生产环境）
-- pt-osc 原理：创建影子表 → 触发器同步数据 → 原子性重命名
pt-online-schema-change --alter "ADD INDEX idx_user_id (user_id)" \
    D=your_db,t=orders --execute

-- ✅ 方案3：使用 gh-ost（GitHub 开源，无触发器）
gh-ost --database="your_db" --table="orders" \
    --alter="ADD INDEX idx_user_id (user_id)" --execute

---

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

现象：执行 DELETE FROM logs WHERE create_time < '2023-01-01' 删除历史数据，删了几百万行，主从延迟飙升到几分钟。

根本原因：大事务产生大量 binlog，从库回放慢；同时长时间持有行锁，影响其他业务。

-- ❌ 危险：一次性删除大量数据
DELETE FROM logs WHERE create_time < '2023-01-01';

-- ✅ 分批删除，每批 1000 条，每批之间 sleep 一下
-- 用存储过程或应用层循环实现
DELETE FROM logs WHERE create_time < '2023-01-01' LIMIT 1000;
-- 循环执行，直到 affected rows = 0

-- ✅ 更好的方案：归档表 + 分区表
-- 1. 先将数据归档到历史表
INSERT INTO logs_archive SELECT * FROM logs WHERE create_time < '2023-01-01' LIMIT 10000;
-- 2. 再删除已归档的数据
DELETE FROM logs WHERE create_time < '2023-01-01' AND id IN (SELECT id FROM logs_archive);

---

五、事务与锁的实战坑

坑9：@Transactional 注解不生效

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

常见原因：

// ❌ 原因1：同类内部调用，绕过了 AOP 代理
@Service
public class OrderService {
    public void createOrder() {
        this.doCreate();  // 直接调用，不走代理，@Transactional 不生效
    }

    @Transactional
    public void doCreate() { ... }
}

// ❌ 原因2：捕获了异常但没有重新抛出
@Transactional
public void createOrder() {
    try {
        // ...
    } catch (Exception e) {
        log.error("error", e);  // 吃掉了异常，事务不会回滚
    }
}

// ❌ 原因3：默认只回滚 RuntimeException，checked exception 不回滚
@Transactional  // 默认不回滚 IOException
public void createOrder() throws IOException { ... }

// ✅ 正确：指定回滚异常类型
@Transactional(rollbackFor = Exception.class)
public void createOrder() throws IOException { ... }

---

坑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 加了行锁，但其他事务插入新行时也被阻塞了。

根本原因：RR 隔离级别下，FOR UPDATE 会加临键锁（Next-Key Lock），锁住记录本身及其前面的间隙。

-- 假设 id 有索引，表中有 id=1, 5, 10
-- ❌ 这个查询会锁住 (5, 10] 的间隙，导致 id=7 的插入被阻塞
SELECT * FROM orders WHERE id = 10 FOR UPDATE;

-- ✅ 方案1：降级到 RC 隔离级别（无间隙锁）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- ✅ 方案2：使用精确的主键查询（退化为记录锁）
SELECT * FROM orders WHERE id = 10 FOR UPDATE;  -- 主键等值查询，只锁记录本身

-- ✅ 方案3：缩短事务，减少锁持有时间

---

六、连接池与连接问题

坑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秒未归还则告警

---

七、数据类型选择问题

坑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 字节

---

八、主从复制问题

坑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 线程

---

九、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;

---

面试高频问题

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

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

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

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

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

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

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

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