-
MySQL高级sql优化主从分库分表-分库表,唯一索引-MySQL视频教程...
1. MySQL高级之主键索引 [ 11:13 ] 2. MySQL高级之唯一索引 [ 17:05 ]...2. 查询性能分析所有字段字符类型优化 [ 19:02 ] 3. MySQL高级之count()优...
36课时 653分钟574人学习lampol免费试看
普通索引和唯一索引,应该怎么选择?
在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的变更。