preface

MySQL executes a SQL statement, and the corresponding table data is read and written by the storage engine (update data, query data).

During this process, the storage engine needs to make some decisions

  • Is the data checked from memory or hard disk
  • Is the data updated in memory, or hard disk
  • When is memory data synchronized to the hard disk

Therefore, the storage engine interacts with memory and hard disks according to internal logic.

We can select the storage engine on demand, such as InnoDB, MyISAM, Memory, etc.

InnoDB is the most commonly used storage engine and is the default storage engine starting with MySQL5.5.8.

InnoDB profile

InnoDB storage engine supports transactions and is designed for online transaction processing (OLTP) applications.

Feature is row lock design, support MVCC, foreign key, provide consistency of non-lock read, at the same time, its own design can be the most effective use of memory and CPU, is the most commonly used MySQL storage engine.

InnoDB important memory structure

InnoDB storage engine has two very important components in memory, the Buffer Pool and the redo log Buffer.

Buffer Pool profile

Buffer pools cache a lot of data, such as data pages, index pages, lock information, and so on.

If you query a record, a page of data will be loaded from disk. The page of data will be stored in the Buffer Pool.

Follow-up queries are searched from the Buffer Pool first. If the query fails to hit the Buffer Pool, the disk is loaded. This reduces disk I/O overhead and improves disk performance.

If the Buffer Pool is hit, the Buffer Pool is directly updated.

The Buffer Pool caches a large amount of data for subsequent queries and updates.

Tip: Don’t think that there are only data pages in the Buffer Pool. It takes up most of the space in the Buffer Pool. More details on Buffer pools will be covered in a later article.

Introduction to the redo log Buffer

Let’s say we changed a piece of data on a page in the Buffer Pool, but the data was not synchronized to the hard disk, so the data was inconsistent. If MySQL went down, the data would be lost.

I don’t know what to do.

To ensure data persistence, the InnoDB storage engine has added redo logs, also known as redo logs.

Every time we update table data, we record “what changes were made on a data page” in the redo log buffer.

When the transaction commits, the redo log buffer is emptied and the redo log file is flushed.

If MySQL goes down, it doesn’t matter because the data will be recovered from the redo log after the restart.

Tip: The redo log is also full of details. This article is an introduction to the redo log.

summary

It is easy to see that Buffer pools and redo log buffers are used to reduce disk I/O overhead.

Since the Buffer Pool and redo logs cover a lot of information, they will be covered separately in the next two articles.

All of today’s content is to pave the way for the following articles, we first know what they are, leave an impression on the line.

Standing on the shoulders of giants:

  • “MySQL actual combat 45 lectures”
  • Start from scratch to become a master of actual MySQL Optimization
  • How MySQL Works: Understanding MySQL from the root
  • MySQL Technology Innodb Storage Engine

MySQL > MySQL > MySQL

  • CURD over the years, have you ever learned about the architecture of MySQL?

About me

A star is a love of technology Java program ape, the public number “program ape a star” regularly share interesting original articles!

Thank you very much for seeing here, the original is not easy, the article can help attention, point a like, share and comment, are all support (don’t want white prostitute)!

I hope you and I can go on the way we want to go, see you in the next article.