MySQL中的普通索引和唯一索引实际开发中的选择

---

前言

本文我们将会从针对普通索引与唯一索引的增删改查的具体执行流程来看看效率的对比。以便让我们在实际业务开发中可以进行更好的选择。

---

一、普通索引和唯一索引介绍

普通索引
普通索引就是建立在普通字段上的索引即不要求字段为主键也不要求字段为UNIQUE。
在创建表时创建普通索引的方式如下

create table table_name (
	...
	index(index_column_1,index_column_2,...)
	);

建表后如果要创建普通索引可以使用如下这条命令

create index index_name on table_name(index_column_1,index_column_2,...);

唯一索引
唯一索引建立在UNIQUE字段上的索引一张表可以有多个唯一索引索引列的值必须唯一但是允许有空值。
在创建表时创建唯一索引的方式如下

create table table_name(
	...
	unique key(index_column_1,index_column_2,...)
	);

建表后如果要创建唯一索引可以使用下面这条命令

create unique index index_name on table_name(index_column_1,index_column_2,...);

明白了基本概念接着我们对执行具体SQL语句的时候看看用这两个索引的具体执行流程是啥性能咋样。

二、查询语句的比较

我们也是通过具体的例子来进行分析。
如果我们执行如下查询 语句

select id from t_table where k=5;

id为主键k为普通索引或者唯一索引。

这个查询语句在索引树上查找的过程先是通过B+树从树根开始按层搜索到叶子节点的指向的数据页然后数据页内部通过二分法来定位记录。接下来我们看下当确定 到第一条记录后对于 普通索引和唯一索引会有什么不同

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

那这个不同会带来多大的性能差异呢其实是微乎其微的。

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

所以当找到k=5的记录的时候它所在的数据页就都在内存里了。那么对于普通索引来说要多做的那一次“查找和判断下一条记录”的操作就只需要一次指针寻找和一次计算。虽然有可能k=5这个记录刚好是这个数据页的最后一个记录那么读取下一个记录就要读取下一个数据页设计到对磁盘io的操作性能可能会降低但是出现这种情况的概率很低所以通过计算平均性能差异可以忽略不计。

由此可见说到底用这两个不同查询语句的区别就是普通索引找到记录后还会往后接着遍历直到记录不满足为止。而唯一索引查询到第一个满足条件的记录后就直接停止检索了。

三、更新语句的比较

在介绍之前我们先介绍下基本的更新过程。再介绍这些之前我们先连接两个概念

• buffer poolMySQL 的 Buffer Pool
• change buffer:它是内存中的一块区域保存在InnoDB的buffer pool中在磁盘上也有对应的持久化空间change buffer在内存中有拷贝也会被写入到磁盘上在系统表空间ibdata中。它在MySQL中的change buffer信息如下默认值是25代表它的大小最大只能占用到InnoDB buffer pool的25%大小如下所示

show variables like "%change%";

明白之后我们再来看下更新流程的基本操作
当更新数据页的时候如果数据页在内存中就直接更新而如果这个数据页还没有在内存中
的话在不影响数据一致性的前提下InooDB会将这些更新操作缓存在change buffer中这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候将数据页读入内存然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正
确性。这样做主要有两个好处

• 第一个将原本2次的磁盘访问整合成1次磁盘访问并且能够保证数据的一致性
• 第二个数据页读入内存是需要占用内存空间的。如果仅仅是为了更新而把数据页读入到内存这未免会造成浪费。因此上述方法能够避免内存的使用提高内存的利用率。

将change buffer中的操作应用到原数据页得到最新结果过程称为merge。触发merge的有以下几种情况

• 访问这个数据页的时候
• 后台线程会定期merge
• 数据库正常关闭的过程中

明白了使用使用change buffer效率的高效。接下来我们看看是不是普通索引和唯一索引都可以使用呢
先告诉结论吧唯一索引不能使用change buffer普通索引会使用。 接下来分析下原因
对于唯一索引来说所有的更新操作都要先判断这个操作是否违反唯一性约束。 比如要插入
(4,400)这个记录就要先判断现在表中是否已经存在k=4的记录而这必须要将数据页读入内存
才能判断。如果都已经读入到内存了那直接更新内存会更快就没必要使用change buffer
了。

明白了普通索引和唯一索引的更新流程接下来我们以一个具体的例子再来看看它们之间的性能差异
如果要在上述所说的表中插入一个如新记录(4,400)的话。我们分别使用的是普通索引和唯一索引
如果记录要更新的目标页在内存中处理流程如下

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

这样看来普通索引和唯一索引对更新语句性能影响的差别只是一个判断只会耗费微小的CPU时间。

如果这个记录更新的目标不在内存中处理流程如下

• 唯一索引将数据页读入内存判断到没有冲突插入这个值语句执行结束
• 普通索引将更新记录在change buffer语句执行结束

将数据从磁盘读入内存涉及随机IO的访问是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问所以对更新性能的提升是会很明显的。

通过上述我们了解了change buffer的具体功效明白了change buffer只限用在普通索引的场景下而不适用于唯一索引。接下来我们对change buffer的使用场景进行深度分析看看对于普通索引的所有场景使用change buffer都可以起到加速作用吗
在一些写多读少的业务中change buffer能够发挥很好的作用。它可以将多次对磁盘的操作合并成一次merge操作从而提高MySQL的性能一次性merge的操作越多收益就越大。

但是需要注意如果你的数据写入之后。立马会读取(也就意味着需要从磁盘读取到内存)那么建议不要使用change buffer因为在这种情况下使用change buffer不会减低IO次数反而多了change buffer的维护开销。

了解了这些之后我们还要落实到实处看看在实际中是怎么进行选择的。

四、索引的选择和实践

这两类索引在查询能力上是没差别的主要考虑的是对更新性能的影响。所以尽量选择普通索引。

如果所有的更新后面都马上伴随着对这个记录的查询那么你应该关闭change buffer。而在其他情况下change buffer都能提升更新性能。

普通索引和change buffer的配合使用对于数据量大的表的更新优化还是很明显的。特别是机械硬盘访问磁盘较耗时。