<>Mysql Logical architecture of

Mysql The logical architecture of the is as follows , The whole is divided into two parts ,Server Tier and storage engine tier .

All operations unrelated to the storage engine are in Server Layer completed , The storage engine layer is responsible for data access .

The function of each step will be introduced according to the process above , Here, take querying a record as an example .

<> Connector

This step is mainly to manage connection and permission verification .

Responsible for managing client connections , such as mysql -u root -p
, This is done between the client and the connector , The connection is divided into long connection and short connection , Long connections are recommended , Because establishing a connection is a relatively complex process . However, long connections also have room for optimization , That is, there are too many long connections , As you perform large query operations , It will occupy more memory .

After establishing the connection , The connector determines the permissions of the user , After that, the user's operation will determine whether to allow it based on the permission .

<> analyzer

This step is mainly lexical analysis and grammatical analysis .

Lexical analysis is mainly to determine what users want to do , such as select Just want to query .

Syntax analysis is mainly used to determine the input of users SQL Compliance Mysql Grammar of .

<> optimizer

After analyzer ,Mysql Already know what users want to do , But for the same one SQL sentence , about Mysql Specific implementation time , There may be many ways to achieve it , Efficiency is different .

At the optimizer step ,mysql It is necessary to determine how to implement the optimal efficiency .

<> Actuator

This step is mainly to operate the engine and return the results . Traverse a data table by operating the storage engine layer , To find qualified data , And return to the client .

<>Mysql The process of executing an update statement

With one SQL The same as the query statement is , stay Mysql It also needs to pass through the connector , analyzer , optimizer , Actuator , The storage engine is also used to access data .

The difference is , The update statement needs to involve two important log modules ,redo log and binlog

<>redo log

A restaurant is booming , But as a hotel , It's inevitable that there are credit and repayment every day .

It's okay if there aren't many people to credit and repay , The boss can record directly with a powder board , If there are many people , Pink board can't write down a month's credit record at all .

So the boss thought he could write all the credit records in the account book , What is written on the pink board is short-term , After work , Take the on the powder board and the account book for reconciliation .

In this example , Powder board is redo log, The ledger is mysql Records in
, Let's use the debt repayment analogy mysql Update process of , If every time someone updates , We all go mysql Found this record in , Low efficiency , therefore mysql Your ideas are in line with the boss , Update operation first redo
log in , Digest slowly after a period of time .

This idea is called WAL technology , Namely Write Ahead Logging technology , Write a log first , Re write disk .

It should be noted that , If the powder board is full , The boss hasn't finished work yet , The boss must stop his work .mysql Medium redo log Total can be recorded 4GB Operation of

When write pos catch up check point,mysql The boss is going to deal with it redo log Yes .

besides , Yes redo log Persistence , Even if the database is restarted abnormally, the log will not be lost , This is crash safe mechanism , But it still needs to be noted that ,redo
log yes innodb Storage engine specific .

<>bin log

binlog yes Server Layer log , Applicable to all storage engines .

Then since there is binlog Yes , Why do you want one innodb Storage engine specific redo log And ?

because mysql Not at first innodb engine , But myisam engine , Using binlog, however binlog Archive only , No, crash safe
mechanism , So I added one redo log.

* redo log yes innodb Storage engine specific , and binlog yes server Layer specific
* redo log Physical logs are stored ,binlog Is a logical log
* redo log As mentioned above , support 4GB size , If there is more, we have to deal with it and cover it ,binlog Log when a log file is filled , A new log file will be created .
Below to update ID by 2 Take a row of data as an example :

The light colored box in the figure indicates that InnoDB Internally executed , The dark box indicates that it is executed in the actuator .

* The actuator shall find the engine first ID=2 This line .ID Is primary key , The engine finds this line directly with a tree search . If ID=2
The data page where this row is located is already in memory , It returns directly to the actuator ; otherwise , You need to read into memory from disk first , Then return .
* The actuator gets the row data given by the engine , Add this value to 1, For example, it turned out to be N, Now it is N+1, Get a new row of data , Then call the engine interface to write this line of new data .
* The engine updates this new row of data into memory , At the same time, record the update operation to redo log inside , here redo log be in prepare
state . Then inform the actuator that the execution is completed , Transactions can be committed at any time . The actuator generates the for this operation binlog, And put binlog Write to disk .
* The executor calls the commit transaction interface of the engine , The engine just wrote redo log Change to submit (commit) state , Update complete .
<> reference resources

Geek time Ding Qi

©2019-2020 Toolsou All rights reserved,
C++ of string of compare usage Python Study notes ( one )evo Tool usage problems ——Degenerate covariance rank, Umeyama alignment is not possibleRISC-V_GD32VF103-TIMER0 timer interrupt java Array subscript variable _Java Basic grammar : array be based on stm32 Control four-wheel trolley motor drive ( one ) be based on redis Design of liking function Software engineering career planning mysql Query random data by conditions _MySQL Random query of several qualified records centos7 install RabbitMq