Q: What storage engines does MySQL have? Which is the default? What’s the difference? How to choose a storage engine?

1. Summary and comparison of storage engines

After mysql5, nine storage engines are supported, but only a few are commonly used, and InnoDB is supported by default.

We can use commands to see which storage engines are supported by the current database.

show engines;
Copy the code

You can also query the storage engine used by the current TABLE: SHOW TABLE STATUS from database Name where Name= ‘TABLE Name’

SHOW TABLE STATUS from user_db where Name='t_user';
Copy the code

Different storage engines have their own features to meet different requirements, as shown in the following table. The three commonly used storage engines are compared. To make a choice, first consider what different features each storage engine offers.

function MyISAM MEMORY InnoDB
Storage limits 256TB RAM 64TB
Support transactions NO NO YES
Full text index support YES NO NO
B tree index is supported YES YES YES
HASH index support NO YES NO
Data caching support NO NO YES
Support data compression YES NO NO
Space utilization low N/A high
Support foreign keys NO NO YES

2. Compare storage engine details

2.1, MyISAM

  1. Main features: does not support transactions, does not support foreign keys, storage speed, fast access, table level locking, transaction integrity does not require or SELECT, INSERT based applications can basically use this engine to create tables;

  2. Disk storage files: Each MyISAM is stored on disk as 3 files with the same file name and table name, but with the extension:

    • FRM — Stores table structures
    • MYD–MYData, store data
    • MYI–MYIndex, store index

  3. Supports three different storage formats:

    • Static (fixed length) table — the default storage format, the advantages of storage is very fast, easy to cache, and table damage is easy to repair, the disadvantage is space;
    • Dynamic (variable length) table – the advantage of saving space, but frequent update prone to fragmentation, difficult to recover from failure;
    • Compressed table – supports data compression and takes up very little space

2.2, the MEMORY

  1. Data is stored in memory and may be lost during downtime. TEXT and BLOB types are not supported. VARCHAR is used in MySQL as a CHAR with fixed length.
  2. Disk storage files: FRM — storage table structure;
  3. Index: Supports hash index (default) and B-tree index.

2.3 InnoDB(default)

  1. Main features: Repeatable Read by MVCC (concurrent version control), support foreign key constraint, support AUTO_INCREMENT column attribute, support automatic disaster recovery, hot backup, support data caching, row-level locking, Suitable for handling multiple concurrent update requests:
  2. Disk storage files:
    • FRM — Stores table structures
    • Ibd — Stores data and indexes on leaf nodes of B+ numbers, with separate IBD files for partitioned tables;
  3. Index: b-tree index

3. Select a storage engine

You need to choose which engine to use flexibly. Multiple tables in a database can use different engines to meet various performance and practical requirements. Using an appropriate storage engine will improve the performance of the entire database. The criteria for selecting the right storage engine can be divided into:

  • Are transactions supported? –InnoDB
  • Do YOU need to use hot spare? –InnoDB
  • Do you need foreign key support? –InnoDB
  • Is crash recovery supported? – the InnoDB, MyISAM
  • Is it a read-based business? –MyISAM
  • Is the data small and frequently used and allowed to be lost? –MEMORY

Select a storage engine based on the standard.