14.6.1.5 将 table 从 MyISAM 转换为 InnoDB

如果您要转换为MyISAMtable 以获得更好的可靠性和可伸缩性，请在转换之前查看以下准则和提示。

调整 MyISAM 和 InnoDB 的内存使用量

从MyISAMtable 过渡时，降低key_buffer_size配置选项的值以释放不再需要用于缓存结果的内存。增加innodb_buffer_pool_size配置选项的值，该选项执行与InnoDBtable 分配高速缓存相似的作用。 InnoDB buffer pool缓存 table 数据和索引数据，从而加快了查询查找速度，并将查询结果保留在内存中以供重用。有关缓冲池大小配置的指导，请参见第 8.12.4.1 节“ MySQL 如何使用内存”。

在繁忙的服务器上，在关闭查询缓存的情况下运行基准测试。 InnoDB缓冲池具有类似的好处，因此查询缓存可能不必要地占用了内存。有关查询缓存的信息，请参见第 8.10.3 节“ MySQL 查询缓存”。

处理太长或太短的 Transaction

因为MyISAMtable 不支持transactions，所以您可能没有过多注意autocommit配置选项以及COMMIT和ROLLBACK语句。这些关键字对于允许多个会话同时读取和写入InnoDBtable 非常重要，从而在繁重的写工作负载中提供了可伸缩性的实质好处。

当事务打开时，系统会保留数据快照，如在事务开始时看到的那样，如果系统在杂散事务保持运行的同时插入，更新和删除数百万行，则可能导致大量开销。因此，请注意避免事务运行时间过长：

  • 如果您要使用mysql会话进行交互式实验，则完成后请始终COMMIT(完成更改)或ROLLBACK(撤消更改)。关闭交互式会话，而不要长时间打开它们，以免意外使事务长时间打开。

  • 确保应用程序中的任何错误处理程序也ROLLBACK未完成更改或COMMIT已完成更改。

  • ROLLBACK是一个相对昂贵的操作，因为INSERT，UPDATE和DELETE操作在COMMIT之前被写入InnoDBtable 中，并期望大多数更改都能成功提交并且回滚很少。试验大量数据时，请避免对大量行进行更改，然后回滚这些更改。

  • 当使用INSERT语句序列加载大量数据时，请定期COMMIT结果以避免事务持续数小时。在典型的数据仓库加载操作中，如果出现问题，请截断 table(使用TRUNCATE TABLE)，然后从头开始，而不要执行ROLLBACK。

前面的技巧可以节省在过长的事务中可能浪费的内存和磁盘空间。当事务短于应有的时间时，问题就在于过多的 I/O。对于每个COMMIT，MySQL 确保将每个更改安全地记录到磁盘上，其中涉及一些 I/O。

  • 对于InnoDBtable 的大多数操作，应使用设置autocommit=0。从效率角度来看，当您发出大量连续的INSERT，UPDATE或DELETE语句时，这避免了不必要的 I/O。从安全角度来看，如果您在mysql命令行或应用程序的异常处理程序中出错，则可以发出ROLLBACK语句来恢复丢失或乱码的数据。

  • autocommit=1适用于InnoDBtable 的时间是运行一系列查询以生成报告或分析统计信息时。在这种情况下，没有与COMMIT或ROLLBACK相关的 I/O 损失，并且InnoDB可以自动优化只读工作负载。

  • 如果您进行了一系列相关更改，请一次完成所有更改，并以一个COMMIT结尾。例如，如果您将相关的信息插入到多个 table 中，则在进行所有更改后执行一次COMMIT。或者，如果您运行许多连续的INSERT语句，则在加载所有数据后再执行一个COMMIT；否则，请执行一次。如果您要执行数百万条INSERT语句，则可能通过每万或十万条记录发出COMMIT来拆分庞大的事务，因此事务不会变得太大。

  • 请记住，即使是SELECT语句也会打开一个事务，因此在交互式mysql会话中运行某些报 table 或调试查询之后，请发出COMMIT或关闭mysql会话。

Handling Deadlocks

您可能会在 MySQL 错误日志或显示引擎的 INNODB 状态的输出中看到有关“死锁”的警告消息。尽管名称听起来很吓人，但对于InnoDBtable 来说deadlock并不是一个严重的问题，并且通常不需要采取任何纠正措施。当两个事务开始修改多个 table 时，以不同的 Sequences 访问 table 时，它们可能会进入一种状态，即每个事务都在 await 另一个事务，并且都无法 continue 进行。启用deadlock detection时(默认)，MySQL 立即检测到这种情况并取消(rolls back)“较小”的事务，从而允许其他事务 continue 进行。如果使用innodb_deadlock_detect配置选项禁用了死锁检测，则InnoDB将依靠innodb_lock_wait_timeout设置在发生死锁的情况下回滚事务。

无论哪种方式，您的应用程序都需要错误处理逻辑来重新启动由于死锁而被强制取消的事务。当您重新发出与以前相同的 SQL 语句时，原始的计时问题不再适用。另一笔 Transaction 已经完成，您可以 continue 进行，或者另一笔 Transaction 仍在进行中，您的 Transaction 会等到完成为止。

如果不断发生死锁警告，则可以检查应用程序代码以一致的方式对 SQL 操作进行重新排序，或缩短事务处理时间。您可以在启用innodb_print_all_deadlocks选项的情况下进行测试，以查看 MySQL 错误日志中的所有死锁警告，而不仅仅是显示引擎的 INNODB 状态输出中的最后一个警告。

有关更多信息，请参见第 14.7.5 节“ InnoDB 中的死锁”。

规划存储布局

为了从InnoDBtable 中获得最佳性能，您可以调整许多与存储布局有关的参数。

转换大型，经常访问且包含重要数据的MyISAMtable 时，请研究并考虑innodb_file_per_table，innodb_file_format和innodb_page_size配置选项以及CREATE TABLE语句的ROW_FORMAT 和 KEY_BLOCK_SIZE 子句。

在您的初始实验期间，最重要的设置是innodb_file_per_table。启用此设置(这是 MySQL 5.6.6 的默认设置)后，新的InnoDBtable 将在file-per-tabletable 空间中隐式创建。与InnoDB系统 table 空间相比，每 table 文件 table 空间允许在 table 被截断或删除时由 os 回收磁盘空间。每 table 文件 table 空间还支持Barracuda文件格式和相关功能，例如 table 压缩，有效的长可变长度列的页外存储以及大索引前缀。有关更多信息，请参见第 14.6.3.2 节“每 table 文件 table 空间”。

您还可以将InnoDB个 table 存储在共享的常规 table 空间中。常规 table 空间支持梭子鱼文件格式，并且可以包含多个 table。有关更多信息，请参见第 14.6.3.3 节“常规 table 空间”。

转换现有 table

要将非InnoDBtable 转换为使用InnoDB，请使用ALTER TABLE：

ALTER TABLE table_name ENGINE=InnoDB;

Warning

不要将mysql数据库中的 MySQL 系统 table 从MyISAM转换为InnoDBtable。这是不受支持的操作。如果这样做，MySQL 将不会重新启动，直到您从备份中还原旧的系统 table 或通过重新初始化数据目录来重新生成它们(请参见第 2.10.1 节“初始化数据目录”)。

克隆 table 的结构

您可以将InnoDBtable 作为 MyISAMtable 的副本，而不是使用ALTER TABLE进行转换，以在切换之前并排测试新旧 table。

用相同的列和索引定义创建一个空的InnoDBtable。使用SHOW CREATE TABLE table_name\G查看要使用的完整CREATE TABLE语句。将ENGINE子句更改为ENGINE=INNODB。

传输现有数据

要将大量数据传输到如上一节所示创建的空InnoDBtable 中，请使用INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns插入行。

您也可以在插入数据后为InnoDBtable 创建索引。从历史上看，创建新的二级索引对于 InnoDB 而言是一项缓慢的操作，但是现在您可以在索引创建步骤中以相对较少的开销加载数据之后创建索引。

如果您对辅助键具有UNIQUE约束，则可以通过在导入操作期间暂时关闭唯一性检查来加快 table 导入的速度：

SET unique_checks=0;
... import operation ...
SET unique_checks=1;

对于大 table，这可以节省磁盘 I/O，因为InnoDB可以使用其change buffer批量写入辅助索引记录。确保数据不包含重复的密钥。 unique_checks允许但不要求存储引擎忽略重复的密钥。

为了更好地控制插入过程，您可以分段插入大 table：

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

插入所有记录后，您可以重命名 table。

在大 table 转换过程中，增加InnoDB缓冲池的大小以减少磁盘 I/O，最多可占物理内存的 80％。您还可以增加InnoDB个日志文件的大小。

Storage Requirements

如果您打算在转换过程中在InnoDBtable 中制作几个临时数据副本，建议您在每个 table 文件 table 空间中创建 table，以便在删除 table 时可以回收磁盘空间。启用innodb_file_per_table配置选项(默认设置)后，将在每个 table 文件 table 空间中隐式创建新创建的InnoDBtable。

无论是直接转换MyISAMtable 还是创建克隆的InnoDBtable，请确保在此过程中有足够的磁盘空间来容纳旧 table 和新 table。 InnoDBtable 比MyISAMtable 需要更多的磁盘空间. 如果ALTER TABLE操作空间不足，则会启动回滚，如果绑定了磁盘，则可能要花费数小时。对于插入，InnoDB使用插入缓冲区将辅助索引记录合并到批索引中。这样可以节省大量磁盘 I/O。对于回滚，不使用这种机制，回滚所花费的时间可能比插入时间长 30 倍。

在回滚失控的情况下，如果数据库中没有有价值的数据，建议您终止数据库进程，而不要 await 数百万的磁盘 I/O 操作完成。有关完整过程，请参见第 14.22.2 节“强制 InnoDB 恢复”。

为每个 table 定义主键

PRIMARY KEY子句是影响 MySQL 查询性能以及 table 和索引空间使用情况的关键因素。主键唯一地标识 table 中的一行。table 中的每一行都必须具有主键值，并且任何两行都不能具有相同的主键值。

这些是主键的指南，后面有更详细的说明。

  • 为每个 table 声明一个PRIMARY KEY。通常，它是查找单行时在WHERE子句中引用的最重要的列。

  • 在原始CREATE TABLE语句中声明PRIMARY KEY子句，而不是稍后通过ALTER TABLE语句添加它。

  • 仔细选择列及其数据类型。数字列优先于字符列或字符串列。

  • 如果没有其他稳定的，唯一的，非空的数字列，请考虑使用自动递增列。

  • 如果不确定主键列的值是否可以更改，则自动增量列也是一个不错的选择。更改主键列的值是一项昂贵的操作，可能涉及在 table 内和每个辅助索引内重新排列数据。

考虑将primary key添加到尚不存在的任何 table。根据 table 的最大投影尺寸使用最小的实用数字类型。这可以使每行稍微紧凑一些，从而可以为大型 table 节省大量空间。如果 table 具有任何secondary indexes，则节省的空间将成倍增加，因为在每个辅助索引条目中都重复主键值。除了减小磁盘上的数据大小之外，小的主键还使更多数据适合buffer pool，从而加快了各种操作的速度并提高了并发性。

如果 table 在某个较长的列(例如VARCHAR)上已经具有主键，请考虑添加新的无符号AUTO_INCREMENT列，并将主键切换为该主键，即使查询中未引用该列。这种设计更改可以在二级索引中节省大量空间。您可以将以前的主键列指定为UNIQUE NOT NULL，以强制执行与PRIMARY KEY子句相同的约束，即防止所有这些列重复或为空。

如果您将相关信息分布在多个 table 中，则通常每个 table 的主键使用同一列。例如，人员数据库可能有多个 table，每个 table 都有员工编号的主键。一个销售数据库可能有一些带有 Client 编号主键的 table，而另一些带有订单编号主键的 table。因为使用主键的查找非常快，所以您可以为此类 table 构造有效的联接查询。

如果您完全忽略了PRIMARY KEY子句，MySQL 将为您创建一个不可见的子句。它是一个 6 字节的值，可能比您需要的时间更长，因此浪费了空间。因为它是隐藏的，所以您不能在查询中引用它。

应用程序性能注意事项

InnoDB的可靠性和可伸缩性功能比等效的MyISAMtable 需要更多的磁盘存储空间。您可能会稍作更改列和索引定义，以提高空间利用率，减少处理结果集时的 I/O 和内存消耗，以及更好地利用索引查找来实现更好的查询优化计划。

如果确实为主键设置了数字 ID 列，请使用该值与任何其他 table 中的相关值进行交叉引用，尤其是对于join查询。例如，与其接受一个国家名称作为 Importing 并进行查询来搜索相同的名称，不如进行一次查询以确定国家 ID，然后进行其他查询(或单个联接查询)以在多个 table 中查找相关信息。与其将 Client 或商品目录号存储为一串数字(可能会用完几个字节)，不如将其转换为数字 ID 以进行存储和查询。一个 4 字节的无符号INT列可以索引超过 40 亿个项目(美国的含义是十亿：十亿)。有关不同整数类型的范围，请参见第 11.1.2 节“整数类型(精确值)-INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT”。

了解与 InnoDBtable 关联的文件

InnoDB文件比MyISAM文件需要更多的照顾和计划。

Name MySql 中文文档
Version
Language
Badge
Last Updated 2021-07-03T12:24:35