如何选择普通索引和唯一索引《死磕MySQL系列 五》

系列文章

一、原来一条select语句在MySQL是这样执行的《死磕MySQL系列 一》

二、一生挚友redo log、binlog《死磕MySQL系列 二》

三、MySQL强人“锁”难《死磕MySQL系列 三》

四、S 锁与 X 锁的爱恨情仇《死磕MySQL系列 四》

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

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

揭开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可以非常明显的提升更新性能。

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