普通索引和唯一索引如何选择

推荐公众号

普通索引和唯一索引如何选择

查询过程

以select * from table_name where num = 3;为例子
都知道InnoDB索引数据结构是B+树，从树根开始，按层搜索到叶子节点，然后数据页内搜索数据

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

为什么性能可以认为是一样的

InnoDB 的数据是按数据页为单位来读写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB
按页来读，当找到num=3时，整个页都在内存中，那么内存中搜索数据，性能相差很小。但是对于普通索引极端情况 比如num=3第一个页中 处于该页最后一条记录 那么继续查找不满足的就需要拉去下一页；

更新过程

sql：insert into table_name (num1,num2) values(5,6);
num1是索引位

先介绍下什么是change buffer

解释 当更新一组数据时，先判断数据所在页是否在内存中，如果在直接更新；如果不在，InnoDB 会将这些更新操作缓存在 change buffer 中这样不需要读取磁盘，当查询时 需要访问这个数据页时，读取到内存中 执行change buffer 中与这个页有关的操作。
什么时候merge：读取数据页会触发 merge ，系统后台线程定期 merge，数据库正常关闭（shutdown）的过程中也会执行 merge 操作。
好处：更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
极端情况：当该页的更新操作足够多在change buffer，然后读取进行merege 能够很明显减少I/O操作；对于更新完需要立即读，那么读取的I/O操作不会减少，还要增加change buffer的维护成本
适用场景：写多读少时，足够多的变更动作在change buffer中，merege时通以执行，减少I/O操作

两种索引的区别

数据页在内存中
普通索引：找到5的位置,比如找到了，在4，6之前，内存中更新即可
唯一索引：找到5的位置，判断是否唯一，不冲突则内存中更新
这种情况 多一个判断
数据页不在内存中
普通索引：记录在change buffer，结束
唯一索引：读入数据页，判断是否冲突，执行更新，结束
这种情况 唯一所以代价就大了，多了I/O操作