--两表结构完全相同,数据量都在500万以上,有什么办法能提高这条语句的查询速度
SELECT *
FROM T_AAA A, T_BBB B
WHERE A.ID = B.ID
AND ABS(A.FIELD1 - B.FIELD1) <= 10
AND ABS(B.FIELD2 - B.FIELD2) <= 100;慢就慢在
AND ABS(A.FIELD1 - B.FIELD1) <= 10
AND ABS(B.FIELD2 - B.FIELD2) <= 100;
这两句话了,求如何改善语句提高查询速度
SELECT *
FROM T_AAA A, T_BBB B
WHERE A.ID = B.ID
AND ABS(A.FIELD1 - B.FIELD1) <= 10
AND ABS(B.FIELD2 - B.FIELD2) <= 100;慢就慢在
AND ABS(A.FIELD1 - B.FIELD1) <= 10
AND ABS(B.FIELD2 - B.FIELD2) <= 100;
这两句话了,求如何改善语句提高查询速度
FROM T_AAA A, T_BBB B
WHERE A.ID = B.ID 将计算结果ABS(A.FIELD1 - B.FIELD1)保存到统计表中
ABS(B.FIELD2 - B.FIELD2) <= 100;
这个有点问题吧,其结果始终是0
2.
FIELD2,FIELD1的值的分布情况大概说一下,比如说正负情况
AND ABS(a.FIELD2 - B.FIELD2) <= 100;
改成
and a.field1<=b.field1+10 and a.field1>=b.field1-10
and a.field2<=b.field2+100 and a.field2>=b.field2-100
试试,建a表中field1和field2、id的联合索引
原语句
update T_AAA A
set a.bj = 0
WHERE not exist (select 1
from T_BBB B
where A.ID = B.ID
AND ABS(A.FIELD1 - B.FIELD1) <= 10
AND ABS(B.FIELD2 - B.FIELD2) <= 100);AND ABS(A.FIELD1 - B.FIELD1) <= 10
AND ABS(A.FIELD2 - B.FIELD2) <= 100);
10和100其实是偏移量,也就是两表字段数据之差小于偏移量就行。慢就是因为这两句解决思路:因为两表不同的数据是比较少的,索性我就先标志出两表相同的数据,然后再拿不同的数据去对比,缩小范围,速度大幅提升。
标志出相同数据:
UPDATE T_AAA A
SET A.BJ = 2
WHERE NOT EXIST (SELECT 1
FROM T_BBB B
WHERE A.ID = B.ID
AND A.FIELD1 = B.FIELD1
AND B.FIELD2 = B.FIELD2);
这条语句很快,接着再执行如下语句,加上偏移量,缩小范围UPDATE T_AAA A
SET A.BJ = 0
WHERE NOT EXIST (SELECT 1
FROM T_BBB B
WHERE A.ID = B.ID
AND ABS(A.FIELD1 - B.FIELD1) <= 10
AND ABS(B.FIELD2 - B.FIELD2) <= 100)
AND A.BJ = 2;最后BJ=0的就是不同的数据。
非常感谢大家的回复,谢谢!