SQL 大佬来救命

[0x19921213]

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 秒。

是 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

确实是军工项目，表结构无法修改了 Oracle 还是 10g 版本：）
接手优化这个 sql 很蛋疼

[8355]

那么压力给到楼下的大佬, 我血压上来了.

[tramm]

你这看着都头疼,看看楼下怎么说

[BeautifulSoap]

问了 chatgpt ，它建议我用 LIKE

[dizheyoulan]

不懂 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]

小白蹲一个 sql 大佬学习学习

[raysonlu]

没有汇聚统计的逻辑，套这么多层干嘛？

[siweipancc]

这查询，我只能用哇塞来形容。上全文搜索吧

[sivacohan]

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]

where sjbj = '0' and concat(mc, ssxmmc, ssxtzc, xinghao, gg) like '%1%'

[zjsxwc]

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]

```
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]

@samv2 #9 其实不确定算不算优化，手动狗头

[bugsystem]

通意千问说改成这样
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]

@zjsxwc

[yulgang]

>> 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]

我貌似第一次感受到论坛评论被 ai 污染的现象

[locochen]

确定的限制条件可以放在最里面.

带入的查询条件,看是否可以通过语法解释后进入最里层的 select.这样就不会全表扫描.

[magicyao]

几个 OR 没法优化，不知道为啥要这么设计，外面两层 select 都可以去掉，不过意义不大

[0914xc]

找了半天，没找到微软小冰。

[NoKey]

现在大家有问题，都是第一个问 AI 么🤣🤣🤣

[lisonfan]

来自 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]

instr 性能优于 like 吗？ 你这几个 or 条件的结果能否预处理成新增字段，然后通过这个字段来查询？

[registerrr]

@NoKey 关键是 AI 有时候是真的行

[idealhs]

有人已经在用 GPT 爽出代码了
有人还在评论区指点 AI 污染

[pengtdyd]

很多人在绞尽脑汁的优化 sql ，难道你就没有想过是物理模型设计的问题吗。表结构设计的一团糟，直接导致后续 sql 难写，这种现象好像很普遍。

[Alias4ck]

什么数据库？

[c6h6benzene]

就我在好奇 xinghao 是“型号”还是“行号”吗？

[ThreeK]

理解不了这 sql 想干嘛。就一张表 ，各个针对结果集的 where 有什么不能直接写到最里边的，非要套这么多层。

[8355]

@NoKey 关键是楼主这个 sql 代码看着非常烧脑

[popvlovs]

@c6h6benzene 也可能是“序号”

[popvlovs]

@c6h6benzene 看岔了，我以为你说的“xh”，不过 OP 这个 SQL 的参数风格，和我以前做军工的时候好像，真是不堪回首 >_<

[RunningMars]

干嘛不这样：
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]

为什么套了这么多层？
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]

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]

先把所有 OR 改成 UNION 写法试试

[sadfQED2]

你说下数据库呀，你如果是传统的关系型数据库，我感觉天王老子来了也没辙。你要不看看实时计算引擎？找个支持倒排索引的实时计算引擎，你这点数据量都不叫事

[zypy333]

所以 AI 写的对吗

[chonanlee]

为啥不从源头上解决数据问题呢？比如把你需要的这个直接在写入的地方直接做个标志位，然后对这个标志位加索引。如果要解决 like 或者 instr ，这种治标不治本

[fxxkgw]

explain 看下
另外 like%%这种是没法用索引的 大概率更慢
另外 可以搜下 数据库优化 美团 能找到一篇美团技术文章讲数据库优化 供参考

[season8]

这个 rownum 没看到函数， 是字段？如果是字段直接放最里面条件里面不就优化了？

[xuanbg]

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]

OP 你这个 sql 是查询 mc/ssxmmc/ssxtzc/xinghao/gg 这 5 个字段中的包含 1 的记录，必然是要全表扫描的。所以就没得优化。但去掉外面套的两层，至少磁盘 IO 是可以降低很多的……

[ijyuqi]

个人的理解，应该是要从 q_materialfusion 表中按 where 的条件筛选排序并生成序列号，取排序前 500 条数据，只是最外面一层，感觉是多余的。

[andytao]

非 DBA ，一直在开发 [袋鼠数据库工具( https://www.datatable.online/zh/)] ，楼主可以从几个点做优化：
1 、优化星号，用具体的字段替代
2 、为排序字段建立索引；
3 、查询条件字段也建议为索引；

[jdOY]

拆成简单查询，代码里面聚合，说不定还能到 1 秒内

[huigeer]

竟然没有一个人说要看看 explain 的执行结果的……

[nianlifeixing]

子查询，一下子血压就上来了

[way2explore2]

@huigeer 正解，

我举着用 or 应该会有帮助，但不能确定是否 2 秒

[sanestays]

嵌套的 rownum 和 limit 啥区别 没必要套那么多层

[fredcc]

以下内容由 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]

@idealhs #24 主要是楼上所有贴出来 ai 答的，无一不是一坨屎，没有一个是真正有效的

[blackmirror]

你这条件很多不走索引吧

[funcNVidia]

看了楼主贴的 SQL ，我血压高了。看了楼下一些贴 AI 的回答，我血压更高了。

[500]

看 T-SQL 也就最里面的子查询有用，功能大概是一个多字段模糊查询？

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

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

更进一步，可以将查询语句进行封装，分段并发查询（例如每 10 万条数据为 1 段，同时进行 20 个并发查询），最后合并所有查询的结果。

[go522000]

看这贴子的 AI 的回复，大多数都是错误的。看来程序被 AI 取代的日子还有一些时间。

[someday3]

你这完全没必要嵌套啊，直接展平就行，看看哪个条件过滤掉的数据更多就放在前面，用 and 套在一起。

比如先 where rownum >= 1 and rownum < 501 。然后再 and 你那一堆。

[0x19921213]

sjbj 字段只有 0 和 1 ，而且 90% 是 0 ，所以肯定要走全表扫描了 :(
有没有其他优化思路呢

[yc8332]

你这个看起来没得优化。考虑其他方案吧

[AmaQuinton]

@qqqq09047 #33 rownum 执行的优先级在 order by 之前， 所以 rownum < 501 代表随机取了 500 行数据， 再进行 order by nf desc, mc asc 排序后得到的数据，


楼主这个 SQL 应该是排序后再取前 500 行数据

[vipdog73]

[ImSealin]

下班前看到这个 SQL 我血压也上来了..

[JKeita]

看到这 SQL 真是会脑溢血

[lff0305]

Oracle 这个版本能创建（或者环境允许创建） instr 的函数索引么，试试函数索引

[chonanlee]

加个字段，建个 Oracle 的 trigger ，更新或者新增的时候用 trigger 去更新新加的字段

[liuhuan475]

不能改表结构 能不能新建表啊

[ruanimal]

@vipdog73 看了贴 ai 图的，血压更高了