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

In this issue, the editor will bring you about how to execute the select query sentence. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

As a relational database, mysql should be the most widely used in China. Maybe you use Oracle, Pg and so on, but most Internet companies, such as our company, use Mysql most often, and the importance is self-evident.

Execute select * from table, what happens at the bottom of the database? So we can get the data?

Suppose I now have a user table with only two columns, one id incrementing and one name of type varchar. The statement of building a table goes like this:

CREATE TABLE IF NOT EXISTS `user` (`id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

The problem is the execution of the following statement.

Select * from user where id = 1

01 mysql architecture overview

To understand this problem, it is necessary to know the internal architecture of mysql. To do this, I drew an architectural diagram of mysql (which you can also understand as the execution of sql query statements), as follows:

First of all, msql is divided into two parts: server layer and storage engine layer. The server layer consists of four functional modules: connector, query cache, optimizer and executor. This layer is responsible for all the core work of mysql, such as built-in functions, stored procedures, triggers, and views.

The storage engine layer is responsible for data access. Note that the storage engine is optional in mysql, and other common ones are InnoDB, MyISAM, Memory, and so on, and the most common one is InnoDB. Now it is also the default storage engine (starting with mysql version 5.5.5), you can see that my table statement above specifies the InnoDB engine. Of course, it is the default if you don't specify it.

Because the storage engine is optional, all storage engines in mysql actually share a single server layer. Back to the point, let's use the flow of this picture to solve Xiao Pang's problem.

1.1 Connector

First of all, if the database is to perform sql, you must first connect to the database. This part of the work is done by the connector. It is responsible for verifying account passwords, obtaining permissions, managing the number of connections, and finally establishing connections with clients. The mysql link database is written as follows:

Mysql-h 127.0.0.1-P 3306-u root-p# 127.0.0.1: ip 3306: Port root: user name

You need to enter a password after running the command, or you can follow-p. However, it is not recommended to do so, there is a risk of password disclosure.

After entering the command, the connector verifies your identity according to your account name and password. There are two things that can happen:

If the account number or password is incorrect, the server will return an error of "ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)" and exit the connection.

After the verification is passed, the connector will go to the permissions table to find out your permissions. After that, whatever permissions you have have to be judged by the permissions you read at this time.

Attention, I'm talking about the permissions found at this time. Even if you modify the permissions of the current user with the administrator account, the connected current user will not be affected. You must restart mysql before the new permissions will take effect.

1.1.1 View connection status

The connection is complete, and if nothing is done later, the connection is idle. You can use the show processlist; command to view the connection information of mysql. As shown in the figure below, my database connections are all in Sleep status, except for connections that perform show processlist operations.

1.1.2 Control connection

If the client does not operate for too long, the connection will be disconnected automatically. This time defaults to 8 hours, which is controlled by the parameter wait_timeout. If you continue to operate after disconnecting, you will receive a "Lost connection to MySQL server during query" error. At this point, you must reconnect to execute the request.

There are long and short connections in the database, long connections: if there are continuous requests after a successful connection, the same connection will always be used. Short connection: disconnect after several requests each time, and need to be established next time.

Because establishing a connection is a time-consuming operation, it is recommended to use a long connection. However, there will be a problem that long connections can lead to excessive memory consumption and sand sculpture forced by the system. This causes MySQL to restart abnormally. How to solve the problem? Two methods:

Disconnect the long connection regularly. Use a specific time, or the program determines that a memory-intensive operation is performed and then disconnects. Then reconnect if you need to operate.

In mySQL version 5.7 or above, you can perform mysql_reset_connection to reconnect resources after each memory-intensive operation. There is no need to reconnect or redo permission authentication, but the connection status will be restored to the time when the connection was created.

1.2 query cache

After the connection is established, you can execute the select statement. This brings you to the second step: query caching.

The data stored in the query cache is in the form of key-value, key is the query statement, and value is the result of the query. The logic goes like this: first, look at the query cache to see if there is a value corresponding to the statement. Some are directly fetched and returned to the client, and if not, the statement continues to be executed in the database. After finding out the result, it will put a copy in the cache and return it to the client.

You may find that caching really smells good, but query caching is not recommended because of its drawbacks. The query cache is invalidated very frequently, and only a certain table is updated. It immediately fails, and for tables that are updated frequently, the probability of hitting the cache is extremely low. It applies only to tables that are not updated frequently.

And MySQL seems to be thinking about that, too. The query_cache_type parameter is provided, and setting it to DEMAND is no longer applicable to Han Village. For statements that want to use caching, you can use SQL_CACHE to display the assignment, like this:

Select SQL_CACHE * from user where id = 1

PS:MySQL 8.0 and above deleted the query cache, and there is no such feature since.

1.3 Analyzer

If you miss the cache, you go to the parser, and here you analyze the sql. The analyzer does lexical analysis. MySQL needs to know what the sql you enter is and what it is made of.

First, it is recognized as a query statement based on "select". The string "user" is recognized as "table name user" and the string "id" is recognized as "column name id".

After that, parsing is performed, which analyzes whether it conforms to the syntax of MySQL according to the input statement. Specific performance is select, where, from and other keywords missing a letter, obviously does not conform to MySQL syntax, this time will report a syntax error exception: it will generally prompt the wrong number of lines, pay attention to "use near" after it.

1.4 Optimizer

After the analyzer, you come to the optimizer. MySQL is a smart guy who optimizes the statements sent by the client before execution to see which ones are less memory-intensive and faster. For example, the following sql statement:

Select * from user u inner join role r on u.id = r.user_id where u.name = "dog brother" and r.id = 666

It can first take out the ID value recorded by name = "Dog Brother" from the user table, and then join the query with the role table, and then determine whether the value of id in the role table is = 666.

It can also be reversed: first take out the ID value of the record id = 666from the role table, and then join the query with the user table to determine whether the name value in the user table is equal to "dog".

The implementation results of the two schemes are the same, but the efficiency is not the same, and the resources occupied are not the same. The optimizer is choosing the solution to execute. It optimizes which index should be used? Which table should be checked first for multi-table joint check? How to connect and so on.

1.5 Actuator

The analyzer knows what to do, and the optimizer knows what to do. Then it is left to the executor to execute.

Start execution to determine whether there are appropriate permissions. For example, if the account does not have permission on the user table, it returns an error of no permission, as shown below:

Select * from user where id = 1 * error 1142 (42000): SELECT command denied to user' nasus'@'localhost' for table 'user'

PS: if you hit the cache and don't get to the executor, do permission verification when the query result is returned.

Back to the point, if you have permission, continue to open the table for execution. The executor uses the corresponding interface according to the engine defined by the table. For example, the sql statement execution process above looks like this:

Take the id index, call the InnoDB engine to take the "first row that meets the condition" interface, and then call the "next row that meets the condition" interface (these interfaces are defined by the storage engine) until there are no more rows in the table that meet the condition. The executor returns the result set of the rows traversed above to the client.

For tables whose id is not an index, the executor can only call the "fetch the first row of the table record" interface, and then determine whether id = 1. If not, skip, yes, there is a result set; then call the storage engine interface to "next row" and repeat the judgment logic until the last row of the table.

At this point, the execution process of the entire SQL is complete.

This is how the select query sentence shared by the editor should be executed. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.