MySQL storage engine type, MYISAM&INNODB, etc.

Some of the text refers to other blogs or text.
Official explanation:

· MyISAM: The default MySQL plug-in storage engine, which is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. Note that it is easy to change the default storage engine of the MySQL server by changing the storage_engine configuration variable.

· InnoDB: For transactional applications, with many features, including acid transaction support.

· BDB: An alternative to the INNODB transaction engine that supports commit, rollback, and other transactional features.

· Memory: Keep all your data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data.

· Merge: Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing.

· Archive: Provides the perfect solution for storing and retrieving large amounts of historically, archived, or security audit information that is rarely referenced.

· Federated: The ability to link multiple separate MySQL servers to create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments.

· Cluster/ndb:mysql's clustered database engine, especially suitable for applications with high performance lookup requirements, also requires the highest uptime and availability.

· Other: The other storage engines include CSV (referencing a comma-delimited file used as a database table), blackhole (for temporary suppression of application input to the database), and the example engine, which can help with the quick creation of a custom plug-in storage engine.

for the entire server or scenario, you can use a different storage engine for each table in the scenario , and you have achieved the desired goals and effects.

Any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, to maximize the performance advantage of MySQL.

Myisam&innodb Some of the details are different
1.InnoDB does not support indexes of type Fulltext.
The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT () from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the count () statement contains a where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.

4.MySQL Administrator database is built, the table defaults to the InnoDB type.

The 5.MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The 6.MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides advanced database functionality such as transaction support, foreign keys, and so on.

Binary data files of type 7.MyISAM can be migrated in different operating systems. That is, it can be copied directly from the Windows system to a Linux system.

Some of the related commands
1. View the table information, including the type of engine used, character encoding, table structure, etc.

Mysql> Show CREATE TABLE t1;–t1 is a table name

1. You can execute the following command to toggle non-transactional tables to transactions (data is not lost), and the InnoDB table is more secure than the MyISAM table:
   ALTER TABLE T1 TYPE=INNODB;–T1 is a table name

2. InnoDB table cannot be used with Repair Table command and MYISAMCHK-R table_name
   But you can use the check table T1, and Mysqlcheck [OPTIONS] database [tables]

3. The following parameters have been added to the command line starting the MySQL database to make the newly published MySQL data table default to use transactions (
   Only the CREATE statement is affected. ）
   –default-table-type=innodb

4. Temporarily changing the default table type can be used:
   Set Table_type=innodb;

MySQL storage engine type, MYISAM&INNODB, etc.