InnoDB磁盘结构之将表从MyISAM转换为InnoDB

一只小码农正在路过
143 阅读13分钟

InnoDB磁盘结构之将表从MyISAM转换为InnoDB

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

调整MyISAM和InnoDB的内存使用量

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

在繁忙的服务器上，在关闭查询缓存的情况下运行基准测试。该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_file_format以及 innodb_page_size配置选项，以及 ROW_FORMAT 和KEY_BLOCK_SIZE条款中的 CREATE TABLE说法。

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

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

转换现有表

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

ALTER TABLE table_name ENGINE=InnoDB;

警告

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

克隆表的结构

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

创建InnoDB具有相同的列和索引定义的空表。使用看到完整的 语句来使用。将子句更改为 。 SHOW CREATE TABLE *table_name*\GCREATE TABLEENGINE``ENGINE=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表相比，InnoDB的可靠性和可伸缩性功能需要更多的磁盘存储。您可能会略微更改列和索引定义，以提高空间利用率，减少处理结果集时的I / O和内存消耗，以及更好地利用索引查找来实现更好的查询优化计划。

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

更多内容欢迎关注我的个人公众号“韩哥有话说”，100G人工智能学习资料，大量后端学习资料等你来拿。

qrcode_for_gh_3214f9e3470a_258.jpg

avatar
文章
阅读
粉丝