sql - HSQLDB中WHERE和ORDER BY的性能问题

Question

我有一个名为history_point的简单表，其中包含以下各列:

id-INTEGER PK

device_id-INTEGER

registered-TIMESTAMP

double_value-DOUBLE

channel-INTEGER

type-VARCHAR(100)

int_value-INTEGER

该表还为以下列组合定义了索引:

id

device_id

registered

channel

device_id，channel，registered

该表包含大约200000行。我使用下面的Java代码运行查询。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcMain {
    public static void main(String[] args) throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        Connection con = DriverManager.getConnection("jdbc:hsqldb:file:db/homeki.db;ifexists=true");
        Statement stmt = con.createStatement();
        long start = System.currentTimeMillis();
        ResultSet rs = stmt.executeQuery("<SQL query goes here>");
        if (rs.next()) {
            System.out.println("Registered: " + rs.getDate("registered"));
        }
        long dur = System.currentTimeMillis() - start;
        System.out.println("Took " + dur + " ms.");
        stmt.execute("SHUTDOWN");
        con.close();
    }
}

当我运行查询 SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 LIMIT 1时，大约需要5毫秒。如果我运行查询 SELECT * FROM history_point ORDER BY registered DESC LIMIT 1，它也需要约5毫秒。 但是，如果我运行查询 SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 ORDER BY registered DESC LIMIT 1，则需要〜1000ms!

考虑到他们在 http://www.hsqldb.org/doc/1.8/guide/ch02.html#N1033B上所说的话，我想这是可以理解的，“HSQLDB不使用索引来改善查询结果的排序”(这对我来说听起来很奇怪)。

但是，如果我使用Eclipse Data Tools Platform的SQL Scrapbook在Eclipse中运行最后一个查询，它将在约5毫秒内执行。我使用SQL Scrapbook对其进行的任何查询都会在约5毫秒内执行。这是为什么？他们都使用相同的JDBC驱动程序和相同的数据库。

如果重要的话， history_point中有〜25000行，其中 device_id = 3和 channel = 0。

我使用的连接字符串是 jdbc:hsqldb:file:db/mystorage.db。该表被创建为 CACHED表(所有数据都存储在磁盘上)。

谁能解释一下？

提前致谢!

Answer 1

如果ORDER BY列都被索引覆盖，并且查询条件可以使用其他索引，则使用查询条件的索引。当前，HSQLDB可以将(device_id, channel, registered)上的索引用于查询条件，但不能同时用于registered列上的排序。

在版本2.2.8中，当存在LIMIT子句时，可以强制在ORDER BY列上使用索引。在这种情况下，将使用注册时的索引:

SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 
ORDER BY registered DESC LIMIT 1 USING INDEX

但这也许是在三列上使用索引的最佳方法:

SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 
ORDER BY device_id DESC, channel DESC, registered DESC LIMIT 1 USING INDEX