拥有非常非常大的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
它似乎工作得很好,但也不是特别高的流量(每天几百次点击),我想知道是否值得努力尝试和优化查询以消除冗余等.
解决方法
阅读你的查询让我想玩RPG.
这绝对不会太长.只要格式很好,我会说实际的限制是大约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.
*/
