MySQL之Btree索引和HASH索引的区别以及索引优化策略

索引是帮助mysql获取数据的数据结构。最常见的索引是Btree索引和Hash索引。

不同的引擎对于索引有不同的支持：Innodb和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引。

我们在mysql中常用两种索引算法BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。
一、BTree
BTree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，例如：
select * from user where name like ‘jack%’;
select * from user where name like ‘jac%k%’;
如果一通配符开头，或者没有使用常量，则不会使用索引，例如：
select * from user where name like ‘%jack’;
select * from user where name like simply_name;
二、Hash
Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。
但为什么我们使用BTree比使用Hash多呢？主要Hash本身由于其特殊性，也带来了很多限制和弊端：
1. Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询。
2. 联合索引中，Hash索引不能利用部分索引键查询。
对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash索引无法被利用。
3. Hash索引无法避免数据的排序操作
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。
4. Hash索引任何时候都不能避免表扫描
Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。
5. Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高
对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

Hash索引

所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO，但是innodb和myisam之所以没有采用它，是因为它存在着好多缺点：

1、因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询

1、每次都要全表扫描

2、由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作

3、不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。

4、当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的。

Btree索引

至于Btree索引，它是以B+树为存储结构实现的。

但是Btree索引的存储结构在Innodb和MyISAM中有很大区别。

在MyISAM中，我们如果要对某张表的某列建立Btree索引的话，如图：

所以我们经常会说MyISAM中数据文件和索引文件是分开的。

因此MyISAM的索引方式也称为非聚集，Innodb的索引方式成为聚集索引。

至于辅助索引，类似于主索引，唯一区别就是主索引上的值不能重复，而辅助索引可以重复。

因此当我们根据Btree索引去搜索的时候，若key存在，在data域找到其地址，然后根据地址去表中查找数据记录。

至于Innodb它跟上面又有很大不同，它的叶子节点存储的并不是表的地址，而是数据

我们可以看到这里并没有将地址放入叶子节点，而是直接放入了对应的数据，这也就是我们平常说到的，Innodb的索引文件就是数据文件，

那么对于Innodb的辅助索引结构跟主索引也相差很多，如图：

我们可以发现，这里叶子节点存储的是主键的信息，所以我们在利用辅助索引的时候，检索到主键信息，然后再通过主键去主索引中定位表中的数据，这就可以说明Innodb中主键之所以不宜用过长的字段，由于所有的辅助索引都包含主索引，所以很容易让辅助索引变得庞大。

我们还可以发现：在Innodb中尽量使用自增的主键，这样每次增加数据时只需要在后面添加即可，非单调的主键在插入时会需要维持B+tree特性而进行分裂调整，十分低效。

Btree索引中的最左匹配原则：
Btree是按照从左到右的顺序来建立搜索树的。比如索引是(name,age,sex)，会先检查name字段，如果name字段相同再去检查后两个字段。

所以当传进来的是后两个字段的数据（age，sex），因为建立搜索树的时候是按照第一个字段建立的，所以必须根据name字段才能知道下一个字段去哪里查询。

所以传进来的是（name，sex）时，首先会根据name指定搜索方向，但是第二个字段缺失，所以将name字段正确的都找到后，然后才会去匹配sex的数据。

建立索引的规则：
1、利用最左前缀：Mysql会一直向右查找直到遇到范围操作（>，<，like、between）就停止匹配。比如a=1 and b=2 and c>3 and d=6；此时如果建立了（a,b,c,d）索引，那么后面的d索引是完全没有用到，当换成了（a,b,d,c）就可以用到。

2、不能过度索引：在修改表内容的时候，索引必须更新或者重构，所以索引过多时，会消耗更多的时间。

3、尽量扩展索引而不要新建索引

4、最适合的索引的列是出现在where子句中的列或连接子句中指定的列。

5、不同值较少的列不必要建立索引（性别）。

BTree索引

  • 使用B+树存储数据
  • B+树索引能够加快数据的查询的速度
  • 更加适合进行范围查找
  • 什么情况下用到BTree索引？
  1. 全值匹配的查询
  2. 匹配最左前缀的查询
  3. 匹配列前缀查询
  4. 范围查找
  5. 精确匹配左前列并范围匹配另外一列
  6. 只访问索引的查询

BTree索引的限制

  1. 如果不是按照索引最左列开始查找，那么无法使用索引。
  2. 使用索引时不能跳过索引中的列。
  3. not in、<>、!=操作无法使用索引。
  4. 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。

Hash索引

  • Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的列时才能够使用Hash索引。
  • 对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。

Hash索引的限制

  • Hash索引中包含的只是Hash码与行指针，因此必须进行二次查找。
  • Hash索引的建立是由Hash码构成的，因此Hash索引无法用于排序。
  • Hash索引不支持部分索引查找也不适合范围查找。
  • Hash索引中Hash码的计算可能存在Hash冲突。

为什么使用索引

  • 索引可以减少存储引擎需要扫描的数据量。
  • 索引可以帮助我们进行排序以避免临时表。
  • 索引可以把随机IO变为顺序IO。

索引的性能成本

  • 由于在写入数据时也要维护索引，因此索引会增加写操作的成本。
  • 太多的索引会导致查询优化器的时间，因为查询优化器要在很多索引中选择出最合适的索引。

索引优化策略

  • 索引列上不能使用表达式或者是函数。
  • 对于InnoDB来说，索引列大小限制767Byte，对于MyISAM来说是1000Byte。
  • 前缀索引或索引列的选择性，索引的选择性是不重复的索引值和表的记录数的比值。
  • 建立联合索引如何选择索引列的顺序？
  1. 经常会被使用到的列优先。
  2. 选择性高的列优先。
  3. 宽度小的列优先使用。
  • 覆盖索引，包含需要查询的所有行的值
  1. 可以优化缓存，减少磁盘IO
  2. 可以减少随机IO，变成顺序IO
  3. 可以避免对InnoDB主键索引的二次查询
  4. 可以减少MyISAM表进行系统调用
  • 无法使用覆盖索引的情况
  1. 存储引擎不支持覆盖索引
  2. 查询中使用了太多的列
  3. 使用了双%号的like查询
  • 使用索引扫描来优化排序
  1. 索引的列顺序和order by子句的顺序完全一致。
  2. 索引红所有列的升序降序和order by子句完全一致。
  3. order by中字段全部在关联表中的第一张表中。
  • 使用BTree索引模拟Hash索引优化查询
  1. 只能处理键值的全值匹配查找。
  2. 所使用的Hash函数决定着索引键的大小。
  • 利用索引优化锁
  1. 索引可以减少锁定的行数。
  2. 索引可以加快处理速度，同时也加快了锁的释放。
  • 删除重复和冗余的索引
  1. primary key(id), unique key(id), index(id)
  2. index(a), index(a,b)
  3. primary key(id), index(a,id)
  4. 使用工具pt-duplicate-key-checker h=127.0.0.1来检查

更新索引统计信息及减少索引碎片

    1. analyze table 表名，InnoDB存储引擎执行该命令不会锁表只是粗略估算值。
    2. optimize table 表名，使用不当会导致锁表。     

 

Mysql索引类型Btree和Hash的区别以及使用场景

一点PHP博客分享关于mysql中索引类型的区别特性，遇到单表数据量大的时候很多开发者都会想到给相对的字段建立索引来提高性能（mysql索引的使用），但很少会去关注索引的类型该如何选择，在mysql中支持有两种类型，最常用的也是默认的Btree类型，其次就是最容易被忽略的Hash类型。下面将分别介绍两种索引类型的区别。

Btree类型的索引，Btree又称b+树

Mysql索引类型Btree和Hash的区别以及使用场景

（1）所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；（只有根节点存储关键字最后树的末梢才有值)

（2）非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层。（非根节点，存储的其实是指向根节点的索引）

（3） 不可能在非叶子结点存数据。

（4）根节点横向也有链指针(方便快速顺藤摸瓜嘛，没这个指针，就算下一个取的值是挨着的邻居，也得跑个圈才能拿到)

通过上述分析，所以能直观的理解出Btree类型在我们查询数据时适合用于范围查找，在某一叶子的节点到另一节点的范围。例如<,>,orderby等场景都可使用。

Hash索引，其检索效率非常高的一种精确定位索引。

Hash索引不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引，它会将计算出的Hash值和对对应的行指针信息记录在Hash表中。但是虽然Hash效率很高但是同样也有很多的弊端存在和限制存在。

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

（2）Hash 索引无法被用来避免数据的排序操作。

（3）Hash 索引不能利用部分索引键（组合索引）查询。

（4）Hash 索引在任何时候都不能避免表扫描。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

因为Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤。经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样，数据库自然也无法利用索引的数据来避免任何排序运算。

Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

注：关于选择索引类型的使用，只能说根据不同的条件场景去选择吧。最后要提醒大家并不是所有数据库引擎都支持hash索引。

posted @ 2020-03-09 19:00  追求极致  阅读(1153)  评论(0)  编辑  收藏  举报