Mysql面试题_索引02

文章目录

• • Mysql面试题_索引02
  • • 1.从性能的角度考虑，怎么选择唯一索引和普通索引，为什么？
    • • 查询过程
      • 更新过程
    • 2.什么是change buffer?
    • 3.change buffer的使用场景？
    • 4.如何选择普通索引和唯一索引？
    • 5.如何理解redo log和change buffer?

1.从性能的角度考虑，怎么选择唯一索引和普通索引，为什么？

首先，唯一索引(UNIQUE)和普通索引(key)使用的数据结构都是b-tree；执行时间复杂度都是O(logn).

查询过程

对于查询过程来说，使用唯一索引和普通索引的性能几乎相同：

• 对于普通索引而言：在查找到满足条件的第一条记录后，还需要炸炒下一条记录，直到碰到第一个不满足条件的记录为止；
• 对于唯一索引而言：由于索引定义了唯一性，在查找到第一个满足条件的记录后，停止检索。

要知道，InnoDB的数据都是按数据页为单位来读写的(default: 16KB)，而当需要读取一条记录时，mysql并不是将这条记录本身从磁盘读取出来，而是以页为单位，将其整体读入内存中；因此对于查询过程而言，两者查询性能几乎相同。

更新过程

对于更新过程来说，唯一索引不能使用change buffer ,而普通索引可以使用chnager buffer。

• 对于唯一索引：所有的更新操作都需要先判断这个操作是否违反唯一性约束。对于插入一条记录，首先要判断的就是该记录在表中是否存在，如果这条记录不在目标页内存中，则需要将数据页读入内存，并判断到没有冲突时，方可插入该记录。要知道将数据从磁盘读入到内存就会涉及随机IO的访问，这是数据库里面成本最高的操作之一。
• 对于普通索引：Mysql为了减少随机磁盘访问，更新操作时便引入了change buffer的机制。因此对于插入一条在目标页内存中不存在的记录，只需要将更新记录在change buffer中就行了，避免了频繁的IO操作。

2.什么是change buffer?

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

• Changer 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%。
• change buffer 因为减少了随机磁盘访问，所以对更新性能的提升很明显。

3.change buffer的使用场景？

• change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。
• change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做purge之前，change buffer记录的变更越多，收益就越大。
• 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

4.如何选择普通索引和唯一索引？

• 普通索引和唯一索引在查询性能上几乎没有差别，但普通索引在更新性能上会优于唯一索引，因此尽量使用普通索引。
• 当更新操作之后马上查询时，不建议使用change buffer
• change buffer更适合普通索引
• 在实际使用中，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

5.如何理解redo log和change buffer?

redo log 与 change buffer(含磁盘持久化) 这2个机制，不同之处在于优化了整个变更流程的不同阶段。 先不考虑redo log、change buffer机制，简化抽象一个变更(insert、update、delete)流程：

1. 从磁盘读取待变更的行所在的数据页，读取至内存页中
2. 对内存页中的行，执行变更操作
3. 将变更后的数据页，写入至磁盘中

• 步骤1，涉及 随机 读磁盘IO；–change buffer机制，优化了步骤1,避免了随机读磁盘IO;
• 步骤3，涉及 随机 写磁盘IO；-redo log机制， 优化了步骤3,避免了随机写磁盘IO，将随机写磁盘，优化为了顺序写磁盘(写redo log，确保crash-safe) ；
• 在mysql innodb中， change buffer机制不是一直会被应用到，仅当待操作的数据页当前不在内存中，需要先读磁盘加载数据页时，change buffer才有用武之地。 redo log机制，为了保证crash-safe，一直都会用到。

redo log主要节省的是随机写磁盘的IO消耗(转成顺序写)，而change buffer主要节省的则是随机读磁盘的IO消耗。