mysql 问题解答 2

3 存储

3.1 存储引擎

3、InnoDB 的四大特性?

InnoDB 是 MySQL 数据库中最常用的存储引擎之一，它的四大特性通常指的是：

1. ACID 兼容性:

   • 原子性 (Atomicity): 保证事务内的操作要么全部成功，要么全部失败，不会出现中间状态。例如，银行转账操作，从一个账户向另一个账户转钱，这两个操作要么同时成功，要么同时失败。
   • 一致性 (Consistency): 确保数据库从一个一致的状态转换到另一个一致的状态。例如，数据库中有一个规则是所有的账户余额都不得为负，在进行任何事务之后，这个规则都必须被遵守。
   • 隔离性 (Isolation): 事务的隔离性指多个事务并发执行时，一个事务的操作不会影响其他事务。例如，当两个银行存款事务并行执行时，每个事务都应该看不到对方的中间状态。
   • 持久性 (Durability): 一旦事务提交，则其所做的更改将永久保存在数据库中，即使发生系统故障也不会丢失。例如，即使在事务写入数据后数据库立即崩溃，重新启动后，那些更改仍然存在。

2. 支持事务:

   • InnoDB 提供了完整的事务支持，遵循上述 ACID 原则。例如，你可以在一个事务中执行多个更新操作，并且可以选择提交（commit）这个事务使所有更改生效，或者回滚（rollback）事务以撤销所有更改。

3. 行级锁定和外键约束:

   • 行级锁定: InnoDB 支持行级锁定，这意味着在执行读写操作时，它锁定的是数据行而不是整个数据表。这种细粒度的锁定机制可以大幅度提升并发处理能力。例如，在处理一个订单表时，同时有多个用户操作不同的订单，行级锁定可以使他们互不干扰。
   • 外键约束: InnoDB 支持外键，确保了数据的完整性。例如，一个订单详情表可以通过外键约束关联到订单表，以确保每个订单详情都对应一个有效的订单。

4. 崩溃恢复能力:

   • InnoDB 通过日志文件（如重做日志，即 redo log）来保证在崩溃后能够恢复数据。当系统崩溃时，InnoDB 可以利用这些日志来重建它在崩溃前的状态，确保数据不会丢失。

InnoDB 的这些特性使它成为了处理大量数据和高并发事务的首选存储引擎。

4、InnoDB 为何推荐使用自增主键？

自增主键是一种特殊的数据库表列，用于在新行插入表时自动产生一个唯一的标识符。自增字段通常是整数类型，每当新记录被插入时，这个数值会自动递增。

举例说明

假设有一个学生信息表 students，包含以下字段：student_id（学生ID）、name（姓名）和 age（年龄）。如果将 student_id 设为自增主键，那么每当新学生信息被插入到表中时，student_id 会自动设置为上一个 student_id 值加1。

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

插入新的学生信息时无需指定 student_id：

INSERT INTO students (name, age) VALUES ('Alice', 20);
INSERT INTO students (name, age) VALUES ('Bob', 22);

在这两条记录插入后，数据库会自动为 Alice 分配 student_id 1，为 Bob 分配 student_id 2。

InnoDB 推荐使用自增主键的原因

InnoDB 存储引擎在处理数据时有其特定的方式，推荐使用自增主键的原因包括：

1. 插入性能:

   • 当新行插入时，自增主键确保了新的值是当前最大值加一，这意味着新记录总是添加到索引的末尾。这样，InnoDB 就可以避免大量的页面分裂操作，因为不需要在索引中频繁地为新行寻找位置。

2. B-Tree 索引结构:

   • InnoDB 使用 B-Tree 数据结构来存储索引。当提到 InnoDB 使用 B-Tree 来存储索引时，实际上指的是 B+树。如果主键是随机分配的，插入新的记录可能会导致 B-Tree 的重新平衡，影响性能。自增主键保证了索引的顺序增长，减少了这种重建和维护的需要。

3. 减少页分裂和碎片:

   • 使用自增主键可以减少数据库索引页的分裂，因为新记录总是在最后添加，这有助于减少空间和性能上的碎片化。

4. 聚簇索引:

   • InnoDB 的表是基于主键顺序存储的，也称为聚簇索引。这意味着表数据实际上存储在主键索引的叶子节点上。拥有自增主键可以使得行数据物理上的存储是有序的，这样对主键的顺序访问会非常高效。

InnoDB 的聚簇索引是一种特殊的数据存储方式，在这种方式中，表中的数据实际上存储在索引的叶节点上。这与非聚簇索引（数据和索引分开存储）形成对比。让我用一个简单的比喻来解释聚簇索引：

想象一下，你有一本按照故事发生时间顺序排列的照片相册。每一页相册都有一个时间标签（索引