MySQL MyISAM default storage engine implementation principle

By default, the MyISAM table will generate three files on the disk: .frm (table structure file), .MYD (data file), and .MYI (index file)

You can specify the storage location of data files and index files when creating them. Only MyISAM tables support this:

• DATA DIRECTORY [=] The absolute path where the data is saved
• INDEX DIRECTORY [=] Absolute path to the index file

The maximum amount of data supported by a single MyISAM table is 2 to the 64th power of jump records

A maximum of 64 indexes can be created for each table.

If it is a composite index, each composite index contains a maximum of 16 columns, and the maximum length of the index value is 1000B

The storage format of the MyISAM engine is:

• Fixed length (FIXED static): refers to the field not containing VARCHAR/TEXT/BLOB
• Dynamic: As long as the field contains VARCHAR/TEXT/BLOB
• COMPRESSED: muisampack created

InnoDB storage engine:

The design follows the ACID model, supports transactions, has the ability to recover from service crashes, and can protect user data to the greatest extent possible.

Support row-level locks, which can improve the read and write performance of multiple users concurrently

InnoDB has its own independent buffer pool, and commonly used data and indexes are cached.

For INSERT, UPDATE, and DELETE operations, InnoDB uses a change buffering mechanism to automatically optimize, provide consistent reads, and cache changed data to reduce disk I/O and improve performance.

After creating an InnoDB table, two files will be generated:

• .frm table structure file
• .ibd data and index storage tablespace

All tables need to create a primary key, preferably with AUTO_INCREMENT, or put it in a frequently queried column as the primary key

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.