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

[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 没看到函数， 是字段？如果是字段直接放最里面条件里面不就优化了？