关于数据库查询，让我们来看一看那些让你的程序停滞不前的罪魁祸首。
我还记得我第一次看到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语句，看它们是怎么运行的。
在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。这样查询的结果更加高效。
几乎每个程序都至少有一个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语句都会实时地对数据集排序，没有缓存。
解决方法是使用index。像这种简单的情况，加一个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来执行它们是一个好办法，要趁早并且经常这样。
我们多半不会把数据库中的所有数据都放到一个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这个功能来查找你数据库查询中潜在的性能问题。即使是最简单的查询也会导致重大的性能问题，所以这值得去检查。:)
Scan the QR Code to add me on WeChat
