各位观众老爷们，大家好！我是今天的主讲人，江湖人称“代码诗人”。今天咱们不聊风花雪月，也不谈人生理想，就来唠唠数据库里两位“索引大佬”的故事：聚簇索引（Clustered Index）和二级索引（Secondary Index）。
开场白：数据库里的寻宝游戏
想象一下，你是一位寻宝猎人，手里握着一份藏宝图，目标是找到埋藏在地下的宝藏。数据库就好比这片埋藏宝藏的土地，而索引，就是你的藏宝图。没有藏宝图，你只能拿着铲子，一寸一寸地挖地，直到找到宝藏为止。有了藏宝图，就能按图索骥，直奔目标而去，效率大大提升！
那么，聚簇索引和二级索引，就像是两种不同类型的藏宝图，它们各有千秋，适用于不同的寻宝场景。
第一章：聚簇索引——“宝藏与藏宝图融为一体”
聚簇索引，顾名思义，就是“聚集”在一起的索引。它可不是简单的藏宝图，而是一种“宝藏与藏宝图融为一体”的寻宝方式。怎么理解呢？
你可以想象，聚簇索引就像是一本按照拼音排序的字典。字典里的每一个字，都对应着一段解释。而聚簇索引，就是把数据按照某个字段（通常是主键）排序，并且把数据本身也存储在这个排序好的结构里。
- 特点一：数据物理排序。 聚簇索引决定了数据在磁盘上的物理存储顺序。数据行会按照索引键的顺序存储在磁盘上。就像字典里的字，按照拼音顺序排列，挨个挨个紧密排列。
- 特点二：一张表只能有一个聚簇索引。 这也很好理解，数据只能按照一种方式排序存储，不可能同时按照两种方式物理排序。你不可能让字典既按照拼音排序，又按照笔画排序吧？除非你复制一本字典。
- 特点三：叶子节点就是数据本身。 这是聚簇索引最关键的特性。叶子节点不再是指向数据行的指针，而是包含了完整的数据行内容。这就相当于藏宝图直接指向宝藏本身，省去了中间环节。
举个栗子🌰：
假设我们有一张 user 表，包含了 id (主键), name, age 三个字段。 我们以 id 作为聚簇索引。
| id | name | age |
|---|---|---|
| 1 | Alice | 25 |
| 2 | Bob | 30 |
| 3 | Charlie | 28 |
那么，数据在磁盘上的存储顺序，就会按照 id 的顺序排列：
[ (1, Alice, 25), (2, Bob, 30), (3, Charlie, 28) ]性能影响：
-
优点：
- 范围查询效率高： 因为数据是按照索引键排序存储的，所以范围查询可以快速定位到起始位置，然后顺序读取数据，非常高效。比如，查询
id在 1 到 3 之间的用户，只需要读取连续的数据块即可。 - 主键查询效率高： 直接通过主键就能找到数据，无需额外的查找步骤。
- 插入效率相对低： 如果不是顺序插入，可能会导致页分裂，影响性能。
- 范围查询效率高： 因为数据是按照索引键排序存储的，所以范围查询可以快速定位到起始位置，然后顺序读取数据，非常高效。比如，查询
-
缺点：
- 更新代价高： 如果更新的字段是索引键，可能会导致数据迁移，影响性能。
- 二级索引需要回表查询： 如果二级索引的查询需要返回除了索引键之外的其他字段，就需要通过聚簇索引回表查询，增加开销。（后面会详细解释回表查询）
第二章：二级索引——“指向宝藏位置的标签”
二级索引（也叫非聚簇索引），就像是图书馆里的图书索引卡片。卡片上记录了书名、作者、主题等信息，以及这本书在书架上的位置。
- 特点一：数据物理排序无关。 二级索引不影响数据在磁盘上的物理存储顺序。
- 特点二：一张表可以有多个二级索引。 这就像图书馆里，可以按照书名、作者、主题等多种方式建立索引卡片。
- 特点三：叶子节点存储索引键和指向数据行的指针。 叶子节点存储的是索引键的值，以及指向数据行的指针（也就是数据在磁盘上的地址）。
举个栗子🌰：
还是以 user 表为例，我们以 name 作为二级索引。
| name | 指针 |
|---|---|
| Alice | 0x10 |
| Bob | 0x20 |
| Charlie | 0x30 |
这里的指针，指向的是 user 表中对应 name 的那一行数据在磁盘上的地址。
性能影响：
-
优点：
- 查询灵活： 可以根据不同的字段建立索引，满足不同的查询需求。
- 插入/更新效率相对高： 因为不影响数据的物理存储顺序，所以插入和更新的代价相对较低。
-
缺点：
- 需要回表查询： 这是二级索引最大的缺点。如果查询需要返回除了索引键之外的其他字段，就需要通过索引找到数据行的指针，然后根据指针去聚簇索引中查找完整的数据行。这个过程就叫做“回表查询”。
回表查询：寻宝过程中的“二次确认”
回表查询就像是寻宝猎人，拿着藏宝图找到了宝藏的位置，但是为了确认是不是真的宝藏，还需要挖开土层，亲自验证一下。
举个例子：
如果我们执行以下 SQL 语句：
SELECT age FROM user WHERE name = 'Alice';- 首先，通过
name上的二级索引，找到Alice对应的指针 0x10。 - 然后，根据指针 0x10，去聚簇索引中查找
id为 1 的那一行数据(1, Alice, 25)。 - 最后，从这行数据中提取
age字段的值 25。
可以看到，这个过程需要两次查找：一次是通过二级索引查找指针，一次是通过聚簇索引查找数据。如果查询的数据量很大，回表查询的代价就会非常高。
第三章：如何选择索引？——“因地制宜，量体裁衣”
既然聚簇索引和二级索引各有优缺点，那么在实际应用中，我们应该如何选择呢？
总的原则是：因地制宜，量体裁衣。根据具体的业务场景和查询需求，选择最合适的索引策略。
一些建议：
- 主键必须要有聚簇索引： 通常情况下，我们会选择主键作为聚簇索引。如果没有显式指定主键，数据库会自动创建一个隐藏的主键索引作为聚簇索引。
- 频繁使用的查询字段可以考虑建立二级索引： 如果某个字段经常被用于
WHERE子句中进行查询，那么可以考虑在这个字段上建立二级索引，提高查询效率。 - 避免过度索引： 索引并不是越多越好。过多的索引会增加数据库的维护成本，并且在插入和更新数据时会降低性能。
- 考虑联合索引： 如果多个字段经常一起出现在
WHERE子句中，可以考虑建立联合索引，提高查询效率。
联合索引：寻宝过程中的“组合拳”
联合索引是指在多个字段上建立的索引。它可以看作是多个单列索引的组合。
举个例子：
如果我们经常需要同时根据 name 和 age 进行查询，可以建立一个 (name, age) 的联合索引。
CREATE INDEX idx_name_age ON user (name, age);联合索引的查询效率通常比多个单列索引更高，因为它可以避免多次索引查找和回表查询。
第四章：索引优化——“磨刀不误砍柴工”
即使选择了合适的索引，也需要进行优化，才能发挥最大的性能。
一些常用的索引优化技巧：
- 避免在
WHERE子句中使用函数或表达式： 这样会导致索引失效，数据库只能进行全表扫描。 - 使用
EXPLAIN命令分析查询语句：EXPLAIN命令可以帮助我们了解查询语句的执行计划，从而发现潜在的性能问题。 - 定期维护索引： 随着数据的不断变化，索引可能会变得碎片化，影响查询效率。可以定期重建索引，提高性能。
第五章：总结——“索引是数据库的翅膀”
索引是数据库的翅膀，可以极大地提高查询效率。但是，索引也需要维护和优化，才能发挥最大的作用。
希望通过今天的讲解，大家对聚簇索引和二级索引有了更深入的了解。记住，选择合适的索引策略，并且不断优化，才能让你的数据库飞起来！🚀
最后，送给大家一句代码诗：
索引如刃，锋利且双刃，
善用则速，滥用则伤身。感谢大家的观看！下次再见！👋