举一个具体例子说明为什么索引不是越多越好,举具体字段

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