How many storage engines are there in MySQL?

Execute the following command to view all mysql storage engines [Recommended tutorial: MySQL Tutorial]

InnoDB Storage Engine

InnoDB is the preferred engine for transactional databases. It supports transaction security tables (ACID), row locking and foreign keys. As you can see in the above figure, InnoDB is the default MySQL engine. The main features of InnoDB are:

1. InnoDB provides MySQL with a transaction-safe (ACID-compatible) storage engine with commit, rollback and crash recovery capabilities. InnoDB locks at the row level and also provides an Oracle-like non-locking read in the SELECT statement. These features increase multi-user deployment and performance. In SQL queries, you can freely mix InnoDB type tables with other MySQL table types, even in the same query

2. InnoDB is designed for maximum performance in processing huge amounts of data. . Its CPU efficiency may be unmatched by any other disk-based relational database engine lock

3. The InnoDB storage engine is fully integrated with the MySQL server. The InnoDB storage engine is maintained for caching data and indexes in main memory. its own buffer pool. InnoDB places its tables and indexes in a logical table space, which can contain several files (or raw disk files). This is different from MyISAM tables, where each table is stored in a separate file. InnoDB tables can be of any size, even on operating systems where the file size is limited to 2GB

4. InnoDB supports foreign key integrity constraints. When storing data in the table, each table is stored according to the primary key. Sequential storage, if the primary key is not specified when defining the table, InnoDB will generate a 6-byte ROWID for each row and use it as the primary key

5. InnoDB is used in many large databases that require high performance

InnoDB does not create a directory on the site. When using InnoDB, MySQL will create a 10MB automatically extended data file named ibdata1 and two 5MB named ib_logfile0 and ib_logfile1 in the MySQL data directory. Log file

MyISAM Storage Engine

MyISAM is based on the ISAM storage engine and extends it. It is one of the most commonly used storage engines in Web, data warehousing and other application environments. MyISAM has high insertion and query speed, but does not support transactions. The main features of MyISAM are:

1. Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files

2. When deleting, updating and inserting Dynamically sized rows produce less fragmentation when operations are mixed. This is done automatically by merging adjacent deleted blocks, and if the next block is deleted, extending to the next block

3. The maximum number of indexes for each MyISAM table is 64, which can be achieved by recompiling Change. The maximum number of columns per index is 16

4, and the maximum key length is 1000 bytes. This can also be changed through compilation. For key lengths exceeding 250 bytes, a key length exceeding 1024 bytes Keys will be used

5. BLOB and TEXT columns can be indexed

6. NULL is allowed in indexed columns. This value occupies 0~1 bytes for each key.

7. All numeric key values ​​are stored in high byte first to allow for a higher index compression

8. Each MyISAM type table has an AUTO_INCREMENT internal column. This column is updated during INSERT and UPDATE operations, and the AUTO_INCREMENT column will be refreshed. Therefore, the AUTO_INCREMENT column of the MyISAM type table is updated faster than the AUTO_INCREMENT of the InnoDB type.

9. You can put the data files and index files in different directories.

10. Each character column can have Different character sets

11. Tables with VARCHAR can have fixed or dynamic record length

12. VARCHAR and CHAR columns can be up to 64KB

Use the MyISAM engine to create a database. 3 files will be generated. The name of the file starts with the table name, and the extension is the file type: frm file storage table definition, the extension of the data file is. MYD (MYData), the extension of the index file. MYI (MYIndex)

MEMORY storage engine

MEMORY storage engine stores the data in the table into memory, providing fast access without querying and referencing other table data. The main features of MEMORY are:

1. Each MEMORY table can have up to 32 indexes, 16 columns per index, and a maximum key length of 500 bytes.

2. MEMORY The storage engine performs HASH and BTREE microcosms

3. You can have non-unique key values ​​in a MEMORY table

4. The MEMORY table uses a fixed record length format

5 , MEMORY does not support BLOB or TEXT columns

6, MEMORY supports AUTO_INCREMENT columns and indexes on columns that can contain NULL values ​​

7, MEMORY tables are shared between all clients (that is, Like any other non-TEMPORARY table)

8. The MEMORY table memory is stored in memory. The memory is shared between the MEMORY table and the internal tables created by the server when it is idle during query processing

9 . When the contents of the MEMORY table are no longer needed, to release the memory used by the MEMORY table, you should execute DELETE FROM or TRUNCATE TABLE, or delete the entire table (using DROP TABLE)

Selection of storage engine

If you want to provide transaction security (ACID compatibility) capabilities for submission, rollback, and crash recovery capabilities, and require concurrency control, InnoDB is a good choice

If the data table is mainly used to insert and query records, the MyISAM engine can provide higher processing efficiency

If the data is only temporarily stored, the amount of data is not large, and high data security is not required, you can choose Memory engine that saves data in memory. This engine is used as a temporary table in MySQL to store the intermediate results of the query.

If there are only INSERT and SELECT operations, you can choose Archive. Archive supports high-concurrency insert operations, but Not transactionally safe per se. Archive is very suitable for storing archived data. For example, you can use Archive

to record log information. Which engine to use needs to be chosen flexibly. Multiple tables in a database can use different engines to meet various performance and actual needs. Use the appropriate one. The storage engine will improve the performance of the entire database.

The above is the detailed content of How many storage engines are there in MySQL?. For more information, please follow other related articles on the PHP Chinese website!