在公司做一站式服务大厅时, 因为数据原因导致接口返回很慢。一个接口返回要90S;
根据接口分析,发现程序在执行SQL时发现又一些数据在执行SQL时很慢,一条SQL运行需要花30S左右。
SELECT DISTINCT
t.id AS appId,
t.priority
FROM
yzs_unified_app t
LEFT JOIN yzs_unified_app_theme t1 ON t.id = t1.app_id
LEFT JOIN yzs_app_unified_role t2 ON t.id = t2.app_id
LEFT JOIN yzs_user_unified_role t3 ON t2.role_id = t3.role_id
LEFT JOIN yzs_app_unified_dept_post t4 ON t.id = t4.app_id
LEFT JOIN yzs_user_unified_dept_post t5 ON t4.dept_post_id = t5.dept_post_id
WHERE
(
t3.user_id = '1185405911867326464' or t5.dept_post_id = -2
)
and t1.theme_id = 5
t3表数据有1W多条数 , T5有100多条数据;
当SQL
t5.dept_post_id = -2 or t3.user_id = '1185405911867326464'
替换上面条件后发现很快
我们分析发现T3 过滤数据在表存在在时间 不出现很慢, 每一条数据要遍历1W条数;
解决问题:
在On条件上过滤数据,
SELECT DISTINCT
t.id AS appId,
t.priority
FROM
yzs_unified_app t
LEFT JOIN yzs_unified_app_theme t1 ON t.id = t1.app_id
LEFT JOIN yzs_app_unified_role t2 ON t.id = t2.app_id
LEFT JOIN yzs_user_unified_role t3 ON t2.role_id = t3.role_id and t3.user_id = '1185405911867326464'
LEFT JOIN yzs_app_unified_dept_post t4 ON t.id = t4.app_id
LEFT JOIN yzs_user_unified_dept_post t5 ON t4.dept_post_id = t5.dept_post_id and t5.dept_post_id = -2
WHERE
t1.theme_id = 5
或者子查询:
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。