I am the way to improve the architecture, click on the top of the “attention”, adhere to every day for you to share technology, private message I reply “01”, send you a programmer growth guide gift package.
Some of the problems found
Question 1
In the past half a year, the R&D team internally attempted to catch a wave of slow query SQL follow-up rates. I found that some students’ idea of slow query processing is to see whether the index is used, and if not, they try to add one. If not, they blame this situation as a historical design problem or decide that it is a small probability event triggered by the user’s special operation, and then apply for exemption.
In fact, the problem has not been fundamentally solved.
Question 2
There is a network can often see some articles like this:
“Slow SQL Performance Optimization”
“Slow SQL Performance optimization is enough”
.
In fact, the content is much the same, either suggest adding indexes, or suggest rewriting SQL….
How can I put it? Knowledge is correct, but not comprehensive, this is easy to mislead new students, ha ha ha.
In this paper, the original
In the process of business project development, we often face to deal with the problem of MySQL slow query, then how should we analyze and solve the problem?
Some students in the process of MySQL slow query main idea is to add index to solve, indeed add index is a good way to solve the problem, but not all. Since slow query as a problem, it needs to clear the cause of the problem, and solve the problem path analysis, teach people to fish than teach people to fish, let us together to unlock MySQL to deal with slow query the correct posture.
The main purpose of this article is to understand why query SQL is slow
Don’t say much nonsense, directly open to do ~
Writing in the front
In the process of business project development, we often face to deal with the problem of MySQL slow query, then how should we analyze and solve the problem?
Some students in the process of MySQL slow query main idea is to add index to solve, indeed add index is a good way to solve the problem, but not all. Since slow queries are the problem, it is necessary to identify the cause of the problem and analyze the path to solve the problem. Let’s get the correct posture of MySQL slow query.
The main contents of this paper include:
1. What happens to query SQL execution?
2. Why is query SQL slow?
1. What happens to query SQL execution?
First, you need to be clear: what happens to the execution of a query SQL?
The process of executing SQL is as follows:
-
Connect to MySQL Server (basic)
-
The client sends the query SQL to the database, and the database verifies whether it has permission to execute
-
The MySQL server first checks the query cache and returns the result stored in the cache immediately if it hits the cache. Otherwise, the flow continues.
-
MySQL server syntax parser, lexical and syntax analysis, pre-processing
-
Flows to the query optimizer to generate an execution plan
-
Based on the generated execution plan, the API exposed by the storage engine is invoked to execute the query
-
The query execution result is returned to the client
-
Close MySQL connection
The execution process may vary depending on the configuration of the MySQL server and execution scenarios.
1) If the application cache query is not enabled, the check of cache query is directly ignored;
2) During the execution, for example, the scanned rows may be locked and blocked by other SQL
2. Why is the query SQL slow?
If we think of query SQL execution as a task, it consists of a number of subtasks, each of which has a certain amount of time. In general, the most fundamental problem that leads to slow queries is that too much data needs to be accessed, resulting in queries that inevitably need to filter a large amount of data.
When faced with slow queries, we need to pay attention to the following two points:
1) Too much unnecessary data was queried
2) Additional records are scanned
2.1 Too Much Unnecessary Data is Queried
Instead of just returning the required data, MySQL actually returns the entire result set to be computed.
Especially in the case of multi-table associated query select *, do we really need all columns? If not, we just specify the corresponding field.
For example, we want to query the product information under the user’s associated order, as shown below:
SELECT * FROM users LEFT JOIN orders ON orders.user_id = users.user_id LEFT JOIN goods ON goods.good_id = orders.good_id WHERE users.name = 'zhangsan';Copy the code
This will return all data columns for the three tables, which can be adjusted to take only the desired columns:
SELECT goods.title, goods.description
FROM users
LEFT JOIN orders ON orders.user_id = users.user_id
LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';
Copy the code
Pulling out all the columns prevents the optimizer from performing optimizations such as index override scans and costs the server additional I/O, memory, and CPU.
2.2 Additional records are scanned
Most of this is due to index misapplication (including: indexes that should be built are not built, or the best indexes are not used).
The sample table structure is as follows:
CREATE TABLE `test_table` (
`name` varchar(32) DEFAULT NULL,
`desc` varchar(32) DEFAULT NULL,
`age` int(16) DEFAULT NULL,
`id` bigint(11) DEFAULT NULL,
KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Copy the code
When index ‘idx_age’ exists, the query execution plan results are as follows:
EXPLAIN SELECT * FROM test_table WHERE age = 10;
Copy the code
If ‘idx_age’ is deleted, it will become a full table scan (ALL). It is estimated that 121524 records need to be scanned to complete the query, as shown in the figure below:
conclusion
According to combing the SQL execution process in MySQL, we found that the execution of any process has its own execution environment and rules. In fact, the essence of producing slow SQL is that we do not execute SQL according to the requirements of the database.
The most fundamental problem with slow queries is that too much data needs to be accessed, leading to queries that inevitably need to filter a large amount of data.
Author: Path to Architecture Improvement, focus on software architecture research, technical learning and personal growth, follow and private message me “01”, send you a programmer development guide gift package.
Thanks for reading!