Research on storage characteristics of InnoDB and MyISAM data

InnoDB and MyISAM are two of MySQL's most important data storage engines, both of which can be used to store tables and indexes, InnoDB indexes and tables are stored in the same file, MyISAM indexes and tables are stored in different files, but even multiple indexes coexist in one file.

Many comparisons on the network are based on both transactional processing and locking mechanisms, and so on, it seems that InnoDB has no disadvantages than MyISAM. But the study found that MyISAM has a prominent advantage in data, especially in index storage, and is discussed below.

1. The study concluded

1) InnoDB occupies more disk space than MyISAM, MyISAM storage data can save space 12%, storage index can save 95%;

2 InnoDB the use of free storage space is not excellent.

The study found that MyISAM can save a lot of disk space, especially on the storage of the index, the advantage is huge, this to the large MySQL database data table and index physical design, has a great guiding significance.

2. Subjects of study and data obtained

MySQL version: 5.1.26-rc-community

The study object was created by a table, Mytable3, initially innodb type. There are 540,000 rows of data that are not duplicated (produced with a random function) and two indexes. A total of two experiments were conducted and the data obtained were as follows.

	Table Occupancy _MB	Index occupancy _MB	InnoDB file Occupancy _MB	MyISAM file Occupancy _MB
Initial type InnoDB	256	240	914	0
Type changed to MyISAM	222	8	914	230
Type change back to InnoDB	256	240	1034	0

It can be seen from the experimental data that after the table type Alter is MYISAM, the disk space is only 8MB, which is 4% of InnoDB. And as the table type changes back to the Innodb,innodb table space is forced to expand 120MB to 1034MB to support the fetch of the table data.