当前位置：首页 > news >正文

数据库不是黑盒：理解它才能用好它

news 2026/6/2 23:23:09

凌晨十一点，监控大屏突然一片红。

运维发来消息：某核心接口平均耗时飙到 17 秒，用户订单列表刷不出来了。工程师盯着慢查询日志里那条 SQL，脑子里冒出的第一个念头是：“再加几个索引应该能好”。

这个念头，暴露了一个行业里极其普遍的认知缺口：把数据库当黑盒，只知道"加索引能快"，却不知道为什么快，也不知道什么时候加了反而更慢。

这是一个真实案例。一张财务流水表，未分库分表，数据量约955 万行。分页查询的 SQL 长这样：

SELECT各种字段FROM`finance_flow`WHERE各种条件LIMIT0,10;

执行耗时：16 秒 938 毫秒。

改写后的 SQL：

SELECT各种字段FROM`finance_flow`main_tableRIGHTJOIN(SELECTidFROM`finance_flow`WHERE各种条件LIMIT0,10)temp_tableONtemp_table.id=main_table.id;

执行耗时：347 毫秒。

同样的条件，同样的数据，快了将近50 倍。

两段 SQL 的差异，表面上看只是把查询条件"挪进了子查询"。但实际上，它触及了 InnoDB 存储引擎最核心的一个概念——回表。

要理解回表，就必须先打开数据库这个"黑盒"。

InnoDB 存储引擎以页（Page）为最小存储单位，每页默认大小为16KB。磁盘 I/O 的最小粒度就是一页，这意味着每次读写都会搬运 16KB 的数据。

InnoDB 的主键索引，是一棵B+ 树。它不是随便选择的数据结构，而是专门为磁盘 I/O 设计的：

一棵高度为 3 的 B+ 树，可以存储上百万行数据，查找任意一行最多只需 3 次磁盘 I/O。如果没有索引，100 万行数据需要 100 万次 I/O，代价相差数十万倍。

这就是索引之所以"快"的第一性原理：它把随机查找变成了有序的树形导航，把 O(n) 的线性扫描变成了 O(log n) 的对数查找。

理解了 B+ 树，再来看 InnoDB 里的两类索引：

聚簇索引（主键索引）：叶子节点存储的是整行数据。按主键查找，一次 B+ 树遍历就能拿到完整记录。

辅助索引（二级索引）：叶子节点存储的是该列的值 + 对应行的主键 ID。按辅助索引查找，先找到主键，再拿着主键去主键索引树上再查一遍——这个"再查一遍"就叫回表。

一次辅助索引查询，实际上要走两棵 B+ 树。

回到开头那个 955 万行的案例：深度分页的LIMIT 100000, 10，MySQL 并不是直接跳过前 10 万行，而是把前 10 万行全部取出来，再丢掉前 9999 条，只返回最后 10 条。每取一行，都要做一次回表。10 万次回表，每次都是磁盘 I/O，耗时 17 秒完全不冤。

改写后的 SQL 把条件扔进子查询，子查询只查id（主键）。主键本身就在辅助索引的叶子节点上，根本不需要回表，10 万次回表变成了 10 万次纯索引扫描，再加上最终 10 次精准回表——快 50 倍的秘密就在这里。

理解了存储结构，就能理解为什么有些"看起来有索引"的查询还是慢。

一张订单表，user_id和pay_time各有独立索引：

SELECT*FROMt_orderWHEREuser_id=?ANDstatus=1ANDDATE(pay_time)=?ORDERBYpay_timeDESCLIMIT20;

跑 EXPLAIN 一看：type: ALL，rows: 9000000，全表扫描。

问题出在DATE(pay_time)。对列使用函数，MySQL 无法利用 B+ 树的有序性来定位——它不知道DATE(pay_time)的值在树里排在哪，只能逐行计算、逐行比对。

索引的本质是有序存储，任何破坏有序性的操作都会让索引失效。常见的陷阱：

把DATE(pay_time) = ?改成pay_time BETWEEN ? AND ?，索引立刻生效，同一查询从 4.8 秒降到 0.3 秒。

另一个经典误区：联合索引里字段的顺序随便排。

假设有(user_id, create_time)联合索引，B+ 树的排序规则是：先按user_id排序，user_id相同时再按create_time排序。

WHERE user_id = 1 AND create_time > '2024-01-01'：命中索引，先精准定位user_id=1的区间，再扫描时间。
WHERE create_time > '2024-01-01'：索引失效，create_time没有独立的有序性，必须全表扫描。
WHERE user_id = 1：命中索引（最左前缀），即使没有用到create_time。

口诀：等值查询的字段放左边，范围查询的字段放右边，不用的字段不要占位。

一张百万订单表，(user_id, create_time)联合索引下：无索引时同等查询耗时超 3 秒，加索引后 20 毫秒内完成，性能提升 150 倍以上。这不是加索引本身的功劳，而是字段顺序对了，B+ 树的结构被充分利用了。