翻译 那些降低数据库性能的 Rails 风格 (Common Rails Idioms that Kill Database Performance)
前言
关于数据库查询，让我们来看一看那些让你的程序停滞不前的罪魁祸首。
我还记得我第一次看到 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 这个功能来查找你数据库查询中潜在的性能问题。即使是最简单的查询也会导致重大的性能问题，所以这值得去检查。:)
已点赞。
顺便有个疑问：
Fault.count
楼主的前两个例子都是全表扫描（select type：all / index）的例子。
在实际生产环境并不多见吧。
大部分情况是 user.faults.count，如果 user_id 索引的 cardinality 比较高的话，速度还是相当快的。
这和 Rails 有半毛钱关系，数据库都这样的。
MongoDB 里面里面 count 全表很快的，因为有额外存储一个 counter，但有条件的时候，和 MySQL、PostgreSQL 的问题一样