对 SQL 查询（特别是 MySQL）长度的实际限制-解网

问：

有一个非常非常大的 SQL 查询，其中包含许多（可能是冗余的）WHERE 子句，这是否特别糟糕？

例如，下面是我从我的 Web 应用程序生成的一个查询，其中所有内容都已关闭，这应该是此程序生成的最大查询：

SELECT * 
FROM 4e_magic_items 
INNER JOIN 4e_magic_item_levels 
  ON 4e_magic_items.id = 4e_magic_item_levels.itemid 
INNER JOIN 4e_monster_sources 
  ON 4e_magic_items.source = 4e_monster_sources.id 
WHERE (itemlevel BETWEEN 1 AND 30)  
  AND source!=16 AND source!=2 AND source!=5 
  AND source!=13 AND source!=15 AND source!=3 
  AND source!=4 AND source!=12 AND source!=7 
  AND source!=14 AND source!=11 AND source!=10 
  AND source!=8 AND source!=1 AND source!=6 
  AND source!=9  AND type!='Arms' AND type!='Feet' 
  AND type!='Hands' AND type!='Head' 
  AND type!='Neck' AND type!='Orb' 
  AND type!='Potion' AND type!='Ring' 
  AND type!='Rod' AND type!='Staff' 
  AND type!='Symbol' AND type!='Waist' 
  AND type!='Wand' AND type!='Wondrous Item' 
  AND type!='Alchemical Item' AND type!='Elixir' 
  AND type!='Reagent' AND type!='Whetstone' 
  AND type!='Other Consumable' AND type!='Companion' 
  AND type!='Mount' AND (type!='Armor' OR (false )) 
  AND (type!='Weapon' OR (false )) 
 ORDER BY type ASC, itemlevel ASC, name ASC

它似乎运行良好，但它的流量也不是特别高（每天几百次点击左右），我想知道是否值得尝试优化查询以消除冗余等。

MySQL SQL 优化

当您尝试使用该网站时，它看起来很慢吗？如果每天只有几百次点击，我想你不会担心。您预计流量会增加吗？多少钱？如果你的时间不紧，你可以去做，让网站面向未来。但是，以编程方式查找和删除冗余所需的时间是否大于仅运行查询所需的时间？

答：

0赞 Oskar 9/19/2008 #1

大多数数据库都支持存储过程以避免此问题。如果您的代码执行速度足够快且易于阅读，则不必为了缩短编译时间而更改它。

另一种方法是使用预准备语句，以便每个客户端连接只获得一次命中，然后仅传入每个调用的参数

18赞 Chris 9/19/2008 #2

默认 MySQL 5.0 服务器限制为“1MB”，最多可配置为 1GB。

这是通过客户端和服务器上的max_allowed_packet设置配置的，有效限制是两者的出租人。

警告：

此“数据包”限制可能不会直接映射到 SQL 语句中的字符。当然，您希望考虑客户端中的字符编码、某些数据包元数据等。

21赞 JosephStyons 9/19/2008 #3

阅读您的查询让我想玩角色扮演游戏。

这绝对不会太长。只要它们的格式正确，我会说实际限制是大约 100 行。在那之后，你最好将子查询分解为视图，只是为了防止你的眼睛交叉。

我处理过一些 1000+ 行的查询，这很难调试。

顺便问一下，我可以建议重新格式化的版本吗？这主要是为了证明格式的重要性;我相信这会更容易理解。

select *  
from
  4e_magic_items mi
 ,4e_magic_item_levels mil
 ,4e_monster_sources ms
where mi.id = mil.itemid
  and mi.source = ms.id
  and itemlevel between 1 and 30
  and source not in(16,2,5,13,15,3,4,12,7,14,11,10,8,1,6,9)  
  and type not in(
                  'Arms' ,'Feet' ,'Hands' ,'Head' ,'Neck' ,'Orb' ,
                  'Potion' ,'Ring' ,'Rod' ,'Staff' ,'Symbol' ,'Waist' ,
                  'Wand' ,'Wondrous Item' ,'Alchemical Item' ,'Elixir' ,
                  'Reagent' ,'Whetstone' ,'Other Consumable' ,'Companion' ,
                  'Mount'
                 )
  and ((type != 'Armor') or (false))
  and ((type != 'Weapon') or (false))
order by
  type asc
 ,itemlevel asc
 ,name asc

/*
Some thoughts:
==============
0 - Formatting really matters, in SQL even more than most languages.
1 - consider selecting only the columns you need, not "*"
2 - use of table aliases makes it short & clear ("MI", "MIL" in my example)
3 - joins in the WHERE clause will un-clutter your FROM clause
4 - use NOT IN for long lists
5 - logically, the last two lines can be added to the "type not in" section.
    I'm not sure why you have the "or false", but I'll assume some good reason
    and leave them here.
*/

实际上，连接会加快速度，尤其是在使用适当的索引时。原因是，如果你所有的子句都在 WHERE 中，mysql 将获取所有数据，然后对其进行过滤;而通过适当的连接，它将只选择所需的数据，这些数据可以小几个数量级 - 过滤速度更快。

1赞 Aeon 9/19/2008

哦，还有......除非我遗漏了什么，否则（type！='Armor' OR （false））将计算为 true 或 false，但无论哪种情况，它都不会影响结果集，所以它真的不需要。

1赞 Asmor 9/19/2008

这是因为盔甲（和武器）是按类型进一步过滤的。因此，例如，如果选择了 Cloth 和 Hide，则会显示：（type！='Armor' OR （FALSE OR restrictions like 'C' OR restrictions like 'H'））该页面只是在括号内添加了“或限制，例如'whatever'”，因此需要false。

2赞 Amy B 9/19/2008

FROM 子句中的 join 将整理 WHERE 子句。@Aeon - 使用适当的查询优化器应该没有性能差异。mysql有那么糟糕吗？

3赞 longneck 12/15/2009

对使用 JOIN 条件混淆 WHERE 子句投反对票。

0赞 Matthew Rapati 9/19/2008 #4

我假设你的意思是“关闭”一个字段没有值？

而不是检查某物是否不是这个，也不是那个等等，你不能检查字段是否为空吗？或者将字段设置为“off”，并检查 type 或其他内容是否等于“off”。

1赞 Kate Bertelsen 9/19/2008 #5

从实际的角度来看，我通常认为任何最终需要超过 10 行才能编写（将每个子句/条件放在单独的行上）的 SELECT 都太长而无法轻松维护。在这一点上，它可能应该作为某种存储过程来完成，或者我应该尝试找到一种更好的方法来表达相同的概念——可能通过创建一个中间表来捕获我似乎经常查询的某种关系。

您的里程可能会有所不同，并且有一些特别长的查询是有充分理由的。但我的经验法则是 10 行。

示例（轻度不正确的 SQL）：

SELECT x, y, z
FROM a, b
WHERE fiz = 1
AND foo = 2
AND a.x = b.y
AND b.z IN (SELECT q, r, s, t
            FROM c, d, e
            WHERE c.q = d.r
              AND d.s = e.t
              AND c.gar IS NOT NULL)
ORDER BY b.gonk

这可能太大了;然而，优化在很大程度上取决于环境。

请记住，查询越长、越复杂，维护起来就越困难。

3赞 Ga1der 3/4/2016 #6

选择 @@global.max_allowed_packet

这是唯一真正的限制，它可以在服务器上调整，因此没有真正的直接答案

上一个：什么原因导致需要修复表？

下一个：如何在MySql中DATETIME字段的日期部分创建索引

对 SQL 查询（特别是 MySQL）长度的实际限制

Practical limit to length of SQL query (specifically MySQL)

评论

评论