系列文章

一、原来一条 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 能够非常明显的晋升更新性能。

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