предисловие
Личные микроблоги в основном основаны на методах обучения коммуникации, обмена опытом и хорошей памяти не так хорошо, как плохая запись. Использование блогов для сохранения учебных материалов — хороший способ. Ниже приведены некоторые общие оптимизации для моих многостоловых запросов к базе данных oracle для оптимизации базы данных.
Распространенные многостоловые запросы
Далее проверьте результаты:
Приведенный выше метод (левый запрос подключения) является одной из наиболее часто используемых форм в моем проекте. Пока я не буду комментировать качество и эффективность. Короче говоря, результаты запроса хорошо показаны. Далее Подробно рассмотрим несколько способов и различий многостоловых запросов.
UNION ALL
Как видно из заголовка, это первый способ, которым мы вводим работу нескольких таблиц: UNION и UNION ALL, UNION и UNION ALL также имеют определенную разницу, сначала очистим основные понятия, UNION и UNION ALL используются для объединения нескольких наборов данных Например, объединение результатов двух операторов выбора в одно целое.
Результаты запроса следующие: 
Как показано на рисунке выше, результирующие наборы mzdm_ для 2 и 5 объединяются вместе, затем следующий UNION ALL заменяется на UNION и затем смотрите на результаты:
Обратите внимание, что найти первый столбец BMH_ на рисунке выше несложно, UNION сортируется (сортировка по умолчанию, то есть сортировка по первому столбцу результата запроса), это то, что он делает с UNION Одно из различий между ALL, посмотрите на следующие два SQL и результаты запроса:
Результаты следующие: 
Результаты следующие:
Вы видите, что второй результат запроса должен быть включен в первый результат запроса, так в чем же разница между UNION и UNION ALL? Взгляните на каждого, во-первых, UNION ALL:
Как показано выше, нетрудно обнаружить, что с помощью UNION ALL для запроса суммы двух вышеуказанных наборов результатов, включая 6 пар дублированных данных + 5 отдельных данных, всего 17, затем посмотрите на UNION Результаты для: 
Очевидно, по сравнению с UNION ALL, UNION автоматически удаляет для нас 6 дублированных результатов, и в результате получается объединение двух вышеупомянутых наборов результатов, и сортировка отсутствует, что является UNION ALL Второе отличие от UNION, наконец, суммируем разницу между UNION и UNION ALL:
1. UNION автоматически удалит повторяющиеся результаты в нескольких наборах результатов, а UNION ALL отобразит все результаты независимо от того, являются ли они дубликатами.
2. UNION отсортирует набор результатов по правилам по умолчанию, в то время как UNION ALL не выполнит сортировку **.
Таким образом, с точки зрения эффективности, очевидно, что UNION ALL выше, чем UNION, потому что исключает работу сортировки и дедупликации. Конечно, следует отметить еще одну вещь: UNION и UNION ALL также можно использовать для объединения результирующего набора двух разных таблиц, но тип поля и номер должны совпадать, например:
Проверьте результаты выполнения:
Если конфигурация данных не совпадает или число столбцов не совпадает, будет сообщено об ошибке:
Если количество столбцов недостаточно, вместо этого можно использовать NULL, чтобы избежать ошибки на рисунке выше. Наконец, давайте возьмем другой пример, чтобы взглянуть на роль UNION ALL в некоторых более значимых сценариях. Сначала создадим временную таблицу:
Результаты следующие:
Наше требование также очень простое: подсчитайте количество вхождений каждого отдельного значения в NAME1 и NAME2. Поговорим об этой идее, сначала посчитайте количество вхождений каждого значения NAME1, затем подсчитайте количество вхождений каждого значения NAME2 и, наконец, объедините два набора результатов выше с UNION ALL и, наконец, выполните еще одну группировку и сортировку:
Результаты следующие:
Хорошо, запрос был выполнен очень хорошо, поэтому здесь будут представлены UNION и UNION ALL.
Использовать ли JOIN
В качестве заголовка пример, написанный в начале блога, использовал LEFT JOIN для завершения запроса на сопоставление двух таблиц, а затем вы можете использовать условие WHERE без JOIN для достижения того же эффекта:
Эффект бега заключается в следующем:
Оглядываясь на SQL и эффект запуска в начале блога, вы можете обнаружить, что он точно такой же, как на картинке выше. Какой из них больше подходит для использования?Формулировка JOIN является стандартом SQL-92. Когда связаны несколько таблиц, используйте метод JOIN для выполнения связанного запроса, чтобы более четко увидеть взаимосвязь между таблицами.Также удобно поддерживать SQL, поэтому не рекомендуется использовать метод запроса WHERE выше, но следует использовать формулировку JOIN.
В и СУЩЕСТВУЕТ
В качестве заголовка это также часто используется в запросе, особенно ключевое слово IN, которое довольно часто используется в проекте. Часто пишется оператор IN через цикл for и StringBuffer, тогда давайте более подробно рассмотрим IN и Сценарии использования EXISTS и проблемы эффективности по-прежнему иллюстрируются примерами, такими как это требование, для запроса оценок всех учеников ханьского языка:
Соблюдайте план выполнения:
Вы можете визуализировать диаграммы последовательности:
Аналогично замените IN на EXISTS и взгляните на SQL и план выполнения:
Соблюдайте план выполнения:
Как показано выше, хотя IN пишется с использованием HASH JOIN (хеш-соединение), а EXISTS пишется с использованием HASH JOIN RIGHT SEMI (хеш-соединение с правой половиной), но их план выполнения не отличается, эффективность остается той же, Это потому, что объем данных не велик, поэтому один выводВ простых запросах IN и EXISTS эквивалентны, Еще одна вещь должна быть ясна, в ранней версии кажется, что есть такие правила:
1. Набор результатов подзапроса мал, используйте IN.
2. Внешний вид небольшой, таблица подзапросов большая, используйте EXISTS.
Эти два утверждения совершенно неверны в Oracle11g! В Oracle8i это часто может быть правильным, но CBO Oracle 9i оптимизировал разницу между IN и EXISTS. У оптимизатора Oracle есть конвертер запросов. Хотя многие SQL написаны по-разному, оптимизатор Oracle будет выполнять запросы в соответствии с установленными правилами. Переписан, переписан как SQL, который, по мнению оптимизатора, является наиболее эффективным, поэтому запись в SQL может отличаться, но план выполнения точно такой же, поэтому есть другой вывод:Что касается более высокого эффекта IN и EXISTS, вы должны вовремя проверять PLAN, а не помнить фиксированный вывод, по крайней мере, в текущей версии Oracle.
INNER LEFT RIGHT FULL JOIN
Например, есть несколько часто используемых методов подключения, давайте посмотрим на различия между ними.
INNER JOIN
Первым является внутреннее соединение (INNER JOIN). Как следует из названия, INNER JOIN возвращает данные, соответствующие двум таблицам. Пример, начинающийся с blog, все еще переписывается как INNER JOIN:
Результаты следующие:
Можно видеть, что результат все тот же, что и выше, но этот пример не объясняет характеристики INNER JOIN, поэтому я заново создаю две таблицы для объяснения проблемы, на этот раз использую более классический Студенческий стол классный стол для тестирования:
Вставьте тестовые данные после создания таблицы:
Как показано выше, вы можете видеть, что это очень просто: таблица ученика вставляет 4 данные, а таблица класса вставляет данные 1. Используйте clsid таблицы ученика, чтобы сопоставить cid таблицы класса с запросом имени класса. Давайте посмотрим на запрос, используя INNER JOIN. заявление:
Вы можете увидеть результаты запроса после запуска:
Как показано выше, концепция INNER JOIN хорошо проверена, а именноВозврат данных, соответствующих обеим таблицамПоскольку таблица классов содержит только одну информацию класса 1 и одну информацию класса 5, а таблица учащихся содержит только двух учащихся класса 1 и ни одного ученика ни в одном классе 5, поэтому, естественно, могут быть возвращены только два.
LEFT JOIN
Как видно из заголовка, LEFT JOIN использует левую таблицу в качестве основной и возвращает все данные в левой таблице. Правая таблица возвращает только совпадающие данные. Измените приведенный выше SQL на LEFT JOIN и посмотрите:
Посмотрите на результаты операции:
Как показано на рисунке выше, это также очень просто, потому что правая таблица (таблица классов) не имеет данных для классов 2 и 3, поэтому имя класса не будет отображаться.
RIGHT JOIN
Как видно из заголовка, RIGHT JOIN и LEFT JOIN противоположны. Данные в правой таблице являются основной таблицей, а левая таблица возвращает только совпадающие данные. Аналогично, вышеприведенный SQL переписывается в форму RIGHT JOIN:
Результаты следующие:
Как показано выше, поскольку таблица классов является основной таблицей для сопоставления, сопоставляются данные 2 учащихся в 1 и 5 классе.
FULL JOIN
Как видно из заголовка, FULL JOIN предназначен для одновременного отображения всех результатов запроса независимо от того, совпадают ли левая и правая стороны, что эквивалентно объединению результатов LEFT JOIN и RIGHT JOIN. Тем не менее переписываем приведенный выше SQL как FULL JOIN и просматриваем результаты:
Результаты следующие:
До сих пор эти четыре метода запроса JOIN были кратко представлены. Концептуально они все еще будут хорошо поняты и различимы.
автокорреляция
Как видно из заголовка, это относительно особый способ использования сцены. Я лично чувствую, что если база данных спроектирована правильно, это требование не появится. Поскольку оно упомянуто, я приведу пример для иллюстрации. Принимая приведенную выше тестовую таблицу учеников в качестве примера, мне нужно добавить один. поле:
Принимая во внимание следующие требования, у каждого ученика есть непосредственный руководитель, который отвечает за проверку домашнего задания. Чтобы избежать мошенничества, учитель не назначает двух человек для проверки друг друга, а будет шататься один за другим. Например, ученик A проверяет ученика B, а ученик B проверяет ученика C, поэтому Наши данные таблицы могут описать эту проблему следующим образом:
Как показано выше, ЛИДЕР Чжан Сана — это Ли Си, ЛИДЕР Ли Си — это Сяо Мин, ЛИДЕР Сяо Мин — это Сяо Ли, а ЛИДЕР Сяо Ли — это Чжан Сан, тогда проблема заключается в том, что Как найти имя ЛИДЕРА каждого студента? Правильно, здесь используется запрос на самоассоциацию. Говоря простым языком, он должен дважды проверить одну и ту же таблицу и связать ее. Использование представлений для объяснения сбора данных более понятно, поэтому сначала создайте два представления:
Далее через запрос самоассоциации:
Результаты следующие:
Как показано на рисунке выше, имя ЛИДЕРА, соответствующее каждому студенту, определяется путем самоассоциации.
НЕ ВНУТРИ и НЕ СУЩЕСТВУЕТ
Например, теперь у нас есть форма информации о студенте и форма результата приема. Например, мы хотим знать, какие студенты не были приняты, то есть форма студента имеет данные, но форма приема не содержит данных студента, тогда вы можете использовать NOT IN Или НЕ СУЩЕСТВУЕТ, все же взгляните на разницу таким образом в сочетании с планом выполнения:
Соблюдайте план выполнения:
Затем преобразуйте SQL в NOT EXISTS и посмотрите на план выполнения:
Как показано выше, оба PLAN используют HASH JOIN RIGHT ANTI, поэтому их эффективность одинакова, поэтому нет никаких плюсов и минусов абсолютной эффективности в отношении NOT IN и NOT EXISTS в Oracle11g, это еще предстоит оценить и протестировать в PLAN Который является более эффективным
Обработка нулевого значения в многостоловом запросе
Например, исходя из следующих требований, мне необходимо проверить показатели приема студентов, чей пол не является мужчиной, но перед этим я сначала добавляю данные ученика, чей регистрационный номер (bmh_) является нулевым, в таблицу учеников следующим образом:
Далее напишите оператор запроса, здесь намеренно используйте ключевое слово NOT IN вместо ключевого слова IN:
Результат операции показан на следующем рисунке:
Мы были удивлены, обнаружив, что данные не были найдены. Это потому, что только один из 5000+ результатов в подзапросе после NOT IN имеет значение NULL, поэтому запрос в целом не будет Показывая любой результат, вы чувствуете, как мышь испортила горшок супа, что является одной из особенностей Oracle, а именно:Если подзапрос после ключевого слова NOT IN содержит нулевые значения, общий запрос вернет нулевойТаким образом, этот тип запроса должен добавлять условия, отличные от NULL, а именно:
Давайте снова посмотрим на результаты:
Как показано на рисунке выше, на этот раз это хороший запрос для получения нужных нам результатов.
резюме
Просто запишите различные режимы в многостоловых запросах Oracle и некоторые моменты и методы оптимизации, которые, я думаю, стоит отметить. Я надеюсь помочь и улучшить студентов, которых я читаю, The End.
Slow join behaviour with 'or' in predicate
I’m faced with a situation which I can’t understand and overcome.
In short we have left-join query like:
This works VERY slowly, while at the same time both separately:
b.key1 has normal index
b.key2 has normal index
I can’t understand the reason for such behavior? Am I missing something very basic in my join strategy or index usage?
Here we go with detailed plans:
WITHOUT OR(TOP_USTR_ADMIN_IP — index name for ustrip column):
Why does using or in the predicate give nested loops and no index usage? Is it possible to force index usage?
UPDATED: missattached wrong plan for «without or».fixed
1 Answer 1
OR is slower because you no longer can perform an index seek, but effectively forces the database engine to look through each leaf node in your index tree. With a single parameter (no OR), the engine can seek down through the index to the relevant leaf nodes, but when you ask it an OR, it scans the entire range of leaf nodes.
If the performance is too slow to tolerate, do as the comments suggest and try a UNION (no duplicate values) or a UNION ALL (allow duplicate values), or even two separate queries on their own and then join the results together in the code layer receiving the results.
Oracle join elimination
На первый взгляд это может показаться странным — зачем кто-то будет писать такой бессмысленный запрос? Но такое может происходить, если мы используем генерированный запрос или обращаемся к представлениям (view).
Трансформация inner join
Давайте рассмотрим небольшой пример (скрипты выполнялись на Oracle 11.2).
Теперь попробуем выполнить простой запрос и посмотрим на его план:
Несмотря на то, что мы запрашиваем колонку только из таблицы child, Oracle, тем не менее, выполняет честный inner join и впустую делает обращение к таблице parent.
Получается, оптимизатор не понимает, что в этом запросе соединение этих двух таблиц не приводит к какой-либо фильтрации или размножению строк. Значит, нужно помочь ему это понять.
Свяжем эти таблицы с помощью foreign key из child на parent и посмотрим на то, как изменится план запроса:
Как видно из плана запроса — этого оказалось достаточно.
Чтобы Oracle смог удалить лишние таблицы из запроса, соединенные через inner join, нужно чтобы между ними существовала связь foreign key — primary key (или unique constraint).
Трансформация outer join
Для того, чтобы Oracle мог убрать лишние таблицы из запроса в случае outer join — достаточно на колонке внешней таблицы, участвующей в соединении, был первичный ключ (primary key) или ограничение уникальности (unique constraint).
И попробуем выполнить следующий запрос:
Как видно из плана запроса, в этом случае Oracle так же догадался, что таблица parent_3 лишняя и ее можно удалить.
Число таблиц, которое может быть удалено из запроса, не ограничено. Join elimination удобно использовать, если существует дочерняя таблица, несколько родительских таблиц и результат их соединения выставлен в виде представления.
Создадим такое представление, которое объединит все наши таблицы и попробуем использовать его в запросе:
Как видно из плана, Oracle отлично справился и с таким запросом тоже.
Трансформация semi join и anti join
Для того, чтобы была возможность таких трансформаций: между таблицами должна быть связь foreign key — primary key, как и в случае inner join.
Сначала рассмотрим пример semi join:
А теперь пример anti join:
Как видно, с такими типами запросов Oracle тоже научился работать.
Трансформация self join
Гораздо реже, но встречаются запросы с соединением одной и той же таблицы. К счастью, join elimination распространяется и на них, но с небольшим условием — нужно чтобы в условии соединения использовалась колонка с первичным ключом (primary key) или ограничением уникальности (unique constraint).
Такой запрос тоже с успехом трансформируется:
Rely disable и join elimination
Есть еще одна интересная особенность join elimination — он продолжает работать даже в том случае, когда ограничения (foreign key и primary key) выключены (disable), но помечены как доверительные (rely).
Для начала просто попробуем отключить ограничения и посмотрим на план запроса:
Вполне ожидаемо, что join elimination перестал работать. А теперь попробуем указать rely disable для обоих ограничений:
Как видно, join elimination заработал вновь.
На самом деле, rely предназначен для немного другой трансформации запроса . В таких случаях требуется, чтобы параметр query_rewrite_integrity был установлен в «trusted» вместо стандартного «enforced», но, в нашем случае, он ни на что не влияет и все прекрасно работает и при значении «enforced».
К сожалению, ограничения rely disable вызывают join elimination только с inner join. Стоит так же отметить, что несмотря на то, что мы можем указывать rely disable primary key или rely disable foreign key для представлений — работать для join elimination это, к сожалению, не будет.
Параметр _optimizer_join_elimination_enabled
Вместе с таким замечательным способом трансформации запроса добавился еще и скрытый параметр _optimizer_join_elimination_enabled, который по умолчанию включен (true) и отвечает за использование этой трансформации.
Если она вам надоест, то ее всегда можно выключить:
Подсказки ELIMINATE_JOIN и NO_ELIMINATE_JOIN
Добавлено после комментария xtender.
Так же, чтобы контролировать эту трансформацию, можно применять подсказки оптимизатора.
Для того, чтобы включить трансформацию, используют подсказку ELIMINATE_JOIN:
Для того, чтобы выключить трансформацию, используют подсказку NO_ELIMINATE_JOIN:
Когда join elimination плохло
В комментариях ниже xtender дал ссылку на свой интересный пример, в котором показывается, что join elimination может ухудшать план выполнения запроса. А так же дал некоторые пояснения в дальнейших комментариях.
Трансформация одинаковых соединений
Есть еще один вариант трансформации — удаление одинаковых соединений из запроса:
Эта трансформация так же отлично работает и с подзапросами, которые превращаются в соединения (subquery unnesting):
Но, такой вариант трансформации имеет некоторые отличия.
1) Для него необязательно иметь связь foreign key — primary key (или unique constraint):
2) На него не влияет отключение параметра _optimizer_join_elimination_enabled:
Но хотя бы действуют подсказки:
Итог
Подводя краткий итог, хочется сказать, что такой способ трансформации может быть действительно полезен в ряде случаев. Но полагаться на него надо тоже с умом. Если внутри вашего представления что-то поменяется и Oracle больше не сможет гарантированно определять то, что связь с таким представлением не фильтрует или не умножает строки, вы получите неожиданную потерю скорости выполнения запроса.
Left Joins are what I want but they are very slow?
And I need to find all users who do not have a bio or shirtsize, (if no bio; then no shirtsize via relation) for any given season.
I originally wrote a query like:
but it is taking 10 seconds to complete now.
I am wondering how I can restructure the query (or possibly the problem) so that it will preform reasonably.
Here is the mysql explain: (ogu = subscribers, b = bio, tn = shirtshize)
The above is pretty sanitized, here’s the realz info:
and the real query looks like:
nid is season_id or bio_id (with a type); term_node is going to be the shirtsize
9 Answers 9
The query should be OK. I would run it through a query analyzer and refine the indexes on the tables.
Joins are one of the most expensive operations that you can perform on an SQL query. While it should be able to automatically optimize your query somewhat, maybe try restructuring it. First of all, I would instead of SELECT *, be sure to specify which columns you need from which relations. This will speed things up quite a bit.
If you only need the user ID for example:
That will allow the SQL database to restructure your query a little more efficiently on its own.
Obviously I haven’t checked this but it seems to be that what you want is to select any subscriber where there there isn’t a matching bio or the join between bios and shirtsizes fails. I would consider using NOT EXISTS for this condition. You’ll probably want indexes on bio.user_id and shirtsizes.bio_id.
EDIT:
Based on your update, you may want to create separate keys on each column instead of/in addition to having compound primary keys. It’s possible that the joins aren’t able to take optimal advantage of the compound primary indexes and an index on the join columns themselves may speed things up.
Is bio_id the primary key of bios? Is it really possible for there to be a bios row with b.user_id = subscribers.user_id but with b.bio_id NULL?
Are there shirtsize rows with shirtsize.bio_id NULL? Do those rows ever have shirtsize.size not NULL?
Would it be any quicker to do a difference between the list of subscribers for the relevant season and the list of subscribers for the season with bios and shirt sizes?
This avoids outer joins, which are not as fast as inner joins, and may therefore be quicker. On the other hand, it might be creating two large lists with very few differences between them. It is not clear whether the DISTINCT in the sub-query would improve or harm performance. It implies a sort operation (expensive) but paves the way for a merge-join if the MySQL optimizer supports such things.
There might be other notations available — MINUS or DIFFERENCE, for example.