翻译 那些降低数据库性能的 Rails 风格 (Common Rails Idioms that Kill Database Performance)

rennyallen · 2017年01月09日 · 最后由 rennyallen 回复于 2017年01月10日 · 8071 次阅读

前言

关于数据库查询,让我们来看一看那些让你的程序停滞不前的罪魁祸首。

我还记得我第一次看到 Rails 的 ActiveRecord,那是一次启发。那是 2005 年的时候,当时我在给一个 PHP 程序写 SQL 语句。突然间,写数据库从单调繁杂的零星工作变得简单且有趣了。

...然后我就开始关注到性能 (performance) 的问题。

ActiveRecord 它本身并不慢。我停止把注意力花在那些实际正在运行的查询上。结果是,当数据量变得庞大后,在 rails 的增删查改操作中一些最符合语言习惯的数据库查询就十分低效。

这篇文章我们将讨论这其中的三个罪魁祸首。但首先,让我们先聊聊怎么知道你的数据库查询是否高效。

测量性能

如果你数据量足够小的话每个数据库查询都是高效的。所以为了真正地感知效率,我们需要以一个生产级别的数据库为基准。在我们的示例中,我们将使用一个有大约 22000 条记录的叫做 faults 的表。

我们会用到 postgres。在 postgres 中,衡量性能的方法是使用 explain。比如:

# explain (analyze) select * from faults where id = 1;
                                     QUERY PLAN
--------------------------------------------------------------------------------------------------
 Index Scan using faults_pkey on faults  (cost=0.29..8.30 rows=1 width=1855) (actual time=0.556..0.556 rows=0 loops=1)
   Index Cond: (id = 1)
 Total runtime: 0.626 ms

这显示出了执行这次查询的预计花费 (cost=0.29..8.30 rows=1 width=1855) 和执行的实际时间 (actual time=0.556..0.556 rows=0 loops=1)。

如果你想要一个更易读的格式,你可以让 postgres 以 YAML 样式 打印出来。

# explain (analyze, format yaml) select * from faults where id = 1;
              QUERY PLAN
--------------------------------------
 - Plan:                             +
     Node Type: "Index Scan"         +
     Scan Direction: "Forward"       +
     Index Name: "faults_pkey"       +
     Relation Name: "faults"         +
     Alias: "faults"                 +
     Startup Cost: 0.29              +
     Total Cost: 8.30                +
     Plan Rows: 1                    +
     Plan Width: 1855                +
     Actual Startup Time: 0.008      +
     Actual Total Time: 0.008        +
     Actual Rows: 0                  +
     Actual Loops: 1                 +
     Index Cond: "(id = 1)"          +
     Rows Removed by Index Recheck: 0+
   Triggers:                         +
   Total Runtime: 0.036
(1 row)

现在我们只需要关注 "Plan Rows" 和 "Actual Rows"

  • Plan Rows 在最坏的情况下,数据库需要循环多少行来响应你的查询
  • Actual Rows 当它执行这次查询时,数据库实际循环了多少行?

如果 "Plan Rows" 是 1,就像上边这样,那么这次查询可能是高效的。如果 "Plan Rows" 等于这个数据库的行数,那么意味着这次查询将会做一个“全表扫描”,并不够好。

既然你知道了怎样来测量查询的效率,那我们来看一些常规的 rails 语句,看它们是怎么运行的。

计数 (Counting)

在 Rails views 中经常看到这样的代码:

Total Faults <%= Fault.count %>

对应的 SQL 为:

select count(*) from faults;

让我们把它放到explain里看看会发生什么。

# explain (analyze, format yaml) select count(*) from faults;
              QUERY PLAN
--------------------------------------
 - Plan:                             +
     Node Type: "Aggregate"          +
     Strategy: "Plain"               +
     Startup Cost: 1840.31           +
     Total Cost: 1840.32             +
     Plan Rows: 1                    +
     Plan Width: 0                   +
     Actual Startup Time: 24.477     +
     Actual Total Time: 24.477       +
     Actual Rows: 1                  +
     Actual Loops: 1                 +
     Plans:                          +
       - Node Type: "Seq Scan"       +
         Parent Relationship: "Outer"+
         Relation Name: "faults"     +
         Alias: "faults"             +
         Startup Cost: 0.00          +
         Total Cost: 1784.65         +
         Plan Rows: 22265            +
         Plan Width: 0               +
         Actual Startup Time: 0.311  +
         Actual Total Time: 22.839   +
         Actual Rows: 22265          +
         Actual Loops: 1             +
   Triggers:                         +
   Total Runtime: 24.555
(1 row)

我们的示例 count 查询循环了 22265 行 — 整个表!在 postgres 中,counts 总是循环整个数据集。 你可以通过加上where条件来减少数据集的大小。取决于你的要求,你可以减到足够小直到效率可以接受。

另外一个解决此问题的方法是把你的 count 值缓存起来。你可以这样来做:

belongs_to :project, :counter_cache => true

另外,当去检查这次查询是否返回任何数据时,用Users.exists?而不是Users.count>0。这样查询的结果更加高效。

排序 (Sorting)

几乎每个程序都至少有一个 index 页面,你从数据库里面读取了最新的 20 条记录然后展示出来。怎样做更简单?

读数据的代码大概像这样:

@faults = Fault.order(created_at: :desc)

对应的 sql 为:

select * from faults order by created_at desc;

那么我们来分析一下:

# explain (analyze, format yaml) select * from faults order by created_at desc;
              QUERY PLAN
--------------------------------------
 - Plan:                             +
     Node Type: "Sort"               +
     Startup Cost: 39162.46          +
     Total Cost: 39218.12            +
     Plan Rows: 22265                +
     Plan Width: 1855                +
     Actual Startup Time: 75.928     +
     Actual Total Time: 86.460       +
     Actual Rows: 22265              +
     Actual Loops: 1                 +
     Sort Key:                       +
       - "created_at"                +
     Sort Method: "external merge"   +
     Sort Space Used: 10752          +
     Sort Space Type: "Disk"         +
     Plans:                          +
       - Node Type: "Seq Scan"       +
         Parent Relationship: "Outer"+
         Relation Name: "faults"     +
         Alias: "faults"             +
         Startup Cost: 0.00          +
         Total Cost: 1784.65         +
         Plan Rows: 22265            +
         Plan Width: 1855            +
         Actual Startup Time: 0.004  +
         Actual Total Time: 4.653    +
         Actual Rows: 22265          +
         Actual Loops: 1             +
   Triggers:                         +
   Total Runtime: 102.288
(1 row)

这里我们可以看到,每次你做这个查询时数据库会对所有的 22265 行进行排序。这样可不好。

默认情况下,SQL 中的每个order_by语句都会实时地对数据集排序,没有缓存。

解决方法是使用索引。像这种简单的情况,加一个 sorted index 到 created_at column 中将会大大提高查询速度。

在你的 Rails migration 中你可以:

class AddIndexToFaultCreatedAt < ActiveRecord::Migration
  def change
    add_index(:faults, :created_at)
  end
end

这会运行以下的 SQL:

CREATE INDEX index_faults_on_created_at ON faults USING btree (created_at);

这里最后的created_at指的是排列顺序,默认是升序。

现在我们再运行一下排序的查询,我们可以看到不再包含一个排序的步骤了,只是简单地从 index 里读取已经排序好的数据。

# explain (analyze, format yaml) select * from faults order by created_at desc;
                  QUERY PLAN
----------------------------------------------
 - Plan:                                     +
     Node Type: "Index Scan"                 +
     Scan Direction: "Backward"              +
     Index Name: "index_faults_on_created_at"+
     Relation Name: "faults"                 +
     Alias: "faults"                         +
     Startup Cost: 0.29                      +
     Total Cost: 5288.04                     +
     Plan Rows: 22265                        +
     Plan Width: 1855                        +
     Actual Startup Time: 0.023              +
     Actual Total Time: 8.778                +
     Actual Rows: 22265                      +
     Actual Loops: 1                         +
   Triggers:                                 +
   Total Runtime: 10.080
(1 row)

如果你要依据多个 columns 排序,你需要创建一个由多个 columns 排序的 index。在 Rails migration 中:

add_index(:faults, [:priority, :created_at], order: {priority: :asc, created_at: :desc)

当你开始做更复杂的查询时,通过 explain 来执行它们是一个好办法,要趁早并且经常这样。

Limits and Offsets

我们多半不会把数据库中的所有数据都放到一个 index 页面里展示。我们用的是 paginate,一次只显示 10, 30 或者 50 条。实现这个的最常规的方法是用 limit 和 offset:

Fault.limit(10).offset(100)

对应的 SQL 为:

select * from faults limit 10 offset 100;

现在如果我们运行 explain,可以看到一些奇怪的东西。扫描的行数是 110,等于 limit 加上 offset。

# explain (analyze, format yaml) select * from faults limit 10 offset 100;
              QUERY PLAN
--------------------------------------
 - Plan:                             +
     Node Type: "Limit"              +
     ...
     Plans:                          +
       - Node Type: "Seq Scan"       +
         Actual Rows: 110            +
         ...

如果你把 offset 改成 10000 你会看到扫描的行数跳到了 10010,这个查询会变慢 64 倍。

# explain (analyze, format yaml) select * from faults limit 10 offset 10000;
              QUERY PLAN
--------------------------------------
 - Plan:                             +
     Node Type: "Limit"              +
     ...
     Plans:                          +
       - Node Type: "Seq Scan"       +
         Actual Rows: 10010          +
         ...

这就可以得出一个不好的结论:当分页的时候,后面的页面要比靠前的页面加载得慢。假设一个页面有 100 条记录 (像上面这个示例一样),那么第 100 页将比第一页慢 13 倍。

那我们该怎么办?

老实说,我还没有找到一个完美的解决方案。首先我想的是减小数据量,我就不用开始时分 100 页或者 1000 页了。

如果你不能减少数据集,最好的方法可能是用where语句替换掉offset/limit

# You could use a date range
Fault.where("created_at > ? and created_at < ?", 100.days.ago, 101.days.ago)

# ...or even an id range
Fault.where("id > ? and id < ?", 100, 200)

结论

我希望这篇文章能够说服你,真的应该利用好 postgres 的 explain 这个功能来查找你数据库查询中潜在的性能问题。即使是最简单的查询也会导致重大的性能问题,所以这值得去检查。:)

原文链接

delicious 👍

@oth So rapid reply!

某些地方翻译的还不够灵性,不过新人的分享精神值得称赞,值得我们大多数人学习。

已点赞。

顺便有个疑问:

Fault.count

楼主的前两个例子都是全表扫描(select type:all / index)的例子。

在实际生产环境并不多见吧。

大部分情况是 user.faults.count,如果 user_id 索引的 cardinality 比较高的话,速度还是相当快的。

这和 Rails 有半毛钱关系,数据库都这样的。

MongoDB 里面里面 count 全表很快的,因为有额外存储一个 counter,但有条件的时候,和 MySQL、PostgreSQL 的问题一样

@hemengzhi88 确实有的地方翻译我很纠结,有点生硬。还是英文原文比较 nice.

@xiaoronglv 对,我想作者举这个例子主要是为了展示 postgre 的 explain 这个功能吧

需要 登录 后方可回复, 如果你还没有账号请 注册新账号