日期:2014-05-17  浏览次数:20602 次

sql语句优化问题. 快速结贴.
老规矩, 先上sql. 
SQL code

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  



读取用户清单的.
useraccounts表(userid, userstate,....) 4w. userid聚焦索引, userstate非聚焦索引
listinfo表(id,userid,.....) 500w. id聚焦索引, userid非聚焦索引.

中间注释的 and ua.[userstate] != 0 部分. 如果不注释, 运行时间600ms. 注释掉运行时间60ms.

如何优化?

------解决方案--------------------
如果 ua.[userstate] 不会出现 负数,则改为 ua.[userstate]>=1
ua.[userstate] !=0 这种方式不会用到索引。用的是扫描的方式。所以慢
------解决方案--------------------
SQL code
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)