ACID 模型是一组数据库设计原则,强调对业务数据和关键任务应用程序重要的可靠性方面。MySQL 包括如 InnoDB 存储引擎等组件,这些组件严格遵循 ACID 模型,以确保数据不被破坏,并且结果不会因软件崩溃或硬件故障等异常情况而失真。当你依赖符合 ACID 的功能时,无需重新发明一致性检查和崩溃恢复机制。在你拥有额外的软件安全措施、超可靠硬件或应用程序可以容忍少量数据丢失或不一致的情况下,你可以调整 MySQL 设置,以牺牲部分 ACID 可靠性来换取更高的性能或吞吐量。The ACID model is a set of database
design principles that emphasize aspects of reliability that are
important for business data and mission-critical applications. MySQL
includes components such as the InnoDB storage
engine that adhere closely to the ACID model so that data is not
corrupted and results are not distorted by exceptional conditions
such as software crashes and hardware malfunctions. When you rely on
ACID-compliant features, you do not need to reinvent the wheel of
consistency checking and crash recovery mechanisms. In cases where
you have additional software safeguards, ultra-reliable hardware, or
an application that can tolerate a small amount of data loss or
inconsistency, you can adjust MySQL settings to trade some of the
ACID reliability for greater performance or throughput.
下面的部分讨论了 MySQL 功能,特别是 InnoDB 存储引擎,如何与 ACID 模型的各个类别交互:The following sections discuss how MySQL features, in particular the
InnoDB storage engine, interact with the
categories of the ACID model:
A:原子性。A: atomicity.
C:一致性。C: consistency.
I::隔离性。I:: isolation.
D:持久性。D: durability.
原子性Atomicity
ACID 模型的 原子性 方面主要涉及 InnoDB 事务。相关的 MySQL 功能包括:The atomicity aspect of the ACID
model mainly involves InnoDB
transactions. Related MySQL
features include:
autocommit设置。Theautocommitsetting.
一致性Consistency
ACID 模型的 一致性 方面主要涉及内部 InnoDB 处理,以保护数据免受崩溃影响。相关的 MySQL 功能包括:The consistency aspect of the ACID
model mainly involves internal InnoDB processing
to protect data from crashes. Related MySQL features include:
InnoDB双写缓冲区。参见 第 17.6.4 节,“双写缓冲区”。TheInnoDBdoublewrite buffer. See Section 17.6.4, “Doublewrite Buffer”.InnoDB崩溃恢复。参见 InnoDB 崩溃恢复。InnoDBcrash recovery. See InnoDB Crash Recovery.
隔离性Isolation
ACID 模型的 隔离性 方面主要涉及 InnoDB 事务,特别是适用于每个事务的 隔离级别。相关的 MySQL 功能包括:The isolation aspect of the ACID
model mainly involves InnoDB
transactions, in particular
the isolation level that
applies to each transaction. Related MySQL features include:
autocommit设置。Theautocommitsetting.事务隔离级别和
SET TRANSACTION语句。参见 第 17.7.2.1 节,“事务隔离级别”。Transaction isolation levels and theSET TRANSACTIONstatement. See Section 17.7.2.1, “Transaction Isolation Levels”.InnoDB锁定 的底层细节。详情可以在INFORMATION_SCHEMA表中查看(见 第 17.15.2 节,“InnoDB INFORMATION_SCHEMA 事务和锁定信息”)以及 Performance Schema 的data_locks和data_lock_waits表。The low-level details ofInnoDBlocking. Details can be viewed in theINFORMATION_SCHEMAtables (see Section 17.15.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”) and Performance Schemadata_locksanddata_lock_waitstables.
持久性Durability
ACID 模型中的 持久性 方面涉及 MySQL 软件功能与您的特定硬件配置的交互。由于这取决于 CPU、网络和存储设备的能力存在多种可能性,这一方面是最难提供具体指导的。(而这些指导可能以 “购买新硬件” 的形式出现。)相关的 MySQL 功能包括:The durability aspect of the ACID model involves MySQL software features interacting with your particular hardware configuration. Because of the many possibilities depending on the capabilities of your CPU, network, and storage devices, this aspect is the most complicated to provide concrete guidelines for. (And those guidelines might take the form of “buy new hardware”.) Related MySQL features include:
InnoDB双写缓冲区。参见 第 17.6.4 节,“双写缓冲区”。TheInnoDBdoublewrite buffer. See Section 17.6.4, “Doublewrite Buffer”.innodb_flush_log_at_trx_commit变量。Theinnodb_flush_log_at_trx_commitvariable.sync_binlog变量。Thesync_binlogvariable.innodb_file_per_table变量。Theinnodb_file_per_tablevariable.存储设备中的写入缓冲区,例如磁盘驱动器、SSD 或 RAID 阵列。The write buffer in a storage device, such as a disk drive, SSD, or RAID array.
存储设备中的电池备份缓存。A battery-backed cache in a storage device.
用于运行 MySQL 的操作系统,特别是其对
fsync()系统调用的支持。The operating system used to run MySQL, in particular its support for thefsync()system call.用于保护运行 MySQL 服务器并存储 MySQL 数据的所有计算机服务器和存储设备的电源的不间断电源(UPS)。An uninterruptible power supply (UPS) protecting the electrical power to all computer servers and storage devices that run MySQL servers and store MySQL data.
您的备份策略,例如备份的频率和类型,以及备份保留期限。Your backup strategy, such as frequency and types of backups, and backup retention periods.
对于分布式或托管的数据应用程序,MySQL 服务器硬件所在数据中心的特定特性,以及数据中心之间的网络连接。For distributed or hosted data applications, the particular characteristics of the data centers where the hardware for the MySQL servers is located, and network connections between the data centers.