mysql in 子查询无法使用索引全表扫描 慎用in

背景

最近慢sql 性能优化 发现一个调用频率高的sql 使用 in 子查询，导致外层全表扫描的问题？
为什么会产生这样的问题？特别强调在优化的使用发现 使用in 和优化后的效果差距 300倍，可见全表扫描的效果可多厉害。 mysql 版本 5.6

参考

搜集了一下资料发现 https://www.cnblogs.com/wy123/archive/2017/06/28/7092194.html 这篇 文章特别像我们的场景，子查询中 查询一个条件，然后非常慢使用不上索引

实践

创建表

create table test_table2
(
	id int auto_increment
		primary key,
	pay_id int null,
	pay_time datetime null,
	other_col varchar(100) null
);

create index test_table2_pay_id_index
	on test_table2 (pay_id);

执行计划

explain extended select * from test_table2
where  pay_id in(
    select pay_id from test_table2
    where pay_id  > 800
     group by pay_id
     having count(pay_id) > 2
 );

从执行计划可以看出 先执行子查询(SUBQUERY), 然后扫描了全表的数据。

[
  {
    "id": 1,
    "select_type": "PRIMARY",
    "table": "test_table2",
    "partitions": null,
    "type": "ALL",
    "possible_keys": null,
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 1010,
    "filtered": 100,
    "Extra": "Using where"
  },
  {
    "id": 2,
    "select_type": "SUBQUERY",
    "table": "test_table2",
    "partitions": null,
    "type": "range",
    "possible_keys": "test_table2_pay_id_index",
    "key": "test_table2_pay_id_index",
    "key_len": "5",
    "ref": null,
    "rows": 200,
    "filtered": 100,
    "Extra": "Using where; Using index"
  }
]

即使强制指定索引也没有用

explain extended select * from test_table2 force index(test_table2_pay_id_index)
where  pay_id in(
    select pay_id from test_table2
    where pay_id  > 800
     group by pay_id
     having count(pay_id) > 2
    );

为什么？

如果直接使用这种查询 (pay_id) in 常量

效率杠杠的，使用了索引 pay_id

explain  select * from test_table2 where pay_id in(800,900)

为什么没有命中索引？

子查询的结果是未知的，不能作为外层的索引判断 。【高版本mysql优化器应该会优化成连接查询】
以上就是利用，在SQL 查询语言执行流程中，优化器执行计划生成已经索引选择阶段，子查询的结果无法提供任何的判断依据，因此不能作为外层判断索引的依据，由此导致外层直接全表扫描了。具体可以参考一下 Mysql的查询流程。
MySql查询语句执行流程

如何改进

接改为连接查询非常快

explain extended 
select * from test_table2 as t1
  inner join (
    select pay_id from test_table2
    where pay_id  > 800
     group by pay_id
     having count(pay_id) > 2
    )as tmp on tmp.pay_id =t1.pay_id;

总结

对于子查询慎用 in，常量情况下可以使用 in 涉及多表操作 in 最好使用 连接查询，性能差异可能几百倍。