Блог

Я только что прочитал часть статьи по оптимизации и ошибся в следующем утверждении:

При использовании SQL замените операторы OR с помощью UNION :

 select username from users where company = ‘bbc’ or company = ‘itv’;
 

Для:

 select username from users where company = ‘bbc’ union
select username from users where company = ‘itv’;
 

Из быстрого EXPLAIN :

Использование OR :

Использование UNION :

Разве это не означает UNION , что это удваивает работу?

Хотя я понимаю UNION , что это может быть более эффективно для определенных СУБД и определенных схем таблиц, это не совсем так, как предлагает автор.

Вопрос

Я ошибаюсь?

Либо в статье, которую вы прочитали, использовался плохой пример, либо вы неверно истолковали их точку зрения.

 select username from users where company = 'bbc' or company = 'itv';
 

Это эквивалентно:

 select username from users where company IN ('bbc', 'itv');
 

MySQL может использовать индекс company для этого запроса просто отлично. Нет необходимости делать какое-либо ОБЪЕДИНЕНИЕ.

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

 select username from users where company = 'bbc' or city = 'London';
 

Предположим, что есть индекс company и отдельный индекс city . Учитывая, что MySQL обычно использует только один индекс для каждой таблицы в данном запросе, какой индекс он должен использовать? Если он использует индекс on company , ему все равно придется выполнять сканирование таблицы, чтобы найти строки, где city находится Лондон. Если он использует индекс on city , ему нужно будет выполнить сканирование таблицы на наличие строк, где company находится bbc.

UNION Решение предназначено для этого типа случаев.

 select username from users where company = 'bbc' 
union
select username from users where city = 'London';
 

Теперь каждый подзапрос может использовать индекс для своего поиска, а результаты подзапроса объединяются с помощью UNION .

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

Мой ответ заключается в том, что индексы помогают сократить результирующий набор до небольшого количества строк, прежде чем произойдет ОБЪЕДИНЕНИЕ. ОБЪЕДИНЕНИЕ действительно устраняет дубликаты, но для этого ему нужно только отсортировать небольшой набор результатов. Могут быть случаи, когда предложения WHERE соответствуют значительной части таблицы, и сортировка во время ОБЪЕДИНЕНИЯ так же дорога, как простое сканирование таблицы. Но чаще всего результирующий набор уменьшается за счет индексированных поисковых запросов, поэтому сортировка намного дешевле, чем сканирование таблицы.

Разница зависит от данных в таблице и искомых терминов. Единственный способ определить наилучшее решение для данного запроса — попробовать оба метода в профилировщике запросов MySQL и сравнить их производительность.

Это не один и тот же запрос.

У меня нет большого опыта работы с MySQL, поэтому я не уверен, что делает или не делает оптимизатор запросов, но вот мои мысли из моего общего опыта (в первую очередь ms sql server).

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

 select distinct username from users where company = ‘bbc’ or company = ‘itv’;
 

и

 select username from users where company = ‘bbc’ 
union
select username from users where company = ‘itv’;
 

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

 select username from users where company = ‘bbc’ or company = ‘itv’;
 

и

 select username from users where company = ‘bbc’ 
union all
select username from users where company = ‘itv’;
 

Это зависит от того, что в конечном итоге делает оптимизатор, исходя из размера данных, индексов, версии программного обеспечения и т. Д.

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

Кроме того, ОБЪЕДИНЕНИЕ имеет некоторые накладные расходы, поскольку оно создает набор сброса (без дубликатов). Каждый оператор в ОБЪЕДИНЕНИИ должен выполняться довольно быстро, если company индексируется… не уверен, что это действительно удвоит работу.

Итог

Если у вас действительно нет острой необходимости выжимать максимум скорости из вашего запроса, вероятно, лучше просто выбрать форму, которая наилучшим образом передает ваши намерения… ИЛИ

Обновить

Я также хотел упомянуть В. Я считаю, что следующий запрос даст лучшую производительность, чем OR (это также форма, которую я предпочитаю).:

select username from users where company in ('bbc', 'itv');

Это мой результат теста

При использовании UNION — Query потребовалось 13.8699
несколько секунд, чтобы проверить основной тип выбора — 247685

когда использование ИЛИ — запроса заняло 0.0126 несколько секунд, и строка проверила первичный тип выбора — 495371

MySQL использует один индекс для запроса, поэтому, когда мы используем or mysql, мы используем индекс одного столбца и сканируем всю таблицу для другого столбца

другая часть объединения может работать 2 раза

вот почему or быстрее, чем union

Почти во всех случаях версия union or union all будет выполнять два полных сканирования таблицы users.

or Версия намного лучше на практике, поскольку она будет сканировать таблицу только один раз. Он также будет использовать индекс только один раз, если он доступен.

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

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

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