同样的SQL请求，CockroachDB需要4分钟Server采取35 SQL。我是不是遗漏了什么？

Question

数据库只包含两个表：

• 钱包(100万行)
• 交易(1 500万行)

CockroachDB 19.2.6运行在3台Ubuntu机器上

• 2 2vCPU
• 8GB内存
• 码头群集装箱

vs

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

• 4vCPU
• 16 RAM

这是我的要求

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

• Server只需使用35 to即可返回前10项结果
• CockroachDB为3.5-5 min。

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

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

这似乎实际上是由于Server和CockroachDB之间的查询计划不同，但是可以通过几种方式来解决。

根本问题是，transaction表有两个指向wallet表的外键约束，但这两个外键都是可空的。这使CockroachDB无法通过联接推送10行限制，因为transaction表上的扫描可能需要生成超过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表上的扫描，因为左联接永远不会丢弃行。

> 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

Answer 2

我相信你的桌子上有过时的统计数据。(希望到目前为止，统计数据已经自动更新，您不再有这个问题了。)您可以在CockroachDB文档中阅读这方面的内容，还有一个链接描述了如何手动创建新的统计信息。