mysql修改表的存储引擎(myisam<=>innodb)

矫情吗；* 2022-06-13 08:18 250阅读 0赞

author:skate  
time:2012/03/30

**修改表的存储引擎myisam<=>innodb**

查看表的存储引擎  
mysql> show create table tt7;  
\+-------+-------------------------------------------------------------------------------------------------------------------------+  
| Table | Create Table                                                                                                            |  
\+-------+-------------------------------------------------------------------------------------------------------------------------+  
| tt7   | CREATE TABLE \`tt7\` (  
  \`id\` int(10) default NULL,  
  \`name\` char(10) default NULL  
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |  
\+-------+-------------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.00 sec)

查看表的数据量  
mysql> select count(1) from tt7;  
\+----------+  
| count(1) |  
\+----------+  
| 16777216 |  
\+----------+  
1 row in set (0.00 sec)

**方法一：**

直接更改存储引擎  
mysql> alter table tt7 engine=innodb;  
Query OK, 16777216 rows affected (2 min 39.80 sec)  
Records: 16777216  Duplicates: 0  Warnings: 0

**方法二：**

把方法一中的存储引擎改回myisam  
mysql> alter table tt7 engine=myisam;  
Query OK, 16777216 rows affected (27.09 sec)  
Records: 16777216  Duplicates: 0  Warnings: 0

从这里也可以看出myisam表要比innodb表快很多

创建个和tt7同样表结构的表  
mysql> create table tt7\_tmp like tt7;  
Query OK, 0 rows affected (0.02 sec)

tt7\_tmp作为中间结果集  
mysql> insert into tt7\_tmp select \* from tt7;  
Query OK, 16777216 rows affected (27.20 sec)  
Records: 16777216  Duplicates: 0  Warnings: 0

删除原表的数据  
mysql> truncate table tt7;  
Query OK, 16777725 rows affected (0.18 sec)

这回更改原表的存储引擎  
mysql> alter table tt7 engine=innodb;  
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0

速度很快就完成了

再把中间结果集的数据导回原表中  
mysql> insert into tt7 select \* from tt7\_tmp;  
Query OK, 16777216 rows affected (2 min 0.95 sec)  
Records: 16777216  Duplicates: 0  Warnings: 0

删除中间表  
mysql> drop table tt7\_tmp;

**测试结果：**

方法二比较快一点，但是数据量要是比较大的话，方法二就要采用化整为零的分批操作的方式，否则insert操作将会具耗时，并产生大量的undo日志。

如果是小表的话（500M以内，根据自己系统的硬件环境），采用方法一就可以  
如果是大表的话，那就采用方法二+批量的方式

如果是批量更改表的存储引擎

用于生成变更的SQL语句：  
SELECT CONCAT('ALTER TABLE ',table\_name,' ENGINE=InnoDB;') FROM information\_schema.tables WHERE table\_schema='db\_name' AND ENGINE='myisam';

用于生成检查表的SQL语句：  
SELECT CONCAT('CHECK TABLE ',table\_name) FROM information\_schema.tables WHERE table\_schema='db\_name';

根据自己系统配置修改如下参数，以加快变更速度（记得以前的值，一会还得改回来）  
SET GLOBAL sort\_buffer\_size=64\*1024\*1024;  
SET GLOBAL tmp\_table\_size=64\*1024\*1024;  
SET GLOBAL read\_buffer\_size=32\*1024\*1024;  
SET GLOBAL read\_rnd\_buffer\_size=32\*1024\*1024;

\------end-------