更快地编写SQL的5个规则
#sql #database #分析 #backend

当您尝试实时处理大量数据时，遵循一些最佳实践。这些是从多年来构建实时系统大规模构建实时系统的经验教训。现在，我们正在与您分享。

快速查询的5个规则

在Tinybird，我们希望我们的用户能够快速发展。两者都在构建查询时，然后在这些查询运行时。因此，我们创建了一个规则的作弊，您可以在需要快速时咨询。

我们想称它们为5个快速查询规则：

  • 规则1¶最好的数据是您不写的数据。
  • 规则2 - 第二最佳数据是您不读的数据。
  • 规则3¶顺序读取速度更快。
  • 规则 - 4¶您处理的数据越少（阅读后），
    • 越好。
  • 规则5字

让我们一一走，使用存储在tinybird中的数据来分析实施每个规则后的性能改进。我们将使用著名的纽约出租车旅行数据集。您可以从您的仪表板中获取一个示例here并通过creating a new Data Source将其直接导入到Tinybird中。

规则1：最好的数据是您不写的数据

这个规则似乎很明显，但并非总是遵循的。 没有理由保存您不需要的数据：它将影响所需的内存（和钱！），查询将需要更多的时间，因此它只有缺点。

SELECT 'do not save what you don\'t need!' AS first_rule

规则2：第二好数据是您不阅读的数据

避免阅读您不需要的数据，您应该尽快应用过滤器。

使用纽约市出租车数据，假设我们想要一份距离大于10英里并且发生在2017-01-01-31 14：00：00和'之间的旅行清单。 2017-01-31 15：00：00。此外，我们想在日期下订购这些旅行。

让我们看到在查询执行结束时应用过滤器与开始时的性能差异。

首先，让我们通过按日期订购所有数据来开始第一个方法：

--NODE: rule2_data_read_NOT_OK
SELECT *
FROM nyc_taxi
ORDER BY tpep_pickup_datetime ASC
---
10.31 MB processed, 139.26k rows, 9.16 ms elapsed

一旦分类数据，我们将过滤：

A screenshot showing a node of SQL that runs over filtered data.

在tinybird中，您可以在先前的节点上运行多个SQL和查询。该节点从上面的上一个查询中通过过滤的SQL运行。

如果添加两个节点的处理时间，则第一种方法大约需要30-60毫秒。

请注意统计信息：第一个节点扫描了139.26K行处理10.31 MB的数据。第二个节点扫描了24.58K行和1.82 MB的数据。如果我们真的需要扫描24.58K，为什么我们首先要扫描139.26K行？

重要的是要意识到这两个值直接影响查询执行时间，并且还会影响您可能同时运行的其他查询。 io带宽也是您需要牢记的。

现在，让我们看看如果在排序之前应用过滤器会发生什么：

--NODE: rule2_data_read_OK
SELECT * FROM nyc_taxi
WHERE (trip_distance > 10) 
AND ((tpep_pickup_datetime >= '2017-01-31 14:00:00') 
AND (tpep_pickup_datetime <= '2017-01-31 15:00:00'))
ORDER BY tpep_pickup_datetime ASC
---
1.82 MB processed, 24.58k rows, 4.33 ms elapsed

您可以看到，如果在排序之前应用了过滤器，则查询为<10 ms。如果您查看读取的数据大小，则它是1.82MB，而读取的行数为24.58K。与以前的方法相比，这些方法要小得多，更有效。

这种显着的差异会发生，因为在第一种方法中，我们正在对所有可用数据（即使是对查询不需要的数据）进行排序，而在第二种方法中，我们只是对所需的行进行排序。

过滤是最快的操作，因此始终首先过滤。

规则3：顺序读取速度更快100倍

要进行顺序读取，正确定义索引至关重要。这些索引应根据我们要执行的查询来定义。在此示例中，我们将通过对表进行排序来模拟索引数据，然后再查询它们，以查看通过非索引列过滤如何影响性能。

。

例如，如果我们想查询数据并通过tpep_pickup_time进行过滤，请比较当数据与任何其他列对数据进行排序时的数据分类时会发生什么。

。

在第一种方法中，我们将按另一列对数据进行排序，例如，passenger_count：

--NODE: rule3_sequential_read_NOT_OK
SELECT * FROM nyc_taxi
ORDER BY passenger_count ASC

一旦我们通过pastenger_count对数据进行排序，我们就会通过tpep_pickup_time筛选它：

A screenshot of a suboptimal SQL query that filters by a column which is not indexed.

该节点过滤器按日期上的日期，而不是按日期排序的数据。结果是次优的。

这种方法大约需要5-10毫秒，扫描行的数量为26.73k，数据大小为1.98MB。

对于第二种方法，我们将按tpep_pickup_time进行分类：

--NODE: rule3_sequential_read_OK
SELECT * FROM nyc_taxi
ORDER BY tpep_pickup_datetime ASC

，一旦按日期进行排序，我们将过滤：

A screenshot of an optimized SQL query that filters on indexed data.

该节点过滤器按日期按日期进行排序的数据。查询扫描的数据要少得多，并且响应速度要快得多。

我们可以看到，如果数据由tpep_pickup_time排序，并且查询使用tpep_pickup_time进行过滤，则只需1-2毫秒，仅扫描10.35k行，并且仅处理765.53 kb。第一种方法是通过另一列过滤的，需要大约5-10毫秒，扫描26.73k行，并处理1.98 mb。

。

重要的是要强调，我们拥有的数据越多，两种方法之间的差异就越大。处理大量数据时，顺序读取的速度可以更快或更高。

因此，要考虑将要进行的查询来定义索引。

规则4：您处理的数据越少（阅读后），越好

您处理的数据越少，您的查询越快，更便宜。因此，如果您只需要两列，则仅检索SELECT中的两个列。

让我们假设对于我们的用例，我们只需要三列：vendorid，tpep_pickup_datetime和trip_distance。

让我们分析选择所有列与仅需要的列之间的差异。

当我们选择所有列时，查询需要大约140-180毫秒，并处理718.55 mb的数据：

--NODE: rule4_read_all_columns_NOT_OK
SELECT *
FROM
(
    SELECT 
        *
    FROM nyc_taxi
    order by tpep_dropoff_datetime
)
---
718.55 MB processed, 9.71m rows, 112.18ms elapsed

但是，当我们仅选择所需的列时，查询只需大约35-60毫秒，并且数据的数据〜20％：

--NODE: rule4_read_some_columns_OK
SELECT *
FROM
(
    SELECT
        vendorid,
        tpep_pickup_datetime,
        trip_distance
    FROM nyc_taxi
    order by tpep_dropoff_datetime
)
---
155.36 MB processed, 9.71m rows, 35.10ms elapsed

正如我们之前提到的，您可以检查扫描数据的大小如何少得多，现在只有155.36MB。使用分析数据库，如果您不需要检索列，则不会读取这些文件，并且效率更高。

因此，您应仅处理所需的数据。

规则5：稍后在处理管道中移动复杂操作

复杂操作（例如连接或聚集）应在处理管道中尽可能迟到。这是因为在第一步中，您应该过滤所有数据，因此最后的行数将比开始时少，因此，执行复杂操作的成本将较低。

所以，首先，让S汇总数据：

--NODE: rule5_complex_operation_NOT_OK
SELECT
    vendorid,
    pulocationid,
    count(*)
FROM nyc_taxi
GROUP BY
    vendorid,
    pulocationid
---
77.68 MB processed, 9.71m rows, 25.09ms elapsed

现在，让我们应用过滤器：

A screenshot of a suboptimal SQL query that filters aggregated data

通过汇总数据进行查询过滤器。结果是次优的。

如果聚合在过滤前进行了执行，则查询总计约为50-70毫秒，并且扫描971万行和过程77.68 mb。

让我们看看如果我们首先过滤，然后汇总会发生什么：

--NODE: rule5_complex_operation_OK
SELECT
    vendorid,
    pulocationid,
    count(*)
FROM nyc_taxi
WHERE vendorid < 10
GROUP BY
    vendorid,
    pulocationid
---
77.68 MB processed, 9.71m rows, 22.13ms elapsed

即使扫描行的数量和数据的大小与以前的方法相同，这种方法也只需20-40毫秒。

因此，您应该在处理管道中尽可能晚。

进行复杂的操作。

一些其他指导

除了这5个规则外，这里还有一些有关tinybird中最佳查询的一般建议：

避免进行全面扫描

您在查询中阅读的数据越少，它们的速度就越快。您可以在tinybird中遵循不同的策略，以避免从查询中阅读数据源中的所有数据（进行完整扫描）：

  • 始终首先过滤
  • 通过在数据源中设置适当的ENGINE_SORTING_KEY来使用索引。
  • ENGINE_SORTING_KEY中存在的列名应该是您将在WHERE子句中使用的列名。您不需要按您用于过滤的所有列进行排序，而只需要首先过滤。
  • ENGINE_SORTING_KEY中列的顺序很重要：从左至右按相关性（更重要的过滤）和基数（较重要的）和基数（较少的基数为首先）。 
--Tinybird Data Source schema
SCHEMA >
  `id` Int64,
  `amount` Int64,
  `date` DateTime

ENGINE "MergeTree"
ENGINE_SORTING_KEY "id, date"

--BAD: not filtering by any column in the sorting key
SELECT *
FROM data_source_sorted_by_date
WHERE amount > 30

--GOOD: filtering by columns present in the sorting key
SELECT *
FROM data_source_sorted_by_date
WHERE
  id = 135246 AND
  date > now() - INTERVAL 3 DAY AND
  amount > 30

避免加入

在tinybird中进行JOIN时，右数据源中的数据被加载到内存中以执行加入。因此，建议避免通过在正确的数据源中过滤数据来加入大数据源。

JOINs在超过1m行的桌子上，可能会导致MEMORY_LIMIT错误，从而影响摄入。

提高联接性能的一种常见模式是以下：

--BAD: doing a join with a large right Data Source
SELECT
    left.id AS id,
    left.date AS day,
    right.response_id AS response_id
FROM left_data_source AS left
INNER JOIN big_right_data_source AS right ON left.id = right.id

--GOOD: pre-filtering the joined Data Source
SELECT
    left.id AS id,
    left.date AS day,
    right.response_id AS response_id
FROM left_data_source AS left
INNER JOIN (
  SELECT id, response_id
  FROM big_right_data_source
  WHERE id IN (SELECT id FROM left_data_source)
) AS right ON left.id = right.id

tinybird的新手？

Tinybird是开发人员和数据团队的real-time data platform。从任何地方摄入数据，使用SQL查询，然后单击以低延迟API的形式发布您的（优化）查询。您可以无需时间限制而无需信用卡。