解读数据库的嵌套查询的性能问题

解读数据库的嵌套查询的性能

explain 是非常重要的性能查询的工具！！！

1、嵌套查询

首先大家都知道我们一般不提倡嵌套查询或是join查询

原因在哪呢？

下面是一个简单地嵌套查询

SELECT id ,name ,age

FROM teacher

WHERE status=0 and name IN (

SELECT name FROM student WHERE age >18

)

我们一开始设想的是先执行内部查询，然后再执行外部查询的。

这是我们美好的愿景。

这个时候我们就可以使用explain来看一下这条语句的执行过程是怎样的

+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
| id  | select_type | table    | type  | possible_keys | key     | key_len | ref | rows | Extra    |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
|  1 | PRIMARY   | teacher   | ALL  | NULL     | NULL     | NULL  | NULL |65712| Using where |
|  1 | PRIMARY   | <subquery2> | eq_ref | distinct_key | distinct_key | 4    | func |  1 |       |
|  2 | DEPENDENT SUBQUERY| student   | ALL  | NULL     | NULL     | NULL  | NULL | 418 | Using where |

这里可以看到student表的select_type是DEPENDENT SUBQUERY

DEPENDENT SUBQUERY是什么意思呢？

翻译就是依靠外层查询

简而言之就是student内层查询要依靠外层查询

如上面显示，teacher表中关联行数是65712

那就意味着内层查询要执行6万次之多，肯定会很慢的。

但也不是所有的嵌套的select_type都是DEPENDENT SUBQUERY

比如还有MATERIALIZED类型，他就是sql自己进行的优化，他会在第一次进行子查询的时候建立一个临时表，保证后续查询的速度。

2、join查询

join连接也是类似的，联表查询时，会有一个驱动表来作为原始数据的循环表。

如果使用的是left join那么左表就是这个驱动表，反之亦然

我们要尽量用小表来当做驱动表。如果实在不能判断哪个比较合适就用join让Mysql来帮你做选择，他会自动选择一个小表来做驱动表。

3、解决方法

1、首先，最直接简单地方法就是不使用嵌套查询。

使用多个单个的查询来代替嵌套查询

2、其次，我们还可以使用临时表进行简单地嵌套查询

SELECT id ,name ,age

FROM teacher t, (SELECT name FROM student WHERE age>18) s

WHERE t.status=0 and t.name=s.name

)

问题：数据库内部嵌套关系实现

我在做报表的时候遇到一个问题，想了很长时间没有解决，后来转换思路一下子就解决了。具体问题是这样的，我们公司有一张行业表，总共有四级行业需要维护，具体包括一级行业、二级行业、三级行业和四级行业，每个行业之间又存在包含关系，比如四级行业包含于三级行业，三级行业包含于二级行业，二级行业包含于一级行业，最诡异的地方就是我们把这么多信息放在一张表里维护，只不过额外加了两个字段以示区分，一个是行业等级，一个是父行业，具体的表结构如下：

最后的需求是有另外一张表，是用四级行业划分的，其中有一项费用，最后需要按一级行业统计每个行业的费用。

模型

根据实际业务，为了说明这个问题，笔者在这里做了一个模型简化，假设我们只有两张表tb_cls和tb_cost，tb_cls包含行业id，行业等级cls，父行业p_id，所有行业（包括一级、二级、三级行业都保存在这张表里）都包含在内，具体创建出来的表如下（为了读者阅读方便，这里做了一个简化：id前面的第一位数代表一级行业编码，例如121表示属于一级大行业；整个id的位数代表几级行业，例如211总共三位表示三级行业）：

另外一张表，我也做了简化，只提取其中用到的行业id和费用两个字段，具体的表内容如下：

问题

我们现在的任务有两个：

• 第一、建立三级行业跟一级行业一一对应关系；
• 第二、按一级行业统计费用。

思路

弯路：

最开始的思路是嵌套，就是根据现实世界的逻辑关系一层一层建立联系，SELECT * FROM tb WHERE id IN(SELECT * FROM tb WHERE)，沿着这个思路尝试了很多，首先在SELECT外层声明的变量内层的嵌套识别不了，内外层建立的变量不能相互访问，另外一个这种建立起来的关系，没有一一对应关系，因为我们用的是IN，最终只要存在就可以，所以没有严格的一一对应关系。具体思路如下：

1.1 第1层：

SELECT id FROM tb_cost

1.2 第2层：

SELECT p_id FROM tb_cls WHERE id IN(SELECT id FROM tb_cost) AND cls=3

1.3 第3层：

SELECT p_id FROM tb_cls WHERE id IN(SELECT p_id FROM tb_cls WHERE id IN(SELECT id FROM tb_cost) AND cls=3) AND cls=2

1.4 第4层（最终）：

SELECT t1.id,t2.id FROM tb_cls AS t1,tb_cost AS t2 WHERE t1.id IN(SELECT p_id FROM tb_cls WHERE id IN(SELECT p_id FROM tb_cls WHERE id IN(SELECT id FROM tb_cost) AND cls=3) AND cls=2)AND cls=1;

最终查询的结果如下：

发现那里不对了没有，每个一级行业下面包含所有的三级行业，所以这种嵌套方式走不通，同时进一步深入下去研究发现嵌套内外层定义的变量是不能相互交互的，什么意思呢？

SELECT t1.id, var_1 FROM t1 WHERE p_id IN(SELECT id AS var_1 FROM t1)var_1变量在内层那个SELECT是不可用的。

新思路：

基于上面的弯路，笔者换了一个，假设我们有3张一模一样的表，通过这3张不同的表来区分各自的逻辑关系，把这3张表看成不同的表，一个个添加条件，具体思路如下：

2.1 第1层：tb_cls(AS t3)三级行业跟tb_cost(AS t4)建立关联：t3.id=t4.id AND t3.cls=3

2.2 第2层：tb_cls(AS t2)二级行业跟tb_cls(AS t3)建立关联：t3.p_id=t2.id AND t2.cls=2

2.3 第3层：tb_cls(AS t1)一级行业跟tb_cls(AS t2)建立关联：t2.p_id=t1.id AND t1.cls=1

最终，建立起来的三级行业对应一级行业的对应关系如下：

SELECT t1.id,t4.id FROM tb_cls AS t1,tb_cls AS t2,tb_cls AS t3,tb_cost AS t4 WHERE t4.id=t3.id AND t3.p_id=t2.id AND t2.p_id=t1.id AND t3.cls=3 AND t2.cls=2 AND t1.cls=1;

查询结果如下，跟我们实际建立的情况一致，第一个任务（第一、建立三级行业跟一级行业一一对应关系）完成。 

解决了第一个任务，第二个任务就简单多了，其实就是按照一级行业id加个GROUP BY，分一下组就可以，

具体语句如下：

SELECT t1.id,SUM(t4.cost) FROM tb_cls AS t1,tb_cls AS t2,tb_cls AS t3,tb_cost AS t4 WHERE t4.id=t3.id AND t3.p_id=t2.id AND t2.p_id=t1.id AND t3.cls=3 AND t2.cls=2 AND t1.cls=1 GROUP BY t1.id;

查询结果如下，简单计算一下一级、二级、三级费用是不是查询出来的值，至此，任务二也圆满完成。

总之，当我们需要解决SQL语句的查询任务的时候，不要一味的选择深奥的技术、逻辑复杂的语言去解决（像笔者这里用多层嵌套，最后把自己绕进去了。）首先我们要做的是简化逻辑，能通过简单的思路解决复杂的问题本身也是一种能力，在这个基础上然后基于性能、需求、业务慢慢再继续优化SQL才是我们应该做的。

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。