数据库索引的原理与优化 1. 索引的基本概念 索引是数据库中对某一列或多列的值进行预排序的数据结构，类似于书籍的目录。它的核心作用是 加快数据检索速度 ，但会增加存储空间和写操作（增删改）的开销。常见的索引类型包括： B+树索引 （最常用，适用于范围查询和等值查询） 哈希索引 （适用于等值查询，但不支持范围查询） 全文索引 （用于文本关键词搜索） 2. 索引的底层结构（以B+树为例） 步骤1：为什么用B+树而不是二叉树？ 二叉树在极端情况下可能退化为链表（如插入有序数据），查询效率从O(log n)退化为O(n)。 B+树是多路平衡查找树，每个节点可存储多个键值和指针，树高度更低（通常3~4层即可存储亿级数据），减少磁盘I/O次数。 步骤2：B+树的特点 非叶子节点只存储键值和子节点指针，不存储实际数据； 叶子节点存储全部数据（或主键指针），并通过链表串联，便于范围查询； 数据均存储在叶子节点，查询效率稳定（每次查询都要走到叶子节点）。 3. 索引的优化策略 步骤1：索引的选择性 选择性高的列（如唯一ID）更适合建索引，重复值少的列能更快过滤数据。 公式： 选择性 = 不重复值数量 / 总行数 ，越接近1效果越好。 步骤2：最左前缀原则（针对复合索引） 复合索引（如 (name, age) ）只能从左到右匹配。 有效用例： WHERE name='Alice' 、 WHERE name='Alice' AND age=20 ； 失效用例： WHERE age=20 （无法跳过 name 直接使用索引）。 步骤3：避免索引失效的常见场景 对索引列使用函数或计算（如 WHERE UPPER(name)='ALICE' ）； 使用 != 、 NOT IN （部分数据库可能全表扫描）； 模糊查询以通配符开头（如 LIKE '%abc' ）。 4. 索引的代价与使用场景 优点 ：显著加速查询、支持排序和分组（如 ORDER BY 索引列可避免额外排序）。 缺点 ： 占用磁盘空间； 写操作需更新索引，降低插入/更新速度； 过多索引会增加查询优化器的选择成本。 5. 实战建议 优先为查询频繁、过滤性高的列建索引； 使用 EXPLAIN 分析SQL执行计划，观察是否命中索引； 定期清理冗余索引（如MySQL可通过 sys.schema_unused_indexes 查看未使用索引）。 通过以上步骤，你可以理解索引如何平衡查询效率与存储开销，并在实际场景中合理设计索引。