A complex subquery SQL optimization

SELECT * from Test.vmark VK where ID in (Select V.id from Usr_center.vmark_degree_update_log V,(Select min (id) ID from usr_center.vmark_degree_update_log where degree_up Date_cause = 0 and Degree_update_type = 0 GROUP by user_id) Logwhere v.id = log.id and V.degree_update_type = 0 and V.degree_update_be Fore between ' 2015-01-01 00:00:00 ' and
' 2015-01-10 00:00:00 ');
+----+--------------------+-------------------------+--------+----------------+---------+---------+------+- ---------+----------------------------------------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+--------------------+-------------------------+--------+-------------- --+---------+---------+------+----------+----------------------------------------------+| 1 | PRIMARY | VK | All | NULL | NULL | NULL | NULL | 66051213 | Using where || 2 | DEPENDENT subquery | <derived3> | All | NULL | NULL | NULL |    NULL | 82947 |  Using where | | 2 | DEPENDENT subquery | V | Eq_ref | Primary,idx_dd | PRIMARY | 4 |        Func | 1 | Using where || 3 | DERIVED | Vmar_degree_update_log | Ref | Idx_dd | Idx_dd |      1 | | 12508106 | Using where; Using temporary; Using Filesort |+----+--------------------+-------------------------+--------+----------------+---------+---------+------+- ---------+----------------------------------------------+4 rows in Set (8.10 sec)

How to interpret the implementation plan?1. First execute the id=3 identification as the derived step, this keyword is derived, the subquery that appears after the from will have this identity. index IDX_DD (degree_update_type,degree_update_after) will be used for this step, ref table name This is a non-unique index scan that is expected to scan 12287942 rows. 2. The first plan for the implementation of id=2 is tomake a full-table scan of the temporary table by id=4 The result of the group by 82946 lines. Here dependent subquery means that the number of rows or executions to be scanned here depends on the other step, which depends on the 4th step. Scan here for 82946 lines. 3. After obtaining log.id information, according to V.id=log.id, to drive V-table, Go is primary,eq_ref also reflect this is a unique index scan, from the rows can be seen, each execution once returned 1 rows, executed how many times, executed 82,946 times, because is " DEPENDENT subquery ", so the number of executions or scan lines depends on the 2nd step, that is, the 2nd step each sweep out one, 3rd step to go once the index. Here we also see that the MySQL execution plan is not a reflection of the final return of a few lines, not to reflect the total number of steps to scan a few lines, do not tell you how many times, but just return the execution once return how many rows. Scan here for 82946 lines. 4. The full table scans the Test.vmar table, 66050840 rows, and then nest the result set returned by the loop join before the step (there are actually more than 80,000 rows). This step is the most time-consuming, read 66050840*80000 line. The cost of scanning the lines here is: 12287942+82946*2+66050840*80000=7263409280000,7 Hundreds of lines, I suppose 100 million lines to read 20 minutes, also need more than 2,300 hours, The execution plan is horrible. Conclusion: A subquery placed in the from is a non-associative subquery, which is OK. But in the place, it matters. 
The problem is in the 4th step, the Test.vmark table actually has the primary key index on the ID, if we can use the result set of the previous three steps to obtain the ID value, and then to drive the ID value of Test.vmark, then it is easy to get the desired result.
Change the SQL as follows to convert in into join.
SELECT * FROM  Test.vmar VK join (select v.id from                Usr_center.vmar_degree_update_log V,                     (select min (id) id
    from Usr_center.vmar_degree_update_log                       where degree_update_cause = 0 and                         degree_update_type = 0                       GROUP BY USER_ID) Log               where v.id = log.id and                 v.degree_update_type = 0 and                 v.degree_update_before between ' 2015-01-01 00:00:00 ' and                     ' 2015-01-10 00:00:00 ') child                  where vk.id = child.id;
+----+-------------+-------------------------+--------+----------------+---------+---------+----------+-------- --+----------------------------------------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-------------------------+--------+----------------+----  -----+---------+----------+----------+----------------------------------------------+| 1 | PRIMARY | <derived2> | All | NULL | NULL | NULL |    NULL |                                              82371 |  || 1 | PRIMARY | VK | Eq_ref | PRIMARY | PRIMARY | 4 |        Child.id |                                              1 |  || 2 | DERIVED | <derived3> | All | NULL | NULL | NULL |    NULL |                                              82948 |  || 2 | DERIVED | V                       | Eq_ref | Primary,idx_dd | PRIMARY | 4 |        Log.id | 1 |  Using where | | 3 |  DERIVED | Vmar_degree_update_log | Ref | Idx_dd | Idx_dd |          1 | | 11901368 | Using where; Using temporary; Using filesort |+----+-------------+-------------------------+--------+----------------+---------+---------+---- ------+----------+----------------------------------------------+
The interpretation of this implementation plan, the previous steps, which corresponds to the 3,4,5 line of the execution plan, is more like, no longer a statement of exhaustion. The resulting set has an ID value, the first line of execution plan to study the result set, there are 82371 rows, each read out the ID value to drive the Test.vmark table's primary key ID. The cost of reading the number of rows here is: 11901368+82948*2+82371*2=48102006 line.

A complex subquery SQL optimization