mysqlinnodbmyisam,mysql从MyISAM迁移到InnoDB引擎过程及优化

由于开发需要使用InnoDB引擎的事务功能&＃xff0c;需要将原有的MyISAM引擎更换为InnoDB&＃xff0c;InnoDB行级锁也可以避免MyISAM的锁表&＃xff0c;操作流程如下&＃xff1a;

查看mysql的存储引擎信息

show engines;

默认是MyISAM&＃xff0c;需要修改为InnoDB;

设置mysql的默认存储引擎 在my.cnf中修改:

default-storage-engine&＃61;InnoDB

设置当前会话的默认存储引擎:

SET storage_engine&＃61;InnoDB;

然后再show engines可以看到默认引擎是InnoDB了&＃xff0c;然后再将原数据库中的表从MyISAM库转换成InnoDB,具体操作如下&＃xff1a;

1、从原mysql数据库fahao中导出表结构&＃xff0c;不带数据

mysqldump -uxxx -p’xxx’ –no-data fahao > fahao.sql

2、在mysql中创建测试库fahao_test

create database fahao_test

3、在导出的表结构fahao.sql中找到ENGINE&＃61;MyISAM DEFAULT CHARSET&＃61;utf8;修改成ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8;

4、在将fahao.sql表结构导入到测试库fahao_test中&＃xff0c;并查看导入的表类型是不是InnoDB&＃xff1f;

用source导入后&＃xff0c;查看表类型方法&＃xff1a;mysql> show table status like ‘fahao_name’\G

5、从原mysql数据库fahao中导出数据&＃xff0c;不需要表结构

mysqldump -uxxx -p’xxxx’ -t fahao > fahao_data.sql

6、fahao_data.sql导入到测试库fahao_test中

至此fahao库的表从MyISAM引擎转换成InnoDB了&＃xff0c;但是有一个问题&＃xff0c;查看表的详细信息时发现Data_free不为零&＃xff0c;说明存在数据碎片&＃xff0c;需要进行优化&＃xff0c;在网上查询资料&＃xff0c;发现有如下的方法.

怎么查看这些碎片&＃xff1f;

显示你数据库中存在碎片的全部列表:

select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in (‘information_schema’, ‘mysql’) and data_free > 0;

查看fahao_test数据库中所有表的详细信息

mysql>show table status from fahao_test\G

查看单个表的详细信息&＃xff1a; 表类型是否InnoDB&＃xff0c;是否有数据碎片

mysql> show table status from fahao_test like ‘table_name’\G

mysql> show table status like ‘table_name’\G

mysql> show table status from fahao_test where name&＃61;’table_name’\G

MySQL提供了一种简便的修正方法,这就是所谓的优化列表(优化表空间&＃xff0c;减少数据碎片&＃xff0c;释放表空间)

对MyISAM、InnoDB引擎的表格有用&＃xff0c;在InnoDB表上面执行会出现下面的提示&＃xff1a;虽然提示不支持&＃xff0c;optimize&＃xff0c;但是已经进行重建和分析&＃xff0c;空间已经回收.

optimize table table_name;

修改表的存储引擎时&＃xff0c;会重建表&＃xff0c;结构文件、数据文件、索引文件等文件,这种方式从原理上&＃xff0c;感觉可以&＃xff0c;但是测试并没有成功。

ALTER TABLE table_name ENGINE&＃61;’InnoDB’;

Query OK, 2919 rows affected (5.92 sec)

Records: 2919 Duplicates: 0 Warnings: 0

Data_free: 5242880 有变化

有一定的作用&＃xff0c;但无法完全释放出表空间&＃xff0c;使Data_free为零

如果需要检查并修复所有的数据库的数据表&＃xff0c;那么可以使用&＃xff1a;

/usr/local/mysql/bin/mysqlcheck -uroot -p -o -A

如果需要修复指定的数据库用

mysqlcheck -uxx -p fahao_test

测试没有效果&＃xff0c;Data_free: 4194304

以上三种方法都测试过&＃xff0c;但都无法将所有数据回收&＃xff0c;Data_free无法为零&＃xff0c;

尝试将表单独mysqldump出后&＃xff0c;drop掉表&＃xff0c;然后重新source导入dump的表&＃xff0c;结果无效&＃xff0c;仍然有 Data_free: 4194304

最后查询资料为什么会有Data_free才发现&＃xff0c;跟表结构、字段长度的设置、字段类型、data page都有关系&＃xff0c;没有合理设置这些都会导致数据碎片&＃xff0c;无法充分利用表空间.如果一定要将Data_free优化为零&＃xff0c;需要对整个表进行优化才行&＃xff0c;以下是借用别人的优化方法.

如果在实际业务中&＃xff0c;确实需要在InnoDB表中存储BLOB、TEXT、长VARCHAR列时&＃xff0c;有下面几点建议&＃xff1a;

1、尽可能将所有数据序列化、压缩之后&＃xff0c;存储在同一个列里&＃xff0c;避免发生多次off-page&＃xff1b;

2、实际最大存储长度低于255的列&＃xff0c;转成VARCHAR或者CHAR类型(如果是变长数据二者没区别&＃xff0c;如果是定长数据&＃xff0c;则使用CHAR类型)&＃xff1b;

3、如果无法将所有列整合到一个列&＃xff0c;可以退而求其次&＃xff0c;根据每个列最大长度进行排列组合后拆分成多个子表&＃xff0c;尽量是的每个子表的总行长度小于8KB&＃xff0c;减少发生off-page的频率&＃xff1b;

4、上述建议是在data page为默认的16KB前提下&＃xff0c;如果修改成8KB或者其他大小&＃xff0c;请自行根据上述理论进行测试&＃xff0c;找到最合适的值&＃xff1b;

5、字符型列长度小于255时&＃xff0c;无论采用CHAR还是VARCHAR来存储&＃xff0c;或者把VARCHAR列长度定义为255&＃xff0c;都不会导致实际表空间增大&＃xff1b;

6、一般在游戏领域会用到比较多的BLOB列类型&＃xff0c;游戏界同行可以关注下。

PS&＃xff1a;

更换成InnoDB后最好做成独立表空间&＃xff0c;编辑my.cnf在innodb段中增加innodb_file_per_table &＃61; 1(1为启用&＃xff0c;0为禁用)配置参数,这样InnoDB会对每个表创建一个数据文件&＃xff0c;然后只需要运行OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。

通过mysql语句可以查看该变量的值&＃xff1a;mysql> show variables like ‘%per_table%’;

版权属于:

运维之道

转载时必须以链接形式注明原始出处及本声明。