修改其他表为INNODB 表很简单一条语句即可

alter table table_name engine=innodb

mysql> show table status like 'u1'\G

*************************** 1. row ***************************

Name: u1

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 131072

Avg_row_length: 55

Data_length: 7340024

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2015-02-05 06:38:12

Update_time: 2015-02-05 06:38:12

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

mysql> show table status like 'u1'\G

*************************** 1. row ***************************

Name: u1

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 130732

Avg_row_length: 84

Data_length: 11026432

Max_data_length: 0

Index_length: 0

Data_free: 4194304

Auto_increment: NULL

Create_time: 2015-02-26 08:02:29

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

注意红色部分同等数据量innodb 所占的磁盘要bimyisam 所占磁盘的空间要多

将u1变回myisam 引擎表

mysql> alter table u1 engine=myisam;

Query OK, 131072 rows affected (0.31 sec)

Records: 131072 Duplicates: 0 Warnings: 0

mysql> show table status like 'u1'\G

*************************** 1. row ***************************

Name: u1

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 131072

Avg_row_length: 55

Data_length: 7340024

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2015-02-26 08:05:47

Update_time: 2015-02-26 08:05:47

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

所占空间又变回去了。

ITPub博客

修改其他引擎表为INNODB 表

最新文章