如果想知道你的数据库查询为啥变得越来越慢了， 那没有啥比 postgres 的 EXPLAIN 更好使的了。
其实也没啥神秘的。 就是让 postgres 告诉咱们，它是怎么去执行这个查询的。你甚至可以进行实际查询然后比较实际的和预期性能差别。
Sound familiar?
也许你已经见识过 EXPLAIN. 早在 Rails 3.2 开始， Rails 会自动 **EXPLAIN 那些耗时大于 500ms 的查询。
但问题是，其输出的结果有些隐秘。 举个例子，这是从 rails development blog 拿来的:
1
2
3
4
5
6
7
8
9
10
11
12
|
% User.where(:id => 1).joins(:posts).explain
EXPLAIN for: SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id" WHERE "users"."id" = 1
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..37.24 rows=8 width=0)
Join Filter: (posts.user_id = users.id)
-> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=4)
Index Cond: (id = 1)
-> Seq Scan on posts (cost=0.00..28.88 rows=8 width=4)
Filter: (posts.user_id = 1)
(6 rows)
|
所以， 这是什么鬼？
在这篇文章我们将会分析类似这样的输出结果，及其给我们的 Ruby Web 开发所带来的影响。
The syntax
如果正在使用的是 Rails, 那么可以在任意 ActiveRecord 查询后面加上 .explain。
1
2
3
4
5
6
7
8
|
> User.where(id: 1).explain
User Load (10.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 [["id", 1]]
=> EXPLAIN for: SELECT "users".* FROM "users" WHERE "users"."id" = $1 [["id", 1]]
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=812)
Index Cond: (id = 1)
(2 rows)
|
虽然，EXPLAIN 方法使用起来很方便， 但是， 它并没有给你一些我们可以在 postgres 中可以用到的高级选项。
要在 postgres 中直接使用 EXPLAIN， 首先用 psql -d yourdb 进入到你的数据库中，然后再执行下面这条语句:
1
2
3
4
5
6
|
EXPLAIN SELECT * FROM users WHERE id=1;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=812)
Index Cond: (id = 1)
(2 rows)
|
这样我们就能得到一些关于 postgres 如何执行查询的信息，包括最少需要做多少工作来完成这个查询。
只用 EXPLAIN 的话并不会真正的执行这条语句，想要真正执行语句， 并给出预期与实际运行结果的对比则需要用 EXPLAIN ANALYZE
1
2
3
4
5
6
|
EXPLAIN ANALYZE SELECT * FROM users WHERE id=1;
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=812) (actual time=0.043..0.044 rows=1 loops=1)
Index Cond: (id = 1)
Total runtime: 0.117 ms
|
Interpreting the output
Postgres 很聪明的呢，他可以找到最高效的方式来执行你的查询。换句话说，他会生成一个「查询计划」，然后 explain只是负责把这个计划输出出来。
考虑以下情况:
1
2
3
4
5
6
7
8
|
# explain select * from users order by created_at limit 10;
QUERY PLAN
-------------------------------------------------------------------------
Limit (cost=892.98..893.01 rows=10 width=812)
-> Sort (cost=892.98..919.16 rows=104 width=812)
Sort Key: created_at
-> Seq Scan on users (cost=0.00..666.71 rows=104 width=812)
(4 rows)
|
这个输出由两部分组成:
- 「节点列表」(node list) 描述了执行该查询时候所发生的一系列动作
- 性能预估，描述了这个列表中的每个动作的耗时。
The node list
去掉所有的性能评估以后， 也就只剩下节点列表了，如下所示:
1
2
3
|
Limit
-> Sort (Sort Key: created_at)
-> Seq Scan on users
|
这有点像是一个 Postgres 写的「程序」用来执行查询操作。 其中包含了三个动作， “limit”, “sort” 和 “seq scan”。 子节点中的输出结果会被向上传送到父节点。 用 Ruby 的话来说就是:
1
|
all_users.sort(:created_at).limit(10)
|
Postgres 会在查询计划中使用许多不同的动作。 但你并不需要掌握所有的这些动作的含义，记住下面这几个就好了:
- Index Scan: 通过索引来获取记录。 类似在 Ruby 的 Hash 中查找某一项。
- Seq Scan: 通过遍历结果集来获取记录。
- Filter: 从结果集中选择那些匹配某条件的记录。
- Sort: 对结果集排序
- Aggregate: 当执行像 count, max, min 这类操作时候会用到
- Bitmap Heap Scan: Uses a bitmap to represent matching records. Operations like and-ing and or-ing can sometimes be done more easily to the bitmap than to the actual records.
https://github.com/digoal/blog/blob/master/201702/20170221_02.md
节点列表中， 每个节点都有一组性能评估。一般长这样子的:
1
|
Limit (cost=892.98..893.01 rows=10 width=812)
|
这些数字代表意思:
- Cost: 该动作的执行成本。它没有具体的单位， 只能和其他同样是 Cost 的数字进行对比
- Rows: 执行该操作一共需要遍历多少行记录。
- Width: 每一行大概有多少字节。
最常用到的参数应该是 Rows。它可以清楚的让我们知道查询的性能。 如果这个值等于 1， 那么该查询性能杠杠的。 如果发现这个值等于表中记录的总数，那么这个查询性能就会随着表的增大变的越来越慢了。
使用 EXPLAIN ANALYZE 时， 会真正的去执行查询，然后就会出现两组数字。前一组是像上面的预估值，后一组则是实际值。
1
2
3
4
5
6
7
8
9
10
|
# explain analyze select * from users order by created_at limit 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Limit (cost=892.98..893.01 rows=10 width=812) (actual time=22.443..22.446 rows=10 loops=1)
-> Sort (cost=892.98..919.16 rows=10471 width=812) (actual time=22.441..22.443 rows=10 loops=1)
Sort Key: created_at
Sort Method: top-N heapsort Memory: 31kB
-> Seq Scan on users (cost=0.00..666.71 rows=10471 width=812) (actual time=0.203..15.221 rows=10472 loops=1)
Total runtime: 22.519 ms
(6 rows)
|
- Actual time: 执行该动作耗费的时长(ms)
- Rows: 执行该操作实际遍历了多少行记录。
- Loops: 有时候某个动作会发生不止一次， 此时， loops 会大于 1
More verbose output
默认情况下 EXPLAIN 给的是一个比较精简的版本。其实， 你可以让从他那得到更多更详细的信息。甚至可以让他的输出信息格式化为 JSON 或者 YAML.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
# EXPLAIN (ANALYZE, FORMAT YAML) select * from users order by created_at limit 10;
QUERY PLAN
------------------------------------------
- Plan: +
Node Type: "Limit" +
Startup Cost: 892.98 +
Total Cost: 893.01 +
Plan Rows: 10 +
Plan Width: 812 +
Actual Startup Time: 12.945 +
Actual Total Time: 12.947 +
Actual Rows: 10 +
Actual Loops: 1 +
Plans: +
- Node Type: "Sort" +
Parent Relationship: "Outer" +
Startup Cost: 892.98 +
Total Cost: 919.16 +
Plan Rows: 10471 +
Plan Width: 812 +
Actual Startup Time: 12.944 +
Actual Total Time: 12.946 +
Actual Rows: 10 +
Actual Loops: 1 +
Sort Key: +
- "created_at" +
Sort Method: "top-N heapsort" +
Sort Space Used: 31 +
Sort Space Type: "Memory" +
Plans: +
- Node Type: "Seq Scan" +
Parent Relationship: "Outer"+
Relation Name: "users" +
Alias: "users" +
Startup Cost: 0.00 +
Total Cost: 666.71 +
Plan Rows: 10471 +
Plan Width: 812 +
Actual Startup Time: 0.008 +
Actual Total Time: 5.823 +
Actual Rows: 10472 +
Actual Loops: 1 +
Triggers: +
Total Runtime: 13.001
(1 row)
|
还可以这样，EXPLAIN (ANALYZE, VERBOSE, FORMAT YAML) SELECT ...
Explain 会生成一大堆的输出。 尤其当查询比较复杂的时候，更是难以解析。
有一些免费的工具可以帮上忙。他们会帮助解析并生成一些漂亮的图表给你。甚至会给出一些标记指出潜在的性能问题。比如这个
Examples
把上面提到的汇总一起举个例子。你是否发现某一些 Rails 的通用做法会生成一些性能不佳的数据库查询?
The counting conundrum
下面的用法应该是非常常见的吧:
1
|
Total Faults <%= Fault.count %>
|
对应生成的 SQL 语句如下:
1
|
select count(*) from faults;
|
给 EXPLAIN 一下，看看发生了些什么事情。
1
2
3
4
5
6
7
|
# explain select count(*) from faults;
QUERY PLAN
-------------------------------------------------------------------
Aggregate (cost=1840.31..1840.32 rows=1 width=0)
-> Seq Scan on faults (cost=0.00..1784.65 rows=22265 width=0)
(2 rows)
|
Woah! 简单的一个 count 查询，竟然遍历了 22,265 行， 也就是整个表。 在 Postgres , count 都是全表查的。
The sorting scandal
下面这个也常见吧， 根据某个字段进行排序。 比如:
1
|
Fault.order("created_at desc").limit(10)
|
其实你只想要 10 条记录而已。 但， 为了得到这 10 条记录， 你需要对整个表进行排序。 从下面可以看出 Sort 操作遍历了 22,265 行记录.
1
2
3
4
5
6
7
|
# explain select * from faults order by created_at limit 10;
QUERY PLAN
----------------------------------------------------------------------------
Limit (cost=2265.79..2265.81 rows=10 width=1855)
-> Sort (cost=2265.79..2321.45 rows=22265 width=1855)
Sort Key: created_at
-> Seq Scan on faults (cost=0.00..1784.65 rows=22265 width=1855)
|
通过添加索引，我们就可以把 **Sort 这个操作换成更高效的 Index Scan
1
2
3
4
5
6
7
8
9
|
# CREATE INDEX index_faults_on_created_at ON faults USING btree (created_at);
CREATE INDEX
# explain select * from faults order by created_at limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Limit (cost=0.29..2.66 rows=10 width=1855)
-> Index Scan using index_faults_on_created_at on faults (cost=0.29..5288.04 rows=22265 width=1855)
(2 rows)
|
Conclusion
强烈建议多一些使用 EXPLAIN 这个命令， 这样， 当数据越来越大，时候不至于措手无策。
译自: A Rubyist’s Guide to Postgresql’s Explain
