MySQL普通索引和唯一索引

渔老师

于 2021-08-31 21:07:25 发布

阅读量443

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_36723038/article/details/120026893

版权

14 篇文章 0 订阅

订阅专栏

普通索引和唯一索引

假设查询语句: select id from T where k = 5;
查询过程：
1. 通过B+树从树根开始，按层搜索到叶子节点
2. 数据页内部通过二分法来定位记录。
  1. 普通索引：查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足K=5的记录。
  2. 唯一索引：唯一索引定义了唯一性，查找到第一个满足条件的记录后，就会停止索引。
两者之间的性能相差不大。
原因：InnoDB的数据是按数据也为单位来读写的。当需要都一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将起整体读入内存，在InnoDB中，每个数据也的大小默认是16KB。
即当找到K=5的记录的时候，它所在的数据就都在内存之中，对于普通索引来说，只需要一次指针寻找和一次计算。

当需要更新一个数据页时，如果数据页在内存中就直接更新。如果没有在内存中，那么在不影响一致性的前提下，InnoDB会将更新操作缓存在change Buffer中。不需要从磁盘中读入此数据页，在下次查询访问这个数据页时，将数据页读入内存，然后执行change Buffer中的有关操作(merge)。
除了数据页会触发merge外，系统有后台线程定期merge。在书库正常关闭(shutdown)的过程中，也会执行merge操作。

唯一索引，所以的更新操作都要先判断这个操作是否违反唯一约束，必须要将数据读入内存才能判断，因此没必要使用，只有普通索引使用。
change buffer用的时buffer pool里的内存，因此不能无限增大。change buffer的大小可以通过参数innodb_change_buffer_max_size来动态设置。

第一种情况，这个记库更新的目标也在内存中:
- 唯一索引：找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束。
- 普通索引：找到3和5之间的位置，插入值。结束。
第二种情况，这个记录要更新的目标不在内存中：
- 唯一索引：需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束。
- 普通索引，将更新记录在change buffer，语句执行结束。

change buffer在meger的时候才是真正进行数据更新的时候，change buffer的主要目的是将记录的变更动作缓存下来，所以在一个数据页做merge之间，change buffer记录的变更越多，收益越大。
因此使用与多谢少读的业务，change buffer的使用效果最最好，如账单类、日志类的系统。
反之，如果业务更新只会马上做查询，那就会触发merge过程，这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。

执行语句

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

假设查到位置后，k1所在的数据页在内存(innoDB buffer pool)中，k2所在的数据页不在内存中。如下图是带change buffer的更新状态图。
![](https://img-blo
分析更新语句。涉及到：内存、redo log(ib_log_filex)、数据表空间(t.lib)、系统表空间(ibdata1)
更新语句做了如下擦偶哦在
1. Page 1 在内存中，直接更新内存；
2. Page 2没有在内存中，就在内存的change buffer区域，记录下“往page 2插入一行”信息
3. 将上述两个动作计入redo log
两次操作合在一起写入磁盘。
redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

关注

专栏目录