MySQL修改表存储引擎方法总结
MySQL作为最常用的数据库，经常遇到各种各样的问题。今天要说的就是表存储引擎的修改。有三种方式,列表如下。
1.真接修改。在数据多的时候比较慢，而且在修改时会影响读取性能。my_table是操作的表，innoDB是新的存储引擎。
| 代码如下 | |
|
ALTER TABLE my_table ENGINE=InnoDB |
|
2.导出，导入。这个比较容易操作，直接把导出来的sql文件给改了，然后再导回去。用mysqldump ，枫哥常用的是navicate那样更容易上手。友情提醒风险较大。
3.创建，插入。这个比第一种速度快， 安全性比第二种高，推荐。分2步操作
.创建表,先创建一个和要操作表一样的表，然后更改存储引擎为目标引擎。
| 代码如下 | |
|
CREATE TABLE my_tmp_table LIKE my_table; |
|
b.插入。为了安全和速度，最好加上事务，并限制id(主键）范围。
| 代码如下 | |
|
INSERT INTO my_tmp_table SELECT * FROM my_table; |
|
修改表的存储引擎myisam<=>innodb
查看表的存储引擎
| 代码如下 | |
|
mysql> show create table tt7; 查看表的数据量
|
|
方法一：
直接更改存储引擎
| 代码如下 | |
| 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; |
|
补充一下
MySql中有哪些存储引擎？
1 MyISAM：这种引擎是mysql最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：
静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。
但是，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。
2 MyISAM Merge引擎：这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
3 InnoDB：InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。
4 memory(heap)：这种类型的数据表只存在于内存中。它使用散列索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。
5 archive：这种类型只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面。
