如果您有想要转换为 InnoDB
以获得更好的可靠性和可扩展性的 MyISAM
表,请在转换之前查看以下指南和提示。
在早期版本的 MySQL 中创建的分区 MyISAM
表与 MySQL 9.0 不兼容。此类表必须在升级之前进行准备,方法是移除分区或将其转换为 InnoDB
。有关更多信息,请参阅 第 26.6.2 节,“与存储引擎相关的分区限制”。
当您从 MyISAM
表过渡时,请降低 key_buffer_size
配置选项的值,以释放不再需要用于缓存结果的内存。增加 innodb_buffer_pool_size
配置选项的值,该选项执行类似的角色,为 InnoDB
表分配缓存内存。 InnoDB
缓冲池 缓存表数据和索引数据,从而加快查询查找速度并将查询结果保留在内存中以供重用。有关缓冲池大小配置的指导,请参阅 第 10.12.3.1 节,“MySQL 如何使用内存”。
由于 MyISAM
表不支持 事务,因此您可能并没有过多地关注 autocommit
配置选项以及 COMMIT
和 ROLLBACK
语句。这些关键字对于允许多个会话同时读取和写入 InnoDB
表非常重要,在写入密集型工作负载中提供了可观的可扩展性优势。
当事务处于打开状态时,系统会保留事务开始时看到的数据的快照,如果系统在某个事务持续运行期间插入、更新和删除了数百万行,这可能会导致大量开销。因此,注意避免长时间运行的事务。
如果您正在使用 mysql 会话进行交互式实验,请在完成后始终
COMMIT
(以完成更改)或ROLLBACK
(以撤消更改)。关闭交互式会话,而不是让它们长时间处于打开状态,以避免意外地长时间保持事务处于打开状态。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。从安全的角度来看,这允许您发出一个ROLLBACK
语句来恢复丢失或损坏的数据,如果您在 mysql 命令行或应用程序中的异常处理程序中犯了错误。autocommit=1
适用于运行一系列用于生成报告或分析统计数据的查询的InnoDB
表。在这种情况下,与COMMIT
或ROLLBACK
相关的 I/O 惩罚不存在,InnoDB
可以 自动优化只读工作负载。如果您进行了一系列相关的更改,请使用单个
COMMIT
在最后一次性完成所有更改。例如,如果您将相关信息插入多个表中,请在完成所有更改后进行一次COMMIT
。或者,如果您运行许多连续的INSERT
语句,请在所有数据加载后进行一次COMMIT
;如果您正在执行数百万个INSERT
语句,也许可以将巨大的事务拆分为每十万或一百万个记录发出一次COMMIT
,这样事务就不会变得太大。请记住,即使是
SELECT
语句也会打开一个事务,因此在交互式 mysql 会话中运行一些报告或调试查询后,请发出COMMIT
或关闭 mysql 会话。
有关相关信息,请参阅 第 17.7.2.2 节,“autocommit、Commit 和 Rollback”。
您可能会在 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
输出中的最后一个警告。
有关更多信息,请参阅 第 17.7.5 节,“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 行格式以及相关功能,例如表压缩、长可变长度列的有效脱页存储以及大型索引前缀。有关更多信息,请参阅 第 17.6.3.2 节,“文件表空间”。
您还可以将 InnoDB
表存储在共享的通用表空间中,该表空间支持多个表和所有行格式。有关更多信息,请参阅 第 17.6.3.3 节,“通用表空间”。
您可能会创建一个 InnoDB
表,该表是 MyISAM 表的克隆,而不是使用 ALTER TABLE
执行转换,以便在切换之前并排测试旧表和新表。
创建一个具有相同列和索引定义的空 InnoDB
表。使用 SHOW CREATE TABLE
查看要使用的完整 table_name
\GCREATE TABLE
语句。将 ENGINE
子句更改为 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。通常,推荐的缓冲池大小是系统内存的 50% 到 75%。您也可以增加 InnoDB
日志文件的大小。
如果您打算在转换过程中在 InnoDB
表中创建多个数据临时副本,建议您在文件表空间中创建这些表,以便在删除表时可以回收磁盘空间。当 innodb_file_per_table
配置选项启用(默认值)时,新创建的 InnoDB
表隐式地创建在文件表空间中。
无论您是直接转换 MyISAM
表还是创建克隆的 InnoDB
表,请确保在转换过程中有足够的磁盘空间来容纳旧表和新表。 InnoDB
表比 MyISAM
表需要更多的磁盘空间。 如果 ALTER TABLE
操作耗尽空间,它将开始回滚,如果磁盘绑定,这可能需要数小时。对于插入操作,InnoDB
使用插入缓冲区以批处理方式将辅助索引记录合并到索引中。这可以节省大量磁盘 I/O。对于回滚,没有使用这种机制,回滚可能比插入操作慢 30 倍。
在回滚失控的情况下,如果您数据库中没有宝贵的数据,建议杀死数据库进程,而不是等待数百万次磁盘 I/O 操作完成。有关完整过程,请参见 第 17.20.3 节,“强制 InnoDB 恢复”。
PRIMARY KEY
子句是影响 MySQL 查询性能以及表和索引空间使用情况的关键因素。主键唯一标识表中的一行。表中的每一行都应该有一个主键值,并且没有两行可以具有相同的主键值。
以下是主键的指南,之后是更详细的解释。
为每个表声明一个
PRIMARY KEY
。通常,它是您在查找单个行时在WHERE
子句中引用的最重要的列。在原始的
CREATE TABLE
语句中声明PRIMARY KEY
子句,而不是通过ALTER TABLE
语句在稍后添加它。仔细选择列及其数据类型。优先选择数字列而不是字符或字符串列。
如果不存在其他稳定的、唯一的、非空数字列可以使用,请考虑使用自动递增列。
如果对主键列的值是否可能发生变化存在疑问,自动递增列也是一个不错的选择。更改主键列的值是一个昂贵的操作,可能涉及重新排列表中的数据以及每个辅助索引中的数据。
考虑向任何还没有主键的表添加 主键。根据表的预期最大大小,使用最小的实用数字类型。这可以使每行略微更紧凑,这可以为大型表节省大量空间。如果表具有任何 辅助索引,空间节省会成倍增加,因为主键值会在每个辅助索引条目中重复。除了减少磁盘上的数据大小之外,较小的主键还可以让更多数据适合 缓冲池,从而加速各种操作并提高并发性。
如果表已经在某个较长的列(例如 VARCHAR
)上具有主键,请考虑添加一个新的无符号 AUTO_INCREMENT
列并将主键切换到该列,即使该列在查询中没有被引用。此设计更改可以在辅助索引中产生大量空间节省。您可以将以前的主键列指定为 UNIQUE NOT NULL
来强制执行与 PRIMARY KEY
子句相同的约束,即防止所有这些列中出现重复值或空值。
如果您将相关信息分散到多个表中,通常每个表都使用同一列作为其主键。例如,人事数据库可能具有多个表,每个表的主键都是员工编号。销售数据库可能有一些表的主键是客户编号,而其他表的主键是订单编号。由于使用主键进行查找非常快,因此您可以为这些表构建有效的联接查询。
如果您完全省略了 PRIMARY KEY
子句,MySQL 会为您创建一个不可见的子句。它是一个 6 字节的值,可能比您需要的更长,从而浪费空间。由于它是隐藏的,因此您不能在查询中引用它。
InnoDB
的可靠性和可扩展性功能比等效的 MyISAM
表需要更多磁盘存储空间。您可能需要稍微更改列和索引定义,以实现更好的空间利用率,减少处理结果集时的 I/O 和内存消耗,以及更好的查询优化计划,从而有效地使用索引查找。
如果您为主键设置了一个数字 ID 列,请使用该值与任何其他表中的相关值进行交叉引用,特别是对于 联接 查询。例如,不要接受国家名称作为输入并进行搜索相同名称的查询,而是进行一次查找以确定国家 ID,然后进行其他查询(或单个联接查询)以查找多个表中的相关信息。不要将客户或目录项编号存储为数字字符串,这可能会占用多个字节,而是将其转换为数字 ID 以进行存储和查询。一个 4 字节的无符号 INT
列可以索引超过 40 亿个项目(使用美国的十亿:10 亿)。有关不同整数类型的范围,请参见 第 13.1.2 节,“整数类型(精确值) - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT”。