15.6.1.3 把表的存储引擎从MyISAM切换为InnoDB

原文地址：https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html

如果你想把MyISAM表转换为InnoDB表从而获得更高的可靠性和可扩展性，在转换之前你需要回顾一下这里的指导和提示。

Note 旧版本MySQL分区的MyISAM表和MySQL8.0是不兼容的。这类表在转换之前需要做一定的处理，要么移除分区，要么把它们转换成InnoDB表。查看第23.6.2节 存储引擎的分区限制获得更多信息。

  • 调整为MyISAM和InnoDB存储引擎的内存使用
  • 处理太长或太短的事务
  • 处理死锁
  • 规划存储布局
  • 转换已有的表
  • 克隆表结构
  • 转移已有数据
  • 存储引擎必备条件
  • 为每一张表定义主键
  • 考虑应用的性能
  • 理解InnoDB表相关的文件

调整为MyISAM和InnoDB存储引擎的内存使用

当你从MyISAM表转换时，调低 key_buffer_size配置选项的值来释放不再使用的内存空间。增加innodb_buffer_pool_size的配置值，该配置在InnoDB中承担了和key_buffer_size相类似的角色。InnoDB的buffer pool同时缓存数据和索引来加速查询和保存查询结构在内存中以便重用。查看第8.12.3.1节以了解如何配置buffer pool大小。

处理太长或太短的事务

因为MyISAM表不支持事务，你应该不需要过度关注autocommit配置和COMMIT及ROLLBACK语句。这些关键词对允许多个会话并发读写InnoDB表是非常重要的，并对写负载高的场景提供了高度的可扩展性。

当一个事务开启时，系统保存一个事务开启时看到的数据快照，当因一个有问题的事务运行导致系统增删改数百万行数据时会对数据库造成极大的压力。因此，注意避免事务运行时间过长：

  • 如果你正在使用mysql的交互模式来实验，当结束时记得总是COMMIT（完成修改）或ROLLBACK（取消修改）。关闭交互模式下的会话而不是让他们保持链接很久以免意外打开事务时间太久。

  • 确保你应用中的异常处理同样在未完成更新时ROLLBACK 和 完成更新时COMMIT。

  • ROLLBACK 是一个相对代价较大的操作，因为INSERT,UPDATE和DELETE操作是在COMMIT之前写到InnoDB表的，这种处理是建立在大多数修改最后能够成功提交而少部分会回滚的预期上。当用大量数据来实验时，避免修改大量的行然后再回滚这些变更。

  • 当使用一系列的INSERT语句来插入大量的数据时，周期性的COMMIT事务来避免让事务持续数个小时。尤其是在为数仓导数据时，如果出错了，你最好清空表（用TRUNCATE TABLE）从头开始而不是执行ROLLBACK操作。

之前的小提示主要关注怎么节约由长时间的事务造成的内存和磁盘空间的浪费。然而当事务比期望的要短时，问题主要集中在过高的I/O。伴随着每一个COMMIT，MySQL需要确保每一个事务都安全的记录到磁盘上，这都需要一些I/O。

  • 对于InnoDB表的大多数操作，你应该设置autocommit=0。从效率的角度来说，这避免了大量接连不断的INSERT，UPDATE或DELETE语句所造成的不必要的I/O。从安全的角度来说，这允许你在mysql命令行或者在你的应用程序的异常处理中执行了错误的操作时，可以使用ROLLBACK来恢复丢失或者错乱的数据。

  • 当运行的查询是用来生成报告或者分析统计时，设置autocommit=1是合适的。在这种情况下，对于COMMIT和ROLLBACK是没有性能损耗的，并且InnoDB能够自动的优化只读负载。

  • 如果你执行了一系列的更新，最后使用一个COMMIT来提交所有的更改。例如，如果你插入相关的信息到几张表中，执行完所有的更改后再执行一个COMMIT。或者你连续运行了很多INSERT语句，当所有的数据都载入后再执行一个COMMIT；当你正在执行百万级的INSERT语句时，尽可能通过每执行1万条或者10万条记录后就执行一次COMMIT来拆分庞大的事务，以避免事务增长的过大。

  • 记住即使是一个SELECT语句也会开启一个事务，因此在交互模式会话中执行了一些报告或者debugging查询后，要么执行COMMIT要么关闭mysql会话。

处理死锁

你也许能在MySQL的error log或者SHOW ENGINE InnoDB STATUS的输出中看到deadlocks相关的信息。不要管这可怕的名字，死锁对于InnoDB表来说并不是严重的问题，并且通常不需要修正。当两个事务开始修改多张表的时候，用不同的顺序访问表，它们可以达到一种状态，每个事务都在等待对方的锁且任务一方都拿不到锁。当deadlock detection是开启（默认是开启的）的时候，MySQL 立即会检测到这种情况并取消（rools back）“较小的”的事务，让另一个事务执行。如果使用innodb_deadlock_detect配置选项禁用死锁检测，遇到死锁的情况下InnoDB会根据innodb_lock_wait_timeout设置来回滚事务。

另一方面，你的应用需要错误处理来重启因死锁被强制取消的事务。当你重新发起和之前相同的SQL语句，原来的计时问题不再有用。要么其他的事务已经结束，然后你的事务可以继续处理，要么其他的事务仍在继续执行，然后你的事务一直等待到它结束。

如果死锁警告重复不断的出现，你可能需要检测你的应用代码来重新排列SQL查询的顺序以保持事务之间的执行顺序保持一致，或者缩短你的事务。你可以开启innodb_print_all_deadlocks选项来测试从而可以在MySQL的error log中看到所有的死锁警告，而不是在SHOW ENGINE INNODB STATUS输出中仅仅看到的最近的死锁警告。

更多的信息，请查看第15.7.5节 InnoDB中的死锁

规划存储布局

为了获得InnoDB表的最好性能，你可以调整一系列存储布局相关的参数。

当你转换的MyISAM表是非常庞大、访问频繁时且存储重要数据时，考察和考虑innodb_file_per_table和innodb_page_size配置选项，和CREATE TABLE语句的ROW_FORMAT和KEY_BLOCK_SIZE的子句。

在你初始测试时，最重要的设置是innodb_file_per_table。当这个设置被打开时，新的InnoDB表是隐式使用file-per-table表空间来创建的。与InnoDB系统表空间对比，当表被删除或清空时file-per-table表空间允许操作系统重新使用。file-per-table表空间同时支持DYNAMIC和COMPRESSED行格式，相关的特性包括表压缩，对长的可变长度的列的高效的跨页存储，以及大的索引前缀。更多信息，请查看第15.6.3.2节 file-per-table 表空间。

你也可以存储InnoDB表在通用共享表空间，此空间支持多种表和所有的行格式。更多信息请查看第15.6.3.3节 通用表空间。

转换已有的表

使用ALTER TABLE语句来转换一张非InnoDB表到InnoDB：

ALTER TABLE table_name ENGINE=InnoDB;

克隆表结构

你可能会通过克隆一张MyISAM表创建一张InnoDB表，而不是通过ALTER TABLE来执行转换，然后在切换前可以并行测试新老表之间的兼容性。

使用相同的列和索引定义来创建一张空的InnoDB表，使用SHOW CREATE TABLE table_name\G来查看需要用到的完整的CREATE TABLE语句。通过ENGINE=INNODB子句来改变存储引擎。

转移已有的数据

为了转移大量数据到一张如上文所创建的空InnoDB表，可以使用INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY PRIMARY_KEY_COLUMNS。

插入数据后你也可以给你的InnoDB表创建索引。历史经验来看，为InnoDB创建二级索引是一个非常慢的操作，但是现在你可以在出入数据后创建索引，从而创建索引引发的开销相对较小。

如果你的二级索引有唯一键约束，你可以通过临时关闭唯一性检测来加速导入操作：

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

对于大型的表，因为InnoDB能够使用它的change buffer能够批量的写入二级索引从而能够节约磁盘I/O。当然要确保数据没有重复的key。unique_checks允许但不要求存储引擎忽略重复的key。 为了更高效的控制插入处理，你可以分批的插入数据到大型表。

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

所有的记录都已被插入后，你就可以重命名这张表了。

在一张大型表的转换过程中，可以提高InnoDB buffer pool的大小到物理内存的80%来减少磁盘I/O的开销，你也可以同时增加InnoDB日志文件的大小。

存储需求

如果你打算在转换期间创建好几个数据表的InnoDB引擎拷贝，建议你在file-per-table表空间创建表以便你能够在删除这些表时这些空间能被重复利用。当innodb_file_per_table配置选项是打开的时候（默认就是打开的），新创建的InnoDB表隐式使用file-per-table表空间。

不管你是把MyISAM表直接转换为InnoDB表还是创建一个InnoDB表的副本，要确保程序处理期间你有足够的磁盘空间来存储新老数据表的数据。InnoDB表需要比MyISAM表更多的磁盘空间。如果在运行ALTER TABLE 时缺少磁盘空间，数据库可能会花好几个小时来回滚操作。对于插入来说，InnoDB使用插入缓冲来批量合并二级索引到索引中。这可以节省大量的磁盘I/O,然而在回滚时却无法使用这套机制，并且回滚操作所需要的时间可能是插入操作的30倍。

在一个异常回滚的情况下，如果你的数据库中没有特别重要的数据，建议你直接kill数据库进程而不是等待数百万的磁盘I/O操作完成。想了解完整的处理流程，请查看第15.20.2节 强制InnoDB恢复。

为每一张表定义一个主键

PRIMARY KEY是一个影响MySQL查询性能和表与索引的空间使用的极其重要的因素。主键唯一确定了一个表中的唯一一行数据。表中的每一行数据一定会有一个主键值，且没有两行数据的主键是重复的。

下面是一些关于主键的使用指导，以及它们的详细解释。

  • 为每一张表定义一个主键，典型的，主键是查找单行数据where子句中最重要的字段。

  • 在CREATE TABLE语句中用PRIMARY KEY 子句去定义主键，而不是后来再通过ALTER TABLE 语句取定义。

  • 小心地选择列和它的数据类型。数值型的列优于字符或字符串类型。

  • 如果没有合适的稳定、唯一、非null的数值型字段可选，使用一个自增的字段也是一个不错的选择。

考虑为每一张没有主键的表添加一个主键。基于一张表最大的数据量来选择一个够用的最小整数类型。这可以让每一行数据的体积稍微小一些，然而对于一张大型的表来说节省的空间也是可观的。如果一张表有二级索引，那么这种空间节省是成倍的，因为在每一个二级索引实体中都会保存主键的值。除了能够减小在磁盘上的数据大小，一个更小的主键也会让更多的数据缓存到buffer pool中，从而加速各种操作和提高并发能力。

如果一张表已经拥有较长的列的主键索引，例如一个VARCHAR字段，考虑添加一个新的非负数的自增列并把主键主键切换到这个列上，即使这个列跟查询无关。这种设计上的改变能够节省二级索引很可观的空间节省。你可以指定老的主键列为UNIQUE NOT NULL强制列拥有和主键相同的约束，也就是不允许这些列有重复或者null值。

如果你把数据分散存储在多张表中，典型的每张表使用相同的列来作为它们的主键。例如，一个人员数据库应该会有好几张表，并且每一张表都有一个工号的主键。一个销售数据库可能会有多张都有顾客 号的表，且另一些表都拥有订单号的主键。因为通过主键来查找是非常快速的，因此你可以在这些表上构造高效的JOIN查询。

如果你完全不使用PRIMARY KEY子句来显式创建一个主键，MySQL会为你创建一个隐藏的主键索引。这是一个6字节的值，可能超过了你需要的范围，这样会浪费空间。由于是隐藏的，因此你在查询中也无法使用它。

应用程序性能考虑

Innodb的可靠性和可扩展性相比相同的MyISAM表需要更多的磁盘空间。在处理查询结果集时，为了更好的磁盘利用，降低磁盘I/O和内存消耗，或者为了更高效的利用索引来优化查询，你可以稍微修改列和索引的定义。

如果你设置了一个数值型ID列作为主键，使用这个值来关联其他表中相关的值，尤其是在JOIN查询时。例如，相对接收一个国家名字作为输入，然后使用这个名字来查询，使用一个国家的ID来查询，然后再从其他表中查询相关的信息（或者仅使用一个JOIN查询）会更好。相比用一个数字字符串来存储一个顾客或者菜单项编号，使用几个字节并将其转换为数字ID进行存储和查询是更好的。一个4字节无符号的整型列可以索引40亿项目（这里是美国的billion：1000 million）。想知道不同整型类型数的存储范围，请查看第11.2.1节 整型类型-INTERGER，SMALLINT，MEDIUMINT，BIGINT。

理解InnoDB表关联的文件

InnoDB文件需要相比MyISAM需要更多关注和规划。

  • 一定不能删除用于维护InnoDB表空间的ibdata文件。

  • 移动和拷贝InnoDB表到不同的服务器的方法在第15.6.1.2节 移动和复制InnoDB表一节中有介绍。