使用UNION按数量级加速SQL查询

SQL是一个非常强大的查询数据的工具。它允许您以声明方式对您的关系数据编写查询，让您描述要检索的数据，而无需介绍如何检索它。在大多数情况下，这很好地运行，并且许多数据库引擎（MySQL，PostgreSQL等）中的查询优化器将创建一个有效的查询计划。

高效的查询计划依赖于使用适当的数据类型的模式，特别是对于主键列，其中误用varchar等事物可以杀死性能。启用快速查询计划的另一个关键元素是适当的索引列，这消除了在检索数据时执行全表扫描的需要。遗憾的是，即使遵循这些模式规则，也可以编写令人惊讶的表现令人惊讶的SQL查询，往往导致开发人员写作此类查询的困惑。也许这类查询的最令人惊讶的方面是它通常以最直观的方式编写来描述数据。

SQL查询性能可能显着降低的最常见情况之一是菱形模式，其中有多种方式将两个表连接在一起。在这样的模式中，查询可能会使用或以多种方式加入表，这消除了优化程序创建有效查询计划的能力。这种情况是最好通过一个例子说明的。

想象一下，我们有以下零售商店的架构，销售食品和饮料。表布局如下：

商店+ --------- + ------ +客户+ ----＆gt; | ID | INT |＆lt; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --------- + ------ + | |地址|文字| | + ---＆gt; | ID | int | | + --------- + ------ + || |名称|文字| | || | store_id | int + - +员工|| + ---------- + ------ + + --------- + ------ + || + -----＆gt; | ID | int | || | |名称|文字| || customer_orders | |角色|文字| || + ------------- + ----------- + | | store_id | int + - + | | ID | int |＆lt; - + | + ---------- + ------ ++ - + customer_id | int | | | |创造|时间戳| | | + ------------- + ----------- + | | employee_markouts | | + ------------- + ----------- + | | | ID | int | customer_order_items | + - +员工_ID | int | + ------------------ + ----- + | | meat_item_id | int + - + | ID | int | | |创造|时间戳| | | customer_order_id | int + - + + ------------- + ----------- + | + - + ENTEM_ITEM_ID | int | | | + ------------------ + ----- + | | meat_items | | + ------- + ------ + | + ----------------------------＆gt; | ID | int |＆lt; - - - - - - - - - - - - - - - - - - - - - - - - - - - - + |标签|文字| |价格| int | + ------- + ------ +

Customer_Order_Items和employee_markouts参考日内，其中包括销售的食品的标签和价格。

出于我们的测试的目的，我们将在PostgreSQL 12.6数据库上部署此架构，其中包含每个表中的以下记录数：

所有订单和Markout都分别在客户和员工之间随机分发。员工和客户也随机分发跨门。

为了审计清单，公司总部的物流团队要求一个工具，可以生成包含所有在特定一天留下特定商店库存的所有膳食的报告。这需要一个查询，其中包括销售给客户的项目，并在指定的日期录制为指定商店的员工Markouts。

要将此请求缩小到更可管理的段中，我们将首先检索在给定商店的给定日期创建的员工Markout的一部分的所有膳食项目。一旦我们有这个，我们将扩展它以包括客户购买的膳食物品。

只检索员工Markout数据的查询在存储表中开始，并将员工表DEPT到DIME_ITEMS表中。这是一个相当简单的查询，并且由于列被索引，我们希望它表现良好。

选择Enal_Items。 *，employee_markouts.employee_id来自Stores上的存储内部员工。 id = employees.store_id Inner加入员工上的Employee_markouts。 ID = employee_markouts.employEe_ID Inner Join_Items在employee_markouts.meal_item_id = dinal_items上。 id商店的id。 id = 250和employee_markouts.created＆gt; =＆＃39; 2021-02-03＆＃39;和employee_markouts.created＆lt; ＆＃39; 2021-02-04＆＃39 ;;

此查询为我们提供了我们正在寻找的数据，并以炽热的快速运行1.499毫秒 - 我们预期的出色表现。问题是我们还没有完成，我们还需要检索一部分客户订单的膳食物品。为此，我们将以以下方式修改上述查询：

我们将通过客户表中包含从商店到Ende_Items的第二个分支，将最终的连接更新到Ente_Items表中以使用或合并两个分支。

由于我们正在寻找员工Markouts或客户订单的一部分的餐品，我们将把我们的所有加入都转换为左转，并在客户分支机构中添加一个条件，以忽略员工的Markouts。

我们还将更改我们选择的列以包含员工属于employee_id或customer_id中的一个。

选择Enal_Items。 *，employee_markouts.employee_id，customer_orders.Customer_ID来自Stores - 员工分支左派加入商店的员工。 id = employees.store_id left加入员工的employee_markouts。 id = employee_markouts.employee_id - 客户分支左加入客户（存储。id = customers.store_id和employee_markouts。id为null）level and customer_orders。 id = customer_orders.customer_id left加入customer_orders上的customer_order_items。 id = customer_order_items.customer_order_id - 将分支加入DIME_ITEMS leap join_items上（customer_order_items.meal_item_Item_Ind_items。ID或employee_markouts.meal_item_id = dinal_items。ID）在哪里存储。 ID = 250和End_Items。 ID不是null和（employee_markouts.created＆gt; =＆＃39; 2021-02-03＆＃39;和employee_markouts.created＆＃39;或customer_orders.created＆gt; =＆ ＃39; 2021-02-03＆＃39;和customer_orders.created＆＃39; 2021-02-04＆＃39;）＆＃39;）小组由dinal_items组。 ID，Employee_markouts。 ID，Customer_orders。 ID，Customer_Order_Items。 ID;

在45个结果中，43个代表客户购买的膳食物品，我们继续看到来自员工Markouts的上一个查询的两餐。不幸的是，这个多分支查询的性能远远差。从之前的Sub-2-millisecond查询崩溃成一个缓慢的3,264毫秒。

对于一次性查询来说，这种性能可能是可以接受的，但对于任何其他用例，在我们的数据库中的数据量相对较少的数据，这是一个以上的执行时间非常差。我们的数据库大概只有75万行。如果我们正在处理数十或数亿岁的行计数，我们的报告表现可能在几十秒内。这是使我们的最终用户等待的不可接受的一段时间，特别是如果他们需要运行多个报告，那么我们需要找到实现更好性能的方法。

在运行我们的两个查询之后，显而易见的是，尝试在单个查询中检索员工和客户的膳食项目，以便我们编写查询＃2导致性能显着下降。即使在不熟悉查询计划的细节（通过重新运行查询以解释或解释分析），我们可以尝试使用我们认为具有更好性能的更简单的查询，并查看是否有一种更好的方法来构成结果。

查询＃1只检索员工Markouts的一部分员工市场，它表现得非常良好。让我们尝试编写查询只能检索一部分客户订单的餐项目并检查其性能。与员工数据的查询一样，此查询将加入商店和End_Items表之间的表，而是通过客户表来执行此操作。有三个客户特定的表而不是两个特定于员工的表，否则此查询与第一个类似：

选择Enal_Items。 *，customer_orders.customer_id来自商店内部的商店上的客户。 ID = Customers.Store_ID Inner Jourse Customer_orders在客户身上。 id = customer_orders.customer_id Inner加入Customer_Order_Items上的Customer_orders。 id = customer_order_items.customer_order_id Inner Join_Items在customer_order_items.meal_item_id = dinam_items。 id商店的id。 id = 250和customer_orders.created＆gt; =＆＃39; 2021-02-03＆＃39;和customer_orders.created＆lt; ＆＃39; 2021-02-04＆＃39 ;;

我们完全得到了我们预期的结果。查看性能，我们看到此查询仅在102毫秒内运行。这比查询＃1慢，因为我们的数据库中的员工有更多的客户，但仍然比3264毫秒Query＃2更快地运行。

现在我们处于检索正确结果的情况，尽管跨越两个查询。尽管如此，查询＃1（仅员工膳食物品）和查询＃3（仅客户膳食项目）的运行时间比查询＃2（员工和客户膳食项目通过多分支加入速度快30倍以上）。我们需要做的就是合并这些查询的结果。好消息是，SQL有一个操作，让我们在保留此速度时执行此操作。

UNION操作允许我们合并两个查询的结果。既然我们知道查询＃1和查询＃3都比查询＃2的速度明显快，我们希望联盟操作的结果也快。

我们几乎逐字使用查询＃1和查询＃3在我们的新组合查询将是什么。由于Union操作要求每个查询的结果包含相同的列，因此我们必须包含一个null占位符列，无论哪个类型的数据（employee_id或customer_id）都不会检索。

联合操作所做的另一件事是结果集中的重复数据删除行。由于我们不关心重复数据删除，我们可以使用联盟所有人告诉数据库引擎它可以跳过重复数据删除步骤。这导致具有较大数据集的性能提升。

选择 - 员工查询End_Items。 *，employee_markouts.employee_id，null as customer_id从商店内部加入员工在商店上。 id = employees.store_id Inner加入员工上的Employee_markouts。 ID = employee_markouts.employEe_ID Inner Join_Items在employee_markouts.meal_item_id = dinal_items上。 id商店的id。 id = 250和employee_markouts.created＆gt; =＆＃39; 2021-02-03＆＃39;和employee_markouts.created＆lt; ＆＃39; 2021-02-04＆＃39; Union所有选择 - 客户查询End_Items。 *，null作为employee_id，customer_orders.customer_id来自Stores Inner Joind Customers上的商店。 ID = Customers.Store_ID Inner Jourse Customer_orders在客户身上。 id = customer_orders.customer_id Inner加入Customer_Order_Items上的Customer_orders。 id = customer_order_items.customer_order_id Inner Join_Items在customer_order_items.meal_item_id = dinam_items。 id商店的id。 id = 250和customer_orders.created＆gt; =＆＃39; 2021-02-03＆＃39;和customer_orders.created＆lt; ＆＃39; 2021-02-04＆＃39 ;;

鉴于我们在上面看到的内容，我们预期45个结果是由此查询的结果。两人为员工，43为客户。运行查询给出以下结果：

我们完全相同的结果，我们期望的结果，在炽热的快速112毫秒。这是一个单一的查询，为我们提供了查询＃2给我们的同样的结果，但这样做的速度大约是约30倍。在这里使用联盟在表现方面几乎没有任何内容。时间基本上只是两个底层查询的总和。

值得注意的是，上述查询的结果与我们的原始查询不同，它由ID列订购。这是因为联盟操作按照它运行每个底层查询的顺序附加行（这也是我们首先获得员工膳食项目的原因）。如果我们需要命令匹配，我们可以通过在一个非常简单的选择操作中包装查询＃4来实现这一目标，这些操作按ID命令结果：

查询＃5 - 使用ID命令使用Union检索员工和客户膳食物品

从上面的（ - ...查询＃4中，为简洁起见）exceed *

查询＃5以与查询＃2相同的顺序给我们完全相同的结果，但性能增加了2,880％。这是一个突出的改进，现在表现足够的查询＃5可以在任何应用程序中使用。

有很多方法可以编写SQL查询来检索给定的一组结果。大多数数据库引擎都非常适合创建表演查询计划，但查询中的某些功能可以忽略查询策划器并导致非常慢的查询。在此帖子中，我们介绍了一个常见的场景，导致查询性能差：使用或将多个连接的多个分支组合在一个查询中。

到达Query＃2以获得综合结果是通过问题的直观思考方式，以及中间或高级SQL技能可能会提出的东西。但是，一旦我们意识到表现不好，我们应用以下步骤找到解决方案：

我们专注于编写更简单和良好的疑问，每个查询都给了我们所需结果的不同部分。

这种技术可以应用于许多情况，其中由于这种类型的菱形分支和合并，查询性能较差。在生产软件系统上时，我们经常看到在以这种方式重写查询时删除的缓慢查询引起的性能瓶颈。在许多情况下，性能改进是如此戏剧性地，它绝对需要缓存查询导致REDIS的系统，导致除了更好的性能之外的系统复杂性较少。

SQL的Union操作通常不会被认为是提高性能的手段。但是，在许多情况下，它可以通过使否则复杂的查询分成几个更快，更简单的查询，从而大大加速查询，然后将其合并在一起。识别联盟可以应用何时应用一些练习，但是一旦有人知道这种技术，就可以通过这种方法寻找能够去除性能瓶颈的情况。

Ben Levy和Christian Charukiewicz是Foxhound Systems的合作伙伴和主要软件工程师。在Foxhound Systems，我们专注于建设快速可靠的自定义软件。您是否面临绩效问题或寻求帮助您正在进行的事情？在[email protected] acto act向我们伸出援手。