mysql普通索引与唯一索引区别

客官°小女子只卖身不卖艺 2021-12-02 00:58 407阅读 0赞

## 一、创建索引语句差异 ##

1.1 普通索引

ALTER TABLE `tt_test`
    ADD INDEX `k_title` (`title`) USING BTREE ;

1.2 唯一索引

ALTER TABLE `tt_test`
    ADD UNIQUE INDEX `un_index_title` (`title`) USING BTREE ;

## 二、查询语句差异 ##

我们用这个查询语句来分析一下，这个查询在索引树上查找的过程是先通过B+树从根开始，按层搜索到叶子节点。

select  id  from tt_test where title='1'

2.1 普通索引查询  
普通索引在找到满足itle=‘1’ 的第一个记录后，需要查询下一个记录，直到碰到第一个不满足  
itle=‘1’ 的条件的记录。

2.2 唯一索引查询  
对于唯一索引，查找到第一个满足条件的记录后，就会停止继续检索。

## 三、更新语句差异 ##

用们用如下查询语句分析

UPDATE tt_test  SET  title='23' WHERE title='1';

3.1 要更新的记录目标叶在内存中

*  对于唯一索引来说，找到 title=‘23’ 前后值， 判断如果没有冲突，title值就被更新，执行语句结束；
 *  对于普通索引来说，找到 title=‘23’ 应该写入的位置，，title值就被更新，执行语句结束；

3.2 要更新的记录目标叶不在内存中

*  对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
 *  对于普通索引来说，是将更新记录在change buffer ，语句执行结束；

## 四、change buffer 的使用场景 ##

将数据从磁盘读入内存涉及随机IO访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。 change buffer 只限用在普通索引的场景下，不适用于唯一索引。  
4.1 change buffer 数据什么时候写到硬盘呢 ？

*  merge的时候才是真正进行更新数据落盘的时候，change buffer 的主要目的就是将变更动作记录下来，然后一次merge  
    将数据顺序刷到磁盘；
 *  在一个业务更新逻辑执行后马上进行查询这种场景，将更新先记录在 change  
    buffer，之后马上要查这个数据页，就会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了 change  
    buffer的维护代价；
 *  change buffer 的数据会定期merge数据到 硬盘；
 *  redo log 中checkpiont 不足以保存数据的时候， 也会触发 change buffer 的merge操作；

4.2 change buffer 数据在内存中，mysql 重启以后会丢失吗 ？

*  在写 change buffer 的同时数据库引擎也会写 redo log，redo log 是顺序写入磁盘的 ， 所在及时mysql  
    cash 掉 数据也不会丢失；
 *  redo log 主要节约的是随机写磁盘的IO消耗（转成顺序写），而change buffer 主要节约的 随机读磁盘的IO消耗；