MySQL数据库修改数据表引擎

浅浅的花香味﹌ 2024-02-18 22:59 46阅读 0赞

**mysql 表类型说明**

MyISAM：这个是默认类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的 顺序访问方法) 的缩写，它是存储记录和文件的标准方法。与其他存储引擎比较，MyISAM具有检查和修复表格的大多数工具。 MyISAM表格可以被压缩，而且它们支持全文搜索。它们不是事务安全的，而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量 的SELECT，MyISAM是更好的选择。

InnoDB：这种类型是事务安全的。它与BDB类型具有相同的特性，它们还支持外键。InnoDB表格速度很快，具有比BDB还丰富的特性，因此如果需要一个事务安全的存储引擎，建议使用它。如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。

对于支持事物的InnoDB类型的标，影响速度的主要原因是AUTOCOMMI，默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打 开也可以），将大大提高性能。

1、查看表状态

SHOW TABLE STATUS LIKE 'corr\_alert';

![20180427165937882][]

Engine：引擎类型（旧版为type）；

Row\_format：行的格式。可选值Dynamic、Fixed或Compressed；

Dynamic行长度可变，一般包含可变长度字段，varchar或Bolb

Fixed：长度固定

Compressed：只在压缩表中存在

collation：默认字符集和字符列排序规则；

checkSum：如果启用，保存的整个表的实时校验和；

Comment：注释或表空间剩余信息；视图则为VIEW文本字样；

2、修改表引擎

目前有三种方式进行切换：

1）ALTER TABLE语句：

ALTER TABLE corr\_alert\_copy ENGINE=INNODB;

优点：高效，按行复制表记录到新表中

缺点：可能消耗系统所有I/O，原表加锁

2）手工导入与导出

使用MYSQL自带工具mysqldump导出到文件，修改文件中的CREATE TABLE引擎语句，同时修改表名，且默认在create table前加上drop table语句

3）创建与查询

CREATE TABLE corr\_alert\_copy LIKE corr\_alert;

ALTER TABLE corr\_alert\_copy ENGINE=INNODB;

INSERT INTO corr\_alert\_copy SELECT \* FROM corr\_alert;

数据量不大，工作很好；数据量大，分批处理，每一段数据一个事务，避免大事务产生的undo操作；

START TRANSACTION

INSERT INTO corr\_alert\_copy select \* from corr\_alert WHERE id BETWEEN x AND y;

COMMIT;

优点：原表还在，可在执行过程中对原表加锁，确保数据一致，操作简单；

案例1：需要把100张表的引擎，从InnoDB改为MYISAM。想到要手工执行，简直头大，还好有存储过程。可以利用存储过程来批量处理。

\-- 定义存储过程  
DELIMITER //  
CREATE PROCEDURE alter\_table\_enegine()  
BEGIN  
DECLARE \`@i\` INT(11);  
DECLARE \`@sqlstr\` VARCHAR(2560);  
SET \`@i\`=0;  
WHILE \`@i\` < 100 DO  
SET @sqlstr = CONCAT(  
"ALTER TABLE pt\_course\_",  
\`@i\`,  
" ENGINE=MYISAM"  
);  
PREPARE stmt FROM @sqlstr;  
EXECUTE stmt;  
SET \`@i\` = \`@i\` + 1;  
END WHILE;  
END;  
\-- 执行存储过程  
CALL alter\_table\_enegine();  
\-- 执行完，删掉临时用的存储过程  
DROP PROCEDURE alter\_table\_enegine;

案例2：

查询数据库有什么引擎：

select table\_name,\`engine\` from information\_schema.tables where table\_schema = 'database\_name';

![70][]

查询表引擎（查看MYISAM）  
SELECT CONCAT(table\_name,' ', engine) FROM information\_schema.tables WHERE table\_schema="bpm" AND ENGINE="MyISAM";

![70 1][]  
生成修改sql（把MYISAM改成INNODB）  
select CONCAT('alter table ',table\_name,' engine=InnoDB;') FROM information\_schema.tables WHERE table\_schema="bpm" AND ENGINE="MyISAM";

![70 2][]

注:Mysql 建表用 MyISAM不用InnoDB(大数据高读取)原因

MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级[数据库][Link 1]功能。两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁.而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。

我作为使用[MySQL][Link 1]的用户角度出发，Innodb和MyISAM都是比较喜欢的，但是从我目前运维的数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，MyISAM绝对是我的首选。

原因如下：

1、首先我目前平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。

2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

3、从平台角度来说，经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时 候MyISAM的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应 的binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出xxx.sql机制备份，因为我平台上最小的一个数 据库实例的数据量基本都是几十G大小。

4、从我接触的应用逻辑来说，select count(\*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是 where对它主键是有效，非主键的都会锁全表的。

5、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文 件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

6、如果和MyISAM比insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，虽然MyISAM可能会逊色Innodb,但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表[架构][Link 2]来解决。

7、如果是用MyISAM的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些select count(\*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查统计)的业务表。

当然Innodb也不是绝对不用，用事务的项目如模拟炒股项目，我就是用Innodb的，活跃用户20多万时候，也是很轻松应付了，因此我个人也是很喜欢Innodb的，只是如果从数据库平台应用出发，我还是会首选MyISAM。

另外，可能有人会说你MyISAM无法抗太多写操作，但是我可以通过架构来弥补，说个我现有用的数据库平台容量：主从数据总量在几百T以上，每 天十多亿 pv的动态页面，还有几个大项目是通过数据接口方式调用未算进pv总数，(其中包括一个大项目因为初期memcached没部署,导致单台数据库每天处理 9千万的查询)。而我的整体数据库服务器平均负载都在0.5-1左右。

[20180427165937882]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6113d2fd34904ba797715063e333d2aa.png
[70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5d3fd93806414bffa815dcf1e40f0439.png
[70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8f906709a20b478199b63cffa64c06bb.png
[70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/94c03dbd0ab2460686455bfb51449343.png
[Link 1]: http://lib.csdn.net/base/14
[Link 2]: http://lib.csdn.net/base/16