您现在的位置: 365建站网 > 365学习 > MySQL的MyISAM和InnoDB引擎互相转换的方法

MySQL的MyISAM和InnoDB引擎互相转换的方法

文章来源:365jz.com     点击数:179    更新时间:2020-04-09 21:22   参与评论

今天进入PHPMyAdmin维护数据库时意外发现一个数据库中有一些表使用的MyISAM,另一些表使用的InnoDB,左思右想没确定导致这种问题的原因,因为一般来说一个网站的数据库所有表应该使用同一种引擎,于是想着如何无损地将所有表转换成同一种引擎。

MySQL(或者社区开源fork的MariaDB)5.5以上支持InnoDB引擎,并将其作为默认数据库引擎。InnoDB带来很多改进,但是对系统资源占用明显增加,对于还在128MB~512MB内存VPS挂WordPress博客的筒子们来说,InnoDB是不合适的,还是得换回MyISAM这批老马!

从万能的搜索引擎了解到MyISAM和InnoDB引擎的区别如下。

MyISAM与InnoDB的区别
InnoDBMyISAM
支持事务处理等不支持事务处理等。
不加锁读取
支持外键不支持外键
支持行锁不支持行锁
不支持FULLTEXT类型的索引支持FULLTEXT类型的索引
不保存表的具体行数,扫描表来计算有多少行保存表的具体行数,不带where时,直接返回保存的行数。
DELETE表时,是一行一行地删除DELETE表时,先drop表,然后重建表
InnoDB把数据和索引存放在表空间里面MyISAM表被存放在三个文件,frm文件存放表格定义
跨平台可直接拷贝使用跨平台很难直接拷贝。
InnoDB中必须包含AUTO_INCREMENT类型字段的索引表格很难被压缩MyISAM中可以使用AUTO_INCREMENT类型字段建立联合索引,表格可以被压缩。


那么,我们如何选择?

MyISAM:应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高。

 建议zblog、dedecms、帝国cms、discuz、emlog之类对数据库读写不多的程序使用。

InnoDB:用于事务处理应用程序,支持外键,如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性。更新删除等频繁(InnoDB可以有效的降低由于删除和更新导致的锁定),对于数据准确性要求比较高的,此引擎适合。

建议Wordpress、joomla、phpBB、Drupal之类比较依赖数据库读写的程序使用。

我的网站使用drupal构建的选择InnoDB更合适,下面提供两种经过验证可用的MyISAM转InnoDB方法。

方法一、通过phpmyadmin命令转换。

以我的网站数据库为例。

MyISAM转InnoDB方法

首先登录phpMyAdmin,点“SQL”。

MyISAM转换成InnoDB引擎的两种方法

然后在SQL查询框中输入命令,ALTER TABLE `数据库表名` ENGINE = INNODB; (后面带小写;)例如转换图片中的ban_ip表,命令就是  ALTER TABLE `ban_ip` ENGINE = INNODB; 可一条一行,同时转换多条命令。如下图:

MyISAM转换成InnoDB引擎图文教程

接着点击查询框右下角的“执行”,命令执行成功如下:

MySQL的MyISAM转换成InnoDB引擎的两种方法

MyISAM转InnoDB查询引擎详细教程

如果执行不成功会有提示信息,检查命令的标点符号是否为英文半角输入,还有命令中的 '表名'前后不带空格。

方法二、使用SSH连接在linux中命令转换。

例如当前数据库用户名为root  数据库名为tuhongwei_com 数据库密码为123456 ,要转换的数据库表名为 ban_ip。

首先连接SSH,输入 mysql -uroot -p tuhongwei_com回车,输入数据库密码123456 回车(注意,在linux系统中输入密码屏幕不会提示),登录数据库成功后如下显示:

MySQL MyISAM转换成InnoDB引擎的两种方法

因为我用的MariaDB所以像上图这样显示,如果是MySQL 前面的MariaDB就显示MySQL。

然后输入命令 alter table ban_ip engine=innodb; (后面英文半角;不要忘记)回车,执行成功后如下提示:

MyISAM转InnoDB引擎图文教程

以上就是MyISAM转InnoDB的两种方法,反之InnoDB转MyISAM方法一样,无非是将命令中的engine=myisam。

查看表的存储引擎
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;

由于开发需要使用InnoDB引擎的事务功能,需要将原有的MyISAM引擎更换为InnoDB,InnoDB行级锁也可以避免MyISAM的锁表,操作流程如下:
查看mysql的存储引擎信息
show engines;
QQ图片20150527140352.png默认是MyISAM,需要修改为InnoDB;

设置mysql的默认存储引擎 在my.cnf中修改:
default-storage-engine=InnoDB

设置当前会话的默认存储引擎:
SET storage_engine=InnoDB;

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

1、从原mysql数据库fahao中导出表结构,不带数据
mysqldump -uxxx -p’xxx’ –no-data fahao > fahao.sql

2、在mysql中创建测试库fahao_test
create database fahao_test

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

4、在将fahao.sql表结构导入到测试库fahao_test中,并查看导入的表类型是不是InnoDB?
用source导入后,查看表类型方法:mysql> show table status like ‘fahao_name’\G

5、从原mysql数据库fahao中导出数据,不需要表结构
mysqldump -uxxx -p’xxxx’ -t fahao > fahao_data.sql 

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

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

怎么查看这些碎片?
显示你数据库中存在碎片的全部列表:
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

查看单个表的详细信息: 表类型是否InnoDB,是否有数据碎片
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=’table_name’\G

MySQL提供了一种简便的修正方法,这就是所谓的优化列表(优化表空间,减少数据碎片,释放表空间)
对MyISAM、InnoDB引擎的表格有用,在InnoDB表上面执行会出现下面的提示:虽然提示不支持,optimize,但是已经进行重建和分析,空间已经回收.
optimize table table_name;
clipboard

修改表的存储引擎时,会重建表,结构文件、数据文件、索引文件等文件,这种方式从原理上,感觉可以,但是测试并没有成功。
ALTER TABLE table_name ENGINE=’InnoDB’;
Query OK, 2919 rows affected (5.92 sec)
Records: 2919 Duplicates: 0 Warnings: 0
Data_free: 5242880 有变化
有一定的作用,但无法完全释放出表空间,使Data_free为零

如果需要检查并修复所有的数据库的数据表,那么可以使用:
/usr/local/mysql/bin/mysqlcheck -uroot -p -o -A
如果需要修复指定的数据库用
mysqlcheck -uxx -p fahao_test
测试没有效果,Data_free: 4194304

以上三种方法都测试过,但都无法将所有数据回收,Data_free无法为零,
尝试将表单独mysqldump出后,drop掉表,然后重新source导入dump的表,结果无效,仍然有 Data_free: 4194304

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

如果在实际业务中,确实需要在InnoDB表中存储BLOB、TEXT、长VARCHAR列时,有下面几点建议:
1、尽可能将所有数据序列化、压缩之后,存储在同一个列里,避免发生多次off-page;
2、实际最大存储长度低于255的列,转成VARCHAR或者CHAR类型(如果是变长数据二者没区别,如果是定长数据,则使用CHAR类型);
3、如果无法将所有列整合到一个列,可以退而求其次,根据每个列最大长度进行排列组合后拆分成多个子表,尽量是的每个子表的总行长度小于8KB,减少发生off-page的频率;
4、上述建议是在data page为默认的16KB前提下,如果修改成8KB或者其他大小,请自行根据上述理论进行测试,找到最合适的值;
5、字符型列长度小于255时,无论采用CHAR还是VARCHAR来存储,或者把VARCHAR列长度定义为255,都不会导致实际表空间增大;
6、一般在游戏领域会用到比较多的BLOB列类型,游戏界同行可以关注下。

PS:
更换成InnoDB后最好做成独立表空间,编辑my.cnf在innodb段中增加innodb_file_per_table = 1(1为启用,0为禁用)配置参数,这样InnoDB会对每个表创建一个数据文件,然后只需要运行OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。
通过mysql语句可以查看该变量的值:mysql> show variables like ‘%per_table%’;



如对本文有疑问,请提交到交流论坛,广大热心网友会为你解答!! 点击进入论坛


发表评论 (179人查看0条评论)
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
用户名: 验证码: 点击我更换图片
最新评论
------分隔线----------------------------