索引基础

B-TREE

MyISAM B-TREE 结构MyISAM B-TREE 结构

来源见水印

  • 基于 B-TREE 的查询类型

    • 全值匹配
    • 匹配最左前缀
    • 匹配列前缀
    • 匹配范围值
    • 精准匹配某一列并范围匹配另外一列
    • 只访问索引的查询

  • 关于 B-TREE 的查询限制

    • 不是按照最左列查询的，则无法使用索引
    • 不能跳过索引中的列
    • 如果某一列使用范围查询，则其右边的列无法使用索引优化查询

hash索引

  • 关于 hash 索引的查询限制
    • 索引数据不是顺序存储，所以不可排序
    • 不支持部分索引列匹配
    • 只支持等值比较查询
    • hash 冲突时只能逐行扫描
    • hash 冲突高时维护操作代价很大

高性能索引策略

独立的列

索引列不应该是表达式的一部分，也不能是函数的参数。

前缀索引和索引选择性

  • 索引选择性
    不重复的索引值和数据表的记录总数的比值（不重复的索引值/记录总数）比例越高索引效率越高。

  • 前缀索引
    解决大字段建立索引占用空间过大的问题。
    ALERT TABLE table_name ADD KEY (field(length))

  • 前缀索引无法 GROUP BY 和 ORDER BY

多列索引

and 条件 需要包含多个相关列的多列索引，而非多个独立的单列索引

选择合适的索引列顺序

聚簇索引

在 B-TREE 索引的叶子页上存储了数据行，因为数据行不能存储在多个地方所以一个表只能有一个聚簇索引。

为了保证少的存储碎片，聚簇索引应当选择自增类型的数据做索引值

覆盖索引

索引包含索要查询字段的值，则称之为覆盖索引，这种索引不需要会查数据行。

InnoDB 的二级索引在叶子节点存储的是行的主键值，所以，如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

使用索引扫描排序

冗余和重复索引

多列索引和单列索引，如果多列索引可以通过前缀索引，则说明该单列索引是重复的。

尽量避免创建多余的索引，应当考虑拓展现有索引。

索引和锁

InnoDB实在读取行的时候加锁的，所以索引可以减少行的读取，从而减少了加锁操作。