InnoDB
tables have the following benefits:
If the server unexpectedly exits because of a hardware or software issue, regardless of what was happening in the database at the time, you don't need to do anything special after restarting the database.
InnoDB
crash recovery automatically finalizes changes that were committed before the time of the crash, and undoes changes that were in process but not committed, permitting you to restart and continue from where you left off. See Section 17.18.2, “InnoDB Recovery”.
当服务器因硬件或软件问题意外终止时,无论数据库当时正在进行何种操作,重启后都无需执行任何特殊操作。InnoDB 崩溃恢复机制会自动完成崩溃前已提交的更改,并撤销进行中但未提交的更改,使您能够从中断点继续运行。详见第 17.18.2 节"InnoDB 恢复机制"。The
InnoDB
storage engine maintains its own buffer pool that caches table and index data in main memory as data is accessed. Frequently used data is processed directly from memory. This cache applies to many types of information and speeds up processing. On dedicated database servers, up to 80% of physical memory is often assigned to the buffer pool. See Section 17.5.1, “Buffer Pool”.
InnoDB 存储引擎通过自主管理的缓冲池,在内存中缓存表和索引数据。频繁访问的数据可直接从内存处理,这种缓存机制适用于多种信息类型并能显著提升处理速度。在专用数据库服务器上,通常会将高达 80%的物理内存分配给缓冲池。详见第 17.5.1 节"缓冲池"。If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. See Section 15.1.20.5, “FOREIGN KEY Constraints”.
若将关联数据拆分至不同表,可设置外键以强制引用完整性。详见第 15.1.20.5 节"外键约束"。If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it. The
innodb_checksum_algorithm
variable defines the checksum algorithm used byInnoDB
.
当磁盘或内存中的数据损坏时,校验和机制会在使用前发出伪数据警报。innodb_checksum_algorithm
变量定义了InnoDB
使用的校验算法。When you design a database with appropriate primary key columns for each table, operations involving those columns are automatically optimized. It is very fast to reference the primary key columns in
WHERE
clauses,ORDER BY
clauses,GROUP BY
clauses, and join operations. See Section 17.6.2.1, “Clustered and Secondary Indexes”.
为表设计合适的主键列时,涉及这些列的操作将自动优化。在WHERE
子句、ORDER BY
子句、GROUP BY
子句及连接操作中引用主键列速度极快。参见第 17.6.2.1 节"聚簇索引与二级索引"。Inserts, updates, and deletes are optimized by an automatic mechanism called change buffering.
InnoDB
not only allows concurrent read and write access to the same table, it caches changed data to streamline disk I/O. See Section 17.5.2, “Change Buffer”.
通过称为变更缓冲的自动机制优化增删改操作。InnoDB
不仅支持对同一表的并发读写访问,还会缓存变更数据以优化磁盘 I/O。详见第 17.5.2 节"变更缓冲"。Performance benefits are not limited to large tables with long-running queries. When the same rows are accessed over and over from a table, the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table. See Section 17.5.3, “Adaptive Hash Index”.
性能优势不仅限于处理大型表和长时间运行的查询。当表中相同行被反复访问时,自适应哈希索引会接管这些查找操作,使其速度更快,就像从哈希表中直接获取一样。参见第 17.5.3 节"自适应哈希索引"。You can compress tables and associated indexes. See Section 17.9, “InnoDB Table and Page Compression”.
您可以压缩表及其关联索引。参见第 17.9 节"InnoDB 表和页压缩"。You can encrypt your data. See Section 17.13, “InnoDB Data-at-Rest Encryption”.
您可以对数据进行加密。参见第 17.13 节"InnoDB 静态数据加密"。You can create and drop indexes and perform other DDL operations with much less impact on performance and availability. See Section 17.12.1, “Online DDL Operations”.
您可以创建和删除索引,并执行其他 DDL 操作,同时对性能和可用性的影响要小得多。参见第 17.12.1 节"在线 DDL 操作"。Truncating a file-per-table tablespace is very fast and can free up disk space for the operating system to reuse rather than only
InnoDB
. See Section 17.6.3.2, “File-Per-Table Tablespaces”.
截断独立表空间文件的速度极快,并且能释放磁盘空间供操作系统直接重用,而非仅InnoDB
。参见第 17.6.3.2 节"独立表空间"。The storage layout for table data is more efficient for
BLOB
and long text fields, with theDYNAMIC
row format. See Section 17.10, “InnoDB Row Formats”.
采用DYNAMIC
行格式时,表数据存储布局对BLOB
和长文本字段更为高效。参见第 17.10 节"InnoDB 行格式"。You can monitor the internal workings of the storage engine by querying
INFORMATION_SCHEMA
tables. See Section 17.15, “InnoDB INFORMATION_SCHEMA Tables”.
通过查询INFORMATION_SCHEMA
表可监控存储引擎的内部运作。参见第 17.15 节"InnoDB INFORMATION_SCHEMA 表"。You can monitor the performance details of the storage engine by querying Performance Schema tables. See Section 17.16, “InnoDB Integration with MySQL Performance Schema”.
通过查询 Performance Schema 表可监控存储引擎的性能细节。参见第 17.16 节"InnoDB 与 MySQL 性能模式的集成"。You can mix
InnoDB
tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data fromInnoDB
andMEMORY
tables in a single query.
您可以在同一语句中混用InnoDB
表与其他 MySQL 存储引擎的表。例如,可以通过连接操作在单个查询中组合InnoDB
和MEMORY
表的数据。InnoDB
has been designed for CPU efficiency and maximum performance when processing large data volumes.
InnoDB
的设计旨在处理海量数据时实现 CPU 高效利用和极致性能。InnoDB
tables can handle large quantities of data, even on operating systems where file size is limited to 2GB.
InnoDB
表能够处理超大规模数据,即使在文件大小限制为 2GB 的操作系统上也不例外。
For InnoDB
-specific tuning techniques you can
apply to your MySQL server and application code, see
Section 10.5, “Optimizing for InnoDB Tables”.
关于可应用于 MySQL 服务器和应用程序代码的 InnoDB
专属调优技术,请参阅第 10.5 节《InnoDB 表优化》。