InnoDB：简介

来自Wikioe
跳到导航 跳到搜索


关于 InnoDB

InnoDB 是通用存储引擎，可兼顾高可靠性和高性能。

  • 在 MySQL 5.7 中，InnoDB 是默认的 MySQL 存储引擎。除非您配置了其他默认存储引擎，否则发出不带“ENGINE=”子句的“CREATE TABLE”语句将创建InnoDB表。


InnoDB 的主要优势：

  1. 其 DML 操作遵循ACID模型，其中事务具有“commit”，“rollback”和“crash-recovery”【？？？】功能来保护用户数据。【请参见“InnoDB 和 ACID 模型”节】
  2. 行级锁和 Oracle 风格“consistent reads”提高了多用户并发性和性能。【请参见“InnoDB 锁和事务模型”节】
  3. InnoDB 表将数据安排在磁盘上，以基于“primary keys”优化查询。每个 InnoDB表都有一个称为“clustered index”的主键索引，该索引对数据进行组织以最大程度地减少主键查找的 I/O。【请参见“群集索引和二级索引”节】
  4. 为了维护数据完整性，InnoDB 支持“FOREIGN KEY”约束。使用外键检查插入，更新和删除操作，以确保它们不会导致不同 table 之间的不一致。【请参见“外键约束”节】


InnoDB 存储引擎功能：

Feature Support
B-tree indexes Yes
备份/时间点恢复 （在服务器中而不是在存储引擎中实现.） Yes
集群数据库支持 No【？？？那是用 NDB ？？？】
Clustered indexes（聚集索引） Yes
Compressed data Yes
Data caches Yes
Encrypted data 是（通过加密功能在服务器中实现；在 MySQL 5.7 和更高版本中，支持静态数据表空间加密）
外键支持 Yes
全文搜索索引 Yes（MySQL 5.6 和更高版本提供 InnoDB 对 FULLTEXT 索引的支持）
地理空间数据类型支持 Yes
地理空间索引支持 Yes（MySQL 5.7 和更高版本提供 InnoDB 对地理空间索引的支持）
Hash indexes No（InnoDB 在内部将哈希索引用于其自适应哈希索引功能）
Index caches Yes
锁粒度 Row
MVCC（多版本并发控制） Yes
复制支持 （在服务器中而不是在存储引擎中实现.） Yes
Storage limits 64TB
T-tree indexes No
事务 Yes
更新数据字典的统计信息 Yes
  • InnoDB 与 MyISAM 的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。（就牵涉到“聚集索引”、“锁粒度”、“MVCC”等相关内容）

使用 InnoDB 表的好处

  • 如果服务器由于硬件或软件问题而崩溃，无论当时数据库中发生了什么，重新启动数据库后都无需执行任何特殊操作。 InnoDB “crash recovery”自动确定在崩溃之前提交的所有更改，并撤消所有正在处理但尚未提交的更改。只需重新启动，然后从上次中断的地方 continue 即可。
  • InnoDB 存储引擎维护自己的“buffer pool”，该“buffer pool”在访问数据时将表和索引数据缓存在主内存中。经常使用的数据直接从内存中处理。此缓存适用于多种类型的信息，并加快了处理速度。在专用数据库服务器上，通常将多达 80％的物理内存分配给缓冲池。
  • 如果将相关数据分成不同的表，则可以设置“foreign keys”强制“referential integrity”（参照完整性）。更新或删除数据，并自动更新或删除其他表中的相关数据。尝试将数据插入到辅助表中，而在主表中没有相应的数据，那么不良数据将自动被踢出。
  • 如果数据在磁盘或内存中损坏，则“checksum”机制会在使用前提醒您注意虚假数据。
  • 当为每个表设计具有适当的“primary key”列的数据库时，涉及这些列的操作会自动进行优化。在“WHERE”子句，“ORDER BY”子句，“GROUP BY”子句和“join”操作中引用主键列非常快。
  • 插入，更新和删除通过名为“change buffering”的自动机制进行了优化。 InnoDB 不仅允许对同一表进行并发读写访问，而且还缓存更改后的数据以简化磁盘 I/O。
  • 性能收益不仅限于具有长期运行查询的巨型表。当从表中一遍又一遍地访问相同的行时，称为自适应哈希指数的功能将接管这些查找，使其变得更快，就像它们从哈希表中出来一样。
  • 可以压缩表和关联的索引。
  • 可以创建和删除索引，而对性能和可用性的影响要小得多。
  • 截断“file-per-table”表空间非常快，并且可以释放磁盘空间供 os 重用，而不是释放“system tablespace”中只有InnoDB可以重用的空间。【？？？】
  • 对于“BLOB”和长文本字段（具有“DYNAMIC”行格式），表数据的存储布局效率更高。
  • 可以通过查询“INFORMATION_SCHEMA”表来监视存储引擎的内部工作情况。
  • 可以通过查询“Performance Schema”表来监视存储引擎的性能详细信息。
  • 可以自由地将 InnoDB 表与其他 MySQL 存储引擎的表混合使用，即使在同一条语句中也是如此。
    例如，您可以使用 join 操作在单个查询中合并 InnoDB 和 MEMORY 表中的数据。
  • InnoDB 旨在处理大量数据时的 CPU 效率和最高性能。
  • InnoDB 表可以处理大量数据，即使在文件大小限制为 2GB 的 os 上也是如此。

InnoDB 表的最佳做法【！！！】

  • 使用一个或多个最频繁查询的列作为每个表指定一个“primary key”，如果没有明显的主键，则指定一个“auto-increment”值。【使用主键】
  • 使用“joins”时，将根据来自多个表的相同 ID 值从这些表中提取数据。为了提高连接性能，请在连接列上定义“foreign keys”，然后在每个表中使用相同的数据类型声明这些列。添加外键可确保对引用的列进行索引，从而可以提高性能。外键还会将删除或更新传播到所有受影响的表，并且如果父表中不存在相应的 ID，则可以防止在子表中插入数据。【使用外键】
  • 关闭“autocommit”。每秒提交数百次会限制性能（受存储设备的写入速度限制）。【关闭自动提交】
  • 通过将“START TRANSACTION”和“COMMIT”语句放在方括号中，将相关的 DML 操作集分组为transactions。虽然您不想过于频繁地提交，但是您也不想发出大量的“INSERT”，“UPDATE”或“DELETE”语句，这些语句运行了几个小时而没有提交。
  • 不使用“LOCK TABLES”语句。 InnoDB可以一次处理所有一次读取和写入同一张表的多个会话，而不会牺牲可靠性或高性能。要获得对一组行的排他性写访问权限，请使用“SELECT ... FOR UPDATE”语法仅锁定要更新的行。【！！！使用“select ... for update”而非“lock tables”！！！】
  • 启用“innodb_file_per_table”选项或使用常规表空间将表的数据和索引放入单独的文件中，而不是 system tablespace。【？？？】
    默认情况下启用“innodb_file_per_table”选项。
  • 评估您的数据和访问模式是否受益于 InnoDB 表或页压缩特性。您可以压缩 InnoDB 表而不会牺牲读/写功能。【？？？】
  • 如果在“CREATE TABLE”的“ENGINE=”子句中指定的引擎存在问题，请使用选项“--sql_mode=NO_ENGINE_SUBSTITUTION”运行服务器以防止使用其他存储引擎创建表。

验证 InnoDB 是默认存储引擎

发出“SHOW ENGINES”语句以查看可用的 MySQL 存储引擎。在 InnoDB 行中查找“DEFAULT”。 

mysql> SHOW ENGINES;

或者，查询“INFORMATION_SCHEMA.ENGINES”表。【一样的效果】 

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;

使用 InnoDB

  1. 【使用 InnoDB 作为默认】如果 InnoDB 不是您的默认存储引擎，则可以通过使用命令行中定义的“--default-storage-engine=InnoDB”或 MySQL 服务器选项文件的“[mysqld]”部分中定义的“default-storage-engine=innodb”重新启动服务器，来确定数据库服务器或应用程序是否可以正确使用 InnoDB。
    【创建表为 InnoDB】由于更改默认存储引擎仅会影响新 table 的创建，因此，请运行所有应用程序安装和设置步骤以确认所有 table 均已正确安装。然后练习所有应用程序功能，以确保所有数据加载，编辑和查询功能都能正常工作。如果 table 依赖于另一个存储引擎特有的功能，则会收到错误消息。将“ENGINE=other_engine_name”子句添加到“CREATE TABLE”语句中，以避免出现此错误。
  2. 【修改表为 InnoDB】、【从旧表创建 InnoDB 新表】如果您没有对存储引擎做出明智的决定，并且想预览使用 InnoDB 创建某些 table 时的工作方式，请为每个 table 发出命令“ALTER TABLE table_name ENGINE = InnoDB”;。或者，要在不干扰原始 table 的情况下运行测试查询和其他语句，请进行复制： 
    CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM other_engine_table;

关闭 InnoDB

Oracle 建议 InnoDB 作为典型数据库应用程序的首选存储引擎，从在本地系统上运行的单用户 Wiki 和博客到会限制性能的高端应用程序。在 MySQL 5.7 中，InnoDB是新 table 的默认存储引擎。

  • InnoDB 无法禁用。 “--skip-innodb”选项（及其同义词“--innodb=OFF”，“--disable-innodb”等）已弃用且无效，使用该选项会导致警告。它将在将来的 MySQL 版本中删除。
取自“http://wiki.eijux.com/index.php?title=InnoDB：简介&oldid=6543”