sqlite - SQLite在将OR条件与WHERE子句一起使用时花了太多时间

Question

我有一个具有1亿行的SQLite数据库（表）。

表架构：

CREATE TABLE users
(
    u_id      VARCHAR(32) PRIMARY KEY,
    u_status  BOOLEAN,
    u_country VARCHAR(2),
    u_score   INT
);

CREATE INDEX dex_sta ON users (u_status);
CREATE INDEX dex_cou ON users (u_country);
CREATE INDEX dex_sco ON users (u_score);

CREATE INDEX dex_sns ON users (u_status, u_score);
CREATE INDEX dex_mul ON users (u_status, u_country, u_score);

当我在不选择多个国家的情况下使用以下简单查询时，我在15毫秒内得到了答复。

SELECT * FROM users WHERE u_status = 1 AND u_country = 'US' ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

SELECT * FROM users WHERE u_status = 1 AND u_country = 'IN' ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

问题从下面的查询开始

当我尝试使用OR条件匹配多个国家/地区时，查询需要60秒才能响应。

SELECT * FROM users WHERE u_status = 1 AND (u_country = 'US' OR u_country = 'IN') ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

查询改进1：

当我通过（INDEXED BY）强制查询使用特定的INDEX时，它在1秒钟内响应

SELECT * FROM users INDEXED BY dex_mul WHERE u_status = 1 AND (u_country = 'US' OR u_country = 'IN') ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

查询改进2：

当我使用UNION ALL查询时，它需要500毫秒来响应

SELECT * FROM users WHERE u_status = 1 AND u_country = 'US'
UNION ALL
SELECT * FROM users WHERE u_status = 1 AND u_country = 'IN'
ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

是否可以在<50毫秒内获得响应，就像匹配多个国家的第一个查询一样？

Answer 1

在查询中使用OR条件不允许使用索引。您可以将查询转换为UNION ALL子句-

SELECT *
FROM users
WHERE u_status = 1 AND u_country = 'US'
UNION ALL
SELECT *
FROM users
WHERE u_status = 1 AND u_country = 'IN'
ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

这可能会解决您的问题。