记一次百万数据量查询sql 优化

问题描述

数据库用的是pgsql。sql 中用到了表连接，group by ，count以及sum 函数，现场测试的时候由于有定时任务在跑，所以数据库中一直有增量。当数据达到200万条时，页面加载时间非常慢，达到40s。

问题分析过程

本人开发小白一个，日常搬砖。之前没有接触过太大数据量的业务，只有面试的时候自己看过相关的sql优化知识，而实践还是头一次。
1.把查询慢的sql提出来,对where条件中的字段加上索引。由于之前开发用的是mysql,对pgsql 的一些特性不是很了解，所以默认的索引结构都是btree。

CREATE INDEX indexname ON tablename (code)
select
         
          count(retrival.id) retrivalNum,
          retrival.index_code,
          retrival.camera_name,
          province."name" place,
          sum(end_time-start_time) retrivalDuration,
          retrival.province_code
         
        from
           retrival
        left join
           resource
        on
          retrival.index_code = resource.index_code
        left join
          province
        on
          resource.province_code  = province.code    
         group by
        retrival.index_code,resource.province_code,retrival.camera_name

2.由于业务比较复杂，涉及到了三个表关联，后来和对接人探讨，最后决定在表中加一个关联字段，以达到少关联一个表的目的。