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

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 查看

可以看到explain查出来的执行计划中都用到了哪些索引，pgsql 默认是按顺序扫描的，用下面的这个语句可以禁用顺序扫描强制使用索引，执行之后速度更快，控制在100ms左右

SET enable_seqscan = OFF;

总结

简单了解用到的pgsql 的索引结构

BRIN索引：存储关于存储在表的连续物理块范围内的值的摘要，可以使用BRIN索引的特定操作符根据索引策略而变化。对于具有线性排序顺序的数据类型，索引数据对应于每个块范围的列中值的最小值和最大值。这支持使用这些运算符的索引查询：

>=
<=
=

Gin索引是用来加快全文搜索的，适合做模糊查询和正则查询。

btree 和 brin 比较（pgsql 社区有相关演示及介绍）：

1.空间上：BRIN相比BTREE索引在空间占用上小很多（数据量千万级时有几千倍之差），在存储空间占用上具备巨大优势。对于数据仓库或者VLDB应用可以节省大量的存储成本。

2.查询上：对于等值的唯一查询，BTREE在性能上有显著优势。但在不同数据分布情况下，BTREE在性能上的领先优势差别非常大，从不到1倍到265倍。

3.在选择创建BRIN还是BTREE索引时，需要权衡性能和空间使用两方面的影响。根据数据量、数据分布和SQL选择最适合的索引类型。在性能相差不大的情况下，选择BRIN可能是更加经济的选择。总体来说，当实际匹配数据量较少时，BTREE索引更加适合；反之，BRIN更加适合。

4.由于BRIN索引的lossy特性，需要消耗较多的CPU时间用于精确匹配。

pgsql 的with

概念：WITH语句通常被称为通用表表达式（Common Table Expressions）或者CTEs。

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。

可以被看成是定义只在一个查询中存在的临时表。

使用：在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

扩展

对于更大数据量的数据还有没有其他更好的方案

查阅了一下网上资料，关于pgsql 大数据量的查询优化文章还是很多的。此处简单罗列，毕竟需要学习和了解的还很多。希望以后能够结合实际业务都能运用的上。

1.根据一定的条件进行分区（年月日等）

2.增加内存大小

3.根据不同的条件合理的时候相应的索引结构。

4.硬件上的优化支持