普通索引 PK 唯一性索引,用哪个好?

数据库 其他数据库
我们建索引的时候,有全文索引、主键索引、唯一性索引、普通索引等,前面两个好理解好区分,大家都知道啥时候用,后面两个该如何区分呢?

我们建索引的时候,有全文索引、主键索引、唯一性索引、普通索引等,前面两个好理解好区分,大家都知道啥时候用,后面两个该如何区分呢?唯一性索引和普通索引该如何选择呢?今天我们就来聊聊这个话题。

1. 准备工作

假设我有如下表:

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int(4) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这表中有 10 万条模拟数据,10 万条模拟数据大家自行创建我就不啰嗦了。

看表结构,有一个 username 索引,这个索引是一个唯一性索引;还有一个 address 索引,这是一个普通索引。

2. 查询

2.1 普通索引查询

我们先来看看普通索引的查询。

我们来做一个简单的查询:

select * from user where address='1';

根据我们前面的讲解(索引下推,yyds!),我们来梳理下这里的查询步骤:

  1. MySQL 的 server 层首先调用存储引擎定位到第一个值为 1 的 address。
  2. 由于 address 是二级索引,二级索引的叶子结点中保存着主键值,所以还需要根据主键值去主键索引上找到完整的数据行,其实就是回表(什么是 MySQL 的“回表”?)。
  3. 存储引擎将读取到的数据行返回给 server 层。
  4. 由于 address 是普通索引,不是唯一性索引,所以 address 为 1 的记录可能不止一条,所以还需在第一次查询的基础上,沿着叶子结点内部的单向链表继续向后扫描,扫描到新的数据后,重复 2、3 步。
  5. 当扫到 address 不为 1 的记录时,停止扫描。

上面是我们的分析,我们来看下执行计划:

执行计划中的 type 为 ref,就说明了我们的分析是没问题的。

2.2 唯一性索引查询

我们再来看看唯一性索引查询。

先来看看一个 SQL:

select * from user where username='1';

对于唯一性索引来说,username 这一列的值是唯一的,所以在查询的过程中,找到第一条username='1' 的记录后,就不需要再找了,对比普通索引的查询步骤,相当于少了第 4、5 步。

我们来看看查询计划:

和前面普通索引的查询计划相比,这里的查询计划 type 为 const,也侧面印证了我们的说法。

2.3 PK

那么从上面的描述中我们可以看出来,似乎唯一性索引在查询的时候表现更优秀?真是情况到底如何,我们再来分析下。

首先,理论上来说,唯一性索引在查询的时候确实更优秀一些,原因很简单:唯一性索引找到满足条件的记录后就不需要再找了;而普通索引找到满足条件的记录后,还需要继续向后查找,直到遇到不满足条件的记录(address 不为 1 的记录)才停止搜索,这么看来,确实唯一性索引更胜一筹!那么这种差异很明显吗?老实说,这个优势可以忽略不计!

为什么呢?

  1. 对于普通索引而言,虽然找到第一条记录之后,还需要继续找后面的,但是因为满足条件的记录是连续的,索引只需要顺着记录之间的单向链表继续向后读就行了,速度快。
  2. 由于 InnoDB 引擎读数据的时候,不是一条一条的读,而是一页一页的读(默认每页 16KB,在什么是 MySQL 的“回表”?一文中,我有大致介绍 16KB 的问题),所以,即使继续向后读,也是内存操作,速度很快。
  3. 也不排除个别情况,例如满足条件的记录刚好是在当前页的最后一条,此时就需要加载新的一页数据,但是这种概率比较小,可以忽略之。

综上所述,唯一性索引和普通索引对搜索效率的影响可以忽略不计。

3 插入/修改

3.1 准备知识

3.1.1 buffer pool

有一个 buffer pool 需要大家了解。

小伙伴们知道,InnoDB 引擎存储数据的时候,是以页为单位的,每个数据页的大小默认是 16KB,我们可以通过如下命令来查看页的大小:

16384/1024=16

刚好是 16KB。

计算机在存储数据的时候,最小存储单元是扇区,一个扇区的大小是 512 字节,而文件系统(例如 XFS/EXT4)最小单元是块,一个块的大小是 4KB,也就是四个块组成一个 InnoDB 中的页。我们在 MySQL 中针对数据库的增删改查操作,都是操作数据页,说白了,就是操作磁盘。

但是大家想想,如果每一次操作都操作磁盘,那么就会产生海量的磁盘 IO 操作,如果是传统的机械硬盘,还会涉及到很多随机 IO 操作,效率低的令人发指。这严重影响了 MySQL 的性能。

为了解决这一问题,MySQL 引入了 buffer pool,也就是我们常说的缓冲池。

buffer pool 的主要作用就是缓存索引和表数据,以避免每一次操作都要进行磁盘 IO,通过 buffer pool 可以提高数据的访问速度。

通过如下命令可以查看 buffer pool 的默认大小:

134217728/1024/1024=128

默认大小是 128MB,因为松哥这里的 MySQL 是安装在 Docker 中,所以这个分配的小一些。一般来说,如果一个服务器只是运行了一个 MySQL 服务,我们可以设置 buffer pool 的大小为服务器内存大小的 75%~80%。

3.1.2 change buffer

还有一个 change buffer 需要大家了解。

前面我们说的 buffer pool 虽然提高了访问速度,但是增删改的效率并没有因此提升,当涉及到增删改的时候,还是需要磁盘 IO,那么效率一样低的令人发指。

为了解决这个问题,MySQL 中引入了 change buffer。change buffer 以前并不叫这个名字,以前叫 insert buffer,即只针对 insert 操作有效,现在改名叫 change buffer 了,不仅仅针对 insert 有效,对 delete 和 update 操作也是有效的,change buffer 主要是对非唯一的索引有效,如果字段是唯一性索引,那么更新的时候要去检查唯一性,依然无法避免磁盘 IO。

change buffer 就是说,当我们需要更改数据库中的数据的时候,我们把更改记录到内存中,等到将来数据被读取的时候,再将内存中的数据 merge 到 buffer pool,此时 buffer pool 中的数据和磁盘中的数据就会有差异,有差异的数据我们称之为脏页,在满足条件的时候(redo log 写满了、内存写满了、其他空闲时候),InnoDB 会把脏页刷新回磁盘。这种方式可以有效降低写操作的磁盘 IO,提升数据库的性能。

通过如下命令我们可以查看 change buffer 的大小以及哪些操作会涉及到 change buffer:

  • innodb_change_buffer_max_size:这个配置表示 change buffer 的大小占整个缓冲池的比例,默认值是 25%,最大值是 50%。
  • innodb_change_buffering:这个操作表示哪些写操作会用到 change buffer,默认的 all 表示所有写操作,我们也可以自己设置为none/inserts/deletes/changes/purges 等。

不过 change buffer 和 buffer pool 都涉及到内存操作,数据不能持久化,那么,当存在脏页的时候,MySQL 如果突然挂了,就有可能造成数据丢失(因为内存中的数据还没写到磁盘上),但是我们在实际使用 MySQL 的时候,其实并不会有这个问题,那么问题是怎么解决的?那就得靠 redo log 了,这个松哥以后再写文章和大家介绍 redo log。

3.2 PK

看了上面 change buffer 的介绍,大家应该已经明白了:

  • 对于非唯一性索引,插入时候直接将数据存储到 change buffer 中就行了,这是一个内存操作,很快。
  • 对于唯一性索引,插入的时候,必须要将数据页读入到内存中(这一步涉及到大量的随机 IO,效率低),检查没有冲突,然后插入。

所以,很明显,在插入的时候,非唯一性索引更有优势。

4. 小结

那么对于一个需要全局唯一的字段,到底是用普通索引还是唯一性索引呢?这个我觉得很难给大家一个放之四海而皆准的建议,因为数据库优化很多时候不是绝对的,要结合自己的实际业务来,所以,无论何时何地,先满足业务需求,在此基础上,再去讨论数据库优化。

如果你能从业务上确保该字段唯一,那么可以使用普通索引,这样可以提高插入/更新速度。

然而,根据墨菲定律,你要是不用唯一索引,该字段中将来大概率会出现脏值,所以你也要考虑业务上对于脏值的容忍程度。


责任编辑:武晓燕 来源: 江南一点雨
相关推荐

2021-09-06 06:45:06

普通索引唯一

2023-02-10 10:14:59

普通索引唯一索引

2009-05-07 14:05:02

MySQL索引分类管理

2011-10-20 10:07:03

LightSwitch

2021-06-15 06:50:08

索引字段数据

2009-03-30 11:27:59

中文域名

2021-07-02 06:54:43

分布式环境ID

2021-05-20 08:16:57

数据库数据软删除数据

2021-06-06 13:03:53

MySQL普通索引

2011-08-18 11:18:25

Oracle唯一约束唯一索引

2018-09-14 09:12:00

数据库MySQL索引约束

2018-09-16 23:14:18

MySQL索引约束主键

2023-02-26 00:00:06

MySQL索引故障

2019-10-21 09:55:12

数据库PostgreSQL Oracle

2022-08-04 08:22:49

MySQL索引

2024-03-26 12:16:13

MySQLInnodb数据库

2023-02-14 08:01:32

2016-08-05 14:33:19

MySQL索引数据库

2023-05-28 13:03:46

BeegoGin设计
点赞
收藏

51CTO技术栈公众号