删除ORM查询：搜索优化查询-DEV365 开发者社区

虽然TypeORM是一个很棒的库，但我有时会觉得它并不总是会产生最有效的查询。为了评估我的想法，我决定使用PostgreSQL的查询执行计划来测试不同的SQL查询以查看它们的性能。

让我们想象有一个很酷的应用程序像个人词典一样工作。每当有人遇到他们不知道的单词时，他们都可以将其保存在应用程序中，并将其及其含义，示例，注释等保存。这样，他们以后可以很容易地查找它。你猜怎么了？我将创建该应用！

设计数据模型以保留此应用程序所需的数据非常简单。我们只需要为此创建三张桌子：User，Vocabulary和Definition。

用户

| Column | Type              | Nullable | Default            |
| ------ | ----------------- | -------- | ------------------ |
| id     | uuid              | not null | uuid_generate_v4() |
| email  | character varying | not null |                    |

词汇

| Column | Type              | Nullable | Default            |
| ------ | ----------------- | -------- | ------------------ |
| id     | uuid              | not null | uuid_generate_v4() |
| word   | character varying | not null |                    |
| userId | uuid              | not null |                    |

定义

| Column       | Type              | Nullable | Default            |
| ------------ | ----------------- | -------- | ------------------ |
| id           | uuid              | not null | uuid_generate_v4() |
| meaning      | character varying | not null |                    |
| vocabularyId | uuid              | not null |                    |

我们都知道一个单词可能具有多个定义，用户在其词典中可能具有各种词汇。从SQL的角度来看，Definition和Vocabulary之间的关系是很多。确切的关系将用于桌子User和Vocabulary。

外键

| Table Name | Column       | Constraint Name                          | Foreign Constraint Definition  |
| ---------- | ------------ | ---------------------------------------- | ------------------------------ |
| Vocabulary | userId       | FK_Vocabulary_userId_User_id             | REFERENCES "User" ("id")       |
| Definition | vocabularyId | FK_Definition_vocabularyId_Vocabulary_id | REFERENCES "Vocabulary" ("id") |

除了创建或更新词汇外，还应有一种显示所有内容的方法。我需要创建一个负责从数据库块中获取数据的API。如我们所知，立即带来一切可能会更糟。还应该有一种机制来检索另一个数据，直到收到所有数据。如果API返回词汇的总数，则可以实现。

让我们迅速显示API请求和响应的输入和输出有效载荷。

interface RequestPayload {
   userId: string;
   pageNumber: number;
   pageSize: number;
}

interface ResponsePayload {
   data: {
       id: string;
       word: string;
       definitions: {
           id: string;
           meaning: string;
       }[];
   }[];
   total: number;
}

批处理大小为20，我必须确保以单词顺序发送响应。

typeorm方法（版本0）

我可以使用Typeorm的getManyAndCount方法快速实现它。

async fetch(userId: string, skip: number, pageSize: number): Promise<ResponsePayload> {
    // Imagine I calculated the value of the skip using pageSize and pageNumber
    const [vocabularies, total] = await this.createQueryBuilder('vocabulary')
        .leftJoin('vocabulary.definitions', 'definition')
        .where(`vocabulary.userId = :userId`, { userId })
        .orderBy(`vocabulary.word`, 'DESC')
        .skip(skip)
        .take(pageSize)
        .select(['vocabulary.id', 'vocabulary.word'])
        .addSelect(['definition.id', 'definition.meaning'])
        .getManyAndCount();

    return {
        data: vocabularies,
        total,
    };
}

getManyAndCount方法在将数据发送回数据之前会产生三个查询。让我们看看这三个查询。

查询1

SELECT DISTINCT "distinctAlias"."vocabulary_id" AS "ids_vocabulary_id", "distinctAlias"."vocabulary_word"
FROM (SELECT "vocabulary"."id"      AS "vocabulary_id",
             "vocabulary"."word"    AS "vocabulary_word",
             "definition"."id"      AS "definition_id",
             "definition"."meaning" AS "definition_meaning"
      FROM "Vocabulary" "vocabulary"
               LEFT JOIN "Definition" "definition" ON "definition"."vocabularyId" = "vocabulary"."id"
      WHERE "vocabulary"."userId" = '41f89c90-7029-46a4-8211-5f8c6e527a2d') "distinctAlias"
ORDER BY "distinctAlias"."vocabulary_word" DESC, "vocabulary_id" ASC
LIMIT 20 OFFSET 4980;

查询2

SELECT "vocabulary"."id"      AS "vocabulary_id",
       "vocabulary"."word"    AS "vocabulary_word",
       "definition"."id"      AS "definition_id",
       "definition"."meaning" AS "definition_meaning"
FROM "Vocabulary" "vocabulary"
         LEFT JOIN "Definition" "definition" ON "definition"."vocabularyId" = "vocabulary"."id"
WHERE ("vocabulary"."userId" = '41f89c90-7029-46a4-8211-5f8c6e527a2d')
  AND ("vocabulary"."id" IN
       ('41f89c90-7029-46a4-8211-5f8c6e527a2d', 'b19457a9-03fb-4c3b-b649-83001b6b9616',
        '17d84794-88a0-4e62-9f1c-88dea1fe8148', '9381945a-14d5-458b-b283-870ba3fa2057',
        'c00d27cb-7ad1-4fd4-9641-73d3484f3741', '34e8fc07-dec6-4dc8-b461-ed412a5f1cc8',
        'c5a52ddf-328f-4f1f-8c19-e38bc5d74ce1', '70de43d8-dbf2-45a4-86f1-1c4a0dc07512',
        '9150aff2-27b0-499e-82de-7a15373f35b0', '25aa3d73-5e50-410a-b16a-522246b54982',
        '3bf46a90-6982-46ac-9414-a5b3d36e9340', '05c26854-bfe2-48b3-ae2b-c3a76e26a473',
        '40808019-f2c1-4857-8bab-a1820cfb974f', '5cb2e93d-c64c-407c-bfd2-5b765f630298',
        '0fdf440c-37cd-46c3-a305-bd3b8937c377', '3fd8baf6-7e5e-4008-a8ef-81e30fcb6b0b',
        '9501f02f-b77c-4abc-b6ef-fe1a44a575f3', 'cf6c5111-9077-4c9f-9d1b-07eeb44a7160',
        '83c4e918-b14e-4848-ac16-e6e88fa6d774', 'ae156eb4-5a94-431a-8c1f-6b384d062e3b',
        'b44b3033-1b54-4d3f-93a0-c73477dddf68'))
ORDER BY "vocabulary_word" DESC;

查询3

SELECT COUNT(DISTINCT ("vocabulary"."id")) AS "cnt"
FROM "Vocabulary" "vocabulary"
        LEFT JOIN "Definition" "definition" ON "definition"."vocabularyId" = "vocabulary"."id"
WHERE "vocabulary"."userId" = '41f89c90-7029-46a4-8211-5f8c6e527a2d';

老实说，我没有留下深刻的印象，因为我认为它可以使它变得更好，例如，我们可以完全从第一个查询中删除LEFT JOIN和DISTINCT，因为它需要使用userId仅选择词汇ID1111111111111111111列以便第二个查询可以使用。

由于库生成了查询，而且除非分配存储库，否则我无法修改代码，我决定尝试其他方法。

查询版本1

SET SESSION my.userId = '41f89c90-7029-46a4-8211-5f8c6e527a2d';


SELECT vocabulary.id,
      vocabulary.word,
      JSON_AGG(JSON_BUILD_OBJECT('id', definition.id, 'meaning', definition.meaning)) AS definitions,
      (COUNT(*) OVER ())::INTEGER                                                     AS total
FROM "Vocabulary" AS vocabulary
        LEFT JOIN "Definition" AS definition ON vocabulary.id = definition."vocabularyId"
WHERE vocabulary."userId" = CURRENT_SETTING('my.userId')::UUID
GROUP BY vocabulary.id, vocabulary.word
ORDER BY vocabulary.word DESC
OFFSET 4980 LIMIT 20;

上面的查询将每个词汇项的关联定义汇总到使用JSON_AGG和JSON_BUILD_OBJECT的JSON数组中，以确保我以后不需要绘制它。它还使用窗口函数COUNT(*) OVER ()来计算请求用户创建的词汇总数。该版本和Typeorm版本之间的基本区别在于，后者仅使用一个查询为我们提供数据。

查询版本2

单个查询和多个查询之间存在权衡，以获取所需的数据。我不会为此辩论。我决定尝试另一种方法，因为我们可以停止使用OFFSET并利用ROW_NUMBER获得子集。

SET SESSION my.userId = '41f89c90-7029-46a4-8211-5f8c6e527a2d';


SELECT vocabulary.id, vocabulary.word, vocabulary.definitions, vocabulary.total
FROM (SELECT vocabulary.id,
            vocabulary.word,
            JSON_AGG(JSON_BUILD_OBJECT('id', definition.id, 'meaning', definition.meaning)) AS definitions,
            (COUNT(*) OVER ())::INTEGER                                                     AS total,
            ROW_NUMBER() OVER (ORDER BY vocabulary.word DESC)                               AS "rowNumber"
     FROM "Vocabulary" AS vocabulary
              LEFT JOIN "Definition" AS definition ON vocabulary.id = definition."vocabularyId"
     WHERE vocabulary."userId" = CURRENT_SETTING('my.userId')::UUID
     GROUP BY vocabulary.id, vocabulary.word) vocabulary
WHERE vocabulary."rowNumber" BETWEEN 4981 AND 5000;

非常简单，因为它只是将排名分配给每个词汇并过滤了正确的批次。

查询版本3

我想尝试LATERAL以避免GROUP BY。一点点努力，我写了查询。

SET SESSION my.userId = '41f89c90-7029-46a4-8211-5f8c6e527a2d';


SELECT vocabulary.id,
      vocabulary.word,
      definitions,
      (COUNT(*) OVER ())::INTEGER AS total
FROM "Vocabulary" AS vocabulary
        LEFT JOIN LATERAL (
   SELECT JSON_AGG(JSON_BUILD_OBJECT('id', definition.id, 'meaning', definition.meaning)) AS definitions
   FROM "Definition" AS definition
   WHERE definition."vocabularyId" = vocabulary.id
   ) AS definitions ON TRUE
WHERE vocabulary."userId" = CURRENT_SETTING('my.userId')::UUID
ORDER BY vocabulary.word DESC
OFFSET 4980 LIMIT 20;

查询版本4

计算可用词汇总数的整个想法是为前端应用程序提供一种决定是否向后端提出一个API请求的方法。该应用不会要求更多是否已经收到所有词汇。

有一种优雅的方法来避免检索用户创建的词汇总数。

Keyset分页，也称为 seek方法，依赖于列的排序来通过数据分页。它避免使用OFFSET。我们可以使用以前的块word来获取更多数据子集，而不是使用页码来确定需要跳过多少数据。

SET SESSION my.userId = '41f89c90-7029-46a4-8211-5f8c6e527a2d';

SELECT vocabulary.id,
       vocabulary.word,
       JSON_AGG(JSON_BUILD_OBJECT('id', definition.id, 'meaning', definition.meaning)) AS definitions
FROM "Vocabulary" AS vocabulary
         LEFT JOIN "Definition" AS definition ON vocabulary.id = definition."vocabularyId"
WHERE vocabulary."userId" = CURRENT_SETTING('my.userId')::UUID
  AND vocabulary.word < 'abcdef0105'
GROUP BY vocabulary.word, vocabulary.id
ORDER BY vocabulary.word DESC
LIMIT 20;

当我们使用订购列时，上述解决方案按预期工作。由于id列的类型为UUID，因此我们不能将其使用，因为ID是随机生成的。如果我们想将分类字段从word更改为id。

，这不是一个好方法。

顺便说一句，侯赛因·纳赛尔（Hussein Nasser）的一个很棒的视频解释了abiaoqian6。

性能测量

您可能已经注意到了。我所有的查询均用于第250页，获取4981st至5000th词汇。

好吧，我写了SQL queries，将大量数据插入数据库。

上面提到的脚本做了以下操作。

它创建了50个用户。
它为每个用户创建了5K词汇，即Vocabulary表中的250K行。
它为每个词汇创建了两个定义。这总共意味着Definition表中的行总数为500K，每个用户的定义为10K。

我注入了大量数据，以了解到目前为止我的查询的性能。 SQL EXPLAIN是测量估计执行成本的精美工具。

由于索引和性能之间存在直接关系，因此我将在此POC数据库中共享可用索引。大多数索引用于主要键，我在Vocabulary表上创建了一个复合唯一的约束，因此inserting the same vocabulary by a user in the table can be prevented easily。另外，在User表的email列上添加了另一个独特的约束。

索引密钥

| Table Name | Index Name                | Index Definition                                                                                    |
| ---------- | ------------------------- | --------------------------------------------------------------------------------------------------- |
| User       | PK_User_id                | CREATE UNIQUE INDEX "PK_User_id" ON public."User" USING btree (id)                                  |
| User       | UQ_User_email             | CREATE UNIQUE INDEX "UQ_User_email" ON public."User" USING btree (email)                            |
| Vocabulary | PK_Vocabulary_id          | CREATE UNIQUE INDEX "PK_Vocabulary_id" ON public."Vocabulary" USING btree (id)                      |
| Vocabulary | UQ_Vocabulary_word_userId | CREATE UNIQUE INDEX "UQ_Vocabulary_word_userId" ON public."Vocabulary" USING btree (word, "userId") |
| Definition | PK_Definition_id          | CREATE UNIQUE INDEX "PK_Definition_id" ON public."Definition" USING btree (id)                      |

使用EXPLAIN ANALYZE执行查询提供了很多关键信息，但是在这里我只想关注计划和执行时间。

时间比较

| Query Version |  Planing Time (Milliseconds)  |    Execution Time (Milliseconds)     |
| :-----------: | :---------------------------: | :----------------------------------: |
|       0       | 0.544 + 0.235 + 0.642 = 1.421 | 116.484 + 58.069 + 108.542 = 283.095 |
|       1       |             0.350             |               133.773                |
|       2       |             0.240             |               129.735                |
|       3       |             0.168             |              107734.892              |
|       4       |             0.190             |                49.012                |

作为Typeorm生成的三个查询，我应用了求和来与其他查询进行比较。

我们看到版本1和2几乎相同，基于LATERAL的版本是最差的，而TypeOmm One则足够好。 最好的是基于Keyset分页的查询。

让我们不要立即做出具体决定。执行时间取决于许多变量，例如数据卷，硬件的性质，缓存，并发等。让我分享一些方案。

我使用相同的用户ID和页码多次执行了相同的查询。时间总是不同。
在创建多个用户之前，我只使用一个用户，5K词汇和10K定义检查了另一个数据集的性能。在这种情况下，typeorm生成的查询优于一个和第二个版本。
使用AWS RDS，Aurora（或其他供应商）也会影响性能。我没有尝试过此数据集，但是一次，我用RDS中的另一个数据集完成了此数据集。

概括

我们始终需要仔细做出决定。最重要的是，我们不应盲目依靠任何ORM库，因为这些库旨在支持多个数据库。因此，这可能是为什么这些是某种通用的解决方案，它们会产生意想不到的，不是很理想的查询（有时）。

。

您知道随机记住一个单词就像追逐阴影吗？就像制作查询一样，记住单词也需要策略。这就是为什么科学家发明了使它变得容易的技术的原因。从长远来看，间隔重复是记住任何事物的技术之一。 leitner系统基于此原理。它建议为每个项目创建一个抽认卡，并在间隔进行检查。

（咳嗽，咳嗽）我根据想法创建了Firecracker Vocabulary Flashcards应用程序。邀请您加入学习派对！

使用或不使用ORM

好吧，我们编写了几个SQL查询。当我们将ORM集成到我们的项目中时，我们应该采取一种方法来执行选定的查询。最简单的方法就像下面的方法。

await this.query(/** paste the raw query here **/);

有人说使用ORM中的原始查询不是一个好方法。无论解决方案是什么，都应使用query builder或find options进行翻译。但是，并非所有类型的查询可能会转换。因此，完全取决于开发人员的偏好以及要求和分析。

。
再见

感谢您的耐心配合。我希望您发现它有见地和愉快！

nb ：我从here拍摄了封面图像。