MySQL普通索引和唯一索引

普通索引和唯一索引

1.两种索引的查询过程

• 假设查询语句: select id from T where k = 5;

• 查询过程：

  1. 通过B+树从树根开始，按层搜索到叶子节点
  2. 数据页内部通过二分法来定位记录。
     1. 普通索引：查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足K=5的记录。
     2. 唯一索引：唯一索引定义了唯一性，查找到第一个满足条件的记录后，就会停止索引。

• 两者之间的性能相差不大。

• 原因：InnoDB的数据是按数据也为单位来读写的。当需要都一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将起整体读入内存，在InnoDB中，每个数据也的大小默认是16KB。

• 即当找到K=5的记录的时候，它所在的数据就都在内存之中，对于普通索引来说，只需要一次指针寻找和一次计算。

2.两种索引的更新过程

change Buffer

• 当需要更新一个数据页时，如果数据页在内存中就直接更新。如果没有在内存中，那么在不影响一致性的前提下，InnoDB会将更新操作缓存在change Buffer中。不需要从磁盘中读入此数据页，在下次查询访问这个数据页时，将数据页读入内存，然后执行change Buffer中的有关操作(merge)。
• 除了数据页会触发merge外，系统有后台线程定期merge。在书库正常关闭(shutdown)的过程中，也会执行merge操作。

---

什么时候使用change Buffer

• 唯一索引，所以的更新操作都要先判断这个操作是否违反唯一约束，必须要将数据读入内存才能判断，因此没必要使用，只有普通索引使用。
• change buffer用的时buffer pool里的内存，因此不能无限增大。change buffer的大小可以通过参数innodb_change_buffer_max_size来动态设置。

如果在上表插入新纪录(4,400)，InnoDB的处理流程。

• 第一种情况，这个记库更新的目标也在内存中:
  • 唯一索引：找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束。
  • 普通索引：找到3和5之间的位置，插入值。结束。
• 第二种情况，这个记录要更新的目标不在内存中：
  • 唯一索引：需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束。
  • 普通索引，将更新记录在change buffer，语句执行结束。

---

• 将数据从磁盘读入内存涉及随机IO的访问，时数据库里面成本最高的操作。change buffer因为减少了随机磁盘访问。

change buffer的使用场景

• change buffer在meger的时候才是真正进行数据更新的时候，change buffer的主要目的是将记录的变更动作缓存下来，所以在一个数据页做merge之间，change buffer记录的变更越多，收益越大。
• 因此使用与多谢少读的业务，change buffer的使用效果最最好，如账单类、日志类的系统。
• 反之，如果业务更新只会马上做查询，那就会触发merge过程，这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。

3. 索引的选择

• 两类索引在查询能力上基本一样，主要考虑的是对更新性能的影响，所以最好选择普通索引。、
• 如果所有的更新后面，都伴随着对这个记录的查询，那么应该关闭change buffer。

4. change buffer 和 redo log

执行语句

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

• 假设查到位置后，k1所在的数据页在内存(innoDB buffer pool)中，k2所在的数据页不在内存中。如下图是带change buffer的更新状态图。
  ![](https://img-blo
• 分析更新语句。涉及到：内存、redo log(ib_log_filex)、数据表空间(t.lib)、系统表空间(ibdata1)
• 更新语句做了如下擦偶哦在
  1. Page 1 在内存中，直接更新内存；
  2. Page 2没有在内存中，就在内存的change buffer区域，记录下“往page 2插入一行”信息
  3. 将上述两个动作计入redo log
• 两次操作合在一起写入磁盘。
• redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。