服务器SQL2005查询分页语句你理解了么(二)
聚焦索引一个表只能有一个，如何解决目前的问题呢？
首先我们分析下
select * from (select *,row_number() over(order by ID asc) as RowNum from H31_DHT_TYPE_101_1) as a where RowNum between 2000000 and 2000200
这条语句的对与错的问题，
1.select *,row_number() over(order by ID asc) as RowNum from H31_DHT_TYPE_101_1表明了需要查询所有字段和列全部搜索出来了，数据小的时候你不会发现，但数据量大的时候就知道了，因为他表明了获取所有字段的数据进行按ID排序，虽然ID也索引了。
2.上面需要很长时间，然后再去定位查询感觉时间很慢。
所以个人的想法是先不用查询出所有字段的数据，
WITH temp AS (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS 'RowNumber' FROM H31_DHT_TYPE_101_1)
这条语句查询出来的结果需要1到2秒，比起上面的基本上需要很长时间算是好的了，
然后再从这里面取ID号，这样就节约了不少时间，
WITH temp AS (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS 'RowNumber' FROM H31_DHT_TYPE_101_1)
select ID,hashKey,recvTime,updateTime,keyContent,keyType,recvTimes,fileCnt,filetotalSize,Detail,viewTimes,viewLevel from H31_DHT_TYPE_101_1 where id in (select id from temp where RowNumber between 2000000 and 2000200)
分析使用的时间在2秒左右
[2013-10-14 2:43:17]: Addindex[1011]:2577092 DBTime:1453ms IndexTime:32ms
[2013-10-14 2:43:19]: Addindex[1011]:2577292 DBTime:1515ms IndexTime:32ms
[2013-10-14 2:43:20]: Addindex[1011]:2577492 DBTime:1578ms IndexTime:31ms
[2013-10-14 2:43:22]: Addindex[1011]:2577692 DBTime:1687ms IndexTime:32ms
[2013-10-14 2:43:23]: Addindex[1011]:2577892 DBTime:1422ms IndexTime:31ms
[2013-10-14 2:43:25]: Addindex[1011]:2578092 DBTime:1547ms IndexTime:31ms
[2013-10-14 2:43:27]: Addindex[1011]:2578292 DBTime:1469ms IndexTime:31ms
[2013-10-14 2:43:28]: Addindex[1011]:2578492 DBTime:1516ms IndexTime:31ms
[2013-10-14 2:43:30]: Addindex[1011]:2578692 DBTime:1422ms IndexTime:31ms
[2013-10-14 2:43:36]: Addindex[1011]:2578892 DBTime:6422ms IndexTime:31ms
[2013-10-14 2:43:40]: Addindex[1011]:2579092 DBTime:3938ms IndexTime:31ms
[2013-10-14 2:43:43]: Addindex[1011]:2579292 DBTime:3484ms IndexTime:32ms
[2013-10-14 2:43:48]: Addindex[1011]:2579492 DBTime:4265ms IndexTime:32ms
[2013-10-14 2:43:50]: Addindex[1011]:2579692 DBTime:1750ms IndexTime:31ms