MSSQL 大数据查询

[kai333hui]

表1 用户表
t_user(pk,name,jf(积分)) 接近500W行
表2 机构表
t_dept(pk,parentpk,pklj(机构的pk路径)) 接近1.5W
表2 用户机构关联表
t_user_dept(pk,userpk,deptpk) 接近600W

需要对该语句进行优化查询：
select * from t_user
where pk in (select userpk in t_user_dept a inner join t_dept b on a.deptpk= b.pk where pklj like '11,111%')
and name like '%aaa%'
order by jf desc

数据量比较大
如何进行优化查询?或者对表结构进行重构?或者如何对表进行分区?

[shoppo0505]

给t_user.name添加索引 给t_dept.pklj添加索引 给t_user_dept userpk,deptpk 两个字段添加外连 with tb_user as ( select * from t_user where name like '%aaa%' ) , tb_dept as ( select * from t_dept where pklj like '11,111%' ) select tb_user.* from t_user_dept inner join tb_user on tb_user.pk = t_user_dept.userpk and tb_dept.pk = t_user_dept.deptpk

[shiyiwan]

几百万数据根本没有必要做表分区，何况表的列如此简单。 虽然没有贴出执行计划，但是觉得你的IN用法不对，在user符合条件的数量较多时应该使用exist改写。

select  * 
  from t_user as t 
 WHERE EXISTS (SELECT 1 FROM t_user_dept a INNER JOIN t_dept b ON a.deptpk = b.pk WHERE b.pklj like '11,111%' AND t.PK = a.userpk)
   AND NAME LIKE '%aaa%'
 ORDER BY t.jf DESC; 

[yooq_csdn]

t_user_dept(pk,userpk,deptpk) 接近600W -------------- 每次都有查询全部数据吗？想想这个问题，每次只取需要的部分。

[中国风]

在条件列建上索引 语句改改

select  c.* from t_user as c inner join t_user_dept a on c.pk=a.userpk inner join t_dept b on a.deptpk= b.pk and pklj  like '11,111%'
where a.name like '%aaa%' order by  c.jf desc

[kai333hui]

引用 1 楼 shoppo0505 的回复:

给t_user.name添加索引 给t_dept.pklj添加索引 给t_user_dept userpk,deptpk 两个字段添加外连 with tb_user as ( select * from t_user where name like '%aaa%' ) , tb_dept as ( select * from t_dept where pklj like '11,111%' ) select tb_user.* from t_user_dept inner join tb_user on tb_user.pk = t_user_dept.userpk and tb_dept.pk = t_user_dept.deptpk

无法绑定由多个部分组成的标识符 "tb_dept.pk"。

[kai333hui]

引用 2 楼 shiyiwan 的回复:

几百万数据根本没有必要做表分区，何况表的列如此简单。 虽然没有贴出执行计划，但是觉得你的IN用法不对，在user符合条件的数量较多时应该使用exist改写。

select  * 
  from t_user as t 
 WHERE EXISTS (SELECT 1 FROM t_user_dept a INNER JOIN t_dept b ON a.deptpk = b.pk WHERE b.pklj like '11,111%' AND t.PK = a.userpk)
   AND NAME LIKE '%aaa%'
 ORDER BY t.jf DESC; 

何况表的列如此简单：我当然还有其他字段的~ 列出来主要关联的字段而已 EXISTS 试过，效率提升不了

[jollroy]

引用 6 楼 kai333hui 的回复:

select * from t_user where pk in (select userpk in t_user_dept a inner join t_dept b on a.deptpk= b.pk where pklj like '11,111%') and name like '%aaa%'

order by jf desc select * from t_user where pk in (select userpk from t_user_dept where deptpk in ( select pk from t_dept where pklj like '11,111%' and name like '%aaaa%' )) 先不要连表查，看看速度多少 你如果用in的话就算加了索引也应该会变成全文搜索了吧。

[xiaoxiangqing]

and name like '%aaa%'-->这个看能不能改成全文检索？

[shoppo0505]

引用 5 楼 kai333hui 的回复:

[quote=引用 1 楼 shoppo0505 的回复:] 给t_user.name添加索引 给t_dept.pklj添加索引 给t_user_dept userpk,deptpk 两个字段添加外连 with tb_user as ( select * from t_user where name like '%aaa%' ) , tb_dept as ( select * from t_dept where pklj like '11,111%' ) select tb_user.* from t_user_dept inner join tb_user on tb_user.pk = t_user_dept.userpk and tb_dept.pk = t_user_dept.deptpk

无法绑定由多个部分组成的标识符 "tb_dept.pk"。[/quote] with tb_user as ( select * from t_user where name like '%aaa%' ) , tb_dept as ( select * from t_dept where pklj like '11,111%' ) select tb_user.* from t_user_dept inner join tb_user on tb_user.pk = t_user_dept.userpk inner join tb_dept on tb_dept.pk = t_user_dept.deptpk

[足球不是方的]

建议不要用join关联，不用in，小表当主链接表，条件过滤时将数据量排除多的条件放右边，修改完sql后执行下“执行计划”，执行计划有显示你sql中表的关联和查询次数，看是否需要添加相应的索引，如果有右键执行计划里面的索引，创建改索引。