实际限制SQL查询的长度(特别是MySQL) [英] Practical limit to length of SQL query (specifically MySQL)

问题描述

拥有一个非常大的SQL查询并带有很多(可能是冗余的)WHERE子句是否特别糟糕?

Is it particularly bad to have a very, very large SQL query with lots of (potentially redundant) WHERE clauses?

例如，这是我从Web应用程序中关闭的所有内容中生成的查询，这应该是该程序生成的最大查询:

For example, here's a query I've generated from my web application with everything turned off, which should be the largest possible query for this program to generate:

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

它似乎运行良好，但是访问量也不高(每天点击几百次)，我想知道是否值得尝试优化查询以消除冗余等.

It seems to work well enough, but it's also not particularly high traffic (a few hundred hits a day or so), and I wonder if it would be worth the effort to try and optimize the queries to remove redundancies and such.

推荐答案

阅读查询使我想玩RPG.

Reading your query makes me want to play an RPG.

这绝对不会太长.只要格式正确，我会说实际的限制是大约100行.之后，最好不要将子查询分解为视图，以免使您的视线交叉.

This is definitely not too long. As long as they are well formatted, I'd say a practical limit is about 100 lines. After that, you're better off breaking subqueries into views just to keep your eyes from crossing.

我已经处理了1000行以上的查询，而且很难调试.

I've worked with some queries that are 1000+ lines, and that's hard to debug.

顺便说一句，我可以建议重新格式化的版本吗?这主要是为了证明格式化的重要性；我相信这会更容易理解.

By the way, may I suggest a reformatted version? This is mostly to demonstrate the importance of formatting; I trust this will be easier to understand.

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.
*/

这篇关于实际限制SQL查询的长度(特别是MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！