MySQL-09：普通索引和唯一索引

雨点打透心脏的1/2处 2022-12-23 11:26 204阅读 0赞

索引是数据库优化中常用的一种手段，索引有多种种类，有普通索引，唯一索引，主键索引，组合索引，全文索引等，下面主要是来区别普通索引和唯一索引。

**普通索引的构建：**

ALTER TABLE `T1` ADD INDEX `k_title` (`title`) USING BTREE ;

**唯一索引的构建：**

ALTER TABLE `T2` ADD UNIQUE INDEX `un_index_title` (`title`) USING BTREE ;

[MySQL的主键不易过长][MySQL] →追根究底还是非主键索引树的结构问题，每个叶子节点是主键+索引值的形式存储，搜素到对应的索引值，拿到主键，再去主键索引树中去寻找。

先上图，这是一颗主键索引树和非主键索引树，

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

### 9.1查询方向分析 ###

一条sql查询语句select id from T where k=5，我们先从B+索引树的根部开始查找(右边那颗)，找到相应的叶子节点(5,500)，这里之后两种索引的做法做了区分：

*  普通索引：查找到第一个满足条件的节点后(5,500)，会继续查找下一个记录，直到碰到一个不满足k=5的记录
 *  唯一索引：因为索引定义了唯一性，查找到第一个满足条件的记录后，就会停止查找。

两者之间的性能差距微乎其微，原因在于InnoDB引擎中索引树的叶子节点其实是一个页，每个页默认为16KB，非主键索引树的一个记录大小我们当做20B，那一个页能存储近千个key+主键id。所以说在普通索引情况下，它要不再进行一次指针移动和计算，要不就加入下一页到内存中寻找。从平均性能，两者在查询方面的性能差异还是很小的。

[MySQL能存多少行数据][MySQL 1]

### 9.2更新方向分析 ###

change buffer：buffer pool中的一部分内存，可以用innodb\_change\_buffer\_max\_size来设置大小，例如值为50时，则表示change buffer至多占用buffer pool的50。

当需要更新一个数据时(插入，修改，更新)，如果数据页在内存中就直接更新；如果数据页不在内存中，则会先将更新操作写入change buffer中，等到下一次需要访问数据页时，数据页加载到内存中并执行change buffer中相关的更新语句。

将change buffer中的操作应用到相应数据页中得到新数据页的过程为merge，以下情况都会触发merge：

*  访问数据页
 *  后台定期merge
 *  数据库正常关闭

**插入语句的性能区别**

唯一索引在进行插入语句时，它需要判断插入的主键是不是唯一，判断主键唯一需要在内存中判断，如果插入的数据页不在内存中，就需要先加载数据页到内存中再进行主键比对，而普通索引直接更新到change buffer中便可以了。

### 9.3 change buffer的使用场景 ###

**问题：** 对于普通索引而言，change buffer在所有场景的使用是否都是性能的提高？

merge是数据真正更新的时刻，change buffer中的更新操作越多，则越有利。这对于写多读少的场景来说是ok的，但是写少读多呢？再一个写入之后，立马来上一个读操作，就需要马上进行merge操作，I/O次数不会减少，反而还增加了change buffer的维护。

### 9.4索引的选择 ###

两类索引在查询能力上相差无几差距，主要还是考虑更新操作上的性能差距，建议一般选择普通索引。

在写少读多的场景下，可以选择关闭change buffer。

**redo log和change buffer的区别：**

这里需要明白 WAL，change buffer的含义和目的

[WAL含义和目的][WAL]

**下面是同学的精髓回答：**

redo log 与 change buffer(含磁盘持久化) 这2个机制，不同之处在于——优化了整个变更流程的不同阶段。 先不考虑redo log、change buffer机制，简化抽象一个变更(insert、update、delete)流程： 1、从磁盘读取待变更的行所在的数据页，读取至内存页中。 2、对内存页中的行，执行变更操作 3、将变更后的数据页，写入至磁盘中。 步骤1，涉及 随机 读磁盘IO； 步骤3，涉及 随机 写磁盘IO； Change buffer机制，优化了步骤1——避免了随机读磁盘IO Redo log机制， 优化了步骤3——避免了随机写磁盘IO，将随机写磁盘，优化为了顺序写磁盘(写redo log，确保crash-safe) -------------------------------- 在我们mysql innodb中， change buffer机制不是一直会被应用到，仅当待操作的数据页当前不在内存中，需要先读磁盘加载数据页时，change buffer才有用武之地。 redo log机制，为了保证crash-safe，一直都会用到。 ------------------------------- 有无用到change buffer机制，对于redo log这步的区别在于—— 用到了change buffer机制时，在redo log中记录的本次变更，是记录new change buffer item相关的信息，而不是直接的记录物理页的变更。

**练习问题：**

1.  什么是索引？不同的索引树在内存中是怎样展现的？
2.  如何构建普通索引和唯一索引？
3.  两个索引在查询时的流程和性能差异？
4.  change buffer的工作流程？
5.  两个索引在更新时的流程和性能差异？
6.  change buffer 在读多写少和写多读少场景的使用
7.  change buffer和redo log的区别？

[MySQL]: https://www.cnblogs.com/xibuhaohao/p/11669906.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/490e9cedc9e047e595248985fe063a05.png
[MySQL 1]: https://blog.csdn.net/luoyang_java/article/details/92781164
[WAL]: https://www.cnblogs.com/agilestyle/p/11447794.html