InnoDB占用磁盘是MyISAM的几倍，这是什么原因？

Question

下面是我的操作流程，TestInfo_0是新建的表，先采用InnoDB，插入100条记录，然后改为MyISAM，再改回InnoDB，发现InnoDB记录大小要大很多，这是什么原因呢？是不是因为BLOB数量多引起的？====================================mysql> use testDB;
Database changed
mysql> show tables;      
+------------------+
| Tables_in_testDB |
+------------------+
| TestInfo_0       | 
+------------------+
1 row in set (0.00 sec)mysql> select TABLE_NAME, ENGINE, AVG_ROW_LENGTH, TABLE_ROWS, DATA_LENGTH from information_schema.TABLES where TABLE_SCHEMA="testDB";
+------------+--------+----------------+------------+-------------+
| TABLE_NAME | ENGINE | AVG_ROW_LENGTH | TABLE_ROWS | DATA_LENGTH |
+------------+--------+----------------+------------+-------------+
| TestInfo_0 | InnoDB |              0 |          0 |       16384 | 
+------------+--------+----------------+------------+-------------+
1 row in set (0.00 sec)-->插入100条记录mysql> select TABLE_NAME, ENGINE, AVG_ROW_LENGTH, TABLE_ROWS, DATA_LENGTH from information_schema.TABLES where TABLE_SCHEMA="testDB";
+------------+--------+----------------+------------+-------------+
| TABLE_NAME | ENGINE | AVG_ROW_LENGTH | TABLE_ROWS | DATA_LENGTH |
+------------+--------+----------------+------------+-------------+
| TestInfo_0 | InnoDB |          47349 |        100 |     4734976 | 
+------------+--------+----------------+------------+-------------+
1 row in set (0.00 sec)mysql> alter table TestInfo_0 type=MyISAM;
Query OK, 100 rows affected, 1 warning (0.11 sec)
Records: 100  Duplicates: 0  Warnings: 0mysql> select TABLE_NAME, ENGINE, AVG_ROW_LENGTH, TABLE_ROWS, DATA_LENGTH from information_schema.TABLES where TABLE_SCHEMA="testDB";
+------------+--------+----------------+------------+-------------+
| TABLE_NAME | ENGINE | AVG_ROW_LENGTH | TABLE_ROWS | DATA_LENGTH |
+------------+--------+----------------+------------+-------------+
| TestInfo_0 | MyISAM |          11472 |        100 |     1147200 | 
+------------+--------+----------------+------------+-------------+
1 row in set (0.00 sec)mysql> alter table TestInfo_0 type=InnoDB;
Query OK, 100 rows affected, 1 warning (0.12 sec)
Records: 100  Duplicates: 0  Warnings: 0mysql> select TABLE_NAME, ENGINE, AVG_ROW_LENGTH, TABLE_ROWS, DATA_LENGTH from information_schema.TABLES where TABLE_SCHEMA="testDB";
+------------+--------+----------------+------------+-------------+
| TABLE_NAME | ENGINE | AVG_ROW_LENGTH | TABLE_ROWS | DATA_LENGTH |
+------------+--------+----------------+------------+-------------+
| TestInfo_0 | InnoDB |          47349 |        100 |     4734976 | 
+------------+--------+----------------+------------+-------------+
1 row in set (0.00 sec)

Answer 1

InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

Answer 2

InnoDB查起来比较慢，myisam较快了

Answer 3

MYISAM的读速度比INNODB的快！写比INNODB的慢！

Answer 4

MYISAM的索引会采用前缀压缩，故索引比innodb要小得多（针对字符串列）。

Answer 5

这是原理上的不同，但从结果来看，InnoDB占用的空间就是要大几倍。难道会是统计误差？我尝试插入100万条记录，InnoDB占用的磁盘空间确实是MyISAM的几倍。实际上我的记录大小大概是11k左右，插入100万条的时候，MyISAM数据文件是11G多，而InnoDB已经到40多G。

Answer 6

MYISAM的读速度比INNODB的快！写比INNODB的慢！
--------
   我在单线程批量插入大量数据的时候，MyISAM性能明显比InnoDB要强，InnoDB磁盘读写压力很大。所谓的
MYISAM的索引会采用前缀压缩，故索引比innodb要小得多（针对字符串列）。
-------------
     | TestInfo_0 | CREATE TABLE `TestInfo_0` (
  `GID` bigint(20) unsigned NOT NULL,
  `Name` varchar(32) default NULL,
  `CreateTime` datetime default NULL,
  `BirthZone` tinyint(4) default NULL,
  `LastLoginZone` tinyint(4) default '0',
  `Gender` tinyint(4) default NULL,
  `Prof` tinyint(4) default NULL,
  `Level` smallint(6) default NULL,
  `XP` int(11) default NULL,
  `Bld` smallint(6) default NULL,
  `Hab` smallint(6) default NULL,
  `Str` smallint(6) default NULL,
  `Int_` smallint(6) default NULL,
  `LeftAP` smallint(6) default NULL,
  `LeftSP` smallint(6) default NULL,
  `CurHP` int(11) default NULL,
  `CurMP` int(11) default NULL,
  `CurEP` int(11) default NULL,
  `Honor` int(11) default NULL,
  `Charm` int(11) default NULL,
  `PworldInstID` bigint(20) unsigned default NULL,
  `EnterMapID` smallint(5) unsigned default NULL,
  `EnterX` int(11) default NULL,
  `EnterY` int(11) default NULL,
  `LastEnterTime` int(11) default NULL,
  `EnterLimit` smallint(5) unsigned default NULL,
  `MapID` smallint(5) unsigned default NULL,
  `X` int(11) default NULL,
  `Y` int(11) default NULL,
  `HairStyle` tinyint(4) default NULL,
  `HeadImage` int(11) default NULL,
  `RoleItemData` blob,
  `RoleDealData` blob,
  `RoleACData` tinyblob,
  `RoleXP2` tinyblob,
  `RoleAssisInfo` blob,
  `UpdateTime` datetime default NULL,
  `Res` tinyint(4) default NULL,
  `Uin` int(10) unsigned default NULL,
  `CurrentActivePet` tinyint(4) default NULL,
  `TaskData` blob,
  `TeamData` tinyblob,
  `Skill` blob,
  `Cooldown` tinyblob,
  `SpecStat` blob,
  `OnlineTime` int(11) default NULL,
  `LoginCount` int(11) default NULL,
  `PKValue` int(11) default NULL,
  `RolePworldData` blob,
  `GuildForRole` tinyblob,
  `GMPower` tinyint(3) unsigned default NULL,
  `IsDelete` tinyint(3) unsigned default NULL,
  `DeleteTime` datetime default NULL,
  `Reputation` blob,
  `LastLogoutTime` datetime default '2009-04-18 00:00:00',
  `BattlePower` int(11) default NULL,
  `BlockCode` tinyint(3) unsigned default NULL,
  `BlockEndTime` datetime default NULL,
  `UseBuyInfo` blob,
  `DailyCounter` blob,
  `MailReadTime` blob,
  PRIMARY KEY  (`GID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
这是我的建表语句，按理说索引占用空间很小。

Answer 7

你去data目录下对应的数据库下可以看看TestInfo_0.MYI索引文件多大吧

Answer 8

MYISAM的读速度比INNODB的快！写比INNODB的慢！ 
-------- 
  我在单线程批量插入大量数据的时候，MyISAM性能明显比InnoDB要强，InnoDB磁盘读写压力很大。打开事务没有？InnoDB支持事务

Answer 9

如果是单是数据文件的话，一般myisam也是比innodb小点的
至于里面的原因，我猜可能是底层里面存储页填充的比例不一样的缘故吧至于索引呢，myisam会对索引采用前缀压缩的，所以，自然会比innodb的索引要小。

Answer 10

你去data目录下对应的数据库下可以看看TestInfo_0.MYI索引文件多大吧
 ---- MyISAM 大小是从这里看的，正常
打开事务没有？InnoDB支持事务
 ---- 没有打开事务， InnoDB是不是缓写磁盘，在批量的时候缓冲区不够导致写磁盘压力大?

Answer 11

针对的都不一样。myISAM是小型的，单一的，备份要锁定表的。
InnoDB是大型的，多cpu并行，有回滚事务安全，备份能和oracle一样，只锁定行。简单的说。如果你有个网游（如：传奇），你用myisam引擎，备份时，很可能所有人都会被踢出游戏。如果用INNODB备份就不会有这个问题。

Answer 12

针对的都不一样。 myISAM是小型的，单一的，备份要锁定表的。 
InnoDB是大型的，多cpu并行，有回滚事务安全，备份能和oracle一样，只锁定行。 简单的说。如果你有个网游（如：传奇），你用myisam引擎，备份时，很可能所有人都会被踢出游戏。如果用INNODB备份就不会有这个问题。 ---------
   InnoDB的优势在于事务和并发，这个我理解。只是占用的空间要大这么多出乎我的意料，按经验值应该是1.x : 1 这样的差距。 

Answer 13

正常？
能贴出myisam下3个文件的大小吗？

Answer 14

正常？ 
能贴出myisam下3个文件的大小吗？---------------
我现在只有100条记录这个是InnoDB引擎是的状态
root@Tencent:/data/mysql/testDB# ll
总计 16
-rw-rw---- 1 mysql mysql    65 2009-10-14 16:37 db.opt
-rw-rw---- 1 mysql mysql 10974 2009-10-14 16:43 TestInfo_0.frm这个是MyISAM引擎是的状态，平均就是11k的样子
root@Tencent:/data/mysql/testDB# ll
总计 1148
-rw-rw---- 1 mysql mysql      65 2009-10-14 16:37 db.opt
-rw-rw---- 1 mysql mysql   10974 2009-10-14 18:07 TestInfo_0.frm
-rw-rw---- 1 mysql mysql 1147200 2009-10-14 18:07 TestInfo_0.MYD
-rw-rw---- 1 mysql mysql    4096 2009-10-14 18:07 TestInfo_0.MYI
root@Tencent:/data/mysql/testDB# 

Answer 15

你看看这个就晓得了。
http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html我的估计理解是这样的。
myisam是可以压缩表的。因为他是基于通过磁盘io来存储的，瓶颈在磁盘IO上。所以传送的数据越小越好。
innodb是直接在内存中虚拟数据库的全表，然后统一缓存磁盘存储。对INNODB来说，瓶颈不再是磁盘io了，CPU为了更好的利用起来，也就不需要再去压缩数据，

Answer 16

InnoDB 所有的表，索引，都在一个文件ibdata中，类似一个 tablespace ，并且，这个文件是一开始就分配的，哪怕你一条记录都没有，它也会是那么大。随着记录的增长，当文件尺寸不足的时候，MYSQL会根据你的设置对这个表空间文件进行自动扩展。而myisam 则一个表有三个文件，frm 表定义文件， myd 数据文件 myi 索引文件。并且是每个表都是单独的文件。这一点和innodb完全不同。
建议你可以先看一下下面的官方文档说明。
15.2.1. InnoDB概述
http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb-overview
InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。 
15.2.3. InnoDB配置
InnoDB存储引擎是默认地被允许的。如果你不想用InnoDB表，你可以添加skip-innodb选项到MySQL选项文件。被InnoDB存储引擎管理的两个重要的基于磁盘的资源是InnoDB表空间数据文件和它的日志文件。如果你指定无InnoDB配置选项，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#myisam-storage-engine
15.1. MyISAM存储引擎
MyISAM是默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。（注意MySQL 5.1不支持ISAM）。 每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 

Answer 17

嘿嘿，看运行中的文件大小，反而是myisam大太多了。

Answer 18

嘿嘿，看运行中的文件大小，反而是myisam大太多了。
   ------ InnoDB的数据并没有放在自己独立的目录下啊。

Answer 19

mysql 配置：
datadir = /data/mysql/
innodb_data_home_dir = /data/mysql/
innodb_data_file_path = ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend
数据文件（由于之前连续插入了100万条记录，空间增长到40多G，所以ibdata4被扩展了。删除记录之后，数据文件并没有减小）：
-rw-rw---- 1 mysql mysql 10737418240 2009-10-14 18:12 ibdata1
-rw-rw---- 1 mysql mysql 10737418240 2009-10-14 16:43 ibdata2
-rw-rw---- 1 mysql mysql 10737418240 2009-10-14 18:12 ibdata3
-rw-rw---- 1 mysql mysql 18035507200 2009-10-14 18:12 ibdata4

Answer 20

无论是从数据文件来看，还是Mysql本身的统计信息来看，InnoDB平均占用40k多，而MyISAM占用10k多。会不会和blob字段多引起的？

Answer 21

删除记录内存其实并不是真实删除数据 是在数据中做个标识位 表示该数据不可见我们要经常trancate table

Answer 22

数据文件（由于之前连续插入了100万条记录，空间增长到40多G，所以ibdata4被扩展了。删除记录之后，数据文件并没有减小）：1. 用mysqldump等工具导出数据
2. 停止 mysqld
3. 删除ibdata*, ib_logfile* 文件
4. 重新启动 mysqld（这时mysqld就会自动创建 idbdata*, ib_logfile* 文件）
5. 将到出来的数据导回去，体积才会减小。 

Answer 23

innodb有2种类型的表空间
1、集中式表空间（默认方式），所有的表和索引存储在一个文件里
2、每表表空间（innodb_per_table好象是这设置），每个表和索引单独一个文件请楼主将表空间形式改为第二种，再比较下

Answer 24

因为INNODB 是有事务的，而MYISAM没有。 所以INNODB需要写到磁盘的东西比MYISAM大多了。

Answer 25

这个跟文件个数的多少没什么关系
关键的是myisam是采用存储压缩的，这是最主要的区别地方

Answer 26

 InnoDB I/O Subsystem Changes
MySQL 5.4 changes to the InnoDB I/O subsystem enable more effective use of available I/O capacity. The changes also provide more control over configuration of the I/O subsystem. 从发展策略上看 mysql推崇 InnoDB，想必今后会在这种格式下大作文章，其它的格式可能会丢掉一些。

Answer 27

Innodb 去掉事务 速度也不错