MySQL-09:普通索引和唯一索引

索引是数据库优化中常用的一种手段,索引有多种种类,有普通索引,唯一索引,主键索引,组合索引,全文索引等,下面主要是来区别普通索引和唯一索引。

普通索引的构建:

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

唯一索引的构建:

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

MySQL的主键不易过长 →追根究底还是非主键索引树的结构问题,每个叶子节点是主键+索引值的形式存储,搜素到对应的索引值,拿到主键,再去主键索引树中去寻找。

先上图,这是一颗主键索引树和非主键索引树,

在这里插入图片描述

9.1查询方向分析

一条sql查询语句select id from T where k=5,我们先从B+索引树的根部开始查找(右边那颗),找到相应的叶子节点(5,500),这里之后两种索引的做法做了区分:

  • 普通索引:查找到第一个满足条件的节点后(5,500),会继续查找下一个记录,直到碰到一个不满足k=5的记录
  • 唯一索引:因为索引定义了唯一性,查找到第一个满足条件的记录后,就会停止查找。

两者之间的性能差距微乎其微,原因在于InnoDB引擎中索引树的叶子节点其实是一个页,每个页默认为16KB,非主键索引树的一个记录大小我们当做20B,那一个页能存储近千个key+主键id。所以说在普通索引情况下,它要不再进行一次指针移动和计算,要不就加入下一页到内存中寻找。从平均性能,两者在查询方面的性能差异还是很小的。

MySQL能存多少行数据

9.2更新方向分析

change buffer:buffer pool中的一部分内存,可以用innodb_change_buffer_max_size来设置大小,例如值为50时,则表示change buffer至多占用buffer pool的50。

当需要更新一个数据时(插入,修改,更新),如果数据页在内存中就直接更新;如果数据页不在内存中,则会先将更新操作写入change buffer中,等到下一次需要访问数据页时,数据页加载到内存中并执行change buffer中相关的更新语句。

将change buffer中的操作应用到相应数据页中得到新数据页的过程为merge,以下情况都会触发merge:

  • 访问数据页
  • 后台定期merge
  • 数据库正常关闭

插入语句的性能区别

唯一索引在进行插入语句时,它需要判断插入的主键是不是唯一,判断主键唯一需要在内存中判断,如果插入的数据页不在内存中,就需要先加载数据页到内存中再进行主键比对,而普通索引直接更新到change buffer中便可以了。

9.3 change buffer的使用场景

问题: 对于普通索引而言,change buffer在所有场景的使用是否都是性能的提高?

merge是数据真正更新的时刻,change buffer中的更新操作越多,则越有利。这对于写多读少的场景来说是ok的,但是写少读多呢?再一个写入之后,立马来上一个读操作,就需要马上进行merge操作,I/O次数不会减少,反而还增加了change buffer的维护。

9.4索引的选择

两类索引在查询能力上相差无几差距,主要还是考虑更新操作上的性能差距,建议一般选择普通索引。

在写少读多的场景下,可以选择关闭change buffer。

redo log和change buffer的区别:

这里需要明白 WAL,change buffer的含义和目的

WAL含义和目的

下面是同学的精髓回答:

redo log 与 change buffer(含磁盘持久化) 这2个机制,不同之处在于——优化了整个变更流程的不同阶段。 先不考虑redo log、change buffer机制,简化抽象一个变更(insert、update、delete)流程: 1、从磁盘读取待变更的行所在的数据页,读取至内存页中。 2、对内存页中的行,执行变更操作 3、将变更后的数据页,写入至磁盘中。 步骤1,涉及 随机 读磁盘IO; 步骤3,涉及 随机 写磁盘IO; Change buffer机制,优化了步骤1——避免了随机读磁盘IO Redo log机制, 优化了步骤3——避免了随机写磁盘IO,将随机写磁盘,优化为了顺序写磁盘(写redo log,确保crash-safe) -------------------------------- 在我们mysql innodb中, change buffer机制不是一直会被应用到,仅当待操作的数据页当前不在内存中,需要先读磁盘加载数据页时,change buffer才有用武之地。 redo log机制,为了保证crash-safe,一直都会用到。 ------------------------------- 有无用到change buffer机制,对于redo log这步的区别在于—— 用到了change buffer机制时,在redo log中记录的本次变更,是记录new change buffer item相关的信息,而不是直接的记录物理页的变更。

练习问题:

  1. 什么是索引?不同的索引树在内存中是怎样展现的?
  2. 如何构建普通索引和唯一索引?
  3. 两个索引在查询时的流程和性能差异?
  4. change buffer的工作流程?
  5. 两个索引在更新时的流程和性能差异?
  6. change buffer 在读多写少和写多读少场景的使用
  7. change buffer和redo log的区别?
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页