Skip to content

Latest commit

 

History

History
273 lines (164 loc) · 24 KB

14.1_innodb-introduction.md

File metadata and controls

273 lines (164 loc) · 24 KB
Raw

14.1 Introduction to InnoDB

14.1 InnoDB简介

[TOC]

InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.7, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.

InnoDB 是一款通用的存储引擎, 同时具备高可靠性和高性能特征。 在MySQL 5.7中, InnoDB是默认的存储引擎。 不带 ENGINE= 子句的 CREATE TABLE 语句, 默认创建的都是 InnoDB 表, 除非DBA修改了默认存储引擎的配置。

Key Advantages of InnoDB

InnoDB的主要优势

  • InnoDB 遵循 ACID 标准模型, 支持事务(transactions), 具有提交(commit), 回滚(rollback) 功能和保护用户数据的崩溃恢复(crash-recovery)机制。 关于ACID的更多信息请参考 14.2 InnoDB和ACID模型。
  • 支持行级锁(Row-level locking), 以及类似于Oracle的 一致性读(consistent reads), 提高了多用户环境下的并发性能。 更多信息可参考 14.7 InnoDB的锁和事务模型。
  • InnoDB表将数据存储到在磁盘上, 基于主键进行查询优化。 每个InnoDB表都有一个主键索引(primary key index), 称为聚集索引(clustered index), 聚集索引可以有效降低主键查找需要的磁盘IO。 更多信息请参考 Section 14.6.2.1, “Clustered and Secondary Indexes”。
  • 为了维持数据完整性(integrity), InnoDB支持外键约束(FOREIGN KEY)。 通过外键对 inserts, updates, 以及 deletes 操作进行检查, 以确保不同表之间的数据不会因为这些操作造成不一致。 更多信息请参考 Section 13.1.18.5, “FOREIGN KEY Constraints”。

Table 14.1 InnoDB Storage Engine Features

Feature Support
B-tree indexes Yes
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.) Yes
Cluster database support No
Clustered indexes Yes
Compressed data Yes
Data caches Yes
Encrypted data Yes (Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest tablespace encryption is supported.)
Foreign key support Yes
Full-text search indexes Yes (InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.)
Geospatial data type support Yes
Geospatial indexing support Yes (InnoDB support for geospatial indexing is available in MySQL 5.7 and later.)
Hash indexes No (InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.)
Index caches Yes
Locking granularity Row
MVCC Yes
Replication support (Implemented in the server, rather than in the storage engine.) Yes
Storage limits 64TB
T-tree indexes No
Transactions Yes
Update statistics for data dictionary Yes

To compare the features of InnoDB with other storage engines provided with MySQL, see the Storage Engine Features table in Chapter 15, Alternative Storage Engines.

要比对 InnoDB 与其他存储引擎的功能特征, 请参阅Chapter 15, Alternative Storage Engines。

InnoDB Enhancements and New Features

For information about InnoDB enhancements and new features, refer to:

InnoDB增强功能和新特性

有关InnoDB增强功能和新特性的信息, 请参阅:

Additional InnoDB Information and Resources

其他InnoDB相关的资源和信息

14.1.1 Benefits of Using InnoDB Tables

You may find InnoDB tables beneficial for the following reasons:

14.1.1 使用InnoDB表的好处

我们会发现使用InnoDB表有这些好处:

  • If your 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 any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.

  • 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.

  • If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and the bad data gets kicked out automatically.

  • If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.

  • When you design your 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.

  • 如果服务器由于硬件或者软件问题而意外崩溃, 不管当时数据库正在做什么操作, 重启数据库之后都不需要执行任何人工操作。 InnoDB 的崩溃恢复机制将自动完成崩溃前已提交的所有更改, 并撤消所有正在处理但尚未提交的更改。只需重新启动, 然后从上次中断的地方继续即可。

  • InnoDB存储引擎维护自己的缓冲池(buffer pool), 在访问数据时将表和索引数据缓存到内存中。经常使用的数据直接从内存中获取。 缓存适用于多种类型的信息, 可以加快处理速度。 在专用数据库服务器上(dedicated database servers), 最多可以将 80％ 的物理内存分配给缓冲池。

  • 如果将相关数据拆分到不同的表中, 可以设置外键来强制保证引用完整性(referential integrity)。更新或删除主表数据时, 会自动更新或删除其他表中相关的数据。 尝试插入子表数据时, 如果在主表中没有相应的数据, 那么无效数据会自动失败。

  • 如果磁盘或内存中的数据被破坏, 则在使用前, 校验和机制(checksum)会提醒我们注意虚假数据。

  • 为每个表设计合适的 主键 之后, 涉及这些列的操作会自动进行优化。 在 WHERE 条件, ORDER BY, GROUP BY 子句以及 join 操作中的主键列操作都特别快。

  • 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.

  • Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.

  • You can compress tables and associated indexes.

  • You can create and drop indexes with much less impact on performance and availability.

  • Truncating a file-per-table tablespace is very fast, and can free up disk space for the operating system to reuse, rather than freeing up space within the system tablespace that only InnoDB can reuse.

  • insert, update, 以及 delete 操作都通过 更改缓冲(change buffering) 的机制进行了自动优化。 InnoDB不仅支持对同一个表进行并发读写, 而且还将更改的数据缓存起来以简化磁盘I/O。

  • 不仅长时间运行的巨型表查询性能会得到优化。 当从表中多次访问同一行时, 自适应哈希索引(Adaptive Hash Index)特性会让这些查询变得更快, 就像是从哈希表中取出来的一样。

  • 可以压缩表和关联的索引。

  • 创建和删除索引时, 对性能和可用性的影响要小得多。

  • 对于 file-per-table 表空间的截断非常快, 并且可以释放磁盘空间, 而不是只释放InnoDB中的 system tablespace 空间。

  • The storage layout for table data is more efficient for BLOB and long text fields, with the DYNAMIC row format.

  • You can monitor the internal workings of the storage engine by querying INFORMATION_SCHEMA tables.

  • You can monitor the performance details of the storage engine by querying Performance Schema tables.

  • You can freely 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 from InnoDB and MEMORY tables in a single query.

  • InnoDB has been designed for CPU efficiency and maximum performance when processing large data volumes.

  • InnoDB tables can handle large quantities of data, even on operating systems where file size is limited to 2GB.

  • 使用 DYNAMIC 行格式, 对于 BLOB 和 long text 数据的存储布局会更加高效。

  • 可以通过 INFORMATION_SCHEMA 中的表来监控存储引擎的内部状况。

  • 可以通过 Performance Schema 中的表来监控存储引擎性能的详细信息。

  • 即使在同一条SQL语句中, 也可以将InnoDB表与其他MySQL存储引擎的表自由组合。 例如, 可以在一个查询中使用 join 来关联查询 InnoDB和MEMORY表中的数据。

  • InnoDB专门为CPU使用效率和最佳性能而设计, 能完美应对大规模的数据。

  • InnoDB表支持很大的数据量, 即使在单个文件限制为2GB的操作系统中也不受影响。

For InnoDB-specific tuning techniques you can apply in your application code, see Section 8.5, “Optimizing for InnoDB Tables”.

在应用程序中进行InnoDB调优的技术, 请参考 Section 8.5, “Optimizing for InnoDB Tables”。

14.1.2 Best Practices for InnoDB Tables

This section describes best practices when using InnoDB tables.

14.1.2 高性能InnoDB最佳实践

下面通过最佳实践来介绍怎么使用InnoDB。

  • Specifying a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there is no obvious primary key.

  • Using joins wherever data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same data type in each table. Adding foreign keys ensures that referenced columns are indexed, which can improve performance. Foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.

  • Turning off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).

  • Grouping sets of related DML operations into transactions, by bracketing them with START TRANSACTION and COMMIT statements. While you don't want to commit too often, you also don't want to issue huge batches of INSERT, UPDATE, or DELETE statements that run for hours without committing.

  • 为每个表设置主键(primary key), 一般是查询最常使用的一列或多列, 如果没有很明显的业务主键, 则使用自增ID(auto-increment)。 【最佳设计原则: 主键无意义, 或者说在生成之前无具体的业务含义, 这样能最大限度避免修改。】

  • 使用 join 从多个表抽取数据时, 基于主键ID值进行关联。 为了提高连接性能, 可在 join 列上定义外键, 这些列在每个表中的数据类型都保持一致。 添加外键可确保对被引用的列添加索引, 从而提高性能。 外键还会将删除或更新传播到所有受影响的表, 如果父表中不存在相应的ID, 则可以防止在子表中插入数据。 【最佳设计原则: 当前基于Web应用的开发年代, 不要使用外键, 那是以数据库为中心的上一代程序的设计方式。】

  • 如果每次业务操作都涉及大量的数据修改, 请关闭 autocommit。 受存储设备的写入速度限制，可能一秒钟只支持几百次提交。

  • 将相关的DML操作归集为“事务”, 放在 START TRANSACTION 和 COMMIT 中间。 需要掌握好每个批次的量, 我们不想频繁提交, 但也不能让一次提交需要耗费很长时间, 对吧。

  • Not using LOCK TABLES statements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacrificing reliability or high performance. To get exclusive write access to a set of rows, use the SELECT ... FOR UPDATE syntax to lock just the rows you intend to update.

  • Enabling the innodb_file_per_table option or using general tablespaces to put the data and indexes for tables into separate files, instead of the system tablespace.

    The innodb_file_per_table option is enabled by default.

  • Evaluating whether your data and access patterns benefit from the InnoDB table or page compression features. You can compress InnoDB tables without sacrificing read/write capability.

  • Running your server with the option --sql_mode=NO_ENGINE_SUBSTITUTION to prevent tables being created with a different storage engine if there is an issue with the engine specified in the ENGINE= clause of CREATE TABLE.

  • 禁止使用 LOCK TABLES 语句。 InnoDB可以一次处理多个会话, 一次读写同一张表, 而不会牺牲可靠性或高性能。要获得对一组行的排他写访问权限, 请使用[SELECT ... FOR UPDATE]（https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html）语法仅锁定要更新的行。

  • 启用innodb_file_per_table选项或者使用常规表空间, 将表数据和索引存放到单独的文件中, 而不要放到 system tablespace 中。

    默认innodb_file_per_table选项是启用的。

  • 评估数据和访问模式是否受益于InnoDB表或页压缩功能。 我们可以在不损害读/写性能的情况下压缩InnoDB表。

  • 如果 CREATE TABLE 的 ENGINE= 子句中指定的引擎存在问题, 则使用选项 --sql_mode=NO_ENGINE_SUBSTITUTION 来启动MySQL服务器, 阻止用其他存储引擎来创建表。

14.1.3 Verifying that InnoDB is the Default Storage Engine

Issue the SHOW ENGINES statement to view the available MySQL storage engines. Look for DEFAULT in the InnoDB line.

14.1.3 检查InnoDB是不是默认存储引擎

使用 SHOW ENGINES 语句可以查看MySQL支持的存储引擎。 我们需要关注 InnoDB 对应的行中是否有 DEFAULT 这个值。

mysql> SHOW ENGINES;

Alternatively, query the INFORMATION_SCHEMA.ENGINES table.

或者查询 INFORMATION_SCHEMA.ENGINES表。

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;

结果都是类似的。

14.1.4 Testing and Benchmarking with InnoDB

If InnoDB is not your default storage engine, you can determine if your database server or applications work correctly with InnoDB by restarting the server with --default-storage-engine=InnoDB defined on the command line or with default-storage-engine=innodb defined in the mysqld section of your MySQL server option file.

Since changing the default storage engine only affects new tables as they are created, run all your application installation and setup steps to confirm that everything installs properly. Then exercise all the application features to make sure all the data loading, editing, and querying features work. If a table relies on a feature that is specific to another storage engine, you receive an error; add the ENGINE=<other_engine_name> clause to the CREATE TABLE statement to avoid the error.

If you did not make a deliberate decision about the storage engine, and you want to preview how certain tables work when created using InnoDB, issue the command ALTER TABLE table_name ENGINE=InnoDB; for each table. Or, to run test queries and other statements without disturbing the original table, make a copy:

14.1.4 InnoDB测试和基准测试

如果InnoDB不是默认存储引擎, 则可以先在测试或者开发环境下,通过命令行参数 --default-storage-engine=InnoDB, 或者在MySQL配置文件 mysqld 一节中定义 default-storage-engine=innodb , 并重启MySQL服务器, 以确定 InnoDB 是否和数据库或应用系统兼容。

由于更改默认存储引擎只会影响新创建的表, 所以需要从头开始进行回归测试, 覆盖所有的系统功能, 包括数据加载, 编辑和查询功能都正常。 如果表依赖于某个特定存储引擎的功能特征, 则会产生错误信息; 这时候将 ENGINE=<other_engine_name> 子句加到CREATE TABLE 语句中即可。

如果不确定使用哪个存储引擎, 并且想看看 InnoDB 表的工作方式, 可以使用 ALTER TABLE table_name ENGINE=InnoDB; 语句。 或者, 在不干扰原始表的情况下, 复制一个表出来进行测试:

CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM other_engine_table;

To assess performance with a full application under a realistic workload, install the latest MySQL server and run benchmarks.

Test the full application lifecycle, from installation, through heavy usage, and server restart. Kill the server process while the database is busy to simulate a power failure, and verify that the data is recovered successfully when you restart the server.

Test any replication configurations, especially if you use different MySQL versions and options on the source server and replicas.

要在实际工作负载下评估整个应用系统的性能, 请安装最新版的MySQL服务器并执行基准测试。

测试整个应用程序生命周期, 包括安装, 重型负载、以及服务器重启。 在数据库繁忙时模拟电源故障, 强制杀掉服务器进程, 并在重启服务器后验证数据是否成功恢复。

测试所有的主从复制配置, 尤其是源服务器和副本服务器使用了不同的MySQL版本和配置的情况下。

14.1.5 Turning Off InnoDB

Oracle recommends InnoDB as the preferred storage engine for typical database applications, from single-user wikis and blogs running on a local system, to high-end applications pushing the limits of performance. In MySQL 5.7, InnoDB is the default storage engine for new tables.

Important

InnoDB cannot be disabled. The --skip-innodb option is deprecated and has no effect, and its use results in a warning. Expect it to be removed in a future MySQL release. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth).

14.1.5 无法禁用InnoDB引擎

Oracle公司建议将InnoDB作为首选存储引擎, 包括本机运行的单用户Wiki和博客, 到需要极限性能的高端应用系统。 在 MySQL 5.7 中, InnoDB是新建表时的默认存储引擎。

重要提示

InnoDB 无法被禁用。 --skip-innodb选项已被弃用, 并且指定了也没有效果, 还会产生警告。 这个选项在将来的MySQL版本中预期会被删除。 这同样适用于同义词(--innodb=OFF, --disable-innodb, 等等)。

相关链接