请大家帮忙优化一段SQL

Question

执行时间为22秒，返回180行
et_d_JobInfo表里的数据量有60万，字段数有40个
et_s_Comp表里的数据量有20万，字段数有33个
我想达到的速度在5秒里面，不知道有没有希望？SELECT ji.[iID] AS ID  
FROM et_d_JobInfo ji,et_s_Comp c  
WHERE ji.iCompID = c.[iID]  
AND ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%')  ) 
AND ((ji.iWorkPlaceID1 LIKE '33%' 
OR ji.iWorkPlaceID2 LIKE '33%' 
OR ji.iWorkPlaceID3 LIKE '33%') ) 
AND (c.iIndustryID LIKE '%%' ) 
AND ji.iSalaryID = 13 
AND ji.tiJobTerm = 1 
AND ji.tijobstate = 1 
AND ji.[iID] 
NOT IN (
SELECT [iJobID] 
FROM [ps_d_ResumeRec] 
WHERE [iUserID] = 395007 
AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7) 
ORDER BY ID DESC

Answer 1

iCompID 和 iID加索引，然后把not in去掉

Answer 2

索引有加，请问not in 要怎么改？

Answer 3

not in 改成 类似ji left join c on ji.[iID]=c.iJobID where c.id is null的形式

Answer 4

改用inner join 
not in 也改
把模糊查询条件放在最后（？）

Answer 5

in , not in 查询很慢.改为exists , not exists.效率会比in,not in稍好一些.

Answer 6

我不会用exists，能不能帮忙改一下

Answer 7

in是把外部和内表进行hash连接
exists是对外表做loop循环，然后每次loop循环再对内部进行查询如果两个表一个较小一个较大。则子查询大表的用exists，子查询表小的用inexp: A 小表   B 大表
1.SELECT * FROM A WHERE CC IN (SELECT CC FROM B)
效率低，用到了A上的CC列索引
SELECT * FROM A WHERE EXISTS (SELECT CC FROM B WHERE CC = A.CC)
效率高，用到了B表上的CC列索引
2.SELECT * FROM B WHERE CC IN (SELECT CC FROM A)
效率高，用到了B表上CC列索引
SELECT * FROM B WHERE EXISTS (SELECT CC FROM A WHERE CC = B.CC)
效率低，用到了A表上CC列的索引
not in 是对内外表进行全表扫描，没有用到索引
not exists的子查询仍用表上索引
所以不论什么情况。not exists比not in要快
例子你就看上面的写吧

Answer 8

简单点说
IN 是用主表的索引
EXISTS是用子表的索引主表小的时候用IN，子表小的时候用EXISTS不论什么情况，只用NOT EXISTS，不用NOT IN

Answer 9

SELECT ji.[iID] AS ID  
FROM et_d_JobInfo ji,et_s_Comp c  
WHERE ji.iCompID = c.[iID]  
AND ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%')  ) 
AND ((ji.iWorkPlaceID1 LIKE '33%' 
    OR ji.iWorkPlaceID2 LIKE '33%' 
    OR ji.iWorkPlaceID3 LIKE '33%') ) 
AND (c.iIndustryID LIKE '%%' ) 
AND ji.iSalaryID = 13 
AND ji.tiJobTerm = 1 
AND ji.tijobstate = 1 
AND  
NOT Exists (
    SELECT [iJobID] 
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7
    And ji.[iID]=[iJobID] 
) 
ORDER BY ID DESC

Answer 10

SELECT ji.[iID] AS ID  
FROM et_d_JobInfo ji RIGHT JOIN et_s_Comp c  
ON ji.iCompID = c.[iID]  
WHERE ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%')) 
AND ((ji.iWorkPlaceID1 LIKE '33%' 
    OR ji.iWorkPlaceID2 LIKE '33%' 
    OR ji.iWorkPlaceID3 LIKE '33%')) 
AND (c.iIndustryID LIKE '%%') 
AND ji.iSalaryID = 13 
AND ji.tiJobTerm = 1 
AND ji.tijobstate = 1 
AND  
NOT EXISTS (
    SELECT [iJobID] 
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7
    And ji.[iID]=[iJobID] 
) 
--ORDER BY ID DESC21秒

Answer 11

SELECT ji.[iID] AS ID  FROM et_d_JobInfo AS ji 
LEFT JOIN et_s_Comp AS c  ON ji.iCompID = c.[iID]
WHERE ji.iCompID = c.[iID]  
AND ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%')  ) 
AND ((ji.iWorkPlaceID1 LIKE '33%' 
    OR ji.iWorkPlaceID2 LIKE '33%' 
    OR ji.iWorkPlaceID3 LIKE '33%') ) 
AND (c.iIndustryID LIKE '%%' ) 
AND ji.iSalaryID = 13 
AND ji.tiJobTerm = 1 
AND ji.tijobstate = 1 
AND  
NOT Exists (
    SELECT [iJobID] 
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7
    And ji.[iID]=[iJobID] 
) 
ORDER BY ID DESC你用的是全联结。笛卡尔积，不慢才怪

Answer 12

去掉order by 会快点。。

Answer 13

NOT EXISTS (
    SELECT [iJobID] 
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7
    And ji.[iID]=[iJobID] 
) 
这段不执行，看看执行时间如何
是不是ps_d_ResumeRec表的数据量也很大？

Answer 14

SELECT ji.[iID] AS ID  
FROM et_s_Comp c LEFT JOIN et_d_JobInfo ji
ON ji.iCompID = c.[iID]  
WHERE ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%')) 
AND ((ji.iWorkPlaceID1 LIKE '33%' 
    OR ji.iWorkPlaceID2 LIKE '33%' 
    OR ji.iWorkPlaceID3 LIKE '33%')) 
AND (c.iIndustryID LIKE '%%') 
AND iSalaryID = 13 AND tiJobTerm = 1 AND tijobstate = 1
--AND  
--NOT EXISTS (
--    SELECT [iJobID] 
--    FROM [ps_d_ResumeRec] 
--    WHERE [iUserID] = 395007 
----    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7
--    And ji.[iID] = [iJobID] 
--) 
--ORDER BY ID DESC23秒 SELECT [iJobID] 
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 70秒

Answer 15

SELECT ji.[iID] AS ID  
FROM et_s_Comp c LEFT JOIN et_d_JobInfo ji
ON ji.iCompID = c.[iID]  用时10秒，数据量是60万条

Answer 16

那么基础的查询时间就要10s，再优化也很难到5s
查询的瓶颈应该是在like那一段。数据量太大了，like效率不行

Answer 17

一般对于大量like的处理,是用配置全文检索的方法全文检索的配置可以参考: 
http://community.csdn.net/Expert/topic/3295/3295983.xml?temp=.8689844

Answer 18

我转的替换like，使sql语句更有效率提到Like语句大家都很熟悉，比如查找用户名包含有"c"的所有用户, 我们可以用 
use mydatabase 
select * from table1 where username like'%c%"
以下是完成上面功能的另一种写法:use mydatabase 
select * from table1 where charindex('c',username)>0
这种方法理论上比上一种方法多了一个判断语句,即>0, 但这个判断过程是最快的, 我相信80%以上的运算都是花在查找字符串及其它的运算上, 所以运用charindex函数也没什么大不了。用这种方法也有好处, 那就是对%,|等在不能直接用like 查找到的字符中可以直接在这charindex中运用, 如下:
use mydatabase 
select * from table1 where charindex('%',username)>0
大家还可以写成: 
use mydatabase 
select * from table1 where charindex(char(37),username)>0
ASCII的字符即为%

Answer 19

AND (c.iIndustryID LIKE '%%' ) 这个条件干啥？为啥不去掉

Answer 20

LZ一口气用了6个LIKE,不慢才怪.LIKE是罪魁祸首

Answer 21

如果iCompID 和 iID加索引，能把数据过滤到最小，那么like的效率可以忽略了
注意，应该是这2个字段的联合索引
而不是2个字段上建单独的索引

Answer 22

LIKE '%%'没什么意思，应该可以去掉，
类似ji.iWorkPlaceID1 LIKE '33%'，改为left(ji.iWorkPlaceID1,2)='33'试试

Answer 23

--iWorkPlaceID1,iWorkPlaceID2,iWorkPlaceID3建立索引
SELECT [iJobID] into #tb
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7SELECT ji.[iID] AS ID  
FROM et_d_JobInfo ji,et_s_Comp c,#tb d  
WHERE ji.iCompID = c.[iID]  and ji.[iID]=d.[iJobID]
AND ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%')  ) 
AND ((ji.iWorkPlaceID1 LIKE '33%' 
    OR ji.iWorkPlaceID2 LIKE '33%' 
    OR ji.iWorkPlaceID3 LIKE '33%') ) 
AND (c.iIndustryID LIKE '%%' ) 
AND ji.iSalaryID = 13 
AND ji.tiJobTerm = 1 
AND ji.tijobstate = 1 and d.[iJobID] is null
ORDER BY ID DESC
drop table #tb

Answer 24

--修改
--iWorkPlaceID1,iWorkPlaceID2,iWorkPlaceID3建立索引
SELECT [iJobID] into #tb
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7SELECT ji.[iID] AS ID  
FROM et_d_JobInfo ji
inner join et_s_Comp c on ji.iCompID = c.[iID]
left join #tb d  on ji.[iID]=d.[iJobID]
WHERE ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%'))---这个啥意思。。 
AND ((ji.iWorkPlaceID1 LIKE '33%' 
    OR ji.iWorkPlaceID2 LIKE '33%' 
    OR ji.iWorkPlaceID3 LIKE '33%') ) 
AND (c.iIndustryID LIKE '%%' ) 
AND ji.iSalaryID = 13 
AND ji.tiJobTerm = 1 
AND ji.tijobstate = 1 and d.[iJobID] is null
ORDER BY ID DESC
drop table #tb

Answer 25

like尽量用右like
尽量不要用or用sqlserver的执行计划找出瓶颈，创建索引

Answer 26

SELECT ji.[iID] AS ID  FROM et_d_JobInfo AS ji 
LEFT JOIN et_s_Comp AS c  ON ji.iCompID = c.[iID]
WHERE ji.iCompID = c.[iID]  
AND ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%')  ) 
AND ((ji.iWorkPlaceID1 LIKE '33%' 
    OR ji.iWorkPlaceID2 LIKE '33%' 
    OR ji.iWorkPlaceID3 LIKE '33%') ) 
AND (c.iIndustryID LIKE '%%' ) 
AND ji.iSalaryID = 13 
AND ji.tiJobTerm = 1 
AND ji.tijobstate = 1 
AND  
NOT Exists (
    SELECT [iJobID] 
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
    --AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7
    AND [dtAddTime]<getdate()-7
    And ji.[iID]=[iJobID] 
) 
ORDER BY ID DESC
--这样试下

Answer 27

1. 索引要建对
2.把那些给力的筛选条件尽量放在后面 
3.像 like '%%'  和 like '%xx' 的这种去掉好了 like  'xx%'可以保留 
4. NOT EXISTS 和 not in 像你这个数据量的话 这2个差别应该是不大的 ，数据量再大一点的话 还是用not in好
5.表的顺序是对的 大表放前面 
6.你这个需求想一句话要达到5秒基本上不可能

Answer 28

SELECT  ID  
FROM
(SELECT ji.[iID] AS ID, ji.iFunTypeID1, ji.iFunTypeID2, ji.iWorkPlaceID1, ji.iWorkPlaceID2, ji.iWorkPlaceID3
 FROM et_d_JobInfo ji,et_s_Comp c  
 WHERE ji.iCompID = c.[iID] 
 AND  ji.iSalaryID = 13 
 AND ji.tiJobTerm = 1 
 AND ji.tijobstate = 1
) AS jii
WHERE (jii.iFunTypeID1 LIKE '%%' OR jii.iFunTypeID2 LIKE '%%')
AND ((jii.iWorkPlaceID1 LIKE '33%' 
    OR jii.iWorkPlaceID2 LIKE '33%' 
    OR jii.iWorkPlaceID3 LIKE '33%')) 
AND NOT EXISTS 
(
    SELECT [iJobID] 
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
AND jii.ID = [iJobID]
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7)
ORDER BY ID DESC;

Answer 29

SELECT  ID  
FROM
(SELECT ji.[iID] AS ID, iFunTypeID1 , iFunTypeID2, iWorkPlaceID1,iWorkPlaceID2, iWorkPlaceID3
 FROM et_d_JobInfo ji,et_s_Comp c  
 WHERE ji.iCompID = c.[iID] 
 AND  ji.iSalaryID = 13 
 AND ji.tiJobTerm = 1 
 AND ji.tijobstate = 1
) AS jii
WHERE (jii.iFunTypeID1 LIKE '%%' OR jii.iFunTypeID2 LIKE '%%')
AND ((jii.iWorkPlaceID1 LIKE '33%' 
    OR jii.iWorkPlaceID2 LIKE '33%' 
    OR jii.iWorkPlaceID3 LIKE '33%')) 
AND NOT EXISTS 
(
    SELECT [iJobID] 
    FROM [ps_d_ResumeRec] 
    WHERE [iUserID] = 395007 
AND jii.ID = [iJobID]
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7)
ORDER BY ID DESC;

Answer 30

SELECT ji.[iID] AS ID  
FROM 
(
SELECT DISTINCT ji.iCompID,ji.[iID]
FROM et_d_JobInfo ji
WHERE ((ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%')) 
AND((ji.iWorkPlaceID1 LIKE '33%' OR ji.iWorkPlaceID2 LIKE '33%' OR ji.iWorkPlaceID3 LIKE '33%'))
AND ji.iSalaryID = 13 
AND ji.tiJobTerm = 1 
AND ji.tijobstate = 1 
AND NOT EXISTS(SELECT 1 FROM [ps_d_ResumeRec] C WHERE C.[iJobID]=ji.[iID] AND [iUserID] = 395007 
    AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7)
)A
JOIN 
(
SELECT c.[iID]
FROM et_s_Comp c
WHERE (c.iIndustryID LIKE '%%' ) 
)B ON A.iCompID=B.[iID]--PLEASE HAVE A TRY

Answer 31

主要效率问题我觉着是LIKE的太多，大大降低效率，STEP 1:CREATE INDEX IDX0_ET_D_JOBINFO ON ET_D_JOBINFO(iCompID);
CREATE INDEX IDX0_ET_S_COMP ON ET_S_COMP(iID);STEP 2:SELECT 
A.iID
FROM et_d_JobInfo A
INNER JOIN 
(
      SELECT
      iID
      FROM et_s_Comp B1
      WHERE iIndustryID LIKE '%%'--没看懂你这是LIKE 什么呢？是所有值吗，小弟才学疏浅，没这么写过
      AND NOT EXISTS(
                        SELECT
                        1
                        FROM ps_d_ResumeRec B2  --此表数据量大吗，大的话可以考虑iJobID建索引
                        WHERE iUserID=395007
                        AND DATEDIFF(D,[dtAddTime],GETDATE()) < 7) 
                        AND B1.iID=B2.iJobID
                    )
)B
ON A. iCompID=B.iID
WHERE 
AND A.iSalaryID = 13 
AND A.tiJobTerm = 1 
AND A.tijobstate = 1 
AND (A.iFunTypeID1 LIKE '%%' OR A.iFunTypeID2 LIKE '%%')  --同上
AND (A.iWorkPlaceID1 LIKE '33%' OR A.iWorkPlaceID2 LIKE '33%' OR A.iWorkPlaceID3 LIKE '33%')
ORDER BY A.iID DESC

Answer 32

ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%'  --这个有点困难
相信你有了必要的索引
1、显示执行计划，
2、在执行计划中找tablescan,如果有，则在相应的列上建立索引，indexscan的速度比tablescan快得多
3、如果是indexscan看看能不能变成indexseek,这又是质的飞跃
4、由于优化是由存储引擎来完成的，也许变动条件的顺序能够将计划解释的更为完美

Answer 33

你的语句太多“or”了！快不了！如果不用那么多or的话达到5秒没什么问题！ji.iFunTypeID1 LIKE '%%' OR ji.iFunTypeID2 LIKE '%%'这句可以去掉吧

Answer 34

OR 太多了，而且不能用LIKE %%

Answer 35

两个表连接查询时，用小表做驱动表，上面已经很明白，et_s_Comp数据比较少，它应该放在前面，作为驱动表，也会帮助提高查询速度

Answer 36

楼主在的话最好是把这2张表的index 弄出来看看啊

Answer 37

我觉得，你可以用SQL SERVER的数据库优化引擎，给你一个专家库的解决方案，他会为你生成合适的索引和查询计划。
我觉得很多人都误解了“优化”，其实影响性能的最根本因素是业务以及针对业务不合理的表结构设计。换句话说：我们总是做亡羊补牢的事情。有些时候是可以弥补的，有些时候我们只能抽根烟郁闷一下而已。