十年DBA总结的MySQL避坑指南：从锁机制到性能优化

引言

“数据库的坑，踩过才知道有多深。”
—— 一位凌晨三点还在处理死锁的DBA

从业十年，见过太多因MySQL配置不当、SQL写法不规范导致的性能灾难。本文总结高频踩坑点，涵盖锁机制、索引设计、事务优化等核心场景，附带真实案例和解决方案。

---

一、锁机制篇：那些年我们遇到的「锁」事

1.1 隐式锁升级：你以为的行锁其实是表锁

坑点：

-- 未命中索引的「行锁」退化为表锁
UPDATE users SET score=100 WHERE name LIKE '%Tom%';

现象：

• 该操作导致全表锁，并发请求堆积
• SHOW PROCESSLIST显示大量Waiting for table metadata lock

避坑方案：

• 确保WHERE条件使用索引
• 用EXPLAIN验证执行计划

1.2 死锁的经典姿势：交叉更新

案例复现：

-- 事务1
BEGIN;
UPDATE account SET balance=90 WHERE id=1;
UPDATE account SET balance=110 WHERE id=2;

-- 事务2（并发执行）
BEGIN;
UPDATE account SET balance=80 WHERE id=2;
UPDATE account SET balance=120 WHERE id=1;

日志分析：

LATEST DETECTED DEADLOCK:
*** (1) TRANSACTION: UPDATE account SET balance=110 WHERE id=2
*** (2) TRANSACTION: UPDATE account SET balance=120 WHERE id=1

解决方案：

• 统一资源访问顺序（如按id升序操作）
• 设置锁超时：innodb_lock_wait_timeout=30

---

二、索引设计篇：最昂贵的教训

2.1 最左前缀失效：联合索引的陷阱

错误示范：

ALTER TABLE orders ADD INDEX idx_status_created(status, created_at);


-- 无法命中索引的查询
SELECT * FROM orders WHERE created_at > '2025-01-01';

黄金法则：

• 高频查询字段放左侧
• 使用EXPLAIN检查key_len

2.2 隐式类型转换：索引失效的幽灵

血泪案例：

-- phone字段是varchar，但传入数字
SELECT * FROM users WHERE phone=13800138000; 

现象：

• 全表扫描
• 索引统计信息显示rows_examined=1000000

修复方案：

-- 显式类型转换
SELECT * FROM users WHERE phone='13800138000';

---

三、事务优化篇：少写一句COMMIT，加班一整夜

3.1 长事务的灾难

错误场景：

BEGIN;
-- 耗时操作（如文件导入）
INSERT INTO big_data ...;
-- 忘记COMMIT，连接断开后事务未提交

后果：

• 持有锁长达数小时
• information_schema.INNODB_TRX显示trx_started早于1小时前

最佳实践：

• 添加事务超时监控：

SELECT * FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

3.2 批量操作的正确姿势

错误写法：

-- 10万次单行更新
BEGIN;
FOR i IN 1..100000 LOOP
  UPDATE products SET stock=stock-1 WHERE id=i;
END LOOP;
COMMIT;

性能对比：

方式	耗时	锁持有时间
单条提交	300s	长
批量更新	5s	短

优化方案：

-- 批量更新
UPDATE products SET stock=stock-1 WHERE id IN(1,2,...,1000);

---

四、监控与应急篇

4.1 必须收藏的诊断命令

-- 查看锁等待
SHOW ENGINE INNODB STATUS\G

-- 找出未提交的长事务
SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_started DESC;

-- 实时监控锁等待
SELECT * FROM sys.innodb_lock_waits;

4.2 紧急止血方案

当数据库CPU飙升至100%时：

快速定位问题SQL：

SELECT * FROM performance_schema.events_statements_history_long 
ORDER BY TIMER_WAIT DESC LIMIT 10;

临时Kill会话：

-- 找出耗时最长的会话
SELECT id, TIME FROM information_schema.PROCESSLIST 
WHERE COMMAND='Query' ORDER BY TIME DESC;

-- 谨慎操作！
KILL 12345;

---

五、终极建议

预生产环境必做：

• 用pt-upgrade检查SQL兼容性
• 用sysbench做并发压测

定期健康检查：

-- 检查冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 检查未使用的索引
SELECT * FROM sys.schema_unused_indexes;

结语

最好的优化是避免优化