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

This article mainly explains "the basic architecture of MYSQL database". 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 basic architecture of MYSQL database".

MYSQL can be divided into two parts: Server layer and storage layer.

The server layer roughly consists of the following sections:

Connector

Query cache

Analysis layer

Optimization layer

Executive layer

First of all, let's introduce the connector.

You need to log in before entering mysql. In fact, you are dealing with the connector. You will check the login information to it. It provides the permissions you have in the database operation. The login information we need to provide is as follows:

Mysql-h host ip-u username-p password

There are two kinds of connections: long connection and short connection.

Persistent connection means that after a successful connection, if the client keeps requesting (executing sql statements), the same connection will be used all the time.

A short connection means that the connection is disconnected after a few queries are executed, and the next query needs to re-establish a new connection.

They also have their own advantages and disadvantages:

Running efficiency takes up memory space long connection is high when the accumulated data is large, the memory occupied is very large, short connection is low and small.

When the data accumulated by a persistent connection is too large to fit in memory, the database is automatically disconnected, which can also be called an abnormal restart, but there is also a way to solve this problem:

Disconnect periodically

Execute the command mysql_reset_connection (reinitialize connection resources)

Because it is tiring and inefficient to re-establish a connection every time, a long connection is usually used.

Sometimes if you want to check the connection status of the database, you can execute this command:

Show processlist

There are two states: idle (sleep) and connection (query)

If the database has not been operated for a certain period of time, it will automatically disconnect, and if the operation continues, a new connection will have to be re-established.

Next, let's talk about query caching.

Caching means that the information of previous operations is temporarily stored in one place, and query is to find the information that has been operated before.

In the database, the query cache is the sql statement executed before the query, which will be very efficient, but there is also a disadvantage that the table has been updated and it is invalidated. So usually not, unless the watch is static, the kind that won't be updated for a long time.

Why is query caching efficient? Because it takes a lot of process to execute a new sql statement, of course, it doesn't show it when you use it. Now let's introduce these processes one by one.

When the user establishes a connection to the database, the next step is the analysis layer.

The analysis layer is divided into two parts: lexical analysis and semantic analysis.

Lexical analysis is to analyze the meaning of each word in the input mysql sentence, such as analyzing the sentence show databases;. [show] means [view], and [databases] refers to all databases, so this statement is used to view all databases. After analyzing the meaning of each word, the next step is to judge whether it conforms to the grammar of mysql, which is the same as the English we learned. When there is no problem with the syntax, proceed to the optimization layer.

The optimization layer is also divided into two parts: determining which index to use and determining the join order of each table.

If you have many fields in your data table that can be used as index conditions, the optimization layer will automatically determine which index is most appropriate to use. If there are multiple tables associated, it determines the join order of each table. Generally speaking, it is quite convenient and intelligent.

Everything is ready, only the implementation, after the end of the optimization layer came to the implementation layer.

At the beginning, we said that the database is used to store data, there must be a storage engine, the storage engine belongs to the storage engine layer, not the sercver layer.

There are roughly three storage engines for MYSQL: innoDB (default), Memory, and MylSAM.

So where to choose so many storage engines? The answer is the execution layer phase, the role of the execution layer is to select the storage engine, the default is innoDB, you can also choose other, by executing the "engine= storage engine name" to switch. In addition, the execution layer also needs to determine whether the user has permission to execute the statement, and if not, an error will be reported.

Finally, a series of operations are performed on the data in the storage engine, such as querying, inserting, deleting and modifying, etc.

Thank you for your reading, the above is the content of "the basic structure of MYSQL database", after the study of this article, I believe you have a deeper understanding of the basic structure of MYSQL database, 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.