postgresql in 优化

 

原sql:

SELECT
    res_id_ori
FROM
    wk_sheet A,
    wk_page b
WHERE
    A .wk_sheet_id = b.wk_sheet_id
    AND b.res_id_ori IN ('919844276962074624','919856729196335104','926389611062652928','914850828240564224','914850835693834240','914850844854202368','914850876089176064','914850863539826688')
and a.status = -999 and a.scan_status = 3 ;

原sql执行计划:

Planning time: 0.145 ms
Execution time: 1313.189 ms

优化方案:创建联合索引

create index  idx_residori_sheetid on wk_page(res_id_ori,wk_sheet_id); 

sql执行计划:

EXPLAIN  (analyze,verbose,timing,costs,buffers) 
SELECT
    res_id_ori
FROM
    wk_sheet A,
    wk_page b
WHERE
    A .wk_sheet_id = b.wk_sheet_id
    AND b.res_id_ori IN ('919844276962074624','919856729196335104','926389611062652928','914850828240564224','914850835693834240','914850844854202368','914850876089176064','914850863539826688')
and a.status = -999 and a.scan_status = 3 ;

--优化效果

Planning time: 0.203 ms
Execution time: 123.095 ms

 

 

联合索引,res_id_ori 由于它in数量少,建议做联合索引前缀;要是res_id_ori太多也会走全表扫码。(wk_sheet_id做前缀比res_id_ori 做前缀慢个200ms,这个需要根据具体情况 具体分析)

 

总结:

1.把合适的业务字段作为主键字段,如果没有合适的就选择从1自增的非业务字段作为主键字段
2.通过explain 检验新上线sql性能以及诊断优化慢查询
3.关于关联查询的建议就是在每个表的关联字段上都要创建索引
4.全表扫描时, 需要在where条件上选择性高的单字段或组合字段上创建索引避免全表扫描
5.如何创建联合索引:
        如果其中有一个字段的选择性高达80%就只需在此字段上创建索引;如果没有选择性高过50%的字段则选择创建联合索引 如果创建联合索引,应该将哪个字段作为前缀索引,结合两个指标 :
        1、选择高频出现的字段作为联合索引前缀列 相比于上面的sql,有另一个sql :select * from t1 where col2=2;只以col2作为条件,这种情况下就将col2作为前缀索引,因为这样可以使得两个sql都会用到联合索引idx_col2_col3 
        2、如果没有情况1,则选择选择性最高的字段作为前缀;且都需通过 select count(distinct col1,col2...)/count(*) from t;来选择一种选择性最高的组合来创建联合索引

 

其他mysql sql优化也是一样的。

posted @ 2019-12-17 13:26  仰望星空的脚踏实地  阅读(792)  评论(0编辑  收藏