@[toc]
我们建索引的时候,有全文索引、主键索引、唯一性索引、普通索引等,前面两个好理解好区分,大家都知道啥时候用,后面两个该如何区分呢?唯一性索引和普通索引该如何选择呢?今天我们就来聊聊这个话题。
假设我有如下表:
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 索引,这是一个普通索引。
我们先来看看普通索引的查询。
我们来做一个简单的查询:
select * from user where address='1';
根据我们前面的讲解(索引下推,yyds!),我们来梳理下这里的查询步骤:
上面是我们的分析,我们来看下执行计划:
执行计划中的 type 为 ref,就说明了我们的分析是没问题的。
我们再来看看唯一性索引查询。
先来看看一个 SQL:
select * from user where username='1';
对于唯一性索引来说,username 这一列的值是唯一的,所以在查询的过程中,找到第一条 username='1'
的记录后,就不需要再找了,对比普通索引的查询步骤,相当于少了第 4、5 步。
我们来看看查询计划:
和前面普通索引的查询计划相比,这里的查询计划 type 为 const,也侧面印证了我们的说法。
那么从上面的描述中我们可以看出来,似乎唯一性索引在查询的时候表现更优秀?真是情况到底如何,我们再来分析下。
首先,理论上来说,唯一性索引在查询的时候确实更优秀一些,原因很简单:唯一性索引找到满足条件的记录后就不需要再找了;而普通索引找到满足条件的记录后,还需要继续向后查找,直到遇到不满足条件的记录(address 不为 1 的记录)才停止搜索,这么看来,确实唯一性索引更胜一筹!那么这种差异很明显吗?老实说,这个优势可以忽略不计!
为什么呢?
综上所述,唯一性索引和普通索引对搜索效率的影响可以忽略不计。
有一个 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%。
还有一个 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:
25%
,最大值是 50%
。none
/inserts
/deletes
/changes
/purges
等。不过 change buffer 和 buffer pool 都涉及到内存操作,数据不能持久化,那么,当存在脏页的时候,MySQL 如果突然挂了,就有可能造成数据丢失(因为内存中的数据还没写到磁盘上),但是我们在实际使用 MySQL 的时候,其实并不会有这个问题,那么问题是怎么解决的?那就得靠 redo log 了,这个松哥以后再写文章和大家介绍 redo log。
看了上面 change buffer 的介绍,大家应该已经明白了:
所以,很明显,在插入的时候,非唯一性索引更有优势。
那么对于一个需要全局唯一的字段,到底是用普通索引还是唯一性索引呢?这个我觉得很难给大家一个放之四海而皆准的建议,因为数据库优化很多时候不是绝对的,要结合自己的实际业务来,所以,无论何时何地,先满足业务需求,在此基础上,再去讨论数据库优化。
如果你能从业务上确保该字段唯一,那么可以使用普通索引,这样可以提高插入/更新速度。
然而,根据墨菲定律,你要是不用唯一索引,该字段中将来大概率会出现脏值,所以你也要考虑业务上对于脏值的容忍程度。