关于InnoDB和MyISAM数据存储特性的研究

InnoDB和MyISAM是MySQL最重要的两种数据存储引擎，两者都可用来存储表和索引，InnoDB的索引和表存储在同一个文件中，MyISAM的索引和表存储在不同文件，但即使多个索引，也共存于一个文件。

　　网络上很多比较都是基于两者的事务性处理和锁处理机制等，似乎InnoDB比MyISAM没有缺点了。但研究发现，MyISAM在数据特别是索引存储方面具有突出的优势，下面就此开展讨论。

　　1. 研究结论

　　1)InnoDB占用磁盘空间比MyISAM大，MyISAM存储数据可节省空间12%，存储索引可节省95%;

　　2)InnoDB对空闲存储空间的使用不优。

　　研究发现，MyISAM可大量节省磁盘空间，特别是对索引的存储上，优势巨大，这对大型MySQL数据库的数据表和索引的物理设计，具有较大的指导意义。

　　2. 研究对象及获得的数据

　   MySQL版本：5.1.26-rc，平台：RHEL 4，Linux 2.6.9-67.ELsmp x86_64

　　研究对象为创建的一个表，mytable3，初始为InnoDB类型。有54万行非重复数据(用随机函数产生)，两个索引。共进行两个实验，获得的实验数据如下。

	表占用_MB	索引占用_MB	InnoDB文件占用_MB	MyISAM文件占用_MB
初始类型InnoDB	256	240	914	0
类型改为MyISAM	222	8	914	230
类型改回InnoDB	256	240	1034	0

　

　   从实验数据可以看出，表类型alter为MyISAM后，所占磁盘空间仅8MB，为InnoDB的4%。而且随着表类型改回InnoDB，InnoDB表空间被迫扩充120MB，达到1034MB，以支持该表数据的回迁。

 

     3. 实验过程

　　--实验用表：mytable3， InnoDB类型。

　　表有三个列，52万行数据，后两个列用随机函数产生，重复行很少。都创建了索引。此时该表数据占用空间256MB，索引占用空间240MB，InnoDB表空间914MB。

mysql> desc mytable3; 　　+-------+-----------+------+-----+---------+-------+ 　　| Field | Type | Null | Key | Default | Extra | 　　+-------+-----------+------+-----+---------+-------+ 　　| id | int(11) | YES | | NULL | | 　　| name | char(100) | YES | MUL | NULL | | 　　| name2 | char(120) | YES | MUL | NULL | | 　　+-------+-----------+------+-----+---------+-------+ 　　3 rows in set (0.00 sec) 　　mysql> select count(*) from mytable3; 　　+----------+ 　　| count(*) | 　　+----------+ 　　| 524288 | 　　+----------+ 　　1 row in set (0.57 sec) 　　mysql> select table_name, table_schema, data_length, index_length from information_schema.tables where table_name = 'mytable3'; 　　+------------+--------------+-------------+--------------+ 　　| table_name | table_schema | data_length | index_length | 　　+------------+--------------+-------------+--------------+ 　　| mytable3 | testbk | 269271040 | 252706816 | 　　+------------+--------------+-------------+--------------+ 　　1 row in set (0.00 sec) 　　[mysql@testlocal data]$ ls -ltr 　　total 947188 　　drwxr-xr-x 2 mysql mysql 4096 Aug 19 09:53 test 　　drwx--x--x 2 mysql mysql 4096 Aug 19 09:53 mysql 　　-rw-rw---- 1 mysql mysql 125 Aug 19 10:43 mysql-bin.000001 　　-rw-rw---- 1 mysql mysql 125 Aug 19 10:43 mysql-bin.000002 　　-rw-rw---- 1 mysql mysql 1086 Aug 19 17:18 mysql-bin.000003 　　-rw-rw---- 1 mysql mysql 693 Aug 19 20:51 mysql-bin.000004 　　-rw-rw---- 1 mysql mysql 125 Aug 19 20:54 mysql-bin.000005 　　-rw-rw---- 1 mysql mysql 125 Aug 19 20:58 mysql-bin.000006 　　-rw-rw---- 1 mysql mysql 6 Aug 19 20:59 testlocal.sohu.com.pid 　　srwxrwxrwx 1 mysql mysql 0 Aug 19 20:59 mysql.sock 　　-rw-rw---- 1 mysql mysql 133 Aug 19 20:59 mysql-bin.index 　　-rw-r----- 1 mysql mysql 12432 Aug 21 11:20 testlocal.sohu.com.err 　　drwx------ 2 mysql mysql 4096 Aug 21 11:22 testbk 　　-rw-rw---- 1 mysql mysql 5010 Aug 21 11:22 mysql-bin.000007 　　-rw-r----- 1 mysql mysql 5242880 Aug 21 11:22 ib_logfile1 　　-rw-r----- 1 mysql mysql 5242880 Aug 21 11:22 ib_logfile0 　　-rw-r----- 1 mysql mysql 958398464 Aug 21 11:22 ibdata1

　　--将该表改变类型为MyISAM

　　发现InnoDB表空间未回缩，但MyISAM占用磁盘容量减少为仅约8MB。此时该表数据占用空间222MB，索引占用空间8MB，InnoDB表空间914MB。　

mysql> alter table mytable3 engine myisam; 　　Query OK, 524288 rows affected (8.51 sec) 　　Records: 524288 Duplicates: 0 Warnings: 0 　　mysql> select table_name, table_schema, data_length, index_length from information_schema.tables where table_name = 'mytable3'; 　　+------------+--------------+-------------+--------------+ 　　| table_name | table_schema | data_length | index_length | 　　+------------+--------------+-------------+--------------+ 　　| mytable3 | testbk | 233308160 | 8528896 | 　　+------------+--------------+-------------+--------------+ 　　1 row in set (0.00 sec) 　　[mysql@testlocal testbk]$ ls -ltr 　　total 236456 　　-rw-rw-r-- 1 mysql mysql 3307 Aug 19 12:30 bak_testbk 　　-rw-rw---- 1 mysql mysql 8556 Aug 19 19:35 mytable.frm 　　-rw-rw---- 1 mysql mysql 8556 Aug 19 20:43 mytable2.frm 　　-rw-rw---- 1 mysql mysql 8618 Aug 21 11:32 mytable3.frm 　　-rw-rw---- 1 mysql mysql 233308160 Aug 21 11:32 mytable3.MYD 　　-rw-rw---- 1 mysql mysql 8528896 Aug 21 11:32 mytable3.MYI

　　--重新改回InnoDB

　　发现InnoDB表空间又扩充了。此时该表数据占用空间256MB，索引占用空间240MB，InnoDB表空间1034MB

mysql> alter table mytable3 engine innodb;         Query OK, 524288 rows affected (17.41 sec)         Records: 524288  Duplicates: 0  Warnings: 0   mysql> select table_name, table_schema, data_length, index_length from information_schema.tables where table_name = 'mytable3';          +------------+--------------+-------------+--------------+          | table_name | table_schema | data_length | index_length |          +------------+--------------+-------------+--------------+          | mytable3   | testbk       |   269271040 |    252706816 |           +------------+--------------+-------------+--------------+          1 row in set (0.00 sec)   [mysql@testlocal data]$ ls -ltr total 1070188 drwxr-xr-x  2 mysql mysql       4096 Aug 19 09:53 test drwx--x--x  2 mysql mysql       4096 Aug 19 09:53 mysql -rw-rw----  1 mysql mysql        125 Aug 19 10:43 mysql-bin.000001 -rw-rw----  1 mysql mysql        125 Aug 19 10:43 mysql-bin.000002 -rw-rw----  1 mysql mysql       1086 Aug 19 17:18 mysql-bin.000003 -rw-rw----  1 mysql mysql        693 Aug 19 20:51 mysql-bin.000004 -rw-rw----  1 mysql mysql        125 Aug 19 20:54 mysql-bin.000005 -rw-rw----  1 mysql mysql        125 Aug 19 20:58 mysql-bin.000006 -rw-rw----  1 mysql mysql          6 Aug 19 20:59 testlocal.sohu.com.pid srwxrwxrwx  1 mysql mysql          0 Aug 19 20:59 mysql.sock -rw-rw----  1 mysql mysql        133 Aug 19 20:59 mysql-bin.index -rw-r-----  1 mysql mysql      12432 Aug 21 11:20 testlocal.sohu.com.err drwx------  2 mysql mysql       4096 Aug 21 11:48 testbk -rw-rw----  1 mysql mysql       5208 Aug 21 11:48 mysql-bin.000007 -rw-r-----  1 mysql mysql    5242880 Aug 21 11:48 ib_logfile1 -rw-r-----  1 mysql mysql    5242880 Aug 21 11:48 ib_logfile0 -rw-r-----  1 mysql mysql 1084227584 Aug 21 11:48 ibdata1