change buffer：到底应该选择普通索引还是唯一索引

文章目录

引言

在上期的文章【深入理解MySQL事务】中，我们讲解了MySQL的事务实现，在【MySQL 日志：Redo、Bin 与 Undo Log】，我们分析了三种核心日志——Redo Log、Bin Log 和 Undo Log，探讨了它们的作用、工作原理及写入时机。这两篇文章都有提到change buffer。。在这篇文章中，我们从索引开始，继续聊聊change buffer。

在现代数据库系统中，索引是提高数据检索速度、优化查询性能的重要工具。MySQL 的 InnoDB 存储引擎通过 B+ 树结构来管理和存储索引数据，以确保查询和更新操作的高效性。索引类型中，普通索引和唯一索引是使用最为广泛的两种，它们在实现、使用场景以及对系统性能的影响上各具特点。

普通索引允许索引值重复，适用于大部分需要频繁查询的数据；唯一索引则保证数据的唯一性，广泛用于具有唯一约束的字段。由于二者的底层机制不同，在查询效率、更新逻辑和数据完整性方面存在显著区别。此外，InnoDB 提供了 change buffer 和 redo log 两项关键机制，分别用于优化写入性能和保障数据一致性。

第一章：普通索引和唯一索引在查询逻辑与效率上的对比

1.1 查询逻辑分析

普通索引的查询逻辑

在 MySQL 中，普通索引是基于 B+ 树的结构。数据以页为单位进行存储和查询，因此即使查询条件匹配多条记录，MySQL 也会按页加载数据来提高效率。普通索引的设计允许数据重复，当执行查询时，MySQL 会从 B+ 树的根节点出发，逐层遍历节点直到找到第一个匹配项的叶子节点。之后，MySQL 会继续扫描该页及后续页以返回所有符合条件的数据。

示例：假设有一个 employees 表，包含 id、name 和 department 字段，其中 department 是普通索引。当查询department = 'Sales'时，MySQL 通过 department 索引树定位到包含“Sales”值的第一个数据页，并从该页开始扫描所有匹配的数据。

唯一索引的查询逻辑

唯一索引和普通索引一样，也是使用 B+ 树存储结构，但它限制了索引列的值必须唯一。查询时，MySQL 能更快地定位到符合条件的数据，因为一旦找到匹配项，即可停止搜索，不需要继续扫描其他节点。然而，由于数据是按页加载的，唯一索引在大多数场景中不会明显优于普通索引。

示例：假设 employees 表中的 id 字段是唯一索引。当查询 id = 102 时，MySQL 通过 B+ 树定位到目标页，加载整个数据页并找到匹配记录。由于该字段唯一，MySQL 直接返回结果。

普通索引查询流程:

唯一索引查询流程:

1.2 查询效率对比

在 MySQL 中，由于数据是以页为单位读取，普通索引和唯一索引在查询效率上的差异通常并不显著。尤其是在现代数据库系统中，缓存技术、CPU 缓存、以及查询优化机制都有效缩小了两者的性能差距。

数据页读取对性能的影响：MySQL 使用固定大小的数据页（InnoDB 默认16KB）管理存储数据，因此每次查询时都会加载包含目标记录的整个页。这意味着即使普通索引需要返回多个匹配记录，查询过程也无需逐条处理，而是以页为单位批量读取，极大提高了读取效率。
缓存与顺序读取的优势：InnoDB 存储引擎采用了缓冲池（Buffer Pool）机制来缓存热点数据页，减少磁盘 I/O 频率。因此，对于频繁访问的索引项（无论是普通索引还是唯一索引），大部分查询都可以直接在内存中完成，进一步提升查询效率。此外，数据库会将随机 I/O 优化为顺序 I/O 进行处理，使得即便普通索引需要多次扫描数据页，性能差异也不大。
唯一索引的优势：唯一索引的优势主要体现在数据唯一性约束上，而非显著提升查询速度。因为唯一索引在查找到首个符合条件的数据后即可返回结果，这在大数据量查询时可能略微减少查找路径。但由于 B+ 树和缓存机制的优化，现代数据库在处理普通索引查询时也能达到高效的性能。

综上所述，普通索引和唯一索引在现代数据库中查询效率上的差异较小。由于数据库以页为单位加载数据，加之缓存和索引优化机制，两种索引类型的查询速度在大多数情况下相近。唯一索引的主要优势在于数据唯一性约束，而非显著的查询性能提升。

普通索引：适合需要返回多条记录的查询条件。尽管在极端大数据量场景中，查询效率可能略低，但大多数查询会因缓存效果而保持较高性能。
唯一索引：适用于需要精确定位单条记录的场景，其查询逻辑略微简化。但其主要用途是提供数据的唯一性保障，而非明显提升查询速度。

第二章：普通索引和唯一索引在更新逻辑与效率上的对比

2.1 更新逻辑分析

普通索引的更新逻辑

普通索引允许重复值，因此在更新操作中无需进行唯一性验证，更新逻辑较为直接。更新过程包括以下步骤：

定位数据页：MySQL 通过 B+ 树定位到要更新的数据所在的页。
修改数据页：在目标页中执行更新操作（如修改记录、插入新记录、删除旧记录）。
变更记录：InnoDB 会将更新的变更先记录到 redo log（重做日志）中，以保证数据的持久性；而对于插入和删除操作，普通索引还可以使用 change buffer 来缓存变更，从而延迟磁盘写入，提高更新效率。
提交更新：更新操作完成后，MySQL 将修改应用到实际的 B+ 树结构中。

示例：假设 employees 表的 department 字段为普通索引，当我们执行 UPDATE employees SET department='HR' WHERE department='Sales'时，MySQL 会逐条更新索引页中的匹配项，并利用 change buffer 延迟部分 I/O 操作。

唯一索引的更新逻辑

唯一索引在更新操作时会附加一个唯一性检查，确保修改不会导致数据冲突。更新步骤如下：

定位数据页：与普通索引相同，唯一索引通过 B+ 树定位目标页。
唯一性检查：在执行更新之前，MySQL 检查目标值是否与现有索引项冲突。这一步会增加额外的 I/O 操作，以确保数据完整性。
更新数据页：完成唯一性验证后，MySQL 执行数据修改。
变更记录：InnoDB 将变更记录到 redo log 中以保证持久性。但由于 change buffer 不支持唯一索引，因此 MySQL 直接将数据写入磁盘。

示例：假设 employees 表中的 id 字段是唯一索引，当执行 UPDATE employees SET id = 105 WHERE id = 102时，MySQL 会首先验证是否存在id = 105的记录。如果没有冲突，才会继续更新操作。

以下流程图展示了普通索引和唯一索引在更新过程中的不同逻辑：

普通索引更新流程图:

唯一索引更新流程图:

2.2 更新效率对比

在数据更新效率方面，普通索引由于可以使用 change buffer 缓存更新，通常表现出更高的效率。而唯一索引必须直接写入磁盘，尤其在高频更新场景下，可能会导致性能瓶颈。

change buffer 的作用：InnoDB 的 change buffer 允许普通索引在更新时将变更先写入内存，而不立即更新磁盘上的 B+ 树结构。之后，当数据被查询或达到一定条件时再批量应用到磁盘，这种方式可以大大降低磁盘 I/O 频率。
唯一索引的局限：由于唯一索引必须保证数据唯一性，每次更新都需要直接写入磁盘，因此无法利用 change buffer 来延迟写入。换句话说，每一次更新都会触发一次 I/O 操作，因此当更新量较大时，唯一索引的性能会受到影响。

综上所述，普通索引和唯一索引在更新逻辑和效率上有以下区别：

普通索引：适用于大批量更新，因为它能够使用 change buffer 缓存变更，从而减少磁盘 I/O 操作，提升性能。
唯一索引：在更新时需要进行唯一性检查，并直接写入磁盘，无法利用 change buffer 缓存操作。因此，在高频更新场景中效率会略低。

总结：在需要频繁更新的场景中，选择普通索引会显著提升性能；而唯一索引则适合数据要求高度唯一性、并且更新量较少的情况。

第三章：底层原理详解 - 普通索引和唯一索引的区别

3.1 索引存储结构对比

在 MySQL 的 InnoDB 存储引擎中，普通索引和唯一索引都基于 B+ 树结构来组织和存储数据。B+ 树是一种平衡树结构，特别适用于数据库系统中需要快速查找的场景。以下是普通索引和唯一索引在 B+ 树结构上的存储方式：

普通索引的存储结构

普通索引允许重复值，在 B+ 树的叶子节点中，索引项按列的值排序，但不强制唯一性。对于相同的索引值，会依次存储多个对应的记录指针。因此，在数据量大、索引项重复较多的情况下，普通索引的 B+ 树可能包含多个叶子节点来存储相同值的索引项。

示例：假设有一个 employees 表，其中 department 字段是普通索引。当索引项的值为“Sales”时，B+ 树的多个叶子节点可能会存储指向不同记录的“Sales”项。

唯一索引的存储结构

唯一索引在设计上不允许重复值，因此每个 B+ 树的叶子节点中仅存储一个符合该唯一性约束的记录指针。唯一索引的这种设计减少了叶子节点的数量，并在查找和更新过程中减少了 I/O 操作。

示例：如果 id 字段为唯一索引，当 B+ 树中存在 id=101 的节点时，任何其他数据项的 id 都不能再等于 101。

3.2 索引维护机制

普通索引的维护机制

当插入、更新或删除操作影响到普通索引的列值时，MySQL 会对 B+ 树进行以下操作：

节点插入或删除：如果新数据使得节点空间不足，B+ 树会进行节点分裂；而如果数据删除后节点利用率过低，可能会触发合并操作。
更新操作：普通索引支持 change buffer 缓存变更，因此不需要每次更新都直接写入磁盘。在数据查询或写入时，系统会合并 change buffer 的数据。
重新平衡：当普通索引的 B+ 树结构发生改变时，系统会自动重新平衡树的结构，以保证查询效率。

唯一索引的维护机制

唯一索引的维护比普通索引更严格，因为它需要保证数据的唯一性。因此在插入或更新时会进行以下操作：

唯一性验证：每次插入或更新操作，InnoDB 必须先检查 B+ 树中是否已经存在相同值的节点，确保数据唯一性。这一操作会带来额外的 I/O。
无法使用 change buffer：由于唯一索引必须保证实时唯一性，因此不能使用 change buffer 缓存变更，导致更多的磁盘 I/O。
树结构更新：当唯一索引发生插入或删除时，也会通过节点分裂、合并等操作维护树的平衡。

3.3 存储结构和维护机制的总结

通过对比可见，唯一索引的存储和维护更为严格，它在查找和更新过程中多了唯一性检查的操作，同时无法利用 change buffer 来延迟写入。但在数据不允许重复的场景中，唯一索引可以提供完整性保障。

普通索引：结构允许重复值，支持 change buffer 缓存更新，因此适合需要批量更新的场景。
唯一索引：结构不允许重复，无法使用 change buffer，但能保证数据完整性，适合对唯一性要求严格的场景。

第四章：change buffer

4.1 唯一索引无法使用 change buffer 的原因

change buffer 是 InnoDB 存储引擎中的一种优化机制，旨在减少磁盘 I/O 操作。它将部分写操作暂时缓存到内存中，在适当时机再批量写入磁盘，以提升性能。然而，change buffer 仅适用于普通索引，唯一索引无法利用这一机制，其主要原因如下：

唯一性约束要求即时校验：唯一索引要求数据的每一项都必须唯一，因此在执行插入或更新操作时，必须立即验证是否存在相同值的数据。如果唯一索引使用 change buffer 缓存变更，无法实时校验唯一性，可能导致重复数据插入或更新，破坏数据的完整性。
普通索引无需实时校验：普通索引允许重复值，因此插入、更新等操作可以暂时存储在 change buffer 中，并在下次读取或合并时批量写入磁盘。由于普通索引不需要即时的完整性检查，所以它可以利用 change buffer 延迟 I/O 操作。
持久性与一致性要求：唯一索引的本质是确保数据库中的数据满足唯一性约束，因此在操作完成时，唯一性必须得到保证。这种严格的要求使得唯一索引的所有修改操作都需要立即写入磁盘，而不能通过 change buffer 延迟。

4.2 change buffer 的工作流程与原理

change buffer 是 InnoDB 的一种专用于普通索引的优化缓冲区。其主要工作原理是在执行插入、更新或删除操作时，先将修改记录在 change buffer 中，而不直接写入目标数据页，等到数据被访问或后台系统资源空闲时再合并至磁盘，从而减少随机 I/O 操作。

change buffer 的工作流程：

变更操作记录：当普通索引发生插入、更新或删除操作时，InnoDB 会先将变更记录到 change buffer 中，而不立即更新实际的数据页。
变更合并：当下一次查询或后台合并触发时，InnoDB 会将 change buffer 中的变更记录合并到目标数据页。
数据页写入磁盘：合并后的数据页会被写入磁盘，完成物理页的更新。

change buffer 的结构：

change buffer 存储在系统表空间中，且占据缓冲池的一部分，因此位于内存中的 change buffer 可以被快速访问。
InnoDB 会动态调整 change buffer 大小，以便在系统资源闲置时最大限度地利用该缓冲区。

流程图：change buffer 的操作流程

4.3 change buffer 的性能优势与局限性

性能优势：

减少磁盘 I/O：通过将变更操作暂时缓存在内存中，InnoDB 可以在后台批量合并数据页，显著减少了对磁盘的随机 I/O 操作。
提升写入效率：change buffer 使得系统能够将多个随机写入操作合并为一次顺序写入，大大提升写入效率。
适用于读写比不平衡的场景：在更新较为频繁、查询较少的场景中，change buffer 可以充分利用系统空闲时间完成合并操作，优化磁盘 I/O。

局限性：

只能用于普通索引：由于唯一索引要求数据的唯一性，change buffer 无法用于唯一索引，否则会破坏唯一性约束。
可能导致延迟读取：由于变更操作被暂时缓存，系统在读取时可能需要等待 change buffer 中的数据合并，导致数据延迟。
内存资源限制：change buffer 会占用缓冲池的一部分空间，因此在内存有限或缓冲池紧张的情况下，change buffer 的效果可能受到限制。

4.4 小结

在 InnoDB 存储引擎中，change buffer 是一种提升普通索引更新效率的重要机制。它允许普通索引通过延迟写入的方式减少随机 I/O，但由于唯一性验证的要求，唯一索引无法使用 change buffer。在读写频率不平衡的应用场景下，change buffer 的使用能够显著优化系统的写入性能，但也有其适用的边界条件。

第五章：change buffer 与 redo log 的原理、区别及使用场景

5.1 change buffer 的原理与作用

change buffer 是 InnoDB 专门设计用于延迟普通索引写入的机制，它的主要作用是通过延迟写入来减少磁盘的随机 I/O 操作，从而提高系统整体性能。

工作原理：在执行插入、更新或删除操作时，InnoDB 将变更记录到内存中的 change buffer，而不立即写入数据页。这些变更会在之后的读取请求或后台空闲时批量合并到磁盘数据页中，以降低 I/O 频率。
作用：change buffer 通过减少写入操作的频率，提升了数据库在高频写入场景中的响应速度。

5.2 redo log 的原理与作用

redo log 是 InnoDB 的崩溃恢复机制之一，它通过记录事务变更操作日志，以便在系统崩溃后能够恢复数据的一致性。与 change buffer 不同的是，redo log 不影响数据写入流程的即时性，但它保障了数据的持久性和一致性。

工作原理：在执行写入操作时，InnoDB 会先将变更记录到 redo log 中，并将 redo log 刷新到磁盘。即使事务还未将数据写入数据页，但通过 redo log 记录的变更可以确保系统崩溃时能够重建这些操作。
作用：redo log 通过持久化日志文件保障数据的可靠性和一致性，即便在崩溃后也可以利用 redo log 重做已提交的事务。

5.3 change buffer 和 redo log 的区别

虽然 change buffer 和 redo log 都涉及到 I/O 优化和延迟写入，但它们在原理、应用场景和实现机制上有显著区别。

特性	change buffer	redo log
主要用途	减少普通索引的随机 I/O 频率	提供事务的崩溃恢复机制
适用对象	仅限普通索引	所有类型的事务操作
延迟写入的对象	仅索引数据页	变更操作的日志
写入时机	在读取或空闲时批量合并至磁盘	每次事务变更后立即写入
缓存位置	缓冲池（Buffer Pool）的一部分	redo log 文件，固定大小循环使用
崩溃恢复能力	不提供崩溃恢复能力	提供崩溃恢复，保证事务的一致性

5.4 change buffer 与 redo log 的顺序 I/O 优化对比

change buffer 的顺序 I/O 优化

数据缓存：change buffer 通过将变更缓存到内存中，延迟写入磁盘，显著减少了随机写入操作。
批量合并：在批量合并变更操作时，change buffer 将多个随机写入变为顺序写入，使得数据页的更新更加高效。
使用场景：适用于频繁写入但读取较少的场景，比如批量数据导入、批量更新等。

redo log 的顺序 I/O 优化

日志持久化：redo log 将每个变更操作记录到日志中，并周期性地顺序写入磁盘，避免了数据页随机写入的开销。
循环使用：redo log 文件是固定大小的循环日志文件，通过不断覆盖旧数据，实现持续顺序写入。
使用场景：适用于所有事务场景，尤其在需要确保数据一致性和事务持久性的应用中，如银行、支付系统等对数据可靠性要求极高的场景。

5.5 使用场景与最佳实践

change buffer 的使用场景

change buffer 适用于写多读少的场景。在数据导入或频繁更新的过程中，change buffer 可以显著减少磁盘 I/O 次数，从而提高数据写入效率。然而，change buffer 不能用于唯一索引，也不适合在高一致性需求的场景中使用。
- 最佳实践：在大批量数据导入、批量修改操作时，充分利用 change buffer 可获得显著性能提升。
redo log 的使用场景

redo log 适用于所有事务场景，特别是在系统崩溃后需要恢复数据的情况下。redo log 在每次事务提交时记录变更操作，即便系统崩溃，InnoDB 也能利用 redo log 重做未完成的事务，保障数据一致性。
- 最佳实践：在金融、银行等数据一致性要求严格的系统中，通过调优 redo log 的大小和刷新频率，可以显著提高事务的持久性和恢复能力。

5.6 小结：change buffer 和 redo log 的选择与组合

change buffer 更适合普通索引的高频写入场景，通过延迟写入和批量合并减少 I/O 操作，优化写性能。
redo log 适合所有事务场景，尤其是在需要崩溃恢复的系统中，通过日志记录提供数据一致性保障。

在实际应用中，change buffer 和 redo log 经常结合使用：change buffer 优化普通索引的写性能，而 redo log 则确保数据一致性和崩溃恢复，两者互相补充，为系统提供更高的写入效率和可靠性。

第六章：总结与索引选择建议

6.1 普通索引和唯一索引的对比总结

在 MySQL 的 InnoDB 存储引擎中，普通索引和唯一索引在存储结构、更新逻辑、查询效率、以及底层原理上都有明显的区别，适用于不同的业务场景。以下是普通索引和唯一索引的关键特点对比：

普通索引：
- 允许重复值：适合数据中包含大量重复值的情况，例如某些分类字段。
- 适用于批量更新：可以利用 change buffer 延迟写入，优化高频写入操作的性能。
- 使用场景：在不需要严格唯一性约束的情况下，普通索引能够提供更灵活的查询和更新性能，适合例如产品分类、地区划分等场景。
唯一索引：
- 保证数据唯一性：每个索引项的值必须唯一，适合确保数据完整性的场景。
- 直接写入磁盘：由于无法使用 change buffer，唯一索引会在写入时直接进行唯一性检查，导致写性能略低。
- 使用场景：唯一索引适用于需要确保数据唯一性的场景，例如用户ID、邮箱等业务逻辑上需要严格保证唯一性的字段。

6.2 change buffer 和 redo log 的对比总结

在系统优化中，change buffer 和 redo log 都是用于减少 I/O 操作、提升性能的关键技术，二者在工作原理和应用场景上互为补充：

change buffer：仅限于普通索引，可以减少更新频率并批量写入磁盘，适用于写多读少的应用场景。
redo log：用于记录所有事务变更操作，提供崩溃恢复保障，适用于需要数据一致性的所有场景。

6.3 索引选择的最佳实践与建议

根据业务需求合理选择索引类型和优化技术，可以显著提升系统性能。以下是一些选择建议：

根据唯一性要求选择索引类型
- 如果字段需要唯一性保障（如用户ID），应使用唯一索引。
- 如果不需要唯一性且包含大量重复数据（如分类字段），普通索引是更好的选择。
考虑更新频率和查询场景
- 对于频繁更新的普通索引字段，InnoDB 的 change buffer 可以大幅减少 I/O 操作，从而提高系统性能。
- 对于高一致性场景或频繁查询的字段，唯一索引可以确保数据完整性，但应避免频繁更新。
充分利用缓存和日志
- 利用 Buffer Pool 缓存热点数据，减少磁盘访问。
- 调整 redo log 文件大小和刷新策略，以平衡写入性能和持久化需求。
分布式架构中的索引选择
- 在高并发、大数据量的分布式场景中，可通过分表或分库策略，结合普通索引和唯一索引，实现更佳的读写性能。

6.4 小结

本文详细分析了 MySQL 中普通索引和唯一索引的结构、查询与更新效率、底层原理、以及 change buffer 和 redo log 的优化机制。通过合理选择索引类型和优化技术，数据库设计者可以在数据完整性和性能之间找到最佳平衡，提升系统的整体效率。

关注我

标签：log,buffer,写入,更新,索引,change
From： https://blog.csdn.net/tatasix/article/details/143711808