@[toc]我们建索引的时候有全文索引、主键索引、唯一索引、普通索引等，前两个很容易理解和区分。每个人都知道何时使用它们。后两者如何区分？？如何选择唯一索引和普通索引？今天我们就来聊聊这个话题。1.准备假设我有下表：CREATETABLE`user`(`id`int(11)unsignedNOTNULLAUTO_INCREMENT,`username`varchar(255)COLLATEutf8mb4_unicode_ciDEFAULTNULL,`address`varchar(255)COLLATEutf8mb4_unicode_ciDEFAULTNULL,`age`int(4)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`username`(`username`),KEY`address`(`address`))ENGINE=InnoDBAUTO_INCREMENT=100001DEFAULTCHARSET=utf8mb4整理=utf8mb4_unicode_ci;这张表中有10万条模拟数据，10万条模拟数据你可以自己创建，我就不赘述了。看表结构，有username索引，是唯一索引；还有一个地址索引，这是一个普通的索引。2.查询2.1普通索引查询我们先来看普通索引查询。让我们做一个简单的查询：select*fromuserwhereaddress='1';根据我们之前的解释（indexpushdown,yyds！），这里整理一下查询步骤：MySQL的server层首先调用存储引擎定位到值为1的第一个地址，由于address是二级索引，primarykeyvalue保存在二级索引的叶子节点中，所以需要根据主键值在主键索引上找到完整的数据行。“？”。存储引擎将读取的数据行返回给服务器层。由于address是普通索引，不是唯一索引，地址为1的记录可能不止一条，所以需要在第一次查询的基础上继续沿叶节点内部的单向链表向后扫描，并扫描到新数据后，重复2、3步。当扫描到地址不为1的记录时，停止扫描。以上就是我们的分析，我们来看执行计划：执行计划中的类型是ref，说明我们的分析没有问题。2.2唯一索引查询我们再来看一下唯一索引查询。我们来看一个SQL：select*fromuserwhereusername='1';对于唯一索引，username列的值是唯一的，所以在查询过程中，找到第一个username='1'的记录后，就不需要再查找了。与普通索引的查询步骤相比，相当于少了第4步和第5步。再来看看查询计划：与前面普通索引的查询计划相比，这里查询计划的类型是const，即也印证了我们的说法。2.3PK那么从上面的描述我们可以看出，好像是唯一索引在查询的时候表现的更好一些？到底是什么情况，我们再分析一下。首先，理论上，唯一索引在查询的时候确实更好。原因很简单：唯一索引找到满足条件的记录后，不需要再次查找；普通索引找到满足条件的记录后，需要继续向后查找，直到遇到不满足条件的记录（地址不为1的记录）才停止查找。从这个角度来说，uniqueindex确实更好！那么这种差异明显吗？老实说，这个优势可以忽略不计！为什么？对于普通索引来说，虽然找到了第一条记录，还需要继续寻找后面的，但是由于满足条件的记录是连续的，索引只需要继续沿着链表之间的单向链表向后读取记录。高速。InnoDB引擎在读取数据的时候，不是一张一张的读取，而是一页一页的读取（默认是每页16KB，在MySQL的“返回表”是什么？文章里我已经大致介绍过16KB的问题），所以，即使继续向后读，也是内存操作，速度还是很快的。不排除个别情况。例如，满足条件的记录恰好是当前页的最后一项。这个时候需要加载新的一页数据，但是这个概率比较小，可以忽略。综上所述，唯一索引和普通索引对搜索效率的影响可以忽略不计。3插入/修改3.1预备知识3.1.1缓冲池有一个缓冲池需要大家了解一下。小伙伴们都知道，InnoDB引擎在存储数据的时候，是以页为单位的，每个数据页的默认大小是16KB。我们可以通过以下命令查看页面大小：16384/1024=16正好是16KB。计算机存储数据时，最小的存储单位是扇区，一个扇区的大小是512字节，而文件系统（如XFS/EXT4）的最小单位是块，一个块的大小是InnoDB中4KB，即4个block组成一个page。我们在MySQL中对数据库进行的增删改查操作，都是对数据页的操作。说白了就是对磁盘的操作。但是想想，如果每次操作都要操作磁盘，就会产生大量的磁盘IO操作。如果是传统的机械硬盘，也会涉及到大量的随机IO操作，效率极低。这严重影响了MySQL的性能。为了解决这个问题，MySQL引入了缓冲池，也就是我们常说的缓冲池。缓冲池的主要作用是缓存索引和表数据，避免每次操作都进行磁盘IO。缓冲池可以提高数据访问速度。缓冲池的默认大小可以通过以下命令查看：134217728/1024/1024=128默认大小为128MB，因为这里的MySQL安装在Docker中，所以分配的比较小。一般来说，如果一台服务器只运行一个MySQL服务，我们可以将缓冲池的大小设置为服务器内存大小的75%到80%。3.1.2changebuffer还有一个changebuffer需要大家了解一下。我们前面提到的缓冲池虽然提高了访问速度，但是增删改查的效率并没有提高。在增删改查时，仍然需要磁盘IO，效率同样低下。为了解决这个问题，MySQL引入了changebuffer。更改缓冲区以前不叫这个名字。以前叫insertbuffer，只对insert操作有效。现在它更名为更改缓冲区。它不仅对插入有效，对删除和更新操作也有效。changebuffer主要是针对非唯一索引有效。如果该字段是唯一索引，那么更新的时候就必须检查唯一性，磁盘IO是不可避免的。Changebuffer是指当我们需要改变数据库中的数据时，我们将变化记录在内存中，等到以后读取数据时，再将内存中的数据合并到缓冲池中。这时，缓冲池中的数据和磁盘上的数据就会有差异。有差异的数据称为脏页。当条件满足时（redolog满，内存满，其他空闲时间），InnoDB会将脏页刷新回磁盘。这种方法可以有效减少写操作的磁盘IO，提高数据库的性能。我们可以通过以下命令查看changebuffer的大小以及哪些操作会涉及到changebuffer：innodb_change_buffer_max_size：这个配置表示changebuffer的大小占整个bufferpool的比例。默认值为25%，最大值为50%。innodb_change_buffering：此操作指示哪些写操作将使用更改缓冲区。默认all表示所有写操作。我们也可以设置为none/inserts/deletes/changes/purges等。但是changebuffer和bufferpool都涉及到内存操作，无法持久化数据。然后，当有脏页的时候，如果MySQL突然挂了，可能会造成数据丢失（因为内存中的数据还没有写入磁盘。），但是我们在实际使用MySQL的时候，其实并没有这个问题，那么如何解决问题呢？然后就看redolog了。宋哥以后会写一篇文章给大家介绍redolog。3.2PK看了上面changebuffer的介绍，你应该明白了：对于非唯一索引，插入时直接把数据存入changebuffer就可以了。这是内存操作，速度非常快。对于唯一索引，插入时，必须将数据页读入内存（这一步涉及大量随机IO，效率低下），检查没有冲突，再插入。所以，很明显，在插入的时候，非唯一索引更有优势。4.总结那么对于一个需要全局唯一的字段，我们应该使用普通索引还是唯一索引呢？我觉得很难给大家一个放之四海而皆准的建议，因为数据库优化往往不是绝对的，需要结合自己的实际业务。因此，无论何时何地，首先要满足业务需求，在此基础上，再谈数据库优化。如果能保证该字段在业务上是唯一的，可以使用普通索引，可以提高插入/更新速度。但是根据墨菲定律，如果不使用唯一索引，以后这个字段中很大概率会出现脏值，所以也要考虑业务对脏值的容忍度。
