mysql 索引 子部分_关于sql中有分组子查询时外层表的索引使用问题

查询1

explain select * from t1 where mobile in (select mobile from t2 group by mobile, draw_date );

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | t2 | index | NULL | mobile | 27 | NULL | 15 | Using index; Start temporary |

| 1 | SIMPLE | t1 | ref | mobile | mobile | 63 | func | 1 | Using index condition; End temporary |

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

此时外层表t1可以用到索引

查询2

explain select * from t1 where mobile in (select mobile from t2 group by mobile, draw_date having count(*)>1);

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using where |

| 2 | DEPENDENT SUBQUERY | t2 | index | mobile | mobile | 27 | NULL | 15 | Using index |

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

子查询增加了having count(*)>1 外层表t1就用不上索引了。

查询3

explain select * from t1 where mobile in (select mobile from (select mobile from t2 group by mobile, draw_date having count(*)>1) a);

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 15 | Start temporary |

| 1 | PRIMARY | t1 | ref | mobile | mobile | 63 | func | 1 | Using index condition; End temporary |

| 3 | DERIVED | t2 | index | mobile | mobile | 27 | NULL | 15 | Using index |

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

子查询再包了一层 外层表t1又可以用索引了。

怎么外层表一会能用上索引， 一会儿又不能？ 何解？