代码之家 › 专栏 › 技术社区 › Nic

进行更有效的连接

join sql

Nic · 技术社区 · 16 年前

这是我的问题,相当简单:

SELECT
    INVOICE_ITEMS.II_IVNUM, INVOICE_ITEMS.IIQSHP
FROM
    INVOICE_ITEMS
LEFT JOIN
    INVOICES
ON 
    INVOICES.INNUM = INVOICE_ITEMS.II_INNUM
WHERE
    INVOICES.IN_DATE
BETWEEN
    '2010-08-29' AND '2010-08-30'
;

我对SQL的了解非常有限,但我试图理解一些概念,如子查询等。我不想重新设计这段代码,而是想解释一下为什么它这么慢(在我的测试数据库中有600多秒)以及如何使它更快。

据我所知,左联接正在创建一个虚拟表,并用联接中的每个结果行填充它,这意味着它正在处理每一行。如何阻止查询完全读取表并仅查找 WHERE/BETWEEN 先执行子句,然后在此之后创建虚拟表(如果可能的话)?

我的逻辑如何?是否有任何一致推荐的资源可以让我进入SQL忍者状态?

编辑:感谢大家的快速和礼貌的回答。目前,我正在通过ODBC连接到一个专用数据库,该数据库在名为OMNIS的快速应用程序开发框架中使用。因此,我真的不知道运行的是哪种优化,但我相信它松散地基于MSSQL。

5 回复 | 直到 16 年前

D'Arcy Rittich 16 年前

我会这样重写它,并确保你有一个索引 i.INNUM , ii.INNUM 和 i.IN_DATE . 这个 LEFT JOIN 正在变成一个 INNER JOIN 由你 WHERE 条款,所以我重写了它:

SELECT ii.II_IVNUM, ii.IIQSHP 
FROM INVOICE_ITEMS ii
INNER JOIN INVOICES i ON i.INNUM = ii.II_INNUM 
WHERE i.IN_DATE BETWEEN '2010-08-29' AND '2010-08-30'

根据您使用的数据库,可能发生的情况是来自 INVOICE_ITEMS 正在加入(由于 左连接 ,无论是否与匹配 INVOICE 或者没有,然后 在哪里? 子句将筛选到与在范围内具有日期的项匹配的项。通过切换到 内连接 ,只需将WHERE子句应用于 INVOICES 具有匹配项的记录 发票项 记录。

HLGEM 16 年前

由于这是一个非常基本的查询,优化器应该可以很好地处理它,所以您的问题可能是索引不正确。您在“In-U-Date”字段和“Invoice-ITEMS.II”Innum字段上有索引吗?如果正确设置了pk-fk关系,则invoices.innum应该已经被索引,但fk不会自动被索引。

Keith 16 年前

您的查询很好,这是您必须查看的索引。

是 INVOICES.INNUM 和 INVOICE_ITEMS.II_INNUM 索引的?

如果不是,SQL必须做一个叫做“扫描”的事情——它搜索每一条记录。

你可以把索引想象成电话簿边上的标签——你知道从哪里开始根据姓氏的第一个字母来寻找人。如果没有索引(比如你想查找以“…儿子”结尾的名字),你就必须搜索整本书。

有不同类型的索引——它们可以排序(如电话簿索引——都按姓氏排序)或不排序(如书后的索引——在查找索引和实际页面时会产生开销)。

您还应该能够查看查询计划—这是服务器执行SQL语句的方式。这可以告诉您各种更高级的东西——例如,有多种方法来完成这项工作:如果两个表都按联接字段排序,则合并联接是可能的,或者嵌套联接将为较大表中的每个记录循环通过较小的表。

Peter 16 年前

好吧,没有理由这个查询速度慢…唯一需要考虑的是,您在invoices.innum=invoice_items.ii_innum上有索引吗?如果添加它们,可以加快选择速度,但会减慢更新/插入速度…

Adam Robinson 16 年前

联接不会在任何东西上创建“虚拟表”,而不仅仅是概念级别。

查询的性能问题很可能是索引不好或索引不足。您应该对以下内容进行索引:

INVOICE_ITEMS.II_INNUM
INVOICES.IN_DATE

你也应该有一个索引 INVOICES.INNUM ,但是如果这是表的主键,那么它已经有了一个。

另外,不要在这里使用左联接。如果在 INVOICE_ITEMS.II_INNUM 和 发票 (和) 发票项目II 不可为空),则在 INVOICE_ITEMS 这不符合 INVOICES . 即使有,你的 WHERE 条件使用的值来自 发票 ,所以您无论如何都要消除任何不匹配的行。只要用普通的 JOIN .