如果你有MyISAM
要转换为的表InnoDB
为了获得更好的可靠性和可伸缩性,在转换之前请查看以下指南和技巧。
分区MyISAM
以前版本MySQL中创建的表与MySQL 8.0不兼容。这些表必须在升级之前准备好,要么删除分区,要么将它们转换为InnoDB
.看到第24.6.2节,“与存储引擎相关的分区限制”,以获取更多资料。
当你从MyISAM
表,降低的值key_buffer_size
配置选项,以释放不再需要缓存结果的内存。的值增加通过innodb_buffer_pool_size
配置选项,执行类似的分配缓存内存的角色InnoDB
表。的InnoDB
缓冲池缓存表数据和索引数据,加快查询的查找速度,并将查询结果保存在内存中以供重用。有关缓冲池大小配置的指导,请参见章节8.12.3.1,“MySQL如何使用内存”.
因为MyISAM
表不支持交易,你可能没有注意到自动提交
配置选项和提交
而且回滚
语句。这些关键字对于允许多个会话读写非常重要InnoDB
并发的表,在写量大的工作负载中提供了大量的可伸缩性优势。
当事务处于打开状态时,系统会保留事务开始时看到的数据快照,如果在一个零散的事务继续运行时,系统插入、更新和删除数百万行,则会导致大量开销。因此,要注意避免事务运行太长时间:
上述技巧可以节省在太长的事务期间可能浪费的内存和磁盘空间。当事务比它们应该的时间短时,问题是过多的I/O。与每个提交
, MySQL确保每个更改都安全地记录到磁盘,这涉及到一些I/O。
对于大多数操作
InnoDB
表,您应该使用的设置自动提交= 0
.从效率的角度来看,当您连续发出大量的I/O时,这可以避免不必要的I/O插入
,更新
,或删除
语句。从安全的角度来看,这允许您发出一个回滚
语句,以恢复丢失或乱码的数据mysql命令行,或应用程序中的异常处理程序中。自动提交= 1
适合于InnoDB
表,当运行一系列查询以生成报告或分析统计数据时。在这种情况下,不存在相关的I/O惩罚提交
或回滚
,InnoDB
可以自动优化只读工作负载.如果您要进行一系列相关的更改,请使用一个单一的方法一次性完成所有更改
提交
最后。例如,如果您将相关的信息插入到多个表中,请执行单个操作提交
在做了所有的更改之后。或者如果你连续跑了很多次插入
语句,做单提交
加载完所有数据后;如果你做了数百万插入
语句,也许通过发出一个提交
每1万个或10万个记录,因此事务不会增长得太大。记住,即使是
选择
语句打开一个事务,因此在运行一些报表或调试查询后在交互mysql会话,要么发出一个提交
或关闭mysql会话。
相关信息请参见第15.7.2.2节,“自动提交、提交和回滚”.
您可能会看到引用的警告消息”死锁”在MySQL错误日志,或输出显示引擎innodb状态
.一个死锁这不是一个严重的问题吗InnoDB
表,并往往不需要任何纠正行动。当两个事务开始修改多个表,以不同的顺序访问表时,它们可能会达到一个状态,即每个事务都在等待另一个事务,并且都无法继续。当死锁检测(默认),MySQL立即检测到这种情况并取消(回滚)”小”事务,允许另一个继续。方法禁用死锁检测innodb_deadlock_detect
配置选项,InnoDB
依赖于innodb_lock_wait_timeout
设置在发生死锁时回滚事务。
无论哪种方式,您的应用程序都需要错误处理逻辑来重新启动由于死锁而强制取消的事务。当您重新发出与以前相同的SQL语句时,原来的计时问题将不再适用。要么其他事务已经完成,您的事务可以继续,要么其他事务仍在进行中,您的事务等待完成。
如果死锁警告经常出现,您可以检查应用程序代码,以一致的方式重新排序SQL操作,或者缩短事务。可以使用innodb_print_all_deadlocks
选项允许查看MySQL错误日志中的所有死锁警告,而不是仅查看数据库中的最后一个警告显示引擎innodb状态
输出。
有关更多信息,请参见15.7.5节,“InnoDB中的死锁”.
从…得到最好的表现InnoDB
表中,您可以调整一些与存储布局相关的参数。
当你转换时MyISAM
表是大的,经常访问,并保存重要数据,调查和考虑innodb_file_per_table
而且innodb_page_size
变量,以及ROW_FORMAT
而且KEY_BLOCK_SIZE
条款的创建表
声明。
在最初的实验中,最重要的设置是innodb_file_per_table
.启用此设置时(默认为new)InnoDB
中隐式创建的表file-per-table表空间。相比之下InnoDB
系统表空间、每个表文件的表空间允许操作系统在表被截断或删除时回收磁盘空间。每个表文件的表空间也支持动态而且压缩行格式和相关特性,如表压缩、用于长可变长列的高效页外存储以及大索引前缀。有关更多信息,请参见第15.6.3.2节,“每个表空间文件”.
你也可以储存InnoDB
共享通用表空间中的表,支持多表和所有行格式。有关更多信息,请参见15.6.3.3节“通用表空间”.
你可能会InnoDB
表是MyISAM表的克隆,而不是使用ALTER TABLE
要执行转换,在切换前要并排测试新旧表。
创建一个空的InnoDB
表具有相同的列和索引定义。使用显示创建表
要看完整的table_name
\ G创建表
语句。改变引擎
条款引擎= INNODB
.
将大量数据传输到一个空数据中InnoDB
表中所创建的表,插入与插入
.innodb_table
Select * frommyisam_table
命令primary_key_columns
属性的索引也可以创建InnoDB
表中插入数据后。从历史上看,创建新的二级索引是一个缓慢的操作InnoDB
,但是现在您可以在数据加载后创建索引,而在索引创建步骤中开销相对较小。
如果你有独特的
对于辅助键的约束,你可以通过在导入操作期间暂时关闭唯一性检查来加速表的导入:
设置unique_checks = 0;...导入操作…设置unique_checks = 1;
对于大表,这样可以节省磁盘I/O,因为InnoDB
可以使用改变缓冲以批处理的方式写入二级索引记录。确保数据不包含重复的键。unique_checks
允许但不要求存储引擎忽略重复的键。
为了更好地控制插入过程,你可以将大表分段插入:
INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey >某物和你的钥匙<=换掉;
插入所有记录后,可以重命名表。
在转换大表时,增大表的大小InnoDB
缓冲池以减少磁盘I/O。通常,推荐的缓冲池大小是系统内存的50%到75%。你也可以增加的大小InnoDB
日志文件。
如果您打算在。中制作多个数据的临时副本InnoDB
在转换过程中的表,建议您在每表文件的表空间中创建表,以便在删除表时可以回收磁盘空间。当innodb_file_per_table
已启用配置选项(默认值),新创建InnoDB
表隐式地创建在每个表文件的表空间中。
你是否转换MyISAM
表直接或创建克隆InnoDB
表时,请确保在此过程中有足够的磁盘空间容纳旧表和新表。InnoDB
表需要更多的磁盘空间MyISAM
表。如果一个ALTER TABLE
操作耗尽空间,开始回滚,如果是磁盘绑定的,可能需要几个小时。插入,InnoDB
使用插入缓冲区将二级索引记录批量合并到索引。这节省了大量的磁盘I/O。对于回滚,不使用这种机制,回滚的时间可能比插入长30倍。
在失控回滚的情况下,如果数据库中没有有价值的数据,建议终止数据库进程,而不是等待数百万个磁盘I/O操作完成。有关完整的过程,请参见章节15.21.3,强制恢复InnoDB.
的主键
子句是影响MySQL查询性能以及表和索引空间使用的关键因素。主键唯一地标识表中的一行。表中的每一行都应该有一个主键值,并且不能有两个行有相同的主键值。
这些是主键的指导原则,后面是更详细的解释。
声明一个
主键
对于每个表。通常,它是您所引用的最重要的列在哪里
子句时查找单行。声明
主键
原条款创建表
语句,而不是稍后通过ALTER TABLE
声明。仔细选择列及其数据类型。首选数字列,而不是字符或字符串列。
如果没有其他稳定的、唯一的、非空的数字列可使用,则考虑使用自动递增列。
如果不确定主键列的值是否会改变,自动递增列也是一个不错的选择。更改主键列的值是一项开销很大的操作,可能涉及重新排列表和每个辅助索引中的数据。
考虑添加一个主键到任何没有表的地方。根据表的最大预计大小使用最小的实际数字类型。这可以使每一行稍微紧凑一些,从而为大型表节省大量空间。如果表有空间,节省的空间将成倍增加二级索引,因为主键值在每个辅助索引项中重复。除了减少磁盘上的数据大小外,较小的主键还可以使更多的数据适合缓冲池加速各种操作,提高并发性。
如果表已经在某个较长的列上有一个主键,例如VARCHAR
,考虑添加一个新的unsignedAUTO_INCREMENT
列并将主键切换到该列,即使在查询中没有引用该列。这种设计更改可以在二级索引中节省大量空间。可以将前主键列指定为唯一的非空
来执行与主键
子句,即防止在所有这些列中重复或空值。
如果将相关信息分散到多个表中,通常每个表都使用相同的列作为其主键。例如,一个人事数据库可能有几个表,每个表都有一个员工编号的主键。销售数据库可能有一些主键为客户号的表,而另一些主键为订单号的表。因为使用主键的查找非常快,所以可以为这样的表构造高效的连接查询。
如果你离开主键
子句完全消失,MySQL会为你创建一个不可见的子句。它是一个6字节的值,可能比您需要的要长,因此会浪费空间。因为它是隐藏的,所以不能在查询中引用它。
的可靠性和可伸缩性特性InnoDB
需要更多的磁盘存储MyISAM
表。您可以稍微更改列和索引定义,以便更好地利用空间,减少处理结果集时的I/O和内存消耗,以及更好的查询优化计划,从而有效地使用索引查找。
如果为主键设置了数字ID列,则使用该值与任何其他表中的相关值进行交叉引用,特别是用于加入查询。例如,与其接受国家名称作为输入并对相同的名称进行查询搜索,不如执行一次查找来确定国家ID,然后执行其他查询(或单个连接查询)来跨多个表查找相关信息。与其将客户或目录商品编号存储为数字字符串(可能会占用几个字节),不如将其转换为用于存储和查询的数字ID。一个4字节无符号数组INT
列可以索引超过40亿个项目(美国十亿的意思是:10亿)。有关不同整数类型的范围,请参见第11.1.2节,“整型(精确值)- Integer, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT”.