• mysql笔记系列(七)唯一索引和普通索引的性能区别


     

    普通索引和唯一索引,应该怎么选择?
    在innodb中每个页的大小为16kb,读一条记录时以页为单位读入内存。

    普通索引查找数据的时候,会将符合条件的都找出来
    唯一索引,只要找到第一条符合条件的,就会立刻返回,不再继续找了,因为唯一的约束已经事先确保了只有一条符合条件。
    但是对查询来说,以上两种 如果数据都只有1条,时间是相似的,微乎其微。

    change buffer
    当更新一个数据页的时候,如果数据页在内存中就直接更新,如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,innoDB会将这些更新操作缓存到change buffer中,这样就不需要从磁盘中读入这个数据页了,下次遇到这个数据页读到内存的时候,就把changebuffer中的更新merege到 这个数据页上面。

    注意:虽然是叫changeBuffer 但是这个也是会持久化到硬盘中的。 merege操作除了在访问数据页的时候会触发,在系统后台也会定期merege ,在数据库正常关闭(shutdown)的过程中,也会执行merge操作。

    这个优化 把更新操作在内存中进行,减少磁盘的读写,可以极大提高性能。

    changerbuffer不能被唯一索引用到,因为唯一索引每次更新都要判断这个操作是否违反唯一性约束,比如说要插入某个数据,要先判断其是否已经存在,那就必须要将这个数据页读入内存中才知道,既然都读入到内存中,那么直接更新就好了,不需要changebuffer。 因此这个只能被普通索引用到。

    那么对于插入来说,
    1.如果目标数据页在内存中
    普通索引
    先找到数据位置,然后插入
    唯一索引
    先找到数据位置,然后判断是否违反唯一键约束,然后插入

    2.如果目标数据页不在内存中
    普通索引
    将数据更新到changebuffer ,执行结束
    普通索引
    将数据页加载到内存中,先找到数据位置,然后判断是否违反唯一键约束,然后插入

    因此 唯一索引和普通索引在查询的时候 区别不大,但是在插入操作的时候,区别很大。
    因为是否读磁盘 对性能影响很大。

    因此 如果业务可以保证唯一性的情况下,普通索引优先于唯一索引。

    changebuffer的适用场景:
    如果数据是写多读少,那么效果将非常明显,因为写入的数据被立刻访问的概率小。

    如果数据是读多写少,因为读的时候会将数据页加到内存,会触发merege , 如果每次写入到changebuffer中就立刻触发merge ,那还不如直接加载到内存中写入,因为这样并没有减少随机IO,反而增加了changebuffer的维护负担。

    如果碰上了大量插入数据慢、内存命中率低的时候,可以考虑排查下是不是唯一索引的影响,因为有唯一索引的数据插入,每次都要加载数据页到内存,然后判断是否存在.

    注意下:随机IO和顺序IO的区别,随机是在任意位置,顺序则是从头到尾或者从尾到头, 效率上来说,随机IO要比顺序IO低的多,因为磁盘读写, 随机读写需要不断的移动磁头或者切换磁道。

    changebuffer和redolog 的区别:
    changebuffer是写在内存中,将原本要写到磁盘中的数据缓存到了内存中,减少了磁盘IO。
    redolog则是写在磁盘中,是将原本写到文件中的随机IO, 变成了顺序IO,提高了效率。

    注意!change buffer 一开始是写内存的,那么如果这个时候机器掉电重启,会不会导致 change buffer 丢失呢?change buffer 丢失可不是小事儿,再从磁盘读 入数据可就没有了 merge 过程,就等于是数据丢失了。会不会出现这种情况?

    答案是不会,因为这个操作记录,在事务提交的时候,也被写入到了redolog里面,崩溃恢复的时候,changeBuffer也会从redolog里面恢复回来。

    merge的过程:
    1.从磁盘读入老数据到内存
    2.从changebuffer找到这个数据页的记录,更新上去
    3.写redolog, redolog里面包含了数据的变更和changebuffer的变更。

     
  • 相关阅读:
    【leetcode】416. Partition Equal Subset Sum
    【leetcode】893. Groups of Special-Equivalent Strings
    【leetcode】892. Surface Area of 3D Shapes
    【leetcode】883. Projection Area of 3D Shapes
    【leetcode】140. Word Break II
    【leetcode】126. Word Ladder II
    【leetcode】44. Wildcard Matching
    【leetcode】336. Palindrome Pairs
    【leetcode】354. Russian Doll Envelopes
    2017.12.22 英语面试手记
  • 原文地址:https://www.cnblogs.com/kelelipeng/p/13164476.html
Copyright © 2020-2023  润新知