Практическое ограничение длины SQL-запроса (в частности, MySQL)

Неужели особенно плохо иметь очень большой SQL-запрос с множеством (потенциально избыточных) предложений WHERE?

Например, здесь возникает запрос, который я создал из своего веб-приложения со всем отключенным, который должен быть самым большим возможным запросом для этой программы:

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

Кажется, что он работает достаточно хорошо, но это также не особенно высокий трафик (несколько сотен ударов в день или около того), и мне интересно, стоит ли пытаться оптимизировать запросы для удаления избыточности и т.д.

Ответ 1

Чтение вашего запроса заставляет меня хотеть играть в 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.
*/

Ответ 2

По умолчанию ограничение сервера MySQL 5.0 " 1 МБ", настраивается до 1 ГБ.

Это настраивается с помощью параметра max_allowed_packet на клиенте и сервере, и эффективное ограничение является арендодателем этих двух.

Предостережения:

Вероятно, это ограничение "пакетов" не сопоставляется непосредственно с символами в инструкции SQL. Разумеется, вы хотите учитывать кодировку символов внутри клиента, некоторые метаданные пакетов и т.д.).

Ответ 3

SELECT @@global.max_allowed_packet

это единственный реальный предел, который он настраивает на сервере, поэтому нет реального прямого ответа

Ответ 4

С практической точки зрения, я обычно рассматриваю любой SELECT, который заканчивает работу с более чем 10 строками для записи (помещая каждое условие/условие на отдельной строке) слишком долго, чтобы легко поддерживать. На данный момент это, вероятно, должно быть сделано как некоторая хранимая процедура, или я должен попытаться найти лучший способ выразить одну и ту же концепцию - возможно, создав промежуточную таблицу для захвата некоторых отношений, которые, как мне кажется, часто запрашивают.

Ваш пробег может отличаться, и есть некоторые чрезвычайно длинные запросы, которые имеют все основания. Но мое эмпирическое правило - 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

Это, вероятно, слишком велико; однако оптимизация будет в значительной степени зависеть от контекста.

Просто помните, чем длиннее и сложнее запрос, тем сложнее будет поддерживать.

Ответ 5

Большинство баз данных поддерживают хранимые процедуры, чтобы избежать этой проблемы. Если ваш код достаточно быстр, чтобы выполнять и легко читать, вы не хотите его менять, чтобы сократить время компиляции.

Альтернативой является использование подготовленных операторов, поэтому вы получаете только один раз на клиентское соединение, а затем передаете только параметры для каждого вызова

Ответ 6

Я предполагаю, что вы "выключили", что поле не имеет значения?

Вместо того, чтобы проверять, что что-то не так, и это также не так и т.д., вы не можете просто проверить, является ли поле нулевым? Или установите поле "выключено" и проверьте, нет ли значения типа или "выключено".