Introduction to MySQL storage engine and differences between MyISAM and InnoDB

MyISAM:The default MySQL plug-in storage engine is one of the most commonly used storage engines in Web, data warehousing, and other application environments. Note: by changing the STORAGE_ENGINE configuration variables, you can easily change the default storage engine of the MySQL server.
InnoDB:It is used for transaction processing applications and has many features, including ACID transaction support.
BDB:It can replace InnoDB's transaction engine and support COMMIT, ROLLBACK, and other transaction features.
Memory:Store all data in RAM and provide extremely fast access in environments where you need to quickly search for references and other similar data.
Merge:Allows MySQL DBAs or developers to logically combine a series of equivalent MyISAM tables and reference them as one object. It is suitable for VLDB environments such as data warehousing.
Federated:Connects multiple separated MySQL servers to create a logical database from multiple physical servers. It is very suitable for distributed environments or data mart environments.
Archive:It provides a perfect solution for storing and retrieving a large number of rarely referenced historical, archived, or security audit information.
Cluster/NDB:MySQL's cluster-based database engine is especially suitable for applications with high-performance search requirements. Such search requirements also require the highest normal working time and availability.
Other:Other storage engines include CSV (referencing files separated by commas as database tables) and Blackhole (used to temporarily prohibit database application input ), and the Example engine (which can help you quickly create a custom plug-in storage engine ).

MySQL: differences between MyISAM and InnoDB

InnoDB and MyISAM are the two most commonly used table types in MySQL, each with its 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.

MyIASM is a new version of the IASM table and has the following extensions:

Binary hierarchy portability.
NULL column index.
There are fewer fragments for Long-varying rows than the ISAM table.
Supports large files.
Better index compression.
Better key? statistical distribution.
Better and faster auto_increment processing.

The following are some differences between details and specific implementations:

1. InnoDB does not support FULLTEXT indexes (currently only MyISAM tables are supported and can only be used in CHAR, VARCHAR, and TEXT fields)
2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, 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 from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, 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 executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like '% wfc %'

Any type of table is not omnipotent. You only need to select a proper table type for the business type to maximize the performance advantage of MySQL.