The difference between MyISAM and InnoDB of MySQL database

The MyISAM of MySQL database differs from the InnoDB in the following ways:

1. Storage structure
Each myisam is stored as three files on disk. The first file name begins with the name of the table, and the extension indicates the file type.
The. frm file stores the table definition.
The data file has an extension of. MYD (MYData).
The extension of the index file is. MYI (Myindex).

2. Storage space
MyISAM: Can be compressed, storage space is small.
InnoDB: Requires more memory and storage, it establishes its dedicated buffer pool in main memory for caching data and indexes.
MyISAM indexes and data are separate, and the indexes are compressed, and memory usage increases a lot. Can load more indexes, and InnoDB is the index and data is tightly bound, no compression will cause innodb than MyISAM volume is large

3. Transaction processing
Tables of the MyISAM type emphasize performance, which is performed more quickly than the InnoDB type, but does not support foreign keys and does not provide transactional support.
INNODB provides advanced database features such as transaction support transactions, external keys (foreign key), and more.

SELECT, UPDATE, INSERT, delete operations
If executing a lot of select,myisam is a better choice.
If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table.
Delete from table, InnoDB does not reestablish the table, but deletes one row at a time. MyISAM is the re-establishment of the table. If you want to empty a table that holds a large amount of data on InnoDB, it is best to use the TRUNCATE TABLE command.

Auto_increment
MyISAM: You can establish a federated index with other fields. The auto-grow column of the engine must be an index, and if it is a composite index, autogrow may not be the first column, and he can be incremented based on the preceding columns.
The Innodb:innodb must contain only the index of the field. The auto-grow column of the engine must be an index, and if it is a combined index, it must be the first column of the combined index.

4. The exact number of rows in the table
MyISAM: The total number of rows that have a table saved, if select count (*) from table, the value is taken directly.
InnoDB: The total number of rows in the table is not saved, and if you use SELECT COUNT (*) from table, the entire table is traversed, consuming considerable amounts, but MyISAM and InnoDB are handled the same way after adding where.

5. Full-Text Indexing
MyISAM: Supports full-text indexing of fulltext types. Chinese is not supported.
InnoDB: Full-text indexing of fulltext type is not supported, but InnoDB can support full-text indexing using Sphinx plug-in and works better.

6. Table Lock Difference
MyISAM: Only table-level locks are supported, table-level locks are supported, and when the user operates the MyISAM table, the Select,update,delete,insert statement automatically locks the table.
InnoDB: Supports transactional and row-level locks, which are the InnoDB of the most important features. Row locks greatly improve the new performance of multiuser concurrency. But InnoDB's row lock is also not absolute, if MySQL cannot determine the scope to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "%aaa%"

Generally speaking:
MyISAM Suitable for:
(1) do a lot of count calculation;
(2) The insertion is not frequent, the query is very frequent;
(3) No business.
InnoDB Suitable for:
(1) Reliability requirements are relatively high, or require business;
(2) Table updates and queries are fairly frequent, and table locking opportunities are relatively large.

The main difference between the two types is that InnoDB supports transactional and foreign key and row-level locks. And MyISAM does not support it. So MyISAM tend to be considered only suitable for use in small projects.

As a user of MySQL, InnoDB and MyISAM are more like, if the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely the first choice.

The reasons are as follows:

1, most of the projects on the platform are read and write less projects, and MyISAM reading performance is stronger than InnoDB.

2, MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding improved a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.

3, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table can not be normal use, this time the superiority of the MyISAM is reflected, casually from the day of the copy of the compressed package out of the corresponding table file, casually put into a database directory, Then dump into SQL and then back to the main library, and the corresponding binlog complement. If it's InnoDB, I'm afraid it can't be so fast, don't tell me to let InnoDB regularly back up with an export xxx.sql mechanism, because the minimum amount of data for a database instance is roughly dozens of g in size.

4, from the application logic of contact, select COUNT (*) and order BY is the most frequent, probably can account for the entire SQL total statement of more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is a row-level lock, That's just where the primary key is valid, and the non-primary key will lock the full table.

5, there is often a lot of application departments need me to give them regular data on some tables, MyISAM words are very convenient, as long as they correspond to the list of the frm. myd,myi files, let them in the corresponding version of the database to start the line, and InnoDB need to export xxx.sql, because the light to other people's files, by the dictionary data file, the other side is not available.

6, if and myisam than insert write operation, InnoDB also not up to MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency of write, from the library can chase is also a problem, It might as well be solved by a multi-instance sub-Library table architecture.

7, if it is used MyISAM, the merge engine can greatly speed up the development of the application department, they just do some select count (*) operation on this merge table, it is very suitable for a large project total of about hundreds of millions of rows of a type (such as log, survey statistics) business table.

Of course InnoDB is not absolutely not used, the project with the business is used InnoDB. In addition, someone might say that you myisam not be able to write too much, but you can make up for it through architecture.

The difference between MyISAM and InnoDB of MySQL database