Как ускорить group by oracle

Обновлено: 24.01.2025

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

И такое будет продолжаться до тех пор, пока приложение не будет подвергнуто жестокому анализу на предмет повышения производительности. Один из способов повысить скорость работы приложения - оптимизация 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.

Еще в школе мне рассказывали про распределительный закон. Он гласит, что 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

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, даешь возможность БД выбрать механизм, которым будет производиться соединение таблиц. Если же используешь подзапросы, то явно указываешь на использование вложенных циклов.

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

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

Подзапросы допускают более свободные условия.
Подзапросы могут содержать 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) работает медленнее.

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

Как ускорить select count(*) С group by ?
Он слишком медленный и используется очень часто.
У меня большие проблемы с использованием select count(*) и group by с таблицей, имеющей более 3 000 000 строк.

relation_title, object_title имеет тип varchar. где relation_title= 'XXXX', который возвращает более 1 000 000 строк, приводят к индексам на object_title не может хорошо работать.

вот несколько вещей, которые я бы попробовал, в порядке возрастающей сложности:

(проще) - убедитесь, что у вас есть правильный индекс покрытия

Это должно максимизировать perf, учитывая вашу существующую схему, так как (если ваша версия оптимизатора mySQL действительно тупая!) это минимизирует количество I / Os, необходимое для удовлетворения вашего запроса (в отличие от того, если индекс находится в обратном порядке, где весь индекс должен быть отсканирован), и он будет покрывать запрос, чтобы вам не пришлось касаться кластеризованного индекса.

(немного сложнее) - убедитесь, что ваши поля varchar как можно меньше

одна из проблем perf с индексами varchar на MySQL заключается в том, что при обработке запроса полный объявленный размер поля будет втянут в ОЗУ. Поэтому, если у вас есть varchar(256), но вы используете только 4 символа, вы все еще платите 256-байтовое использование ОЗУ во время обработки запроса. Оуч! Так что, если сможешь . сожмите свои ограничения varchar легко, это должно ускорить ваши запросы.

(сложнее) - нормализуют

30% ваших строк, имеющих одно строковое значение, - это четкий призыв к нормализации в другую таблицу, чтобы вы не дублировали строки миллионы раз. Рассмотрим нормализацию в три таблицы и использование целых идентификаторов для их объединения.

в некоторых случаях вы можете нормализовать под обложками и скрыть нормализацию с представлениями, которые соответствуют имени текущая таблица. тогда вам нужно только сделать ваши запросы INSERT/UPDATE/DELETE осведомленными о нормализации, но вы можете оставить свой выбор в покое.

(hardest) - Хешируйте столбцы строк и индексируйте хэши

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

кроме того, если relation_title имеет малый размер varchar, но заголовок объекта имеет большой размер, тогда вы можете потенциально хэшировать только object_title и создавать индекс на (relation_title, object_title_hash) .

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

также обратите внимание, что есть интересные эффекты чувствительности к регистру/сортировки от хэширования, так как хэш строки нижнего регистра не совпадает с хэшем верхнего регистра. Поэтому вам нужно будет убедиться, что вы подаете заявку канонизация строк перед их хэшированием - другими словами, только в нижнем регистре хэша, если вы находитесь в БД без учета регистра. Вы также можете обрезать пробелы с начала или конца, в зависимости от того, как ваша БД обрабатывает ведущие/конечные пробелы.

индексирование столбцов в предложении GROUP BY было бы первым, что нужно попробовать, используя составной индекс. На такой запрос потенциально можно ответить, используя только данные индекса, избегая необходимости сканирования таблицы вообще. Поскольку записи в индексе отсортированы, СУБД не нужно выполнять отдельную сортировку в рамках групповой обработки. Однако индекс замедлит обновления таблицы, поэтому будьте осторожны, если ваша таблица будет тяжелой новинки.

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

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

материализованное представление было бы другим возможным подходом, но снова это не поддерживается непосредственно в MySQL. Однако, если вам не требуется, чтобы статистика подсчета была полностью обновлена, вы можете периодически запускать CREATE TABLE . AS SELECT . инструкция для ручного кэширования результатов. Это немного уродливо, поскольку оно не прозрачно, но может быть приемлемо в вашем случае.

вы также можете поддерживать таблицу кэша логического уровня с помощью триггеров. Эта таблица будет иметь столбец для каждого столбца в предложении GROUP BY со столбцом Count для хранения количества строк для данного значения ключа группировки. Каждый раз, когда строка добавляется или обновляется в базовой таблице, вставьте или увеличьте/уменьшите строку счетчика в сводной таблице для этого конкретного ключа группировки. Это может быть лучше, чем подход поддельного материализованного представления, поскольку кэшированная сводка всегда будет обновляться, и каждое обновление выполняется постепенно и должно иметь меньшее влияние на ресурсы. Я думаю, ты . однако придется следить за конфликтом блокировки в таблице кэша.

Если у вас есть InnoDB, count(*) и любая другая агрегатная функция выполнит сканирование таблицы. Я вижу здесь несколько решений:

используйте триггеры и храните агрегаты в отдельной таблице. Плюсы: честность. Минусы: медленные обновления
использовать очереди обработки. Плюсы: быстрые обновления. Минусы: старое состояние может сохраняться до тех пор, пока очередь не будет обработана, поэтому пользователь может почувствовать недостаток целостности.
полностью отделите слой доступа к хранилищу и храните агрегаты в отдельной таблице. Этот слой хранения будет знать структуру данных и может применять дельты вместо выполнения полного подсчета. Например, если вы предоставляете функциональность "addObject", вы будете знать, когда объект был добавлен, и, таким образом, совокупность будет затронута. Тогда вы делаете только update table set count = count + 1 . Плюсы: быстрые обновления, целостность (вы можете использовать блокировку, хотя в случае, если несколько клиентов могут изменить одну и ту же запись). Минусы: вы пара немного бизнес-логики и хранения.

Я вижу, что несколько человек спросили, какой движок вы используете для запроса. Я бы настоятельно рекомендуем вам использовать MyISAM для следующих reasions:

InnoDB в - @Sorin Mocanu правильно определил, что вы будете выполнять полное сканирование таблицы независимо от индексов.

MyISAM - всегда сохраняет текущее количество строк под рукой.

наконец, как заявил @justin, убедитесь, что у вас есть правильный индекс покрытия:

есть момент, в котором вам действительно нужно больше RAM / CPUs / IO. Возможно, вы нажали это для своего оборудования.

Я отмечу, что обычно не эффективно использовать индексы (если они не являются покрытие) для запросов, которые достигают более 1-2% от общего числа строк в таблице. Если ваш большой запрос выполняет поиск индекса и поиск закладок, это может быть из-за кэшированного плана, который был только из общего запроса дня. Попробуйте добавить in WITH (INDEX=0), чтобы заставить сканирование таблицы и посмотреть, быстрее ли это.

Если вы какой размер всей таблицы, вы должны запросить мета-таблицы или информационную схему (которые существуют на каждой СУБД, которую я знаю, но я не уверен в MySQL). Если ваш запрос является выборочным, вы должны убедиться, что для него есть индекс.

AFAIK вы больше ничего не можете сделать.

Я бы предложил архивировать данные, если нет какой-либо конкретной причины хранить их в базе данных или вы можете разделить данные и запускать запросы отдельно.

Всем привет. Возможно переработал, мозг не находит готового решения сходу.

GROUP BY `browser`

В таблице несколько миллионов записей. Результат выдает за 200 с чем то секунд.

- поле user_id - int(10)

- поле browser - VARCHAR(50),

- сделан индекс по полю user_id

Как максимально возможно это ускорить?

Заранее спасибо за Ваше время.

Создать индекс из browser + user_id

Как вариант иметь поле browser_crc (INT) и там хранить crc число от поля browser . Тогда идекс по browser_crc + user_id по идее будет быстрее, так как это числа.

P.S. "ORDER BY NULL" - это что такое вообще ?

Stek:
Создать индекс из browser + user_id

P.S. "ORDER BY NULL" - это что такое вообще ?

Спасибо за ответ.

1. ORDER BY NULL - отмена сортировки. Заметил что иногда из за этого есть прирост в скорости.

2. Спасибо за совет насчет индекса. Скажите, а у меня user_id уже используется в составном индексе в данной таблице. Не приведет ли к еще большим тормозам создание еще одного индекса, где будет упоминаться user_id ?

Доп. индекс дает прирост нагрузки при добавлении записей, выборку он только ускоряет.

Отмена сортировки – убрать конструкцию ORDER BY. Вы зачем вообще группировку используете? Она нужна для т.н. агрегатных ф-ций. Если вы просто хотите распределить выборку по группам, используйте сортировку по соотв. полю: ORDER BY `browser`.

Stek, miketomlin, спасибо за содействие.

Вопрос: мне стоит добавить browser в существующий составной индекс где уже используется user_id или лучше создать новый индекс на browser и user_id?

На уточняющие вопросы принято отвечать.

Для обычной сортировки можно включить browser в существующий индекс, только соблюдайте «смежность» и нужный порядок следования составных частей в индексе (сначала из WHERE, потом из ORDER BY).

elitedesign:
Всем привет. Возможно переработал, мозг не находит готового решения сходу.

SELECT `browser`,`stream_id`,`source_id`,`service_id`,`user_id`
FROM clients_log
WHERE `user_id`!='0'
GROUP BY `browser`
ORDER BY NULL

В таблице несколько миллионов записей. Результат выдает за 200 с чем то секунд.
- поле user_id - int(10)
- поле browser - VARCHAR(50),
- сделан индекс по полю user_id

Как максимально возможно это ускорить?

Заранее спасибо за Ваше время.

1) Попробуйте запрос вида

2) В любом случае уберите order by null

3) Убедитесь что mysql хотя бы 5.6 версии, а в идеале 5.7. В 5.6 пофиксили много багов с оптимизацией запросов, в 5.7 еще дочистили.

4) Добавьте индекс на browser, user_id или user_id, browser

5) Подумайте о перестройке структуры БД.

При нескольких миллионах записей хранить browser varchar(50) несколько бессмысленно, тем более varchar(50) может обрезать юзер-агента при такой короткой длине. Вынесите browser в отдельную таблицу, оставьте референс в виде browser_id int.

пысы: мы бы начали с пункта 5.

Разработка крупных и средних проектов. Можно с криптой. Разумные цены. Хорошее качество. Адекватный подход.

Я так понимаю нужен distinct, и соответственно напрашивается индекс на все вот эти вот поля:

По логике наверное первым указывать юзера?

Ну и в целом конечно edogs правильно написал и по юзерагенту и по остальному.

А я бы прежде чем начать гадать по гуще, попробовал бы запустить с explain

Разработка проектов на Symfony, Laravel, 1C-Bitrix, UMI.CMS, OctoberCMS

Так практический опыт, которым тут делятся, обычно на этом и основан. Или тут собрались одни теоретики?

Хотя лично мне даже назначение запроса не до конца понятно (ТСу влом ответить, зачем это нужно), поэтому отчасти приходится гадать.

edogs:

2) В любом случае уберите order by null

Ещё один. Вы знаете, для чего это нужно? Специально для вас:

By default, MySQL sorts all GROUP BY col1, col2, . queries as if you specified ORDER BY col1, col2, . in the query as well. If you include an explicit ORDER BY clause that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs.

If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL.

ТС, делайте отдельную таблицу по браузерам, каждому USER_AGENT отдельный id, из других таблиц ссылайтесь на эту таблицу по id, городить огороды с хэшами - чушь, базу забьёте ещё больше всяким хламом.

В этой таблице сделайте уникальный ключ по USER_AGENT, а можете и по browser_crc (INT), как выше предлагали, только смысла в этом не вижу.

Это если необходимо выбрать USER_AGENT из базы, а если нет, то вообще без JOIN'ов обойдётесь, тупо по `browser_Id` группировать. Структуру таблиц сами додумаете, не маленькие.

И ещё, запрос без лимитов на таком количестве данных - слабоумие и отвага?

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

1. Ни каких подзапросов, только JOIN
Как я уже писал ранее, если выборка 1 к 1 или надо что-то просуммировать, то ни каких подзапросов, только join.
Стоит заметить, что в большинстве случаев оптимизатор сможет развернуть подзапрос в join, но это может случиться не всегда.

2. Выбор IN или EXISTS ?
На самом деле это сложный выбор и правильное решение можно получить только опытным путем.
Я дам только несколько советов:
* Если в основной выборке много строк, а в подзапросе мало, то ваш выбор IN. Т.к. в этом случае запрос в in выполнится один раз и сразу ограничит большую основную таблицу.
* Если в подзапросе сложный запрос, а в основной выборке относительно мало строк, то ваш выбор EXISTS. В этом случае сложный запрос выполнится не так часто.
* Если и там и там сложно, то это повод изменить логику на джойны.

3. Не забывайте про индексы
Совет для совсем новичков: вешайте индексы на столбцы по которым джойните таблицы.

4. По возможности не используйте OR.
Проведите тесты, возможно UNION выглядит не так элегантно, за то запрос может выполнится значительно быстрей. Причина в том, что в случае OR индексы почти не используются в join.

5. По возможности не используйте WITH в oracle.
Значительно облегчает жизнь, если запрос в with необходимо использовать несколько раз ( с хинтом materialize ) в основной выборке или если число строк в подзапросе не значительно.
Во всех других случаях необходимо использовать прямые подзапросы в from или взаранее подготовленную таблицу с нужными индексами и данными из WITH.
Причина плохой работы WITH в том, что при его джойне не используются ни какие индексы и если данных в нем много, то все встанет. Вторая причина в том, что оптимизатору сложно определить сколько данных нам вернет with и оптимизатор не может построить правильный план запроса.
В большинстве случаев WITH без +materialize все равно будет развернут в основной запрос.

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

7. Используйте KEEP взамен корреляционных подзапросов.
В ORACLE есть очень полезные аналитические функции, которые упростят ваши запросы. Один из них - это KEEP.
KEEP позволит сделать вам сортировку или группировку основной выборки без дополнительно запроса.
Пример: отобрать контрагента для номенклатуры, который раньше остальных был к ней подвязан. У одной номенклатуры может быть несколько поставщиков.
При обычном бы подходе пришлось бы делать корреляционный подзапрос для каждой номенклатуры с выбором минимальной даты.
Но не злоупотребляйте большим числом аналитических функций, особенно если они имеют разные сортировки. Каждая разная сортировка - это новое сканирование окна.

8. Гуляние по выборке вверх-вниз
Менее популярная функция, но не менее полезная. Позволяет смещать текущую строку выборки на N элементов вверх или вниз. Бывает полезно, если необходимо сравнить показатели рядом стоящих строк.
Следующий пример отбирает продажи департаментов отсортированных по дате. К основной выборке добавляются столбцы со следующим и предыдущим значением выручки. Второй параметр - это на сколько строк сместиться, третьи - параметр по-умолчанию, если данные соседа не нашлись. При обычном подходе бы пришлось это делать через логику приложения.

9. Direct Path Read
Установка этой настройки (настройкой или параллельным запросом) - чтение данных напрямую в PGA, минуя буферный кэш. Что укоряет последующие этапы запроса, т.к. не используется UNDO и защелки совместного доступа.

10. Direct IO
Использование прямой записи/чтения с диска без использования буфера файловой системы (файловая система конкретно для СУБД).
* В случае чтения преимущество в использовании буферного кэша БД, замен кэша ФС (кэш бд лучше заточен на работу с sql)
* В случае записи, прямая запись гарантирует, что данные не потеряются в буфере ФС в случае выключения электричества (для redolog всегда использует fsync, в не зависимости от типа ФС)

Читайте также: