面试题mysql普通索引唯一索引如何选择

mysql普通索引和唯一索引

                                   熊大

大家都知道我们公司来了一个漂亮得妹子还经常找我探讨技术！
一天讨论得mysql得索引于是对话如下：
妹子： 熊哥你知道普通索引和唯一索引得区别吗？
我： 创建语句不一样！
妹子： 创建语句的确不一样还有别的嘛？比如查询、更新得时候。
我：。。。。。。。

话说妹子都给指明了方向那么今天就彻底弄明白普通索引唯一索引得区别。

既然是全方面比较那我们从宏观开始：!

通过上图可以看出名称、性别、诞生过程都能一目了然当然这也不是我们得重点。

查询过程：
通过上文得知我们得数据是存在页里面得。也就是说它一查会把整个页全部查出来然后加载到内存然后再用二分查找找到相应数据。每个数据页的大小默认是 16KB

select * from test where k=5;

当k为普通索引时：

它会找到k为5得记录然后查找判断下一条记录记录是否符合条件不符合返回。

当k为唯一索引时：

它会找到k为5得记录直接返回因为唯一索引是具有唯一性得。

从上面来看在查询得时候普通索引会多一次查找判断但是这个操作是在内存中做得
动作其实可以忽略不记。当然如果恰好它在寻找判断下一条记录得时候不在本页那
就有点复杂了。但是总得来说查询效率其实没多大区别得。

更新过程：

从上图中看出普通索引在更新得时候会用到change buffer 那么这里重点介绍
一下。

change buffer ：

`当需要更新一个新的数据页时如果数据页在内存中则会直接更新。
如果没有在内存中在不影像数据一致性得前提下。
InnoDB将把更新操作缓存到change buffer中。
在下次查询得时候将数据读到内存中然后在执行change buffer相关页得操作
从而保证数据正确性。`

说明：

change buffer 也会数据持久化将数据更新到磁盘中
而更新到磁盘中得(也就是原始数据页)这个动作称作merge。

那么什么时候可以使用change buffer 呢？

比如唯一索引要插入一条数据它会将数据读到内存既然用到了内存肯定要比change buffer 快所以也用不到change buffer。
事实上也就普通索引才能用到change buffer了。

那么我们看下如果有一条插入语句(5,500)InnoDB处理流程

> 假设在内存中: 
> `唯一索引:判断是否有冲突,然后插入语句结束。 
> 普通索引: 插入语句结束。`
> 可以看出唯一索引多了一次判断但是这个在内存中执行可以忽略不记。
> 假设没在内存中：
> `唯一索引：从磁盘中读取该数据页,判断是否冲突,插入语句执行完毕。`
> `普通索引：写入change buffer 语句执行完毕。`

针对没用在内存场景中我们当然希望使用change buffer 将我们的操作缓存到内存中 在它meger之前change buffer记录越多我们的收益也越高。

那么有那些场景适合change buffer呢？

那就是写多读少得时候。这样会充分使用changebuffer。
相反是写多读多情况下即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 。这个change buffer 也就变成了负担。

备注:

在上面我们提到了changer buffer 那么它所在的位置是在pool buffer。
也就是说pool buffer 是由两部分组成一个是page 一个是changer bufer
那么它在哪里使用的呢?不知道大家还记不记得二阶段提交如果忘记请看
二阶段提交
其实它会先写道pool buffer 然后在写到readlog。