Question

sql优化 查询太慢，需要提高查询速度

帮忙对下面sql进行优化，特别是not in 语句的优化
SELECT *
FROM (SELECT M2.*,
(SELECT COUNT(ID)
FROM GISDUCT
WHERE STATUS != 0
AND DOMAIN_ = '592'
AND PROPERTYTYPE IN (0)
AND DUCTSEGID = H.ID
AND ID NOT IN (SELECT DISTINCT SUPERIORID
FROM GISDUCT
WHERE SUPERIORID IS NOT NULL
AND DUCTSEGID = H.ID)) OCCUPYCOUNT,
(SELECT COUNT(ID)
FROM GISDUCT
WHERE DUCTSEGID = H.ID
AND PROPERTYTYPE IN (0)
AND ID NOT IN (SELECT DISTINCT SUPERIORID
FROM GISDUCT
WHERE SUPERIORID IS NOT NULL
AND DUCTSEGID = H.ID)) TOTALCOUNT
FROM GISDUCTSEG M2,
(SELECT ID
FROM GISDUCTSEG
WHERE 1 = 1
AND PROPERTYTYPE = 0
AND DOMAIN_ = '592') H
WHERE M2.ID = H.ID)
WHERE TOTALCOUNT != 0
AND OCCUPYCOUNT / TOTALCOUNT * 100 >= 20
ORDER BY (OCCUPYCOUNT * 100 / TOTALCOUNT) DESC
查询耗时一般6s左右，希望内在2、3s内查询出

Answer 1

你的这个查询要优化的地方不是not in, 而是整个查询的结构: 使用了太多的子查询，而且都是查找的相同的表(GISDUCT表查询4次)，这肯定不是好的查询方法，应该把你要达到的目的再思考，转化成合适的查询语句。
个人认为，你算OCCUPYCOUNT和TOTALCOUNT的子查询应该可以在一个查询中搞定的，因为都是查询GISDUCT表，只是统计取值的条件有所不同罢了，而按条件统计可以用类似“case when 条件 then count(xxx) else 0 end”的结构来实现。有问题可再讨论。

Answer 2

光贴sql有什么用.
把查询计划发来啊.

追问

请见问题补充

Answer 3

没有真实环境很难测试的，可以上传你的数据库文件吗，就设计的表就可以了