mysql 唯一索引 alter table mysql的唯一索引

转载

编程梦想实现家 2023-08-28 11:37:41

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

普通索引的构建：

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

唯一索引的构建：

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

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

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

mysql 唯一索引 alter table mysql的唯一索引_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相关的信息，而不是直接的记录物理页的变更。

练习问题：