MySQL - 为什么InnoDB选择B+树索引？Change buffer？

Love The Way You Lie 2024-02-21 10:31 45阅读 0赞

#### 为什么InnoDB选择B+树索引 ####

1.  **性能需求和查询场景**：InnoDB通常需要在各种查询场景下提供卓越的性能。B+树索引非常适合满足这些需求，尤其在支持范围查询和排序等复杂操作时表现出色。
2.  **磁盘和内存效率**：将数据从磁盘加载到内存需要耗费大量时间，因此磁盘I/O效率至关重要。以下是更详细的考虑因素：
    
     *  **哈希索引局限**：虽然哈希索引可以提供O(1)复杂度的查询，但它无法很好地支持范围查询和排序，这可能导致必须进行全表扫描。
     *  **B树的局限**：B树可以在非叶子节点存储数据，但在查询连续数据时可能引入更多的随机I/O。
     *  **B+树的优势**：B+树的所有叶子节点都通过指针相互连接，减少了顺序遍历时可能出现的随机I/O。

#### **普通索引和唯一索引的选择** ####

唯一索引无法利用"Change buffer"的优化机制。
    	因此，从性能的角度出发，如果业务允许，建议优先考虑使用非唯一索引。

##### 什么是"Change buffer"？ #####

“Change buffer” 是 InnoDB 存储引擎中的一项重要性能优化机制，它主要用于减少磁盘 I/O 操作的次数，从而提高数据库的写入性能。具体来说，“Change buffer” 的主要作用如下：

1.  **延迟索引更新**：当有写操作（如插入、更新、删除）需要修改B+树索引结构时，InnoDB不会立即将这些修改写入磁盘的索引页。相反，它将这些修改操作记录在"Change buffer" 中，同时在内存中维护一份待更新的索引树。
2.  **减少随机磁盘写入**：通过将索引修改操作暂时保存在"Change buffer"中，InnoDB可以将随机的磁盘写入操作转换为更集中的顺序写入操作。这是因为多个索引修改操作可以在内存中聚合，并一次性写入磁盘，减少了随机I/O操作，提高了写入性能。
3.  **提高并发性能**：由于修改操作首先被记录在"Change buffer"中，事务可以更快地提交，而不必等待磁盘 I/O 完成。这提高了数据库的并发性能，因为多个事务可以并行执行而不会相互阻塞。
4.  **在恢复时加速**：在数据库崩溃后，InnoDB可以使用"Change buffer"来快速重放未写入磁盘的索引修改操作，从而缩短数据库恢复的时间。

需要注意的是，"Change buffer"适用于非唯一索引，因为唯一索引必须在插入新数据行时立即更新。这一机制有助于提高数据库的写入性能，特别是对于大量的插入、更新和删除操作，它可以显著减少随机磁盘写入的负担，从而改善数据库的性能和并发能力。

###### 那主键索引、联合索引会利用吗？ ######

1.  **主键索引**：主键索引是表的主要索引，它通常不会利用"Change buffer"。主键索引的唯一性要求使得任何对主键的插入、更新或删除操作必须立即更新索引，以确保数据的一致性和唯一性。
2.  **联合索引**：联合索引也不会受益于"Change buffer"。联合索引是指建立在多个列上的索引，与主键索引一样，它们也要求立即维护索引的一致性，因此不会使用"Change buffer"来延迟索引更新。