索引定义、作用和分类
索引的定义
索引（Index）是一种数据结构，用于快速定位和访问数据集合中的特定信息。它类似于书籍的“目录”，通过建立关键值与数据位置的映射关系，减少数据检索时的扫描范围，从而提升查询效率。索引广泛应用于数据库、文件系统、搜索引擎等领域。
核心作用
-
加速查询
- 避免全表扫描（如数据库中的
SELECT * FROM table），直接通过索引定位目标数据。 - 时间复杂度从O(n)O(n)（线性扫描）降低到O(logn)O(logn)（树结构索引）或O(1)O(1)（哈希索引）。
- 避免全表扫描（如数据库中的
-
保证数据唯一性
- 唯一索引（Unique Index）确保某列的值不重复（如身份证号）。
-
优化排序与连接
- 对排序（
ORDER BY）或连接（JOIN）操作提供预排序支持，减少计算开销。
- 对排序（
常见索引类型
1. 按数据结构分类
-
B树/B+树索引：
- 数据库最常用的平衡树结构，支持范围查询（如
WHERE age > 20）和排序。 - B+树非叶子节点仅存键值，叶子节点通过指针链接，适合磁盘存储。
- 数据库最常用的平衡树结构，支持范围查询（如
-
哈希索引：
- 基于哈希表，查询时间复杂度为O(1)O(1)，但仅支持等值查询（如
WHERE id = 5），不支持范围查询。
- 基于哈希表，查询时间复杂度为O(1)O(1)，但仅支持等值查询（如
-
全文索引：
- 针对文本内容的关键词搜索（如
MATCH(content) AGAINST('keyword')）。
- 针对文本内容的关键词搜索（如
-
位图索引：
- 适用于低基数列（如性别），通过位图标记数据存在性。
2. 按数据库特性分类
-
聚集索引（Clustered Index）：
- 数据按索引键值物理排序（如InnoDB的主键索引），一张表只能有一个。
-
非聚集索引（Non-Clustered Index）：
- 索引与数据分离，通过指针指向数据位置（如书籍目录指向页码），一张表可有多个。
索引的优缺点
| 优点 | 缺点 |
|---|---|
| 显著提升查询速度 | 占用额外存储空间 |
| 加速表连接和排序操作 | 增删改操作变慢（需维护索引） |
| 唯一性约束保障数据完整性 | 不合理的索引可能降低性能 |
索引的适用场景
- 高频查询字段（如用户ID、订单号）
- 常作为
WHERE条件的列 - 需要排序或分组的列（如
ORDER BY create_time） - 外键关联的列（加速表连接）
示例：数据库索引工作原理
假设表users包含id, name, age三列，对age创建索引：
- 无索引时：查询
WHERE age = 25需逐行扫描所有记录。 - 有索引时：
- 通过B+树快速定位
age=25的节点，直接获取对应数据地址。 - 减少磁盘I/O，查询效率提升数十倍。
- 通过B+树快速定位
索引设计原则
- 避免过度索引：仅对关键字段建索引。
- 选择区分度高的列（如身份证号而非性别）。
- 联合索引注意顺序：将高频查询条件放在最左侧。
- 定期维护：重建碎片化索引以保持性能。
总结
索引是数据检索的“加速器”，通过空间换时间的策略优化查询性能。合理设计索引是数据库调优的核心步骤，但需权衡读写开销，避免滥用。
特性
1. B+树：索引的核心数据结构
（1）核心特性
| 特性 | 说明 |
|---|---|
| 层数少 | 每个节点存储大量键值（如16KB页），大幅降低树高度，减少磁盘I/O次数 |
| 范围查询高效 | 叶子节点通过双向链表连接，支持快速顺序遍历（如WHERE id > 100） |
| 数据全在叶子 | 非叶子节点仅存键值（不存数据），叶子节点存储完整数据或主键（见聚簇索引） |
（2）对比B树：
- B树所有节点存储数据，范围查询需多次回溯；B+树只需遍历叶子链表，性能更优。
2. 聚簇索引 vs 非聚簇索引
| 特性 | 聚簇索引（主键索引） | 非聚簇索引（二级索引） |
|---|---|---|
| 存储结构 | 数据行按主键顺序物理存储，叶子节点即数据页 | 独立结构，叶子节点存储主键值（非数据行） |
| 查询流程 | 直接通过主键定位数据 | 需先查索引获取主键，再回表查询数据（二次I/O） |
| 数量限制 | 每表仅1个 | 可创建多个 |
| 性能影响 | 主键范围查询极快 | 回表可能导致性能损耗 |
示例（MySQL InnoDB）：
-- 聚簇索引：表数据存储即主键索引
CREATE TABLE user (
id INT PRIMARY KEY, -- 聚簇索引
name VARCHAR(20),
INDEX idx_name (name) -- 非聚簇索引
);3. 最左前缀原则
（1）定义
联合索引(a, b, c)生效的查询条件必须从最左列开始，且不可跳过中间列。
（2）生效场景
| 查询条件 | 是否使用索引 | 原因 |
|---|---|---|
WHERE a=1 | ✔️ | 命中最左列 |
WHERE a=1 AND b=2 | ✔️ | 连续命中前两列 |
WHERE b=2 | ❌ | 未命中最左列 |
WHERE a=1 AND c=3 | ✔️（仅a） | 跳过了b列，c无法使用索引 |
4. 覆盖索引（Covering Index）
（1）定义
查询的字段全部包含在索引中，无需回表即可返回结果。
（2）示例
-- 创建索引
CREATE INDEX idx_user ON user(name, age);
-- 覆盖索引查询
SELECT name, age FROM user WHERE name = 'Alice'; -- 直接通过索引返回数据
-- 非覆盖索引查询
SELECT name, age, email FROM user WHERE name = 'Alice'; -- 需回表查询email（3）联合索引 vs 覆盖索引
| 要点 | 联合索引 | 覆盖索引 |
|---|---|---|
| 核心价值 | 加速多条件查询和排序 | 避免回表，减少I/O |
| 设计关键 | 列顺序和查询条件匹配 | 索引包含所有查询字段 |
| 性能影响 | 可能仍需回表 | 直接通过索引返回结果 |
| 关系 | 覆盖索引可通过扩展联合索引实现 | 覆盖索引是联合索引的一种高阶用法 |
5. 索引失效场景
（1）函数操作或表达式
-- 失效：对索引列使用函数
SELECT * FROM user WHERE UPPER(name) = 'ALICE';
-- 失效：对索引列进行运算
SELECT * FROM user WHERE id + 1 = 100;（2）隐式类型转换
-- 失效：name为字符串类型，但查询使用数字
SELECT * FROM user WHERE name = 123; -- 等价于 WHERE CAST(name AS INT) = 123（3）其他常见失效场景
| 场景 | 示例 |
|---|---|
| OR条件非全索引覆盖 | WHERE id=1 OR email='a@example.com' |
| 前导通配符LIKE | WHERE name LIKE '%abc%' |
| 索引列参与计算 | WHERE YEAR(create_time) = 2023 |
6. 索引设计建议
- 高频查询字段优先：WHERE、JOIN、ORDER BY字段优先建索引。
- 控制索引数量：过多索引会增加写操作开销。
- 利用覆盖索引：将频繁查询的字段包含在索引中。
- 避免冗余索引：如
(a, b)和(a)同时存在时，后者冗余。
附：索引失效检测工具
- EXPLAIN命令：分析SQL执行计划，观察
key字段是否命中索引。
EXPLAIN SELECT * FROM user WHERE name = 'Alice';- 输出关键字段：
type: ref（索引范围扫描）Extra: Using index（覆盖索引）