SQLite联合查询优化：消除子查询扫描与排序临时B树

表定义 #

create table User (
    userUuid text not null primary key,
    username text not null,
    thisUserBlockedCurrentUser int not null,
    currentUserBlockedThisUserTsCreated int not null,
    searchScreenScore int,
    recentSearchedTsCreated int,
    friends int not null
);

create index User_X on User(thisUserBlockedCurrentUser, friends);

查询语句及执行计划 #

explain query plan
select *
from (select User.*
      from User
      where friends = 1
        and User.currentUserBlockedCurrentUserTsCreated is null
        and User.thisUserBlockedCurrentUser = 0
        and User.username != ''
      union
      select User.*
      from User
      where recentSearchedTsCreated is not null
        and User.currentUserBlockedCurrentUserTsCreated is null
        and User.thisUserBlockedCurrentUser = 0
        and User.username != '')
order by case when friends = 1 then -2 when recentSearchedTsCreated is not null then -1 else searchScreenScore end,
         username;

执行计划输出：

CO-ROUTINE (subquery-2)
COMPOUND QUERY
LEFT-MOST SUBQUERY
SEARCH User USING INDEX User_X (thisUserBlockedCurrentUser=? AND friends=?)
UNION USING TEMP B-TREE
SEARCH User USING INDEX User_X (thisUserBlockedCurrentUser=?)
SCAN (subquery-2)
USE TEMP B-TREE FOR ORDER BY

当前查询已使用索引，但仍存在子查询扫描和排序阶段的临时B树操作。尝试添加更多索引消除这些操作但未成功，请问有什么合适的索引可以消除该扫描操作？

1. 创建覆盖式复合索引，消除子查询扫描 #

两个子查询共享thisUserBlockedCurrentUser = 0、currentUserBlockedCurrentUserTsCreated is null、username != ''这三个过滤条件，再加上各自的专属条件，针对两类场景创建覆盖索引，让数据库无需回表即可获取所有需要的数据，同时直接通过索引过滤结果：

针对「friends=1」的子查询索引

CREATE INDEX User_Friends_Filter ON User(
    thisUserBlockedCurrentUser,
    currentUserBlockedCurrentUserTsCreated,
    friends,
    username,
    searchScreenScore,
    recentSearchedTsCreated,
    userUuid
);

索引前置过滤条件，后续列包含查询返回和排序需要的所有字段，实现覆盖查询，避免额外的表扫描。

针对「recentSearchedTsCreated is not null」的子查询索引

CREATE INDEX User_RecentSearch_Filter ON User(
    thisUserBlockedCurrentUser,
    currentUserBlockedCurrentUserTsCreated,
    recentSearchedTsCreated,
    username,
    searchScreenScore,
    friends,
    userUuid
);

同样以过滤条件开头，覆盖所有查询所需字段，减少数据库读取开销。

2. 替换UNION为UNION ALL（无重复数据时） #

如果两个子查询的结果集不存在重复的userUuid（即同一个用户不会同时满足friends=1和recentSearchedTsCreated is not null），将UNION改为UNION ALL，这样数据库无需创建临时B树去重，直接合并结果集，消除UNION USING TEMP B-TREE操作。

3. 索引匹配排序规则，消除排序临时B树 #

让两个子查询的结果集提前按最终排序规则有序，合并后无需再排序：

针对第一个子查询的排序优化索引

调整索引顺序，让过滤后的结果按username（最终排序第二列）有序：

CREATE INDEX User_Friends_Sorted ON User(
    thisUserBlockedCurrentUser,
    currentUserBlockedCurrentUserTsCreated,
    friends,
    username,
    searchScreenScore,
    recentSearchedTsCreated,
    userUuid
);

针对第二个子查询的排序优化索引

同样让过滤后的结果按username有序：

CREATE INDEX User_RecentSearch_Sorted ON User(
    thisUserBlockedCurrentUser,
    currentUserBlockedCurrentUserTsCreated,
    recentSearchedTsCreated,
    username,
    searchScreenScore,
    friends,
    userUuid
);

数据库可以直接从两个索引中取出有序结果，按-2、-1的权重合并，避免临时B树排序。

4. 合并查询逻辑（可选） #

将两个子查询合并为单查询，用OR连接专属条件，创建对应复合索引：

SELECT *
FROM User
WHERE thisUserBlockedCurrentUser = 0
  AND currentUserBlockedCurrentUserTsCreated IS NULL
  AND username != ''
  AND (friends = 1 OR recentSearchedTsCreated IS NOT NULL)
ORDER BY CASE WHEN friends = 1 THEN -2 WHEN recentSearchedTsCreated IS NOT NULL THEN -1 ELSE searchScreenScore END, username;

对应的复合索引：

CREATE INDEX User_Combined_Filter ON User(
    thisUserBlockedCurrentUser,
    currentUserBlockedCurrentUserTsCreated,
    username,
    friends,
    recentSearchedTsCreated,
    searchScreenScore,
    userUuid
);

这种方式需要测试数据库对OR条件的优化效率，部分场景下能进一步减少临时操作。

内容的提问来源于stack exchange，提问作者J. Doe