首页 注册 登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
• 请不要在回答技术问题时复制粘贴 AI 生成的内容
0x19921213
V2EX  ›  程序员

SQL 大佬来救命

  •  1      
  •   0x19921213 · 2023-05-22 14:36:04 +08:00 · 5726 次点击
    这是一个创建于 585 天前的主题，其中的信息可能已经有所发展或是发生改变。
    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 条附言  ·  2023-05-23 15:05:29 +08:00

    是 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 条附言  ·  2023-05-23 15:06:35 +08:00
    确实是军工项目，表结构无法修改了 Oracle 还是 10g 版本：）
    接手优化这个 sql 很蛋疼
  • instr
  • select
  • SQL
  • from
    66 条回复  •  2023-05-24 14:16:29 +08:00
    8355
        1
    8355  
       2023-05-22 14:43:06 +08:00
    那么压力给到楼下的大佬, 我血压上来了.
    tramm
        2
    tramm  
       2023-05-22 14:44:43 +08:00
    你这看着都头疼,看看楼下怎么说
    BeautifulSoap
        3
    BeautifulSoap  
       2023-05-22 14:48:30 +08:00
    问了 chatgpt ，它建议我用 LIKE
    dizheyoulan
        4
    dizheyoulan  
       2023-05-22 14:52:14 +08:00
    不懂 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  
       2023-05-22 14:53:26 +08:00
    小白蹲一个 sql 大佬学习学习
    raysonlu
        6
    raysonlu  
       2023-05-22 14:53:43 +08:00
    没有汇聚统计的逻辑，套这么多层干嘛？
    siweipancc
        7
    siweipancc  
       2023-05-22 14:54:35 +08:00 via iPhone   ❤️ 1
    这查询，我只能用哇塞来形容。上全文搜索吧
    sivacohan
        8
    sivacohan  
       2023-05-22 14:55:47 +08:00   ❤️ 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  
       2023-05-22 14:59:07 +08:00
    where sjbj = '0' and concat(mc, ssxmmc, ssxtzc, xinghao, gg) like '%1%'
    zjsxwc
        10
    zjsxwc  
       2023-05-22 14:59:19 +08:00   ❤️ 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  
       2023-05-22 15:00:00 +08:00
    ```
    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  
       2023-05-22 15:00:13 +08:00
    @samv2 #9 其实不确定算不算优化，手动狗头
    bugsystem
        13
    bugsystem  
       2023-05-22 15:00:14 +08:00
    通意千问说改成这样
    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  
       2023-05-22 15:03:57 +08:00
    @zjsxwc
    ![sql7f63f62d0af1e49f.png]( https://youjb.com/images/2023/05/22/sql7f63f62d0af1e49f.png)
    yulgang
        15
    yulgang  
       2023-05-22 15:10:45 +08:00
    >> 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  
       2023-05-22 15:28:44 +08:00   ❤️ 11
    我貌似第一次感受到论坛评论被 ai 污染的现象
    locochen
        17
    locochen  
       2023-05-22 15:28:51 +08:00
    确定的限制条件可以放在最里面.

    带入的查询条件,看是否可以通过语法解释后进入最里层的 select.这样就不会全表扫描.
    magicyao
        18
    magicyao  
       2023-05-22 15:32:40 +08:00
    几个 OR 没法优化，不知道为啥要这么设计，外面两层 select 都可以去掉，不过意义不大
    0914xc
        19
    0914xc  
       2023-05-22 15:38:26 +08:00
    找了半天，没找到微软小冰。
    NoKey
        20
    NoKey  
       2023-05-22 15:38:49 +08:00
    现在大家有问题，都是第一个问 AI 么🤣🤣🤣
    lisonfan
        21
    lisonfan  
       2023-05-22 15:39:51 +08:00
    来自 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  
       2023-05-22 15:40:15 +08:00
    instr 性能优于 like 吗？ 你这几个 or 条件的结果能否预处理成新增字段，然后通过这个字段来查询？
    registerrr
        23
    registerrr  
       2023-05-22 15:40:21 +08:00
    @NoKey 关键是 AI 有时候是真的行
    idealhs
        24
    idealhs  
       2023-05-22 15:41:00 +08:00
    有人已经在用 GPT 爽出代码了
    有人还在评论区指点 AI 污染
    pengtdyd
        25
    pengtdyd  
       2023-05-22 15:46:46 +08:00
    很多人在绞尽脑汁的优化 sql ，难道你就没有想过是物理模型设计的问题吗。表结构设计的一团糟，直接导致后续 sql 难写，这种现象好像很普遍。
    Alias4ck
        26
    Alias4ck  
       2023-05-22 15:52:50 +08:00
    什么数据库？
    c6h6benzene
        27
    c6h6benzene  
       2023-05-22 15:54:56 +08:00
    就我在好奇 xinghao 是“型号”还是“行号”吗？
    ThreeK
        28
    ThreeK  
       2023-05-22 17:00:37 +08:00
    理解不了这 sql 想干嘛。就一张表 ，各个针对结果集的 where 有什么不能直接写到最里边的，非要套这么多层。
    8355
        29
    8355  
       2023-05-22 17:16:37 +08:00
    @NoKey 关键是楼主这个 sql 代码看着非常烧脑
    popvlovs
        30
    popvlovs  
       2023-05-22 17:18:17 +08:00
    @c6h6benzene 也可能是“序号”
    popvlovs
        31
    popvlovs  
       2023-05-22 17:21:42 +08:00
    @c6h6benzene 看岔了，我以为你说的“xh”，不过 OP 这个 SQL 的参数风格，和我以前做军工的时候好像，真是不堪回首 >_<
    RunningMars
        32
    RunningMars  
       2023-05-22 17:21:54 +08:00   ❤️ 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  
       2023-05-22 17:23:27 +08:00   ❤️ 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  
       2023-05-22 17:26:57 +08:00
    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  
       2023-05-22 17:36:07 +08:00
    先把所有 OR 改成 UNION 写法试试
    sadfQED2
        36
    sadfQED2  
       2023-05-22 17:39:25 +08:00 via Android
    你说下数据库呀，你如果是传统的关系型数据库，我感觉天王老子来了也没辙。你要不看看实时计算引擎？找个支持倒排索引的实时计算引擎，你这点数据量都不叫事
    zypy333
        37
    zypy333  
       2023-05-22 17:41:51 +08:00
    所以 AI 写的对吗
    chonanlee
        38
    chonanlee  
       2023-05-22 17:55:54 +08:00
    为啥不从源头上解决数据问题呢？比如把你需要的这个直接在写入的地方直接做个标志位，然后对这个标志位加索引。如果要解决 like 或者 instr ，这种治标不治本
    fxxkgw
        39
    fxxkgw  
       2023-05-22 18:08:07 +08:00 via Android
    explain 看下
    另外 like%%这种是没法用索引的 大概率更慢
    另外 可以搜下 数据库优化 美团 能找到一篇美团技术文章讲数据库优化 供参考
    season8
        40
    season8  
       2023-05-22 18:22:13 +08:00
    这个 rownum 没看到函数， 是字段？如果是字段直接放最里面条件里面不就优化了？
    xuanbg
        41
    xuanbg  
       2023-05-22 18:33:33 +08:00
    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  
       2023-05-22 18:44:57 +08:00
    OP 你这个 sql 是查询 mc/ssxmmc/ssxtzc/xinghao/gg 这 5 个字段中的包含 1 的记录，必然是要全表扫描的。所以就没得优化。但去掉外面套的两层，至少磁盘 IO 是可以降低很多的……
    ijyuqi
        43
    ijyuqi  
       2023-05-22 19:57:18 +08:00 via iPhone
    个人的理解，应该是要从 q_materialfusion 表中按 where 的条件筛选排序并生成序列号，取排序前 500 条数据，只是最外面一层，感觉是多余的。
    andytao
        44
    andytao  
       2023-05-22 20:39:38 +08:00
    非 DBA ，一直在开发 [袋鼠数据库工具( https://www.datatable.online/zh/)] ，楼主可以从几个点做优化：
    1 、优化星号，用具体的字段替代
    2 、为排序字段建立索引；
    3 、查询条件字段也建议为索引；
    jdOY
        45
    jdOY  
       2023-05-22 22:27:05 +08:00
    拆成简单查询，代码里面聚合，说不定还能到 1 秒内
    huigeer
        46
    huigeer  
       2023-05-22 22:39:34 +08:00 via iPhone   ❤️ 7
    竟然没有一个人说要看看 explain 的执行结果的……
    nianlifeixing
        47
    nianlifeixing  
       2023-05-23 01:06:41 +08:00 via iPhone
    子查询，一下子血压就上来了
    way2explore2
        48
    way2explore2  
       2023-05-23 06:17:30 +08:00
    @huigeer 正解，

    我举着用 or 应该会有帮助，但不能确定是否 2 秒
    sanestays
        49
    sanestays  
       2023-05-23 09:33:59 +08:00
    嵌套的 rownum 和 limit 啥区别 没必要套那么多层
    fredcc
        50
    fredcc  
       2023-05-23 09:50:02 +08:00
    以下内容由 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  
       2023-05-23 09:58:42 +08:00
    @idealhs #24 主要是楼上所有贴出来 ai 答的，无一不是一坨屎，没有一个是真正有效的
    blackmirror
        52
    blackmirror  
       2023-05-23 10:18:49 +08:00
    你这条件很多不走索引吧
    funcNVidia
        53
    funcNVidia  
       2023-05-23 10:21:17 +08:00
    看了楼主贴的 SQL ，我血压高了。看了楼下一些贴 AI 的回答，我血压更高了。
    500
        54
    500  
       2023-05-23 11:02:51 +08:00
    看 T-SQL 也就最里面的子查询有用，功能大概是一个多字段模糊查询？

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

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

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

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


    楼主这个 SQL 应该是排序后再取前 500 行数据
    vipdog73
        60
    vipdog73  
       2023-05-23 17:13:16 +08:00
    ImSealin
        61
    ImSealin  
       2023-05-23 17:15:44 +08:00
    下班前看到这个 SQL 我血压也上来了..
    JKeita
        62
    JKeita  
       2023-05-23 17:23:52 +08:00
    看到这 SQL 真是会脑溢血
    lff0305
        63
    lff0305  
       2023-05-23 21:23:55 +08:00 via Android
    Oracle 这个版本能创建（或者环境允许创建） instr 的函数索引么，试试函数索引
    chonanlee
        64
    chonanlee  
       2023-05-23 22:50:32 +08:00 via Android
    加个字段，建个 Oracle 的 trigger ，更新或者新增的时候用 trigger 去更新新加的字段
    liuhuan475
        65
    liuhuan475  
       2023-05-24 11:10:17 +08:00
    不能改表结构 能不能新建表啊
    ruanimal
        66
    ruanimal  
       2023-05-24 14:16:29 +08:00
    @vipdog73 看了贴 ai 图的，血压更高了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   966 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 22:54 · PVG 06:54 · LAX 14:54 · JFK 17:54
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.