1. The sequence

This is the bottom of the database, so interviewers usually like to ask, I remember byte, Ali and other big factory interviewers will ask this, so today we are going to learn it thoroughly.

2. Abbreviated version

I remember being asked about databases: “Have you used databases? Oh, yes. Have you used select statements? Select * from student where studentId = 1; How does this statement execute at the bottom of the database?”

This time don’t lie, it’s time for our show. As shown in the picture above, I recommend that you all have a picture in your head. When we have a picture like this, we can say the answer easily and directly.

• 1. Connector: Use the connector to check whether the user name and password are correct. Otherwise, obtain user permission information.
• 2. Query cache: If the query cache is enabled, query whether there is a KEY in the CACHE for the CURRENT QUERY THROUGH the SQL statement. If there is a key, directly return value to end the query.
• 3. Analyzer: If no result is returned from the cache, the ANALYZER parses the SQL statement. If the SQL statement is incorrect, the syntax error message is returned and the query ends.
• 4. Optimizer: after the SQL statement passes the analyzer normally, the optimizer confirms the final execution plan, and determines the index in the table and the join order of the table;
• 5. Executor: When it reaches the executor, it checks whether the user has the permission to operate the SQL table. If no error message is returned indicating insufficient permission, otherwise, it calls the data query interface provided by the storage engine to query data.

Perfect answer.

3. Detailed version

Of course, we can not simply for the interview and so learn, we must learn it systematically, so draw lessons from god’s database to explain us to learn.

Above is a database composed of a whole, so let’s look at each piece of it in detail.

1. A

As shown in the figure above, MySQL can be divided into Server layer and storage engine layer.

Server layer

The Server layer includes connectors, query caches, analyzers, optimizers, actuators, etc. It covers most of the core service functions of MySQL, as well as all the built-in functions (such as date, time, math and encryption functions, etc.). All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, etc.

Storage engine layer

The storage engine layer is responsible for data storage and extraction. Its architecture mode is plug-in, supporting InnoDB, MyISAM, Memory and other storage engines. The most commonly used storage engine is InnoDB, which has been the default storage engine since MySQL 5.5.5.

This means that when creating tables in the database, InnoDB is used by default if the engine type is not specified. We can specify the type as InnoDB engine:

CREATE TABLE 't_project' (' id 'bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT' increment ', 'project_name' varchar(64) NOT NULL DEFAULT 'COMMENT' App-java ', 'project_name_cn' varchar(50) NOT NULL DEFAULT 'COMMENT' app-java', 'project_name_cn' varchar(50) NOT NULL DEFAULT 'COMMENT' ', PRIMARY KEY (' id '), KEY 'idx_project_name' (' project_name ') ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT=' table '; 12345678910Copy the code

2. The connector

Ok, now let’s look at the connector. First we need to connect to the database. Connectors establish connections to clients, obtain permissions, and maintain and manage connections. The join command is written like this:

mysql -h$ip -P$port -u$user -p
1
Copy the code

Mysql is the client tool used to establish a connection with the server. After the classic TCP handshake, the connector authenticates your identity with the user name and password you enter, followed by commands on the server.

If we’re in a project and we’re already writing to our config file, we’re going to read from your config file.

If we are an individual testing on a native machine (such as the Navicat tool) then this step is already done when we enter the connection.

The results of

1. If the user name or password is denied, you will receive an error saying “Access denied for user” and the client will stop executing.

2. If the user name and password are authenticated, the connector will check the permission table for you. After that, the permission determination logic in the connection will depend on the permissions read at that time.

This means that once a user successfully establishes a connection, even if you change the user’s permissions using the administrator account, the existing connection permissions will not be affected. After the modification is complete, only newly created connections will use the new permission Settings.

Once the connection is complete, if you have no subsequent action, the connection is idle, as you can see in the show ProcessList command. The graph in the text is the result of show ProcessList, where the Command column is displayed as “Sleep”, indicating that there is now a free connection in the system.

If the client is inactive for too long, the connector automatically disconnects it. This time is controlled by the wait_timeout parameter, and the default is 8 hours.

If the client sends a request again after the connection has been disconnected, it will receive an error message: Lost Connection to MySQL Server during Query.

Long connection and short connection

We talked about long connections and short connections, HTTP, and sockets in the previous network, remember?

Mp.weixin.qq.com/s/_gomh2t6E…

In the database, a persistent connection means that the same connection is used all the time if the client continues to receive requests after a successful connection. A short connection is one that is disconnected after a few queries are executed and then re-established the next time.

The process of establishing a connection is usually quite complicated, so I suggest that you minimize the action of establishing a connection in use, that is, try to use a long connection, but establishing a long connection will have the following problems.

The problem

There are times when MySQL memory usage increases very quickly with long connections, because the memory temporarily used by MySQL during execution is managed in connection objects. These resources are released when the connection is disconnected. If long connections are accumulated, the system may use too much memory and kill it forcibly (OOM). In this case, MySQL restarts abnormally.

Solution 1

Disconnect long connections periodically. Use for a period of time, or in the program to determine the execution of a large memory – consuming query, disconnect, then query and reconnect.

If you are using MySQL 5.7 or later, you can re-initialize the connection resource by executing mysql_reset_connection after each large operation. This process does not require reconnection and re-authentication of permissions, but restores the connection to the state it was in when it was created.

Have an impact

• All active transactions are rolled back and automatic commit mode is reset.
• All lock tables will be released.
• All temporary tables will be closed and cleared.
• Session system variables are reinitialized to the value of the corresponding global system variable.
• 5. User-defined variables will be lost;
• 6, will release Prepared statements;
• HANDLER variables are closed;
• LAST_INSERT_ID() is reset to 0.
• 9. The lock obtained by GET_LOCK() is released.

Solution 2 Connection pool

Most use connection pooling to maintain connection counts.

Database connection pool, is the server application to establish a number of connections to the database, has not used the connection pool on the connection pool, when the connection pool, this is faster than no connection to establish a new connection (TCP connection is needed time), so as to improve the transmission efficiency.

In the Spring framework, it implements a persistent connection pool, which allows other programs and clients to connect. This connection pool will be shared by all connected clients. Connection pool can accelerate connection, reduce database connection and reduce the load of database server.

3. Query the cache

After the connection is established, you can execute the SELECT statement. The execution logic leads to the second step: query cache.

When MySQL gets a query request, it goes to the query cache to see if the query has been executed before. Previously executed statements and their results may be cached directly in memory as key-value pairs. Key is the query statement and value is the query result. If your query can find the key directly in the cache, the value will be returned directly to the client.

If the statement is not in the query cache, subsequent execution phases continue. After the execution is complete, the results are stored in the query cache. As you can see, if the query hits the cache, MySQL can return the result directly without having to perform further complicated operations, which can be very efficient. However, problems can arise.

Because query caching often does more harm than good.

The query cache invalidates so frequently that whenever a table is updated, all the query cache on that table is cleared. So it’s very likely that you struggled to save the results, and an update wiped them out before you even used them. For databases under pressure to update, the hit ratio of the query cache can be very low. Unless your business has a static table, it only updates once in a while. For example, a query from a system configuration table is suitable for query caching.

The solution

Fortunately, MySQL also provides this “on demand” approach. You can set the query_cache_type parameter to DEMAND so that query caching is not used for default SQL statements. For statements where you are sure to use the query cache, you can specify it explicitly with SQL_CACHE, as in the following statement:

Select SQL_CACHE * from T where ID=10; 12Copy the code

Note that MySQL 8.0 directly removed the entire query cache function, meaning that this function has been completely removed from MySQL 8.0.

4. The analyzer

If the query cache is not hit, it is time to actually execute the statement. First, MySQL needs to know what you are going to do, so it needs to parse SQL statements.

“Lexical analysis”

The parser first does a “lexical analysis”. You are entering an SQL statement consisting of multiple strings and Spaces. MySQL needs to figure out what the strings are and what they represent.

MySQL knows from the keyword “select” that you typed in. This is a query. It also recognizes the string “T” as “table name T” and the string “ID” as “column ID.”

“Grammatical analysis”

Based on the results of the lexical analysis, the parser will determine whether the SQL statement you entered meets the MySQL syntax based on the syntax rules.

If your statement is not correct, You will receive an error warning “You have an error in your SQL syntax”, such as the statement select missing the beginning of the letter “s”.

A common grammar error will indicate where the first error occurred, so focus on the words immediately following “use near”.

5. The optimizer

After a profiler, MySQL knows what you need to do. Before execution can begin, it needs to be processed by the optimizer.

The optimizer determines which index to use when there are multiple indexes in the table. Or determine the order in which tables are joined when a statement has multiple joins. For example, if you execute a statement like the following, this statement performs a join on two tables:

 select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
1
Copy the code

1. First fetch the ID value of the record c=10 from table T1, then associate it with table T2 according to the ID value, and then judge whether the value of D in t2 is equal to 20.

2. You can also fetch the ID value of the record d=20 from table T2, and then associate it with T1 according to the ID value, and then judge whether the value of C in T1 is equal to 10.

The logical result of the two execution methods is the same, but the efficiency of the execution will be different, and the role of the optimizer is to decide which one to use.

Let’s take a closer look at the steps the optimizer takes to perform its main task

• 1. Select the most appropriate index;
• 2, select table sweep or walk index;
• 3. Select table association order;
• 4. Optimize where clause;
• 5. Eliminate useless tables in management;
• Order by and group by;
• Select outer JOIN from inner join;
• 8, simplify the sub-query, determine the result cache;
• 9. Merger attempt;

6. Actuators

MySQL knows what you want to do from the parser, it knows how to do it from the optimizer, so it goes to the executor phase and starts executing statements.

Have no legal power

If you do not have permission to perform a query on T, an error will be returned, as shown below. (If the cache is open and if the query cache is hit, permissions are verified when the query is cached back. The query also calls Precheck to verify permissions before the optimizer)

select * from T where ID=10;


ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T';
1234
Copy the code

Have permission

If you have permission, open the table and continue. When a table is opened, the executor uses the interface provided by the table engine according to its definition.

For example, in table T of our example, the ID field has no index, then the execution flow of the executor is like this:

1. Call InnoDB engine interface to get the first row of this table, check whether the ID value is 10, if not, skip, if yes, save this row in the result set;

2. Invoke the engine interface to fetch “Next row” and repeat the same logic until the last row of the table is fetched.

3. The executor returns the recordset consisting of all rows that meet the preceding conditions as a result set to the client.

At this point, the statement is complete.

The same logic is performed for indexed tables. The first call is the “fetch the first row that meets the condition” interface, and then the “next row that meets the condition” interface is iterated, all of which are already defined in the engine.

You’ll see a ROWS_EXAMINED field in the database’s slow query log that shows how many rows were scanned during this statement execution. This value is accumulated each time the executor calls the engine to retrieve the row.

All right. Let’s recall the content of the above picture again, is not more clear ah. At this point, you can confidently reply, “Hello interviewer, omit 500 words here.”

4. References

The MySQL combat 45 speak time.geekbang.org/column/arti…

InnoDB Storage Engine Edition 2

High Performance MySQL Edition 3

Please pay attention to the public number “programmer interview way” reply to “interview” to get a complete set of interview package!!

Recommendation of high quality articles

1. Computer network —- Three times handshake four times wave 2. An article that gives you a thorough understanding of the structure of HTTP request and response packets 3. A dream come true —– project self-introduction 4. An article that lets you thoroughly understand the past life of HTTP 5. An article that will get you through HTTP methods and status codes 6. Here’s your design pattern 7. Shock!!! Check out this programmer interview manual!! 9. Nearly 30 interviews shared