Differences between MyISAM and InnoDB and enterprise selection engine requirements, myisaminnodb

Differences between MyISAM and InnoDB and enterprise selection engine requirements, myisaminnodb

Differences between MyISAM and InnoDB and enterprise selection Engine Requirements

I. Differences between MyISAM and InnoDB

1. MySQL uses MyISAM by default.

2. MyISAM does not support transactions, but InnoDB does. The AUTOCOMMIT of InnoDB is enabled by default, that is, each SQL statement is encapsulated into a transaction by default and automatically submitted, which affects the speed, therefore, it is best to display multiple SQL statements between begin and commit to form a transaction to commit.

3. InnoDB supports row locking. MyISAM does not support row locking. It only supports locking the entire table. That is, the Read and Write locks of the same MyISAM table are mutually exclusive. If both read requests and write requests exist in the queue During Concurrent read/write of MyISAM, The Write Request has a higher priority by default, even if read requests come first, MyISAM is not suitable for situations where a large number of queries and modifications coexist, so the Query Process will be blocked for a long time. Because MyISAM is a lock table, other write processes will starve to death if a read operation is time-consuming.

4. InnoDB supports foreign keys, not MyISAM.

5. InnoDB has a larger primary key range, which is twice the maximum value of MyISAM.

6. InnoDB does not support full-text indexing, while MyISAM does. Full-text index is used to create an inverted Sorting index for each word in char, varchar, and text (excluding deprecated words. The full-text index of MyISAM is useless because it does not support Chinese word segmentation. It must be separated by a user and then written into the data table with spaces, words with less than four Chinese characters will be ignored like deprecated words.

7. MyISAM supports GIS data, which is not supported by InnoDB. MyISAM supports the following Spatial Data Objects: Point, Line, Polygon, and Surface.

8. Using MyISAM with no where count (*) is much faster than using InnoDB. Because MyISAM has a built-in counter, it reads from the counter directly when count (*), while InnoDB must scan the entire table. Therefore, when running count (*) on InnoDB, it is generally accompanied by where, and where should contain index columns other than primary keys. Why do we emphasize "outside the primary key" here "? In InnoDB, primary index is stored together with raw data, while secondary index is stored separately, and a pointer points to primary key. Therefore, if you only use count (*), you can use secondary index to scan faster, while primary key is mainly used to scan the index and return raw data.

Summary: The main difference between the two types is that Innodb supports transaction processing and Foreign keys and row-level locks.

Summary:

Myisam	InnoDB
Transactions are not supported.	Support transactions
Data row locking is not supported.	Supports row locking.
Foreign keys are not supported	Supports Foreign keys
Small primary key range	Large primary key range
Full-text index supported	Full-text index not supported
Support GIS data	GIS data not supported

Ii. Reasons Why enterprises choose MyISAM

As a user of MySQL, Innodb and MyISAM are both preferred. If the database platform meets the requirement of 99.9% stability, convenient scalability and high availability, MyISAM is definitely the first choice.

The reason is as follows:

1. Most projects hosted on the 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. Application developers often fail to update the where write range of a table every one or two months. As a result, the table cannot be used normally, 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 regularly use the xxx. SQL export mechanism for backup, because the minimum data volume of a database instance is usually dozens of GB.

4. In terms of application logic, 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. Innodb is used for transaction projects. In addition, some may say that MyISAM cannot resist too many write operations, but it can be compensated by the architecture.