日期:2014-05-18  浏览次数:20546 次

帮忙优化下面这两条sql 语句。
请帮忙,将下面的两条语句,进行最大化的效率提升,让其占用cpu不要太高。谢谢大人们。。。。请帮忙改一下。


SELECT       tbtk.tmid,   tbtk.title,   tbtk.flevel,pointid,fscore,tbtk.ans     FROM   userdata   INNER   JOIN   tbtk   ON   userdata.tmid   =   tbtk.tmid     Where   userdata.paperno   =   126   And   userdata.ksnum   =   1   And   userdata.userid   =   77   And   tbtk.Category   =   36     ORDER   BY     userdata.tmorder,   userdata.dataid

上面这一句,用事件跟踪器的结果如下:
//Reads=16655
//cpu=109

SELECT       tbtk.tmid,   tbtk.title,   tbtk.chapter,a,b,c,d,e,f,tbtk.flevel,pointid,fscore,tbtk.ans       FROM   userdata   INNER   JOIN   tbtk   ON   userdata.tmid   =   tbtk.tmid     Where   userdata.paperno   =   126   And   userdata.ksnum   =   1   And   userdata.userid   =   77   And   tbtk.Category   =   37     ORDER   BY     userdata.tmorder,   userdata.dataid

上面这一句,用事件跟踪器的结果如下:
//Reads=16655
//cpu   =94

------解决方案--------------------
已經ok了...加索引吧
------解决方案--------------------
已经写的很标准了

在索引方面想想办法
------解决方案--------------------
SELECT tbtk.tmid, tbtk.title, tbtk.flevel,pointid,fscore,tbtk.ans FROM userdata INNER JOIN tbtk ON userdata.tmid = tbtk.tmid Where userdata.paperno = 126 And userdata.ksnum = 1 And userdata.userid = 77 And tbtk.Category = 36 ORDER BY userdata.tmorder, userdata.dataid

可以用表别名更好点.
SELECT b.tmid, b.title, b.flevel,pointid,fscore,b.ans FROM userdata a INNER JOIN b ON a.tmid = b.tmid Where a.paperno = 126 And a.ksnum = 1 And a.userid = 77 And b.Category = 36 ORDER BY a.tmorder, a.dataid