[MySQL] explanation of MyISAM and InnoDB

MyISAM and InnoDB

InnoDB and MyISAM are the two most common table types used by many people when using MySQL. The 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 transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.

The following are some differences between details and specific implementations:

◆ 1. InnoDB does not support Fulltext indexes.

◆ 2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) fromtable, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.

◆ 3. For fields of the auto_increment type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.

◆ 4. When deleting from table, InnoDB does not create a new table, but deletes a row.

◆ 5. the load table frommaster operation does not work for InnoDB. The solution is to change the InnoDB table to the MyISAM table first, and then the InnoDB table after the data is imported, however, it is not applicable to 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 be scanned when an SQL statement is executed, the InnoDB table will also lock the entire table, for example, updatetable set num = 1 where name like "A %"

The main difference between the two types is that InnoDB supports transaction processing and Foreign keys and row-level locks, but MyISAM does not. Therefore, MyISAM is often considered only suitable for small projects.

From the perspective of mysql users, InnoDB and MyISAM both prefer to use MySQL, but from the perspective of my current O & M database platform, we need to meet the following requirements: 99.9% stability, for convenient scalability and high availability, MyISAM is definitely my first choice.

The reason is as follows:

1. First, most of the projects on my current platform are read-write-less projects, while MyISAM has a much better read performance than InnoDB.

2. MyISAM indexes and data are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded, while InnoDB is closely bound with indexes and data. Without compression, InnoDB is much larger than MyISAM.

3. From the platform perspective, it is often the next two months that application developers may accidentally update the where write range of a table, which makes the table unusable, at this time, the superiority of MyISAM is reflected. The files of the corresponding table can be retrieved from the compressed package copied on the current day, stored in a database directory, dumped into SQL, and then exported back to the master database, and add the corresponding BINLOG. If it is InnoDB, I am afraid it cannot be so fast. Don't tell me to let InnoDB export XXX regularly. SQL mechanism backup, because the data volume of the smallest database instance on my platform is basically dozens of GB.

4. From the application logic I have come into contact with, select count (*) and order by are the most frequent operations, which may account for more than 60% of the total SQL statements, in fact, this operation of InnoDB locks the table. Many people think that InnoDB is a row-Level Lock, that is, where is only valid for its primary key, and non-primary keys will lock the entire table.

5. There are often many application departments that need to regularly provide some table data to them. For MyISAM, It is very convenient to send it to the corresponding table's frm. MYD and myi files, so that they can start the database in the corresponding version, and InnoDB needs to export XXX. SQL, because the dictionary data files cannot be used by the other party.

6. If InnoDB and MyISAM are compared with insert write operations, InnoDB still cannot achieve the Write Performance of MyISAM. If it is an index-based update operation, although MyISAM may be inferior to InnoDB, however, it is also a problem whether the database can catch up with such highly concurrent writes. It is better to solve this problem through the Multi-instance database/table sharding architecture.

7. If MyISAM is used, the merge engine can greatly accelerate the development speed of the Application Department. They only need to perform some selectcount (*) operations on the MERGE table, it is very suitable for business tables of a type of rows (such as logs, survey statistics) with a total number of hundreds of millions of projects.

Of course, InnoDB is not absolutely unnecessary. I use InnoDB to simulate stock trading projects with transaction projects, for example. When more than 0.2 million of active users are active, it is also very easy to cope, therefore, I personally like InnoDB, but if I start from the database platform application, I will still prefer MyISAM.

In addition, some people may say that MyISAM cannot defend against too many write operations, but I can make up for it through the architecture, saying that my existing database platform capacity is: the total volume of Master/Slave Data is more than several hundred TB, and more than million PV dynamic pages are generated every day. There are also several major projects that do not count as the total number of PVS for calling through data interfaces, (this includes a large project where a single database processes tens of millions of queries every day because memcached was not deployed in the early stage ). The average load of my entire database server is between 0.5 and 1.