MySQL InnoDB 存储引擎索引那些事儿

InnoDB 存储引擎中，表是根据主键顺序组织存放的，称为索引组织表。每个表都有一个主键，如果没有显示定义主键，则会选择第一个创建的非空唯一索引作为主键，如果没有非空唯一索引，InnoDB引擎则自动创建一个6字节大小指针。

 

1、索引类型

MySQL InnoDB 存储引擎支持 B+ 树索引，哈希索引， 和全文索引。

1）B+ 树

Balance 平衡查找树，所有记录节点都是按键值大小顺序存储在同一层的叶子节点上，各叶子节点通过指针进行链接。

插入删除数据时，B+ 树总会保持平衡。B+ 树的高度一般在2-4层，也就是查找某一键值的行记录时，只需要2-4次的IO，查询时间短。

B+ 树索引分为聚集索引和辅助索引，聚集索引就是索引和数据存储在一起的索引类型，B+ 树聚集索引就是在叶子节点上存储整行数据。

辅助索引的叶子节点存储键值，和对应的行记录的聚集索引键。

2）哈希索引

哈希算法在查找数据时，只需要一次磁盘 IO，查找速度非常快，用于精确定位。因为不支持范围查找，实际使用比较少。

InnoDB引擎使用除法散列方式的哈希函数，冲突机制使用的是链表方式，并且支持自适应哈希索引，默认开启，可以通过 innodb_adaptive_hash_index 来禁止此特性。

3）全文索引

根据最左匹配的原则，B+ 树不适合 like '%word%'这样的查询，于是引出了全文检索，即将存储在数据库中的任意内容查找出来的技术。

在InnoDB引擎中，对一个表只能建立一个全文索引，并且不支持没有单词界定符的语言，如中、日、韩语等。

所以一般搜索引擎，都是使用另外的技术来实现全文检索，比如ElasticSearch。

 

2、联合索引

一般查询条件中都会使用到两个或更多个字段，针对此情况，可以创建联合索引，即在多个字段上建立索引。

联合索引建立原则，最左匹配和区分度最高的字段往前放。

 

3、覆盖索引

覆盖索引指的是从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录；因为辅助索引存储表记录的部分字段，比聚集索引小，所以IO操作少。

1) 对于统计操作，例如 count(*)，有时候根据查询条件判断不会使用覆盖索引，但是优化器还是会选择辅助索引。

例如，表table有联合索引，使用a、b两个字段，查询语句为：

select count(*) from table where b > '2020-01-01'

2) 对于不能进行索引覆盖的查询，但是查找的数据是少量（20%）的情况下，MySQL 查询优化器还是会选择辅助索引。

例如，表table有多个字段，只在a、b两个字段上建立联合索引，查询语句为：

select * from table where a< '2020-01-01'

当查询数据比例少于20%的时候，会使用覆盖索引，即使用联合索引。

 

4、索引下推

索引下推，Index Condition Pushdown， 是  MySQL 5.6 版本开始支持的查询优化方式，指的是在索引取出时，就会进行 where 查询条件过滤，然后才去获取数据记录。

支持 range，ref，eq_ref, ref_or_null 类型查询，支持MyISAM和InnoDB存储引擎。当优化器进行索引下推时，SQL的执行计划结果 extra列会显示 Using index condition。