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：

当我通过(索引 BY(强制查询使用特定的索引时，它会在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;

是否可以像匹配多个国家/地区的第一次查询一样内获得响应？

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;

这可能会解决您的问题。