盒子论坛 v2.1

查询某一行记录：

select * from
(
select ROW_NUMBER() OVER(order by id) as PosIndex, * from Table
) A
where A.PosIndex=5

是否可以进行优化，不触发全表查询？
望高手指点。
----------------------------------------------
武汉天气不好

作者：

cdfs (cdfs)	★☆☆☆☆	-
	普通会员

2021/3/20 13:46:46

1楼：

A.PosIndex 上没索引？
----------------------------------------------
-

作者：

dbyoung (dbyoung)	★☆☆☆☆	-
	普通会员

2021/3/20 13:52:35

2楼：

PosIndex 来自于 ROW_NUMBER 系统内置函数，索引不知道有没有。
总之这样查询很慢。

这样写又不允许：
select ROW_NUMBER() OVER(order by id) as PosIndex, * from Table where PosIndex=5
----------------------------------------------
武汉天气不好

作者：

nolem (nolem)	★☆☆☆☆	-
	普通会员

2021/3/20 14:48:36

3楼：

你用 ROW_NUMBER 又沒任何條件,當然是一定要full table scan 才能取得row number啊,
你要先想辦法縮減條件資料在裡面,或改變你的方式.
----------------------------------------------
-

作者：

iamdream (银河恒久远，梦想无止境！)	★☆☆☆☆	-
	大贡献会员

2021/3/20 17:51:25

4楼：

楼主还是说说你的目的。
----------------------------------------------
-广袤璀璨的银河,永无止境的梦想(梦无止境游银河) 博客挂了……

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/3/20 18:18:28

5楼：

3楼正解
----------------------------------------------
-

作者：

adsoft (adsoft)	★☆☆☆☆	-
	普通会员

2021/3/20 19:19:32

6楼：

SELECT
  PosIndex = 5
, *
FROM Table
WHERE id = (
  SELECT TOP(1) id
  FROM (
    SELECT TOP(5)
      id
    FROM Table
    ORDER BY id
    ) a
  ORDER BY id DESC)
----------------------------------------------
-

作者：

dbyoung (dbyoung)	★☆☆☆☆	-
	普通会员

2021/3/20 21:08:57

7楼：

楼上高手，谢谢谢谢！！！
----------------------------------------------
武汉天气不好

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/3/21 1:17:22

8楼：

特地拿个800万数据表来测试了下，6楼的代码耗时比楼主的多一倍，结果还是错的。
----------------------------------------------
-

作者：

emailx45 (emailx45)	▲▲▲▲△	-
	普通会员

2021/3/21 8:06:15

9楼：

hi @Dalas

can you try in your 8mi records with this SQL by Interbase 2020?

SELECT Id, MyPosIndex
FROM table001
WHERE MyPosIndex=5
ORDER BY id
ROWS 1

hug
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3

作者：

emailx45 (emailx45)	▲▲▲▲△	-
	普通会员

2021/3/21 8:12:18

10楼：

Firebird Window Functions:

https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-windowfuncs.html
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/3/21 9:49:00

11楼：

@ emailx45 (emailx45)

我没用过 Interbase，甚至都不知道怎么用它。
我用的是 SQL SERVER 2019，直接在 Microsoft SQL Server Management Studio 18里测试的。
----------------------------------------------
-

作者：

emailx45 (emailx45)	▲▲▲▲△	-
	普通会员

2021/3/21 10:10:55

12楼：

OK! my SQL expression just result in "1" row if exist many others with value = 5 ---> ROWS 1 is used in Interbase to catch on 1 row if the resulset is more that 1

Select * from Table ROWS 4 --> just 4 records

in SQL Servers exists this clause? ROWS ???
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/3/21 10:42:00

12楼：

纠正一下：

6楼的结果是对的，不过耗时差不多。

早先测试结果错误，是我的表中没有ID这个字段，用了USER_ID代替，结果跑出200多万条，而楼主只是要排名第5的一条记录。
现在新建了个表，只有 ID, USER_ID,结果跑出来是相同的，耗时差不多。

此帖子包含附件:

大小:41.5K

----------------------------------------------
-

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/3/21 11:14:51

13楼：

@emailx45 (emailx45)

你9楼的代码，我看字面的理解，是根据ID排序后取第一行，用 sql server 来写，应该是这样：

SELECT top 1 Id, MyPosIndex
FROM table001
WHERE MyPosIndex=5
ORDER BY id

不知道我的理解是否正确，我没用过Interbase，不清楚它的语法，但看着跟 oracle 的 rownum=1 挺像
----------------------------------------------
-

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/3/21 11:46:39

14楼：

Select * from Table ROWS 4 --> just 4 records

in SQL Servers exists this clause? ROWS ???

sql server 这样写：

select top 4 * from Table --> 取前4行
----------------------------------------------
-

作者：

adsoft (adsoft)	★☆☆☆☆	-
	普通会员

2021/3/21 14:40:31

15楼：

@dalas

此帖子包含附件:

大小:90.7K

----------------------------------------------
-

作者：

adsoft (adsoft)	★☆☆☆☆	-
	普通会员

2021/3/21 19:34:54

16楼：

最好的结果：

此帖子包含附件:

大小:54.2K

----------------------------------------------
-

作者：

emailx45 (emailx45)	▲▲▲▲△	-
	普通会员

2021/3/22 2:18:06

17楼：

thanks @Dalas

me too, I dont know so much about MSSQL server commands.
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3

作者：

nolem (nolem)	★☆☆☆☆	-
	普通会员

2021/3/22 4:36:54

18楼：

@dalas
他原始sql 是先對id排序後再取出rownum=5 的資料
拉出plan 應該就很清楚了

改寫的寫法, 你一樣是order by id 取top 5 ~
一樣可以你試著拉出plan看看
你也許變快的原因只是因為mssql memory cache 喔~

@dbyoung
如果你的id 本身為btree index ,因為是full index fast scan .
自然會優化改善取回速度集資料量,否則一樣都必須進行full table scan

所以我認知除非釐清原po的商業邏輯不然很難跟他說怎麼改寫喔~
----------------------------------------------
-

作者：

bluestorm8 (bluestorm)	▲▲△△△	-
	普通会员

2021/3/23 11:01:30

19楼：

WITH
a AS (SELECT TOP 5 * FROM test ORDER BY id),
b AS (SELECT ROW_NUMBER() OVER(ORDER BY id) as PosIndex, * FROM a)
SELECT * FROM b WHERE PosIndex=5;

最重要的优化不是语句优化，而是索引优化：id上需建一个索引（primary key或普通index），这样查询起来就快很多了。
----------------------------------------------
-

作者：

bluestorm8 (bluestorm)	▲▲△△△	-
	普通会员

2021/3/23 11:07:59

20楼：

实际的执行计划，几乎没有区别：

此帖子包含附件:

大小:97.9K

----------------------------------------------
-

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/3/23 21:40:31

21楼：

其实两种写法还是有区别的。
我的测试环境不建索引，没有主键，数据量746万行，字段25个，其中nvarchar字段20个，int 字段5个，用于排序的id字段为nvarchar。

select a.*
from ( select *,
          row_number() over(order by id) rn
       from   Table
     ) a
where  rn=5
这个代码，第一次跑了2分41秒，第二次跑了1分50秒。

如果这样改写：
select a.*
from    Table a
join (
select a.*
from ( select id,  --关键在这里，只取一个字段
          row_number() over(order by id) rn
       from   Table
     ) a
where  rn=5
) b on a.id=b.id
第一次跑了51秒，第二次跑了45秒。

以下不用row_number

select top 1 *
from ( select top 5 *
       from   Table
     order by id
       ) a
第一次跑28秒，第二次跑了25秒。

改用这个方法：
select a.*
from   Table a
join (
select top 1 id
from ( select top 5 id
       from   Table
     order by id
      ) a
) b on a.id=b.id

第一次跑36秒，第二跑了28秒。

可见row_number效率还是有点低。
从上面表中提取两个字段，一个int，一个 nvarchar，新建一个表，行数不变，仍然746万，以int字段排序，
把新表代入上面4个代码，基本差不多，最多2秒，最少1.6秒，相差几乎可以忽略。之所以每个代码跑两次，就是想看看18楼那位兄弟说的 memory cache 影响有多大。

我在12楼的截图，测试的就是两个字段的表，就是这个结果，字段少，跑数时间几乎没差别。
----------------------------------------------
-

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/3/23 21:58:21

22楼：

有时代码简短，并不一定就是最高效的，实际瓶颈还是在磁盘IO上，小数据无所谓，数据量大的还是得考虑代码的优化。

楼主这样的没有数据筛选条件，只能是全表排序。
----------------------------------------------
-

作者：

dbyoung (dbyoung)	★☆☆☆☆	-
	普通会员

2021/3/24 7:34:59

23楼：

@dalas:
你用 top 取代 row_number 的查询写法是不正确的。adsoft 的写法是正确的。

我只是简化了问题。实际的查询要复杂些。有条件，有关联表查询。等等。
我知道了 Top、limit、join 等就可以了。就可以优化查询了。

谢谢各位高手指点。
----------------------------------------------
武汉天气不好

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/3/24 8:07:27

24楼：

@dbyoung (dbyoung)

我只是在测试这4种写的效率问题，没按你的要求来了，要取你的结果，只需top 1多加一个 order by id desc 就可以了:

select top 1 id
from ( select top 5 id
       from   Table
     order by id
      ) a
)
order by id desc
----------------------------------------------
-

信息

登陆以后才能回复