hash索引btree索引聚簇索引非聚簇索引

Posted on | In

索引概要

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

  • Btree索引
  • Hash索引

不同的引擎对于索引有不同的支持：

  • Innodb和MyISAM默认的索引是Btree索引；
  • Mermory默认的索引是Hash索引。

Hash索引

Mermory默认的索引是Hash索引。

所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，

排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO，
但是innodb和myisam之所以没有采用它，是因为它存在着好多缺点.

Hash索引的缺点

  • Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
    由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

  • Hash 索引无法被用来避免数据的排序操作。
    由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

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

  • Hash 索引在任何时候都不能避免表扫描。
    前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

  • Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
    对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

Btree索引

Innodb和MyISAM默认的索引是Btree索引；
至于Btree索引，它是以B+树为存储结构实现的。
但是Btree索引的存储结构在Innodb和MyISAM中有很大区别。

btree索引在MyISAM中的实现

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

所以我们经常会说MyISAM中数据文件和索引文件是分开的。
因此MyISAM的索引方式也称为非聚集,
至于辅助索引，类似于主索引，唯一区别就是主索引上的值不能重复，而辅助索引可以重复。

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

btree索引在Innodb中的实现

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

我们可以看到这里并没有将地址放入叶子节点，而是直接放入了对应的数据，

这也就是我们平常说到的，Innodb的索引文件就是数据文件，

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

我们可以发现，这里叶子节点存储的是主键的信息，

所以我们在利用辅助索引的时候，检索到主键信息，

然后再通过主键去主索引中定位表中的数据，这就可以说明Innodb中主键之所以不宜用过长的字段，由于所有的辅助索引都包含主索引，

所以很容易让辅助索引变得庞大。

我们还可以发现：在Innodb中尽量使用自增的主键，

这样每次增加数据时只需要在后面添加即可，

非单调的主键在插入时会需要维持B+tree特性而进行分裂调整，十分低效。

Btree索引中的最左匹配原则：

Btree是按照从左到右的顺序来建立搜索树的。

比如索引是(name,age,sex)，

会先检查name字段，如果name字段相同再去检查后两个字段。

所以当传进来的是后两个字段的数据（age，sex），

因为建立搜索树的时候是按照第一个字段建立的，所以必须根据name字段才能知道下一个字段去哪里查询。

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

建立索引的规则：

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

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

  • 尽量扩展索引而不要新建索引

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

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

练习题

  1. 数据索引的正确是(正确答案A, D)

    A、一个表只能有一个聚族索引，多个非聚族索引
    B、字符串模糊查询不适合索引
    C、哈希 索引有利于查询字段用于大小范围的比较查询
    D、多余的索引字段会降低性能

  2. Select A,B from Table1 where A between 60 and 100 order by B，下面哪些优化sql性能(正确答案B)

    A、字段A 建立hash索引，字段 B不建立索引
    B、字段 A 建立btree索引，字段 B不建立索引
    C、字段A 不建立 索引，字段 B建立btree索引