MY SQL 使用OR 及IN子查询后无法使用组合索引的问题-性能调优

[liuri璇玑]

表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，改为ＪＯＩＮ
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秒

请教各位大侠有无其他方案

[shoppo0505]

第一种方法再改进下，使用CTE递归找出所有884以及下属部门，这一步很快的。 然后再用你这个语句。

[独木成林_]

----替换一下你自己的表结构吧
;with c as (
select id,parent_id,name from area where id = '430000'
union all
select A.id ,A.parent_id,A.name from area A
Inner JOin c b on A.parent_id = B.id 
)
select * From c
Inner JOin T ON C.parent_id = d.departmentId And d.companyId=2

[liuri璇玑]

v5的版本，用不了with ，已用方案1和二结合处理，谢谢两位大侠