MySQL 笔记：索引、事务隔离、锁与慢查询优化实战

MySQL 出问题的方式很固定：慢查询、死锁、连接打满。这篇把原理和排查命令放在一起，方便出事时直接翻。

一、索引：为什么是 B+ 树

二、联合索引与最左前缀

索引 (org_id, status, create_time) 相当于建立了 (org_id)、(org_id, status)、(org_id, status, create_time) 三组有序性。

索引失效的八种写法（这些在代码评审里反复出现）：

1. 索引列上做函数：where date(create_time) = '2026-09-01' → 改成范围查询
2. 隐式类型转换：where order_no = 123（order_no 是 varchar）→ 加引号
3. like 以 % 开头：like '%abc'
4. 对索引列做运算：where amount + 1 > 100
5. or 连接非索引列
6. 不符合最左前缀
7. 优化器判断全表更快（小表、或命中行数太多）—— 这时走全表反而是对的
8. 字符集/排序规则不一致（联表时两表字段字符集不同）

三、覆盖索引与回表

如果查询的字段都在索引里，就不用回表，称为覆盖索引。EXPLAIN 的 Extra 出现 Using index 就是覆盖索引。

-- 索引 (org_id, status)
select id, status from t_order where org_id = 1;   -- 覆盖索引，不回表
select * from t_order where org_id = 1;            -- 要回表拿其它字段

所以别写 select *，只查需要的字段，既省 IO 又可能走覆盖索引。

四、事务与隔离级别

MVCC 是 InnoDB 实现 RC/RR 的核心：每行有隐藏的 DB_TRX_ID 和 DB_ROLL_PTR，通过 undo log 形成版本链；读的时候按 ReadView 判断哪个版本可见。

• RC：每条语句生成一个 ReadView → 能看到别人已提交的修改
• RR：事务开始时生成一个 ReadView → 整个事务看到的是同一份快照

快照读 vs 当前读：

select * from t where id=1;          -- 快照读，不加锁
select * from t where id=1 for update;  -- 当前读，加排他锁
update / delete                      -- 都是当前读

五、锁与死锁

InnoDB 有三类行锁：

死锁排查：

-- 查看最近一次死锁
SHOW ENGINE INNODB STATUS\G
-- 在输出里找 LATEST DETECTED DEADLOCK 段，能看到两个事务各自持有什么锁、等什么锁

-- 查看当前锁等待
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM information_schema.INNODB_LOCKS;    -- 5.7
-- 查看正在跑的长事务（超过 60s）
SELECT * FROM information_schema.innodb_trx
 WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

减少死锁的四条经验：

1. 固定加锁顺序：批量更新按主键排序后再执行，避免交叉等待。
2. 缩小事务：事务里不要有 RPC、查外部接口。
3. 用主键更新：where id = ? 只锁一行；where status = ? 可能锁一片（间隙锁）。
4. 降低隔离级别到 RC：RC 下没有间隙锁，死锁概率明显下降（但要接受不可重复读）。

六、慢查询优化的标准流程

-- 1. 开启慢日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
-- 2. 抓正在跑的慢 SQL
SELECT id, user, host, db, time, state, LEFT(info, 120)
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep' AND time > 2 ORDER BY time DESC;
-- 3. 分析
EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;    -- MySQL 8，能看到真实耗时

EXPLAIN 关键列：type（system > const > eq_ref > ref > range > index > ALL）、key、rows、Extra（Using filesort、Using temporary 要优化）。

优化顺序：加/改索引 → 改 SQL 写法（避免 select *、拆子查询、改 or 为 union）→ 改设计（冗余字段、汇总表）→ 分库分表。

七、生产复盘：征期高峰的批量更新死锁

脱敏自某财税 SaaS（代账批量申报场景），每月 1–15 日为征期。

现象：征期上午批量生成清册时，MySQL 报 Deadlock found when trying to get lock，重试后部分成功，整体任务变慢。

排查：SHOW ENGINE INNODB STATUS 里看到两个事务在更新同一批企业的申报记录，但加锁顺序相反 —— 任务 A 按机构分组处理（企业顺序 1,3,2），任务 B 按税种分组（企业顺序 2,3,1），两条不同的 update ... where org_id=? and taxpayer_no=? 交叉等待。

根因：批量更新的顺序不固定，且事务里还夹了一次远程调用（查税局数据），事务持锁时间被拉长到秒级。

改造：

1. 批量更新前按主键排序，保证加锁顺序一致。
2. 把远程调用挪出事务：先查好数据，再开事务一次性更新。
3. 大批量改成分片提交（每 500 条一个事务），缩小事务粒度。
4. 加死锁重试（最多 3 次，带退避），重试前重新排序。

结论：死锁的本质是「加锁顺序 + 持锁时间」。固定顺序解决交叉等待，缩短事务解决等待窗口，两者都要做。

八、日常维护命令

-- 表大小
SELECT table_name, ROUND(data_length/1024/1024) data_mb,
       ROUND(index_length/1024/1024) idx_mb, table_rows
FROM information_schema.tables WHERE table_schema='db' ORDER BY data_length DESC;
-- 索引命中率（越低越该优化）
SHOW STATUS LIKE 'Handler_read%';
-- 连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
-- 锁等待超时设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';   -- 默认 50s，可降到 10-20s
-- 在线加索引（不锁表）
ALTER TABLE t_order ADD INDEX idx_org_status(org_id, status), ALGORITHM=INPLACE, LOCK=NONE;

九、几条硬规则

1. 表必须有主键，且用自增 bigint（UUID 做主键会让页分裂严重）。
2. 字段 NOT NULL + 默认值，NULL 会让索引和统计变复杂。
3. 金额用 decimal，时间用 datetime（不要用 timestamp，2038 问题）。
4. 大字段（text/blob）拆到副表。
5. 在线 DDL 用 pt-online-schema-change 或 gh-ost，直接 ALTER 大表会锁表。
6. 连接数打满先看是不是有慢 SQL 占着连接，而不是急着调大 max_connections。