Mysql, as a relational database, should be the most widely used in China. Maybe you use Oracle, Pg, etc., but most Internet companies, such as our company, use Mysql the most, which is self-evident.
Select * from table (select * from table); So we get the data? Here’s what got me, and I’ve been reading a lot of books and blogs. Hence the article.
Suppose I now have a user table with only two columns, one with an incremented ID and one with a name of type VARCHAR. The construction sentence goes like this:
CREATE TABLE IF NOT EXISTS `user`(
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL.PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the codeThe problem with chubby is the execution of the following statement.
select * from user where id = 1;
Copy the code01 mysql Architecture Overview
To understand this, you need to know the internal architecture of mysql. To do this, I drew a diagram of the mysql architecture (which you can also think of as the execution of SQL queries) as follows:
First, MSQL is divided into two parts: server layer and storage engine layer. The Server layer consists of four functional modules, which are 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 storage engines are optional in mysql. Common ones include InnoDB, MyISAM, Memory, etc. The most common one is InnoDB. It is now the default storage engine (starting with mysql 5.5.5), and you can see that I specified the InnoDB engine in the above statement. Of course, it’s the default if you don’t specify it.
Since storage engines are optional, all storage engines in mysql actually share a server layer. Back to the subject, let’s use this diagram to solve the problem of little fat.
1.1 the connector
First, the database to execute SQL, must be connected to the database. This part of the job 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. Mysql > select * from ‘mysql’;
mysql -h 127.0. 01. -P 3306 -u root -p
# 127.0. 01. : ip 3306Root: indicates the user nameCopy the codeAfter running the command, you need to enter the password, which can also be followed by -p. However, this is not recommended, there is a risk of password leakage.
After entering the command, the connector authenticates your identity against your account name and password. There are two things that can happen:
- The server will return “ERROR 1045 (28000): Access denied for user ‘root’@’127.0.0.1’ (using password: YES)” to log out.
- Verify that, and the connector will go to the permission table to find your permission. Whatever permissions you have after that will be judged by the permissions you read at this point.
Notice, I’m talking about the permissions found at this point. Even if you change the permission of the current user with the administrator account, the current user connected to the mysql server will not be affected. You must restart the mysql server for the new permission to take effect.
1.1.1 Checking the Connection Status
The connection is complete, and if nothing is done afterwards, the connection is idle. You can use show processList; All my database connections are in Sleep state, except for the connection where THE show processList operation is performed.
1.1.2 Control connection
If the client does not operate for too long, this connection will be automatically disconnected. This time is 8 hours by default and is controlled by the wait_timeout parameter. Lost Connection to MySQL Server during Query: Lost connection to MySQL Server during Query You must then reconnect to execute the request.
There are long and short connections in the database. Long connections: after a successful connection, the same connection will always be used. Short connection: Disconnects after several requests are executed and needs to be re-established next time.
Setting up a connection takes time. Therefore, you are advised to use a long connection. However, there is a problem with the long connection which can cause too much memory usage and be forced by the system. The MySQL restarts unexpectedly. How to solve it? Two methods:
- Disconnect long connections periodically. Disconnect after a certain amount of time, or when the program determines that an operation is taking up too much memory. Then reconnect if needed.
- For mySQL 5.7 or later, you can run mysql_reset_connection to reconnect resources after each memory-consuming operation. In this case, no reconnection or permission authentication is required, but the connection state is restored to the original state.
1.2 Querying Cache
After the connection is established, you can execute the SELECT statement. This brings us to the second step: query cache.
The data stored in the query cache is in the form of key-value. Key is the query statement, and value is the query result. Does the query cache have a value for this statement? If yes, go back to the client directly, if no, continue to the database to execute the statement. After finding the result, a copy is put in the cache and returned to the client.
You may find caching really sweet, but it’s not recommended to use query caching because of its drawbacks. The query cache invalidates very frequently and only one table is updated. It is immediately invalidated, and the probability of hitting the cache is extremely low for frequently updated tables. It only applies to tables that are not frequently updated.
And MySQL seems to have taken this into account. The query_cache_type parameter is provided, and setting it to DEMAND will no longer apply to Hancun. For statements that use caching, SQL_CACHE can be used to display the specified value, like this:
select SQL_CACHE * from user where id = 1;
Copy the codeMySQL 8.0 and above removed the query cache.
1.3 analyzer
If the cache is not hit, it is entered into the analyzer, which is analyzing the SQL. The parser does lexical analysis. MySQL needs to know what the SQL you’re typing is and what it consists of.
First, identify this as a query statement by “SELECT”. The string “user” is identified as “table name user” and the string “id” is identified as” column name ID “.
After that, it analyzes whether the input statement is consistent with MySQL syntax. MySQL > select, WHERE, FROM, etc. (select, WHERE, from, etc.);
1.4 the optimizer
After the analyzer, we come to the optimizer. MySQL is a smart cookie that optimizes the statements passed by the client before executing them to see if they take up less memory and run 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 elder brother"and r.id = Awesome!
Copy the codeMysql > select * from user where ID = 666; mysql > select * from user where ID = 666
Select * from user where id = 666; select * from user where id = 666; select * from user where id = 666;
The execution result of the two schemes is the same, but the efficiency is different and the resources occupied are different. The optimizer is choosing which scenario 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 actuators
The profiler knows what to do, and the optimizer knows what to do. Then it’s up to the actuators to execute.
Start to check whether you have the corresponding permission. For example, if the user account has no permission on the user table, return no permission error, as shown in the following:
select * from user where id = 1;
ERROR 1142 (42000) :SELECT command denied to user 'nasus'@'localhost' for table 'user'
Copy the codePS: If the hit cache does not go to the executor, then the permission is verified when the query result is returned.
Back to business, continue to open table execution if you have permission. The executor uses the corresponding interface according to the engine defined by the table. For example, our SQL statement execution flow looks like this:
-
Go to the ID index, call the InnoDB engine to fetch the “first row that meets the criteria” interface, and loop through the “next row that meets the criteria” interface (which is defined by the storage engine) until there are no more rows in the table that meet the criteria. The executor returns the result set of the rows traversed above to the client.
-
For a table whose ID is not an index, the executor can only call the “fetch the first row of the table record” interface and determine whether ID = 1. If not, skip; if yes, the result set exists; Call the storage engine interface to “next row” and repeat the logic until the last row of the table.
So far, the execution of the whole SQL process is finished, small fat understand?
Shoulders of giants
- Time.geekbang.org/column/arti…
conclusion
This paper introduces the structure of MySQL and the execution process of this SQL query statement through a simple SQL query statement. I believe you will have a deeper understanding of SQL.
Give some books
For more exciting articles and resources, please follow our wechat official account: JavaFish
