智汇 IT
城启新篇

数据库索引不是银弹 一场关于页分裂与回表的慢查询夜谈

凌晨两点,报警群弹出一条慢查询。QPS 不高。CPU 也没满。可接口就是卡。开发说已经加了索引。DBA 看了一眼执行计划,回了句:加了,但没完全加。

这场景太常见。数据库索引像书末的目录。查得快,是因为不翻整本书。麻烦在于,目录也要维护。页分裂、回表、统计信息失真,随便哪一个都能让目录变成摆设。

MySQL InnoDB B+树索引叶子链表结构图
MySQL InnoDB B+树索引叶子链表结构图

先说清楚一件事。索引不是缓存。它不保存整行结果,只保存键值和指向数据页的定位信息。InnoDB 里,二级索引叶子节点存的是主键值。查非索引列,还得拿主键回聚簇索引再找一次。这就是回表。一次查询拆成两次查找,随机 IO 的概率立刻上去。

一页能装多少行 决定了索引的脾气

B+树不是抽象概念。它落在 16KB 的数据页上。页里塞多少键,取决于键的长度。主键用 bigint,还是用 uuid 字符串,差别很大。前者紧凑,后者臃肿。臃肿的索引不只占磁盘,还让树更高。树高一高,IO 次数就多。

更隐蔽的是页分裂。顺序插入时,新键总往右走,页写得满,分裂也规矩。随机插入就难看了。中间页满了,得拆。拆完可能留下半空页。写入放大、碎片、空间浪费,全来了。很多团队只盯慢查询日志,不看 页分裂和索引碎片。等发现时,表已经胖得不像话。

那要不要干脆用自增主键?多数 OLTP 场景下,是省心的。可别把它当教条。分库分表、合并写入、业务唯一键,都会改变取舍。索引设计从来不只看数据库,还看访问模式。

最左匹配不是玄学 是排序后的前缀扫描

联合索引经常被误解。有人以为建了 (a,b,c),查 b 和 c 也能飞。实际执行时,优化器可能直接全表扫。原因不神秘:B+树先按 a 排,再按 b 排,再按 c 排。跳过 a,后面的有序性没法直接用。就像电话簿按姓、名排,你只报名字,还是得翻。

所以 联合索引的列顺序,是拿查询模式换来的。等值条件放前面,范围条件放后面。排序、分组也影响顺序。别拍脑袋写。把高频 SQL 拉出来,看 where、order by、group by 怎么组合。实在拿不准,用 EXPLAIN 看 key、rows、filtered,比争论靠谱。

MySQL联合索引最左匹配执行计划示意图
MySQL联合索引最左匹配执行计划示意图

不过话说回来,EXPLAIN 也会骗人。统计信息过期,优化器可能选错索引。明明有更优路径,它偏要走全表。这时先别急着加 force index。先更新统计信息,再看数据分布。很多“索引失效”其实是统计信息失效。

回表 覆盖索引 和那些被忽略的代价

回表 覆盖索引 和那些被忽略的代价
回表 覆盖索引 和那些被忽略的代价

覆盖索引是个好词,但不是万能药。查询列都在索引里,确实不用回表。可索引越宽,页越少装键,写入越贵。你把五个列塞进一个索引,读是爽了,写可能哭了。插入、更新、删除都要维护这棵额外的树。高写入表尤其明显。

还有个老问题:区分度。性别、状态、是否删除,这类列单独建索引,通常没多大用。扫描一半数据,还不如全表。真正有用的索引,往往能把结果集砍到很小。比如订单号、用户 ID、时间范围组合。别只看列名,要看选择率。

有人问,那到底建几个?没有标准答案。读多写少的报表库,可以豪放点。写多读少的事务表,得克制。索引是借未来的写入成本,换现在的查询速度。借多了,迟早要还。

别拿索引当许愿池

别拿索引当许愿池
别拿索引当许愿池

我见过最危险的场面,是每来一个慢查询就加一个索引。三个月后,表上有二十个索引。查询没快多少,写入慢了,磁盘涨了,DDL 也疼。更糟的是,优化器面对太多选择,反而容易选错。

正确姿势很土:先定位慢查询,再看执行计划,再评估数据量和分布,最后才决定加不加。加完要回归验证。删除旧索引也一样,别怕。很多索引从上线那天起,就没被用过。用 performance_schema 或 pg_stat_user_indexes 看看访问计数,心里有数。

还有个容易漏的点:函数和隐式转换。where date(created_at)=… 会让普通索引失效。where user_id=’123′ 而字段是整数,也可能出问题。不是数据库矫情,是它没法拿索引顺序去匹配表达式。改写 SQL,往往比加索引便宜。

回到凌晨那条慢查询。最后没加新索引。改写了 SQL,把一个范围条件挪到联合索引最左,删掉一个重复索引。接口从 1.8 秒降到 80 毫秒。不是奇迹。只是把索引当成了需要维护的数据结构,而不是许愿池。

数据库索引的好用,建立在理解代价之上。它让读变快,也让写变重。它减少扫描,也增加存储。它帮你排序,也可能误导优化器。真正难的不是 create index,是知道什么时候不 create。

免责声明:市场有风险,选择需谨慎!此文仅供参考,不作买卖依据。如有侵权请联系删除。
文章名称:数据库索引不是银弹 一场关于页分裂与回表的慢查询夜谈
文章链接:https://m.itcdj.com/news/863.html