In depth :MySQL database MyISAM versus InnoDB storage engine

MySQL has a variety of storage engines, MyISAM and InnoDB are two of the most commonly used. Here are some basic concepts (not in depth) about these two engines.
MyISAM is the default storage engine for MySQL. It is based on the traditional ISAM type, supports full-text search, but is not transaction-safe, and does not support foreign keys. Each MyISAM table is stored in three files: frm file store table definition; The data file is MYD (MYData); The index file is MYI (MYIndex).
InnoDB is transactional engine, support the rollback, crash recovery ability, multi version concurrency control, ACID transactions, support row-level locking (InnoDB table row lock is not absolute, if cannot be determined when performing a SQL statements MySQL to scan the scope of the InnoDB table will also be a full table lock, such as like operation SQL statement), and provide with Oracle type 1 to unlocked read way. InnoDB stores its tables and indexes in one table space, which can contain several files.
Main differences:
The & # 8226; MyISAM is non-transaction-secure, while InnoDB is transaction-secure.
The & # 8226; The granularity of MyISAM locks is at the table level, while InnoDB supports row-level locking.
The & # 8226; MyISAM supports full-text type indexes, while InnoDB does not.
The & # 8226; MyISAM is relatively simple, so it is better than InnoDB in efficiency. For small applications, MyISAM should be considered.
The & # 8226; The MyISAM table is saved as a file, and using MyISAM for data transfer across platforms can save you a lot of trouble.
The & # 8226; The InnoDB table is more secure than the MyISAM table, and you can switch the non-transaction table to the transaction table without losing the data (alter table tablename type=innodb).
Application scenarios:
The & # 8226; MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If your application needs to execute a large number of SELECT queries, then MyISAM is a better choice.
The & # 8226; InnoDB is used for transaction processing applications and has a number of features, including ACID transaction support. If your application needs to perform a large number of INSERT or UPDATE operations, InnoDB should be used to improve the performance of concurrent multiuser operations.
Common commands:
(1) storage types of view table (3 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 make the newly published tables use transactions by default:
•--default-table-type=InnoDB
(4) temporarily change the default table type:
•set table_type=InnoDB
•show variables like 'table_type'