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

This article mainly introduces how the SQL implementation process in MySQL is, which is very detailed and has a certain reference value. Friends who are interested must read it!

MYSQL architecture

Take a look at an architecture diagram first, as follows:

Module detailed explanation

Connector: used to support interaction between various languages and SQL, such as PHP,Python,Java 's JDBC

Management Serveices & Utilities: system management and control tools, including backup and recovery, MySQL replication, clustering, etc.

Connection Pool: connection pooling, managing resources that need to be buffered, including user password permissions threads, etc.

SQL Interface: used to receive the user's SQL command and return the query results required by the user

Parser: used to parse SQL statements

Optimizer: query optimizer

Cache and Buffer: query cache, besides row record cache, there are table cache, Key cache, permission cache and so on.

Pluggable Storage Engines: plug-in storage engine that provides API to the service layer to deal with specific files.

Architecture layering

The MySQL is divided into three layers, the connection layer with the client, the service layer that really performs the operation, and the storage engine layer that deals with the hardware.

Connection layer

If our client wants to connect to port 3306 of the MySQL server, it must establish a connection with the server, so manage all connections and verify the identity and permissions of the client. These functions are completed in the connection layer.

Service layer

The connection layer gives the SQL statement to the service layer, which contains a series of processes:

For example, the judgment of query cache, calling the corresponding interface according to SQL, parsing our SQL statements lexically and grammatically (such as how to identify keywords, aliases, syntax errors, etc.).

Then there is the optimizer, the bottom layer of MySQL will optimize our SQL statement according to certain rules, and then give it to the executor to execute.

Storage engine

The storage engine is where our data is really stored, and different storage engines are supported in MySQL. Further down is memory or disk.

The execution process of SQL

Taking a query statement as an example, let's take a look at the workflow of MySQL.

Select name from user where id=1 and age > 20

First of all, let's take a look at a picture, and the following process is based on this picture:

Connect

The first step for a program or tool to operate a database is to establish a connection with the database.

There are two kinds of connections in the database:

Short connection: short connection means that close is dropped immediately after the operation is completed.

Persistent connections: persistent connections can be kept open, reducing the cost of creating and releasing connections on the server side, which can also be used when later programs access.

It is troublesome to establish a connection, first of all, send a request, send a request to verify the account password, and check the permissions you have after verification, so try to use a long connection in the process of use.

Maintaining a long connection consumes memory. If the connection is inactive for a long time, the MySQL server will be disconnected. You can use the SQL statement to view the default time

Show global variables like 'wait_timeout'

This time is controlled by wait_timeout, which defaults to 28800 seconds and 8 hours.

Query cache

MySQL comes with a cache module built into it. After executing the same query, we found that the cache did not take effect. Why? Caching for MySQL is off by default.

Show variables like 'query_cache%'

Turning it off by default means it is not recommended, so why doesn't MySQL recommend using its own cache?

This is mainly due to the limited application scenarios of the cache that comes with MySQL:

The first is that it requires that SQL statements must be exactly the same, with an extra space in the middle, and different cases of letters are considered to be different SQL.

The second is that when any piece of data in the table changes, all caches in the table will be invalidated, so it is not suitable for applications with a large number of data updates.

Therefore, it is more appropriate to leave caching to the ORM framework (for example, MyBatis has first-level caching enabled by default), or to an independent caching service, such as Redis.

In MySQL 8.0, the query cache has been removed.

Syntax parsing and preprocessing

Why can a SQL statement be recognized? If you execute a random string hello, the server reports a 1064 error:

[Err] 1064-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 'hello' at line 1

This is the parser and preprocessing module of MySQL.

The main thing to do in this step is to do lexical and syntactic analysis and semantic parsing of the sentence based on SQL grammar.

Lexical analysis

Lexical analysis is to break a complete SQL sentence into words.

For example, a simple SQL statement: select name from user where id = 1 and age > 20

It will identify the select, this is a query statement, and then it will also identify the user, you want to do a query in this table, and then identify the conditions behind the where, so I need to find these contents.

Grammar analysis

The parser does some syntax checking on the SQL, such as whether the single quotation marks are closed, and then generates a data structure based on the SQL statement according to the syntax rules defined by MySQL. This data structure is called parse tree (select_lex).

For example, the English grammar "I use is, you use are" is definitely not allowed if it is not correct. After syntax analysis, you will receive an error message from You hava an error in your SQL syntax if you find that your SQL statement does not conform to the rules.

Preprocessor

If you write a SQL with correct morphology and syntax, but the table name or field does not exist, where will the error be reported? Is it at the execution layer of the database or the parser? For example: select * from hello

Still report an error during parsing. There is a preprocessor in parsing SQL. It examines the generated parsing tree to resolve semantics that cannot be parsed by the parser. For example, it checks the existence of table and column names, and checks names and aliases to make sure there is no ambiguity. A new parsing tree is obtained after preprocessing.

Query optimizer

Is there only one way to execute a SQL statement? Or is the final SQL executed by the database the SQL we sent?

The answer is no. A SQL statement can be executed in many ways and eventually returns the same result, which is equivalent. But if there are so many modes of execution, how do you get them? Which one should be chosen to execute in the end? According to what criteria to choose?

This is the module (Optimizer) of MySQL's query optimizer. The purpose of the query optimizer is to generate different execution plans (Execution Plan) according to the parsing tree, and then choose an optimal execution plan. MySQL uses a cost-based optimizer, which is the least expensive execution plan.

You can use this command to view the cost of the query:

Show status like 'Last_query_cost'

What types of optimizations can MySQL's optimizer handle?

Here are two simple examples:

1. When we make an associated query on multiple tables, the data of which table is used as the base table.

2. When there are multiple indexes that can be used, choose which index.

In fact, for every kind of database, the optimizer module is essential, and they achieve the goal of optimizing query efficiency as much as possible through complex algorithms. But the optimizer is not omnipotent, not even junk SQL statements can be automatically optimized, and we can not always choose the best execution plan. We should pay attention to it when writing SQL statements.

Carry out the plan

The optimizer eventually turns the parse tree into an execution plan (execution_plans), which is a data structure. Of course, this execution plan is not necessarily the optimal execution plan, because MySQL may not cover all execution plans.

How do we view MySQL's execution plan? For example, if multiple tables are associated with a query, which table should be queried first? Which indexes might be used when executing the query, and what indexes are actually used?

MySQL provides a tool to execute the plan. We can see the information about the execution plan by adding EXPLAIN before the SQL statement.

EXPLAIN select name from user where id=1; storage engine

Before introducing the storage engine, let's ask two questions:

1. From a logical point of view, where is our data put, or in what structure?

2. Where will the implementation plan be carried out? Who's going to do it?

Basic introduction of Storage engine

In a relational database, the data is placed in the table Table. We can think of this table as an Excel spreadsheet. So our table not only stores the data, but also organizes the storage structure of the data, which is determined by our storage engine, so we can also call the storage engine table type.

In MySQL, multiple storage engines are supported, and they are replaceable, so they are called plug-in storage engines. Why support so many storage engines? Isn't one enough?

In MySQL, each table can specify its storage engine, rather than using only one storage engine for a database. The use of the storage engine is based on tables. Also, you can modify the storage engine after creating the table.

How to choose a storage engine?

If you have high requirements for data consistency and need transaction support, you can choose InnoDB.

If there are more data queries and less updates, and the query performance is relatively high, you can choose MyISAM.

If you need a temporary table for the query, you can choose Memory.

If all storage engines do not meet your needs and have sufficient technical capabilities, you can develop a storage engine in C according to the internal manual of the official website. (https://dev.mysql.com/doc/internals/en/custom-engine.html%EF%BC%89)

Executive engine

Who uses the execution plan to operate the storage engine? This is the execution engine (executor), which uses the corresponding API provided by the storage engine to complete the operation.

Why did we modify the storage engine of the table without any change in the mode of operation? Because storage engines with different functions implement the same API.

Finally, the data is returned to the client, even if there is no result.

Chestnut

Or take the above sql statement as an example, and then comb through the entire sql execution process.

Select name from user where id = 1 and age > 20

Query whether the role of the current executor has permissions through the connector. If there is, go on, if not, it will be rejected, and the error message of Access denied for user will be reported.

The next step is to query the cache, first to see if there is any in the cache, if so, there is no need to go down and return the result directly to the client; if it is not in the cache, then execute the syntax parser and preprocessing module. (version 8.0 of MySQL deletes the whole function of query cache directly.)

Syntax parser and preprocessing are mainly used to analyze whether the lexical and grammatical of sql statements are correct. If there is no problem, the next step is to go to the query optimizer.

The query optimizer will optimize the sql statement to see which method is the most cost-effective, and which sql statement will be executed. The sql above has two optimization schemes:

First look up the names of the people whose id is 1 in the table user, and then look for those who are older than 20 years old.

First query all the people in the table user who are older than 20 years old, and then look for those with an id of 1.

After the optimizer decides which scheme to choose, the execution engine executes it. The result is then returned to the client.

The above is all the contents of the article "what is the implementation process of SQL in MySQL". Thank you for reading! Hope to share the content to help you, more related 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.