Spy on the REDOLOG of InnoDB from the execution process of an update SQL

1 Introduction

In order to achieve better read and write performance of the database, InnoDB will cache data in memory (InnoDB Buffer Pool), and the modification of disk data will also lag behind the memory. At this time, if the process or machine crashes, the memory data will be lost. In order to To ensure the consistency and durability of the database itself, InnoDB maintains the REDO LOG. Before modifying the Page, you need to record the modified content in REDO, and ensure that the REDO LOG is placed on the disk earlier than the corresponding Page, which is often referred to as WAL, Write Ahead Log. When a failure occurs and memory data is lost, InnoDB will restore the Page to the state before the crash by replaying REDO when it restarts.

2 The execution process of MYSQL update statement

2.1 MYSQL Architecture <br>Generally speaking, MySQL can be divided into three parts: client, server layer and storage engine layer, as shown in the figure.
The Server layer includes connectors, query caches, analyzers, optimizers, executors, etc., covering most of MySQL's core service functions, as well as all built-in functions (such as date, time, math and encryption functions, etc.), all across storage engines All functions are implemented in this layer, such as stored procedures, triggers, views, etc.

The storage engine layer is responsible for data storage and retrieval. Its architecture mode is plug-in and supports multiple storage engines such as InnoDB, MyISAM, and Memory. The most commonly used storage engine today is InnoDB, which has been the default storage engine since MySQL 5.5.5.

2.2 Update the execution of SQL

When we execute an update SQL, how do we execute it? Let's execute a simple SQL update statement (default storage engine InnoDB)
update T set c=c+1 where ID=2;

Step 1: Connector <br>Connect to the database through the connector first. The connector is responsible for establishing a connection with the client, verifying the correctness of the user name and password, and at the same time obtaining the user's authority and placing it in the cache, maintaining and managing the connection
Step 2: Cache <br>After the connection is established, if the SELECT query statement is executed, it will query whether the SQL result set exists in the cache. If there is a result, then verify the permissions of the user table and data, and finally the query will be obtained. The result is returned. If it is an update operation such as UPDATE, DELETE, etc., then the query cache related to this table will be invalidated, so this statement will clear all cached results on table T.
Step 3: The analyzer <br>If the query cache is not hit, the actual statement execution begins. First, MySQL needs to know what you're doing, so it needs to parse the SQL statement.
The analyzer will first do "lexical analysis". What you input is an SQL statement composed of multiple strings and spaces. MySQL needs to identify what the strings are and what they represent. For example, whether column c exists in table T in this statement, etc.
After completing these identifications, it is necessary to do "grammatical analysis". According to the result of lexical analysis, the parser will judge whether the SQL statement you input satisfies MySQL grammar according to the grammar rules. Whether the update, where, etc. in the SQL statement conform to the SQL syntax
Step 4: Optimizer <br>After the analyzer, MySQL knows what to do. Before starting to execute, it is also processed by the optimizer. The optimizer decides which index to use when there are multiple indexes in the table; or when a statement is associated with multiple tables (join), decides the join order of each table; the optimizer decides to use the ID index. Specifying the index also specifies which interface of the storage engine the subsequent executor needs to call for execution.
Step 5: Actuator
MySQL knows what you want to do through the analyzer and how to do it through the optimizer, so it enters the executor phase and starts executing the statement. When starting the execution, you must first judge whether you have the permission to execute the query on this table T. If not, an error of no permission will be returned. The executor is responsible for the specific execution, finds this row, and then updates it.

2.3 InnoDB storage engine introduces REDOLOG

Mysql itself has its own log record binlog (archive log: divided into two modes: row and statement), but only relying on binlog has no crash-safe capability, so InnoDB uses another set of log system redolog at the storage engine layer to achieve crash. -safe ability. At the same time, in order to achieve better read and write performance, InnoDB will cache data in the memory (InnoDB Buffer Pool), and the modification of disk data will also lag behind the memory. At this time, if the process or machine crashes, the memory data will be lost, thus Ensure the consistency and durability of the database itself. Before modifying the Page, you need to record the modified content in REDO, and ensure that the REDO LOG is placed on the disk earlier than the corresponding Page, which is often referred to as WAL, Write Ahead Log. When a failure occurs and memory data is lost, InnoDB will restore the Page to the state before the crash by replaying REDO when it restarts.

So what kind of REDO do we need?

First, the maintenance of REDO adds a copy of disk write data. At the same time, in order to ensure that the data is correct, the transaction can only be returned to the user when all his REDOs are placed on the disk. The write time of REDO will directly affect the system throughput. Obviously, the amount of data in REDO To be as little as possible. Secondly, system crashes always happen unexpectedly. When restarting and replaying REDO, the system does not know which pages corresponding to REDO have been dropped. Therefore, REDO replay must be reentrant, that is, REDO operation must be guaranteed. Idempotent. Finally, in order to speed up the restart and recovery speed through concurrent replay, REDO should be Page-based, that is, a REDO only involves the modification of one Page.

The small amount of data is the advantage of Logical Logging, and idempotency and Page-based are the advantages of Physical Logging. InnoDB takes an approach called Physiological Logging to get the best of both worlds. The so-called Physiological Logging is to use Page as the unit, but record it in a logical way within the Page. For example, a REDOLOG acting on the Page type records the modification of a Record in the Page, the method is as follows:
(Page ID, Record Offset, (Filed 1, Value 1) … (Filed i, Value i) … )
Among them, PageID specifies the Page page to be operated, Record Offset records the offset position of the Record in the Page, and the following Field array records the Field to be modified and the modified Value.

2.4 REDOLOG record content

Among them, Type is the function object of the record (according to the different function objects of REDO records, it can be divided into three categories: acting on Page, acting on Space, and Logic types that provide additional information), Space ID and Page Number uniquely identify a Page page , these three items are the header information that all REDO records need to have.

The latter is unique to the MLOG_REC_UPDATE_IN_PLACE type (acting on Page), where Record Offset is used to give the position offset of the record to be modified in the Page, and Update Field Count indicates that there are several Fields in the record to be modified, followed by each Field gives Field Number, Field Data Length and Field Data

3 Summary

By tracking the execution process of an update SQl statement, we can understand the execution process of Mysql, understand the content format of REDOLOG data, and fundamentally understand the design ideas and principles of REDOLOG, which can provide ideas for the development and design of future application systems. reference and practice.