SQL优化（一） ---- in和exist

1.背景：

基础表:

原始场景不贴了，贴出一个相似场景，有3张表，

学生表(t_student) --> 100000条数据，总共有10万个学生

课程表(t_course) --> 30条数据，总共有30种课程

得分表(t_score) --> 300万数据，每个学生的每一门课程的得分

需求:

查询出语文得分是87分的学生Id。

最早版本的sql:

select s.* from t_student s where s.sid in (select sid from t_score sc where sc.cid = 1 and sc.score = 87);

在以上数据的基础上，我们执行一次，发现已经等不到结果了，一直卡住，像死掉了一般；

1. 分析:

1. explain打开执行计划：

1.1 第一个直观感觉，可以加索引解决问题，因为此时type都是ALL。

1.2 DEPENDENT SUBQUERY这个关键字可不是什么好东西，暂时放着，不去管这个。

2. 先根据第一反应，加索引，没有什么是不能通过索引解决的。

CREATE index sc_c_id_index on t_score(cid);
CREATE index sc_c_score_index on t_score(score);

2.1 执行查询语句：

发现依然没法短时间查询出结果，本机等了10分钟没结果，果断砸了~~~~~

2.2 改变加索引的方式，做成覆盖索引：

2.2 执行语句: 终于出结果了，主要是利用覆盖索引的特性，直接在索引里查到sid，不需要二次查询，节省了时间

2.3 查看执行计划，总的来说不错，解决了问题。

2. 优化部分:

但是，我们一开始就使用了索引去优化，其实本身并没有对sql去分析一下，也就是没使用最优的sql，这点问题被索引的高效掩盖了，是一个隐藏的问题。

2.1 分析

我们发现，其实in查询这边可以改成连接查询，其实可以通过表的关联来查询，不必使用in查询这种容易炸的sql语法。

2.1 擦，不使用索引的基础上竟然只要1.1秒就查出数据了，可见in 中带子查询是多么坑的一件事，慎用~~~~

2.2 看下优化后的sql语句吧。

select `test`.`s`.`sid` AS `sid`,`test`.`s`.`name` AS `name` 
from `test`.`t_student` `s` join `test`.`t_score` `sc` 
where ((`test`.`s`.`sid` = `test`.`sc`.`sid`) and (`test`.`sc`.`cid` = 1) and (`test`.`sc`.`score` = '87'))

2.3 我们知道，sql语句的执行顺序是下面的顺序，因此这个sql是先去进行join，然后再进行where条件的过滤。

select[distinct]  
from  
join（如left join）  
on  
where  
group by  
having  
union  
order by  
limit  

2.4 再次优化，能不能我们先过滤出where里的特定条件，然后再join，这样又能再次排除一些垃圾数据了

擦泪，，结果并没有好，反而比先join后where的情况更差了点。

可以看见唯一的区别就是本来以为可以提高效率的查询多出一个DERIVED过程。

最后。我们选择优化之后的第一个版本，先join后where吧，然后我们再跟进需求加上特定索引。注意索引最左匹配原则。

CREATE index sc_sid_index on t_score(sid);
CREATE index sc_cid_index on t_score(cid,score);

走一波：

起飞~~~~~~~~~~~~~~

最后回到主题上来，in和exist还是慎用比较好，当然不是绝对。

另外就是  A in B 时，最好B表数据少为优。 A exist B 时， A表数据少为优。