MySQL线上600W纪录的MyISAM表,要把存储引擎改为innoDB,一alter表就立马锁死的问题探讨...

最新推荐文章于 2025-07-09 10:46:35 发布
最新推荐文章于 2025-07-09 10:46:35 发布 · 342 阅读 · 0
文章标签：

#数据库

本文讨论了一种在不影响业务的情况下将大型在线数据库从MyISAM迁移到InnoDB的方法。提出了使用临时表进行数据同步及主从复制的策略，以减少停机时间和锁定问题。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

行业内部朋友企鹅上问:

Sun Shine:
我线上有个数据库大约600百万数据我想把存储引擎改为innoDB, 现在表平均每分钟有50个插入,已修改表就立马锁死,有什么好的办法吗?

黄杉 11:27:31
那肯定会锁表啊，你全天业务低峰期是在哪个时间短啊? ltertabletbnameengine=innodb;就是全锁表的
Sun shine11:28:33
很平均的现在相对数据少点
黄杉()11:28:57
如果这样的话，你的模式是ms 还是mm呢？ 果是这样子的话，可以先在一台从上面做，然后主从切换
黄杉()11:30:05
线上生产库竟然没有主从机制？

Sun shine11:30:11
如果整表改的话 需要多久
对的，下一步准备做主从复制。

黄杉()11:31:32
这样子，你在测试库 test库里面建一个临时表，把现在600W的纪录倒入到临时表里面，先做一下alter table tbname engine=innodb; 试下看需要多久

Sun shine11:32:15
我试试下，其他还有什么好的办法没？

黄杉()11:32:30
有啊，用临时表啊

Sun shine11:33:02
好的 我试试。谢谢

黄杉()11:33:14
1 ，在test库上面建立一个临时表，表结构跟600W的纪录的表结构一样，只是engine=innodb
2， 将600W纪录导入到临时表里面
3，在原表上建立触发器，同步数据到临时表里面
4， 观察一段时间，看原表与临时表数据是否一致
5 ，观察完毕，稳定后，执行rename table xxx.tbname to test.tbname_old_myisam;rename table test.tbname to xxx.tbname; 底切换过来，当然这个过程会有2秒的down time

Sun shine11:36:33
恩好的 2秒的话 没问题。

黄杉()11:36:39
你才 1tps，估计这最多2秒的down time 对你几乎没有影响的

Sun shine11:36:50
对的。

黄杉()11:37:14
你可以先在测试环境试试我这个方案

Sun shine11:37:35
恩ok, 怎么对单个表的 不锁表备份 以及导入


黄杉()11:55:17
你的是myisam吧，直接copy文件啊，然后数据库重起一下，copy数据文件到test库的目录下，然后重起mysql数据库，test库里面就有数据了


黄杉()11:56:18
这是最野蛮的做法


Sun shine11:56:33
好强悍。

黄杉()11:56:35
还有就是用mysqldump以及mysql导入
mysqldump -uadmin -p -P3307 -h127.0.0.1 --default-character-set=utf8 --extended-insert=false os ulc> /home/check/ulc.sql 类似这样的导出
我以前在生产库就是如此操作的，很快就导出来了，记住加-d参数，只导出数据，不导出表结构


黄杉()12:00:43
还有一种方案，就是不用导入导出了，在原表上写一个触发器，数据同时更新到临时表里面


Sun shine12:01:39
看的不是太懂，就是我有个表库是 ota表示 otalog 单独到处这个表然后再导入到另一库 test、


黄杉()12:01:58
是的,test库的表engine=innodb


Sun shine12:03:03
mysqldump -uadmin -p -P3307 -h127.0.0.1 --default-character-set=utf8 --extended-insert=false os ulc> /home/check/ulc.sql 这个怎么改?

黄杉()12:03:49
mysqldump -uadmin -p -P3307 -h127.0.0.1 --default-character-set=utf8 --extended-insert=false -d ota otalog> /home/check/ulc.sql

黄杉()12:04:59
-u后面跟的是用户名，-p后面跟的是密码


Sun shine12:05:11
恩好的。

Share出来，感觉有些地方回答不是太好，欢迎大家一起交流探讨。

总结：

1 线上要主从或者主主，不能单机

2 600W的记录，如果一分钟insert 50条的话，一天增长 SELECT 24*60*50 = 72000，7W条纪录，一年增长SELECT 24*60*50*365=26280000，2千万纪录。个人觉得可以考虑分表或者分区策略了。

3 看表的命名是log结尾的，估计是日志系列的业务，可以考虑存到mongodb这种nosql里面，减轻MySQL的压力。

iteye_4185
关注
MySQL存储引擎MyISAM和InnoDB
热爱IT行业
07-30 1493
数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。
InnoDB系统崩溃的常见解决恢复方法与监控方法讲解
Web_boom的博客
01-06 1776
最近还整理一份JavaScript与ES的笔记，一共25个重要的知识点，对每个知识点都进行了讲解和分析。能帮你快速掌握JavaScript与ES的相关知识，提升工作效率。
三分钟入门 InnoDB 存储引擎中的表锁和行锁
Java笔记虾
08-16 436
各位对 ”锁“ 这个概念应该都不是很陌生吧，Java 语言中就提供了两种锁：内置的 synchronized 锁和 Lock 接口，使用锁的目的就是管理对共享资源的并发访问，保证数据的完整...
MYSQL中Innodb表锁详解
qq_41473691的博客
11-14 2425
意向锁的并发性：意向锁不会与行锁的共享/排它锁互斥，正因为如此，意向锁并不会影响到多个事务对不同数据行加排它锁时的并发性（不然直接用普通的表锁就行了）注：意向锁是有存储引擎自己的维护的，用户无法手动操作意向锁，在为数据行加共享/排它锁之前，Innodb会先获取该数据行所载数据表对应的意向锁。表级别的S锁、X锁：在对某个表执行增删改查语句的时候，Innodb存储引擎不会为这个表添加表级别的。3、IX、IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。表锁（Table Lock)
表空洞清理ALTER TABLE table_name ENGINE=InnoDB
07-09 248
的作用是将表的存储引擎更改为 InnoDB，同时重建表以优化存储结构。
mysql优化innodb表
阳水平的博客
10-13 544
项目中有个表经常增删改,因此定期需要优化一下表: optimize table `vip_active_anonymous`; 执行下面语句直接提示: Table does not support optimize, doing recreate + analyze instead. 查过资料表明,optimize table 对于innodb来说，无法作为a single operation，实际的操作是： ALTER TABLE test.foo ENGINE=InnoDB; -- 比较费时,测
存储引擎：InnoDB
小浪与艳花
08-27 388
InnoDB 存储引擎 优点： 提供 提交，回滚，崩溃恢复能力的事务安全。 支持外键约束 锁机制默认为行锁 支持B Tree索引和集群索引； 支持数据缓存和索引缓存。 缺点： 空间使用较高来保留数据和索引； 内存使用占用较高； 批量插入速度相比myisam较慢； 访问速度相比myisam慢； 以下着重阐述存储引擎InnoDB的表...
记一次极限的OPTIMIZE TABLE 经历
theflowerofac^MQLYES的专栏
09-05 1081
可以通过OPTIMIZE TABLE 回收掉没有在使用的磁盘空间碎片，但需要提前预估好供其“倒腾“使用的空间。
MySQL创建数据表时设定引擎MyISAM/InnoDB操作
09-08
如果需要将已存在的表的存储引擎从MyISAM改为InnoDB，可以使用ALTER TABLE语句。例如： ```sql ALTER TABLE test ENGINE=InnoDB; ``` 这个命令会将test表的存储引擎改为InnoDB。 如果需要批量更改数据库中所有表的...
Mysql存储引擎MyISAM的常见问题（表损坏、无法访问、磁盘空间不足）
09-10
MySQL存储引擎MyISAM在使用过程中可能会遇到一系列问题，这些问题包括表损坏、表过大导致无法访问以及数据目录磁盘空间不足。以下是对这些问题的详细分析和解决方案。 1. **处理MyISAM存储引擎的表损坏** 当...
Mysql更换MyISAM存储引擎为Innodb的操作记录总结
09-09
本文将重点讨论如何将MySQL中的MyISAM存储引擎转换为InnoDB存储引擎，以及这两种引擎的区别。 MyISAM是MySQL早期版本的默认存储引擎，以其快速的读取性能和对全表锁定的处理而闻名。然而，MyISAM不支持事务处理、...
mysql更改引擎(InnoDB,MyISAM)的方法
09-10
MySQL是一种广泛使用的开源关系型数据库管理系统，其支持多种存储引擎，包括InnoDB和MyISAM。这两种引擎各有特点，选择哪种引擎取决于应用的需求。InnoDB是MySQL的默认存储引擎（自MySQL 5.5.5版本起），它支持事务...
Mysql中的Optimize table命令
初心不改
07-05 5134
在MySQL中，optimizetable命令用来重新整理(InnoDB & MyISAM)表格并优化空间利用。优化后的表更容易被访问，节省了磁盘空间，同时可以加速查询，以达到更好的性能。
mysql知识
atongmu2017的博客
04-20 1089
是一种以。
mysql进阶：optimize table命令
热门推荐
chenpeng19910926的专栏
03-29 4万+
在使用mysql的时候有时候，可能会发现尽管一张表删除了许多数据，但是这张表表的数据文件和索引文件却奇怪的没有变小。这是因为mysql在删除数据（特别是有Text和BLOB）的时候，会留下许多的数据空洞，这些空洞会占据原来数据的空间，所以文件的大小没有改变。这些空洞在以后插入数据的时候可能会被再度利用起来，当然也有可能一直存在。这种空洞不仅额外增加了存储代价，同时也因为数据碎片化降低了表的扫描效率...
mysql之DDL
黄啊码
04-09 511
一 简介:今天来DDL的变革 二 DDL演化方式: 1 copy table : 1 创建临时表2 copy数据到临时表 3 rename进行交换 缺点 1 阻塞事务 2占用磁盘空间 2 inplace : 1 在线更改表,不会拷贝临时表 缺点 1 阻塞事务 3 online_ddl ：1 在线更改表,不会拷贝临时表 优点 1 不会阻塞事务 因此MySQL最新版本中，InnoDB支...
MySQL的analyze table和optimize table对表进行定期优化
小灯数据
04-01 7707
1、analyze table优化表的统计信息 mysql> show index from t; +-------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+--...
案例 - optimize table 的一些坑
weixin_34248023的博客
08-17 4088
线上IM消息的数据库，磁盘空间使用率已到达96%没申请到扩容的新机器，无法做数据库迁移保留的是全量聊天记录，一条都不许删在这种场景下，为了减少空间容量，只能对表做碎片整理来释放空间, optimize table当我们使用mysql进行delete数据，delete完以后，发现空间文件ibd并没有减少，这是因为碎片空间的存在，举个例子，一共公司有10号员工，10个座位，被开除...
mysql-java后端面试（自用笔记，更新中）
zhangzulin1234的博客
09-07 1011
索引 什么是索引：排好序的数据结构 红黑树： 规则： （1）根节点和叶子节点都是黑色 （2）每个红节点的两个子节点都是黑色的 （3）从任一节点到叶子节点的所有路径都包含相同数目的黑色节点 3. B树和B+树： 定义：都是每个节点最多包含m个孩子（m>=2） 优势：每个节点默认是一页数据，每页16KB，索引以文件的形式存储到磁盘上，每次在磁盘中读数据，然后在内存中进行比较（内存中比较比较快），且可以进行二分查找。 4. B树和B+树的区别 B+树的非叶子节点不...
mysql 修改表innodb_批量修改Mysql表引擎为InnoDB的方法
最新发布
07-17
<think>我们面对的任务是批量修改MySQL数据库中多个表的存储引擎为InnoDB。 方法一：使用ALTER TABLE语句逐个修改，但效率低。 方法二：生成并执行批量修改的SQL脚本。 步骤： 1. 生成需要修改的表列表。我们可以通过查询information_schema数据库来获取当前数据库（或指定数据库）中所有非InnoDB引擎的表。 2. 为每个表生成一个ALTER TABLE语句，并将这些语句保存到一个SQL脚本中。 3. 执行这个SQL脚本。 具体操作： 步骤1：查询非InnoDB引擎的表 假设我们要修改的数据库名为`your_database_name`，则查询语句如下： ```sql SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND ENGINE <> 'InnoDB'; ``` 注意：这里我们排除了已经是InnoDB引擎的表。 步骤2：生成修改脚本 我们可以使用GROUP_CONCAT（在MySQL中）或者使用编程方式（如使用脚本语言）来生成多个ALTER TABLE语句。 但是，如果表很多，我们可以用以下方式在MySQL中生成一个包含所有ALTER语句的SQL文件： 方法A：使用SQL生成语句并输出到文件 ```sql SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND ENGINE <> 'InnoDB' INTO OUTFILE '/path/to/alter_engine.sql'; ``` 注意：使用INTO OUTFILE需要MySQL有文件写入权限，且文件路径必须是MySQL服务端可写的。 方法B：在命令行中使用批处理 我们也可以在命令行中使用mysql命令结合sed或awk来生成并执行： ```bash mysql -u username -p -e "SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='your_database_name' AND ENGINE <> 'InnoDB'" | grep 'ALTER' > alter_engine.sql ``` 然后，我们可以检查生成的alter_engine.sql文件，确认无误后执行： ```bash mysql -u username -p your_database_name < alter_engine.sql ``` 方法C：使用存储过程（不推荐，因为需要创建和删除存储过程，且可能涉及权限） 步骤3：执行生成的SQL文件 如上所示，使用mysql命令行客户端执行生成的SQL文件。 注意事项： 1. 在修改之前，请确保备份数据库，因为修改存储引擎可能会影响数据（特别是从MyISAM转到InnoDB，两种引擎的索引结构不同，表锁和行锁的区别等）。 2. 修改过程中会锁表，所以请在业务低峰期操作。 3. 检查是否有表使用了InnoDB不支持的特性（如MyISAM的全文本索引，在InnoDB中从MySQL5.6开始支持，但如果是更早的版本则不支持）。 4. 如果表很大，修改可能需要较长时间。 另外，如果数据库中有视图，则不需要修改，因为视图没有存储引擎的概念。 因此，批量修改的步骤如下： 1. 备份数据库。 2. 生成修改脚本（如上所述）。 3. 执行修改脚本。 下面是一个完整的示例（假设数据库名为mydb）： 生成脚本文件： ```sql mysql -u root -p -e "SELECT CONCAT('ALTER TABLE \`', TABLE_NAME, '\` ENGINE=InnoDB;') AS sql_stmt FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydb' AND ENGINE <> 'InnoDB' AND TABLE_TYPE='BASE TABLE'" > alter_engine.sql ``` 然后检查alter_engine.sql文件，再执行： ```bash mysql -u root -p mydb < alter_engine.sql ``` 注意：这里我们过滤了TABLE_TYPE='BASE TABLE'，因为information_schema.TABLES中还包括视图（VIEW），而视图不需要修改存储引擎。 如果表中包含外键约束，在修改存储引擎之前，需要确保外键约束在InnoDB中能够正确工作（InnoDB支持外键，而MyISAM不支持）。如果从MyISAM转到InnoDB，那么之前没有检查的外键约束现在会被InnoDB强制执行，所以需要确保数据满足外键约束。 另外，如果从MyISAM转换到InnoDB，原有的FULLTEXT索引在MySQL5.6之前是不支持的，需要删除全文索引或者升级MySQL版本。在MySQL5.6及以上版本，InnoDB支持全文索引。 因此，在转换之前，最好检查一下表结构，特别是索引部分。 总结：批量修改MySQL表的存储引擎为InnoDB，核心是生成并执行一组ALTER TABLE语句。通过查询information_schema.TABLES获取非InnoDB引擎的表，然后为每个表生成ALTER TABLE语句并执行。 相关问题： 1. 如何检查MySQL表的当前存储引擎？ 2. 将MyISAM表转换为InnoDB表需要注意哪些问题？ 3. 如何批量修改MySQL数据库中的所有表的主键？ 4. 在修改存储引擎时，如何避免长时间锁表？ 5. MySQL中InnoDB和MyISAM存储引擎的主要区别是什么？</think>### 批量修改MySQL表存储引擎为InnoDB的方法 #### 方法一：使用SQL脚本生成器（推荐） ```sql -- 步骤1：生成批量修改语句 SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ENGINE=InnoDB;') AS sql_statement FROM information_schema.TABLES WHERE TABLE_SCHEMA = '你的数据库名' AND ENGINE <> 'InnoDB' -- 排除已是InnoDB的表 AND TABLE_TYPE = 'BASE TABLE'; -- 仅处理普通表 -- 步骤2：执行生成的SQL -- 将上一步的输出结果复制执行 ``` #### 方法二：命令行批量操作 ```bash # 生成并直接执行修改命令 mysql -u用户名 -p密码 -Nse \ "SELECT CONCAT('ALTER TABLE \`', TABLE_NAME, '\` ENGINE=InnoDB;') \ FROM information_schema.TABLES \ WHERE TABLE_SCHEMA = '你的数据库名' \ AND ENGINE != 'InnoDB'" | \ mysql -u用户名 -p密码 你的数据库名 ``` #### 方法三：存储过程批量修改 ```sql DELIMITER $$ CREATE PROCEDURE convert_to_innodb() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE tbl_name VARCHAR(255); DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND ENGINE != 'InnoDB'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; convert_loop: LOOP FETCH cur INTO tbl_name; IF done THEN LEAVE convert_loop; END IF; SET @sql = CONCAT('ALTER TABLE ', tbl_name, ' ENGINE=InnoDB'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE cur; END$$ DELIMITER ; -- 执行存储过程 CALL convert_to_innodb(); ``` ### 注意事项 1. **权限要求**：需要`ALTER`权限和`information_schema`查询权限 2. **备份优先**：操作前务必备份数据库 3. **锁表影响**：大表转换可能长时间锁表，建议在低峰期操作 4. **外键约束**：InnoDB强制外键约束，转换前需确保数据一致性 5. **特殊索引**：MyISAM的`FULLTEXT`索引需转换为InnoDB格式（MySQL 5.6+支持） 6. **系统表**：不要修改`mysql`系统库中的表 ### 验证转换结果 ```sql -- 检查非InnoDB表 SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '你的数据库名' AND ENGINE <> 'InnoDB'; ``` > **重要提示**：从MyISAM转换到InnoDB时，表空间管理方式会改变（.frm/.MYD → 系统表空间/独立表空间），建议转换后运行`OPTIMIZE TABLE`重组数据[^1]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值