DELPHI盒子
!实时搜索: 盒子论坛 | 注册用户 | 修改信息 | 退出
检举帖 | 全文检索 | 关闭广告 | 捐赠
技术论坛
 用户名
 密  码
自动登陆(30天有效)
忘了密码
≡技术区≡
DELPHI技术
移动应用开发
Web应用开发
数据库专区
报表专区
网络通讯
开源项目
论坛精华贴
≡发布区≡
发布代码
发布控件
文档资料
经典工具
≡事务区≡
网站意见
盒子之家
招聘应聘
信息交换
论坛信息
最新加入: 123glimmer
今日帖子: 19
在线用户: 13
导航: 论坛 -> 数据库专区 斑竹:liumazi,waterstone  
作者:
男 dbyoung (dbyoung) ★☆☆☆☆ -
普通会员
2021/3/20 13:28:15
标题:
求教:SQL 语句优化 浏览:541
加入我的收藏
楼主: 查询某一行记录:

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!
作者:
男 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!
作者:
男 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!
作者:
男 dalas (dalas) ▲▲▲▲▲ -
普通会员
2021/3/21 10:42:00
12楼: 纠正一下:

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

早先测试结果错误,是我的表中没有ID这个字段,用了USER_ID代替,结果跑出200多万条,而楼主只是要排名第5的一条记录。
现在新建了个表,只有 ID, USER_ID,结果跑出来是相同的,耗时差不多。
此帖子包含附件:
PNG 图像
大小: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
此帖子包含附件:
PNG 图像
大小:90.7K
----------------------------------------------
-
作者:
男 adsoft (adsoft) ▲▲▲▲▲ -
注册会员
2021/3/21 19:34:54
16楼: 最好的结果:
此帖子包含附件:
PNG 图像
大小: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!
作者:
男 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楼: 实际的执行计划,几乎没有区别:
此帖子包含附件:
JPEG 图像
大小: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
----------------------------------------------
-
信息
登陆以后才能回复
Copyright © 2CCC.Com 盒子论坛 v2.1 版权所有 页面执行23.4375毫秒 RSS