InnoDB or MyISAM talk about MySQL storage engine Selection

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, but from the perspective of my current O & M database platform to meet the requirements: 99.9% Stability

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, but from the perspective of my current O & M database platform to meet the requirements: 99.9% Stability

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 select count (*) 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.

Views 697

Original article address: [go to] InnoDB or MyISAM. Let's talk about the MySQL storage engine selection. Thank you for sharing with me.