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

一、创建索引语句差异

1.1 普通索引

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

   
   
  • 1
  • 2

1.2 唯一索引

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

   
   
  • 1
  • 2

二、查询语句差异

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

select  id  from tt_test where title='1'

   
   
  • 1

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

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

三、更新语句差异

用们用如下查询语句分析

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

   
   
  • 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消耗;
上一篇:input根据字符大小显示组件(只适用于type=text)


下一篇:vue中使用vue-visibility-change监听浏览器页面之间的切换