表1 :费用业务表,表名cost_expense_account,数据量100W+,创建者和部门有建索引
表2:组织架构表,表名hr_department_parent,数据量6000+
需求:查询费用业务表中,公司=2 且 (创建者ID=2487 或部门等于884及下属部门的数据)
方案1
把884及下属部门变成常量
select a.* from cost_expense_account a
where a.creatorId=2487 or a.departmentId in (445,446,447,884) and a.companyId=2
-- order by a.id desc
limit 10000;
性能不错,能使用creatorId和departmentId索引
但极端情况,这个拼出来的字符串会有3000+,就慢了
方案2
select a.* from cost_expense_account a
where (a.creatorId=2487 or a.departmentId in (select departmentId from hr_department_parent where parentId=884)) and companyId=2
无法使用departmentId索引,慢
方案3,不使用IN,改为JOIN
select a.* from cost_expense_account a
left JOIN hr_department_parent b on (a.departmentId = b.departmentId and a.depVersion = b.depVersion) and a.creatorId<>2487
where (a.creatorId=2487 or b.parentId=884) and a.companyid=2
order by id desc limit 10000;
;
效果:如果没有and a.companyid=2,速度不错0.2S,加上and a.companyid=2后,需要4秒
方案4,用UNION
SELECT m.*
FROM `cost_expense_account` m
JOIN
(
SELECT id FROM `cost_expense_account` a1 WHERE a1.`creatorId` = 2487
UNION ALL
SELECT a.id
FROM `cost_expense_account` a
JOIN `hr_department_parent` b ON (a.`departmentId` = b.`departmentId` AND a.`depVersion` = b.`depVersion` AND b.`parentId` = 884)
) n ON m.id = n.id
order by m.id desc limit 10000;
效果:8秒
请教各位大侠有无其他方案


