大数据量查询视图应用

随手记录一下工作解决问题的方法
主表A数据量级百万以上 子表B中有三个非主键字段需要跟主表A中三个非主键字段进行比对，任意一个字段比对成功即可，子表C中主键ID关联子表B中一个字段，一开始的时候想法很简单三个字段用or连接一下查询就OK了，如下：

select a.*, 
from A a 
left outer join B b on (( b.字段1 = a.字段1 and length(b.字段1)>6 ) 
or ( b.字段2 = a.字段2 and length(b.字段2)>6) 
or (b.字段3 = a.字段3 and length(b.字段3)>6 )) 
left outer join C c on c.id = b.cc_id
where to_char(to_char(df.declare_time,'yyyy-mm-dd')::timestamp + '365 day', 'yyyy-mm-dd') >= to_char(now()::timestamp,'yyyy-mm-dd')

实际去库里查询了一下，发现用时真的很恐怖，这种毫无优化的循环嵌套算法，达到了600多秒，后来多方查证之后，决定使用视图方式进行数据的查询，如下：

>  SELECT A表字段逐个写出    FROM ((A a
>      LEFT JOIN B b ON ((((b.字段1)::text = (a.字段1)::text) AND (length((b.字段1)::text) > 6))))
>      LEFT JOIN C c ON (((c.id)::text = (b.cc_id)::text)))   WHERE ((length((b.字段1)::text) > 6) AND (to_char(((to_char(c.declare_time,
> 'yyyy-mm-dd'::text))::timestamp without time zone + '365
> days'::interval), 'yyyy-mm-dd'::text) >= to_char((now())::timestamp
> without time zone, 'yyyy-mm-dd'::text))) UNION  SELECT A表字段逐个写出   
> FROM ((A a
>      LEFT JOIN B b ON ((((b.字段2)::text = (a.字段2)::text) AND (length((b.字段2)::text) > 6))))
>      LEFT JOIN C c ON (((c.id)::text = (b.cc_id)::text)))   WHERE ((length((b.字段2)::text) > 6) AND (to_char(((to_char(c.declare_time,
> 'yyyy-mm-dd'::text))::timestamp without time zone + '365
> days'::interval), 'yyyy-mm-dd'::text) >= to_char((now())::timestamp
> without time zone, 'yyyy-mm-dd'::text))) UNION  SELECT A表字段逐个写出   
> FROM ((A a
>      LEFT JOIN B b ON ((((b.字段3)::text = (a.字段3)::text) AND (length((b.字段3)::text) > 6))))
>      LEFT JOIN C c ON (((c.id)::text = (b.cc_id)::text)))   WHERE ((length((b.字段3)::text) > 6) AND (to_char(((to_char(c.declare_time,
> 'yyyy-mm-dd'::text))::timestamp without time zone + '365
> days'::interval), 'yyyy-mm-dd'::text) >= to_char((now())::timestamp
> without time zone, 'yyyy-mm-dd'::text)))

视图创建使用SQL，如下：

> CREATE VIEW view_test as select a.* from A a  left outer join B b on
> (b.字段1 = a.字段1 and length(b.字段1)>6) left outer join C c on c.id =
> b.cc_id where length(b.字段1)>6 and
> to_char(to_char(c.declare_time,'yyyy-mm-dd')::timestamp + '365 day',
> 'yyyy-mm-dd') >= (to_char(now()::timestamp,'yyyy-mm-dd'))

使用一个字段条件创建后，使用UNION连接了剩余两个字段的语句，应用视图后查询用时仅需1.8s,但是在项目Mapper映射中使用，要稍稍慢一些，不过比之前600s直接中断服务连接要强的多。

总结一下：
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

建表时字段类型选择：
A：尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
B：VARCHAR的长度只分配真正需要的空间
C：使用枚举或整数代替字符串类型
D：尽量使用TIMESTAMP而非DATETIME
E：单表不要有太多字段，建议在20以内
F：避免使用NULL字段，很难查询优化且占用额外索引空间