MySQL普通索引和唯一索引到底什么区别?（上）

1 概念区分

普通索引 V.S 唯一索引

普通索引可重复，唯一索引和主键一样不能重复。

唯一索引可作为数据的一个合法验证手段，例如学生表的身份证号码字段，我们人为规定该字段不得重复，那么就使用唯一索引。（一般设置学号字段为主键）

主键 V.S 唯一索引

主键保证DB的每一行都是唯一、不重复，比如身份证，学号等，不重复。

唯一索引的作用跟主键一样。

不同的是，在一张表里面只能有一个主键，主键不能为空，但唯一索引可以有多个。唯一索引可以有一条记录为null。

比如学生表：

• 在学校，一般用学号做主键，身份证号作为唯一索引
• 在教育局，就把身份证号弄成主键，学号作为唯一索引

所以选谁做主键，取决于业务需求。

2 案例

某居民系统，每人有唯一身份证号。若系统要按身份证号查姓名：

select name from CUser where id_card = 'ooxx';      

估计你会在id_card建索引。但id_card字段较大，不推荐做主键。于是现在有如下选择：

在id_card创建唯一索引

创建一个普通索引

假定业务代码已经确保不会写入重复身份证号，这两个选择逻辑上都是正确的。

性能优化角度考虑，选择唯一索引还是普通索引呢？

假设字段 k 上的值都不重复。

InnoDB索引结构

接下来分析性能。

3 查询性能

select id from T where k=4      

通过B+树从root开始层序遍历到叶节点，数据页内部通过二分搜索：

普通索引

查找到满足条件的第一个记录(4,400)后，需查找下个记录，直到碰到第一个不满足k=4的记录

唯一索引

由于索引具备唯一性，查到第一个满足条件的，就会停止搜索

看起来性能差距很小。

InnoDB数据按数据页单位读写。即读一条记录时，并非将该一个记录从磁盘读出，而以页为单位，将其整体读入内存。

所以普通索引，多了一次“查找和判断下一条记录”的操作，即一次指针寻找和一次计算。

若k=4记录恰为该数据页的最后一个记录，则此时要取下个记录，还得读取下个数据页。

对整型字段，一个数据页可存近千个key，因此这种情况概率其实也很低。因此计算平均性能差异时，可认为该操作成本对现在CPU开销忽略不计。

4 更新性能

往表中插入一个新记录(4,400)，InnoDB会有什么反应？

这要看该记录要更新的目标页是否在内存：

在内存

• 找到3和5之间的位置，插入值，结束。
• 找到3和5之间的位置，

  判断到没有冲突

  ，插入值，结束。只是一个判断的差别，耗费微小CPU时间。

不在内存

• 将数据页读入内存，判断到没有冲突，插入值，结束。
• 将更新记录在change buffer，结束。
• 将数据从磁盘读入内存涉及随机I/O访问，是DB里成本最高的操作之一。而change buffer可以减少随机磁盘访问，所以更新性能提升明显。

5 索引选择的最佳实践

普通索引、唯一索引在查询性能上无差别，主要考虑更新性能。所以，推荐尽量选择普通索引。

若所有更新后面，都紧跟对该记录的查询，那就该关闭change buffer。其它情况下，change buffer都能提升更新性能。

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

在使用机械硬盘时，change buffer的收益也很大。

所以，当你有个类似“历史数据”的库，并且出于成本考虑用机械硬盘，应该关注这些表里的索引，尽量使用普通索引，把change buffer 开大，确保“历史数据”表的数据写性能。