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

liuri璇玑 IT  2021-02-26 03:18:17
表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秒

请教各位大侠有无其他方案
...全文
98 点赞 收藏 3
写回复
3 条回复
liuri璇玑 03月04日
v5的版本,用不了with ,已用方案1和二结合处理,谢谢两位大侠
回复 点赞
独木成林_ 03月04日


----替换一下你自己的表结构吧
;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


回复 点赞
shoppo0505 03月02日
第一种方法再改进下,使用CTE递归找出所有884以及下属部门,这一步很快的。 然后再用你这个语句。
回复 点赞
发动态
发帖子
疑难问题
创建于2007-09-28

9307

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
社区公告
暂无公告