记一次复用数据库连接池填坑嵌套和关联查询sql语句

本文为了记录问题排查解决的过程，以及为后续遇到类似问题解决提供方向。

先描述下问题，需要通过关联查询table_A和table_B两表的数据，分页显示，其中，还需要查询每条记录的参与人数，对应数据在表table_C中。码代码时为了方便想用一条sql解决问题，测试环境由于数据量级的问题，一切OK！上线时候，查询数据出现超时情况，前段请求超时时间设置为5秒。

1、排查问题

通过查看日志发现部分请求的响应时间5-6秒。随分析问题原因！
本项目中存在权限问题，不同用户查看数据量不同。然后发现普通用户查看列表数据无任何问题，管理员账号查看数据出现超时问题，管理员账号的权限是可以查看所有数据，由于查看列表接口没有复杂逻辑，先排查下sql语句的执行时间！

先附上sql语句

    SELECT t.*, s.name, (select count(o.id) FROM table_C o where o.act_id = t.id)
    FROM table_A t left join table_B s on t.type = s.type_code where 条件

上面的sql语句很简单，查看生产上这条sql执行时间，发现查询20条记录差不多接近3秒，全量数据10s以上。查看执行计划，发现没有命中索引，都是全表扫描，TABLE ACCESS FULL。首先想到的是left join 改写为inner join，然而依然是全表扫描，后来想到查询结果里面有s.name的 ，若把s.name去掉，table_A表inner join table_B关联字段有索引是可以命中索引 INDEX RANGE SCAN（关联查询的查询结果字段需要是索引字段，才能命中索引）。但是查询结果里面是需要name。因此不可行。

可命中索引的关联查询

    SELECT t.*, (select count(o.id) FROM table_C o where o.act_id = t.id)
    FROM table_A t left join table_B s on t.type = s.type_code where 条件

2、分析问题

先查看三个表的记录个数，表A几千条数据，表B几十条数据，表C有十几万条数据。通过执行计划可知嵌套查询的表C没有全表查询，检索个数为几百条。那么总的查询记录数也不多，不可能出现执行时间这么长的情况！查看执行计划发现表A的字节数达到5232918，依稀猜测可能导致此问题的原因是表A的一条记录太大。后来想到sql查询结果是按页获取的，每页的大小不同的数据库有不同的限制。然后分析表A的结构，表A字段数为40多字段，没有超过50，目前不考虑增量表（当前上线时刻，不可能改动表结构），存在几个字段存储的值非常大，有clob字段。按数据库每页可以存1M来算，单条记录为100K，则可以存10条，如果单条记录为1K，则可以存1000行。数据存储的记录数越多，查询的结果会更快，没有存下来的记录都需要重新查询一遍。
初步定位问题可能是大表导致关联查询时间过长，那么先想办法减少查询的记录行数；尽可能一次查询多的数据。由于知道表A行数据过大，因此，先通过where条件查询出A表的记录，然后在做关联查询。

有条件的查询然后关联查询

    SELECT t.*, s.name, (select count(o.id) FROM table_C o where o.act_id = t.id)
    FROM （select t.* from table_A t where 条件）t 
    left join table_B s on t.type = s.type_code where 条件

上面的sql确实是减少检索表A记录个的数，通过执行计划可知检索记录数减少了，字节数也下降了。奈何条件不能有效的减少表A的查询的结果，因此临时表的结果记录数依然偏多。

3、解决问题

由于表B记录数和行记录都很小，因此表A和B的关联查询可继续使用。表C的记录数十几万条，在嵌套查询的时候需要查询表A中的记录id，然后到表C中查询匹配的记录。由于表A数据较大，因此需要减少表之间的关联查询。个人理解数据库先缓存表A的记录，然后去表C查询匹配的记录个数，原本的想法是减少数据库对表A的中间缓存量（此处不能确定嵌套查询是否如此，后续会去查阅相关资料）。那就先把sql拆分，先通过表A和B关联查询获取结果，观察结果及执行计划。

去除嵌套的关联查询

    SELECT t.*, s.name FROM （select t.* from table_A t where 条件）t 
    left join table_B s on t.type = s.type_code where 条件

上面的sql语句由于关联查询结果有表B的非索引字段，因此依然没有命中索引，关联查询的执行计划的检索记录数和字节数都没有发生变化。由于是分页查询，查询时间确实非常快了0.2m，在有缓存的情况下，查询更快。因此可以确定耗时长的应该就是在嵌套查询的过程中，由于表A的行记录很大，占的字节数较大，而表C的记录数又是非常多。因此sql语句执行时间过长。
现在怎么把表C的查询结果，拼接到最终的查询结果中。程序操作数据库最耗时的就是连接，现在的框架中都用了数据库连接池，本项目中使用了druid连接池，项目启动时就创建了多个连接池，基本省去了创建连接这个最耗时的过程，因此可以利用连接池的复用来拼接查询结果。

查询表C的记录

    select count(o.id) FROM table_C o where o.act_id = '20'

在逻辑层先通过关联查询获取查询结果，在对结果依次查询表C中的结果。本来是操作数据的次数增加了很多，但是，最终处理时间确实减少了几十倍。因为表C的查询语句简单，而且，有效利用的连接池，减少了耗时的操作。

总结

在很多互联网公司基本是严令禁止关联表查询的，由于本项目中的数据量级较小，为了减少逻辑处理过程，因此依然用了关联查询。此问题出现在上线时间节点，因此在不可能改变硬件环境，以及较小改动情况下，择优解决问题。最大的心得体会是数据库里面内容还是很深，了解的太少，这里记录问题的解决过程，以便后面此类问题的解决，以及为解决相关问题提供方向。首先是要找到问题，然后利用所了解的知识去解决问题，最后去补充盲点和尝试更优的方案解决问题。