這是我的表定義：
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.currentUserBlockedThisUserTsCreated is null
and User.thisUserBlockedCurrentUser = 0
and User.username != ''
union
select User.*
from User
where recentSearchedTsCreated is not null
and User.currentUserBlockedThisUserTsCreated 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
所以使用了索引，但是order by中仍然涉及到一個scan和一個b-tree。我嘗試通過添加更多索引來擺脫它們，但我沒有讓它作業。
擺脫 的索引的任何想法scan？
uj5u.com熱心網友回復：
您的查詢可以簡化為：
SELECT *
FROM User
WHERE (friends = 1 OR recentSearchedTsCreated IS NOT NULL)
AND currentUserBlockedThisUserTsCreated IS NULL
AND thisUserBlockedCurrentUser = 0
AND username <> ''
ORDER BY CASE
WHEN friends = 1 THEN -2
WHEN recentSearchedTsCreated IS NOT NULL THEN -1
ELSE searchScreenScore
END,
username;
查詢計劃將是：
SEARCH User USING INDEX User_X (thisUserBlockedCurrentUser=?)
USE TEMP B-TREE FOR ORDER BY
我看不出如何在ORDER BY子句中比 B-TREE 更好，因為您使用自定義運算式進行排序。
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/511654.html