Comparison between MyISAM and InnoDB In the MySQL database storage engine

MySQL databaseOfStorage EngineIs what we will introduce in this article. It includes:MyISAMAndInnoDBMyISAM is the default storage engine of MySQL. Based on the traditional ISAM type, full-text search is supported, 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), and the index file is MYI (MYIndex ).

InnoDB is a transaction engine that supports rollback, crash recovery, multi-version concurrency control, and ACID transactions. It is not absolute to support row-level locking of InnoDB tables, 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, while InnoDB is transactional.

The granularity of MyISAM locks is 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 for cross-platform data transfer saves a lot of trouble.

InnoDB tables are safer than MyISAM tables. when data is not lost, you can switch non-transaction tables to the transaction table alter table tablename type = innodb ).

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. There are three types of table Storage ):

 
 

  
  
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' 
 
 

This article introduces the storage engines MyISAM and InnoDB of MySQL databases. I hope this introduction will help you gain some benefits.