java - SQL语句花费大量时间，是否可以对其进行优化？

Question

这是我的 SQL 查询，我用它得到了所有重复项，但有一个(最新的):

SELECT d.C_ContactID, d.C_EmailAddress, d.C_DataSourceID, d.C_DateCreated 
FROM duplicates as d 
WHERE d.C_DateCreated !=(select max(d2.C_DateCreated) 
FROM duplicates d2 
WHERE d2.C_DataSourceId = d.C_DataSourceId)

是否可以以某种方式对其进行优化？不幸的是，在 300 000 条记录中，需要 +- 40 分钟。

查询的方法是:

public ArrayList<Record> get() throws SQLException,
        ClassNotFoundException {
    Statement st = DBConnect.DBC.con.createStatement();
    String sql = ("select d.C_ContactID, d.C_EmailAddress, d.C_DataSourceID, 
d.C_DateCreated "
            + "from duplicates as d "
            + "where d.C_DateCreated !=(select max(d2.C_DateCreated) "
            + "from duplicates d2 where d2.C_DataSourceId = d.C_DataSourceId)");
    ResultSet rs = st.executeQuery(sql);
    DBConnect.DBC.con.commit();
    while (rs.next()) {

        int contactID = rs.getInt("C_ContactID");
        String email = rs.getString("C_EmailAddress");
        String dataSourceID = rs.getString("C_DataSourceID");
        String dateCreated = rs.getString("C_DateCreated");
        duplicate = new Record(contactID, email, dataSourceID, dateCreated);
        duplicates.add(duplicate);
    }
    rs.close();
    st.close();

    return duplicates;
}

Answer 1

您首先要在duplicates(C_DataSourceId, C_DateCreated)上创建索引:

create index duplicates_DataSourceId_DateCreated on duplicates(C_DataSourceId, C_DateCreated);

如果您使用的数据库支持窗口函数，那么我会将其改写为:

SELECT d.C_ContactID, d.C_EmailAddress, d.C_DataSourceID, d.C_DateCreated 
FROM (select d.*, max(C_DateCreated) over (partition by C_DataSourceId) as maxdc
      from duplicates d 
     ) d
WHERE d.C_DateCreated <> maxdc;

值得进行比较，因为有时窗口函数具有高效的实现。

而且，如果您有索引，则查询的稍微高效的版本是:

SELECT d.C_ContactID, d.C_EmailAddress, d.C_DataSourceID, d.C_DateCreated 
FROM duplicates d
WHERE EXISTS (select 1
              from duplicates 2
              where d2.C_DataSourceId = d.C_DataSourceId and
                    d2.C_DateCreated > d.C_DateCreated
             );

这表示从重复项中获取所有行，其中另一行(具有相同的源)创建了更大的日期。略微的优点是，不必查看所有值来获取max()。它只需要找到第一个。主要的性能改进将是综合指数。