# 基础
索引在引擎层实现,不同存储引擎索引的实现不同。
# B-Tree 索引
非叶子节点存储索引键值,叶子结点存储实际数据。
索引由多个列组成时,只对以下类型查询有效,适配 WHERE、ORDER BY 和 GROUP BY:
- 全值匹配,匹配索引中的所有列
- 匹配最左前缀,匹配索引中的第一列
- 匹配列前缀,匹配索引中第一列的值的开头部分
- 匹配范围值,匹配索引中第一列的范围
- 精确匹配某一列并范围匹配另外一列,匹配的列中前面的列精确匹配,最后的列范围匹配
- 只访问索引的查询,即覆盖索引
# 哈希索引
只有精确匹配所有列的查询才有效
限制:
- 只包含哈希值和行指针,不存储字段值,不能覆盖索引
- 不按照索引值顺序存储,无法排序
- 不支持部分索引列匹配查找
- 只支持等值比较查询,不支持任何范围查询
- 访问索引数据非常快,有哈希冲突时需要遍历链表
- 索引维护代价高,删除时也需要遍历链表
# 自定义哈希索引
如果某个字段长度特别长,用它来做 B-Tree 索引不太合适,可再创建一个它的哈希值字段,用这个字段作为索引列
# 其他索引
- 空间数据索引(R-Tree)
- 全文索引
# 索引优点
- 减少服务器扫描的数据量
- 帮助服务器避免排序和临时表
- 将随机 IO 变为 顺序 IO
# 高性能的索引策略
- 独立的列,索引列不能是表达式的一部分。
- 前缀索引和索引选择性,前缀索引可以节约索引空间,提高索引效率,降低索引选择性。索引的选择性指,不重复的索引值和数据表的记录总数的比值,越大性能越好。
- 多列索引,多个单列索引性能不如一个多列索引。
- 合适的索引顺序,如果有 ORDER BY、GROUP BY 和 DISTINCT 等要考虑避免随机 IO 和排序,如果没有时,要将选择性最高的列放在前面。
# 聚簇索引
叶子节点包含了全行的数据,非叶子结点包含索引列,一般每个表只有一个聚簇索引,InnoDB 支持聚簇索引,主键为索引列。如果没有定义主键,InnoDB 会隐式定义一个主键作为聚簇索引。
优点:
可以通过主键列直接访问到所有相关数据,访问更快
缺点:
- 只是提高了磁盘访问速度,并不针对内存
- 插入速度严重依赖于插入顺序,所以一般主键设置自增
- 更新代价高,需要移动的数据多
- 插入或更新可能会导致页分裂问题,会导致占用更多的磁盘空间
- 行比较稀疏时或页分裂导致数据存储不连续时。可能导致全表扫描变慢
- 二级索引叶子节点需要包含主键列,会更大
- 二级索引需要回表访问两次 B-Tree
# InnoDB 和 MyISAM 的数据分布对比
MyISAM 的主键索引和二级索引结构一样,叶子节点都不会存储行的所有数据,而是存储行号;InnoDB 的主键索引是聚簇索引,叶子节点会存储行的所有数据,二级索引叶子节点存储的只是主键列的值。
# 覆盖索引
指索引包含所有需要查询的字段。
优点:
- 减少数据访问量
- 顺序存储减少磁盘 IO
- 不需要额外的系统调用
- 不需要回表
# 索引扫描做排序
优点:
可以顺序读取。
能使用索引顺序的查询:
- 索引列顺序和 ORDER BY 子句顺序完全一样,且均为倒序或正序
- 多表关联时,ORDER BY 子句的列必须全部是第一个表
- 需要满足最左前缀,除非最左边的列值是常数
不能使用索引排序的查询:
- 列排序方向不同
- 有一个不在索引的列
- where 和 order by 无法组成最左前缀
- 前面的列是范围条件
- 在某个列上有多个等于条件,也相当于范围查询
- 多表关联主表被优化器优化成关联表
# 压缩索引
MyISAM 使用前缀压缩来减少索引大小。
# 冗余和重复索引
应尽量避免容易和重复索引,尽量扩展已有索引,只有当索引已经很大时再创建新索引。
# 索引和锁
索引减少访问的数据行数,也减少锁的数量。没有在索引范围内的行,即便会在 where 条件中被过滤掉,也会进行加锁。