本文转载自微信公众号《MySQL江湖道》，作者陈哈哈。转载本文请联系MySQL江湖路公众号。说到唯一索引和普通索引，相信大家都不陌生。当同事问你这两者有什么区别？也许你会脱口而出：“这还是个问题吗？看名字，一种是允许字段重复，一种是不允许重复数据！”不知道能不能解决小姐姐的问题，但你在同事眼里绝对不是什么好玩的东西~要知道，一眼就能看出来的答案，一般不会有人问，除非你问傻瓜~那么你在处理一个公民信息表的时候，其中一列是公民的身份证号码信息，你会怎么选择哪个索引呢？为什么？对于一个经历过风风雨雨、日日夜夜的程序员来说，你需要考虑的不仅仅是要不要重复这种问题，而是……开个玩笑~~你应该结合实际情况综合考虑每个场景.其实，如果保证业务代码中不会写重复的身份证号，那么这两种选择在逻辑上都是正确的。但是在SELECT和DML场景下，唯一索引和普通索引有很多区别。1.SELECT中，唯一索引和普通索引的区别本文的测试引擎选用我们最常用的InnoDB，版本为MySQL8.0；假设，执行查询的语句是：selectidfromTwhereid_card=666；数据演示）我们知道MySQL的InnoDB使用的是B+树实现的索引结构。查找过程从B+树的根开始，逐层查找666所在的叶子节点，然后取出该节点所在的数据页。数据页读入内存后，id_card=666的行数据二分定位到数据页中。B+树的查找过程如上图所示：1.从磁盘加载磁盘块1到内存，发生IO，在内存中使用二分查找找到600到700之间的666，锁住P2磁盘块1的指针。2.通过磁盘块1的P2指针地址加载磁盘块3到内存，第二次IO发生，锁定磁盘块3的P2指针。3.通过磁盘块7加载到内存磁盘块3的P2指针，第三次出现IO，同时根据二分查找找到666。查询结束。普通索引和唯一索引定位方法：普通索引：找到第一条id_card=666记录后，继续查找，直到遇到第一条id_card<>666记录，结束。唯一索引：由于索引定义了唯一性，找到第一条满足条件的记录后，直接结束。两者在查询方面的性能差距可以忽略不计。对于很多普通索引的操作，由于是以数据页为单位读入内存，默认数据页大小为16KB（约1000行），需要额外的“查找判断下一条记录”操作完毕。只需要一次指针查找和一次计算。当然，不可避免要查询的数据是数据页的最后一位，所以需要读取下一个数据页，算法会比较复杂。但是你要知道，这个概率是很小的，我们程序员一定要相信逆墨菲定律：一个很大概率不会出现，还没有被发现的BUG，在很难改的前提下，你就是不去知道它就结束了，它发生了我该怎么办？有考试！？？有同学问我：为什么普通的索引会一直往下找？之所以继续往下查找，是因为普通索引允许重复值，而B+Tree自然是有序的。SQL中没有规定limit1，所以他要往下查，看有没有相同条件的数据一起返回，直到找到第一条不满足条件的数据。2、在DML中，唯一索引和普通索引的区别是ding！这是本文的重点。在阅读它之前，我们需要了解什么是更改缓冲区。了解MySQL机制的同学都知道，在进行DML（INSERT、UPDATE、DELETE）等操作时，InnoDB会使用changebuffer来加速写操作，可以将写操作的随机磁盘访问调整为部分顺序操作.在那个时代，随机磁盘访问（随机I/O）也是数据库操作中最耗性能的缺陷。当对普通索引（非唯一索引）的数据页发生写操作时，将操作内容写入内存中的changebuffer，并立即返回（执行完成）。这里我以UPDATE操作为例。当一行数据需要更新时，它会先判断该行所在的数据页是否在内存中。如果有，则直接更新到内存数据页中。如果数据页不在内存中，那么在不影响数据一致性的前提下，InnoDB会将这些UPDATE操作缓存在changebuffer中，这样就不需要从磁盘读取数据页了。当有SQL查询需要访问该数据页的数据时，该数据页会被读入内存后，先执行changebuffer中与该页相关的UPDATE操作，这样保证了逻辑的正确性这个数据页。可以看出，changebuffer会从内存持久化到磁盘，将changebuffer中的操作应用到原始数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统还有一个后台线程会周期性的merge。在正常关闭数据库的过程中，也会进行merge操作，相当于清理脏页（将修改后的数据更新到实际的数据文件中）。触发merge的操作主要有以下几类（**你应该记住了**）：有一个SQL线程正在访问这个数据页；masterthread线程每秒或每10秒执行一次mergechangebuffer操作；关闭时在数据库中是正常的。孩子们，你们是不是有很多问号？？如果数据库服务器宕机，数据会丢失吗？需要redolog+binlog来保证这一点。可以参考作者[1]的另一篇文章，本文不再赘述。跑远了？言归正传，上文提到，普通索引（非唯一索引）会使用Changebuffer来加速写操作。你够聪明，能开门见山吗？是的，唯一索引不会使用Changebuffer，如果为索引设置了唯一属性。在插入或修改操作时，InnoDB必须执行唯一性检查。如果索引页没有读到缓冲池中，则无法验证索引是否唯一。如果索引页被读入内存，那么直接更新内存会更快，而且不需要使用changebuffer。对于普通索引（非唯一索引）的DML操作，当要更新的数据页在内存中时，找到上一个值和下一个值的区间插入即可；当要更新的数据页不在内存中时，直接将操作写入Changebuffer即可。舒服的！对于唯一性索引，当要更新的数据页不在内存中时，索引每次都要将数据页读入内存中，判断唯一性。将数据从磁盘读入内存涉及到大量的随机IO访问，遇到高频写操作时很慢？？唉，别想了，难受！至此，相信你对于普通索引和唯一索引的选择已经有了一定的概念。普通索引和唯一索引的查询能力没有区别，主要考虑更新的影响。还得结合实际业务场景来判断。如果读取比update和insert表大很多，可以使用唯一索引和普通索引。但是，如果业务需求相反，我个人认为应该使用普通索引。当然，如果是那种更新后的需求Immediatelyvisible需求就是那些更新后需要立即查询的需求。这种情况下不推荐使用普通索引，因为这样会频繁产生merge操作，起不到changebuffer的作用。相反，需要额外的空间来维护更改缓冲区。有点吃亏。我们在使用普通索引时，尤其是在使用机械盘的场景下，尽量增大changebuffer，保证数据写入速度。最后，通过枚举changebuffer的配置，结束今天的分享。相信看到这里的每个人都是一个有心人，也是一个热爱MySQL的孩子。记得不要吝啬自己的喜好哦~~changebufferconfigurationinnodb_change_buffer_max_size%配置写缓冲区的大小，占整个缓冲池的比例，默认值为25%，可以通过修改这个值提高InnoDB的写效率，最大值为50%。mysql>showvariablelike'%innodb_change_buffer_max_size%';+----------------------------+-------+|Variable_name|Value|+--------------------------------+--------+|innodb_change_buffer_max_size|25|+--------------------------------+--------+1rowinset(0.00sec)是否innodb_change_buffering配置缓存辅助索引页的修改，默认为all，即缓存INSERT/DELETE/UPDATE等DML操作。mysql>showvariablelike'%innodb_change_buffering%';+------------------------+--------+|Variable_name|Value|+----------------------+--------+|innodb_change_buffering|all|+------------------------+--------+1rowinset(0.00sec)参考文献[1]《听我讲完redo log、binlog原理，面试官老脸一红》:https://blog.csdn.net/qq_39390545/文章/详情/115214802
