Detailed explanation of MySQL execution principle, logical layering, and changing database processing engine

Having used MySQL for such a long time, I believe I have already memorized the SQL statements. So, I tried to understand its execution principle. The following are the key points I summarized during the learning process.

As long as it is a B/S architecture, there will be a client and a server, and MySQL is no exception.

First, the client sends a request, which is a query request (Select), and the object of the request is the server. How does the server handle this query function?

Logical Layering

First of all, we can imagine the server as a large container with four layers of structure. When a request comes in, these four layers will be executed, and only after one execution will the desired result be returned to us.

First layer: connection layer

In other words, when our client sends a Select, it is directly handed over to the connection layer for processing, and its role is to provide services for connecting to the client.

The connection layer only establishes a connection with the client and gets our request (Select), but it does not process it itself because it only makes connections. So who will process it? Continue walking down.

Second layer: service layer

The service layer can do two things:

1. Provide users with various interfaces that can be used

For example, the query just now is that our common CRUD (create, delete, modify and query) operations are all here. The connection layer gets this Select and directly gives it to the service layer. In addition to providing an interface to the user, it also provides something called a SQL optimizer.

2. MySQL Optimizer

When we write a SQL statement to execute, after executing here (the second layer), the optimizer will think that the performance of the SQL statement I wrote is not good enough. At this time, the optimizer will write a SQL statement equivalent to the one I wrote after execution to replace it. This equivalent writing method is to optimize the SQL statement you wrote through this optimizer. Because it thinks that the performance of your SQL statement is too low, it optimizes your SQL statement. This optimization operation is what the optimizer does;

Obviously, the optimizer can optimize SQL statements, which can have certain benefits on performance, but it also has disadvantages! For example:

"When you optimize yourself, it is a, but the actual execution is not a, because the optimizer will think that your optimization is not good enough, and the result is that a becomes b. In other words, I clearly wrote a, but the program executes b."

This would cause confusion in our development process since it is already optimized. What you wrote is no longer consistent with what you wrote before.

The third layer: engine layer

It provides various ways to store data, the most common ones are: lnnoDB, MylSAM

Key differences:

lnnoDB [MySQL default]: When it was designed, it was transaction-first [suitable for high-concurrency operations] Principle: Because it is a row lock, I have to lock every piece of data. If I lock too much, the performance will be reduced. Although the performance is reduced, it is suitable for high concurrency and is not prone to errors.

MylSAM: Performance priority principle: Because it is a table lock, the ten data in the table are not affected, and the ten locks are completed at one time, so the performance is fast

Performance priority is easy to understand. For example, if there are 10,000 pieces of data, the faster it is processed, the higher the efficiency will be.

Things are to prevent some concurrent operations. Too much concurrency may cause errors, so it is suitable for high-concurrency operations.

So when doing a project, when building a database, if performance is the priority, choose the MySAM engine. If it is a high-concurrency operation, choose the lnnoDB engine. I will talk about how to change the engine below.

Layer 4: Storage Layer

This is easy to understand. The final data is stored in the storage layer.

After talking about the four layers, let's clarify our thinking

First, the client sends a Select operation---> the connection layer receives it and sends it to the service layer---> the service layer optimizes your query and gives the optimization result to the engine layer---> select the engine of the current database. After selecting the engine, the engine sends the final data to the storage layer---> the storage layer uses the storage layer to store data

View Engine

show engines; View all engines supported by MySQL

Execution Result:

Yes means support. In the InnoDB support option, it is DEFAULT (default), which means that when we create a database, the default engine is InnoDB.

View the engine currently used by the database

show variables like '%storage_engine%';

Execution Result:

Obviously, we are currently using InnoDB

Specifies the engine for database objects

I am now creating a table. I am not using InnoDB for this table, but MySAM, because I want to prioritize performance. Now I will use the mysql command line to create the table

1. Specify database command: use database name

2. Create a simple table in the specified database

create table tb(

id int (4),

name varchar(5),

primary key(id)

)ENGINE=MyISAM;

Execution Result:

It indicates that the creation is successful, and the engine of the table is the one we manually specified, not the default one.

The above are all the knowledge points related to MySQL execution principles, logical layering, and changing the database processing engine. Thank you for your reading and support for 123WORDPRESS.COM.