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

This article introduces the knowledge of "how to understand the differences in the implementation plan caused by MySQL limit". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Today, I received an alarm from a business, indicating that the slow log is relatively frequent. Log in to the environment and find that SQL is a very simple statement. Under MySQL version 5.7.16, the execution time in the slow log is nearly 1 minute. I executed it from the database and found that there is a lot of room for optimization:

Select OrgId from `testcomm`.apply _ join_org where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 1; Empty set (48.71 sec)

The implementation plan is as follows:

Explain select OrgId-> from `testcomm`.apply _ join_org-> where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 1\ G * * 1. Row * * id: 1 select_type: SIMPLE table: apply_join_org partitions : NULL type: index possible_keys: IndexRTUser key: IndexCreateTime key_len: 5 ref: NULL rows: 4332 filtered: 0.00 Extra: Using where 1 row in set 1 warning (0.00 sec)

At this time, the structure of the table is somewhat hasty, and the structure has been deleted.

CREATE TABLE `apply_join_ org` (`ApplyJoinId` int (11) NOT NULL AUTO_INCREMENT, `RTId` int (11) DEFAULT NULL, `UserId` int (11) NOT NULL, `OrgId` int (11) NOT NULL, `ApplyMsg` varchar (100) DEFAULT NULL, `CreateTime` datetime NOT NULL, `ReplyMemId` int (11) DEFAULT'0, `ReplyTime` datetime NOT NULL, `ApplyStatus` tinyint (4) DEFAULT'1' COMMENT'0 reject 1 application 2 consent', `IfDel` tinyint (4) DEFAULT'1Q, `UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP `RP` int (11) DEFAULT'0' COMMENT'RP', `sex` tinyint (1) DEFAULT NULL, `IfLeaguer` tinyint (1) NOT NULL DEFAULT '0values, PRIMARY KEY (`ApplyJoinId`), KEY `IndexOrgIdStatus` (`OrgId`, `ApplyStatus`, `IfDel`), KEY `IndexRTUser` (`UserId`), KEY `IndexCreateTime` (`CreateTime`) ENGINE=InnoDB AUTO_INCREMENT=22495957 DEFAULT CHARSET=utf8 1 row in set

In addition, the amount of data involved in this table is about 20 million. From the current implementation efficiency, there is no doubt that it is a full table scan.

In fact, when it comes to this problem, it is relatively easy to understand. From the performance of the statement, combined with the table structure, we can feel that the whole execution of SQL is originally based on the field UserId, but it is unexpected that the index selection error is caused by the CreateTime in order by, and the execution cost varies greatly.

So here, how do we characterize this question:

1) is it caused by order by?

2) is it caused by the sorting of the time field?

3) is it caused by limit operation?

4) is it due to the poor data filtering effect of userid itself?

For these questions, we can quickly verify them through a few comparative SQL.

From the following SQL, you can see that order by is not the main reason

Select OrgId-> from `testcomm`.apply _ join_org-> where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime; Empty set (0.01 sec

Order by sorting is not the main reason.

Select OrgId-> from `testcomm`.apply _ join_org-> where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc; Empty set (0.01 sec)

Order by sorting + limit 10 is not the main reason.

Select OrgId from `testcomm`.apply _ join_org where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 10; Empty set (0.01 sec)

Order by sorting + limit 2 is not the main reason.

Select OrgId-> from `testcomm`.apply _ join_org-> where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 2; Empty set (0.01 sec)

After these comparisons, limit 1 is mainly added, and the index selection will change. Let's grab an execution plan for limit 2 and take a look. You can clearly see that type is ref, and there is a great difference in ref (const).

> explain select OrgId from `testcomm`.apply _ join_org where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 2\ G * * 1. Row * * id: 1 select_type: SIMPLE table: apply_join_org partitions: NULL type : ref possible_keys: IndexRTUser key: IndexRTUser key_len: 4 ref: const rows: 4854 filtered: 1.00 Extra: Using index condition Using where; Using filesort 1 row in set, 1 warning (0.00 sec)

If you want further information, you can use the following ways:

SET optimizer_trace= "enabled=on" SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\ G

View

The information in the reconsidering_access_paths_for_index_ordering section will be the key.

"index_provides_order": true

"order_direction": "desc"

The analysis of this problem mainly lies in the evaluation of cost. Obviously, in the current test, additional order by sorting operations are added, resulting in a slightly higher cost, while in the evaluation of the optimizer, it is obvious that some information is missing which leads to misjudgment.

There are several ways to fix it:

1) complement the complete composite index, userid and CreateTime can complement each other. The scheme has been fully simulated and tested in a homogeneous environment, and can achieve the expected results.

Alter table `testcomm`.apply _ join_org drop key IndexRTUser; alter table `testcomm`.apply _ join_org add key `IndexRTUser2` (UserId,CreateTime)

2) use force index's hint method to force indexing, which is of course intrusive to the business.

3) adjust the SQL logical mode to see if it is possible to use other ways to replace this limit 1 usage pattern.

In the long run, in fact, the optimizer in the whole evaluation is still relatively weak. As for the judgment basis in index selection, if we have auxiliary information such as histograms, the whole process will be even more powerful. Some simulation tests will be carried out in 8.0, and the test results will be presented later.

This is the end of the content of "how to understand the differences in the implementation Plan caused by MySQL limit". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.