将表从 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.