数据库 记录一下 dashboard 性能优化 (10s->1ms)

hooopo · 2021年04月12日 · 最后由 dsh0416 回复于 2021年04月16日 · 1192 次阅读
本帖已被管理员设置为精华贴

记录一次性能优化

Hackershare 后台的一个 Dashboard 页面,由于很多统计类的查询,响应越来越慢,差不多要十几秒打开。主要是有两个 50w 左右的数据表,count 非常慢,还有一部分原因就是这台 2c4g 的服务器部署了很多程序,CPU 经常被其他服务占用。

大概的数据量:

hackershare=# \dt+
                              List of relations
 Schema |            Name            | Type  | Owner  |  Size   | Description
--------+----------------------------+-------+--------+---------+-------------
 public | bookmarks                  | table | deploy | 1259 MB |
 public | clicks                     | table | deploy | 4360 kB |
 public | comments                   | table | deploy | 134 MB  |
 public | follows                    | table | deploy | 48 kB   |
 public | likes                      | table | deploy | 88 kB   |
 public | rss_sources                | table | deploy | 1280 kB |
 public | tag_subscriptions          | table | deploy | 88 kB   |
 public | taggings                   | table | deploy | 117 MB  |
 public | tags                       | table | deploy | 2200 kB |
 public | users                      | table | deploy | 2848 kB |

第一招,使用 union all,把多条 count 合并成一条语句:

select 'bookmark' as key, count(*) as count from bookmarks

UNION ALL

select 'comment' as key, count(*) as count from comments

UNION ALL

select 'click' as key, count(*) as count from clicks

UNION ALL 

others

返回结构大概这样:

   key    | count
----------+--------
 click    |  65103
 comment  | 421423
 bookmark | 465078

比之前的有提升,但效果不大...

第二招,使用 explain

# usage
# FastCount.new(User.all).call
# => 826
# FastCount.new(User.where("id > 200")).call
# => 665

class FastCount
  attr_reader :scope, :sql

  def initialize(scope)
    @scope = scope
    @sql = scope.to_sql
  end

  def call
    explain_sql = "explain (format json) #{sql}"
    result = ApplicationRecord.connection.execute(explain_sql)[0]["QUERY PLAN"]
    json = JSON.parse(result)
    json[0]["Plan"]["Plan Rows"].to_i
  end
end

看看效果:

explain (format json)  select * from bookmarks;
             QUERY PLAN
-------------------------------------
 [                                  +
   {                                +
     "Plan": {                      +
       "Node Type": "Seq Scan",     +
       "Parallel Aware": false,     +
       "Relation Name": "bookmarks",+
       "Alias": "bookmarks",        +
       "Startup Cost": 0.00,        +
       "Total Cost": 89629.30,      +
       "Plan Rows": 464730,         +
       "Plan Width": 1278           +
     }                              +
   }                                +
 ]
(1 row)

Time: 0.898 ms

不到 1 毫秒!!

另外,居然可以支持带过滤条件甚至带 JOIN 语句的 count,比如:

FastCount.new(User.where("id > 200")).call
  • 适用场景:分页和 dashboard 之类不需要数据绝对准确,但对性能还有一些要求的场景。
  • 缺点:并不能保证数据绝对准确,取决于你的 auto vacuum 设置,一般情况下如果你的表记录足够大,并且更新频繁,使用这种方案几乎误差范围都是很小的。

Full Code: https://github.com/hackershare/hackershare/pull/115/files

呐尼?!用 explain 来查询 😳

Rei 回复

快如⚡

统计的玩意加个缓存咯。。

居然还有这种操作。。。。。。

MySQL 有类似的,压测的时候用过,不过误差有点大,也没仔细找原因。

mysql> SELECT count(*) AS TOTALNUMBEROFTABLES
   -> FROM INFORMATION_SCHEMA.TABLES
   -> WHERE TABLE_SCHEMA = 'business';

Oracle 有个 Zone Map 的优化,会单独记录 count 之类的。

9 楼 已删除

这个是表记录条数,不能加条件的,实际就没什么用了。

高级高级

好奇,你咋不弄几个 counter 存统计数据。

每隔一小时计算一下 count 就可以了。

对于这种待条件的查询

FastCount.new(User.where("id > 200")).call

数据库的 explain 的估算值好像是根据直方图来估算的。如果直方图的粒度很粗,字段的值的分布也不均匀,估算值可能会有很大的误差。

xiaoronglv 回复

一个小时的误差还真赶不上这种,有了新的过滤条件还要维护,在索引里的一般误差不是很大,这种方案的前提是表大并且更新频繁,auto vacuum 就会运行的频率高,数据也就准了

不如加个 redis 吧

赞这个脑洞

counter cache 不好吗。

骚操作!学到了👍

数据库自己都被玩懵了

这个是真的骚,666

骚气的操作,之前 pg 统计各表数据我是用 pg_class 这个表来搞的,但是加不了条件查询,你这更骚

explain 的结果不准吧…… 本质上它是 PostgreSQL 为表存储的一套大概统计数据,用来决定 query plan 的。不过这脑洞确实大。

darkbaby123 回复

一定不完全准确,但只要你的 vacuum 执行正常,我观察下来,表越大误差越低。

之前用过一个 function 来把 explain 的估计 count 值拿出来。但还没想到这个 format json 的操作,这个可以拼接任意条件查询了,之前写法要是这么拼有注入风险。

jasl 将本帖设为了精华贴。 04月17日 03:44
需要 登录 后方可回复, 如果你还没有账号请 注册新账号