Mysql storage engine type, MyISAM & amp; InnoDB, and mysqlmyisam

Mysql storage engine types, such as MyISAM and InnoDB, and mysqlmyisam

For some texts, refer to other blogs or texts.
Official explanation:

· MyISAM: the default MySQL plug-in storage engine, which 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: used for transaction processing applications. It has many features, including ACID transaction support.

· BDB: it can replace InnoDB's transaction engine and support COMMIT, ROLLBACK, and other transaction features.

· Memory: stores all data in RAM and provides extremely fast access in environments where reference and other similar data need to be quickly searched.

· 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.

· Archive: provides a perfect solution for storing and retrieving a large number of rarely referenced historical, archived, or security audit information.

· Federated: connects multiple separated MySQL servers and creates a logical database from multiple physical servers. It is very suitable for distributed environments or data mart environments.

· 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), Blackhole (used to temporarily prohibit database application input ), and the Example engine (which can help you quickly create a custom plug-in storage engine ).

For the entire server or solution, you can use different storage engines for each table in the solution., Has achieved the desired goal and effect.

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.

Differences in MyISAM & InnoDB details
1. InnoDB does not support FULLTEXT indexes.
2. InnoDB does not save the specific number of rows in the table. That is to say, run select count () From table, InnoDB needs to scan the entire table to calculate the number of rows, but MyISAM only needs to simply read the number of rows saved. Note that when count () When the 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 MySQL Administrator creates a database, the default table type is InnoDB.

5. The MyISAM type does not support advanced processing such as transaction processing, whereas the InnoDB type does. 6. MyISAM tables emphasize performance. The execution speed is faster than that of InnoDB tables, but transactions are not supported. InnoDB provides advanced database functions such as transaction support and Foreign keys.

7. MyISAM binary data files can be migrated in different operating systems. That is, it can be copied directly from Windows to linux.

Related commands
1. view the table information, including the engine type, character encoding, and table structure used.

Mysql> show create table t1;-t1 is the table name