将表从 MyISAM 转换为 InnoDB
任务
您决定将一个或多个表从MyISAM更改为InnoDB。这应该很简单，只需执行ALTER TABLE foo ENGINE=InnoDB命令即可。但是您听说可能会出现一些微妙的问题。
本文描述了可能出现的问题以及如何解决这些问题。
建议 搜索问题的一种方法是（至少在*nix中）：
mysqldump --no-data --all-databases >schemas egrep 'CREATE|PRIMARY' schemas # 关注主键 egrep 'CREATE|FULLTEXT' schemas # 查找全文索引 egrep 'CREATE|KEY' schemas # 查找各种组合的索引
了解索引的工作原理将有助于更好地理解在InnoDB中可能运行得更快或更慢的内容。
索引问题
（这些建议中的大部分和这些事实中的一些有例外。）
事实 每个InnoDB表都有一个主键。如果您没有提供，则使用第一个非NULL UNIQUE键。如果无法完成，则提供一个6字节的隐藏整数。
建议 查找没有主键的表。明确指定一个主键，即使它是人造的AUTO_INCREMENT。这不是绝对要求，但对于InnoDB而言，它比MyISAM更强烈。有一天您可能需要遍历该表；如果没有明确的PK，则无法完成。
事实 主键的字段包括在每个二级键中。
- 请牢记这一点，以检查冗余索引。
PRIMARY KEY(id), INDEX(b), -- 实际上与INDEX(b, id)相同 INDEX(b, id) -- 实际上与INDEX(b)相同
- (保留其中一个INDEX，而不是两个)
- 注意微妙的事情，例如
PRIMARY KEY(id), UNIQUE(b), -- 保留唯一性约束 INDEX(b, id) -- 删除此项
- 另外，由于PK和数据是共存的：
PRIMARY KEY(id), INDEX(id, b) -- 删除这个索引；它几乎没什么用
对比 MyISAM的这个特性在InnoDB中不可用；'id'的值将在每个不同的'abc'值处重新开始：
id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (abc, id)
模拟MyISAM“特性”的方法可能是这样的：您想要的是这个，但它不起作用，因为它引用了表两次：
INSERT INTO foo
(other, id, ...)
VALUES
(123, (SELECT MAX(id)+1 FROM foo WHERE other = 123), ...);
相反，您需要类似于这样的变体。（您可能已经有了BEGIN...COMMIT。）
BEGIN;
SELECT @id := MAX(id)+1 FROM foo WHERE other = 123 FOR UPDATE;
INSERT INTO foo
(other, id, ...)
VALUES
(123, @id, ...);
COMMIT;
必须使用事务以防止另一个线程获取相同的id。
建议 查找此类主键。如果找到这样的主键，请考虑如何更改设计。没有简单的解决方法。但是，以下可能是可以的。（确保id的数据类型足够大，因为它不会重新开始。）：
id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (abc, id), UNIQUE(id)
建议 保持主键短小。如果有辅助索引，请记住它们包括PK字段。长PK会使辅助索引变得臃肿。好吧，也许不会 — 如果字段有很多重叠。 示例：PRIMARY KEY(a,b,c), INDEX(c,b,a)—没有额外的臃肿。
建议 检查AUTO_INCREMENT大小。
- 几乎从不需要BIGINT。它至少会浪费4个字节/行（与INT相比）。
- 总是使用UNSIGNED和NOT NULL。
- MEDIUMINT UNSIGNED（最大16M）可能足以代替INT
- 要保持悲观 —更改很痛苦。
对比 “垂直分割”。这里是指您人为地将表拆分为另一个并行表，以移动笨重的列（例如BLOB）。在MyISAM中，这是有益的，可以避免在不需要读取它时跨越BLOB。InnoDB以不同的方式存储BLOB和TEXT —767字节在记录中，其余部分在其他块中。因此，将表重新组合起来可能（也可能不）值得。注意：InnoDB行限制为8KB，而767计入其中。
事实。 在MariaDB 10.0.5之前，FULLTEXT和SPATIAL索引在InnoDB中不可用。请注意，MyISAM和InnoDB FULLTEXT索引使用不同的stopword列表和不同的系统变量。
推荐。 搜索此类索引。将此类表保留在MyISAM中。最好从InnoDB中拆分出最少的列进行垂直分割（请参见上文）。
事实。 INDEX的最大长度在引擎之间是不同的。（这种变化不太可能影响到您，但要小心。）MyISAM允许1000字节；InnoDB允许767字节，刚好足够一个
VARCHAR(255) CHARACTER SET utf8. ERROR 1071 (42000): 指定的键太长；最大键长度为767字节
事实。 PRIMARY KEY包含在数据中。因此，对于没有辅助索引的表，SHOW TABLE STATUS将显示0字节（或16KB）的Index_length。否则，Index_length是辅助键的总大小。
事实。 PRIMARY KEY包含在数据中。因此，通过PK进行精确匹配可能会比InnoDB快一点。并且，“范围”扫描通过PK可能更快。
事实。 通过辅助键进行查找会遍历辅助键的B树，抓取PRIMARY KEY，然后遍历PK的B树。因此，在InnoDB中，辅助键查找有点更加麻烦。
对比 PRIMARY KEY的字段包含在每个辅助键中。这可能会导致在InnoDB中出现“Using index”（在EXPLAIN计划中）的情况，而在MyISAM中没有发生。 （这是一个轻微的性能提升，并抵消了否则需要的双重查找。）但是，当“Using index”对PRIMARY KEY有用时，MyISAM会执行“index scan”，而InnoDB实际上必须执行“table scan”。
与MyISAM相同。 几乎总是
INDEX(a) -- 删除这个索引，因为另一个处理它。 INDEX(a,b)
对比 数据按PK顺序存储。这意味着“最近”的记录在末尾“聚集”在一起。这可能比MyISAM具有更好的“引用局部性”。
与MyISAM相同。 优化器几乎从不在单个SELECT中使用两个索引。（5.1偶尔会执行“index merge”。）子查询和UNION中的SELECT可以独立选择索引。
微妙问题。 当您删除行时，AUTO_INCREMENT id将被删除。REPLACE也是如此，它是DELETE加INSERT。
非常微妙的问题。 复制发生在COMMIT上。如果您有多个使用事务的线程，则AUTO_INCREMENT可能以无序方式到达从站。一个事务BEGINs，抓取一个id。然后另一个事务抓取一个id但在第一个完成之前COMMIT。
与MyISAM相同。 在InnoDB和MyISAM中，通常都不使用“前缀”索引。例如：INDEX(foo(30))
非索引问题
InnoDB 的磁盘空间可能是 MyISAM 的 2-3 倍。
MyISAM 和 InnoDB 在使用 RAM 方面有很大的差异。如果更改了所有表，则应进行重大调整：
- key_buffer_size — 小但非零；比如 10M；
- innodb_buffer_pool_size — 可用 RAM 的 70%
InnoDB 实际上不需要 CHECK、OPTIMIZE 或 ANALYZE。从维护脚本中删除它们（如果保留它们，也不会有真正的危害）。
备份脚本可能需要检查。MyISAM 表可以通过复制三个文件进行备份。对于 InnoDB，仅当设置 innodb_file_per_table 为 1 时才可能进行备份。在 MariaDB 10.0 之前，从生产环境复制表或数据库以用于开发环境是不可能的。改用 mysqldump。自 MariaDB 10.0 起，可以创建热备份 - 请参见备份和恢复概述。
在 MariaDB 5.5 之前，不支持 DATA DIRECTORY 表选项用于 InnoDB。自 MariaDB 5.5 起，它得到支持，但仅在 CREATE TABLE 中支持。INDEX DIRECTORY 没有效果，因为 InnoDB 不使用单独的索引文件。为了更好地平衡几个磁盘的工作负载，还可以更改某些 InnoDB 日志文件的路径。
了解 autocommit 和 BEGIN/COMMIT。
- （默认）autocommit = 1：没有 BEGIN 或 COMMIT 语句存在时，每个语句本身就是一个事务。这接近于 MyISAM 的行为，但并不是最好的选择。
- autocommit = 0：COMMIT 将关闭一个事务并开始另一个事务。对我来说，这很笨拙。
- （推荐）BEGIN ... COMMIT 可以让您控制要被视为事务和“原子”的操作序列。如果需要将某些内容撤消回 BEGIN，则包括 ROLLBACK 语句。
Perl 的 DBIx::DWIW 和 Java 的 JDBC 具有执行 BEGIN 和 COMMIT 的 API 调用。这些可能比“执行” BEGIN 和 COMMIT 更好。
在任何地方都要测试错误！由于 InnoDB 使用行级锁定，因此可能会遇到意外死锁。引擎将自动回滚到 BEGIN。正常恢复是从 BEGIN 开始重新执行。请注意，这是具有 BEGIN 的一个强有力的理由。
LOCK/UNLOCK TABLES — 删除它们。用 BEGIN ... COMMIT 替换它们（有点）。如果将 innodb_table_locks 设置为 1，则 LOCK 将起作用，但效率较低，并且可能存在微妙问题。
在 5.1 中，ALTER ONLINE TABLE 可以显著加速某些操作。（通常 ALTER TABLE 将表复制并重建索引。）
几乎所有东西的“限制”在 MyISAM 和 InnoDB 之间是不同的。除非您有巨大的表、宽行、许多索引等等，否则您不太可能遇到不同的限制。
MyISAM 和 InnoDB 混合？这没问题。但是有一些注意事项。
- 应相应地调整 RAM 设置。
- 不同引擎的表 JOIN 可以工作。
- 影响两种类型表的事务可以撤消 InnoDB 更改，但会保留 MyISAM 更改。
- 复制：MyISAM 语句在完成后被复制；InnoDB 语句会等待 COMMIT。
在 InnoDB 中，FIXED（与 DYNAMIC 相对）毫无意义。
PARTITION — 您可以对 MyISAM 和 InnoDB 表进行分区。请记住奇怪的规则：您必须
- 没有 UNIQUE（或 PRIMARY）键；或者
- 在每个 UNIQUE 键中都有您要“分区”的值。
对 InnoDB 不建议使用前者。如果您想要 AUTO_INCREMENT，则后者会很混乱。
PARTITION 中的 PRIMARY KEY — 由于每个键都必须包含您正在 PARTITION 的字段，那么 AUTO_INCREMENT 如何工作呢？好吧，似乎有一个方便的特殊情况：
- 有效：PRIMARY KEY(autoinc，partition_key)
- 对于 InnoDB无效：PRIMARY KEY(partition_key，autoinc)
也就是说，当 AUTO_INCREMENT 是 PRIMARY KEY 的第一个字段时，它将正确递增，并且在所有 PARTITION 上都是唯一的，否则不会。
参见
Rick James graciously allowed us to use this article in the Knowledge Base.
Rick James' site has other useful tips, how-tos, optimizations, and debugging tips.
