数据库索引优化指南

5.3 创建高性能的索引

一、索引类型

1. B-Tree 索引

• 特点：对索引列顺序组织存储，适合查找范围数据
• 支持的查询：
  • 全值匹配
  • 匹配最左前缀
  • 匹配列前缀
  • 匹配范围值
  • 精确匹配某一列并范围匹配另一列
  • 只访问索引的查询
  • 排序
• 限制：
  • 非最左列不匹配
  • 不能跳过索引中的列
  • 某列范围查询时，其余索引列无法使用

2. 哈希索引

• 特点：只有精确匹配索引所有列的查询才有效
• 优势：
  • 索引只存储哈希值，结构紧凑，查询速度快
• 限制：
  • 不存储索引值，需要读取行
  • 无法用于排序
  • 不支持部分索引列匹配
  • 只支持等值比较查询
  • 可能发生哈希冲突
  • 哈希冲突较多时，索引维护操作代价高
• 备注：InnoDB内部会自动创建自适应哈希索引

3. R-Tree 索引

• 专为多维数据（如空间数据）设计，适合地理空间查询

4. 全文索引

• 专为文本搜索优化，支持全文检索和模糊匹配

二、高性能索引策略

1. 独立的列

• 索引列不能是表达式的部分
• 例如：WHERE column = value 而不是 WHERE column + 1 = value

2. 前缀索引和索引的选择性

• 适用于很长的字符列（BLOB、TEXT或很长的varchar）
• 优点：索引更小，检索速度更快
• 缺点：无法使用前缀索引做GROUP BY、ORDER BY和覆盖扫描

3. 多列索引

• 使用场景：
  • 当查询需要对多个索引做相交操作时，使用包含所有相关列的多列索引
  • 当查询需要多个索引做联合操作（EXPLAIN的Extra包含Using union）时，可能耗费更多CPU和内存资源
  • EXPLAIN的type列中包含index_merge表示使用了索引合并
• 优化建议：使用IGNORE INDEX让优化器忽略某些索引

4. 选择合适的索引列顺序（B-Tree）

• 经验法则：将选择性最高的列放在前面
• 备注：经验法则考虑的是全局基数和选择性，实际使用中需考虑表中值的分布情况

5. 聚簇索引

• 特点：
  • 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替
  • 如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引
• 优点：
  • 相关数据保存在一起
  • 数据访问更快（索引和数据保存在同一个B-Tree中）
  • 覆盖索引扫描查询可直接使用页节点中的主键值
• 缺点：
  • 仅在I/O密集型应用中提高性能
  • 插入速度严重依赖插入顺序
  • 更新聚簇索引列代价高
  • 可能面临"页分裂"问题
  • 可能导致全表扫描变慢

三、顺序主键

1. 非循序主键问题

• 问题：
  • 写入目标页可能已从缓存中移除，导致大量随机I/O
  • 乱序写入导致页分裂，需要移动大量数据
  • 页分裂导致数据碎片
• 解决方案：使用OPTIMIZE TABLE重建表并优化页填充

2. 顺序主键问题

• 问题：
  • 并发插入可能导致主键上界成为"热点"，出现间隙锁竞争
  • AUTO_INCREMENT的锁机制成为"热点"

四、覆盖索引

• 定义：一个索引包含所有需要查询的字段值
• 特点：
  • 查询字段按需读取，当所有字段都包含在索引中时，存储引擎无需回表查找行（减少随机I/O）
  • MySQL在索引中不能做以通配符开头的LIKE查询
  • EXPLAIN的Extra列包含"Using index"表示为索引覆盖查询

五、使用索引扫描来做排序

• 特点：
  • EXPLAIN的type列包含index表示使用了索引扫描来做排序
  • 仅当索引列顺序和ORDER BY顺序完全一致，且所有列排序方向相同时，才能使用索引排序
  • 如果前导列为常量（如WHERE a='1'），则ORDER BY子句可以不满足索引最左前缀要求

六、冗余和重复索引

• 特点：
  • InnoDB主键列已包含在二级索引中，通常无需重复包含主键
  • 冗余索引用于满足不同查询场景，但会影响写操作性能

七、未使用的索引

• 检测方法：
  • Percona Server或MariaDB：打开userstates服务器变量，运行后查询INFORMATION_SCHEMA.INDEX_STATISTICS
  • Percona Toolkit：使用pt-index-usage工具读取查询日志分析索引使用情况

八、索引和锁

• 特点：
  • EXPLAIN的Extra列包含"Using where"表示存储引擎返回行后应用WHERE过滤条件
  • 索引可以减少需要锁定的行数

九、索引案例学习

• 优化建议：
  • 尽可能将需要范围查询的列放到索引后面，以便优化器使用尽可能多的索引列
  • 对于范围条件查询，MySQL无法使用范围列后面的其他索引列
  • 对于多等值查询（如WHERE a=1 AND b=2），则无此限制

关键总结：在设计数据库索引时，应根据实际查询模式和数据分布情况，选择合适的索引类型和结构，避免冗余索引，确保查询性能和写入性能的平衡。