索引的作用

  • 提高查询效率

  • 消除数据分组/排序

  • 避免”回表”查询

  • 优化聚合查询

  • 用于多表JOIN关联查询

  • 利用唯一性约束，保证数据唯一性

  • InnoDB行锁实现

  • 增加I/O成本

  • 增加磁盘空间

  • 不适合的索引，或索引过多，都不是好事

索引类型

  • BTREE，InnoDB & MyISAM

  • Fractal TREE，TokuDB

  • HASH HEAP NDB InnoDB AHI

  • RTREE

  • FULLTEXT

聚集索引

  • 一种特殊索引，其key值顺序决定了表数据行的 物理（相对）

  • 每张表只能建一个聚集索引，除了TokuDB引擎

  • InnoDB中，聚集索引即表，表即聚集索引

  • MyISAM没有聚集索引的概念

  • 聚集索引优先选择列

    • INT/BIGINT

    • 数据连续（单调顺序）递增/自增

  • 不建议的聚集索引

    • 修改频繁的列

    • 新增数据太过离散

主键索引

  • 主键由表中的一个或多个字段组成

  • 用于唯一地标识表中的某一个记录&外键约束；

  • 主键值不能重复，也不能包含NULL；

  • InnoDB聚集索引选择次序原则

    • 显示声明的主键

    • 第一个NOT NULLABLE的唯一索引

    • ROWID（实例级，6bytes）

  • InnoDB主键特点

    • 索引定义时，不管有无显式包含主键，实际都会存储主键

    • 在5.6.9后，优化器已能自动识别索引末尾的主键值(Index Extensions)，在这之前则需要显示加上主键列才可以被识别

    • WHERE c1=? AND PK=?

    • WHERE c1=? ORDER BY PK

辅助索引

  • 非聚集索引，或者二级索引，俗称普通索引

  • 当通过InnoDB辅助索引来查找数据的时候，辅助索引会通过页级的指针来找到主键索引的主键，然后通过该主键索引找到相应的行数据

联合索引

  • 多列组成，所以也叫多列索引

  • 适合WHERE条件中的多列组合

  • 有时候，还可以用于避免回表（覆盖索引）

  • MySQL还不支持多列不同排序规则（MySQL8.0起支持）

  • 联合索引建议

    • WHERE条件中，经常同时出现的列放在联合索引中

    • 把选择性（过滤性/基数）大的列放在联合索引的最左边

部分索引

  • 部分索引的原因

    • char/varchar太长全部做索引的话，效率太差，存在浪费

    • 或者blob/text类型不能整列作为索引列，因此需要使用前缀索引

  • 部分索引选择建议

    • 统计平均值

    • 满足80%-90%覆盖度就够

  • 缺点

    • 无法利用前缀索引完成排序

覆盖索引

  • 通过索引数据结构，即可直接返回数据

InnoDB索引

  • 索引最大长度767bytes

  • 启用innodb_large_prefix，增加到3072bytes，只针对DYNAMIC、COMPRESSED格式管用

  • 对于REDUNDANT、COMPACT格式，最大索引长度还是767bytes

  • MyISAM表索引最大长度是1000bytes

  • 最大默认排序长度1000bytes

索引统计

  • show index from table

  • select * from I_S.STATISTICS

  • mysql.innodb_index_xxxx

  • innodb_stats_auto_recalc

    • 默认启用