Секретные записки WEB-программиста




100mbru, bitrix, CentOS, cms, drupal, java, joomla, LAMP, linux, mysql, nicru, Open-Source, php, Red Hat, seo, Typo3, ubuntu, win, windows, авто, администрирование, алгоритмы, алкоголь, бизнес, битрикс, видео, военмех, выборы, германия, джино, домены, интересности, исследования, картинки, кино, компьютеры, ливия, магазин, маразм, мастерхост, обработка-изображений, пейнтбол, политика, политэкономия, прикольное-видео, программирование, путешествия, работа, религия, рунет, сайтостроение, сео, сми, технологии, украина, форум, хиханьки, холивары, хостинг

Оптимизация SQL-запросов

10.10.2012

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

И такое будет продолжаться до тех пор, пока приложение не будет подвергнуто жестокому анализу на предмет повышения производительности. Один из способов повысить скорость работы приложения - оптимизация SQL-запросов. Этот способ хорош тем, что не надо лезть в дебри оптимизации SQL-сервера. Проще не допускать появления неэффективных SQL-запросов. Но если такое уже случилось, ищи выходы из сложившихся неприятных ситуаций.

Общая оптимизация

Каждая SQL-операция имеет так называемый "коэффициент полезности" – уровень эффективности данной операции. Чем больше балл, тем "полезней" операция, а значит, SQL-запрос выполняется быстрее.

Практически любое условие состоит из двух операндов и знака операции между ними.

Примеры

Чтобы лучше понять таблицы, рассмотрим пример расчета рейтинга запроса.

… WHERE smallint_column = 12345

5 баллов за поле слева (smallint_column), 2 балла за точный цифровой операнд(smallint_column), 10 баллов за операцию сравнения (=) и 10 баллов за значение справа (12345). Итого получили 27 баллов. Теперь рассмотрим более сложный пример:

... WHERE char_column >= varchar_column || "x"

5 баллов за поле слева (char_column), 0 баллов за символьный операнд (char_column), 5 баллов за операцию больше или равно (>=) , 3 балла за логическое выражение (varchar_column || "x") , 0 баллов за символьный операнд (varchar_column). В итоге получим 13 баллов.

Естественно, такие расчеты не обязательно проводить для каждого запроса. Но когда встанет вопрос о скорости условий того или иного запроса, его можно будет выяснить с помощью этих двух таблиц. На скорость запроса также влияет количество выбираемых данных и дополнительные директивы, которые рассмотрим ниже. Также имей в виду, что расчет "коэффициента полезности" не является неким универсальным способом оптимизации. Все зависит от конкретной ситуации.

Основной закон при оптимизации запросов - закон преобразования. Неважно, как мы представляем условие, главное чтобы результат остался прежним. И снова рассмотрим пример. Есть запрос: ... WHERE column1 < column2 AND column2 = column3 AND column1 = 5 . Используя перестановку, получаешь запрос: …WHERE 5 < column2 AND column2 = column3 AND column1 = 5 . Результат запроса будет один и тот же, а продуктивность разной, потому что использование точного значения (5) влияет на производительность.

Если ты изучал С или С++, то знаешь, что выражение x=1+1-1-1 во время компиляции станет x=0. Удивительно, что лишь некоторые БД способны выполнять такие операции. При выполнении запроса БД будет выполнять операции сложения и вычитания и тратить твое драгоценное время. Поэтому всегда лучше сразу рассчитывать такие выражения там, где это возможно. Не WHERE a - 3 = 5 , а … WHERE a = 8 .

Еще одна возможность оптимизировать запрос - придерживаться общей идеи составления условий в SQL. Другими словами, условие должно иметь вид: <колонка> <операция> <выражение>. Например, запрос "... WHERE column1 - 3 = -column2" лучше привести к виду: ... WHERE column1 = -column2 + 3 .

И эти приемы оптимизации работают практически всегда и везде.

Оптимизируем условия

Теперь настало время произвести оптимизацию самих условных операторов SQL. Большинство запросов используют директиву SQL WHERE, поэтому, оптимизируя условия, можно добиться значительной производительности запросов. При этом почему-то лишь небольшая часть приложений для БД используют оптимизацию условий.

AND

Очевидно, что в серии из нескольких операторов AND условия должны располагаться в порядке возрастания вероятности истинности данного условия. Это делается для того, чтобы при проверке условий БД не проверяла остальную часть условия. Эти рекомендации не относится к БД Oracle, где условия начинают проверяться с конца. Соответственно, их порядок должен быть обратным – по убыванию вероятности истинности.

OR

Ситуация с данным оператором прямо противоположна ситуации с AND. Условия должны располагаться в порядке убывания вероятности истинности. Фирма Microsoft настойчиво рекомендует использовать данный метод при построении запросов, хотя многие даже не знают об этом или, по крайней мере, не обращают на него внимание. Но опять-таки это не относится к БД Oracle, где условия должны располагаться по возрастанию вероятности истинности.

Еще одним условием для оптимизации можно считать тот факт, что если одинаковые колонки располагаются рядом, запрос выполняется быстрее. Например, запрос ".. WHERE column1 = 1 OR column2 = 3 OR column1 = 2" будет выполняться медленней, чем запрос "WHERE column1 = 1 OR column1 = 2 OR column2 = 3" . Даже если вероятность истинности условия column2 = 3 выше, чем column1 = 2.

AND + OR

Еще в школе мне рассказывали про распределительный закон. Он гласит, что A AND (B OR C) - то же самое, что и (A AND B) OR (A AND C ). Опытным путем было установлено, что запрос вида "...WHERE column1 = 1 AND (column2 = "A" OR column2 = "B")" выполняется несколько быстрее, чем "...WHERE (column1 = 1 AND column2 = "A") OR (column1 = 1 AND column2 = "B")" . Некоторые БД сами умеют оптимизировать запросы такого типа, но лучше перестраховаться.

NOT

Эту операцию всегда следует приводить к более "читабельному" виду (в разумных пределах, конечно). Так, запрос "...WHERE NOT (column1 > 5)" преобразуется в "...WHERE column1 <= 5" . Более сложные условия можно преобразовать используя правило де Моргана, которое ты тоже должен был изучить в школе. Согласно этому правилу NOT(A AND B) = (NOT A) OR (NOT B) и NOT(A OR B) = (NOT A) AND (NOT B) . Например, условие "...WHERE NOT (column1 > 5 OR column2 = 7)" преобразуется в более простую форму: ...WHERE column1 <= 5 AND column2 <> 7 .

IN

Многие наивно полагают, что запрос "... WHERE column1 = 5 OR column1 = 6" равносилен запросу "...WHERE column1 IN (5, 6)" . На самом деле это не так. Операция IN работает гораздо быстрее, чем серия OR. Поэтому всегда следует заменять OR на IN, где это возможно, несмотря на то, что некоторые БД сами производят эту оптимизацию. Там, где используется серия последовательных чисел, IN следует поменять на BETWEEN. Например, "...WHERE column1 IN (1, 3, 4, 5)" оптимизируется к виду: …WHERE column1 BETWEEN 1 AND 5 AND column1 <> 2 . И этот запрос действительно быстрее.

LIKE

Эту операцию следует использовать только при крайней необходимости, потому что лучше и быстрее использовать поиск, основанный на full-text индексах. К сожалению, я вынужден направить тебя за информацией о поиске на просторы всемирной паутины.

CASE

Сама эта функция может использоваться для повышения скорости работы запроса, когда в нем есть более одного вызова медленной функции в условии. Например, чтобы избежать повторного вызова slow_function() в запросе "...WHERE slow_function(column1) = 3 OR slow_function(column1) = 5" , нужно использовать CASE:

... WHERE 1 = CASE slow_function(column1)

WHEN 3 THEN 1

WHEN 5 THEN 1

END

Сортировка

ORDER BY используется для сортировки, которая, как известно, занимает время. Чем больше объем данных, тем больше времени займет сортировка, поэтому нужно обязательно ее оптимизировать. На скорость сортировки в запросах влияет три фактора:

  • количество выбранных записей;
  • Самой ресурсоемкой сортировкой является сортировка строк. Несмотря на то, что текстовые поля имеют фиксированную длину, длина содержимого этих полей может быть различной (в пределах размера поля). Поэтому неудивительно, что сортировка колонки VARCHAR(100) будет медленней, чем сортировка колонки VARCHAR(10) (даже если данные будут одинаковые). А происходит это из-за того, что при сортировке сама база данных выделяет память для своих операций в соответствии с максимальным размером поля независимо от содержимого. Поэтому при объявлении полей всегда следует использовать размер, который нужен, и не выделять лишние байты про запас.

    На компьютерах с ОС Windows поля типа INTEGER занимают 32 бита, а поля типа SMALLINT – 16 бит. Логично предположить, что сортировка полей типа SMALLINT должна происходить быстрее. На самом деле сортировка INTEGER происходит быстрее, чем SMALLINT. Также сортировка INTEGER происходит быстрее, чем CHAR.

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

    Группирование

    Операция GROUP BY используется для определения подмножества в результате запроса, а также для применения к этому подмножеству агрегатных функций. Рассмотрим несколько наиболее эффективных методов оптимизации операции группирования.

    Первое, что следует помнить, - нужно использовать как можно меньше колонок для группировки. Также следует избегать лишних условий. Например, в запросе SELECT secondary_key_column, primary_key_column, COUNT(*) FROM Table1 GROUP BY secondary_key_column, primary_key_column колонка secondary_key_column совершенно не нужна. Причина простая: secondary_key_column является уникальным полем, оно может не иметь значений NULL, а значит, некоторые данные могут просто потеряться. Но если убрать secondary_key_column из секции GROUP BY, некоторые БД могут выдать ошибку о том, что невозможно указывать это поле, если оно не объявлено в секции GROUP BY. Для решения этой проблемы можно написать запрос в таком виде: SELECT MIN(secondary_key_column), primary_key_column, COUNT(*) FROM Table1 GROUP BY primary_key_column . Этот запрос быстрее и "правильнее" с точки зрения конструирования запросов.

    В большинстве БД операции WHERE и HAVING не равноценны и выполняются не одинаково. Это значит, что следующие два запроса логически одинаковы, но выполняются с разной скоростью:

    SELECT column1 FROM Table1 WHERE column2 = 5 GROUP BY column1 HAVING column1 > 6

    SELECT column1 FROM Table1 WHERE column2 = 5 AND column1 > 6 GROUP BY column1

    Второй запрос работает быстрее, чем первый. HAVING следует использовать в тех редких случаях, когда условие (в примере column1 > 6) сложно выразить без ущерба производительности.

    Если требуется группирование, но без использования агрегатных функций ( COUNT(), MIN(), MAX и т.д.), разумно использовать DISTINCT. Так, вместо SELECT column1 FROM Table1 GROUP BY column1 лучше использовать SELECT DISTINCT column1 FROM Table1 .

    При использовании MIN() и MAX() учитываем, что эти функции лучше работают по отдельности. Это значит, что их лучше использовать в раздельных запросах или в запросах с использованием UNION.

    При использовании функции SUM() большей производительности можно добиться используя SUM(x + y) , а не SUM(x) + SUM(y) . Для вычитания лучше противоположное: SUM(x) – SUM(y) быстрее, чем SUM(x – y).

    Соединения таблиц (JOINS)

    Вот где сложно что-то сказать про оптимизацию, так это при использовании JOIN . Дело в том, что скорость выполнения таких операций во многом зависит от организации самой таблицы: использование foreign-key, primary-key, количество вложенных соединений и т.д. Иногда лучшей производительности можно добиться используя вложенные циклы непосредственно в программе. Иногда быстрее работают JOINs. Однозначного совета по тому, как использовать разные способы соединения таблиц, не существует. Все зависит от конкретного случая и архитектуры БД.

    Подзапросы (SUBQUERIES)

    Раньше далеко не все БД могли похвастаться поддержкой подзапросов, а сейчас практически любая современная БД это умеет. Даже MySQL, которая несколько лет воплощала подзапросы в жизнь, наконец разжилась их поддержкой. Основная проблема при оптимизации подзапросов - не оптимизация непосредственно самого кода запроса, а выбор правильного способа для реализации запроса. Задачи, для которых используются подзапросы, также могут решаться с помощью вложенных циклов или JOIN’ов. Когда используешь JOIN, даешь возможность БД выбрать механизм, которым будет производиться соединение таблиц. Если же используешь подзапросы, то явно указываешь на использование вложенных циклов.

    Что выбрать?

    Ниже аргументы в пользу того или иного способа. Выбирай сам в зависимости от ситуации.

    Достоинства JOIN:

    • Если запрос содержит условие WHERE, встроенный оптимизатор БД будет оптимизировать запрос в целом, в то время как в случае использования подзапросов запросы будут оптимизироваться отдельно.
    • Некоторые БД более эффективно работают с JOINs, нежели с подзапросами (например, Oracle).
    • После JOIN’а информация окажется в общем "списке", что нельзя сказать про подзапросы.

    Достоинства SUBQUERIES:

    • Подзапросы допускают более свободные условия.
    • Подзапросы могут содержать GROUP BY, HAVING, что намного сложнее реализовать в JOIN’ах.
    • Подзапросы могут использоваться при UPDATE, что невозможно при использовании JOIN’ов.
    • В последнее время оптимизация подзапросов самими БД (их встроенным оптимизатором) заметно улучшилась.

    Основное преимущество JOIN’ов в том, что не надо указывать БД то, каким именно способом производить операцию. А основное преимущество подзапросов в том, что цикл подзапроса может иметь несколько итераций (повторений), что, в свою очередь, может существенно увеличить производительность.

    Заключение

    В этой статье показаны самые распространенные способы увеличения производительности SQL-запросов. Тем не менее, чтобы оптимизировать запросы, есть еще очень много разных уловок и трюков. Оптимизация запросов больше похожа на искусство, чем на науку. У каждой базы данных свои встроенные оптимизаторы, которые могут помочь в этом нелегком деле, но всю работу за тебя никто не сделает. Как говорил старенький преподаватель по физике: "Чтобы решать задачи, их нужно решать".

    Не рекомендуется использовать ORDER BY в связке с такими операциями, как DISTINCT или GROUP B Y, потому что данные операторы могут создавать побочные эффекты для сортировки. Как следствие, ты можешь получить неправильно отсортированный набор данных, который может оказаться критическим в некоторых ситуациях. Такое следствие не относится к оптимизации, но забывать о нем не стоит.

    Прежде чем повышать производительность сети и наращивать аппаратные средства сервера, попробуй сделать оптимизацию.

    У любой SQL-операции есть "коэффициент полезности". Чем выше коэффициент, тем "полезней" операция: запрос выполняется быстрее.

    В отличие от компиляторов, не все БД умеют упрощать выражения типа x=1+1-1-1 до x=0. Следовательно, они тратят драгоценное время на выполнение пустых операций. Оптимизируй их заранее.

    При использовании функции SUM() можно добиться большей производительности с помощью SUM(x + y) , а не SUM(x) + SUM(y) .

    Но если функции SUM() требуются для вычитания, используй противоположное: SUM(x) – SUM(y). SUM(x – y) работает медленнее.

    У каждой БД есть свои встроенные оптимизаторы, но они далеки от совершенства. Поэтому оптимизируй заранее.

  • количество колонок после оператора ORDER BY;
  • длина и тип колонок, указанных после оператора ORDER BY.
  • Если запрос содержит условие WHERE, встроенный оптимизатор БД будет оптимизировать запрос в целом, в то время как в случае использования подзапросов запросы будут оптимизироваться отдельно.
  • Некоторые БД более эффективно работают с JOINs, нежели с подзапросами (например, Oracle).
  • После JOIN’а информация окажется в общем "списке", что нельзя сказать про подзапросы.
  • Подзапросы допускают более свободные условия.
  • Подзапросы могут содержать GROUP BY, HAVING, что намного сложнее реализовать в JOIN’ах.
  • Подзапросы могут использоваться при UPDATE, что невозможно при использовании JOIN’ов.
  • В последнее время оптимизация подзапросов самими БД (их встроенным оптимизатором) заметно улучшилась.

  • http://www.xakep.ru/magazine/xs/052/040/1.asp