sql - 具有子查询性能的SQLite查询

Question

我对Sqlite数据库有下一个SQL查询：

SELECT * FROM messages   WHERE type IN (3) AND modem_id IN( 
    SELECT device_id FROM client_devices WHERE client_id=0 AND device_id IN (7368859)) 
ORDER BY time_detected DESC LIMIT 1000

子查询带来单个数据行的位置。查询对我的数据执行大约7秒。单独的子查询执行不到1毫秒。但是，如果我摆脱子查询并将此单个modem_id直接传递给查询：

SELECT * FROM messages   WHERE type IN (3) AND modem_id IN( 7368859) 
ORDER BY time_detected DESC LIMIT 1000

查询执行不到50毫秒。

我被误解了吗？

UPD：
查询：

SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN( SELECT 7368859) ORDER BY time_detected DESC LIMIT 1000

执行7秒。和查询

SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN(7368859) ORDER BY time_detected DESC LIMIT 1000

执行44ms。
那就是问题所在。

UPD：

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `stations` (
    `bs_id` INTEGER NOT NULL UNIQUE,
    `online_status` INTEGER,
    `dl_status` INTEGER,
    `status_duration`   INTEGER,
    `noise` INTEGER,
    `temperature`   INTEGER,
    `dl_busyness`   INTEGER,
    `dl_aver_busyness`  INTEGER,
    `bs_state`  INTEGER,
    `rev_list`  TEXT,
    `ul_bitrates`   TEXT,
    `dl_bitrates`   TEXT,
    `ul_base_freqs` TEXT,
    `dl_base_freqs` TEXT,
    `last_hb_time`  INTEGER,
    `bs_type`   TEXT,
    `timezone_offset`   INTEGER NOT NULL DEFAULT (10800),
    PRIMARY KEY(`bs_id`)
);
CREATE TABLE IF NOT EXISTS `radiomodems` (
    `id`    INTEGER,
    `batch_id`  INTEGER,
    `nbfi_ver`  INTEGER NOT NULL DEFAULT 0,
    `hw_type`   TEXT,
    `protocol`  TEXT,
    `dl_strength`   INTEGER NOT NULL DEFAULT 26,
    `ul_messages_per_ack`   INTEGER NOT NULL DEFAULT 1,
    `dl_messages_per_ack`   INTEGER NOT NULL DEFAULT 1,
    `ul_base_freq`  INTEGER NOT NULL DEFAULT 868800000,
    `dl_base_freq`  INTEGER DEFAULT 446000000,
    `dl_mode`   INTEGER NOT NULL DEFAULT 0,
    `dl_phy`    TEXT NOT NULL DEFAULT 'DL_PSK_200',
    `dl_num_of_retries` INTEGER NOT NULL DEFAULT 3,
    `key`   TEXT,
    `bs_data`   TEXT,
    `ul_bitrates`   TEXT,
    `dl_bitrates`   TEXT,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `messages` (
    `id`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `modem_id`  INTEGER NOT NULL,
    `station_id`    INTEGER NOT NULL,
    `time_detected` INTEGER NOT NULL,
    `time_saved`    INTEGER NOT NULL,
    `type`  INTEGER NOT NULL DEFAULT (0),
    `iterator`  INTEGER NOT NULL,
    `payload`   BLOB NOT NULL,
    `snr`   INTEGER NOT NULL,
    `rssi`  INTEGER NOT NULL,
    `freq`  INTEGER NOT NULL,
    `phy`   INTEGER NOT NULL,
    `comment`   TEXT
);
CREATE TABLE IF NOT EXISTS `downlinks` (
    `tag_id`    TEXT,
    `modem_id`  INTEGER NOT NULL,
    `station_id`    INTEGER NOT NULL DEFAULT (0),
    `payload`   BLOB NOT NULL,
    `flags` INTEGER NOT NULL DEFAULT (0),
    `status`    INTEGER NOT NULL,
    `posted_time`   INTEGER NOT NULL DEFAULT (strftime('%s','now','utc')),
    `placeholder`   TEXT,
    PRIMARY KEY(`tag_id`)
);
CREATE TABLE IF NOT EXISTS `clients` (
    `id`    INTEGER,
    `apikey`    TEXT NOT NULL UNIQUE,
    `role`  INTEGER NUT DEFAULT 1,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `client_devices` (
    `client_id` INTEGER NOT NULL,
    `device_id` INTEGER NOT NULL,
    FOREIGN KEY(`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE,
    PRIMARY KEY(`client_id`,`device_id`),
    FOREIGN KEY(`device_id`) REFERENCES `radiomodems`(`id`) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS `time4_idx` ON `messages` (
    `type`,
    `time_detected`
);
CREATE INDEX IF NOT EXISTS `time3_idx` ON `messages` (
    `type`,
    `modem_id`,
    `time_detected`
);
CREATE INDEX IF NOT EXISTS `time2_idx` ON `messages` (
    `type`,
    `station_id`,
    `time_detected`
);
CREATE INDEX IF NOT EXISTS `time1_idx` ON `messages` (
    `type`,
    `modem_id`,
    `station_id`,
    `time_detected`
);
CREATE INDEX IF NOT EXISTS `modem_id_idx` ON `radiomodems` (
    `id`
);
CREATE INDEX IF NOT EXISTS `dl_tag_id_idx` ON `downlinks` (
    `tag_id`
);
CREATE INDEX IF NOT EXISTS `dl_status_idx` ON `downlinks` (
    `status`
);
CREATE INDEX IF NOT EXISTS `client_dev_idx` ON `client_devices` (
    `device_id`
);
CREATE INDEX IF NOT EXISTS `batch_idx` ON `radiomodems` (
    `batch_id`
);
CREATE INDEX IF NOT EXISTS `apikey_idx` ON `clients` (
    `apikey`
);
COMMIT;

查询计划：

explain query plan SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN( SELECT 7368859) ORDER BY time_detected DESC LIMIT 1000
"0" "0" "0" "SEARCH TABLE messages USING INDEX time4_idx (type=?)"
"0" "0" "0" "EXECUTE LIST SUBQUERY 1"

explain query plan SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN(7368859) ORDER BY time_detected DESC LIMIT 1000
"0" "0" "0" "SEARCH TABLE messages USING INDEX time3_idx (type=? AND modem_id=?)"

UPD：
在我的情况下，“ modem_id IN（*）”和“ type IN（*）”都可以作为向量的标量，并取决于程序逻辑，因此解决方案是使“ type IN（*）”始终作为向量，例如“在所有查询执行完之后，输入IN（-1，*）'。

Answer 1

type IN (SELECT ...)中的子查询可以返回任意数量的行，因此数据库假定存在许多行，并估计在该列表中查找type更快，而不是相反。

当您知道子查询仅返回一行时，将其写为scalar subquery：

... WHERE type = (SELECT ...)