MySQL开发中的典型Bug排查与解决方案

案例一：复合索引失效问题

技术环境

• MySQL版本：5.7/8.0
• 存储引擎：InnoDB
• 表结构：包含uid和order_status字段的订单表

Bug现象

在查询select * from order_info where uid=5837661 order by id asc limit 1时，虽然表上有idx_uid_stat(uid,order_status)复合索引，但执行计划显示使用了全表扫描而非索引。

排查步骤

1. 使用EXPLAIN分析查询执行计划，发现possible_keys显示可能使用idx_uid_stat，但实际key为空
2. 检查表结构确认索引确实存在
3. 使用optimizer_trace分析优化器决策过程
4. 发现优化器认为全表扫描成本低于使用索引+回表成本

解决方案

-- 优化查询方式1：强制使用索引
SELECT * FROM order_info FORCE INDEX(idx_uid_stat) 
WHERE uid=5837661 ORDER BY id ASC LIMIT 1;

-- 优化查询方式2：使用覆盖索引
SELECT id FROM order_info 
WHERE uid=5837661 ORDER BY id ASC LIMIT 1;

避坑总结

1. LIMIT 1并不总是能保证优化器选择索引
2. 复合索引需要满足最左前缀原则
3. 避免在索引列上使用函数或计算
4. 考虑使用覆盖索引减少回表操作
5. 必要时可以使用FORCE INDEX提示

案例二：死锁问题排查

技术环境

• MySQL版本：5.7
• 隔离级别：READ-COMMITTED
• 并发事务场景

Bug现象

系统出现死锁错误，多个事务相互等待对方释放锁资源，导致业务请求超时。

排查步骤

1. 检查MySQL错误日志获取死锁详细信息
2. 执行SHOW ENGINE INNODB STATUS查看死锁信息
3. 分析发现事务A持有索引X的锁，等待索引Y的锁；事务B持有索引Y的锁，等待索引X的锁
4. 确认即使在READ-COMMITTED级别下也存在间隙锁问题

解决方案

-- 1. 统一加锁顺序
BEGIN;
SELECT * FROM table1 WHERE id=1 FOR UPDATE;
SELECT * FROM table2 WHERE id=2 FOR UPDATE;
COMMIT;

-- 2. 调整隔离级别(根据业务需求)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 3. 减少事务持有锁的时间
-- 将大事务拆分为小事务

避坑总结

1. 统一事务中的加锁顺序
2. 避免长事务，尽快提交或回滚
3. 合理设置隔离级别，不是越高越好
4. 使用锁超时机制：innodb_lock_wait_timeout
5. 考虑使用乐观锁替代悲观锁

案例三：NULL值处理陷阱

技术环境

• 任何MySQL版本
• 包含允许NULL字段的表

Bug现象

查询条件WHERE email='xxx'会漏掉NULL记录，导致数据不完整。

排查步骤

1. 发现查询结果与预期记录数不符
2. 检查表数据确认存在NULL值记录
3. 验证= NULL条件无效
4. 确认需要使用IS NULL语法

解决方案

-- 正确查询NULL值的方法
SELECT * FROM users WHERE email IS NULL;

-- 查询非NULL值
SELECT * FROM users WHERE email IS NOT NULL;

-- 多表关联时处理NULL值
SELECT u.*, COALESCE(p.phone, 'N/A') 
FROM users u LEFT JOIN phones p ON u.id = p.user_id;

避坑总结

1. 始终使用IS NULL/IS NOT NULL判断空值
2. 避免在索引列上使用IS NULL(InnoDB不索引全NULL记录)
3. 多表关联时使用LEFT JOIN+COALESCE保证主表记录不丢失
4. 设计表结构时明确字段是否允许NULL
5. 应用程序中显式处理数据库返回的NULL值

开发建议

1. 索引使用原则：
   • 避免在索引列上使用函数或计算
   • 复合索引遵循最左前缀原则
   • 注意隐式类型转换导致索引失效
   • 避免使用LIKE '%keyword'前导通配符
2. 事务设计原则：
   • 尽量使用短事务
   • 统一加锁顺序
   • 合理设置隔离级别
   • 考虑使用乐观锁机制
3. NULL值处理：
   • 设计阶段明确字段NULL属性
   • 查询时使用正确语法
   • 关联查询注意NULL值影响
   • 应用层做好NULL值防御

通过以上真实案例的分析和解决方案，希望能帮助开发者避免常见的MySQL陷阱，提高数据库应用的稳定性和性能。