举一个具体例子说明为什么索引不是越多越好,举具体字段
文章目录
- 1. 核心舞台：笔记表 (`t_note`) 结构设计
- 🚨 错误的操作：
- 2. 结合具体字段，拆解三大翻车现场
- 现场一：给 `view_count`（浏览量）加索引 —— 导致写放大，拖垮数据库
- 现场二：给 `status`（状态）加索引 —— 优化器直接放弃，索引纯粹沦为累赘
- 现场三：单独给 `user_id` 和 `create_time` 加索引 —— 遭遇多条件查询时的“ filesort 降维打击”
- 3. 面试总结话术
为了让你在面试中能把这个概念“讲出画面感”，我们直接以你熟悉的社交平台（如小红书、博客系统）的“笔记表（t_note）”为例。
我们来看一张设计非常典型的表，以及如果“盲目滥建索引”会引发怎样的系统灾难。
1. 核心舞台：笔记表 (t_note) 结构设计
假设我们的数据库里有一张表，包含以下 5 个核心字段：
| 字段名 | 数据类型 | 字段含义 | 业务读写特征 |
|---|---|---|---|
id | BigInt | 笔记唯一 ID | 主键（自动拥有聚簇索引） |
user_id | BigInt | 作者用户 ID | 经常用来查询“某用户发布的所有笔记” |
status | TinyInt | 审核状态 | 只有 3 个值：0-草稿,1-审核中,2-已发布 |
view_count | Int | 笔记浏览量 | 高频变动，用户每点击一次，数值就+1 |
create_time | DateTime | 创建时间 | 经常用来做“按最新时间排序”的列表展示 |
🚨 错误的操作：
一个缺乏经验的开发人员奉行“资源无限，建了再说”的原则，给user_id、status、view_count、create_time每一个字段都单独建立了一个单列索引。
2. 结合具体字段，拆解三大翻车现场
现场一：给view_count（浏览量）加索引 —— 导致写放大，拖垮数据库
view_count是一个高频变动的数值型字段。每当一篇热门笔记被上万人浏览时，后端会疯狂执行：
UPDATEt_noteSETview_count=view_count+1WHEREid=999;- 不建索引时：数据库只需要找到
id=999的行，把里面的数字直接改掉，极其迅速。 - 建了
idx_view_count索引后：B+ 树索引为了保证“可以通过浏览量快速排序”，它要求树里的数据必须是严格有序的。
当view_count从100变成101时，它在原先索引树里的位置就失效了。MySQL 必须做两件事：
- 把旧的
100从原来的索引节点里抹去。 - 拿着新的
101去索引树里重新寻路，插入到新的正确位置。
- 灾难后果：如果并发量高，索引树为了容纳频繁变动的数值，会发生频繁的页分裂与节点合并。CPU 算力全部被浪费在“维持这棵树的平衡”上，磁盘 I/O 瞬间飙满，哪怕磁盘无限大，写入通道也会直接被塞死。
说白了,就是因为非聚簇索引和聚簇索引,每个非叶子结点就是索引列的值,如果你经常修改这个索引列,就会导致这个索引列重排序,需要频繁发生页分裂和节点合并,瞬间磁盘IO拉满
现场二：给status（状态）加索引 —— 优化器直接放弃，索引纯粹沦为累赘
status字段的特性是“区分度极低”（基数太小），一共就0, 1, 2三种可能。
假设你的平台有 1000 万条笔记，其中“已发布（status=2）”的笔记占了 900 万条。此时用户在前端刷信息流，触发查询：
SELECT*FROMt_noteWHEREstatus=2;- MySQL 优化器的内心独白：“我想查状态为 2 的数据。如果我走
idx_status索引，我得在这棵树里找出 900 万个节点，然后拿着这 900 万个主键 ID，再去主键索引树里回表（Lookup）900 万次去拿完整数据……这太傻了，我不如直接把整张表从头到尾扫描一遍（Full Table Scan）来得快！” - 灾难后果：最终优化器会直接放弃这个索引，选择全表扫描。这意味着，这个索引在查询时毫无用处；但每次有新笔记发布（
Insert）或下架（Update status）时，MySQL 还必须硬着头皮去更新这棵没用的索引树。它变成了纯粹的“带薪摸鱼”累赘。
现场三：单独给user_id和create_time加索引 —— 遭遇多条件查询时的“ filesort 降维打击”
当我们在个人主页看某个大 V 的笔记时，通常需要：看某个用户的、并且按时间倒序排列的最新笔记。
SELECT*FROMt_noteWHEREuser_id=8888ORDERBYcreate_timeDESC;现状：我们有独立的
idx_user_id和独立的idx_create_time。MySQL 的尴尬：在一条查询里，MySQL通常一次只能选择使用一个单列索引。
如果它选了
idx_user_id，它能快速定位出作者是 8888 的 1 万条笔记。但接下来，为了实现ORDER BY create_time DESC，它不得不把这 1 万条数据拉到内存甚至磁盘里，进行痛苦的filesort（文件排序）。如果它选了
idx_create_time，它会丧失筛选作者的能力。灾难后果：两个独立的索引并没有让“联合查询+排序”变快。
正确解法：应该砍掉这两个独立索引，建立一个**联合索引
idx_user_time(user_id, create_time)**。让数据在叶子节点里由于最左匹配原则，天然既按用户分类，又在分类内部按时间排好序，实现真正的O ( 1 ) O(1)O(1)级精准秒出。
3. 面试总结话术
以后被问到这个问题，把这个例子甩给面试官：
“比如在社交平台的笔记表设计中，如果给**‘浏览量（
view_count）’这种高频变动字段加索引，会导致每次点击都引发 B+ 树的节点分裂与重排，引发严重的写放大**；
如果给**‘审核状态（status）’这种区分度极低的字段加索引，由于回表成本过高，优化器在查询时会直接弃用**，导致其沦为只耽误写入性能的累赘；
如果在‘按作者查看最新笔记’的场景下，盲目建两个独立的单列索引，依然无法避免大数据量下的filesort内存排序，远不如建一个(user_id, create_time)的联合索引有效。
所以，空间哪怕无限，索引也必须精准克制。”