mysql like子查询语句_浅谈mysql的子查询

再进行执行计划分析：

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 |

+----+--------------------+------------+-------+-----------------+----------------------+---------+

发现性能还是上不去，关键在两个表扫描的行数并没有减小(8962*41005)，上面添加的索引没有太大的效果，现在查看t表的执行结果：

db@3306 ：select orderdto1_.tradeoid from t orderdto1_ where orderdto1_.proname like '%??%' or orderdto1_.procode like '%??%';

Empty set (0.05 sec)

结果集为空，所以需要将t表的结果集做作为驱动表；

4．通过上面测试验证，普通的mysql子查询写法性能上是很差的，为mysql的子查询天然的弱点，需要将sql进行改写为关联的写法：

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.查看执行计划：

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.执行时间：

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)

缩短到了毫秒；