09 | 普通索引和唯一索引，应该怎么选择？

想请教一下老师系统表空间跟数据表空间这两个概念各是什么意思.

抱歉做一个名词勘误，把change buffer应用到旧的数据页，得到新的数据页的过程，应该称为merge更合适。

先回答今天的问题,今天的答案应该在文章里就能找到 1.change buffer有一部分在内存有一部分在ibdata. 做purge操作,应该就会把change buffer里相应的数据持久化到ibdata 2.redo log里记录了数据页的修改以及change buffer新写入的信息 如果掉电,持久化的change buffer数据已经purge,不用恢复。主要分析没有持久化的数据 情况又分为以下几种: (1)change buffer写入,redo log虽然做了fsync但未commit,binlog未fsync到磁盘,这部分数据丢失 (2)change buffer写入,redo log写入但没有commit,binlog以及fsync到磁盘,先从binlog恢复redo log,再从redo log恢复change buffer (3)change buffer写入,redo log和binlog都已经fsync.那么直接从redo log里恢复。 老师,我有几个问题想请教下: 1.如果是针对非唯一索引和唯一索引的更新和delete而且条件是where 索引值=这种情况, 是否二级索引和唯一索引就没有区别呢 2.rr模式下,非唯一索引还会加gap,开销应该也不算小吧 3.还有老师你是怎么判断内存命中率的,是hit rate嘛？ 4.ob好像就是这个思路来做的聚簇索引的insert优化,不知道是怎么判断的唯一性 特别感谢老师,以前的知识都是很碎片化,没有深入的去思考。 经过几期的学习以后,感觉思路越来越开阔,以前觉得很高深的知识点,现在也有点豁然开朗的感觉。

感觉今天这篇问题很严重啊, 首先说一下我是第一次接触 change buffer这个概念, 可能产生了什么误会.. 我理解的文中讲述change buffer的作用体现在 针对普通索引(非主键的都是二级索引, 二级索引又包括了唯一索引和普通索引)在有数据update操作(不包括insert)的时候,能有减少io写操作的功能, 而且这个操作是提现在更新表数据上的. 为什么我在这里会理解成update操作呢.. (InnoDB中表就是按索引的方式存放的, 即使我们不主动创建主键 也会生成一个默认的row_id来当做主键, 意味着表一定是有一个主键, 即唯一索引. insert操作 一定会涉及主键索引的变动, 所以change buffer针对 insert 是完全没有用的吗??) 针对change buffer 我百度了一下, 有文章描述 change buffer 是针对表中包含普通索引的表在insert操作时, 优化 普通索引的更新(在insert时,不会立即更新普通索引 而是保存到change buffer延迟处理). 这么一个功能. 不知道 这个理解是否正确呢?

早，请您看看我以下疑问： 1. 看完后感觉牵扯到之前的内容，又糊涂了。change buffer相当于推迟了更新操作，那对并发控制相关的是否有影响，比如加锁？我一直以为加锁需要把具体的数据页读到内存中来，才能加锁，然而并不是？ 2. 在change buffer中有此行记录的情况下，再次更改，是增加一条还是原地修改？ 3. purge行为之后应该不会再产生redo log了吧？ 从应用开发的角度看，还是由数据库保证唯一好。

老师你好，我说下我的理解，不知道有没有问题 1、changebuffer跟普通数据页一样也是存在磁盘里，区别在于changebuffer是在共享表空间ibdata1里 2、redolog有两种，一种记录普通数据页的改动，一种记录changebuffer的改动 3、只要内存里脏页（innodb buffer pool）里的数据发生了变化，就一定会记录2中前一种redolog （对数据的修改记录在changebuffer里的时候，内存里是没有这个物理页的，不存在脏页） 3、真正对磁盘数据页的修改是通过将内存里脏页的数据刷回磁盘来完成的，而不是根据redolog

会导致change buffer丢失，会导致本次未完成的操作数据丢失，但不会导致已完成操作的数据丢失。 1.change buffer中分两部分，一部分是本次写入未写完的，一部分是已经写入完成的。 2.针对未写完的，此部分操作，还未写入redo log，因此事务还未提交，所以没影响。 2.针对，已经写完成的，可以通过redo log来进行恢复。 所以，不会对数据库造成影响。

回答一下melon的问题。 change Buffer和数据页一样，也是物理页的一个组成部分，数据结构也是一颗B+树，这棵B+树放在共享表空间中，默认ibdata1中。change buffer 写入系统表空间机制应该和普通表的脏页刷新到磁盘是相同的机制--Checkpoint机制； 之所以change buffer要写入系统表空间，是为了保证数据的一致性，change buffer做修改时需要写redo，在做恢复时需要根据redo来恢复change buffer，若是不进行change buffer写入系统表空间，也就是不进行持久化，那么在change buffer写入内存后掉电（也就是篇尾提出的问题），则无法进行数据恢复。这样也会导致索引中的数据和相应表的相应列中的数据不一致。 change buffer 写入到了系统表空间，purge 的时候会先查询change buffer里对应的记录，然后进行purge，因为change buffer B+树的key是表空间ID，所以查询根据表空间ID 查询change buffer会很快。

前两次学了之后没时间总结。。今天继续 总结: 选择普通索引还是唯一索引？ 对于查询过程来说： a、普通索引，查到满足条件的第一个记录后，继续查找下一个记录，知道第一个不满足条件的记录 b、唯一索引，由于索引唯一性，查到第一个满足条件的记录后，停止检索 但是，两者的性能差距微乎其微。因为InnoDB根据数据页来读写的。 对于更新过程来说： 概念：change buffer 当需要更新一个数据页，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中的与这个页有关的操作。 change buffer是可以持久化的数据。在内存中有拷贝，也会被写入到磁盘上 purge:将change buffer中的操作应用到原数据页上，得到最新结果的过程，成为purge 访问这个数据页会触发purge，系统有后台线程定期purge，在数据库正常关闭的过程中，也会执行purge 唯一索引的更新不能使用change buffer change buffer用的是buffer pool里的内存，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。 将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。 change buffer 因为减少了随机磁盘访问，所以对更新性能的提升很明显。 change buffer使用场景 在一个数据页做purge之前，change buffer记录的变更越多，收益就越大。 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer,但之后由于马上要访问这个数据页，会立即触发purge过程。 这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。 索引的选择和实践： 尽可能使用普通索引。 redo log主要节省的是随机写磁盘的IO消耗(转成顺序写)，而change buffer主要节省的则是随机读磁盘的IO消耗。 思考题： change buffer不会丢失，因为change buffer是可以持久化的数据，在磁盘上占据了系统表空间ibdata，对应的内部系统表名为SYS_IBUF_TABLE。因此在异常关机的时候，不会丢失。

老师讲的真清楚！但是我突然想到一个问题，文中讲change buffer中存的内容是“在某个数据页更新什么”，但是在update/insert时，确定这条记录更新/插入在哪个数据页，不也是有一个查找的过程么？（肯定有一个一层层查找的过程，会路过很多数据页啊）为了确定在哪个数据页操作而遍历过的数据页也会读进内存作缓存吗？