MySQL MyISAM default storage engine implementation principle

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.

You may also be interested in:
  • Summary of the differences between MySQL storage engines MyISAM and InnoDB
  • Detailed explanation of the difference between MyISAM and InnoDB in MySQL storage engine
  • Introduction to MySQL storage engine and the difference between MyISAM and InnoDB
  • The difference between MySQL storage engines InnoDB and MyISAM
  • Detailed explanation of non-clustered indexes in MySQL's MyISAM storage engine

<<:  The problem and solution of using docker storage and causing Exit to cause files to fail to upload to the server

>>:  Super detailed basic JavaScript syntax rules

Recommend

MySQL 5.7 Common Data Types

——Notes from "MySQL in Simple Terms (Second ...

Pure CSS to adjust Div height according to adaptive width (percentage)

Under the requirements of today's responsive ...

Vue routing lazy loading details

Table of contents 1. What is lazy loading of rout...

Some "pitfalls" of MySQL database upgrade

For commercial databases, database upgrade is a h...

CSS3 achieves flippable hover effect

CSS3 implements a flippable hover effect. The spe...

MySQL deadlock routine: inconsistent batch insertion order under unique index

Preface The essence of deadlock is resource compe...

js to implement verification code interference (static)

This article shares the specific code of js to im...

MySQL DATE_ADD and ADDDATE functions add a specified time interval to a date

MySQL DATE_ADD(date,INTERVAL expr type) and ADDDA...

Solution to the problem of repeated triggering of functions in Vue project watch

Table of contents Problem description: Solution 1...

What is WML?

WML (Wireless Markup Language). It is a markup la...

The whole process record of Vue export Excel function

Table of contents 1. Front-end leading process: 2...

How to install golang under linux

Go is an open source programming language that ma...

Summary of Mysql high performance optimization skills

Database Command Specification All database objec...

Complete steps to install MySQL 5.5 on CentOS

Table of contents 1. Preparation before installat...