select
    *
from
    (
    SELECT
        ROW_NUMBER() over (order by  li.id   desc ) rn 
        ,li.* 
    FROM 
        [ListInfo] li  
        left join  UserAccounts ua on ua.UserID = li.UserID 
     where 1=1
     --  and ua.[userstate] != 0 
    ) lis
where
    rn between 1 and 20  

SELECT  *
FROM    ( SELECT    row_number() OVER ( ORDER BY id DESC ) AS rn ,
                    *
          FROM      listinfo
          WHERE     EXISTS ( SELECT 1
                             FROM   useraccounts
                             WHERE  listinfo.userid = userid
                                    AND userstate != 0 )
        ) list
WHERE   rn BETWEEN 1 AND 20

------解决方案--------------------
同意1楼观点把！=0 改成>0 or <0

ps :rn BETWEEN 1 AND 20--> rn>=1 and rn<21 试试。

  

------解决方案--------------------
不用“  rn between 1 and 20 ”
改成top 20 ?　试试

------解决方案--------------------
有效的使用索引
1、避免不可参数化的搜索条件（不可参数化：<>,!=,!>,!<,not exists,not in, not like in,or）
2、避免在where子句列上使用算术运算符(where 字段 * 2=1000)
3、避免where子句列上的函数(where substring(字段,1,1)='A')

------解决方案--------------------
select * 换成所要的列 如select 字段1，字段2


------解决方案--------------------

个人建议
1、尝试取消userstate非聚焦索引，因为userstate应该是状态列，该列的值无非就几个（0,1,2,3）
所以说该列不适合建立索引

2、将ua.[userstate] !=0改成可参数化的搜索条件（=,>,>=,<,<=和between）