Shulou(Shulou.com)06/01 Report--

The following mainly brings you the definition and differential analysis of MyISAM and InnoDB. I hope these words can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

MyISAM InnoDB distinction

1. MYISAM

.frm: table structure

.MYD: table data

.MYI: table index

II. INNODB

.frm: table structure

.idb: tablespace

.opt: character set and arrangement rules

MyISAM and InnoDB explain that InnoDB and MyISAM are the two most commonly used table types for many people when using MySQL. These two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transactions, while the InnoDB type does. Tables of type MyISAM emphasize performance and perform several degrees faster than type InnoDB.

#

MyISAM and InnoDB explanation

InnoDB and MyISAM are the two most commonly used table types for many people when using MySQL. These two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transactions, while the InnoDB type does. Tables of type MyISAM emphasize performance and perform several times faster than type InnoDB, but do not provide transaction support, while InnoDB provides advanced database functions such as transaction support already external keys.

Here are some of the details and differences in implementation:

◆ 1.InnoDB does not support indexes of type FULLTEXT.

The specific number of rows of the table is not saved in ◆ 2.InnoDB, that is, when select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows there are, but MyISAM can simply read out the number of saved rows. Note that when the count (*) statement contains the where condition, the operation of the two tables is the same.

◆ 3. For fields of type AUTO_INCREMENT, the InnoDB must contain an index with only that field, but in the MyISAM table, a federated index can be established with other fields.

When ◆ 4.DELETE FROM table, InnoDB does not re-establish the table, but deletes it row by row.

The ◆ 5.LOAD TABLE FROM MASTER operation does not work for InnoDB, and the solution is to change the InnoDB table to MyISAM table first, and then to InnoDB table after importing data, but it does not apply to tables that use additional InnoDB features, such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If the MySQL cannot determine the range to scan when executing a SQL statement, the InnoDB table will also lock the whole table, such as update table set num=1 wherename like "% aaa%"

The main difference between the two types is that Innodb supports transactions with foreign keys and row-level locks. But MyISAM does not support it. So it is easy to think that MyISAM is only suitable for small projects.

#

From the perspective of MySQL users, both Innodb and MyISAM are preferred, but MyISAM is definitely my first choice in terms of 99.9% stability, convenient scalability and high availability for my current OPS database platform.

The reasons are as follows:

1. First of all, most of the projects I currently carry on the platform are projects that read more and write less, and the read performance of MyISAM is much better than that of Innodb.

2. The index of MyISAM is separated from the data, and the index is compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is that indexes and data are tightly bundled, and no compression is used, resulting in Innodb being larger than MyISAM.

3. From the platform point of view, it often happens every 2 months that application developers accidentally update a table where to write the wrong scope, resulting in the table can not be used properly. At this time, the advantages of MyISAM are reflected. Take out the files of the corresponding table from the compressed package copied on the same day, casually put them in a database directory, and then dump into sql and then import back to the main database, and add the corresponding binlog. If it's Innodb, I'm afraid it can't be so fast. Don't tell me to let Innodb back up regularly with the export xxx.sql mechanism, because the smallest database instance on my platform basically has dozens of gigabytes of data.

4. From the application logic I have come into contact with, select count (*) and order by are the most frequent, accounting for more than 60% of the total sql statements. In fact, this operation Innodb also locks the table. Many people think that Innodb is a row-level lock, but where is valid for its primary key, and non-primary keys will lock the whole table.

5, there are often many application departments need me to give them some table data on a regular basis, MyISAM is very convenient, as long as send them the corresponding table frm.MYD,MYI file, let them start in the corresponding version of the database on the line, and Innodb needs to export xxx.sql, because only to other people's files, affected by dictionary data files, the other party is unable to use.

6. If compared with MyISAM and insert write operation, Innodb can not achieve the write performance of MyISAM. For index-based update operation, although MyISAM may be inferior to Innodb, it is also a problem whether the write with high concurrency can catch up with the database. It is better to solve the problem through multi-instance sub-database sub-table architecture.

7. If MyISAM is used, the merge engine can greatly accelerate the development speed of the application department. As long as they do some select count (*) operations on this merge table, it is very suitable for a certain type of rows business table (such as logs, surveys and statistics) with a total amount of hundreds of millions of large projects.

Of course, Innodb is not absolutely unnecessary, using transaction projects such as simulated stock speculation projects, I use Innodb, more than 200,000 active users, it is also very easy to deal with, so I personally like Innodb very much, but if starting from the database platform applications, I will still choose MyISAM.

In addition, some people may say that your MyISAM cannot resist too many writes, but I can make up for it through the architecture. Tell me about the capacity of the database platform I currently use: dynamic pages with a total amount of master and slave data of more than a few hundred tons, more than one billion pv per day, and several large projects that are called through data interfaces that are not included in the total number of pv. (this includes a large project that did not deploy memcached initially, resulting in a single database handling 90 million queries per day.) On the other hand, the average load of my overall database server is about 0.5-1.

For the above definition and differential analysis of MyISAM and InnoDB, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.