V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
0x19921213
V2EX  ›  程序员

SQL 大佬来救命

  •  1
     
  •   0x19921213 · 312 天前 · 5313 次点击
    这是一个创建于 312 天前的主题,其中的信息可能已经有所发展或是发生改变。
    select * from 
    (select rownum as xh, t.* from 
    (
    	select * from (
    	SELECT
    			* 
    		FROM
    			q_materialfusion 
    			
    			where sjbj = '0' 
    			and ( instr(mc, '1') > 0 
    			OR instr(ssxmmc, '1') > 0 
    		  OR instr(ssxtzc, '1') > 0
    	    OR instr(xinghao, '1') > 0
          OR instr(gg, '1') > 0)
    			order by nf desc, mc asc
    	) where  rownum < 501
    ) t) where  xh >= 1;
    

    这段 SQL 有优化空间吗,数据 200W ,返回需要 2 秒。

    第 1 条附言  ·  311 天前

    是 Oracle 数据库,rownum 很特殊,用过 Oracle 的可能理解为什么嵌套这么多层。instr 性能确实比 like 模糊查询好很多。另外附上执行计划

    Id Operation Name Rows Bytes Cost (%CPU) Time
    0 SELECT STATEMENT 1000 2911K 10330 (5) 00:02:04
    * 1 VIEW 1000 2911K 10330 (5) 00:02:04
    2 COUNT
    3 VIEW 1000 2898K 10330 (5) 00:02:04
    * 4 COUNT STOPKEY
    5 VIEW 452K 1280M 10330 (5) 00:02:04
    * 6 SORT ORDER BY STOPKEY 452K 58M 10330 (5) 00:02:04
    * 7 TABLE ACCESS FULL Q_MATERIALFUSION 452K 58M 10330 (5) 00:02:04
    第 2 条附言  ·  311 天前
    确实是军工项目,表结构无法修改了 Oracle 还是 10g 版本:)
    接手优化这个 sql 很蛋疼
    66 条回复    2023-05-24 14:16:29 +08:00
    8355
        1
    8355  
       312 天前
    那么压力给到楼下的大佬, 我血压上来了.
    tramm
        2
    tramm  
       312 天前
    你这看着都头疼,看看楼下怎么说
    BeautifulSoap
        3
    BeautifulSoap  
       312 天前
    问了 chatgpt ,它建议我用 LIKE
    dizheyoulan
        4
    dizheyoulan  
       312 天前
    不懂 sql ,问了 chatgpt ,它说改成这样
    ```
    SELECT *
    FROM (
    SELECT ROWNUM AS xh, t.*
    FROM (
    SELECT *
    FROM q_materialfusion
    WHERE sjbj = '0'
    AND '1' IN (mc, ssxmmc, ssxtzc, xinghao, gg)
    ORDER BY nf DESC, mc ASC
    ) t
    WHERE ROWNUM < 501
    )
    WHERE xh >= 1;
    ```
    smallWang
        5
    smallWang  
       312 天前
    小白蹲一个 sql 大佬学习学习
    raysonlu
        6
    raysonlu  
       312 天前
    没有汇聚统计的逻辑,套这么多层干嘛?
    siweipancc
        7
    siweipancc  
       312 天前 via iPhone   ❤️ 1
    这查询,我只能用哇塞来形容。上全文搜索吧
    sivacohan
        8
    sivacohan  
       312 天前   ❤️ 1
    SELECT t.rownum as xh, t.*
    FROM q_materialfusion as t
    where t.sjbj = '0'
    and t.rownum < 501
    and t.xh >= 1
    and (instr(t.mc, '1') > 0
    OR instr(t.ssxmmc, '1') > 0
    OR instr(t.ssxtzc, '1') > 0
    OR instr(t.xinghao, '1') > 0
    OR instr(t.gg, '1') > 0)
    order by t.nf desc, t.mc asc;

    这么写不可以吗?为什么要用多个子查询?
    如果这么写可以,那就注意一下除了使用 instr 的部分其他查询条件最好有组合索引。
    samv2
        9
    samv2  
       312 天前
    where sjbj = '0' and concat(mc, ssxmmc, ssxtzc, xinghao, gg) like '%1%'
    zjsxwc
        10
    zjsxwc  
       312 天前   ❤️ 1
    SELECT
    rownum as xh, *
    FROM
    q_materialfusion

    where sjbj = '0'
    and (
    instr(mc, '1') > 0
    OR instr(ssxmmc, '1') > 0
    OR instr(ssxtzc, '1') > 0
    OR instr(xinghao, '1') > 0
    OR instr(gg, '1') > 0
    )
    and rownum < 501
    and rownum >= 1

    order by nf desc, mc asc
    JC1027
        11
    JC1027  
       312 天前
    ```
    SELECT
    rownum as xh,
    t.*
    FROM (
    SELECT *
    FROM q_materialfusion
    WHERE sjbj = '0'
    AND (
    mc LIKE '%1%'
    OR ssxmmc LIKE '%1%'
    OR ssxtzc LIKE '%1%'
    OR xinghao LIKE '%1%'
    OR gg LIKE '%1%'
    )
    ORDER BY nf DESC, mc ASC
    )
    WHERE rownum BETWEEN 1 AND 500;
    ```
    samv2
        12
    samv2  
       312 天前
    @samv2 #9 其实不确定算不算优化,手动狗头
    bugsystem
        13
    bugsystem  
       312 天前
    通意千问说改成这样
    SELECT
    t.rownum as xh,
    t.*
    FROM
    (
    SELECT
    *
    FROM
    q_materialfusion
    WHERE sjbj = '0'
    AND ( instr(mc, '1') > 0
    OR instr(ssxmmc, '1') > 0
    OR instr(ssxtzc, '1') > 0
    OR instr(xinghao, '1') > 0
    OR instr(gg, '1') > 0)
    ORDER BY
    nf DESC,
    mc ASC
    ) t
    WHERE
    t.rownum >= 501
    zjsxwc
        14
    zjsxwc  
       312 天前
    yulgang
        15
    yulgang  
       312 天前
    >> ChatGPT 建议:

    根据您提供的 SQL 查询语句,以下是一些可能的优化建议:

    >1.创建索引:确保在 q_materialfusion 表上为用于过滤和排序的列( sjbj 、mc 、ssxmmc 、ssxtzc 、xinghao 、gg 、nf )创建索引。索引可以大大提高查询性能,特别是在大型数据集上。

    >2.使用 UNION 替代 OR 条件:将 OR 条件改为使用 UNION 连接多个查询,每个查询只包含一个条件。这样可以更好地利用索引,提高查询性能。例如:

    ```
    SELECT *
    FROM q_materialfusion
    WHERE sjbj = '0' AND instr(mc, '1') > 0
    ORDER BY nf DESC, mc ASC
    UNION
    SELECT *
    FROM q_materialfusion
    WHERE sjbj = '0' AND instr(ssxmmc, '1') > 0
    ORDER BY nf DESC, mc ASC
    -- 添加其他条件的 UNION 查询
    LIMIT 500;

    ```

    >3.分页查询:如果您只需要返回前几行结果,可以使用分页查询,而不是检索全部数据。通过使用 ROWNUM (或类似的机制)进行分页,您可以避免一次性检索所有数据。例如,对于前 100 条结果:

    ```
    SELECT *
    FROM (
    SELECT ROWNUM AS xh, t.*
    FROM (
    SELECT *
    FROM (
    SELECT *
    FROM q_materialfusion
    WHERE sjbj = '0'
    AND (instr(mc, '1') > 0
    OR instr(ssxmmc, '1') > 0
    OR instr(ssxtzc, '1') > 0
    OR instr(xinghao, '1') > 0
    OR instr(gg, '1') > 0)
    ORDER BY nf DESC, mc ASC
    )
    WHERE ROWNUM < 501
    ) t
    )
    WHERE xh BETWEEN 1 AND 100;

    ```

    >4.批量处理:如果可能,可以考虑将大型查询拆分为多个较小的查询,并以批处理方式处理数据。这样可以减少单个查询的复杂性和负载,并且更容易进行并行处理。

    请注意,优化查询性能是一个复杂的过程,需要结合实际数据和数据库配置进行调整。建议在生产环境中进行测试,并根据实际情况进行调整。
    raysonlu
        16
    raysonlu  
       312 天前   ❤️ 11
    我貌似第一次感受到论坛评论被 ai 污染的现象
    locochen
        17
    locochen  
       312 天前
    确定的限制条件可以放在最里面.

    带入的查询条件,看是否可以通过语法解释后进入最里层的 select.这样就不会全表扫描.
    magicyao
        18
    magicyao  
       312 天前
    几个 OR 没法优化,不知道为啥要这么设计,外面两层 select 都可以去掉,不过意义不大
    0914xc
        19
    0914xc  
       312 天前
    找了半天,没找到微软小冰。
    NoKey
        20
    NoKey  
       312 天前
    现在大家有问题,都是第一个问 AI 么🤣🤣🤣
    lisonfan
        21
    lisonfan  
       312 天前
    来自 Github Copilot Chat 的回复

    GitHub Copilot: 这段 SQL 语句看起来已经进行了一些优化,但是还有一些可以尝试的优化方法。以下是一些可能的优化建议:

    1. 索引优化:确保表中的所有列都有索引,特别是用于筛选和排序的列。可以使用 `explain` 命令来查看查询计划,以确定是否使用了索引。

    2. 分页优化:如果只需要返回前几行结果,可以使用 `limit` 子句来限制返回的行数,而不是使用 `rownum` 子查询。这样可以避免在子查询中生成大量的行,从而提高查询性能。

    3. 避免使用 `OR`:`OR` 操作符通常会导致查询性能下降,因为它需要对多个条件进行计算。可以尝试将多个条件拆分为多个查询,然后使用 `union` 运算符将结果合并。

    4. 避免使用 `select *`:只选择需要的列,而不是使用 `select *` 选择所有列。这可以减少查询返回的数据量,从而提高查询性能。

    下面是一个可能的优化版本的 SQL 语句:

    ```
    SELECT
    nf,
    mc
    FROM
    q_materialfusion
    WHERE
    sjbj = '0'
    AND (
    instr(mc, '1') > 0
    OR instr(ssxmmc, '1') > 0
    OR instr(ssxtzc, '1') > 0
    OR instr(xinghao, '1') > 0
    OR instr(gg, '1') > 0
    )
    ORDER BY
    nf DESC,
    mc ASC
    LIMIT 500;
    ```

    请注意,这只是一个可能的优化版本,具体的优化方法可能因数据库结构和数据分布而异。建议您在实际环境中测试不同的优化方法,以确定哪种方法最适合您的情况。
    huiyadanli
        22
    huiyadanli  
       312 天前
    instr 性能优于 like 吗? 你这几个 or 条件的结果能否预处理成新增字段,然后通过这个字段来查询?
    registerrr
        23
    registerrr  
       312 天前
    @NoKey 关键是 AI 有时候是真的行
    idealhs
        24
    idealhs  
       312 天前
    有人已经在用 GPT 爽出代码了
    有人还在评论区指点 AI 污染
    pengtdyd
        25
    pengtdyd  
       312 天前
    很多人在绞尽脑汁的优化 sql ,难道你就没有想过是物理模型设计的问题吗。表结构设计的一团糟,直接导致后续 sql 难写,这种现象好像很普遍。
    Alias4ck
        26
    Alias4ck  
       312 天前
    什么数据库?
    c6h6benzene
        27
    c6h6benzene  
       312 天前
    就我在好奇 xinghao 是“型号”还是“行号”吗?
    ThreeK
        28
    ThreeK  
       312 天前
    理解不了这 sql 想干嘛。就一张表 ,各个针对结果集的 where 有什么不能直接写到最里边的,非要套这么多层。
    8355
        29
    8355  
       312 天前
    @NoKey 关键是楼主这个 sql 代码看着非常烧脑
    popvlovs
        30
    popvlovs  
       312 天前
    @c6h6benzene 也可能是“序号”
    popvlovs
        31
    popvlovs  
       312 天前
    @c6h6benzene 看岔了,我以为你说的“xh”,不过 OP 这个 SQL 的参数风格,和我以前做军工的时候好像,真是不堪回首 >_<
    RunningMars
        32
    RunningMars  
       312 天前   ❤️ 1
    干嘛不这样:
    SELECT
    rownum AS xh,*
    FROM
    q_materialfusion
    WHERE
    sjbj = '0'
    AND (
    instr( mc, '1' ) > 0
    OR instr( ssxmmc, '1' ) > 0
    OR instr( ssxtzc, '1' ) > 0
    OR instr( xinghao, '1' ) > 0
    OR instr( gg, '1' ) > 0
    )
    AND rownum >= 1
    AND rownum < 501
    ORDER BY
    nf DESC,
    mc ASC

    然后去优化索引。
    qqqq09047
        33
    qqqq09047  
       312 天前   ❤️ 1
    为什么套了这么多层?
    SELECT
    * ,
    rownum as xh
    FROM
    q_materialfusion
    where
    sjbj = '0'
    and rownum>=1
    and rownum < 501
    and (
    instr(mc, '1') > 0
    OR instr(ssxmmc, '1') > 0
    OR instr(ssxtzc, '1') > 0
    OR instr(xinghao, '1') > 0
    OR instr(gg, '1') > 0
    )
    order by nf desc, mc asc
    akira
        34
    akira  
       312 天前
    1. 因为你只需要最近的 500 条,考虑下在最里面一层加一下别的限制,例如时间,或者 id 编号啥的,减少检索范围。

    2. 这几个字段是啥玩意来的,如果是单字符,就直接写相等判断,那就可以走索引了。 如果都是多字符,那做个临时字段把这几个字符串都合并,然后用一句就可以了。


    and ( instr(mc, '1') > 0
    OR instr(ssxmmc, '1') > 0
    OR instr(ssxtzc, '1') > 0
    OR instr(xinghao, '1') > 0
    OR instr(gg, '1') > 0)

    200w 数据的话,处理了这 2 个点基本上就够了
    ChadGPT
        35
    ChadGPT  
       312 天前
    先把所有 OR 改成 UNION 写法试试
    sadfQED2
        36
    sadfQED2  
       312 天前 via Android
    你说下数据库呀,你如果是传统的关系型数据库,我感觉天王老子来了也没辙。你要不看看实时计算引擎?找个支持倒排索引的实时计算引擎,你这点数据量都不叫事
    zypy333
        37
    zypy333  
       312 天前
    所以 AI 写的对吗
    chonanlee
        38
    chonanlee  
       312 天前
    为啥不从源头上解决数据问题呢?比如把你需要的这个直接在写入的地方直接做个标志位,然后对这个标志位加索引。如果要解决 like 或者 instr ,这种治标不治本
    fxxkgw
        39
    fxxkgw  
       312 天前 via Android
    explain 看下
    另外 like%%这种是没法用索引的 大概率更慢
    另外 可以搜下 数据库优化 美团 能找到一篇美团技术文章讲数据库优化 供参考
    season8
        40
    season8  
       312 天前
    这个 rownum 没看到函数, 是字段?如果是字段直接放最里面条件里面不就优化了?
    xuanbg
        41
    xuanbg  
       312 天前
    SELECT * FROM q_materialfusion
    where sjbj = '0'
    and ( instr(mc, '1') > 0 OR instr(ssxmmc, '1') > 0 OR instr(ssxtzc, '1') > 0 OR instr(xinghao, '1') > 0 OR instr(gg, '1') > 0)
    order by nf desc, mc asc limit 500;
    xuanbg
        42
    xuanbg  
       312 天前
    OP 你这个 sql 是查询 mc/ssxmmc/ssxtzc/xinghao/gg 这 5 个字段中的包含 1 的记录,必然是要全表扫描的。所以就没得优化。但去掉外面套的两层,至少磁盘 IO 是可以降低很多的……
    ijyuqi
        43
    ijyuqi  
       312 天前 via iPhone
    个人的理解,应该是要从 q_materialfusion 表中按 where 的条件筛选排序并生成序列号,取排序前 500 条数据,只是最外面一层,感觉是多余的。
    andytao
        44
    andytao  
       312 天前
    非 DBA ,一直在开发 [袋鼠数据库工具( https://www.datatable.online/zh/)] ,楼主可以从几个点做优化:
    1 、优化星号,用具体的字段替代
    2 、为排序字段建立索引;
    3 、查询条件字段也建议为索引;
    jdOY
        45
    jdOY  
       311 天前
    拆成简单查询,代码里面聚合,说不定还能到 1 秒内
    huigeer
        46
    huigeer  
       311 天前 via iPhone   ❤️ 7
    竟然没有一个人说要看看 explain 的执行结果的……
    nianlifeixing
        47
    nianlifeixing  
       311 天前 via iPhone
    子查询,一下子血压就上来了
    way2explore2
        48
    way2explore2  
       311 天前
    @huigeer 正解,

    我举着用 or 应该会有帮助,但不能确定是否 2 秒
    sanestays
        49
    sanestays  
       311 天前
    嵌套的 rownum 和 limit 啥区别 没必要套那么多层
    fredcc
        50
    fredcc  
       311 天前
    以下内容由 copilot chat 答复:

    这段 SQL 查询语句可以进行如下优化:

    使用 UNION 操作符代替多个 OR 条件,可以提高查询效率。
    将 instr 函数的参数改为常量,可以避免在查询时进行函数计算,提高查询效率。
    将 order by 子句中的 nf desc 改为 nf ,可以避免进行排序操作,提高查询效率。
    将 rownum 的限制条件放到子查询中,可以避免在查询时进行排序操作,提高查询效率。
    优化后的 SQL 查询语句如下:

    SELECT *
    FROM (
    SELECT
    ROW_NUMBER() OVER (ORDER BY nf, mc) AS xh,
    t.*
    FROM (
    SELECT * FROM q_materialfusion WHERE sjbj = '0' AND 1 = 0
    UNION
    SELECT * FROM q_materialfusion WHERE sjbj = '0' AND instr(mc, '1') > 0
    UNION
    SELECT * FROM q_materialfusion WHERE sjbj = '0' AND instr(ssxmmc, '1') > 0
    UNION
    SELECT * FROM q_materialfusion WHERE sjbj = '0' AND instr(ssxtzc, '1') > 0
    UNION
    SELECT * FROM q_materialfusion WHERE sjbj = '0' AND instr(xinghao, '1') > 0
    UNION
    SELECT * FROM q_materialfusion WHERE sjbj = '0' AND instr(gg, '1') > 0
    ) t
    WHERE rownum < 501
    )
    WHERE xh >= 1;

    在这个优化后的查询语句中,使用了 ROW_NUMBER() 函数来生成行号,避免了使用 rownum 进行排序操作。同时,使用了 UNION 操作符代替了多个 OR 条件,避免了在查询时进行函数计算。最后,将 rownum 的限制条件放到子查询中,避免了在查询时进行排序操作。
    shakoon
        51
    shakoon  
       311 天前
    @idealhs #24 主要是楼上所有贴出来 ai 答的,无一不是一坨屎,没有一个是真正有效的
    blackmirror
        52
    blackmirror  
       311 天前
    你这条件很多不走索引吧
    funcNVidia
        53
    funcNVidia  
       311 天前
    看了楼主贴的 SQL ,我血压高了。看了楼下一些贴 AI 的回答,我血压更高了。
    500
        54
    500  
       311 天前
    看 T-SQL 也就最里面的子查询有用,功能大概是一个多字段模糊查询?

    可以给 rownum 加聚集索引,mc 、ssxmmc 、ssxtzc 、xinghao 、gg 加非聚集索引;

    如果多字段模糊查询的条件都是一样的,也可以加一个专用列,这个列的内容是 mc 、ssxmmc 、ssxtzc 、xinghao 、gg 几个列的拼接,并为这个列建立非聚集索引,再使用该列替换之前的多重 OR 判断,将 INSTR 替换为 LIKE ;

    更进一步,可以将查询语句进行封装,分段并发查询(例如每 10 万条数据为 1 段,同时进行 20 个并发查询),最后合并所有查询的结果。
    go522000
        55
    go522000  
       311 天前
    看这贴子的 AI 的回复,大多数都是错误的。看来程序被 AI 取代的日子还有一些时间。
    someday3
        56
    someday3  
       311 天前
    你这完全没必要嵌套啊,直接展平就行,看看哪个条件过滤掉的数据更多就放在前面,用 and 套在一起。

    比如先 where rownum >= 1 and rownum < 501 。然后再 and 你那一堆。
    0x19921213
        57
    0x19921213  
    OP
       311 天前
    sjbj 字段只有 0 和 1 ,而且 90% 是 0 ,所以肯定要走全表扫描了 :(
    有没有其他优化思路呢
    yc8332
        58
    yc8332  
       311 天前
    你这个看起来没得优化。考虑其他方案吧
    AmaQuinton
        59
    AmaQuinton  
       311 天前
    @qqqq09047 #33 rownum 执行的优先级在 order by 之前, 所以 rownum < 501 代表随机取了 500 行数据, 再进行 order by nf desc, mc asc 排序后得到的数据,


    楼主这个 SQL 应该是排序后再取前 500 行数据
    vipdog73
        60
    vipdog73  
       311 天前
    ImSealin
        61
    ImSealin  
       311 天前
    下班前看到这个 SQL 我血压也上来了..
    JKeita
        62
    JKeita  
       311 天前
    看到这 SQL 真是会脑溢血
    lff0305
        63
    lff0305  
       310 天前 via Android
    Oracle 这个版本能创建(或者环境允许创建) instr 的函数索引么,试试函数索引
    chonanlee
        64
    chonanlee  
       310 天前 via Android
    加个字段,建个 Oracle 的 trigger ,更新或者新增的时候用 trigger 去更新新加的字段
    liuhuan475
        65
    liuhuan475  
       310 天前
    不能改表结构 能不能新建表啊
    ruanimal
        66
    ruanimal  
       310 天前
    @vipdog73 看了贴 ai 图的,血压更高了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2763 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 12:49 · PVG 20:49 · LAX 05:49 · JFK 08:49
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.