Table of Contents
自动创建的索引#
主键索引#
- 当对表添加主键约束时，由数据库自动创建主键索引，在
InnoDB中自动变成聚簇索引，每个表只能有一个主键索引 - 仍通过
B+树存储，但聚簇索引的结构是将数据行存储在叶子节点中
外键索引#
- 当对表添加外键约束时，由数据库自动创建
- 在引用列上创建普通索引
隐式索引#
手动创建的索引#
普通索引#
- 通过
CREATE INDEX idx_name ON table(column)创建 - 通过
B+树存储，其中叶子节点存储主键值（而不是完整的数据行），非叶子节点存储索引值，叶子节点之间会形成双向链表 - 查询：回表查询，获取叶子节点的主键后查询主键索引，得到完整的数据行
- 插入：根据
B+树的插入规则插入 - 删除：标记搜索到的叶子节点为删除
- 更新：仅索引列更新时会影响该索引，先删除旧记录再插入新记录，代价是最高的操作
- 索引合并：
- 交集合并：查询条件涉及多个索引时，会分别获取符合条件的主键集合，然后取交集，用于
AND连接 - 并集合并：用于
OR连接
- 索引下推(
Index Condition Pushdown，ICP)： - 覆盖索引优化
唯一索引#
- 通过
CREATE UNIQUE INDEX idx_name ON table(column)创建 - 在普通索引的基础上，支持快速唯一性检查
全文索引#
- 通过
CREATE FULLTEXT INDEX idx_name ON table(column)创建 - 通过倒排索引结构实现
- 支持自然语言搜索，只能对文本类型建立索引
空间索引#
- 通过
CREATE SPATIAL INDEX idx_name ON table(column)创建 - 通过
R树存储
索引生效规则#
组合索引与最左前缀原则#
- 最左前缀原则是
B+树带来的特性，B+树在针对组合索引时，根据从左到右的顺序进行排序（建立索引） - 非叶子节点只会存储一个键，越先考虑的索引键越接近根节点，越右的索引键则在左边键的子树下继续建立索引
- 最左前缀原则指，查询条件所涉及的索引键必须是组合索引的一个最左前缀（即从最左字段开始连续使用索引列），才能使用上这个组合索引
查询条件#
- 第一个范围查询之后的查询条件均无法使用索引，因为范围查询后在
B+树中找到的是多个非叶子节点，它们的子树无法保证下一个索引列的完全有序 - 等值查询一定生效
- 不等查询（仅
!=）无法生效 NULL查询一定生效- 模糊查询：只有
前缀%可以使用索引 - 涉及函数或运算、以及涉及类型转换的无法使用索引
OR查询与AND查询可以触发索引合并，但如果有部分列没有索引，则可能全表扫描IN查询可以看作多个OR，如果列表过大也可能选择全表扫描- 总之，所有会影响查询条件无序的操作都可能使得索引失效