最近有个api接口查询特别慢,最终跟踪下来是sql的问题,一个查询大概6.5s;因为这个sql连接的表过多,然后sql语句写的有问题,先描述下场景,然后再说下解决方案:
场景涉及到3张表左右,表结构是这样的
case表------------
caseId caseName
1 项目1
2 项目2
..............
requirement表---------
requirementId caseId
1 1
2 1
3 2
4 2
..........
datamart表-------
id caseid
1 1
2 1
3 2
4 2
需求是统计所有case的requirement的数量和datamart数量;比较容易想的思路是直接三个表进行左连接
SELECT
cases.caseId,
count(datamart.id) AS "datamartCount",
count(requirement.requirementId) AS "requirementCount"
FROM cases
LEFT JOIN datamart ON cases.caseId = datamart.caseId
LEFT JOIN requirement ON requirement.caseId = cases.caseId
GROUP BY cases.caseId
结果如图所示
但是这种方式效率是极地的;我们不妨先不做分组,看下执行的结果
SELECT *
FROM cases
LEFT JOIN datamart ON cases.caseId = datamart.caseId
LEFT JOIN requirement ON requirement.caseId = cases.caseId
执行结果如图
这个数量是相当大的,记录条数指数级增长,2*2*2直接8条记录;这个demo还是三张表,每张表只有2条记录,如果5张表,每张表50条记录,这个数量可能达到50*50*50*50*50条记录,这个数据就很大了;所以这种方式会很慢;
优化的方式主要是通过子查询,返回查询结果,然后再和表join,具体代码如下
SELECT *
FROM cases
LEFT JOIN (SELECT
datamart.caseId,
count(datamart.id)
FROM datamart
GROUP BY datamart.caseId) AS d
ON d.caseId = cases.caseId
LEFT JOIN (SELECT
requirement.caseId,
count(requirement.requirementId)
FROM requirement
GROUP BY requirement.caseId) AS r ON r.caseId = cases.caseId
查询结果如下所示
这种方式因为记录数比较少,所以效率很高;
总结:在left join 多张表的时候,不妨试试这种思路,先做子查询的结果,再做join,而不是直接join连接;