互联网服务
两个SQL语句比较，哪种更优？
如下另个语句
update t1 set (a1,b1) = (select a2,b2 from t2 where t1.c1 = '2008-05-31' and t1.d1 = t2.d2) where t1.c1 = '2008-05-31' and t1.d1 in (select d2 from t2)update t1 set (a1,b1) = (select a2,b2 from t2 where t1.d1 = t2.d2) where t1.c1 = '2008-05-31' and t1.d1 in (select d2 from t2)其中c1,d1为t1的索引，d2为t2的主键。sql执行时先ixscan t1表找到符合条件的记录，再去连表t2，还是先t1与t2表内连接，然后再根据t1表的条件筛选？这两句sql哪个性能更优？
update t1 set (a1,b1) = (select a2,b2 from t2 where t1.c1 = '2008-05-31' and t1.d1 = t2.d2) where t1.c1 = '2008-05-31' and t1.d1 in (select d2 from t2)update t1 set (a1,b1) = (select a2,b2 from t2 where t1.d1 = t2.d2) where t1.c1 = '2008-05-31' and t1.d1 in (select d2 from t2)其中c1,d1为t1的索引，d2为t2的主键。sql执行时先ixscan t1表找到符合条件的记录，再去连表t2，还是先t1与t2表内连接，然后再根据t1表的条件筛选？这两句sql哪个性能更优？
7同行回答
UPDATE t1
SET (a1, b1) =
(SELECT a2, b2
FROM t2
WHERE t1.c1 = '2008-05-31' AND t1.d1 = t2.d2)
WHERE t1.c1 = '2008-05-31'
AND EXISTS
(SELECT d2
FROM t2
WHERE t1.d1 = t2.d2);
索引加好，这样效率是不是更高一些。可以考虑用游标循环update，比较一下效率。收起
SET (a1, b1) =
(SELECT a2, b2
FROM t2
WHERE t1.c1 = '2008-05-31' AND t1.d1 = t2.d2)
WHERE t1.c1 = '2008-05-31'
AND EXISTS
(SELECT d2
FROM t2
WHERE t1.d1 = t2.d2);
索引加好，这样效率是不是更高一些。可以考虑用游标循环update，比较一下效率。收起
浏览899