关于java:听说唯一性索引搜索快

@[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！），咱们来梳理下这里的查问步骤：

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. 小结

那么对于一个须要全局惟一的字段，到底是用一般索引还是唯一性索引呢？这个我感觉很难给大家一个放之四海而皆准的倡议，因为数据库优化很多时候不是相对的，要联合本人的理论业务来， 所以，无论何时何地，先满足业务需要，在此基础上，再去探讨数据库优化。

如果你能从业务上确保该字段惟一，那么能够应用一般索引，这样能够进步插入 / 更新速度。

然而，依据墨菲定律，你要是不必惟一索引，该字段中未来大概率会呈现脏值，所以你也要思考业务上对于脏值的容忍水平。