文章目录写在前面你要搞懂的一条主线第 1 章问题从哪来——「有索引为什么还慢」第 2 章B 树——索引的「目录」长什么样2.1 为什么用 B 树而不是二叉树2.2 索引本质上是什么2.3 一个小例子idx_age 的叶子里有什么第 3 章聚簇索引——表数据本身就在一棵 B 树里3.1 聚簇索引名称的由来1为什么叫「聚簇」2和普通二级索引的区别3直观类比4在 InnoDB 中的具体表现3.2 三个容易混在一起的词3.3 聚簇索引的叶子长什么样3.4 类比按门牌号排序的公寓楼第 4 章二级索引——另一棵 B 树叶子只带「门牌号」4.1 二级索引是什么4.2 两棵树如何配合第 5 章回表——定义、步骤与判断方法5.1 正式定义5.2 同一个表三种查询对比5.3 回表的成本直觉5.4 一句话记忆第 6 章覆盖索引——如何故意避免回表6.1 什么是覆盖索引6.2 代价与权衡第 7 章总流程图与自测7.1 查询走哪条路总览7.2 自测题建议先闭卷再对答案7.3 30 秒口述检验附录术语速查表一篇用虚构示例讲清 InnoDB 索引与回表的入门笔记。全文只使用student表示例不涉及任何业务表或项目代码。写在前面你要搞懂的一条主线在 InnoDB 里可以把一张表想象成两或多棵 B 树聚簇索引叶子节点里放着完整的一行数据按主键排序。二级索引叶子节点里只有索引列 主键没有你要的其他列。查询时如果只用了二级索引却还需要name这类不在索引里的列就要拿着主键再到聚簇索引里取一次完整行——这一步就叫回表。下面按「存储结构 → 索引类型 → 查询路径 → 如何避免回表」的顺序展开。第 1 章问题从哪来——「有索引为什么还慢」假设有这样一张学生表CREATETABLEstudent(idINTPRIMARYKEY,-- 主键InnoDB 下即聚簇索引nameVARCHAR(50),ageINT,class_idINT,INDEXidx_age(age)-- 在 age 上的普通索引二级索引);你执行SELECTname,ageFROMstudentWHEREage18;直觉上age有索引应该很快。但有时你会发现索引确实用上了磁盘/缓冲池的读取次数却并不低。原因在于idx_age这棵索引树里并没有name这一列。索引帮你快速找到了「哪些学生的 age 是 18」但若要拿到name往往还要再查一次主表数据——也就是回表。本章 takeaway索引不是整张表的复印件它只保存「排序用的键」和「找到那一行所需的地址主键」。第 2 章B 树——索引的「目录」长什么样2.1 为什么用 B 树而不是二叉树表数据可能有百万、千万行。索引要支持等值查询age 18范围查询age BETWEEN 18 AND 20排序ORDER BY ageB 树的特点很适合这些场景特点含义带来的好处多叉、树矮每个节点有很多孩子树的高度低从根到叶通常只需几次磁盘 IO数据都在叶子非叶子节点只存「指路」的键内存里能缓存更多「目录」节点叶子形成链表同一层的叶子按顺序串起来范围扫描时顺着链表走不用反复回根可以把它想成一本厚字典的拼音目录中间页告诉你「去哪个字母区间」真正的词条数据都在最后的叶子页上而且叶子页是连续编号的翻起来很顺。2.2 索引本质上是什么索引 按某一列或多列排序的一棵 B 树。你查WHERE age 18引擎在这棵树上定位到age 18的叶子。叶子上记的不是「整行学生档案」而是能继续找到这一行的线索——具体是什么线索取决于这是聚簇索引还是二级索引下一章讲。2.3 一个小例子idx_age 的叶子里有什么在idx_age的 B 树叶子上可能看到类似(age17, id2) (age18, id3) (age18, id7) (age19, id12) ...注意这里有age和id没有name。这就是为什么后面会出现「回表」——索引只给了你门牌号id没给你住户姓名name。第 3 章聚簇索引——表数据本身就在一棵 B 树里3.1 聚簇索引名称的由来在 MySQL 里「聚簇索引」这个名字的核心含义是把索引和数据行「聚」在一起存放。1为什么叫「聚簇」英文是clustered indexcluster有「聚集、成团、成簇」的意思。聚簇索引的叶子节点直接存放数据行而不是只存指向别处的指针。主键索引的结构与表数据绑定在一起各行按主键顺序「聚集」在 B 树的叶子层。2和普通二级索引的区别类型叶子节点里有什么普通索引二级索引主键值或行定位信息完整数据行另存于聚簇索引聚簇索引完整数据行查到叶子页就等于查到数据3直观类比普通索引像书的目录只告诉你正文在第几页。聚簇索引像目录和正文合订本翻到这一页就直接看到内容。4在 InnoDB 中的具体表现InnoDB 的主键索引就是聚簇索引。若没有显式主键InnoDB 会按顺序选择聚簇键第一个非空唯一索引若仍没有则生成隐藏的row_id6 字节作为聚簇索引键。一句话之所以叫聚簇索引是因为表的数据记录与主键索引聚集在同一棵 B 树里叶子节点直接保存数据行。3.2 三个容易混在一起的词术语一句话解释主键Primary Key唯一标识一行在 InnoDB 里通常也充当聚簇索引的排序键。聚簇索引键Clustered Index Key决定「这一行在聚簇 B 树里排在哪」的键常见就是主键id。聚簇索引Clustered Index那棵「叶子 完整行数据」的 B 树表的主数据按这棵树的顺序存储。在 InnoDB 中可以暂时把三者理解成主键 ≈ 聚簇索引键聚簇索引 按主键组织的那棵「主数据树」。入门阶段先记住有主键id时聚簇索引就是按id排序无主键时的选择规则见3.1 节。3.3 聚簇索引的叶子长什么样student表按主键id的聚簇索引叶子节点里存的是整行id1 → (name李四, age17, class_id2) id2 → (name王五, age17, class_id1) id3 → (name张三, age18, class_id1) id7 → (name赵六, age18, class_id3) ...关键对比请记住聚簇索引的叶子≈ 数据行本身或行的主要部分二级索引的叶子≠ 整行只有索引列 主键3.4 类比按门牌号排序的公寓楼门牌号 主键id聚簇索引键每个房间里的全部信息 一行完整数据name, age, class_id…公寓楼按门牌号物理排列 聚簇索引按主键顺序存数据你拿着id3去找人直接进 3 号房就能拿到张三的全部信息 ——一次命中不用再去别处抄档案。第 4 章二级索引——另一棵 B 树叶子只带「门牌号」4.1 二级索引是什么在age列上建的idx_age是独立于聚簇索引的另一棵 B 树排序键是age非主键**叶子 **上通常是age的值 主键id聚簇索引键。idx_age 的叶子示例 (age18, id3) (age18, id7) (age19, id12)注意这里没有name。二级索引的作用按 age 快速找到对应学生的 id 列表。4.2 两棵树如何配合查SELECT name FROM student WHERE age 18时逻辑上是步骤 1在 idx_age二级索引上找 age18 → 得到 id 列表 [3, 7, ...] 步骤 2对每个 id到聚簇索引主数据树上取完整行 → 读出 name步骤 2 就是回表索引树 → 主数据树多走一趟。第 5 章回表——定义、步骤与判断方法5.1 正式定义回表Table Lookup / 回表查询是指MySQL 通过 **二级索引 **找到了满足条件的行的主键值聚簇索引键但查询需要的列不在该二级索引里于是还要拿着主键到聚簇索引主表数据中再读取完整那一行。可以记成两步先查索引在二级索引的 B 树里定位拿到id主键再查表用这些id到聚簇索引里取完整行如name等列。5.2 同一个表三种查询对比SQL主要使用的结构是否回表原因SELECT id FROM student WHERE age 18idx_age否需要的id、age都在idx_age叶子里。SELECT name FROM student WHERE age 18idx_age→ 聚簇索引是name不在idx_age里必须用id再取整行。SELECT * FROM student WHERE id 3聚簇索引否直接按主键在聚簇树上取整行不经过二级索引。5.3 回表的成本直觉若age 18匹配1000 行且查询需要回表二级索引上可能扫描 1000 个叶子条目得到 1000 个id聚簇索引上大致要进行1000 次「按主键找行」实际实现会做一定优化但直觉上仍是「匹配越多回表越多」。因此高选择性过滤后剩很少行时回表尚可接受低选择性一次命中成千上万行且要很多列时回表成本会非常明显。5.4 一句话记忆二级索引给你的是地址主键回表是按地址去主楼聚簇索引取包裹完整行。第 6 章覆盖索引——如何故意避免回表6.1 什么是覆盖索引当SELECT 用到的列 WHERE 用到的列已经全部包含在某个索引里时优化器可以只扫描这棵索引不必再访问聚簇索引取行——这叫索引覆盖Covering Index也就不需要回表。例如建立联合索引INDEXidx_age_name(age,name)则SELECTnameFROMstudentWHEREage18;可能只需扫描idx_age_nameage用于过滤name直接在索引叶子里不必再按 id 回聚簇索引。6.2 代价与权衡好处减少回表降低 IO对大结果集尤其明显。代价索引变宽占更多磁盘写入时要维护更多索引页。这是典型的用空间换时间需按查询模式设计不是索引越多越好。第 7 章总流程图与自测7.1 查询走哪条路总览主键 / 聚簇索引二级索引是否收到 SQL优化器选择哪个索引?在聚簇 B 树按 id 定位叶子即完整行 → 直接返回在二级索引 B 树扫描所需列是否都在该索引中?覆盖索引: 只读索引树回表: 用主键 id 再查聚簇索引拼出完整行后返回返回结果集7.2 自测题建议先闭卷再对答案Q1.主键、聚簇索引键、聚簇索引在student表里分别对应什么参考答案主键id聚簇索引键一般就是id聚簇索引按id组织、叶子存完整行数据的那棵 B 树Q2.idx_age的叶子上有哪些列没有什么参考答案有age以及主键id没有name、class_id等其他列Q3.什么情况下会回表什么情况下不会参考答案会使用二级索引且 SELECT 还需要索引里不存在的列不会只查索引里已有的列覆盖索引或直接用主键在聚簇索引上查Q4.B 树叶子链表对WHERE age BETWEEN 18 AND 20有什么帮助参考答案找到起始叶子后可以沿链表顺序扫描到结束边界而不必反复从根节点查找。7.3 30 秒口述检验如果你能不看笔记说出下面这段话说明已经掌握主线InnoDB 表数据按主键存在聚簇索引的 B 树里age上的二级索引是另一棵树叶子只有age和id。查name时要先用age找到id再按id去聚簇索引取整行第二次就是回表。附录术语速查表中文英文在本文明中的含义B 树B Tree索引背后的多叉平衡树数据在叶子且叶子相连聚簇索引Clustered Index叶子存整行表数据按主键顺序组织二级索引Secondary Index非主键索引叶子多为「索引列 主键」主键 / 聚簇索引键PK / Clustered Key定位一行在聚簇树中位置的唯一键常为id回表Table Lookup二级索引拿到主键后再查聚簇索引取完整行覆盖索引Covering Index查询列全在索引内无需回表整理完毕完结撒花