关于千万级别查询效率
SELECT account.UserID, account.Account,account.LastAccessMsg,account.LastAccessTime,account.CreateTime FROM T_UserAccount account WHERE UserID NOT IN (SELECT DISTINCTROW bill.UserID FROM T_User_Bill bill ) ORDER BY account.CreateTime DESC
我想查在T_User_Bill 中没有的用户，应该怎样效率好点啊？求……T_User_Bill 1000多w的数据 ,T_UserAccount 20W

[解决办法]
不要用 not in

userID 上加索引。

SQL code

select account.* from T_UserAccount account left join T_User_Bill bill on account.UserID = bill.UserID where bill.UserID is null;
[解决办法]
在T_User_Bill 的UserID上加索引


select *
from  T_UserAccount A
where not exists (select 1 from  T_User_Bill  B where A.UserID =B.UserID )
[解决办法]
SQL code
SELECT * FROM T_UserAccount AWHERE not EXISTS(SELECT 1 FROM T_User_Bill B WHERE A.UserID=B.UserID);
[解决办法]
SELECT account.UserID, account.Account,account.LastAccessMsg,account.LastAccessTime,account.CreateTime  
FROM T_UserAccount account  
WHERE not exists (select 1 from T_User_Bill where UserID=account.UserID)