将表从MyISAM转换为InnoDB

如果您具有MyISAM要转换InnoDB为更好的可靠性和可伸缩性的表，请在转换之前参见以下准则和提示。

注意
MyISAM在早期版本的MySQL中创建的分区表与MySQL 8.0不兼容。此类表必须在升级之前准备，方法是删除分区或将其转换为InnoDB。有关更多信息，请参见“与存储引擎有关的分区限制”。

调整MyISAM和InnoDB的内存使用量
处理太长或太短的交易
处理死锁
规划存储布局
转换现有表
克隆表的结构
传输现有数据
储存要求
为每个表定义一个主键
应用程序性能注意事项
了解与InnoDB表关联的文件

调整MyISAM和InnoDB的内存使用量

从MyISAM表过渡时，降低key_buffer_size配置选项的值以释放不再需要缓存结果的内存。增加innodb_buffer_pool_size配置选项的值，该选项执行与为InnoDB表分配缓存内存类似的作用。该InnoDB缓冲池可以缓存表数据和索引数据，加快了查询，查找并保持查询结果在内存中进行再利用。有关缓冲池大小配置的指导，请参见“ MySQL如何使用内存”。

处理太长或太短的交易

由于MyISAM表不支持事务，因此您可能没有过多注意autocommit配置选项和COMMITand ROLLBACK语句。这些关键字对于允许多个会话同时读取和写入InnoDB表很重要，从而在写繁重的工作负载中提供了可伸缩性的实质好处。

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

如果您正在使用mysql会话进行交互式实验，请务必COMMIT在完成时（完成更改）或ROLLBACK（撤消更改）。关闭交互式会话，而不要长时间打开它们，以避免意外地使事务长时间打开。
确保您的应用程序中的任何错误处理程序也未ROLLBACK完成更改或COMMIT已完成更改。
ROLLBACK这是一个相对昂贵的操作，因为INSERT，UPDATE和DELETE操作会写入到InnoDB之前的表中COMMIT，并期望大多数更改都能成功提交并且回滚很少。在尝试大量数据时，请避免对大量行进行更改，然后回滚这些更改。
当使用一系列INSERT语句加载大量数据时，请定期COMMIT执行结果以避免事务持续数小时。在数据仓库的典型加载操作中，如果出现问题，请截断表（使用TRUNCATE TABLE），然后从头开始，而不是执行操作ROLLBACK。

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

对于InnoDB表格的大多数操作，应使用设置autocommit=0。从效率的角度看，这样就避免了在发出大量连续的不必要的I / O INSERT，UPDATE或DELETE语句。从安全角度来看，ROLLBACK如果您在mysql命令行或应用程序中的异常处理程序中出错，则允许您发出语句以恢复丢失或乱码的数据。
autocommit=1适用于InnoDB表的时间是运行一系列查询以生成报告或分析统计信息时。在这种情况下，不存在与COMMIT或相关的I / O损失ROLLBACK，并且InnoDB可以自动优化只读工作负载。
如果进行一系列相关更改，请一次完成所有更改，最后一次COMMIT完成。例如，如果您将相关信息插入多个表中，则COMMIT在进行所有更改后执行一次。或者，如果您运行许多连续的INSERT语句，则COMMIT在加载所有数据之后执行一次；如果您要执行数百万条INSERT语句，则可能通过发出COMMIT每万或十万条记录来拆分庞大的事务，因此事务不会变得太大。
请记住，即使是一条SELECT语句也会打开一个事务，因此在交互式mysql会话中运行某些报表或调试查询后，请发出a COMMIT或关闭mysql会话。

处理死锁

您可能会在MySQL错误日志中看到有关“死锁”的警告消息，或者输出SHOW ENGINE INNODB STATUS。尽管名称听起来很吓人，但是对于表来说，死锁并不是一个严重的问题InnoDB，并且通常不需要采取任何纠正措施。当两个事务开始修改多个表时，以不同的顺序访问表时，它们可能会进入一种状态，即每个事务都在等待另一个事务，并且都无法继续进行。当死锁检测被使能（默认）中，MySQL立即检测到这种情况并取消（回滚）的“小”交易，允许对方继续进行。如果使用innodb_deadlock_detect配置选项禁用了死锁检测，则在死锁的情况下InnoDB依靠该innodb_lock_wait_timeout设置回滚事务。

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

如果死锁警告不断发生，则可以检查应用程序代码以一致的方式对SQL操作进行重新排序，或缩短事务。您可以测试innodb_print_all_deadlocks启用的选项，以参见MySQL错误日志中的所有死锁警告，而不仅仅是SHOW ENGINE INNODB STATUS输出中的最后一个警告。

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

规划存储布局

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

当您将MyISAM是大的，经常访问的，并保持至关重要的数据表，调查和考虑innodb_file_per_table和innodb_page_size配置选项，以及ROW_FORMAT和KEY_BLOCK_SIZE条款中的CREATE TABLE说法。

在您的初始实验中，最重要的设置是innodb_file_per_table。启用此设置（默认设置）后，将InnoDB在每表文件表空间中隐式创建新表。与InnoDB系统表空间相比，每表文件表空间允许在表被截断或删除时由操作系统回收磁盘空间。每表文件表空间还支持 DYNAMIC和 COMPRESSED行格式以及相关功能，例如表压缩，用于长可变长度列的有效页外存储以及大索引前缀。有关更多信息，请参见“每表文件表空间”。

您还可以将InnoDB表存储在共享的通用表空间中，该表空间支持多个表和所有行格式。有关更多信息，请参见“通用表空间”。

转换现有表

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

ALTER TABLE table_name ENGINE=InnoDB;

克隆表的结构

您可以制作一个InnoDB表，该表是MyISAM表的克隆，而不是ALTER TABLE用来执行转换，以便在切换之前并排测试新表和新表。

创建InnoDB具有相同的列和索引定义的空表。使用看到完整的语句来使用。将子句更改为。SHOW CREATE TABLE table_name\GCREATE TABLEENGINEENGINE=INNODB

传输现有数据

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

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

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

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

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

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

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

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

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

储存要求

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

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

对于失控的回滚，如果数据库中没有有价值的数据，建议您终止数据库进程，而不要等待数百万的磁盘I / O操作完成。有关完整过程，请参见“InnoDB恢复”。

为每个表定义一个主键

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

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

PRIMARY KEY为每个表声明一个。通常，它是WHERE查找单行时在子句中引用的最重要的列。
PRIMARY KEY在原始CREATE TABLE语句中声明该子句，而不是稍后通过一条ALTER TABLE语句添加它。
仔细选择列及其数据类型。数字列优先于字符列或字符串列。
如果没有其他稳定的，唯一的，非空的数字列要使用，请考虑使用自动递增列。
如果不确定主键列的值是否可以更改，则自动增量列也是一个不错的选择。更改主键列的值是一项昂贵的操作，可能涉及在表内和每个辅助索引内重新排列数据。

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

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

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

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

应用程序性能注意事项

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

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

了解与InnoDB表关联的文件

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

您不得删除代表系统表空间的 ibdata文件。InnoDB
“移动或复制InnoDB表”InnoDB中介绍了将表移动或复制到其他服务器的方法。