How MySQL stores the difference between MyISAM and InnoDB

MyISAM and InnoDB explain:

InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

The following are some of the details and the specific implementation differences:

1. InnoDB does not support indexes of type Fulltext.

2. The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT () from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the count () statement contains a where condition, the operation of the two tables is the same.

3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.

4. Delete from table, InnoDB does not reestablish the table, but deletes one row at a time.

5. The LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.

In addition, the row lock of the InnoDB table is 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%"

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

I use MySQL as a user point of view, InnoDB and MyISAM are more like, but from my current operation of the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely my first choice.

The reasons are as follows:

1. First of all, I am currently hosting most of the projects are read and write less projects, and MyISAM reading performance is much stronger than InnoDB.

The index and data of 2.MyISAM are separate, and the index is compressed, and the memory usage is 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. From the platform point of view, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table is not 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, Put it in a database directory, then dump into SQL and back to the main library, and binlog the corresponding. 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 smallest database instance on my platform has a size of dozens of g of data.

4. From the application logic I contacted, select COUNT (*) and order by are the most frequent, probably accounted for more than 60% of the total SQL statement operation, and this operation InnoDB is actually lock table, many people think InnoDB is row-level lock, That's just where the primary key is valid, and the non-primary key will lock the full table.

5. There are 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 can not reach 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 using MyISAM, the merge engine can greatly speed up the development of the application, as long as they do some select count (*) operations on the merge table, which is ideal for business tables of a certain type (such as logs, survey statistics) of a large project with a total of about hundreds of millions of.

Of course, InnoDB is not absolutely not, with business projects such as simulation stocks, I am using InnoDB, active users more than 200,000, is also very easy to cope with, so I personally also like InnoDB, but if from the database platform application, I would prefer MyISAM.

In addition, some people may say that you myisam can not resist too much write operation, but I can make up by the structure, say my existing database platform capacity: The total number of master and slave data in more than hundreds of T, more than 1 billion PV dynamic page per day, there are several large items are called by the data interface method is not counted into PV total, ( This includes a large project because the initial memcached was not deployed, resulting in a single database processing 90 million queries per day). My overall database server load averaged around 0.5-1.

How MySQL stores the difference between MyISAM and InnoDB