@[toc]
咱们建索引的时候,有全文索引、主键索引、唯一性索引、一般索引等,后面两个好了解好辨别,大家都晓得啥时候用,前面两个该如何辨别呢?唯一性索引和一般索引该如何抉择呢?明天咱们就来聊聊这个话题。
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!),咱们来梳理下这里的查问步骤:
- MySQL 的 server 层首先调用存储引擎定位到第一个值为 1 的 address。
- 因为 address 是二级索引,二级索引的叶子结点中保留着主键值,所以还须要依据主键值去主键索引上找到残缺的数据行,其实就是回表(什么是 MySQL 的“回表”?)。
- 存储引擎将读取到的数据行返回给 server 层。
- 因为 address 是一般索引,不是唯一性索引,所以 address 为 1 的记录可能不止一条,所以还需在第一次查问的根底上,沿着叶子结点外部的单向链表持续向后扫描,扫描到新的数据后,反复 2、3 步。
- 当扫到 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 的记录)才进行搜寻,这么看来,的确唯一性索引更胜一筹!那么这种差别很显著吗?诚实说,这个劣势能够忽略不计!
为什么呢?
- 对于一般索引而言,尽管找到第一条记录之后,还须要持续找前面的,然而因为满足条件的记录是间断的,索引只须要顺着记录之间的单向链表持续向后读就行了,速度快。
- 因为 InnoDB 引擎读数据的时候,不是一条一条的读,而是一页一页的读(默认每页 16KB,在什么是 MySQL 的“回表”?一文中,我有大抵介绍 16KB 的问题),所以,即便持续向后读,也是内存操作,速度很快。
- 也不排除个别情况,例如满足条件的记录刚好是在当前页的最初一条,此时就须要加载新的一页数据,然而这种概率比拟小,能够疏忽之。
综上所述,唯一性索引和一般索引对搜寻效率的影响能够忽略不计。
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. 小结
那么对于一个须要全局惟一的字段,到底是用一般索引还是唯一性索引呢?这个我感觉很难给大家一个放之四海而皆准的倡议,因为数据库优化很多时候不是相对的,要联合本人的理论业务来, 所以,无论何时何地,先满足业务需要,在此基础上,再去探讨数据库优化。
如果你能从业务上确保该字段惟一,那么能够应用一般索引,这样能够进步插入 / 更新速度。
然而,依据墨菲定律,你要是不必惟一索引,该字段中未来大概率会呈现脏值,所以你也要思考业务上对于脏值的容忍水平。