MySQL索引结构之btree和hash，主键唯一普通索引区分，changeBuffer

MySQL的btree索引和hash索引的区别

 

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像BTree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 BTree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

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

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

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

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

（3）Hash 索引不能利用部分索引键查询，即不适用最左前缀原则。

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

（4）Hash 索引在任何时候都不能避免表扫描，不可使用覆盖索引。

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

 

索引分类:

普通索引,主键索引,唯一索引,组合索引区分

主键索引和非主键索引的效率区别:

主键索引内部是聚族索引是最快的，因为他在叶节点直接就存储的是数据，而别的索引存储的是数据存放的位置，等于要多一个步骤才能获得数据。

 

主键索引和唯一索引的区别:

比较：

1对于主健/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；

2主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的；

3主健可作外健，唯一索引不可；

4主健不可为空，唯一索引可；

5主健也可是多个字段的组合；

6主键与唯一索引不同的是：

(1).有not null属性；

(2).每个表只能有一个

 

唯一索引与普通索引性能对比

查询操作：

• 对于普通索引来说，查找到满足条件的第一个记录  后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存；所以要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。性能差别可忽略不计

更新过程中的changeBuffer

需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer中，这样就不需要从磁盘中读入这个数据页了。
purge：将 change buffer 中的操作应用到原数据页，得到最新结果的过程。
触发purge时机：除了访问这个数据页会触发 purge 外，系统有后台线程会定期 purge。在数据库正常关闭（shutdown）的过程中，也会执行 purge 操作。
需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。
changeBuffer的好处：
1减少mysql读磁盘，加快执行速度
2数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

changeBuffer的问题：

如果在更新或查询操作的时候需要对changeBuffer进行purge操作，那么你的数据库会突然慢一下。

唯一和普通索引对changeBuffer的使用
如果更新的记录已经在内存页中了
对于唯一索引来说，直接插入这个值，语句执行结束。
这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。
 如果更新的记录没有在内存页中
因为唯一索引在执行更新操作之前，需要先判断索引值是否已经存在，所以必将数据页读入内存进行判断；故不能使用changBuffer
普通索引则会使用changeBuffer

change buffer 的使用场景

通过上面的分析，你已经清楚了使用 change buffer 对更新过程的加速作用，也清楚了 change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。那么，现在有一个问题就是：
普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

因为 purge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 purge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。此时应该使用唯一索引
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 purge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。应该使用唯一索引