Mysql in 型子查询引出的陷阱

在开发的时候，核心有一个goods表和category表，category中有多级分类。假设有一个父分类为6，这个父分类中没有商品，商品都在子分类中，那么要查询分类为6的商品，如果我们使用in型子查询，会使用下面的sql。

select * from  goods where cat_id in (selectcat_id from category where parent_id=6);

直观的感觉：先执行括号里面的，也就是select cat_id from category where parent_id = 6，然后在执行select * from goods where cat_id in (1,2,3,4)，这里假设6号栏目下有子栏目1,2,3,4。

事实上，并不是这样的，会全部扫描goods表，每扫到一行，就与category表对照,看parent_id=6是否成立，原因mysql的查询优化器,针对In型做优化,被改成了exists子查询的执行效果，当goods表越大时, 查询速度越慢。

我们来看一下查询计划，来佐证我的推论，首先在外层sql，在外层中type为all，表示进行全盘查goods表，虽然我已经在cat_id列上加了索引，但是不会使用到任何索引；然后看一下内层sql，在内容sql中使用到了category表的主键索引，为什么呢？因为在外层sql每查找一条记录，就会带上cat_id去cat表中执行这样的sql，select * from category where cat_id = x and parent_id = 6;，这个cat_id就是外层sql每查找到一件商品的分类编号，这条语句会使用到category表的主键索引。

那么该如何优化呢？

我们可以改用连接查询，看看explain分析的结果，首先只是全表扫描category表，而category表的数据比较少，全表扫描对性能的影响也不会太大，不想上面的全表扫描goods，这个对性能的影响就太大了。然后就是连接查询了，还用到了goods表的cat_id的索引

select * from goods inner join (select cat_id from category where parent_id = 6) as tmp on goods.cat_id = tmp.cat_id;

in型子查询和连接查询的对比：

query_id为1的是in型子查询，花费时间为0.00131675，而连接查询的花费时间为0.00068575，可以看到，在这个问题上，连接查询比in型子查询来的高效。但并不是说对于所有的查询in型子查询都比连接查询来的慢。