如以下示例,表的记录很多有A,B,C三个表,每个表有6千万条记录,SELECT
A.a1,A.a2,B.b1,B.b2,C.c1,C.c2
FROM
A
LEFT OUTER JOIN B ON B.a1=A.a1
LEFT OUTER JOIN C ON C.a1=A.a1
where A.a2='xxx'
现在用左连接对表进行查询,有什么办法可以将其效率优化???
A.a1,A.a2,B.b1,B.b2,C.c1,C.c2
FROM
A
LEFT OUTER JOIN B ON B.a1=A.a1
LEFT OUTER JOIN C ON C.a1=A.a1
where A.a2='xxx'
现在用左连接对表进行查询,有什么办法可以将其效率优化???
select A.a1,A.a2,B.b1,B.b2,C.c1,C.c2
from A,B,C where B.a1=A.a1 and C.a1=A.a1
and A.a2='xxx'所得到的结果一致
建个索引 create index index_a2 on A(a2)
SELECT
A.a1,A.a2,B.b1,B.b2,C.c1,C.c2
FROM
(select * from A where A.a2='xxx')A
LEFT OUTER JOIN B ON B.a1=A.a1
LEFT OUTER JOIN C ON C.a1=A.a1
这样的结果不一致的,楼主的SQL语句A表所有的数据都会显示,而你这个SQL语句只有符合where条件的数据才会显示出来