您当前的位置：首页 > 计算机 > 软件应用 > 数据库 > PostgreSQL

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

时间：04-25来源：作者：点击数：

问题描述

数据库用的是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.由于业务比较复杂，涉及到了三个表关联，后来和对接人探讨，最后决定在表中加一个关联字段，以达到少关联一个表的目的。

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
           province
        on
          retrival.province_code  = province.code
        
        where position('100000' in province.path) > 0
        and retrival.camera_name like '%%'
        AND retrival.start_time >= ''
        AND retrival.end_time <=  ''
        group by
        retrival.index_code,retrival.province_code,retrival.camera_name,province.name
        limit 25 index 0

3.对连接表关联到的字段加索引。

4.加完索引之后查询时间并没有得到改善，查询时间还是30s左右，加上分页并没有什么大的影响。

5.这时候我想着尝试不做表关联，将sql拆分，去掉group by ，将逻辑转换到代码中（以为这样会更快）

改完之后测试接口请求，发现在代码中效率也没有得到改善，反而更慢，因为放到代码中分页的优势就没有了。

6.由于项目现场催的比较紧，没有足够的时间去查阅相关资料，所以求助了带我的导师。

7.导师将字段索引分类改成了联合索引，并根据不同的查询条件修改对应的索引结构。这里pgsql 不只有btree一种索引结构。还有brin,gin等,初步了解gin可用到模糊查询（适用于非前缀模糊查询）的字段，brin 是比较，类似于>= <=等。

create extension pg_trgm; 
create extension btree_gin; （必须管理员权限执行）
create index indexname on tablename using gin (...)

create index indexname on tablename using brin (...) with (pages_per_range=1)

8.修改联合索引并调整字段顺序之查询时间在15s左右。explain 看sql 执行情况，发现还是group by 上面以及count 函数消耗的时间比较久。group by 后面跟了四个字段。这时候数据量已经达到700万。所以还是需要再优化。

9.导师提出了pgsql 的with 语句，此处，将group by 拆分，在with 中只根据count里面的字段进行统计，此处with 相当于将结果放入内存作为一个临时表。然后再从内存中关联其他需要的字段。这样优化之后分页查询执行时间只需要几百毫秒。

WITH tm AS (
	SELECT 
	retrival.index_code,
	COUNT
	( retrival.index_code ) retrivalNum,
	SUM ( end_time - start_time ) retrivalDuration
FROM
	retrival
	JOIN province ON retrival.province_code = province.code 
WHERE
	retrival.start_time >= '' 
	AND retrival.end_time <= ''
--	AND retrival.camera_name LIKE'%研%' 
	AND POSITION ( '100000' IN province.PATH ) > 0 
GROUP BY
	retrival.index_code
ORDER BY retrival.index_code
LIMIT 25 OFFSET 0

	)
	SELECT 
	distinct retrival.index_code,
	retrival.province_code ,
	retrival.camera_name,
	province."name" place,
	t.retrivalNum, t.retrivalDuration
	FROM  retrival
	JOIN tm t ON retrival.index_code = t.index_code
	JOIN  province ON retrival.province_code = province.code
	ORDER BY retrival.index_code
	LIMIT 25 OFFSET 0

explain 查看