MySQL实战45讲——09|普通索引和唯一索引，应该怎么选择-CSDN博客

本文链接：https://blog.csdn.net/qq_51931826/article/details/129845516

文章目录

09|普通索引和唯一索引，应该怎么选择？

09|普通索引和唯一索引，应该怎么选择？

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码保证了不会写入两个重复的身份证号，如果市民系统需要按照身份证号查询姓名，就会执行类似这样的SQL语句：

mysql>select name from CUser where id_card = 'xxxxxxxxxxxxxxxxxxx';

所以你一定会考虑，在id_card字段上创建索引

由于身份证号字段比较大，我不建议你把身份证号当作主键，那么现在你有两个选择，要么给id_card字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会重复写入身份证号，那么这两个选择逻辑上都是正确的

那么现在，从性能的角度考虑，你要选择唯一索引还是普通索引呢？还是以文章04|深入浅出索引(上)的例子为例

mysql>create tableT(
	id int primary key,
    k int not null,
    name varchar(16),
    index (k)
)engine = InnoDB;

查询过程

查询语句：select id from T where k = 5。这个查询语句在索引树上查找的过程，先通过B+树从树根开始，按层搜索到叶子节点，然后可以以数据页内部通过二分法来定位记录

对于普通索引，找到满足条件的第一个记录(5, 500)后，需要找到下一个记录，直到碰到第一个不满足k = 5的记录
对于唯一索引，由于索引定义了唯一性，找到第一个满足条件的索引，就会停止继续检索

那么，这样性能有多大差别呢？答案是，微乎其微

因为，InnoDB的数据是以数据页为单位来读写的，所以，当需要读取一条记录的时候，并不是把这个记录本身从磁盘中读出，而是以页为单位，将其整体读入内存，在InnoDB中，每个数据页的默认大小是16KB

因为引擎是按页读写的，所以说，当找到k = 5的记录的时候，它所在的数据页都在内存里了，那么对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算

当然，如果k = 5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作才会复杂一些

但是，对于一个整型字段，一个数据页可以存放近千个Key，因此出现这种情况的概率就会很低，所以，平均性能可以忽略不计

更新过程

当需要更新一个数据页的时候，如果数据页在内存中就直接更新，而如果这个数据页不在内存里，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘读入这个数据页了，在下次需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中和这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性

值得注意的是，虽然名字是buffer，但是实际上，它是可以持久化的数据，也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。将change buffer的操作应用的原数据页，得到最新结果的过程叫：merge。除了访问这个数据页会触发merge外，后台线程会定期merge。在数据库正常关闭的过程中，也会执行merge操作

显然，如果能够将更新操作先记录在cahnge buffer，减少读磁盘，语句的执行速度会得到明显的提升，而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率

那么，什么条件下可以使用change buffer呢？

对于唯一索引，所有的更新操作都要先判断这个操作是否违法唯一性约束，比如，要插入(4, 400)这个记录，就要先判断现在表中已经存在k = 4的记录，而这必须要将数据页读入内存才能判断，如果都已经读入内存了，那直接更新内存会更快，就没必要用change buffer了

因此，唯一索引的更新就不能使用change buffer，实际上页只有普通索引可以使用

change buffer用的是buffer pool里的内存，因此不能无限的增大，change buffer的大小，可以通过参数innodb_change_max_size来动态设置，这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%

一个新的问题，如果要在这张表中插入一个新的记录(4, 400)，InnoDB的处理流程是怎么样的？

第一种情况：这个记录要更新的目标页在内存中，这时候InnoDB的处理流程如下：

对于唯一索引，找到3, 5之间的位置，判断没有冲突，插入这个值，语句执行结束
对于普通索引，找到3, 5之间的位置，插入这个值，语句执行结束

这样看来，普通索引和唯一索引的差别只是一个判断

第二种情况：这个记录所在的数据页不在内存中，

对于唯一索引，需要将数据页读入内存判断到没有冲突，插入这个值，语句结束
对于普通索引，则是把更新更新到change buffer中，语句执行结束

将数据从磁盘读入内存呢涉及随机IO的访问，是数据库里成本最高的操作。因此，对于普通索引来说，性能的提升非常的显著

change buffer的使用场景

通过上面从分析，现在我们直到了change buffer只适用于普通索引的场景下，那么是普通索引都适用吗？

因为merge的时候是真正进行数据更新的时候，而change buffer的主要目的就是把记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多，收益越大

因此，对于写多读少的业务，写完很少被访问，change buffer的使用效果最高，比如说：账单类、日志类

反过来，假设一个业务是写入之后马上访问，确实是会把更新记录在change buffer中，但是由于又要马上访问这个数据页，因此会立即触发merge，这样不仅不会减少随机IO的次数，反而增加的chang buffer的维护代价，因此change buffer反而起到了副作用

索引选择和实践

那么回到开头的问题，普通索引和唯一索引如何选择，其实这两种索引在查询能力上是没啥差别的，主要考虑的是对更新性能的影响，所以，建议尽量选择普通索引

如果所有的更新后都伴随着对这个更新的查询，那么应该关闭change buffer，而其他情况下，change buffer都会增加性能

实际使用中，普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的

change buffer 和redo log

了解了change buffer的原理，你可能会联想到文章02|日志系统：一条SQL更新语句是如何执行的的redolog 和WAL

WAL提升性能的核心机制也是减少随机读写，所以容易弄混

现在，我们要在表里插入数据：

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

假设k索引树当前的状态，找到位置后，k1所在的数据页在内存中，k2所在的数据页不在内存中

分析这条更新语句，你会发现这涉及了四个部分：内存、redo log、数据表空间、系统表空间

这条更新语句做了如下的操作：

Page1在内存中，之间更新内存
Page2不在内存中，在change buffer中记录"我要往Page2插入一行"
将上诉两个动作记入redolog中

那么，这之后的查询请求要如何处理？

比如，现在要执行select * from t where k in (k1, k2)

如果读语句在更新语句后不久，内存中的数据都还在，那么此时的两个读操作就和系统空间、redo log无关了

读Page1的时候，直接从内存中返回，WAL之后读数据，不一定要读盘，也不一定要从redolog中把数据更新以后才可以返回
读Page的时候，需要把Page从磁盘读入内存中，然后应该change buffer里面的操作日志，生成一个正确的版本并且返回结果