看过前几期文章的伙伴会发现并没有聊过关于索引和事务的知识点，这两个大点再之前的文章中已经写过了。

这里给大家一个传送门点击直接查看哈！

揭开MySQL索引神秘面纱

上来就问MySQL事务，瑟瑟发抖...

MVCC：听说有人好奇我的底层实现

幻读：听说有人认为我是被MVCC干掉的

接下来打开普通索引和唯一索引的世界。

一、了解普通索引和唯一索引

普通索引

MySQL 中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

主键索引是一种特殊的唯一索引，不允许有空值。

扩展一下其它两中索引，知识点放在一起记忆会更好

全文索引

只能在 char，varchar，text 类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有“你是个靓仔，靓女。。。”通过靓仔，可能就可以找到该条记录。

空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL 中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用 SPATIAL 关键字。要求，引擎为 Myisam，创建空间索引的列，必须将其声明为 not null。

索引添加方式

1、 主键索引：alter table table_name add primary key (column)

2、 唯一索引：alter table table_name add unique (column)

3、普通索引：alter table table_name add index index_name (column)

4、全文索引：alter table table_name add fulltext (column)

5、多列索引：alter table table_name add index index_name (column1,column2,column3)

二、应用场景

现在你应该知道普通索引和唯一索引的区别，接下来看看在一些场景下如何选择两个索引。

丁老师文章中提到一个业务场景是市民系统，通过身份证号来查姓名。

这里咔咔也借用这个场景来给大家通过咔咔的思路描述一下这个流程。

执行语句为 select name from user where card = '6104301996xxxxxxxx';

这个场景第一反应肯定是给 card 创建一个索引，但创建什么索引呢？主键索引肯定不建议使用。

思考：为什么不能用身份证号来作为主键索引？

三、为什么不能用太大的值作为主键

Innodb 存储引擎的主键索引结构如下图

普通索引数据结构如下图

主键索引的叶子节点存储的是对应主键的整行数据。

普通索引的叶子节点存储的是对应的主键值。

如果说 B+Tree 读取数据的深度是三层，每个磁盘的大小为 16kb。

那在 B+Tree 中非叶子节点可以存储多少数据呢！一般来说我们每个表都会存在一个主键。

根据三层来计算，第一层跟第二层存储的是 key 值，也就是主键值。

都知道 int 类型所占的内存时 4Byte（字节），指针的存储就给个 6Byte，一共就是 10Tybe，那么第一层节点就可以存储 16 * 1000 /10 = 1600。

同理第二层每个节点也是可以存储 1600 个 key。

第三层是叶子节点，每个磁盘存储大小同样安装 BTree 的计算一样，每条数据占 1kb。

在 B+Tree 中三层可以存储的数据就是 1600 1600 16 = 40960000

结论：若主键过大会直接影响索引存储的数据量，所以非常不建议使用过大的数据作为主键索引。

四、从查询的角度分析

假设现在要查 card = 5 这条记录，查询过程为，先通过 B+树从树根开始，按层搜索到叶子节点，然后通过二分法来定位 card = 5 的这条记录。

普通索引

对于普通索引来说当找到 card = 5 这条记录后，还会继续查找，直到碰到第一个不满足 card = 5 的记录为止。

唯一索引

对于唯一索引就非常简单的了，唯一索引的特性就是数据唯一性，所以查到 card = 5 这条记录后就不在查找下一条记录了。

普通索引多查询的一次对性能影响大吗？

这个影响几乎可以忽略，在之前的几期文章中咔咔给大家普及了一个名词“局部性原理”。

数据和程序都有聚集成群的倾向，在访问了一条数据之后，在之后有极大的可能再次访问这条数据和这条数据的相邻数据。

所以说 MySQL 的 Innodb 存储引擎，在读取数据时也会采取这种局部性原理，每次读取的数据是 16kb，也就是一页。

在 Innodb 存储引擎下每页的大小默认为 16kb，这个参数也可以进行调整，参数为 innodb_page_size。

但有一种情况虽说几率非常低，但还是需要知道的。

当索引为普通索引时，查到的数据正好是一页的最后一个数据，此时就需要读取下一页的数据，这个操作是有点复杂，但对于现在的 CPU 来说可以忽略不计。

五、了解 change buffer

首先，需要先了解一个新的知识点 change buffer。

当需要更新 card = 5 这条记录时，这条数据所在的数据页在内存中就直接更新，如若不在的话就需要将更新的操作缓存在 change buffer 中。当下次查询需要访问这个数据页时，将这个数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

接着，了解另一个新的知识点 merge。

当把 change buffer 中的数据应用到数据页，得到最新结果的过程成为 merge，另外数据库正常关闭的过程中，也会执行 merge 操作。

结论：更新操作将记录先记录到 change buffer 中，可以减少磁盘 I/O，语句执行速度会提升。

注意

1、 数据从 change buffer 读入内存是需要占用 buffer pool 的，使用 change buffer 可以避免占用内存。

2、change buffer 也是可以持久化数据的，change buffer 在内存中有拷贝，也会被写入到磁盘。

六、change buffer 在什么条件下使用

思考：为什么唯一索引使用不到 change buffer

唯一索引肯定是用不到，对于这个答案如果你感觉有点不适，就需要在回到之前几期文章再好好看看。

唯一索引插入一行数据时都会执行一次查询操作判断表中是否已经存在这条记录，判断是否违反唯一约束，既然必须得把数据页的数据读入内存，那还用 change buffer 个什么劲啊！

因此，只有普通索引可以使用。

在上文中知道了将 change buffer 数据读入内存时是需要占用 buffer pool 的内存，因此在 MySQL 中也给了一个参数来设置 change buffer 的大小。跟其它的数据单位可能有点出入，若设置为 30，就表示 change buffer 只占用 buffer pool 内存的 30%。

思考：在什么场景下不能使用 change buffer？

change buffer 的作用是将更新的动作缓存下来，所以对一个数据页做 merge 时，change buffer 记录的变更越多，收益就越大。

但也并不是所有场景都适用，咔咔目前所开发的是一款账款软件，大部分更新后都是立马查看，这种情况是不是就违背了上面说的对一个数据页做 merge 时，change buffer 记录的越多，收益越大。

因此，只有写多读少的场景，change buffer 才能发挥非常大的作用。

思考：为什么更新完立马查询 change buffer 就没多大用处了呢？

一条记录发起更新操作后，先记录到 change buffer 中，接着，当查询的数据在这个数据页时会立即触发 merge，这样随机访问的 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以说这种业务模式使用 change biffer 会起到反作用。

思考：如何关闭 change buffer

只需要将参数 innodb_change_buffer_max_size = 0 即可。

七、从更新语句性能的影响的角度分析

第一种情况这条数据要更新的数据页在内存中。

唯一索引：在内存中查找是否有这条记录，不存在时则插入这个值。

普通索引：直接更新需要更新的值即可。

结论: 当要更新的数据页在内存中时，唯一索引就比普通索引多一次判断。

第二种情况这条数据要更新的数据页不在内存中。

唯一索引：需要将这条数据所在的数据页读入内存中，查找是否存在这条记录，然后更新数据。

普通索引：将这条要更新的数据记录在 change buffer 即可。

结论： change buffer 当更新的数据不在数据页中时，如果你的索引是普通索引则可以很显著的提升性能。

注意： 当你把一个索引从普通索引改为唯一索引时一定要注意 change buffer 的影响，会直接影响内存命中率。

八、总结

回到文章主题如何选择普通索引和唯一索引，在查询方面两者是没有什么差别的，主要是在更新操作上的影响。

如果你的业务跟咔咔的场景一样，更新后立马要对这个记录查询，那么就可以选择直接关闭 change buffer。

若不是这种场景，则尽量选择普通索引，使用 change buffer 可以非常明显的提升更新性能。

坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助，我是咔咔，下期见。