MySQL Storage engine

MySQL There are a variety of storage engines, MyISAM and InnoDB are two of the most common. Here are some basic concepts about these two engines (not in-depth introduction).

MyISAM based on the traditional ISAM type, full-text search is supported, but not transaction-safe, and foreign keys are not supported. Each MyISAM table is stored in three files: the frm file is stored as a table definition, the data file is MyD (MYData), and the index file is myi (Myindex).

InnoDB ( default) is a transactional engine that supports rollback, crash resiliency, multi-version concurrency control, acid transactions, row-level locking (InnoDB table row locks are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, The InnoDB table also locks the full table, such as the SQL statement when it operates, and provides a non-lock read that is consistent with the Oracle type. InnoDB stores its tables and indexes in a table space, a tablespace can contain several files.

Main differences:

• myisam Non-transactional security, while InnoDB is transaction-safe.

• myisam myisam myisam

• myisam innodb table is more secure than the MyISAM table, and you can switch non-transactional tables to transaction tables (ALTER TABLE tablename TYPE=INNODB) without ensuring that the data is not lost.

Application Scenarios:

• MyISAM Manage non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is a better choice if you need to perform a large number of select queries in your application.

• InnoDB for transactional applications, with many features, including acid transaction support. If you need to perform a large number of insert or update operations in your app, you should use InnoDB, which can improve the performance of multiple user concurrency operations.

Common commands:

(1) View the storage type of table (three kinds):

• Show CREATE TABLE TableName

• Show table status from DBName where Name=tablename

• Mysqlshow-u user-p password--status dbname tablename

(2) Modify the table's storage engine:

• ALTER TABLE TableName TYPE=INNODB

(3) Add the following parameter to the command line that starts the MySQL database so that the newly published table will use the transaction by default:

• --default-table-type=innodb

(4) Temporarily change the default table type:

• Set Table_type=innodb

• Show variables like ' Table_type '

MySQL Storage engine