目录
3.更新过程这两种索引的区别？涉及到change buffer
前言:
在不同的业务场景下面，到底该怎么设计索引？
是使用普通索引还是使用唯一索引。
什么时候单独建立一个索引，什么时候建立组合索引？
1. 唯一索引和普通索引的区别
比如身份证，如果业务代码已经保证了不会重复插入了。
而且业务中查询也是按照身份证号来查询的，那么
我会在id_card_no这个字段上面建立索引
因为使用自增ID有很多好处，而且身份证号很长，并不适合做主键。
---------------------------
唯一索引和普通索引的区别：
普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。
唯一索引 是因为普通索引在索引树上面允许存在重复的值，而唯一索引不允许，
唯一索引在创建索引的时候使用关键字UNIQUE来定义唯一的索引。
创建唯一索引的好处：
1.减轻mysql的负担，索引更有效率
2.唯一索引保证记录唯一性。
更重要的是避免数据出现重复(觉得是底层的一种校验，在插入的时候拒绝)
另外一个表可以创建多个唯一索引。
2.唯一索引和普通索引查询过程的区别
B+树内部索引树可以认为数据页内部通过二分法来定位记录。
普通索引：找到了满足条件的第一个记录之后，还需要找下一个记录，直到碰到第一个不满足条件的记录。
唯一索引：因为定义了唯一性，查找到第一个满足条件的记录之后，就会停止检索。
这个查询过程的差距可以不计算。
因为innodb是通过数据页来读写的，所以在读一条记录的时候，并不是讲这个记录从磁盘拿出来，
而是以页为单位，整体读入内存。
在innodb中每个数据页的大小是16KB
如果下一条数据已经在内存的数据页里面了，对于普通索引就是多做一次查找和判断洗一条记录。
做一次寻址和计算。
因为整型字段，一个数据页可以放上千个key，所以出现要去下一个数据页的概率很低。
所以这两种差异可以小到不计。
3.更新过程这两种索引的区别？涉及到change buffer
个人总结：因为唯一索引在更新的时候，要把数据页查出来，看看冲不冲突，然后在更新进去。
所以唯一索引并不能用changebuffer。
所以如果是频繁写入更新的数据库，尽量别使用唯一索引，建立普通索引就好~
以下学习自极客时间-MYSQL实战45讲。
更新的时候，如果数据页在内存中，就会直接更新。如果没在内存中，在不影响数据一致性的前提下，
innodb有着更新操作缓存change buffer。
这个缓存的特点，内存中有一份 硬盘中也有一份，是持久化的，
而且查询到这个数据页的时候，会进行吧这个change buffer merge进去，执行merge操作。
(PS:后台线程也会定期的merge这个change buffer到磁盘中)
change buffer 可以减少读磁盘，执行速度会得到很大的提升。
数据读入到内存中占用buffer pool，这种方式可以避免占用内存，提高内存的占用率，因为减少读入内存的次数。
另外：数据从硬盘中读入到内存是随机IO访问，成本很高。
4. change buffer的使用场景
普通索引的所有场景，不一定都会用到change buffer。
因为merge才是真正数据更新的时刻，change buffer就是记录下来更新的动作，在一个数据页做merge的时候，如果记录的数据阅读，收益越大。
账单，日志等写入多的可以使用change buffer 使用普通索引。
如果一个业务的更新更新莫使是写入之后，马上就会做查询，频繁merge随机访问IO不会少，那么代价会很高，
不适合change buffer
5. 总结
查询上面几乎没差别的，主要考虑更新的时候的对性能的影响。尽量选取普通索引。
如果更新之后就要查询的记录，应该关闭change buffer。
change buffer 和redo log？
redo log加快随机写硬盘的IO消耗，让它变成顺序写入。
change buffer节省了随机读硬盘的IO消耗。
6.QA
Q: 是否使用唯一索引？
业务正确性第一，如果业务不能保证写入不重复的数据，那么业务就是用数据库来做约束。
但是由此可能会导致 --大量插入数据慢，内存命中率很低的时候(查询几乎没区别但是插入和更新有影响的。)
在一些“归档库”的场景，你是可以考虑使用普通索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。
Q:归档库？
大概看了看网上的思路，感觉是数据量很大，为了迎接新的报增的数据或者优化一些查询？
将表的历史数据，根据索引，截断成一天一天的(大概)，然后迁移到归档表中。
比如时间有索引，查询整天的，然后分配弄定时任务来定时执行弄到归档表。
数据库归档技术是一种保持在线数据库规模大体不变却有能够为用户应用提供稳定的数据库性能的方法。其工作原理是，将数据库中不经常使用的数据迁移至近线设备，将长期不使用的数据迁移至文件形式归档。这样，随着应用的需要，数据会在在线、近线和文件文档之间移动，如当应用需要访问很久以前的某些数据，它们的物理位置在近线设备，则会自动移动到在线设备。对用户的应用而言，这些都是透明的，就像所有数据都存放在在线设备一样，不会对数据库应用产生任何影响。
--有点难，具体https://blog.csdn.net/weixin_34128411/article/details/90494542
把一条记录从生产数据库插入到历史数据库中，然后把该条记录在生产数据库中删除就实现了数据库归档的功能。
Q:系统表空间与数据表空间？
系统表空间就是用来放系统信息的，比如数据字典什么的，对应的磁盘文件是ibdata1,
数据表空间就是一个个的表数据文件，对应的磁盘文件就是 表名.ibd
Q:change buffer与加锁？
锁是一个单独的数据结构，如果数据页上有锁，change buffer 在判断“是否能用”的时候，就会认为否
在change buffer中有此行记录的情况下，再次更改，会继续增加一条
Q: 上面提到的merge的行为？
merge其实是从磁盘读数据页到内存，然后应用，这一步都是更新的内存，同时写redolog
Q:change buffer存在哪里？脏页是什么？
1、changebuffer跟普通数据页一样也是存在磁盘里，区别在于changebuffer是在共享表空间ibdata1里
2、redolog有两种，一种记录普通数据页的改动，一种记录changebuffer的改动
3、只要内存里脏页（innodb buffer pool）里的数据发生了变化，就一定会记录2中前一种redolog
（对数据的修改记录在changebuffer里的时候，内存里是没有这个物理页的，不存在脏页）
3、真正对磁盘数据页的修改是通过将内存里脏页的数据刷回磁盘来完成的，而不是根据redolog
Q:change buffer也是一种物理页？(checkpoint机制)
change Buffer和数据页一样，也是物理页的一个组成部分，数据结构也是一颗B+树，这棵B+树放在共享表空间中，默认ibdata1中。change buffer 写入系统表空间机制应该和普通表的脏页刷新到磁盘是相同的机制--Checkpoint机制；
之所以change buffer要写入系统表空间，是为了保证数据的一致性，change buffer做修改时需要写redolog，在做恢复时需要根据redo来恢复change buffer，若是不进行change buffer写入系统表空间，也就是不进行持久化，那么在change buffer写入内存后掉电，则无法进行数据恢复。这样也会导致索引中的数据和相应表的相应列中的数据不一致。
change buffer 写入到了系统表空间，purge 的时候会先查询change buffer里对应的记录，然后进行purge，因为change buffer B+树的key是表空间ID，所以查询根据表空间ID 查询change buffer会很快。
也就是changebuffer 是需要持久化的，否则没有数据备份了，如果断电之类change buffer丢失需要用redolog 回复change buffer~
Q:InnoDB Purge？
InnoDB由于要支持多版本协议, 因此无论是更新, 删除, 都只是设置记录上的deleted bit标记位, 而不是真正的删除记录. 后续这些记录的真正删除, 是通过Purge后台进程来进行。
Purge进程定期扫描InnoDB的undo, 按照先读老undo, 再读新undo的顺序, 读取每条undo record. 对于每一条undo record, 判断其对应的记录是否可以被purge(purge进程有自己的read view, 等同于进程开始时最老的活动事务之前的view, 保证purge的数据, 一定是不可见数据, 对任何人来说), 如果可以purge, 则构造完整记录(row_purge_parse_undo_rec). 然后按照先purge二级索引, 最后purge聚簇索引的顺序, purge一个操作生成的旧版本完整记录.
原文链接：https://blog.csdn.net/zwleagle/article/details/44567251
Q:buffer pool?
buffer pool，硬盘在读写速度上相比内存有着数量级差距，如果每次读写都要从磁盘加载相应数据页，DB的效率就上不来，因而为了化解这个困局，几乎所有的DB都会把缓存池当做标配（在内存中开辟的一整块空间，由引擎利用一些命中算法和淘汰算法负责维护和管理），change buffer则更进一步，把在内存中更新就能可以立即返回执行结果并且满足一致性约束（显式或隐式定义的约束条件）的记录也暂时放在缓存池中，这样大大减少了磁盘IO操作的几率
Q:change buffer的持久化过程？
merge 的执行流程是这样的：从磁盘读入数据页到内存（老版本的数据页）；从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据。
扫一扫
480
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
