On the optimization techniques of sub-query in Mysql _mysql

MySQL's subquery optimization has been not very friendly, has been criticized by the industry more than I have encountered in the SQL optimization of one of the most problems, you can click here, here to get some information, MySQL processing subqueries, the subquery will be rewritten, usually, we hope that from the inside out, That is, the result of the subquery is completed, and then the query is driven by the subquery to the table to complete the inquiry, but on the contrary, the subquery will not be executed first; today I hope to deepen our understanding of MySQL subqueries by introducing some practical cases:

Case: User Feedback database response is slow, many business updates are jammed; Log in to the database to observe and discover long-running SQL;

| 10437 | usr0321t9m9 | 10.242.232.50:51201 | OMS | Execute | 1179 | Sending

SQL is:

Select tradedto0_.* from A1 tradedto0_ where tradedto0_.tradestatus= ' 1 ' and
(tradedto0_.tradeoid in (select) Orderdto1_.tradeoid from A2 orderdto1_ where is
orderdto1_.proname like '%?? % ' or orderdto1_.procode like '%?? % ')) and tradedto0_.undefine4= ' 1 ' and
tradedto0_.invoicetype= ' 1 ' and tradedto0_.tradestep= ' 0 ' and (tradedto0_. Ordercompany like ' 0002% '] order by tradedto0_.tradesign ASC, tradedto0_.makertime desc limit 15;

2. Updates to other tables are blocked:

Update A1 set tradesign= ' dab67634-795c-4eac-b4a0-78f0d531d62f ',
markcolor= ' #CD5555 ', memotime= ' 2012-09-22 ', Markperson= '?? ' where tradeoid in (' gy2012092204495100032 ');

In order to restore the application as soon as possible, the application is restored to normal after the long execution of the SQL is killed.
3. Analysis of the implementation plan:

Db@3306:explain Select tradedto0_.* from A1 tradedto0_ where tradedto0_.tradestatus= ' 1 ' and (Tradedto0_.tradeoid in (sel ECT orderdto1_.tradeoid from A2 orderdto1_ where is orderdto1_.proname like '%?? % ' or orderdto1_.procode like '%?? % ')) and tradedto0_.undefine4= ' 1 ' and tradedto0_.invoicetype= ' 1 ' and tradedto0_.tradestep= ' 0 ' and (tradedto0_.
Ordercompany like ' 0002% '] order by tradedto0_.tradesign ASC, tradedto0_.makertime desc limit 15; +----+--------------------+------------+------+---------------+------+---------+------+-------+-----
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
Extra | +----+--------------------+------------+------+---------------+------+---------+------+-------+-----
| 1 | PRIMARY | tradedto0_ | All | NULL | NULL | NULL | NULL | 27454 | The Using where; Using Filesort | | 2 | DEPENDENT subquery | orderdto1_ | All | NULL | NULL | NULL | NULL | 40998 |
Using where | +----+--------------------+------------+------+---------------+------+---------+------+-------+-----
 

From the execution plan, we begin to optimize it step by piece:
First, let's take a look at the second line of the execution plan, which is the part of the subquery, orderdto1_ the full table scan, and we'll see if we can add the appropriate index:
A. Use Overlay index:

Db@3306:alter Table A2 Add index IND_A2 (proname,procode,tradeoid);
ERROR 1071 (42000): Specified key was too long; Max key length is 1000 bytes

Adding a composite index exceeds the maximum key length limit:
B View the field definitions for this table:

 DB@3306:DESC A2;
+---------------------+---------------+------+-----+---------+-------+
| FIELD        | TYPE     | NULL | KEY | DEFAULT | Extra |
+---------------------+---------------+------+-----+---------+-------+
| OID         | VARCHAR (m)  | NO  | PRI | NULL  | | |
Tradeoid      | VARCHAR (m)  | YES |   | NULL  | | |
Procode       | VARCHAR (m)  | YES |   | NULL  | | |
Proname       | VARCHAR (1000) | YES |   | NULL  | | |
Spctncode      | VARCHAR (200) | YES |   | NULL  |    |

C To view the average length of a table field:

Db@3306:select MAX (Length (proname)), Avg (Length (proname)) from A2;
+----------------------+----------------------+
| MAX (LENGTH (proname)) | AVG (LENGTH (proname)) |
+----------------------+----------------------+
|       |    24.5588 |

D Reduce the length of a field

ALTER TABLE MODIFY COLUMN proname VARCHAR (156);

Further analysis of the execution plan:

Db@3306:explain Select tradedto0_.* from A1 tradedto0_ where tradedto0_.tradestatus= ' 1 ' and (Tradedto0_.tradeoid in (sel ECT orderdto1_.tradeoid from A2 orderdto1_ where is orderdto1_.proname like '%?? % ' or orderdto1_.procode like '%?? % ')) and tradedto0_.undefine4= ' 1 ' and tradedto0_.invoicetype= ' 1 ' and tradedto0_.tradestep= ' 0 ' and (tradedto0_.
Ordercompany like ' 0002% '] order by tradedto0_.tradesign ASC, tradedto0_.makertime desc limit 15; +----+--------------------+------------+-------+-----------------+----------------------+---------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
Extra | +----+--------------------+------------+-------+-----------------+----------------------+---------+
| 1 | PRIMARY | tradedto0_ | Ref | Ind_tradestatus | Ind_tradestatus | 345 | Const,const,const,const | 8962 | The Using where; Using Filesort | | 2 | DEPENDENT subquery | orderdto1_ | Index | NULL | IND_A2 | 777 | NULL | 41005 | The Using where;
Using Index | +----+--------------------+------------+-------+-----------------+----------------------+---------+
 

Discover performance or not, key in two tables the number of rows scanned did not decrease (8962*41005), the index added above did not have much effect, now view the results of the T table:

Db@3306:select orderdto1_.tradeoid from T orderdto1_ where is orderdto1_.proname like '%?? % ' or orderdto1_.procode like '%?? %';
Empty Set (0.05 sec)

The result set is empty, so the result set of the T table needs to be used as a driving table;
4. Through the above test verification, the common MySQL subquery writing performance is very poor, for the MySQL subquery natural weaknesses, need to rewrite the SQL to the associated wording:

Select tradedto0_.* from A1 tradedto0_ (select Orderdto1_.tradeoid from A2 where orderdto1_ like '%?? % ' or orderdto1_.procode like '%?? % ') T2 where tradedto0_.tradestatus= ' 1 ' and (tradedto0_.tradeoid=t2.tradeoid) and tradedto0_.undefine4= ' 1 ' and Tradedto0_.invoicetype= ' 1 ' and tradedto0_.tradestep= ' 0 ' and (tradedto0_.ordercompany like ' 0002% ') Order by tradedto0_. Tradesign ASC, tradedto0_.makertime desc limit 15;

5. View the execution plan:

Db@3306:explain Select tradedto0_.* from A1 tradedto0_ (select Orderdto1_.tradeoid from A2 orderdto1_ where orderdto1_. Proname like '%?? % ' or orderdto1_.procode like '%?? % ') T2 where tradedto0_.tradestatus= ' 1 ' and (tradedto0_.tradeoid=t2.tradeoid) and tradedto0_.undefine4= ' 1 ' and Tradedto0_.invoicetype= ' 1 ' and tradedto0_.tradestep= ' 0 ' and (tradedto0_.ordercompany like ' 0002% ') Order by tradedto0_.
Tradesign ASC, tradedto0_.makertime desc limit 15; +----+-------------+------------+-------+---------------+----------------------+---------+------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
Extra | +----+-------------+------------+-------+---------------+----------------------+---------+------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | | 2 | DERIVED | orderdto1_ | Index | NULL | IND_A2 | 777 | NULL | 41005 | The Using where;
Using Index | +----+-------------+------------+-------+---------------+----------------------+---------+------+
 

6. Execution Time:

Db@3306:select tradedto0_.* from A1 tradedto0_, (select orderdto1_.tradeoid from A2 orderdto1_ where orderdto1_.proname L Ike '%?? % ' or orderdto1_.procode like '%?? % ') T2 where tradedto0_.tradestatus= ' 1 ' and (tradedto0_.tradeoid=t2.tradeoid) and tradedto0_.undefine4= ' 1 ' and Tradedto0_.invoicetype= ' 1 ' and tradedto0_.tradestep= ' 0 ' and (tradedto0_.ordercompany like ' 0002% ') Order by tradedto0_. Tradesign ASC, tradedto0_.makertime desc limit;
Empty Set (0.03 sec)

shortened to milliseconds;