Question

SQL嵌套查询优化

SELECT count(c.CUST_NO) FROM CI_CUS_PERSON c LEFT JOIN CI_CUS_CERT CERT ON c.CUST_NO=CERT.CUST_NO WHERE c.CUST_TYPE like '1%'
and EXISTS (SELECT 1 FROM OP_ASN_CUST_INST aci WHERE aci.CUST_NO = c.CUST_NO AND aci.ORGID IN (select eao.ORGID from eos_auth_org eao WHERE eao.OPERATORID=1))
主要是where 条件语句EXISTS (SELECT 1 FROM OP_ASN_CUST_INST aci WHERE aci.CUST_NO = c.CUST_NO AND aci.ORGID IN (select eao.ORGID from eos_auth_org eao WHERE eao.OPERATORID=1)) 这个嵌套的优化，我的积分就这么多，没法加了，求高手
多谢大家帮忙，如果有办法我会都给积分，现在问题还没解决，下午再看看怎么办
结果集有重复数据列，单独对 c.CUST_NO的 Distinct，这个 c.CUST_NO，原来就有索引，我是没办法完成了，已经交给大老处理，我已经尝试过对单独 对c.CUST_NO使用Distinct，再和 CI_CUS_PERSON 内连接，结果也不理想，总之谢谢了，分数给你吧，你留在这最久

Answer 1

单从语句方面基本上没有优化的地方了，可以尝试下
SELECT count(c.CUST_NO)
FROM CI_CUS_PERSON c
LEFT JOIN CI_CUS_CERT CERT ON c.CUST_NO=CERT.CUST_NO
WHERE c.CUST_TYPE like '1%'
AND c.CUST_NO IN (SELECT CUST_NO FROM OP_ASN_CUST_INST aci WHERE ORGID IN (select eao.ORGID from eos_auth_org eao WHERE eao.OPERATORID=1)) 如果后面那个in里面的查询量不大的话。

可以考虑索引
OP_ASN_CUST_INST.CUST_NO,OP_ASN_CUST_INST.ORGID,
CI_CUS_PERSON.CUST_NO上面建立索引

追问

已经建立索引，90W的数据非常慢，我考虑过
SELECT count( c.CUST_NO)FROM CI_CUS_PERSON c
inner join OP_ASN_CUST_INST aci on aci.CUST_NO = c.CUST_NO inner join eos_auth_org eao on eao.ORGID = aci.ORGID and eao.OPERATORID=1
LEFT JOIN CI_CUS_CERT CERT ON c.CUST_NO=CERT.CUST_NO
where c.CUST_TYPE = '1' 这样会提高点效率，可是没办法去除重复记录

追答

tRY

SELECT count(c.CUST_NO)
FROM (SELECT * FROM CI_CUS_PERSON WHERE CUST_TYPE like '1%') c
LEFT JOIN CI_CUS_CERT CERT ON c.CUST_NO=CERT.CUST_NO
WHERE EXISTS (SELECT 1 FROM (SELECT * FROM OP_ASN_CUST_INST WHERE ORGID IN (select ORGID from eos_auth_org WHEREOPERATORID=1)) aci
WHERE aci.CUST_NO = c.CUST_NO )

追问

执行结果还是很慢，这个方法没有去除嵌套问题，老大再帮忙想想

追答

已经建立的索引有哪些？

Try this one without sub-query

SELECT count(c.CUST_NO)
FROM (select * from CI_CUS_PERSON where CUST_TYPE like '1%') c
LEFT JOIN CI_CUS_CERT CERT ON c.CUST_NO=CERT.CUST_NO
INNER JOIN (SELECT aci.CUST_NO
FROM OP_ASN_CUST_INST aci
INNER JOIN (SELECT ORGID FROM eos_auth_org WHERE OPERATORID=1) eao
ON aci.ORGID =eao.ORGID) t2
ON t2.CUST_NO = c.CUST_NO

追问

做了下测试，在性能方面可以了，就是无法做到去除重复，加上Distinct后效率又下来了，这有没有办法解决

追答

不明白你的去重复是要去掉那个重复。可以考虑在distinct列加索引

当然，也要综合考虑，为了一个查询增加索引，要考虑值不值得。

Answer 2

1. 关联的列都建索引
2. 这样试试：
EXISTS (SELECT 1 FROM OP_ASN_CUST_INST aci,eos_auth_org eao WHERE aci.CUST_NO = c.CUST_NO AND aci.ORGID = eao.ORGID and eao.OPERATORID=1)

追问

测试了下，效率还是没有提高，需要1分钟多，使用的是SYBASE数据库

追答

分别查一下：
1. 下列表的数据行数
2. 指定列的不同值个数
3. 指定内容的数据个数
然后根据这些情况，再尝试优化。

A. CI_CUS_PERSON 行数，CUST_NO 的个数，是否索引
B. OP_ASN_CUST_INST 行数，CUST_NO 的个数，是否索引; ORGID 的个数，是否索引
C. eos_auth_org 行数，ORGID 的个数，是否索引；OPERATORID 不同值的个数，值为1 的行数，是否索引
D. CI_CUS_CERT 行数，CUST_NO 的个数，是否索引; c.CUST_TYPE like '1%' 的行数，是否索引

追问

一、CI_CUS_PERSON ，条数854466，CUST_NO 是索引
二、OP_ASN_CUST_INST，条数 915789，CUST_NO ORGID 是索引
三、eos_auth_org，条数 3857，这个是视图，我也是现在才知道
四、已测试，和CI_CUS_CERT 关系不大

追答

1. 你没有告诉我各个关联列中的不同值的个数
2. 既然eos_auth_org是视图，可以尝试去掉它，看其影响多大（因为是视图，不管其返回多少行，需要考虑其基表有多少行，是否合理使用索引才行）
3. 再次尝试去掉子查询（我觉得这个应该和原先的等效，需要你测试一下）：

SELECT count(c.CUST_NO)
FROM CI_CUS_PERSON c LEFT JOIN CI_CUS_CERT CERT
ON c.CUST_NO=CERT.CUST_NO
inner join OP_ASN_CUST_INST aci on aci.CUST_NO = c.CUST_NO
inner join eos_auth_org eao on aci.ORGID = eao.ORGID
WHERE c.CUST_TYPE like '1%' and eao.OPERATORID=1

4. 如果还不行的话，我觉得再改进的可能性不是很大。

追问

应该是视图导致效率低下，视图必须要改成其他方式，这种连接的，对多字段的去重，不好实现，多谢帮忙

追答

呵呵，不客气，找到原因就有改进的可能

Answer 3

Select Count(A.CUST_NO) From
(SELECT Distinct c.CUST_NO FROM CI_CUS_PERSON c
inner join OP_ASN_CUST_INST aci on aci.CUST_NO = c.CUST_NO
inner join eos_auth_org eao on eao.ORGID = aci.ORGID and eao.OPERATORID=1
LEFT JOIN CI_CUS_CERT CERT ON c.CUST_NO=CERT.CUST_NO
where c.CUST_TYPE = '1'
) A

追问

count(A.CUST_NO) 我是用来测试的，其实查的是很多字段，有办法在很多字段里面对“A.CUST_NO”使用Distinct ？

追答

可以。里面选择出来，外面再选择出来就可以了
Select A.CUST_NO,A.XXXX From
(SELECT Distinct c.CUST_NO,c.XXXX FROM CI_CUS_PERSON c
inner join OP_ASN_CUST_INST aci on aci.CUST_NO = c.CUST_NO
inner join eos_auth_org eao on eao.ORGID = aci.ORGID and eao.OPERATORID=1
LEFT JOIN CI_CUS_CERT CERT ON c.CUST_NO=CERT.CUST_NO
where c.CUST_TYPE = '1'
) A

追问

加上后SELECT Distinct TOP 2500 c.CUST_NO as "custNo", c.CUST_NAME as "custName", CERT.CERT_NO as "certNo", c.CUST_TYPE as "custType"
FROM CI_CUS_PERSON c
inner join OP_ASN_CUST_INST aci on aci.CUST_NO = c.CUST_NO
inner join eos_auth_org eao on (eao.ORGID = aci.ORGID and eao.OPERATORID=1)
LEFT JOIN CI_CUS_CERT CERT ON c.CUST_NO=CERT.CUST_NO
where c.CUST_TYPE = '1' ，效率变得很慢

追答

这是直接Sql提高的比较好的办法了。还是不行的话，只能想着利用存储过程来实现了。
用临时表来储存你需要的数据 逐步筛选。这样才会达到你的要求。