MySQL architecture _ MySQL

To learn a database system, you must first understand its architecture and its architecture principles, which will be of great help for later analysis and performance tuning. then, you can understand it by analyzing the architecture diagram. Directory overview architecture diagram summary architecture. connection management and security verification each client will build and learn a database system. First, you must understand its architecture. understanding its architecture principles will be of great help to the analysis problems and performance tuning in the future, next we will understand it by analyzing the structural diagram.

Directory

Overview

Architecture diagram

Summary

Architecture diagram

1. connection management and security verification

Each client establishes a thread to connect to the server. the server has a thread pool to manage these connections. if the client needs to connect to the MYSQL database, verification is required, including user name, password, and host information.

2. parser

The parser mainly analyzes query statements and generates a parsing tree. First, the parser analyzes the syntax of the query statement to check whether there is a problem with the syntax. In addition, the parser queries the cache. if a corresponding statement exists in the cache, the query result is returned and the subsequent optimization operations are not performed. The premise is that the data in the cache is not modified. of course, if the data is modified, it will be cleared out of the cache.

In some books, the query cache is placed before parsing. do you not need to judge whether the syntax is wrong? If you have any questions, please try again.

3. Optimizer

The Optimizer is mainly used to optimize query statements, including selecting appropriate indexes and data reading methods, including obtaining query overhead information and statistical information, this is why there is an arrow in the graph that the optimizer points to the storage engine. I have not seen the relationship between the optimizer and the storage engine in other articles. my personal understanding here is that the optimizer needs to use the storage engine to obtain the approximate query data and statistical information.

4. actuator

The executor includes executing the query statement, returning the query result, and generating the execution plan including some processing operations with the storage engine.

Storage Engine

1. lock types

The reason for the lock is to solve concurrency control. for a query statement to prevent the queried data from being changed by other statements, it is necessary to lock the data, we are familiar with the shared and exclusive locks.

2. lock granularity

Any operation consumes resources, and adding locks also consumes resources. for the same query, if we lock fewer resources, the concurrency increases, and the resource consumption increases, on the contrary, the lower the low consumption of concurrency.

Table lock)

Table lock is the minimum resource consumption lock in MYSQL. you only need to apply a lock to the corresponding table in the query. An SQL statement first needs to obtain the lock. First, the query statement will judge whether there is a lock on the table. if there is an exclusive lock on the table, the query statement will wait, therefore, you only need to obtain the lock once in the query statement. you do not need to determine whether a row lock exists at the next layer. the lock resource consumption is low. At the same time, if there is an exclusive lock attached to the table, other statements need to wait for the lock to be released, and the system concurrency is very low.

Row lock)

The row lock is the opposite to the table lock. An update statement first checks whether exclusive locks exist in the corresponding table. If no exclusive locks exist, it then checks whether there is a lock in the corresponding update record, if no exclusive lock exists. Row locks consume more resources than table locks, but because they only add locks on the corresponding record rows, other record rows in the corresponding table are not affected, the system concurrency is higher than the table lock.

Deadlock

In a high-concurrency system, deadlocks often occur due to the existence of locks and transactions. For example, a simple deadlock occurs when two transactions are executed simultaneously.

Transaction 1

Start transaction;

Update user set name = 'zhangsan' where id = 1

Wait 5 seconds

Update user set name = 'Li si' where id = 2

COMMIT;

Transaction 2

Start transaction;

Update user set name = 'zhangsan' where id = 2

Wait 5 seconds

Update user set name = 'Li si' where id = 1

COMMIT;

Because the two transactions lock the corresponding row records when updating the statement on the first day, when the statement is updated on the second day, the corresponding record row has been locked and needs to wait, this is a common deadlock.

3. Transactions

A major difference between mysql and other database products is that transactions are determined by the storage engine. for example, MYISAM, MEMORY, and ARCHIVE do not support transactions, A transaction is to solve a set of queries, either all of which are successfully executed or all of which fail.

Mysql transactions are automatically committed by default, unless a transaction is displayed

Show variables like 'autocommit ';

Modify automatic submission mode, 0 = OFF, 1 = ON

Note: modifying automatic commit is invalid for non-transaction tables because they do not have the commit and rollback concepts. some commands will force automatic commit, such as DLL commands and lock tables.

Set autocommit = OFF

Or

Set autocommit = 0

What you may be familiar with is the ACID properties of transactions: atomicity, consistency, isolation, and durability.

Atomicity: a transaction is an inseparable minimum unit of work. The entire transaction must be committed in full or fail to be rolled back.

Consistency: The database always changes from one consistent state to another consistent state.

Isolation: Changes made by a firm are invisible to other transactions before they are finally committed.

Persistence: once the transaction is committed, the changes will be permanently stored in the database. even if the system crashes, data will not be lost.

Isolation level

The isolation level is used to define the changes made by a firm. by default, the system uses the isolation level. Generally, the higher the isolation level, the lower the system concurrency, and the higher the system overhead, mysql has four isolation levels:

Uncommitted read (read uncommitted): the uncommitted read isolation level is also called READ dirty, which means that the transaction can read uncommitted data from other transactions.

Read committed: in other database systems such as SQL Server, the default isolation level is COMMITTED READ, the committed read isolation level is invisible for modifying other transactions before the transaction is committed.

Repeatable read: ensures that the results of multiple identical queries in the same transaction are consistent, for example, if a transaction queries a record at the beginning and then executes the same query after several seconds, the results of the two queries must be the same, deduplication is also the default isolation level of mysql ,.

SERIALIZABLE: SERIALIZABLE ensures that no new data is inserted within the read range. for example, when a transaction first queries data within a certain range, the second query also obtains data of the same range, and no new data is inserted into the range.

Query System default isolation level, current session isolation level

Select @ global. tx_isolation, @ tx_isolation;

Set the current isolation level of the system and session to committed read.

/* Set the current isolation level of the system */

SET global transaction isolation level read committed;

/* Set the current session isolation level */

Set session transaction isolation LEVEL read committed;

Note: Avoid mixed storage engine tables in the same transaction, such as tables with mixed transactions and non-transaction transactions. normal commit will not be affected. if a rollback operation occurs, non-transaction table modifications cannot be rolled back, which will lead to database inconsistency. once this happens, it will be difficult to fix it. Therefore, it is important to select a proper storage engine for the table.

Implicit and display locking

The so-called implicit locking means that the system automatically locks instead of adding locks. INNODB uses two lock protocols to execute locks at any time during transaction execution, all locks are released at the same time when you execute commit or rollback.

The display lock is a manual lock, such as lock tables or unlock tables. the display lock is based on the server layer settings and is irrelevant to the storage engine, whether your table is myisam or innodb, you can add the lock tables. However, myisam itself is the lock tables, so you can add more tables.

Note: Avoid using lock tables in transactions.

Storage

Storing this knowledge is explained separately later.

Summary

Each person may have a different understanding of the mysql architecture. it is based on his own understanding that it is not an official standard architecture and is for reference only.