6.1数据库索引优化

Btree索引和Hash索引

mysql的索引是在存储引擎层实现的，而不是在mysql的服务器中实现的，不同的存储引擎可能索引工作的方式是不同的，即使是同一种类型的索引，在不同的存储引擎上底层实现也不尽相同

Mysql支持的索引类型

• B-tree索引：通常我们说的索引就是指它

• Hash索引

B-tree索引的特点：

• B-tree索引使用B+树来存储数据

• B-tree索引能够加快数据的查询速度

• B-tree索引更适合进行范围查找

提示：在B+树中，每一个叶子节点都包含指向下一个叶子节点的指针，方便进行叶子节点之间的遍历

B+树是一个平衡的查找树，没一个叶子到根部的距离相同

所有的记录节点都是按照键值大小顺序存放的

叶子节点之间是使用指针进行连接的

对于不同的存储引擎，具体实现可能有所不同，例如MyISAM存储在叶子节点中通过数据的物理位置来引用索引的行的，而InnoDB是通过主键来引用索引的行的

指针定义了子节点中值的上限和下限

叶子节点的指针指向的是被索引的数据，而不是其他叶子节点

在MyISAM存储引擎中，指针指向的是数据的物理地址；在InnoDB存储引擎中，指针指向的是主键

B树索引的应用场景

• 全值匹配的查询

  字段name=“sophia”

• 匹配最左前缀的查询

  字段name和字段age的联合索引

• 匹配列前缀查询

  字段name like “soph%”

• 匹配范围值的查询

  6< age <18

• 精确匹配左前列并范围匹配另外一列

• 只访问索引的查询

Btree索引的使用限制

使用索引命中的数据占用了表中的大部分数据时，mysql的查询优化器可能认为使用全表扫描比使用索引性能更高，所以即使你在查询的字段上使用了索引，也不会使用索引的方式来查询数据

• 如果不是按照索引最左列开始查找，则无法使用索引

• 使用索引时不能跳过索引中的列

• Not in 和 <> 操作无法使用索引

• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

Hash索引的特点

• Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引

• 对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码

Hash索引的限制

• Hash索引必须进行两次查找

使用Hash索引查找需要需要进行两次读取，Hash索引中包括的只是键值和Hash码以及对应的行的指针，索引中并没有保存字段的值，使用Hash索引时需要，通过Hash索引找到对应的行→通过行号读取行数据

• Hash索引无法用于排序

• Hash索引不支持部分索引查找也不支持范围查找，只支持全值匹配查找

• Hash索引中Hash码的计算可能存在Hash冲突

为什么要使用索引

• 索引大大减少了存储引擎需要扫描的数据量（索引文件大小通常远远小于数据文件大小）

  InnoDB发生一次IO以页（16kb）为单位的

• B-tree索引可以帮助我们进行排序以避免使用临时表

• B-tree索引可以把随机I/O变为顺序I/O

索引是不是越多越好

• 索引会增加写操作的成本

• 大多的索引会增加查询优化器的选择时间