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

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