Tips for optimizing subqueries in MySQL _ MySQL

This article mainly introduces the sub-query optimization techniques in MySQL. the optimization of sub-queries is the basic of many optimization methods in MySQL, if you need it, you can refer to the optimization of mysql subqueries, which has not been very friendly and has always been criticized by the industry. it is also one of the most frequently encountered problems in SQL optimization, you can click here to obtain some information. mysql will rewrite the subquery when processing the subquery. In general, we want, that is, the subquery results are completed first, and then the external query tables are driven by the subquery to complete the query. However, on the contrary, the subquery is not executed first; today, we hope to introduce some practical cases to deepen our understanding of mysql subqueries:

Case: The user reports that the database response is slow, and many service updates are stuck. log on to the database and observe the SQL statements that have been executed for a long time;

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

SQL:

select tradedto0_.* from a1 tradedto0_ where tradedto0_.tradestatus='1'and (tradedto0_.tradeoid in (select orderdto1_.tradeoid from a2 orderdto1_ whereorderdto1_.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. update of other tables is 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, after the SQL statements executed for a long time are killed, the application returns to normal;
3. analyze the execution plan:

db@3306 ：explain select tradedto0_.* from a1 tradedto0_ where tradedto0_.tradestatus='1' and (tradedto0_.tradeoid in (select orderdto1_.tradeoidfrom a2 orderdto1_ where 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 | Using where; Using filesort || 2 | DEPENDENT SUBQUERY | orderdto1_ | ALL | NULL | NULL | NULL | NULL | 40998 | Using where |+----+--------------------+------------+------+---------------+------+---------+------+-------+-----

From the execution plan, we start to optimize it step by step:
First, let's take a look at the second row of the execution plan, that is, the part of the subquery. orderdto1 _ scans the entire table. let's see if we can add an appropriate index:
A. overwrite indexes:

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

The maximum key length limit for adding a composite index is exceeded:
B. view the field definitions of the table:

 db@3306 ：DESC a2 ;+---------------------+---------------+------+-----+---------+-------+| FIELD        | TYPE     | NULL | KEY | DEFAULT | Extra |+---------------------+---------------+------+-----+---------+-------+| OID         | VARCHAR(50)  | NO  | PRI | NULL  |    || TRADEOID      | VARCHAR(50)  | YES |   | NULL  |    || PROCODE       | VARCHAR(50)  | YES |   | NULL  |    || PRONAME       | VARCHAR(1000) | YES |   | NULL  |    || SPCTNCODE      | VARCHAR(200) | YES |   | NULL  |    |

C. view the average length of table fields:

db@3306 ：SELECT MAX(LENGTH(PRONAME)),avg(LENGTH(PRONAME)) FROM a2;+----------------------+----------------------+| MAX(LENGTH(PRONAME)) | avg(LENGTH(PRONAME)) |+----------------------+----------------------+|  95       |    24.5588 |

D. narrow down the field length

ALTER TABLE MODIFY COLUMN PRONAME VARCHAR(156);

Then, analyze the execution plan:

db@3306 ：explain select tradedto0_.* from a1 tradedto0_ where tradedto0_.tradestatus='1' and (tradedto0_.tradeoid in (select orderdto1_.tradeoid from a2 orderdto1_ where 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 | Using where; Using filesort || 2 | DEPENDENT SUBQUERY | orderdto1_ | index | NULL | ind_a2 | 777 | NULL | 41005 | Using where; Using index |+----+--------------------+------------+-------+-----------------+----------------------+---------+

The performance is still not good. The key is that the number of rows scanned in the two tables is not reduced (8962*41005). The index added above does not have much effect. now, view the execution result of the t table:

db@3306 ：select orderdto1_.tradeoid from t orderdto1_ where orderdto1_.proname like '%??%' or orderdto1_.procode like '%??%';Empty set (0.05 sec)

The result set is empty. Therefore, the result set of table t must be used as the driving table;
4. through the test and verification above, the performance of common mysql subqueries is poor, which is a natural weakness of mysql subqueries. you need to rewrite the SQL statement into the associated statement:

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;

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 | Using where; Using index |+----+-------------+------------+-------+---------------+----------------------+---------+------+

6. Execution time:

db@3306 ：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;Empty set (0.03 sec)

Reduced to milliseconds;