今天对一个视图进行优化,数据量有百万以上,一共关联了7张表,其中有一张表 JOIN 了两次(假设为 a 和 b ),且两次 ON 的判断条件不一样,这就导致了会用到不同的索引,而且在 WHERE 条件的时候,a 用到了3个条件判断,b用到了4个条件判断,这样导致了sql执行的时候消耗过多,时间变慢。调优后查询时间从30s变为了6s,达到预期要求。
假设上面说的jion两次的表为test表,看如下SQL:
SELECT DISTINCT
t.a,
t.b,
t.c,
a.name,
b.car
FROM t
JOIN test a ON a.id = t.id
AND a.name = t.name
AND a.city = t.city
JOIN test b ON b.password = t.password
AND b.car = t.car
WHERE
a.a1 = 'a1'
AND a.a2 = 'a2'
AND a.a3 <> 'a3'
AND b.b4 = 'Y'
AND b.b5 <> 'N'
AND b.b6 = 345
AND b.b7 = 0
此种情景,数据量有百万以上,假设test表有索引,分别为:
testI1(id,name,city),
testI2(password,car).
那么这个sql在查询时,虽然会用到这个索引,但是,由于WHERE条件判断项太多,
导致每一条数据在查询的时候都需要进行判断,这就增加了查询时间。
解决方案:
SELECT DISTINCT
t.a,
t.b,
r.c,
r.name,
r.car
FROM (
SELECT DISTINCT
t.a,
t.b,
t.c,
a.name,
b.car,
b.b4,
b.b5,
b.b6,
b.b7
FROM t
JOIN test a ON a.id = t.id
AND a.name = t.name
AND a.city = t.city
JOIN test b ON b.password = t.password
AND b.car = t.car
WHERE
a.a1 = 'a1'
AND a.a2 = 'a2'
AND a.a3 <> 'a3'
) r
WHERE
r.b4 = 'Y'
AND r.b5 <> 'N'
AND r.b6 = 345
AND r.b7 = 0
将WHERE条件进行分割,先将总体数据查出来,再进行WHERE过滤,这样提交效率。