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';

   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';

查询表引擎（查看MYISAM）
SELECT CONCAT(table_name,'  ', engine)   FROM information_schema.tables WHERE table_schema="bpm" AND ENGINE="MyISAM";  

生成修改sql（把MYISAM改成INNODB）
select CONCAT('alter table ',table_name,' engine=InnoDB;') FROM information_schema.tables WHERE table_schema="bpm" AND ENGINE="MyISAM"; 

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

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

　　我作为使用MySQL的用户角度出发，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,但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表架构来解决。

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

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

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