主页 / dba / 问题 / 78112
Accepted
Gili
Asked: 2014-10-01 21:03:14 +0800 CST

将复杂查询分解为多个简单查询的开销？

  • 772

当用多个简单查询替换单个复杂查询时，我应该期望什么样的开销？

我的目标是提高所有 SQL 代码的可读性和可移植性，因此我将尽可能使用简单的结构并用 ANSI SQL 替换特定于数据库的扩展。

例如：

  • 假设客户端正在调用动态 SQL（而不是存储过程）
  • 场景 1：客户端调用：INSERT INTO employee SELECT name FROM user
  • 场景 2：客户端调用：
Statement getNames = connection.createStatement();
try (ResultSet rs = getNames.executeQuery("SELECT name FROM user"))
{
  while (rs.next())
  {
    String name = result.getString(1);
    PreparedStatement prepared = connection.prepareStatement("INSERT INTO employee SET name = ?");
    prepared.setString(1, name);
    prepared.executeUpdate();
  }
}

场景 1 不是一个复杂的查询，但为了论证起见，我们假设它是。场景 2 使用多个（更简单的）查询获得相同的结果。与场景 1 相比，场景 2 的开销是多少？这是我应该担心的事情还是可以忽略不计？

更新：https ://stackoverflow.com/a/14408631/14731提出了一个很好的观点。手动分解查询会硬编码执行计划，而不是让数据库的优化器进行选择。话虽这么说，但仍不清楚开销是否有意义。

performance application-design

2 个回答

  1. Best Answer

    作为一般经验法则，您应该向数据库提供尽可能多的有关您正在执行的任务的信息。这如何适用于您的场景？

    情景 1 ( INSERT .. SELECT)

    数据库知道您要将一整套数据从一个表或从一个派生表批量移动到另一个。它可以优化给定的执行：

    • 批量操作的基数
    • 将被调用的各种约束/触发器
    • 需要在新表中保留的存储量
    • 等等等等

    场景 2 (SELECT和 N x INSERT)

    数据库不知道您将在SELECT. 即使它足够聪明，可以收集有关您的 之后会发生什么的长期统计数据和启发式方法SELECT，但对后续负载进行任何假设都是不明智的。因此，实际上，这种情况通常比另一种情况要糟糕得多。

    不过，有一些评论需要说明：

    • 即使您有充分的应用程序设计理由将INSERTs 像这样分区，您也应该将它们批量发送到数据库
    • 分区的一个很好的理由INSERT是您可以对交易长度进行更细粒度的控制。在打开日志记录的长时间运行的事务中插入数百万条记录通常是不好的。因此，要么关闭日志记录，要么在 N 次插入后提交

    结论

    以上是关于你的两种具体情况的评论。现实世界的场景并不是那么简单，但你也在问题中说明了这一点。我想表达的观点是，在很多情况下，你应该让数据库执行批量数据操作，因为那是它非常擅长的。

    • 3

  2. 对于您的特定示例场景 1，将所有数据保存在服务器上。场景 2 将要求数据被打包，通过线路发送到客户端，其中必须被缓冲（并且可能溢出到磁盘）然后取消缓冲，重新打包并发送回服务器，在那里它将最后得到处理。这个网络时间加起来。使用足够大的行集经常这样做，您会看到延迟增加。一次做一行（如您的示例所示），您将在余下的自然生活中毁掉您的决定。批量提交通常比单独提交报表更快。

    场景 1 是带有隐式事务的单个语句以保持数据一致。场景 2 将需要显式事务来实现相同的一致性。这些锁必须在到客户端的往返期间一直持有，这将导致阻塞。

    如果您将一个复杂的查询拆分为更简单的语句，这些语句全部作为批次提交，则有得有失。例如，拆分

    select
    <lots of rows>
from <really complex predicate>

    进入

    select
    <some columns>
into #T1
from dbo.Table1 as a
where <whatever>;

select
    <other stuff>
from #T1
inner join dbo.Table2 as b
where <etc.>

    将有明显的写入和读取#T1 的成本。但是，如果您可以以有助于第二个查询的方式索引#T1，可能会有好处，也许如果对 Table1 的值执行了复杂的操作。

    存储过程可能会受到参数嗅探的影响。这有时可以通过在每次执行时重新编译过程来纠正。重新编译可能很昂贵。通过将一个复杂的查询拆分为几个更简单的查询，您可以将重新编译提示仅放在从中受益的语句上。

    优化器不会无限期地搜索最佳执行计划。它最终会超时并以当时可用的最佳状态运行。优化器必须做的工作随着查询的复杂性呈指数级增长。拥有更简单的查询可以让优化器为每个单独的查询找到最佳计划，从而提供更低的整体执行成本。

    • 1

相关问题