Shulou(Shulou.com)06/02 Report--

This article mainly explains "the logical architecture principle of mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the logical architecture principle of mysql".

1. General logical framework

First give a logical architecture diagram, this diagram is for you to analyze and understand from a macro point of view.

From this picture, you can get the following information:

(1) the mysql architecture can be divided into four layers.

(2) the first layer is mainly the connection of different clients.

(3) the second layer mainly deals with the connection of the client.

(4) the third layer deals with the sql statements of the client.

(5) in the fourth layer, the storage engine actually executes sql statements.

Now that you have a good understanding of the overall structure of mysql and a general understanding of what each layer is for, we are gradually looking at it in detail. after all, the picture above is still too general.

Second, the detailed logical architecture diagram

Look at the picture below.

This picture is more detailed than the previous one, and it is still divided into four layers from top to bottom.

Layer 1: here we can see that there are many clients connected, such as PHP, python, jdbc, and so on.

The second layer: this layer is the link layer, corresponding to the first diagram of the connection processing layer, mainly to deal with the client connection, this is because there are many kinds of clients.

The third layer: this layer is the service layer. How to deal with sql statements that mainly deal with client requests? The general process is to query the cache first, and if the cache exists, take it out and return it directly. If not, the parser parses, and then the optimizer optimizes, and the functions provided by each storage engine are concentrated in this layer, such as stored procedures, triggers, views, and so on.

Layer 4: this layer is the engine layer, where we can see several different storage engines, such as InnoDB,MyISAM,memory and so on. Really responsible for data storage and extraction in MySQL.

If we use jdbc to execute an inserted sql statement, how does the whole process execute?

First of all, jdbc belongs to the first layer, connects to the mysql server through the connection processing of the second layer, then parses the sql statement through the parser, then optimizes the sql statement through the optimizer, and finally calls the interface of the storage engine of the fourth layer, executes the insert statement, and inserts the data into the file or other file system.

Isn't it easy. Usually when learning mysql, the teacher will introduce the storage engine. At that time, you may not have noticed his importance, but now I believe you should realize it. In other words, those functions of the service layer are provided by the storage engine. Different storage engines may provide different things. How do you compare these storage engines? We listed some indicators to compare:

Functional MylSAMMEMORYInnoDBArchive storage restrictions 256TBRAM64TBNone support transactions NoNoYesNo support full-text indexing YesNoNoNo support tree index YesYesYesNo support hash indexing NoYesNoNo support data cache NoN/AYesNo support foreign key NoNoYesNo

For mysql databases, the default is InnoDB, which does not support hash indexes and full-text indexes. So if one day you don't like it, you can change it. The sentence is SET default_storage_engine=.

.

Thank you for your reading, the above is the content of "the logical architecture principle of mysql". After the study of this article, I believe you have a deeper understanding of the logical architecture principle of mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.