In-depth discussion: Comparison between MySQL database MyISAM and InnoDB Storage Engine

MySQL has multiple storage engines. MyISAM and InnoDB are two common storage engines. Here we will introduce some basic concepts about the two engines (not in depth ).
MyISAM is the default storage engine of MySQL. Based on the traditional ISAM type, full-text search is supported, but it is not transaction secure and does not support foreign keys. Each MyISAM table is stored in three files: the frm file stores the table definition, the data file is MYD (MYData), and the index file is MYI (MYIndex ).
InnoDB is a transaction engine that supports rollback, crash recovery, multi-version concurrency control, ACID transactions, and row-level locking. (row locks of InnoDB tables are not absolute, if MySQL cannot determine the scope of the scan when executing an SQL statement, the InnoDB table will also lock the entire table, such as the SQL statement during the like operation ), and provides the same non-lock read method as Oracle. InnoDB stores its tables and indexes in one tablespace, which can contain several files.
Main differences:
• MyISAM is non-transactional secure, while InnoDB is transactional secure.
• MyISAM locks are table-level, while InnoDB supports row-level locks.
• MyISAM supports full-text indexes, while InnoDB does not.
• MyISAM is relatively simple, so it is more efficient than InnoDB. For small applications, you can consider using MyISAM.
• MyISAM tables are saved as files. Using MyISAM storage in cross-platform data transfer saves a lot of trouble.
• The InnoDB table is safer than the MyISAM table, so that the non-transaction table can be switched to the transaction table (alter table tablename type = innodb) without data loss ).
Application scenarios:
• MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If the application needs to execute a large number of SELECT queries, MyISAM is a better choice.
• InnoDB is used for transaction processing applications and has many features, including ACID transaction support. If the application requires a large number of INSERT or UPDATE operations, InnoDB should be used to improve the performance of multi-user concurrent operations.
Common commands:
(1) view the Storage types of tables (three types ):
• Show create table tablename
• Show table status from dbname where name = tablename
• Mysqlshow-u user-p password -- status dbname tablename
(2) modify the storage engine of the table:
• Alter table tablename type = InnoDB
(3) Add the following parameters to the command line that starts the mysql database to enable transactions for newly published tables by default:
• -- Default-table-type = InnoDB
(4) temporarily change the default table Type:
• Set table_type = InnoDB
• Show variables like 'table _ type'