本章内容

普通索引与唯一索引

普通索引

普通索引是最基本的索引类型，它没有任何限制，允许索引列中存在重复值。创建普通索引可以加快查询速度，但不会强制保证数据的唯一性。

创建普通索引语法：

CREATE INDEX index_name ON table_name (column_name);

其中：

• index_name：索引名称。
• table_name：表名。
• column_name：创建索引的列名。

唯一索引

唯一索引与普通索引类似，但它要求索引列中的值是唯一的，不允许重复。唯一索引可以用来保证数据的唯一性，常用于主键或唯一约束。

创建唯一索引语法：

CREATE UNIQUE INDEX index_name ON table_name (column_name);

其中：

• index_name：索引名称。
• table_name：表名。
• column_name：创建索引的列名。

总结： 普通索引和唯一索引都可以提高查询效率，但唯一索引还能保证数据的唯一性。

本文将从性能角度分析普通索引和唯一索引该如何选择。

创建表：

create table t_user(
    id int primary key,
    card varchar(18),
    name varchar(30)
)engine=InnoDB;
insert into t_user values
(1,'432522222888228881','南秋同学1'),
(2,'432522222888228882','南秋同学2'),
(3,'432522222888228883','南秋同学3'),
(5,'432522222888228885','南秋同学5'),
(6,'432522222888228886','南秋同学6'),
(8,'432522222888228888','南秋同学8'),
(9,'432522222888228889','南秋同学9');

其中：

• card：身份证号。
• name：姓名。

由于身份证号字段长度较长，因此，选择基于card字段建立唯一索引或者普通索引。

查询过程分析

假如执行如下语句：

select id from t_user where card = '432522222888228885';

语句查找过程：先通过B+树从树根开始按层搜索到叶子节点（即：数据页），在数据页内部再通过二分法来定位记录：

• 如果是普通索引，则查找到满足条件的第一个记录(1,'432522222888228885','南秋同学5')后，需要查找下一个记录，直到碰到第一个不满足card='432522222888228885'条件的记录。
• 如果是唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

InnoDB的数据以数据页为单位进行读写。读取一条记录时，并不是从磁盘中读取该记录本身，而是以页为单位，将整个数据页读入内存。每个数据页的默认大小为16KB。

因此，当查找到card='432522222888228885'的记录时，该记录所在的数据页会读入内存中。对于普通索引来说，只是多做一次查找和判断（判断下一条记录是否符合条件）。即使card='432522222888228885'的记录刚好是数据页的最后一条记录，也只需读取下一个数据页，复杂度可以忽略不计。

因此，查询过程普通索引和唯一索引的性能没有明显差别。

更新过程分析

在分析普通索引和唯一索引对更新过程的性能影响之前，先说明一下change buffer。

change buffer

change buffer（变更缓冲区）是MySQL InnoDB存储引擎的一种优化机制，它可以提高对于插入、删除和更新操作的性能。在MySQL 5.5之前，change buffer只针对插入操作进行了优化，称为插入缓冲（insert buffer）。而在MySQL 5.5之后，change buffer进行了优化，对于删除和更新操作也有效，称为写缓冲（change buffer）。

change buffer结构，如图所示：

注：对buffer pool不了解的同学请移步主页查阅「一文搞懂」MySQL缓冲池（buffer pool）。

change buffer执行流程

change buffer写入流程

如图所示：

处理流程：

• 对不在buffer pool中的普通索引（即：非唯一索引）数据页执行插入、删除或更新操作时，会将这些操作记录到change buffer中，而不是直接写入磁盘。
• 对在buffer pool中的数据页执行插入、删除或更新操作时，直接更新buffer pool中的数据页。

以上操作会存在两次内存操作和一次磁盘操作：

• 内存操作一：change buffer中记录写入操作。
• 内存操作二：修改buffer pool的数据页。
• 磁盘操作：将两条插入数据合并写入redo log（顺序写）中。

change buffer读取流程

如图所示：

处理流程：

• 读取buffer pool中存在的数据页（即：page2）时，直接从buffer pool中返回即可。
• 读取buffer pool中不存在的数据页（即：page1）时，需要先将page1从磁盘读入内存，再将change buffer中的操作日志merge生成一个正确的版本返回。

change buffer合并（merge）过程

将change buffer中的操作应用到磁盘上的数据页，得到新的数据页的过程称为merge。

merge触发时机：

• 读取change buffer中记录的数据页时，会将change buffer合并到buffer pool 中，再将数据页刷新到磁盘。
• 当系统空闲或者数据库正常关闭（shutdown）时，后台master线程发起merge。
• change buffer的内存空间已满时，后台master线程会发起merge。

change buffer大小配置

change buffer使用的是buffer pool中的内存，其大小可通过参数innodb_change_buffer_max_size进行配置：

• innodb_change_buffer_max_size表示change buffer占用buffer pool内存大小的百分比，默认为25%，最大为50%：
• 当在系统中存在大量插入、更新、删除操作时，可以调大innodb_change_buffer_max_size参数值来提高系统的写入性能。
• 当在系统中存在大量查询操作时，可以调小innodb_change_buffer_max_size参数值来减少buffer pool中数据页的淘汰概率，提高系统的读取性能。
• innodb_change_buffer_max_size可动态调整，无需重新启动服务器。

change buffer类型配置

change buffer类型通过参数innodb_change_buffering进行配置：

• all：默认值，缓冲区插入、删除和清除。
• none：不缓存任何操作。
• inserts：缓冲区插入操作。
• deletes：缓冲区删除标记操作。
• changes：缓冲区插入和删除标记操作。
• purges：缓冲区在后台发生的物理删除操作。

查看change buffer配置信息命令：

show variables like '%innodb_change_buffering%';

change buffer只对普通索引生效的原因

唯一索引的更新、插入操作会先判断当前操作是否违反唯一性约束，该操作需要先将索引数据页读取到内存中，由于索引数据页已经读取到内存中，因此，更新、插入直接在内存中操作即可，无需change buffer支持。

普通索引的更新、插入操作无需判断当前操作是否违反唯一性约束，不需要将索引数据页读取到内存中，更新、插入需要change buffer来提升操作性能。

change buffer小结

通过使用change buffer，可以减少对磁盘的访问次数，提高对于插入、删除和更新操作的性能：

• 对于大量DML操作（如：批量插入）的场景下，性能提升非常明显。
• 对于更新数据后马上进行查询的场景，由于查询会触发merge过程，不但不会减少IO访问频次，反而会增加change buffer的维护代价。

更新过程

假如向t_user表中插入一条记录，执行语句：

insert into t_user values(7,'432522222888228887','南秋同学7');

要更新的数据页在内存中，处理流程：

• 如果是普通索引，则找到card为432522222888228886和432522222888228888之间的位置，插入数据即可。
• 如果是唯一索引，则找到card为432522222888228886和432522222888228888之间的位置，判断是否违反唯一性约束，没有违反唯一性约束，再插入数据。

因此，此种情况下，唯一索引和普通索引的区别只是多了一次唯一性约束判断，插入性能没有明显差别。

要更新的数据页不在内存中，处理流程：

• 如果是普通索引，则只需将更新记录在change buffer中。
• 如果是唯一索引，则需要将数据页读入内存，找到card为432522222888228886和432522222888228888之间的位置，判断是否违反唯一性约束，没有违反唯一性约束，再插入数据。

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

总结

普通索引和唯一索引在查询能力上没差别，主要考虑对更新性能的影响。因此，建议尽量选择普通索引。

【阅读推荐】

更多精彩内容，如：

• Redis系列

• 数据结构与算法系列

• Nacos系列

• MySQL系列

• JVM系列

• Kafka系列

请移步【南秋同学】个人主页进行查阅。内容持续更新中......

【作者简介】

一枚热爱技术和生活的老贝比，专注于Java领域，关注【南秋同学】带你一起学习成长～