sql-server - 同样的 SQL 请求，CockroachDB 需要 4min SQL Server 需要 35ms。我错过了什么吗？

Question

The database仅包含 2 个表:

钱包(100 万行)

事务(1500 万行)

CockroachDB 19.2.6 在 3 台 Ubuntu 机器上运行

每个 2vCPU

每个 8GB RAM

Docker 集群容器

对比

SQL Server 2019 在一台机器上运行 Windows Server 2019

4vCPU

16GB 内存

这是请求

select * from transaction t 
join wallet s on t.sender_id=s.id
join wallet r on t.receiver_id=r.id
limit 10;

SQL Server 只需要 35ms 就返回前 10 个结果

CockroachDB 需要 3.5-5 分钟。

1) 我知道 CockroachDB 的基础设施还不够公平，但是.. 差异实在太大了。我错过了什么吗？或者 CockroachDB 对于这个特定的 SQL 请求非常慢？

2)当我执行这个请求时，所有3个蟑螂节点的CPU都上升到了100%。正常吗？

更新:这是请求“解释”。我不知道如何阅读它..

> explain select * from transaction t
            -> join wallet s on t.sender_id=s.id
            -> join wallet r on t.receiver_id=r.id
            -> limit 10;
        tree         |       field        |     description
+---------------------+--------------------+----------------------+
                    | distributed        | true
                    | vectorized         | false
limit               |                    |
│                  | count              | 10
└── hash-join      |                    |
        │             | type               | inner
        │             | equality           | (receiver_id) = (id)
        │             | right cols are key |
        ├── hash-join |                    |
        │    │        | type               | inner
        │    │        | equality           | (sender_id) = (id)
        │    │        | right cols are key |
        │    ├── scan |                    |
        │    │        | table              | transaction@primary
        │    │        | spans              | ALL
        │    └── scan |                    |
        │             | table              | wallet@primary
        │             | spans              | ALL
        └── scan      |                    |
                    | table              | wallet@primary
                    | spans              | ALL

Answer 1

看起来这实际上是由于 SQL Server 和 CockroachDB 之间的查询计划不同，但可以通过几种方式解决。
根本问题在于transaction表有两个外键约束指向 wallet表，但两个外键都可以为空。这可以防止 CockroachDB 通过连接推送 10 行限制，因为对 transaction 的扫描table 可能需要为整个查询生成 10 多行以生成最多 10 行。
我们在查询计划中看到了这一点:

> explain select * from transaction t
  join wallet s on t.sender_id=s.id
  join wallet r on t.receiver_id=r.id
  limit 10;
                    info
---------------------------------------------
  distribution: full
  vectorized: true

  • limit
  │ count: 10
  │
  └── • lookup join
      │ table: wallet@primary
      │ equality: (receiver_id) = (id)
      │ equality cols are key
      │
      └── • lookup join
          │ table: wallet@primary
          │ equality: (sender_id) = (id)
          │ equality cols are key
          │
          └── • scan
                estimated row count: 10,000
                table: transaction@primary
                spans: FULL SCAN

请注意，在两个连接之后都会应用限制。
有两种相对简单的方法可以解决这个问题。首先，我们可以替换 joins与 left joins .这将允许将限制下推到 transaction 上的扫描。 table 因为左连接永远不会丢弃行。

> explain select * from transaction t
  left join wallet s on t.sender_id=s.id
  left join wallet r on t.receiver_id=r.id
  limit 10;
                  info
----------------------------------------
  distribution: full
  vectorized: true

  • lookup join (left outer)
  │ table: wallet@primary
  │ equality: (receiver_id) = (id)
  │ equality cols are key
  │
  └── • lookup join (left outer)
      │ table: wallet@primary
      │ equality: (sender_id) = (id)
      │ equality cols are key
      │
      └── • scan
            estimated row count: 10
            table: transaction@primary
            spans: LIMITED SCAN
            limit: 10

另一种选择是在外键约束中设置引用列 non null .这也将允许将限制下推到 transaction 上的扫描。表，因为即使是内部联接也永远不会丢弃行。

> alter table transaction alter column sender_id set not null;
ALTER TABLE

> alter table transaction alter column receiver_id set not null;
ALTER TABLE

> explain select * from transaction t
  join wallet s on t.sender_id=s.id
  join wallet r on t.receiver_id=r.id
  limit 10;
                  info
----------------------------------------
  distribution: full
  vectorized: true

  • lookup join
  │ table: wallet@primary
  │ equality: (receiver_id) = (id)
  │ equality cols are key
  │
  └── • lookup join
      │ table: wallet@primary
      │ equality: (sender_id) = (id)
      │ equality cols are key
      │
      └── • scan
            estimated row count: 10
            table: transaction@primary
            spans: LIMITED SCAN
            limit: 10