MySQL Statement performance analysis case

The difference in performance of two SQL is different and functionally identical.
Example One
Requirements: Take out a group (assuming ID 100) under the user number (ID), user nickname (nick_name), user gender
(sexuality), User signature (sign), and user birthday (birthday), and follow the time to join the group
(user_group.gmt_create) To reverse order, remove the first 20.
Solution One,
SELECT Id,nick_name
From User,user_group
WHERE user_group.group_id = 1
and user_group.user_id = User.ID
Limit 100, 20;
Solution II,
SELECT User.id,user.nick_name
From (
SELECT user_id
From User_group
WHERE user_group.group_id = 1
ORDER by gmt_create Desc
Limit 100,20) T,user
WHERE t.user_id = user.id;
Let's take a look at the execution plan first:
[Email protected]: example 10:32:13> explain
SELECT Id,nick_name
From User,user_group
--WHERE user_group.group_id = 1
and user_group.user_id = User.ID
ORDER by user_group.gmt_create Desc
Limit 100,20\g
1. Row ***************************
Id:1
Select_type:simple
Table:user_group
Type:ref
Possible_keys:user_group_uid_gid_ind,user_group_gid_ind
Key:user_group_gid_ind
Key_len:4
Ref:const
rows:31156
Extra:using where; Using Filesort
2. Row ***************************
Id:1
Select_type:simple
Table:user
Type:eq_ref
Possible_keys:primary
Key:primary
Key_len:4
ref:example.user_group.user_id
Rows:1
Extra:
[Email protected]: example 10:32:20> explain
SELECT User.id,user.nick_name
-From (
SELECT user_id
From User_group
--WHERE user_group.group_id = 1
ORDER by gmt_create Desc
, limit 100,20) T,user
--WHERE t.user_id = user.id\g
1. Row ***************************
Id:1
Select_type:primary
Table: <derived2>
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:20
Extra:
2. Row ***************************
Id:1
Select_type:primary
Table:user
Type:eq_ref
Possible_keys:primary
Key:primary
Key_len:4
ref:t.user_id
Rows:1
Extra:
3. Row ***************************
Id:2
Select_type:derived
Table:user_group
Type:ref
Possible_keys:user_group_gid_ind
Key:user_group_gid_ind
Key_len:4
Ref:const
rows:31156
Extra:using Filesort
Execution Plan Comparison analysis:
The execution plan in solution one shows that MySQL is using indexes on two tables participating in joins, and the User_group table leverages
User_group_gid_ind index (key:user_group_gid_ind), the user table takes advantage of the primary key index (key:
PRIMARY), before participating in the join, MySQL joins with the user table through the Where filtered result set, and finally by sorting out
The "limit 100,20" result of the Join results in a return.
Solution two SQL statements take advantage of subqueries, so the execution plan is slightly more complex, first you can see two tables and
Solution 1 uses the same index (the same index is used), and the execution plan displays the subquery to User_group as
The driver, which is the first filter by User_group and immediately the result set ordering of this theory, also obtained the SQL
The "Limit 100,20" bar results, and then joins with the user table to get the corresponding data. Someone here might suspect that in a self-query
The number of record bars obtained from the User_group table with the user table participating in join IS not 20, but all results of the entire group_id=1.
So let's see the first line in the execution plan, which fully illustrates that all 20 records in the outer-layer query are
Return.
By comparing the execution plans of two solutions, we can see the records that need to participate in the join with the user table in the first solution
Count MySQL is estimated by statistical data to be 31156, that is, all records that satisfy group_id=1 are returned through the User_group table.
Number (the actual data in the system is 20000). The second solution's execution plan, the user table participates in the join the data is only 20
, which is quite different from the initial analysis in this section, we believe that the second solution should be significantly better than the first one.
Below we verify our judgment by comparing the profile details of the two solution-aware SQL actual execution. By
The largest two-part resource consumed by SQL statement execution is IO and CPU, so to save space, only BLOCK IO and CPU are listed
Two profile information (a detailed introduction to Query Profiler will be presented separately in the following sections):
Open the profiling feature first, and then execute the SQL statements for each of the two solutions:
[Email protected]: example 10:46:43> set profiling = 1;
Query OK, 0 rows Affected (0.00 sec)
[Email protected]: example 10:46:50> SELECT id,nick_name
From User,user_group
--WHERE user_group.group_id = 1
and user_group.user_id = User.ID
ORDER by user_group.gmt_create Desc
, limit 100, 20;
+--------+-----------+
| ID | Nick_name |
+--------+-----------+
| 990101 | 990101 |
| 990102 | 990102 |
| 990103 | 990103 |
| 990104 | 990104 |
| 990105 | 990105 |
| 990106 | 990106 |
| 990107 | 990107 |
| 990108 | 990108 |
| 990109 | 990109 |
| 990110 | 990110 |
| 990111 | 990111 |
| 990112 | 990112 |
| 990113 | 990113 |
| 990114 | 990114 |
| 990115 | 990115 |
| 990116 | 990116 |
| 990117 | 990117 |
| 990118 | 990118 |
| 990119 | 990119 |
| 990120 | 990120 |
+--------+-----------+
Rows in Set (1.02 sec)
[Email protected]: example 10:46:58> SELECT user.id,user.nick_name
-From (
SELECT user_id
From User_group
--WHERE user_group.group_id = 1
ORDER by gmt_create Desc
, limit 100,20) T,user
WHERE t.user_id = user.id;
+--------+-----------+
| ID | Nick_name |
+--------+-----------+
| 990101 | 990101 |
| 990102 | 990102 |
| 990103 | 990103 |
| 990104 | 990104 |
| 990105 | 990105 |
| 990106 | 990106 |
| 990107 | 990107 |
| 990108 | 990108 |
| 990109 | 990109 |
| 990110 | 990110 |
| 990111 | 990111 |
| 990112 | 990112 |
| 990113 | 990113 |
| 990114 | 990114 |
| 990115 | 990115 |
| 990116 | 990116 |
| 990117 | 990117 |
| 990118 | 990118 |
| 990119 | 990119 |
| 990120 | 990120 |
+--------+-----------+
Rows in Set (0.96 sec)
To view the profile information in the system, the execution profile information for the two SQL statements just executed has been recorded:
[Email protected]: example 10:47:07> show Profiles\g
1. Row ***************************
Query_id:1
duration:1.02367600
Query:select Id,nick_name
From User,user_group
WHERE user_group.group_id = 1
and user_group.user_id = User.ID
ORDER by user_group.gmt_create Desc
Limit 100,20
2. Row ***************************
Query_id:2
duration:0.96327800
Query:select User.id,user.nick_name
From (
SELECT user_id
From User_group
WHERE user_group.group_id = 1
ORDER by gmt_create Desc
Limit 100,20) T,user
WHERE t.user_id = user.id
2 rows in Set (0.00 sec)
[Email protected]: example 10:47:34> SHOW profile cpu,block io io for query 1;

Rows in Set (0.00 sec)

[Email protected]: example 10:47:40> SHOW profile cpu,block io io for query 2;

Let's take a look at the IO consumption in two SQL execution, the difference is "sorting result", we return
Look at the comparison of the previous execution plan, the timing of the two solutions to filter the data is not the same, the sorting needs to take
The amount of data one is 20000, one is 20, exactly with the profile information here, the first solution
The IO value of "sorting result" is nearly 500 times times the second solution.
And then look at the CPU consumption, all consumption, the biggest consumption is also "sorting result" this item, the first
One of the reasons for consuming more than the above IO consumption difference is the same.
Conclusion:
Through the execution plan analysis of the two identical SQL statements above, and through the actual execution of the
The validation of the profile data proves that the second solution is better than the first solution.

MySQL Statement performance analysis case