Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the implementation principle of the MyISAM default storage engine in MySQL. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The default MyISAM table produces three files on disk: .frm (table structure file), .MYD (data file), and .MYI (index file).

You can specify where to store data files and index files at creation time, and only the MyISAM table supports:

DATA DIRECTORY [=] absolute path to data preservation

Absolute path to the INDEX DIRECTORY [=] index file

The maximum amount of data supported by a single table in MyISAM is 2 to the power of 64 hop records.

A maximum of 64 indexes can be built per table

In the case of a composite index, each composite index contains up to 16 columns, and the maximum length of the index value is 1000B

Storage format of the MyISAM engine:

Fixed length (FIXED static): this means that the field does not contain VARCHAR/TEXT/BLOB

Dynamic (DYNAMIC): as long as the field contains VARCHAR/TEXT/BLOB

Compression (COMPRESSED): muisampack creation

InnoDB Storage engine:

The design follows the ACID model, supports transactions, has the ability to recover from service crashes, and can maximize the protection of users' data.

Support for row-level locks, which can improve read and write performance when multi-user concurrency

InnoDB has its own independent buffer pool, and commonly used data and indexes are in the cache.

For INSERT, UPDATE, and DELETE operations, InnoDB uses a change buffering mechanism for automatic optimization, provides consistent reads, caches changed data, reduces disk Imax O, and improves performance

After the InnoDB table is created, two files are produced:

.frm table structure file

.ibd data and index storage tablespace

All tables need to create a primary key, preferably with AUTO_INCREMENT, or into frequently queried columns as primary keys

This is the end of the article on "what is the implementation principle of MyISAM default storage engine in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.