普通索引和唯一索引查询过程区别
例如执行一条查询语句:select id from T where k=5
这个查询语句在索引树上查找的过程,先是通过 B+ 树从树根开始,按层搜索到叶子节点,就能取到满足k条件的id值。
- 对于普通索引来说,查找到满足条件的第一个记录 (5,500) 后,需要查找下一个记录,直到碰到第一个不满足 k=5 条件的记录。
- 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。
可以看出来,对于同样的一条查询语句。在K字段使用唯一索引时候会比普通索引少检索一次。对于少掉的一次检索,我们来讨论下他对性能的影响。
mysql的数据存储是以数据页形式,每个数据页的大小默认是 16KB,对于整型字段,一个数据页可以放近千个 key,读取数据每次是以页为单位,读取到内存。
所以大多数情况,在使用普通索引时,多出来的这最后一次检索,数据还是和前一次在同一个数据页里,我们就只需要一次指针寻找和一次计算。不需要再次读取数据页,产生磁盘IO。
综上,在查询操作时,普通索引和唯一索引差别是微乎其微的。
普通索引和唯一索引更新区别
1. change buffer:
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。(重要)
change buffer在内存中有拷贝,也会被写入磁盘。
将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作。
可以看出来,如果能将更新操作先记录在change buffer,可以减少读磁盘,语句执行速度会得到明显提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够避免占用内存,提高内存利用率。
2.change buffer的使用
唯一索引不能使用change buffer
对于唯一索引来说,所有的更新操作都要先判断这个操作是否违反唯一性约束。比如,要插入 (4,400) 这个记录,就要先判断现在表中是否已经存在 k=4 的记录,而这必须要将数据页读入内存才能判断。如果都已经读入到内存了,那直接更新内存会更快,就没必要使用 change buffer 了。
tips:change buffer 用的是 buffer pool 里的内存,因此不能无限增大。change buffer 的大小,可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候,表示 change buffer 的大小最多只能占用 buffer pool 的 50%
对于普通索引和唯一索引
- 如果要更新的目标页已经在内存中了,那么就直接更新操作;
- 如果要更新的目标页不在内存中
- 对于唯一索引,因为要校验是否违反唯一性约束,所以需要先将数据页读到内存中,再执行更新操作
- 对于普通所以,我们就能使用change buffer记录更新数据
将数据从磁盘读入内存涉及随机 IO 的访问,是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问,所以对更新性能的提升是会很明显的。所以可以看出来,对于更新操作,普通索引对比唯一索引有更大的优势,因为可以用到change buffer
change buffer使用场景
使用 change buffer 对更新过程的加速作用, change buffer 只限于用在普通索引的场景下,而不适用于唯一索引。
但是change buffer会比较适合写多,读少的场景
。因为如果写完后都是马上要读,那么会频繁的执行merge操作,这样随机访问的IO次数并不会减少,反而还要多维护一个change buffer。