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

The difference between key and index in MySQL

Let's look at the code first: ALTER TABLE rep...

Three strategies for rewriting MySQL query statements

Table of contents Complex query and step-by-step ...

VMware15 installation of Deepin detailed tutorial (picture and text)

Preface When using the Deepin user interface, it ...

Solve the Linux Tensorflow2.0 installation problem

conda update conda pip install tf-nightly-gpu-2.0...

Detailed explanation of VueRouter routing

Table of contents vue router 1. Understand the co...

What is WML?

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

Summary of flex layout compatibility issues

1. W3C versions of flex 2009 version Flag: displa...

Detailed explanation of Docker Secret management and use

1. What is Docker Secret 1. Scenario display We k...

Introduction to building a DNS server under centos7

Table of contents 1. Project environment: 2: DNS ...

Vue + element dynamic multiple headers and dynamic slots

Table of contents 1. Demand 2. Effect 3. All code...

Cleverly use CSS3's webkit-box-reflect to achieve various dynamic effects

In an article a long time ago, I talked about the...

MYSQL database basics - Join operation principle

Join uses the Nested-Loop Join algorithm. There a...

How to run MySQL using docker-compose

Directory Structure . │ .env │ docker-compose.yml...