本文章承接上篇文章 , 主要介绍我在优化查询中数据库的选型,以及做了那些优化.
数据库选型
上篇文章已经提到,我要把聚合后的数据放入到另一个数据库中.自然而言需要重新进行技术选型,刚开始以为这部分会很快结束 , 但实际上这中间花了很长一段时间 , 主要原因是我准备工作不足 , 没有认真分析具体的查询场景 , 使得在挑选数据库时方向出现偏差 . 当时我最简单的想法是数据库要快一点,但其实这个快也有许多不同的场景,比如是查询快还是写入快,是复杂查询快还是高并发快一些,而以上每种快的场景都会对应于不同的数据库.
后来我分析了一整个月的查询请求, 统计各种条件使用的比例 ,并和产品经理沟通确认.梳理之后发现主要的查询场景如下:
-
大部分查询只检索近期数据
默认的时间查询条件是最近三天,这个时间内的订单更新和查询的频率都比较高
-
数据总量并不大
精简后总数据量大概是200gb , 其中分区后的热点数据只有10gb左右 .
-
有模糊查询 , 但无需分词需求
订单查询中需要使用模糊查询 , 但是字段是一些随机编码 , 因此无法使用字典进行分词
-
查询条件一带多
大部分查询只涉及七天内订单 , 有时也会搭配其他列一起检索 , 一些典型sql如下:
...where time between '2021-11-01' and '2021-11-08' and type in (1 , 2); ...where time between '2021-11-01' and '2021-11-08' and company_id in (1 , 2); ...where time between '2021-11-01' and '2021-11-08' and state =1 and belong =3 and city in ('北京' , '香港'); ...where time between '2021-11-01' and '2021-11-08' and phone like '%123321%' and car_no like 'changdy';查询输入框总计有五六十个 , 不同的业务部门也有不同的组合条件 , 这也就导致了联合索引无法覆盖所有情景 , 因此要求数据库在面对复杂查询时能够利用多个索引 .
-
所有的数据需要支持增删改 , 并且要求实时性
-
查询结果不需要进行聚合操作
虽然这个数据库主要是给业务人员进行订单查询 ,但其实也没有聚合的操作,也不算是标准的olap场景
最终的选型
经过再三斟酌,最终选择了PostgreSQL. 主要原因如下:
- 数据体量不大,关系型数据库其实也能完全承载
- 分区表性能优异.之前版本中pg需要使用继承表来达到分区的目的,但是在后续的版本中改成了声明式 ,性能优化比较多. 并且之后还陆续优化了剪枝功能.相比MySQL的分区表,更加完善.
- 针对模糊查询,pg可以使用插件pg_trgm达到走索引的目的,其原理非常适合作为车牌号,车架号这种没有语义的模糊查询场景.
- 索引功能比较强大:
- pg的索引种类更加丰富,比如gin索引,其结构与es的倒排索引比较相近 . 相比于B-Tree家族,gin索引的等值查询效率可能更高
- 针对复杂查询,pg能够同时利用多个索引,每个列上基本上只需要创建一次索引 .而同一情况下MySQL到了8.x才勉强有所缓解
- 或许大家见到过一些管理系统,前端一个文本输入框,但是查询时却能同时模糊查询几个不同的字段, 这个功能在pg下可以使用我上文提到的pg_trgm插件和gin索引来实现.同时pg可以使用
CONCURRENTLY关键字并发的创建索引,以及条件索引创建指定条件下的索引, 结合我们的业务场景.每天凌晨都会重新创建近三天的车牌号,手机号,订单号,车架号等常用模糊查询字段的合并索引.在前端页面上,用户只需要在一个输入框内输入信息,就可以在三天的时间范围内搜索多个字段的匹配.这个索引的体积比较小,匹配效率非常不错, 并且前端只有一个输入框,也提高了用户的操作效率
在选择数据库时前后问了很多朋友,甚至还考虑过clickhouse,Cassandra,也看了不少db-engines上的介绍, 最终还是回到了关系型数据库PostgreSQL上.这几年pg的势头的挺不错的，但仍旧与Oracle存在不小的差距。同时MySQL接近躺平，希望pg能够接接起开源关系型数据库的大旗.
在数据库产品越来越多的今天,选型时一定要认真分析好自己的场景,看下特性是否匹配, 避免出现类似于分布式数据库仍旧大表跨节点join的情况.
