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

What is the logical architecture of MySQL? in view of this question, this article introduces in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Logical architecture diagram:

Let's simplify the above diagram to have the simple logical architecture of MySQL as shown below, and we will analyze each component in detail later.

MySQL can be divided into Server layer and storage engine layer as a whole.

Server layer

Most of the core service functions of MySQL are in the Server layer, which includes connectors, query caching, parsers, optimizers, and executors.

The Server layer covers most of the functions of MySQL, including query parsing, analysis, optimization, caching, and all built-in functions (such as date, time, mathematics, and encryption functions). All cross-storage engine functions are implemented in this layer: stored procedures, triggers, views, and so on.

Connector

Each client connection will have a thread in the server process, and the query for this connection will only be executed in this separate thread.

When a client application connects to a MySQL server, it is the connector that first receives it. Connectors are responsible for establishing connections with clients, obtaining permissions, maintaining and managing connections.

Command to connect to the MySQL server:

Mysql-h$ip-P$port-u$user-p

After typing the command, we will be prompted for the password, or we can write the password after-p, but there is a risk that the password will be leaked.

If we cannot connect to the server according to the user name and password we entered, we can see the following error:

[root@codegirl] # mysql-hlocalhost-P3306-uroot-p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

This error message is returned by the connector.

So when we establish a connection to the server through the client command mysql, the connector does two things:

Authenticate the user name and password. If the authentication fails, we receive the above 1045 exception and the client program ends execution. If the authentication is successful, the client establishes a connection with the server.

After the connection is successful, the connector will continue to verify the user's permissions, such as which tables we have query permissions, which tables have modification permissions, or authorization permissions. After that, the permission judgment logic in this connection is based on the permissions read at this time. So if you change the permissions, be sure to reconnect!

The connection of connector is divided into long connection and short connection.

Persistent connection: after a successful connection, if the client continues to make requests, it will always be used through a connection.

Short connection: disconnect each time a few queries are executed, and re-establish a connection next time.

The process of establishing a connection is complicated, and now most services use long-term connections.

If there is no request from the client after the connection is established, the connection will be disconnected. This time is controlled by the parameter wait_timeout, which defaults to 8 hours.

View the connection time settings for MySQL:

Mysql > show variables like 'wait_timeout%'

The timeout time is set in seconds, 28800 pounds, 60 pounds, 60 hours.

Query cache

Once the connection is established, we can execute the sql statement.

Select query statement:

Mysql > select * from user where id = 1

It does not query the data in the table directly, but first queries the cache, if it exists in the cache, it directly returns the data in the cache, and then queries the data in the table if it does not exist in the cache, and then adds the query results to the cache.

This logic is like adding Redis cache to reduce the pressure on the database. If the cache exists, there is no need for subsequent parsing and execution steps, and efficiency will be greatly improved.

The data cached by MySQL exists in the form of key-value. Key is our query sql statement, and value is the query result corresponding to this sql statement.

At this time, we can't help thinking that if the data in the database changes frequently, the cache needs to be invalidated in time, so that we can get the latest data in the next query.

Yes, MySQL all caches for the table will be invalidated whenever there is a change in the table's data or table structure. So if it is a table that often involves additions, deletions and changes, the cache does not have much practical significance. It may be updated as soon as the cache is added, and the cache added by the boss is invalid again. But if our table is a static table like the system configuration, caching can work.

In development, if we test the execution time of a sql, we first determine whether the cache is available. The command to query whether the cache is available:

Mysql > show variables like'% have_query_cache%'

Caching is available by default:

To modify the configuration of the cache, we can modify the configuration file of MySQL: / etc/my.cnf, and add configuration: query_cache_type=0

The options are: 0, 1, 2: 0 means not to use cache, 1 means to use cache, and 2 means to use it as needed.

You can also use the command:

Mysql > set global query_cache_type = 0

Check to see if caching is on:

Mysql > select @ @ query_cache_type

If we want to be able to use caching for certain sql statements after caching is turned off, we can explicitly specify sql to use caching through SQL_CACHE.

Mysql > select SQL_CACHE * from user

The MySQL8.0 version has completely removed the cache, and we only need to know about the caching component. You need to be aware of the impact of caching on performance when using different versions of MySQL.

Parser

When the sql statement is actually executed, the parser first parses the sql statement we entered, and the MySQL parser parses the sql statement into an internal data structure (parsing tree), which the optimizer can then optimize.

The sql statement we give to the parser is made up of strings and spaces. The first step of the parser is to parse the string and identify what each string represents.

The parser recognizes the sql keyword to know whether we are querying or updating. The parser recognizes the string 'user' as a table name and the string' id' as a column. After the parser recognizes the string, it begins to verify that the string we give conforms to the syntax specification.

The parser validates the syntax and verifies the existence of tables and columns based on the parsed tables and columns.

If the table or column does not exist, or if there is a problem with the syntax, we can receive an error message.

Mysql > select * from aa where id = 1

ERROR 1146 (42S02): Table 'test.aa' doesn't exist

Mysql > select * fromuser where id=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fromuser where id=1' at line 1 optimizer

After being processed by the parser, the parsing tree is obtained. At this point, MySQL already knows exactly what it is going to do, but optimizes sql before starting execution.

The optimizer optimizes the sql statement, including rewriting the query, determining the reading and writing order of the table, selecting the appropriate index, and so on.

The optimizer involves a lot of content, we first have a preliminary impression of it, and then we will learn more about it later.

After the optimizer, the execution scheme of the sql statement has been determined, and the parsing goes into the executor to begin execution.

Actuator

When the executor executes the sql statement, it first verifies that it has permissions on the table, and if it does not, it returns an error message that it has no permissions. If you have permission, the table will be opened to continue execution. When the table is opened, the executor uses the interface provided by the execution engine according to the table's execution engine.

Storage engine

The storage engine layer is responsible for data storage and extraction. Storage engine is plug-in, supporting InnoDB, MyISAM, Memory and other storage engines. MySQL also provides some third-party storage engines. This plug-in structure design enables different companies to choose different engines according to their own needs.

Now the most commonly used storage engine is InnoDB, which is the default storage engine after the MySQL5.5.5 version. If we do not specify the storage engine type when creating the table, the default is InnoDB.

Different storage engines have a common Server layer, and the ability to distinguish between the Server layer and the engine layer is important for us to learn locks and transactions later.

Different engines hold data and indexes in different ways, but the definition of the table is the responsibility of the MySQL service layer, which is consistent.

Today we only look at two common storage engines, InnoDB and MyISAM, and other engine partners can view the relevant documentation if they are interested.

InnoDB

Let's first take a look at the information about the 'user' table, whose storage engine is InnoDB.

Mysql > show table status like 'user'\ G * * 1. Row * * Name: user # Table name Engine: InnoDB # Storage engine Type Version: 10 Row_format: Dynamic # Row format If the table contains fields of variable length such as Varchar, then it is Dynamic Rows: 0 # rows. For the InnoDB engine, this is the estimated Avg_row_length: 0 # average number of bytes per row Data_length: 16384 # size of table data (bytes) Max_data_length: 0 # maximum capacity of table data Engine related Index_length: 0 # size (in bytes) Data_free: 0 Auto_increment: NULL # next self-increasing value Create_time: 2021-02-16 14:24:46 Update_time: NULL Check_time: NULL Collation: utf8_general_ci # default character set Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)

InnoDB's data is stored in a tablespace, and it stores the data and indexes of each table in a separate file. The user' table has two data files on disk:

.frm file: represents the definition of the table, defined by the server layer of MySQL.

.ibd files: data and index files.

InnoDB uses MVCC multi-version control to support high concurrency. And it implements four standard transaction isolation levels, and the default isolation level is repeatable. It supports row locking and prevents phantom reading through a gap locking strategy.

InnoDB is based on clustered index and has high performance for primary key-based queries.

MyISAM

Let's first take a look at the information for the 'user_isam' table, whose storage engine is MyISAM.

Mysql > show table status like 'user_isam'\ G * * 1. Row * * Name: user_isam Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data _ length: 0 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2021-02-16 16:36:25 Update_time: 2021-02-16 16:36:25 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (2021 sec)

MyISAM stores the table in two files: a data file and an index file.

.frm file: represents the definition of the table, defined by the server layer of MySQL.

.MYD file: represents a data file.

.MYI file: represents an index file.

MyISAM provides many features, but it does not support transactions and row locks, it locks the entire table, and it cannot be safely recovered after a crash, which is why it was replaced by InnoDB.

This is the answer to the question about what the logical architecture of MySQL is. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.