Comparison between MySQL storage engines MyISAM and InnoDB

Data in MySQL is stored in files (or memory) using various technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels and ultimately provides a wide range of different functionality and capabilities. By choosing different technologies, you can gain additional speed or functionality that improves the overall functionality of your app.

MySQL has a variety of storage engines, MyISAM and InnoDB are two commonly used ones. Here are some basic concepts about these two engines (not an in-depth introduction).

MyISAM is the default storage engine for MySQL. It is based on the traditional ISAM type and supports full-text search, but it is not transaction-safe 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); the index file is MYI (MYIndex).

InnoDB is a transactional engine that supports rollback, crash recovery, multi-version concurrency control, ACID transactions, and row-level locking (the row lock of the InnoDB table is not absolute. If MySQL is executing a SQL statement If the scope to be scanned cannot be determined, the InnoDB table will also lock the entire table, such as the SQL statement during the like operation), and provide a lock-free reading method consistent with the Oracle type. InnoDB stores its tables and indexes in a tablespace, which can contain several files.

Main difference:

MyISAM is non-transactionally safe, while InnoDB is transactionally safe.

The granularity of MyISAM locks is table level, while InnoDB supports row-level locking.

MyISAM supports full-text type indexes, but InnoDB does not support full-text indexes.

MyISAM is relatively simple, so it is better than InnoDB in terms of efficiency. Small applications can consider using MyISAM.

MyISAM tables are saved in the form of files. Using MyISAM storage in cross-platform data transfer will save a lot of trouble.

InnoDB tables are more secure than MyISAM tables. You can switch non-transactional tables to transactional tables (alter table tablename type=innodb) while ensuring that data will not be lost.

Application scenario:

MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If your application needs to perform a large number of SELECT queries, MyISAM is a better choice.
InnoDB is used for transaction processing applications and has numerous features, including ACID transaction support. If your application needs to perform a large number of INSERT or UPDATE operations, you should use InnoDB, which can improve the performance of multi-user concurrent operations.
Commonly used commands:

(1) View the storage type of the table (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) Start mysql Add the following parameters to the command line of the database to make 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'

The above is a brief introduction to MyISAM and InnoDB, the two storage engines of mysql, as well as an introduction to the differences between the two engines.

Related recommendations:

A simple example of mysql's MERGE storage engine

mysql transactions and mysql storage engine_MySQL

What are the differences between the two engines of MySQL

The above is the detailed content of Comparison between MySQL storage engines MyISAM and InnoDB. For more information, please follow other related articles on the PHP Chinese website!