代码之家 › 专栏 › 技术社区 › Asmor

SQL查询长度的实际限制(特别是mysql)

optimization sql mysql

Asmor · 技术社区 · 16 年前

有一个非常非常大的带有大量(可能是多余的)where子句的SQL查询,这是不是特别糟糕?

例如,以下是我在关闭所有功能的情况下从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

它似乎工作得很好,但它也不是特别高的流量(每天大约有几百个点击量),我想知道是否值得尝试优化查询以消除冗余等等。

6 回复 | 直到 16 年前

JosephStyons 16 年前

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

Steve Clay 11 年前

max_allowed_packet

Ga1der 8 年前

Kate Bertelsen 16 年前

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

Oskar 16 年前

Matthew Rapati 16 年前