Where или join что быстрее

JOIN быстрее, чем WHERE?

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

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

Теперь я хочу получить все документы, у которых более 500 просмотров. На ум приходят два решения:

Оба запроса эквивалентны или есть один способ, который намного лучше другого? Если да, то почему?

РЕДАКТИРОВАТЬ: как указано в ответах, этот вопрос был нацелен на SQL Server, но мне было бы интересно узнать, отличается ли он от других механизмов баз данных (MySQL и т. Д.).

10 ответов

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

SQL-сервер выполняет запросы в следующем порядке (это должно дать вам представление о функциях предложений WHERE и JOIN)

Порядок обработки запросов Microsoft SQL Server

(Шаг 8) ВЫБРАТЬ (Шаг 9) ОТЛИЧИТЬ (Шаг 11)
(Шаг 1) ИЗ left_table
(Шаг 3) join_type JOIN right_table
(Шаг 2) ON join_condition
(Шаг 4) ГДЕ where_condition
(шаг 5) GROUP BY group_by_list
(шаг 6) WITH [CUBE | ROLLUP]
(шаг 7) HAVING available_clause
(Шаг 10) ORDER BY order_by_list

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

Для MS-SQL оба запроса приводят к одинаковым планам выполнения, но имейте в виду:

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

По крайней мере, в MySQL они оба будут оптимизированы для одного и того же запроса.

Когда вы используете Sqlite: синтаксис where немного быстрее, потому что Sqlite сначала переводит синтаксис соединения в синтаксис where перед выполнением запроса.

Также обратите внимание, что вопреки распространенному мнению, эти два понятия не эквивалентны. Некоторые вещи намного более неудобны, а некоторые просто невозможны. В статье Калена Делани Inside SQL Server 2000 приведены некоторые примеры; не уверен, что это делают новые версии, потому что этот синтаксис соединения в любом случае устарел.

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

Если вам когда-либо понадобится использовать внешние соединения, вы должны знать, что синтаксис * = устарел в SQL Server и скоро будет удален. Кроме того, в настоящее время он не всегда работает должным образом и может не давать правильных результатов, поэтому его НИКОГДА не следует использовать. Сочетание явных внешних объединений и соединений с предложением where (неявные объединения) значительно усложняет чтение и понимание запроса специалисту по обслуживанию.

Думаю, это тоже не имеет значения. Конечно, вы можете проверить, идентичен ли план объяснения этих двух запросов. Чтобы просмотреть план объяснения в MySQL, вы должны поставить ключевое слово «объяснение» перед оператором, например:

Я уверен, что в MSSQL тоже есть эквивалент.

Между прочим: похоже, что это отношение 1: 1, поэтому я бы просто включил атрибут nbviews непосредственно в таблицу документа, поэтому вы можете сохранить соединение.

Источник

Исследуем производительность JOIN в MySQL

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

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

О чём я не буду писать

Для начала я бы хотел сразу сказать, что я не буду делать:

— тюнинг MySQL: все настройки берутся по умолчанию ( в том числе innodb_buffer_pool_size = 8 Мб и прочее )
— интеграцию с языками программирования: все запросы будут делаться через MySQL клиент Sequel Pro, и время будет замеряться исходя из его показаний
— очевидные вещи, вроде джойна при выборке 3х строк: вопрос, экономить на спичках, или нет, я рассматривать не хочу — мы будем рассматривать экономию в десятки раз, а не десятки процентов

Начальные условия

У нас будет две простых до безобразия таблички ( таблицы были сделаны просто для примера и заполнены случайными данными ):

Calls — 10 млн строк:

idINT PRIMARY KEY AUTO_INCREMENT
user_idINT
costINT
call_dtDATETIME
tariff_idINT

Users — 100 тыс строк:

idINT PRIMARY KEY AUTO_INCREMENT
birthdateDATE
nameVARCHAR(10)
sexENUM(‘M’,’F’)

Названия, я думаю, говорят сами за себя, индексы есть только по первичному ключу (id). Чисто в принципе, мы бы могли создать какие-нибудь индексы, которые бы нам помогли выполнять те запросы, которые мы будем исследовать, но у нас цель другая, а именно — исследовать, насколько быстро работает JOIN.

Типы таблиц

Для целей исследования, таблица Calls бралась двух видов — MyISAM и InnoDB, а таблица Users, к которой мы делаем JOIN, трёх видов — MyISAM, InnoDB и MEMORY

First blood

Все тесты проводились на моём ноутбуке, с MySQL версии 5.5.9 на Mac OS X с дефолтными настройками от MAMP. Все таблицы вмещались в память полностью, запросы прогонялись несколько раз, чтобы убедиться, что всё попадает в кеш.

Для начала, давайте просто посмотрим на скорость просмотра строк в MyISAM и InnoDB, выполнив такой запрос (напомню, что индексов ни по цене, ни по user_id нет — мы измеряем скорость FULL SCAN в MySQL):

Результаты (погрешность менее 5%):

InnoDB, msMyISAM, ms
15 360862
25 3901 150

Не хочу заниматься более подробным изучением, почему на аггрегирующие выборки в MyISAM так влияет кол-во попавших под WHERE строк, но факт остается фактом — при полном последовательном просмотре таблицы, MyISAM быстрее InnoDB в 4.5 раза. Отсюда и мнение о том, что InnoDB «тормоз», и о том, что сама MySQL (с MyISAM) очень шустра.

Мини-вывод: при полном последовательном просмотре MyISAM в 5 раз быстрее InnoDB

Давайте теперь подключим к делу таблицу Users — не зря же мы её создавали.
Мы будем исследовать запросы такого плана:

Параметр для cost подбирается таким образом, чтобы под выборку попал определенный процент записей в таблице Calls

Если мы сделаем запрос, приведенный выше, к мускулю, с параметром cost, который будет соответствовать N% строк, то MySQL сделает джойн всего-лишь в N% случаев, а для остальных (100-N)% строк он JOIN делать не будет. Вот такой MySQL умница.

Давайте же приступим к результатам тестирования с JOIN:

FULL SCAN + JOIN 0.1% строк

Users \ CallsInnoDB, ms (только JOIN, ms)MyISAM, ms (только JOIN, ms)
InnoDB5 450 (

100)MEMORY5 350 (

100)

Пока что времена отличаются очень несущественно от FULL SCAN. Оно и понятно — ведь JOIN делается для мизерного количества строк.

FULL SCAN + JOIN 1% строк

Users \ CallsInnoDB, ms (только JOIN, ms)MyISAM, ms (только JOIN, ms)
InnoDB5 660 (300)999 (140)
MyISAM6 530 (1 200)1 810 (950)
MEMORY5 460 (100)911 (65)

Забавно, да? Всего-лишь 1% строк джойнится, а результаты для MyISAM + MyISAM больше в 2 раза, чем для MyISAM + InnoDB. Довольно забавно, что JOIN к InnoDB в данном случае оказывается быстрее, чем JOIN к MyISAM. И это мы ещё не начали тестировать :)!

FULL SCAN + JOIN 10% строк

Users \ CallsInnoDB, msMyISAM, ms
InnoDB7 230 (1 900)2 190 (990)
MyISAM16 100 (8 800)10 200 (9 000)
MEMORY6 080 (700)1 440 (580)

За державу (MyISAM) обидно, а что поделать… Выходит, MyISAM не такой уж шустрый… Или нет? Давайте посмотрим на результаты финального тестирования

FULL SCAN + JOIN 100% строк

Users \ CallsInnoDB, msMyISAM, ms
InnoDB18 000 (14 650)12 500 (11 655)
MyISAM100 000 (96 650)91 600 (90 750)
MEMORY10 500 (7 150)5 280 (4 435)

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

Ну а с MEMORY, я думаю, всё ясно — MEMORY дает оверхед в 525% (4 435 ms) на джойн по PK, InnoDB дает оверхед в 1 380% (11 655 ms), а про MyISAM стыдно говорить.

Замена JOIN на IN(. )

Зоркий глаз мог заметить, что для нашего сценария (когда мы делаем JOIN к users, чтобы отсеять всех женщин из таблицы звонков) есть способ без JOIN, а с простым перечислением всех user_id в IN():

Такой запрос к таблице типа MyISAM отработает за 3 730 мс, а к InnoDB — за 8 290 мс. Зоркий глаз может заметить, что этот способ быстрее, чем JOIN к MEMORY, хоть и не намного. Этот способ подходит в случаях, если у вас очень быстрое подключение к MySQL (например UNIX socket). В остальных случаях, ИМХО, при таких количествах записей очевидно, что гонять огромное количество трафика между MySQL сервером и сервером приложений — не самая лучшая идея.

Выводы можете сделать сами: если нужно много джойнить, и почему-то нужна высокая производительность, используйте комбинацию MyISAM + InnoDB, или просто InnoDB + InnoDB, если джойнов больше одного. Ну а если вы — экстремал, можете использовать MyISAM + MEMORY для получения максимальной производительности при любых сценариях. Есть ещё вариант с MEMORY+MEMORY, но для большого количества записей я бы не стал так делать ;).

UPD: Хочется поблагодарить хабраюзера homm за очень полезные комментарии, например за этот. В общем, очень рекомендую прочитать комментарии, в них разъясняется много вещей, которые почему-то для читающих не были очевидны:
— кэш запросов отключен
— JOIN делается по первичному ключу
— индексы на таблицу Calls не создаются, потому что мы не ставим задачу оптимизировать какой-то конкретный запрос

Источник

Что быстрее?

Что лучше, быстрее, правельнее?
SELECT `users`.`name` FROM `users`, `groups` WHERE `groups`.`id` = ‘someid’ AND `groups`.`id` =.

SQL и PHP код, что быстрее?
На сайте есть база данных MySQL. Сайт написан на PHP. Необходимо выводить данные с базы.

Что быстрее читать с файлов или запрос к базе?
Здравствуйте. Меня интересует вопрос. Поясняю. Например у меня есть 20-50 папок и в каждой есть.

Что быстрее и как лучше реализовать список категорий
1. Вопрос простой: что будет быстрее запрос типа select * или select id, name, грубо говоря с.

По идее должно быть одинаково, но правильнее посмотреть план выполнения в обоих случаях

При inner join мы можем пользовать индекс по id.
При outer join для той таблицы, в которой записей больше (т.е. есть не соответствующие второй), будет использован full scan. Что при большой таблице не шибко быстро.

Where или join что быстрее. Смотреть фото Where или join что быстрее. Смотреть картинку Where или join что быстрее. Картинка про Where или join что быстрее. Фото Where или join что быстрееPhp+mysql что будет если запрос SELECT выполнится быстрее чем UPDATE (в разных потоках)
Ребята. Вот объясните мне пожалуйста. Может ли быть такое?: Предположим, что открылось 2.

Как сделать быстрее
Можно ли оптимизировать код SELECT `Work`.`UserId` AS id, COUNT( `Work`.`statusId` ) AS dost.

Что быстрее WHERE x=1 или WHERE x IN (1)?
Есть какие-нибудь сведения насчет данных команд WHERE x=1 или WHERE x IN (1)? Вопрос в том, что.

Источник

Предложение WHERE лучше выполнять до IN и JOIN или после

В конце статьи было написано ON, а предложение JOIN рассматривается перед WHERE.

Предположим, у нас есть основная таблица, в которой записано 10 миллионов записей, и подробная таблица (которая имеет ссылку на главную таблицу (FK)) с 50 миллионами записей. У нас есть запрос, который хочет всего 100 записей подробной таблицы в соответствии с PK в главной таблице.

В этой ситуации ON и JOIN выполняются до WHERE? Я имею в виду, что у нас есть 500 миллионов записей после JOIN, а затем WHERE применить к нему? Или сначала WHERE apply, а затем JOIN и ON Рассмотреть? Если второй ответ верен, он имеет несогласованность с top статья?

5 ответов

Это не имеет значения

Логический порядок обработки всегда соблюдается: независимо от фактического порядка обработки

INNER JOINs и условия WHERE эффективно ассоциативны и коммутативны (отсюда синтаксис ANSI-89 «соединение в where»), поэтому фактический порядок не имеет значения.

Логический порядок становится важным с внешними соединениями и более сложными запросами: применение WHERE к ВНЕШНЕЙ таблице полностью меняет логику.

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

В статье просто говорится, что это логический порядок выполнения, а также в конце абзаца добавляется эта строка;)

«Обратите внимание, что фактическое физическое выполнение инструкции определяется обработчиком запросов, и порядок может отличаться от этого списка».

Нет определенного порядка. Механизм SQL определяет порядок выполнения операций на основе стратегии выполнения, выбранной его оптимизатором.

Просто перечитайте отличную серию по запросу Пола Уайта. Оптимизатор и вспомнил об этом вопросе.

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

По (надеюсь!) Очевидным причинам попробуйте это только на экземпляре разработки и не забудьте снова включить их и удалить все неоптимальные планы из кеша.

Вы можете видеть, что с отключенными этими двумя правилами выполняется декартово соединение и фильтрация после него.

Where или join что быстрее. Смотреть фото Where или join что быстрее. Смотреть картинку Where или join что быстрее. Картинка про Where или join что быстрее. Фото Where или join что быстрее

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

Where или join что быстрее. Смотреть фото Where или join что быстрее. Смотреть картинку Where или join что быстрее. Картинка про Where или join что быстрее. Фото Where или join что быстрее

В некоторой степени вы можете иногда контролировать это (или мешать этому) с помощью вашего SQL, например, с помощью агрегатов в подзапросах.

Логический порядок обработки ограничений в запросе можно преобразовать только в соответствии с известными инвариантными преобразованиями.

По-прежнему логически эквивалентен:

И, как правило, у них будет один и тот же план выполнения.

И поэтому оптимизатор не собирается преобразовывать их в один и тот же план выполнения.

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

Источник

Условие в JOIN или WHERE

Есть ли разница (производительность, передовой опыт и т. Д.) Между постановкой условия в предложении JOIN и предложении WHERE?

Что вы предпочитаете (и, возможно, почему)?

10 ответов

Я рекомендую вам писать запросы в максимально удобочитаемом виде.

Иногда это включает в себя создание INNER JOIN относительно «неполного» и включение некоторых критериев в WHERE просто для того, чтобы упростить сопровождение списков критериев фильтрации.

Но это, конечно, зависит от обстоятельств.

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

Однако то, где вы помещаете условие, имеет огромное значение, если вы используете левое или правое соединение. Например, рассмотрим эти два запроса:

Первый предоставит вам только те записи, заказ которых датирован позднее 15 мая 2009 г., таким образом, левое соединение будет преобразовано во внутреннее соединение.

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

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

Большинство продуктов СУБД оптимизируют оба запроса одинаково. В «Настройка производительности SQL» Питера Гулуцана и Труди Пельцер они протестировали несколько марок СУБД и не обнаружили разницы в производительности.

Я предпочитаю хранить условия соединения отдельно от условий ограничения запросов.

Я предпочитаю, чтобы JOIN объединял полные таблицы / представления, а затем использовал WHERE, чтобы ввести предикат результирующего набора.

Он кажется синтаксически чище.

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

Меня всегда слегка забавляет, когда кто-то демонстрирует результаты своих тестов SQL, и они выполнили обе версии sproc 50 000 раз в полночь на сервере разработки и сравнивают среднее время.

Помещение условия в соединение кажется мне «семантически неправильным», поскольку JOIN не для этого предназначены. Но это очень качественно.

Дополнительная проблема: если вы решите переключиться с внутреннего соединения на, скажем, правильное соединение, наличие условия внутри JOIN может привести к неожиданным результатам.

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

Лучше добавить условие в Join. Производительность важнее читабельности. Для больших наборов данных это важно.

ГДЕ будет фильтровать после того, как СОЕДИНЕНИЕ произошло.

Отфильтруйте JOIN, чтобы предотвратить добавление строк в процессе JOIN.

Источник

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *