【译】如何让数据库工效翻十倍
source link: https://my.oschina.net/edgedb/blog/5425890
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
【译】如何让数据库工效翻十倍 - EdgeDB Inc. - OSCHINA - 中文开源技术交流社区
本篇译文是二合一，原文作者是 Yury Selivanov（@1st1）和 Elvis Pranskevichus（@elprans），原文《A Path to a 10x Database》发布于 2019 年 6 月 18 日，后半段摘自发布于 2019 年 4 月 11 日的《EdgeDB 1.0 Alpha 1》，因时间较久，一些内容已不适用，因此我把有用的部分摘抄出来，译作本文。原文没有对 10x 做出解释，10x 的一种说法来源于“优秀的工程师通常有 10 倍与常人的效率”，那么一个 10x 数据库就意味着，数据库的执行效率与使用者的工作效率将能“翻十倍”。今天是农历正月初一，祝大家虎年 10x！
我们最早是从一家有理想的外包公司起身，与其他许多人一样，在资源有限的现实中，平衡对质量的追求对我们来说也是一件很纠结的事情。
幸运的是，我们在这段时间里得以服务许多不同行业的项目，于是自然地积累下了一些内部框架，用以更快交付的同时，可以有更让人愉悦的过程和结果。而这些框架中的关键元素就是我们的数据库抽象集成，当时可以做到比如：
- 在 schema 层面定义数据权限规则，因此不需要写重复的查询语句。
- 通过 schema 自省和标注，大规模自动生成图形界面、API 接口以及带有完整校验功能的数据输入表单。
- 抽象出一些可重用的 schema 定义和查询，简单拼装就能实现很多不同的常见应用模式。
这些想法的成功，激发了我们进一步开发它们的动力，于是就有了现在的 EdgeDB。
EdgeDB 的使命是赋予用户更多操作数据的能力、灵活度与自信，无论是构建服务还是数据研究，亦或是 SRE 持续部署的工作。这听上去有些拗口，那我们看一下 EdgeDB 有哪些功能：
- 无需 ORM 或其他抽象层的繁重开销，即可与任何编程语言有效地接驳；
- 使用如 JSON 或 Apache Arrow 这样的常见序列化格式高效收发数据；
- 亦可通过如 REST 和 GraphQL 等常见应用层协议对接客户端；
- 支持精心设计便于审计的安全策略，集中定义数据与 schema 的安全性（2.0 规划中，译注）；
- 内置 schema migration 与历史记录。
- 大型多功能标准库，包含基础类型、常用函数和操作符。
为了实现这些功能，我们设计出了一款更强大的现代化查询语言以替换 SQL 语言，更新了关系数据库中 schema 的设计方式，并编写出了一整套在开发和生产环境中高效操作数据库所必须的工具。
适用性是指，在特定环境下，特定用户完成特定目标时的有效性、工作效率和满意程度。
——ISO 9241（人机交互中的工效学）
EdgeDB 的设计遵循以下理念：
- 符合人类工效学（译注：也就是追求最佳工作效率）
操作 EdgeDB 的方方面面——如数据模型、EdgeQL——都应易学易懂，用户体验满分。
- 执行效率高
EdgeQL 的各项功能、不同编程语言的客户端以及各类工具的设计都应考虑到高效低延迟。
- 保证正确性
绝不因工效或效率而牺牲正确性。不符合逻辑的行为必须报错。
以上这些不仅适用于数据库核心及其查询语言的设计，也适用于用户在编程语言、操作系统及开发工作流中与数据库的交互。
口说无凭，下面我们一起尝试用不同的框架，实现相同的一个小 App，以观察 EdgeDB 在开发效率、执行效率和正确性方面，如何做到工效翻十倍的。译者加。
假设我们想要用 Python 做一个豆瓣电影，功能包括：
- 电影（Movie）页面展示电影名、发行年份、简介、海报图、导演和演员列表、总评分以及影评列表，每条影评（Review）又包括影评内容和评分，以及提交影评的用户的名字和头像。
- 人物（Person）页面展示姓名、照片、生平，以及导演或参演过得电影列表，其中每部电影又包括海报图、电影名、发行年份和总评分。
- 用户（User）页面展示名字和头像，以及最近 10 条影评，每条影评又包括影评内容和评分，以及所评价电影的名称、海报图和总评分。
豆瓣最早可能也就是这样吧，数据结构如下图所示：
用于选型比较的技术栈有：Django + PostgreSQL、SQLAlchemy + PostgreSQL、手写 SQL + PostgreSQL、MongoDB 和 EdgeDB。
可以在这里下载本文用到的全部代码。
Django
Django 作为一款使用广泛的 Python 框架，自带一个简易 ORM，但足以满足我们的 schema 要求。写查询也相对直接，基本上就是 list 和 dict 等常规 Python 代码，加上一点查询构建：
def render(self, movie):
result = {}
if movie:
directors = [rel.person for rel in
movie.directors_rel.order_by(
'list_order', 'person__last_name'
).select_related('person')]
cast = [rel.person for rel in
movie.cast_rel.order_by(
'list_order', 'person__last_name'
).select_related('person')]
reviews = movie.reviews \
.order_by('-creation_time').select_related('author')
result = {
'id': movie.id,
'image': movie.image,
'title': movie.title,
'year': movie.year,
'description': movie.description,
'directors': [{
'id': person.id,
'full_name': person.get_full_name(),
'image': person.image,
} for person in directors],
'cast': [{
'id': person.id,
'full_name': person.get_full_name(),
'image': person.image,
} for person in cast],
'avg_rating': movie.get_avg_rating(),
'reviews': [{
'id': review.id,
'body': review.body,
'rating': review.rating,
'author': {
'id': review.author.id,
'name': review.author.name,
'image': review.author.image,
},
} for review in reviews],
}
return result
值得注意的是，虽然类似 Django 这样的 REST 框架可以极大地简化数据抓取和响应生成，但这通常会带来巨大的性能开销。
SQLAlchemy
SQLAlchemy 是一款强大的 Python ORM，支持各种类型的关系型 schema 设计，允许对象关系的微调，或者指定不同的关系加载方法，以生成比默认 Django 更高效的查询语句。
这个特性也有额外开销——想要充分发挥 SQLAlchemy 的所有潜能，使用者需要面对一堆相当复杂的 API，如各种加载策略和预制查询等。SQLAlchemy 允许用户做底层优化，那用户就得同时精通 SQLAlchemy 和 SQL 本身，才能达到最佳执行效率。
def get_movie(sess, id):
# ...
NULLS_LAST = 2 ^ 64
def sort_key(rel):
if rel.list_order is None:
return (NULLS_LAST, rel.person_rel.last_name)
else:
return (rel.list_order, rel.person_rel.last_name)
baked_query = bakery(lambda sess: (
sess.query(m.Movie)
.options(
orm.subqueryload(m.Movie.directors_rel)
.joinedload(m.Directors.person_rel, innerjoin=True),
orm.subqueryload(m.Movie.cast_rel)
.joinedload(m.Cast.person_rel, innerjoin=True),
orm.subqueryload(m.Movie.reviews)
.joinedload(m.Review.author, innerjoin=True),
)
)
)
baked_query += lambda q: q.filter_by(id=sa.bindparam('id'))
movie = baked_query(sess).params(id=id).first()
directors = [rel.person_rel for rel in
sorted(movie.directors_rel, key=sort_key)]
cast = [rel.person_rel for rel in
sorted(movie.cast_rel, key=sort_key)]
result = {
'id': movie.id,
'image': movie.image,
'title': movie.title,
'year': movie.year,
'description': movie.description,
'avg_rating': float(movie.avg_rating),
'directors': [
{
'id': d.id,
'full_name': d.full_name,
'image': d.image,
} for d in directors
],
'cast': [
{
'id': c.id,
'full_name': c.full_name,
'image': c.image,
} for c in cast
],
'reviews': [
{
'id': r.id,
'body': r.body,
'rating': float(r.rating),
'author': {
'id': r.author.id,
'name': r.author.name,
'image': r.author.image,
}
} for r in sorted(movie.reviews,
key=lambda x: x.creation_time,
reverse=True)
]
}
return json.dumps(result)
MongoDB
MongoDB 是一个文档数据库，用户现在可以通过“聚合流水线”来执行一些不算简单的查询了，但对我们的 App 来说，相对于 Django、SQLAlchemy 甚至纯 SQL，用 MongoDB 来写查询还是很有挑战的。
MongoDB 的查询本质上就是一些底层数据的表达式，类似 SQL 数据库中查询计划输出的结果，因此很难说我们的查询到底是不是最优的。通常来看，MongoDB 的查询会较难调整或重构，而使用多个子查询语句来组合出一条查询语句是基本上不可能的。
def get_movie(db, id):
movie = db.movies.aggregate([
{
'$match': {
'_id': id
}
},
{
'$lookup': {
'from': 'people',
'localField': 'cast',
'foreignField': '_id',
'as': 'cast'
}
},
{
'$lookup': {
'from': 'people',
'localField': 'directors',
'foreignField': '_id',
'as': 'directors'
}
},
{
'$lookup': {
'from': 'reviews',
'foreignField': 'movie',
'localField': '_id',
'as': 'reviews'
}
},
{
'$unwind': {
'path': "$reviews",
'preserveNullAndEmptyArrays': True
}
},
{
'$lookup': {
'from': 'users',
'localField': 'reviews.author',
'foreignField': '_id',
'as': 'reviews.author'
}
},
{
'$sort': {"reviews.creation_time": -1},
},
{
'$group': {
'_id': "$_id",
'title': {'$first': "$title"},
'year': {'$first': "$year"},
'image': {'$first': "$image"},
'description': {'$first': "$description"},
'cast': {'$first': "$cast"},
'directors': {'$first': "$directors"},
'reviews': {'$push': "$reviews"}
}
},
{
'$project': {
'cast': { # 计算 `full_name` 会造成大约 5% 的额外开销，
# 但其他所有测试都这么做了，所以 mongodb 不冤，
# 正好看看它能否处理好这类查询。
'$map': {
'input': '$cast',
'as': 'c',
'in': {
'name': {
"$concat": [
"$$c.first_name",
" ",
{
'$cond': {
'if': {
'$eq': ['$$c.middle_name', '']
},
'then': '',
'else': {
"$concat": [
"$$c.middle_name", ' '
]
}
}
},
"$$c.last_name"
]
},
'image': '$$c.image',
'_id': '$$c._id',
}
}
},
'directors': { # 见“cast”的注释
'$map': {
'input': '$directors',
'as': 'c',
'in': {
'name': {
"$concat": [
"$$c.first_name",
" ",
{
'$cond': {
'if': {
'$eq': ['$$c.middle_name', '']
},
'then': '',
'else': {
"$concat": [
"$$c.middle_name", ' '
]
}
}
},
"$$c.last_name"
]
},
'image': '$$c.image',
'_id': '$$c._id',
}
}
},
'reviews': 1,
'image': 1,
'title': 1,
'year': 1,
'description': 1,
'avg_rating': {'$avg': '$reviews.rating'}
}
}
])
movie = list(movie)
result = bson.json_util.dumps(movie[0])
return result
手写 SQL
使用关系数据库时，手写 SQL 不失为一种十分高效的方式，因为你可以完全掌控数据的获取方式。但在这种情况下，数据库与客户端的选择变的尤其重要。比如说 PostgreSQL 从 11.0 起，支持聚合任意表达式的数组，进一步允许我们从一个深层嵌套的数据结构中，用一条查询语句就可以制造出最理想的返回结果（比如免除 JOIN 所带来的冗余数据）。然而遗憾的是，很多 PostgreSQL 的客户端（包括最流行的 Python 客户端——psycopg2）都没有办法直接解析这样的查询结果。
因此，我们将测试两种客户端：psycopg2 以及我们自己的 asyncpg。
psycopg2
对于 psycopg2，我们不得不把查询拆分成多条语句，这很像 Django 和 SQLAlchemy 在底层的实现。同样是用来渲染电影页面的代码：
def get_movie(conn, id):
with conn.cursor() as cur:
cur.execute('''
SELECT
movie.id,
movie.image,
movie.title,
movie.year,
movie.description,
movie.avg_rating
FROM
movies AS movie
WHERE
movie.id = %s;
''', [id])
movie_rows = cur.fetchall()
movie = movie_rows[0]
cur.execute('''
SELECT
person.id,
person.full_name,
person.image
FROM
directors
INNER JOIN persons AS person
ON (directors.person_id = person.id)
WHERE
directors.movie_id = %s
ORDER BY
directors.list_order NULLS LAST,
person.last_name
''', [id])
directors_rows = cur.fetchall()
cur.execute('''
SELECT
person.id,
person.full_name,
person.image
FROM
actors
INNER JOIN persons AS person
ON (actors.person_id = person.id)
WHERE
actors.movie_id = %s
ORDER BY
actors.list_order NULLS LAST,
person.last_name
''', [id])
cast_rows = cur.fetchall()
cur.execute('''
SELECT
review.id,
review.body,
review.rating,
author.id AS author_id,
author.name AS author_name,
author.image AS author_image
FROM
reviews AS review
INNER JOIN users AS author
ON (review.author_id = author.id)
WHERE
review.movie_id = %s
ORDER BY
review.creation_time DESC
''', [id])
reviews_rows = cur.fetchall()
return json.dumps({
'id': movie[0],
'image': movie[1],
'title': movie[2],
'year': movie[3],
'description': movie[4],
'avg_rating': str(movie[5]),
'directors': [
{
'id': d[0],
'full_name': d[1],
'image': d[2]
} for d in directors_rows
],
'cast': [
{
'id': c[0],
'full_name': c[1],
'image': c[2]
} for c in cast_rows
],
'reviews': [
{
'id': r[0],
'body': r[1],
'rating': r[2],
'author': {
'id': r[3],
'name': r[4],
'image': r[5]
}
} for r in reviews_rows
]
})
用这种方法来获取数据并不复杂，并且性能也非常符合预期。但必须说明的是，上面的代码是有瑕疵的，因为在并发的情况下有可能发生数据争用。要避免这种情况，就需要在允许 REPEATABLE READ 的事务中来执行所有查询。这种情况同样适用于各类 ORM，比如在 Django 中，如果不作任何处理的话，Django 会默认使用“自动提交”模式，所以每个查询都有可能是用了不同的数据库快照。
asyncpg
对于 asyncpg，客户端和服务器都会使用二进制数据编码，因此可以处理任何类型的嵌套数据，所以我们可以用最理想的方式来获取数据：
async def get_movie(conn, id):
# 以下查询语句需要 PostgreSQL 11，并且只有 asyncpg 能处理其结果
movie = await conn.fetch('''
SELECT
movie.id,
movie.image,
movie.title,
movie.year,
movie.description,
movie.avg_rating,
(SELECT
COALESCE(array_agg(q.v), (ARRAY[])::record[])
FROM
(SELECT
ROW(
person.id,
person.full_name,
person.image
) AS v
FROM
directors
INNER JOIN persons AS person
ON (directors.person_id = person.id)
WHERE
directors.movie_id = movie.id
ORDER BY
directors.list_order NULLS LAST,
person.last_name
) AS q
) AS directors,
(SELECT
COALESCE(array_agg(q.v), (ARRAY[])::record[])
FROM
(SELECT
ROW(
person.id,
person.full_name,
person.image
) AS v
FROM
actors
INNER JOIN persons AS person
ON (actors.person_id = person.id)
WHERE
actors.movie_id = movie.id
ORDER BY
actors.list_order NULLS LAST,
person.last_name
) AS q
) AS actors,
(SELECT
COALESCE(array_agg(q.v), (ARRAY[])::record[])
FROM
(SELECT
ROW(
review.id,
review.body,
review.rating,
(SELECT
ROW(
author.id,
author.name,
author.image
)
FROM
users AS author
WHERE
review.author_id = author.id
)
) AS v
FROM
reviews AS review
WHERE
review.movie_id = movie.id
ORDER BY
review.creation_time DESC
) AS q
) AS reviews
FROM
movies AS movie
WHERE
id = $1;
''', id)
movie = movie[0]
return json.dumps({
'id': movie['id'],
'image': movie['image'],
'title': movie['title'],
'year': movie['year'],
'description': movie['description'],
'avg_rating': float(movie['avg_rating']),
'directors': [
{
'id': d[0],
'full_name': d[1],
'image': d[2],
} for d in movie['directors']
],
'cast': [
{
'id': c[0],
'full_name': c[1],
'image': c[2],
} for c in movie['actors']
],
'reviews': [
{
'id': r[0],
'body': r[1],
'rating': r[2],
'author': {
'id': r[3][0],
'name': r[3][1],
'image': r[3][2],
}
} for r in movie['reviews']
]
})
实际上这还挺不错的！我们用一条查询语句就获得了所有数据，根本不需要担心数据争用和数据库事务。但关键在于，老版本的 PostgreSQL 并不支持这样的查询，并且也不是所有的数据库客户端都能处理这样的查询结果。
EdgeDB
使用 EdgeDB 的第一步就是定义数据结构。下面的代码定义了两个对象类型：Review 和 Movie（完整 schema 定义在这里）。此处使用的是 schema 定义语言（SDL），我们在语言的可读性和表达力上花费了大量的精力：
type Review {
required property body -> str;
required property rating -> int64 {
constraint min_value(0);
constraint max_value(5);
}
required link author -> User;
required link movie -> Movie;
required property creation_time -> cal::local_datetime {
default := cal::to_local_datetime(datetime_current(), 'UTC')
}
}
type Movie {
required property title -> str;
required property year -> int64;
required property description -> str;
multi link directors -> Person;
multi link cast -> Person;
property avg_rating := math::mean(.<movie[IS Review].rating);
}
EdgeQL
EdgeQL 是 EdgeDB 最重要的查询语言，用一条语句就可以完成各种嵌套结构的查询，返回 JSON 字符串形式的结果，或是不同编程语言所适用的数据结构。
比如返回 JSON 字符串的查询：
def get_movie(conn, id):
return conn.query_single_json('''
SELECT Movie {
id,
image,
title,
year,
description,
avg_rating,
directors: {
id,
full_name,
image,
}
ORDER BY @list_order EMPTY LAST
THEN .last_name,
cast: {
id,
full_name,
image,
}
ORDER BY @list_order EMPTY LAST
THEN .last_name,
reviews := (
SELECT Movie.<movie[IS Review] {
id,
body,
rating,
author: {
id,
name,
image,
}
}
ORDER BY .creation_time DESC
),
}
FILTER .id = <uuid>$id
''', id=id)
或者返回 Python 对象结构，以便于在手动 JSON 序列化之前，从服务端对数据进行进一步的处理：
def get_movie(conn, id):
m = conn.query_single('''
... 跟上面一模一样的 EdgeQL 查询语句 ...
''', id=id)
return json.dumps({
'id': str(m.id),
'image': m.image,
'title': m.title,
'year': m.year,
'description': m.description,
'avg_rating': m.avg_rating,
'directors': [
{
'id': str(d.id),
'full_name': d.full_name,
'image': d.image,
} for d in m.directors
],
'cast': [
{
'id': str(c.id),
'full_name': c.full_name,
'image': c.image,
} for c in m.cast
],
'reviews': [
{
'id': str(r.id),
'body': r.body,
'rating': r.rating,
'author': {
'id': str(r.author.id),
'name': r.author.name,
'image': r.author.image,
}
} for r in m.reviews
]
})
GraphQL
最后，EdgeDB 还支持开箱即用的 GraphQL。虽然不如 EdgeQL 一样强大，但 GraphQL 可以直接使用在 SDL 中定义的复杂对象视图，间接支持计算属性和反向链接查询。
query movie($id: ID!) {
movie: GraphQLMovieDetails(filter: {id: {eq: $id}}) {
id
image
title
year
description
directors {
id
full_name
image
}
cast {
id
full_name
image
}
avg_rating
reviews(order: {creation_time: {dir: DESC}}) {
id
body
rating
author {
id
name
image
}
}
}
}
有了这些测试 App 的不同实现，我们就可以对比评测执行性能了。
我们使用 Python 的 NLTK 库生成了一份包含 10 万人物、10 万用户、约 60 万影评和超过 2.5 万电影的测试数据集，其中又有错综复杂的关联关系。姓名根据美国人口普查数据随机生成，而标题、文本、简介和影评的生成则是尽量模拟了现实生活中的真实语言，也照顾到了真实影评类应用常见的文本长度和大概的写作特点。数据集的大小特意设计为内存装得下，但又有足够的分量去测试出查询的质量。
我们使用了下面的配置来执行测试：
- 数据库跑在一台 12 核的 GCP 虚拟机上，有 16G 内存和一块 SSD 硬盘。
- 测试脚本跑在另外一台 8 核的 GCP 虚拟机上，有 12G 内存和一块 SSD 硬盘。
- 并发量设置为 24，这对应了 Python 测试脚本的 24 个独立操作系统进程，每个进程有一个独立的数据库连接；用 Go 语言写的测试脚本则有 24 个 goroutine 客户端。
- 每次测试都有 10 秒钟的热机时间，接着是 30 秒不间断的测试。
首先需要着重强调的是，尽管这些查询看起来普普通通，但实际上数据库需要高效完成许多任务，才能满足这些查询的效率。每种测试中，SQL 查询都要 JOIN 多张表，用聚合函数算出电影的总评分；而对于 MongoDB 的各个查询，我们使用了聚合框架来组合不同的查询结果。
测试结果的一些解读：
-
我们观察到，Django ORM 的瓶颈在于 CPU——低效率的 Python 代码太多了。不同于 SQLAlchemy，Django 缺少查询生成的微调机制，你甚至不能控制一个视图有多少次数据库查询。
加上 Django REST 框架之后变得更慢了（见完整报告）。
-
为了提高 SQLAlchemy 的性能，我们做了大量的调整和实验，其中最为重要的就是“预制”查询，和选一个最优的加载策略。如果不使用这些高级特性，SQLAlchemy 的性能也就跟 Django ORM 差不多。
-
MongoDB 性能差还挺让人吃惊的。一开始我们盲猜虽然聚合框架可能比较慢，但是按 ID 来索取文档至少应该很快。但打脸的是，对于等价的按 ID 索取操作，PostgreSQL 仍然比 MongoDB 快不少。另外，MongoDB 除了数据库 JOIN 比较慢之外，它的 Python 客户端也不怎么快——BSON 到 JSON 的转换消耗了大量 CPU 时间。
-
PostgreSQL 与 psycopg2 的配合表现相当不错，而它输给 asyncpg 的两个限制性因素为：一次调用需要执行多次查询，以及使用更慢的基于文本的数据编码方式。通过 psycopg2 我们可以确定，在 Python 中使用 ORM 能让请求变慢 5 至 10 倍。
-
EdgeDB 表现相当出色。相较于所有其他解决方案，EdgeDB 能用少得多的代码来完成建表和查询。
通过 HTTP 来使用 GraphQL 和 EdgeQL 的性能很好，即使 HTTP 带来了额外的开销。这意味着，EdgeDB 已经可以用于任何支持 HTTP 的编程语言了。
“EdgeDB repack”测试使用了阻塞式 IO 的 Python 客户端，查询返回 Python 对象形式的数据，再手动编码成 JSON；而“EdgeDB JSON asyncio”测试则是使用了非阻塞式的 Python asyncio 客户端，并让 EdgeDB 直接返回 JSON 字符串。
EdgeDB 的 Python 客户端经过了大量的优化，因此几乎不怎么占用 CPU 时间。阻塞式测试与 asyncio 测试的性能不分伯仲，但后者可以更高效地利用 CPU 资源，使用的内存却少很多。
最后，我们知道现在的 EdgeDB 还有一些不足之处，会在将来的版本中修复，到时候性能应该会有进一步的提升。
-
PostgreSQL 与 asyncpg 是最佳性能组合，给未来版本的 EdgeDB 提供了追赶的目标。但是要知道，asyncpg 是世界上最快的 PostgreSQL 驱动之一，并且日常工作中我们一般不会写出如测试中那般的查询语句。
通过我们的测评可以看出，即使相较于使用世界上顶尖的 RDBMS、榜首的驱动且人工优化过的 SQL，EdgeDB 先进的查询语言仍然可以为开发者提供十分具有竞争力的执行性能。
受限于简单的数据结构和查询语句，这篇文章中的示例并不能展现出 EdgeQL 的全部潜能，比如 EdgeQL 可以不费吹灰之力地增加嵌套深度，而这对于其他选项来说难度会显著增加。（与此同时，EdgeDB 还保证了正确性，并大幅降低了开发者的心智磨损，配合出色的性能表现，工作效率何止“翻十倍”。译者加。）
欢迎关注我们的官方网站、掘金专栏、知乎专栏和 OSCHINA 项目主页，了解更多资讯。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK