← 返回思维链目录

数据库与 SQL 思维链

看到慢查询、死锁、索引失效,顺藤摸瓜找到根因

1. 慢查询:SQL 为什么慢

现象接口慢,定位到 DB 段,某条 SQL 跑了几秒
本质:SQL 慢 = 扫的数据太多。理想是走索引精确取几条(B+树 3-4 次 IO),实际退化成全表扫(逐行对比几百万行)。核心是搞清楚"索引有没有用上、为什么没用上"。
直觉:慢 SQL 第一步永远是 EXPLAIN 看 type 和 key —— type=ALL(全表扫)就是索引没用上,key=NULL 就是没走索引。
🌊 顺藤摸瓜
① EXPLAIN
先看执行计划 → EXPLAIN → 看 type(ALL=全表扫最差,ref/range=走索引)、key(实际用的索引,NULL=没用)、rows(预估扫多少行)、Extra(Using index=覆盖索引好,Using filesort/temporary=差)
EXPLAINtype=ALL 全表key=NULL 没索引rows 扫描量
② 索引没用上?
type=ALL → 索引失效了 → 常见六种:① 索引列做运算/函数(WHERE YEAR(time));② 隐式类型转换(字段 varchar 传数字);③ LIKE 左模糊('%abc');④ OR 连非索引列;⑤ != / NOT IN;⑥ 联合索引不满足最左前缀
运算/函数类型转换左模糊最左前缀
③ 索引用上了还慢?
索引走了但还慢 → 可能回表太多(二级索引查到主键,再回主键索引查完整行)→ 优化:覆盖索引(查的字段都在索引里,不用回表)→ 看 Extra 是不是 Using index
回表覆盖索引Using index
④ 数据量问题
索引都对但数据量太大 → ① 大分页(LIMIT 100万,10 要扫前 100 万行)→ 改游标分页;② 单表太大 → 分库分表;③ 历史数据多 → 冷热分离
大分页游标分库分表冷热分离
⑤ 写法问题
SQL 本身写得差 → ① SELECT *(多查多传)→ 只查需要的列;② 子查询(常产临时表)→ 改 JOIN;③ N+1 查询(循环里查)→ 改批量 IN
别 SELECT *子查询改 JOINN+1 改批量
排查三步:EXPLAIN 看走没走索引 → 没走查索引失效原因 → 走了还慢看回表/数据量/写法。

2. 索引怎么设计

现象加了索引还是慢?或者索引太多写入变慢?
本质:索引是拿空间换时间的有序数据结构(B+树)。建索引是为了加速查询,但每个索引都有代价 —— 占空间 + 写入时要维护。不是越多越好。
🌊 顺藤摸瓜
① 建在哪
WHERE / ORDER BY / GROUP BY / JOIN 的字段建索引 → 区分度高的优先(性别低,手机号高)→ 联合索引区分度高的放左边(最左前缀)
区分度最左前缀联合索引顺序
② 为什么 B+树
为什么不用 hash/二叉树? → hash 等值快但不支持范围(ORDER BY/BETWEEN);二叉树数据多树高 IO 多 → B+树叶子链表范围查询快 + 树矮(3-4 层存千万级)
B+树叶子链表hash 不支持范围3-4 层千万级
③ 索引代价
不是越多越好 → 每个索引:① 占空间;② 写入慢(INSERT/UPDATE/DELETE 要维护索引树)→ 写多读少的表少建索引
占空间写入维护代价写多读少少建
④ 聚簇 vs 非聚簇
聚簇索引(InnoDB 主键):叶子存完整行数据,一张表只有一个 → 非聚簇(二级索引):叶子存主键值,要回表查完整行 → 所以主键选短的(自增 int/id)
聚簇(主键)二级索引回表主键选短的自增
建索引原则:查常用的建、区分度高的建、联合索引高区分放左。不是越多越好,写入频繁的表少建。

3. 死锁:两个事务互相等

现象偶发 Deadlock found 报错,事务回滚
本质:两个事务相反顺序锁同一批资源,互相等对方释放,形成循环等待。MySQL 检测到会主动回滚其中一个。
🌊 顺藤摸瓜
① 经典场景
事务 A:先锁记录 1 再锁 2;事务 B:先锁 2 再锁 1 → A 等 B 释放 2,B 等 A 释放 1 → 死锁 → 看日志 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK
加锁顺序不一致循环等待SHOW ENGINE INNODB STATUS
② 锁类型
MySQL 锁:记录锁(锁一行)/ 间隙锁(锁一个范围防插入,RR 级别防幻读)/ 临键锁(记录+前面间隙)→ 间隙锁是死锁高发区(范围查询锁太多)
记录锁间隙锁临键锁 Next-KeyRR 防幻读
③ 预防
① 统一加锁顺序(所有事务按同样顺序锁);② 缩短事务(持锁时间短碰撞少);③ 降低隔离级别(RC 比 RR 间隙锁少);④ 必要时用乐观锁代替
统一加锁顺序缩短事务RC 降间隙锁

4. 事务隔离:为什么会有脏读幻读

现象并发下数据"怪怪的":读到别人没提交的、同一条两次读不一样、查询多出几行
本质:多个事务并发操作同一批数据,如果不隔离,会互相干扰。隔离级别越高越安全但并发越低 —— 这是安全与性能的权衡。
🌊 顺藤摸瓜
① 三种问题
脏读(读到别人没提交的,对方回滚了你就读错)→ 不可重复读(同一条两次读不一样,别人改了)→ 幻读(查询多出/少了行,别人插入/删了)
脏读不可重复读幻读
② 四个级别
RU(读未提交,啥都能读)→ RC(读已提交,防脏读)→ RR(可重复读,MySQL 默认,防脏读+不可重复读+用间隙锁防幻读)→ Serializable(串行,全防但最慢)
RURCRR(MySQL 默认)Serializable
③ MVCC
怎么做到读不加锁还能隔离? → MVCC(多版本并发控制)→ 每行有版本号,事务读到的是快照版本 → 读不加锁(快照读),写才加锁(当前读)→ 读写不冲突,并发高
MVCC快照读不加锁当前读加锁读写不冲突

5. 缓存与 DB 不一致

现象更新了数据,但用户看到的还是旧的
本质:数据存两份(DB + Redis),更新一份时另一份没同步,出现短暂不一致。缓存只能保证最终一致,强一致不能用缓存。
🌊 顺藤摸瓜
① 先更 DB 还是先删缓存?
主流 Cache Aside:先更 DB 再删缓存 → 但有并发问题:删缓存后、更 DB 前,另一请求读到旧 DB 写回缓存(旧值)→ 延迟双删兜底(删→更→等一会→再删)
Cache Aside先更 DB 再删缓存延迟双删
② 更彻底
订阅 MySQL binlog(Canal)→ 异步刷缓存 → 解耦应用,一致性更好 → 但架构复杂
binlog 订阅Canal异步刷缓存
③ 强一致?
要强一致(金融)? → 别用缓存,直接查 DB,或加分布式锁串行 → 缓存的代价就是放弃强一致
最终一致强一致别用缓存

总结

现象本质第一反应
SQL 慢扫的数据太多EXPLAIN 看 type/key,全表扫就查索引失效
索引失效优化器判断不走索引更划算查六种失效场景(运算/类型/左模糊...)
死锁相反顺序加锁,循环等待SHOW ENGINE INNODB STATUS,统一加锁顺序预防
脏读/幻读并发事务互相干扰隔离级别 + MVCC 快照读
缓存不一致数据存两份没同步Cache Aside + 延迟双删,强一致别用缓存
本质:数据库问题的核心大多是"扫的数据太多"或"并发互相干扰"。索引解决前者(少扫),事务隔离/锁解决后者(有序)。
思维链 · 数据库与 SQL · 全栈资料库