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

Editor to share with you how the InnoDB storage engine in MySQL is designed, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

Two members of MySQL, binlog and redo log. However, these are just two minions in the MySQL family. Mysql can achieve high performance and reliability, and they are definitely not the only ones.

What other members are there in MySQL?

Among them, the lowest storage engine layer (Storage Engines), which determines how MySQL will store data, how to read and write data, but also largely determines the read and write performance and data reliability of MySQL.

For such an important layer of capabilities, MySQL provides great extensibility, and you can define what kind of storage engine you want to use: InnoDB, MyISAM, MEMORY, CSV, or even develop your own storage engine and use it.

Generally speaking, when we talk about Mysql with high performance and high reliability, we all refer to Mysql based on InnoDB storage engine. So, in this lecture, let's take a look at what other members of redo log,InnoDB have, what kind of roles they have, and how they cooperate with each other.

InnoDB memory architecture

InnoDB is divided into two main blocks: memory and disk, so let's start with memory.

1 、 Buffer Pool

As mentioned earlier, MySQL will not modify the disk data directly, because it is too slow. MySQL will change the memory first, then record the redo log, and then scan the disk when you are free. If there is no data in memory, go to the disk load.

And the place where the data is stored is Buffer Pool.

When we develop, we usually use redis to do caching to ease the pressure on the database. In fact, MySQL itself has done something similar to caching.

MySQL reads data from disk in "page" units, and so does data in Buffer Pool. In fact, Buffer Pool is an a linked list of pages, a linked list with pages as elements.

Why a linked list? Because, like caching, it needs a set of elimination algorithms to manage data.

Buffer Pool uses an algorithm based on LRU (least recently used) to manage memory.

2 、 Change Buffer

As mentioned above, if there is no corresponding "page" data in memory, MySQL will load the data from disk. If the required "page" is different each time, or it is not adjacent "page", then every time MySQL will go to load, which will be very slow.

So if MySQL finds that the page you want to modify is not in memory, write down the changes you want to make to the page in a place called Change Buffer, record the redo log at the same time, and then slowly load the data to memory. After load comes over, apply the changes recorded in Change Buffer to memory (Buffer Pool). This action is called merge; and the action of brushing memory data to disk is called purge:

Merge:Change Buffer-> Buffer Pool

Purge:Buffer Pool-> Disk

The above is the definition of Change Buffer on MySQL's official website. if you take a closer look, you will find that Change Buffer is only used when operating "secondary index" because "clustered indexes" must be "unique", which means that every time you insert or update, you need to check whether the same field already exists, so there is no need to use Change Buffer. In addition, the "clustered index" operation is relatively random, usually in the adjacent "page" operation, such as the use of self-increasing primary key of the "clustered index", then insert is incremental, orderly, unlike the "secondary index", access is very random.

3 、 Adaptive Hash Index

The MySQL index, whether on disk or after being load to memory, is a B + tree, and the number of searches of the B + tree depends on the depth of the tree. You see, the data has been put in memory, can not be found "all at once", but also "a few things", this space sacrifice is not worth it?

Especially those frequently accessed data, every time I come over, I have to go to the B+ tree to query, then I will think, I just use a pointer to record the location of the data?

This is the Adaptive Hash Index (Adaptive Hash Index). Adaptive, as the name implies, MySQL automatically evaluates whether it is worthwhile to use adaptive indexes, and if it is observed that hash indexing can improve speed, build it.

4 、 Log Buffer

The redo log in Log Buffer will be brushed to disk.

Operating System Cache

Between the memory and the disk, you can see that MySQL draws a layer called Operating System Cache. In fact, this does not belong to the ability of InnoDB, but a layer of cache added by the operating system in front of the disk in order to improve performance. Here, interested students can refer to Wikipedia: Page Cache.

InnoDB disk architecture

What's on the disk? In addition to table structure definitions and indexes, there are also roles designed for high performance and reliability, such as redo log, undo log, Change Buffer, Doublewrite Buffer, and so on.

1. Tablespace (Tablespaces)

As you can see, there are five types of Tablespaces: The System Tablespace;File-Per-Table Tablespaces;General Tablespace;Undo Tablespaces;Temporary Tablespaces.

Among them, the data of the table we usually create can be stored in any one of The System Tablespace, File-Per-Table Tablespaces, General Tablespace, depending on your configuration and the sql statement when creating the table.

2 、 Doublewrite Buffer

If Change Buffer is to improve performance, then Doublewrite Buffer is to ensure the reliability of data pages.

As mentioned earlier, MySQL reads and writes in "pages". There are multiple rows of data in a "page". When writing data, MySQL will first write the page in memory and then refresh it to the page on disk.

At this time, the problem arises, suppose that in the process of refreshing from memory to disk, a "page" is brushed in half, and suddenly the operating system or MySQL process runs out, at this time, the page data in memory is cleared, while the page data in the disk, brushed in half, is in an intermediate state, which can be said to be an "incomplete" or even "broken" page.

Some students said, isn't there a Redo Log? In fact, at this time, Redo Log has been powerless. Redo Log can only load the page data in the disk to memory and then apply Redo Log when the page data in the disk is normal and not damaged. If the page data in the disk has been corrupted, Redo Log cannot be applied.

Therefore, before MySQL brushes the data to the disk, it should first write the data to another place, that is, Doublewrite Buffer, and then start writing to the disk. Doublewrite Buffer can be understood as a recovery, and in case crash does occur, you can use Doublewrite Buffer to repair the data on disk.

The above is all the content of the article "how to design the InnoDB storage engine in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.