разделение одного запроса на четыре, чтобы избежать массовых соединений?

Итак, у меня есть запрос, который выглядит так: 

SELECT col1, col2, col3 ...
FROM action_6_members m
LEFT JOIN action_6_5pts f ON f.member_id = m.id
LEFT JOIN action_6_10pts t ON t.member_id = m.id
LEFT JOIN action_6_weekly w ON w.member_id = m.id
WHERE `draw_id` = '1' ORDER BY m.id DESC LIMIT 0, 20;

Теперь это массовое объединение (3,5 миллиона *40 тысяч * 20 тысяч)

Итак, моя идея состояла в том, чтобы:

Сделать SELECT * FROM action_6_members WHEREdraw_id= '1' ORDER BY id DESC LIMIT 0, 20;

Затем повторите это, используя php для построения $in = "IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)";

Затем запустите
select * from action_6_5pts where member_id in $in
select * from action_6_10pts where member_id in $in
select * from action_6_weekly where member_id in $in

Затем объедините их все вместе с помощью php,

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

Замечу ли я значительный бонус к производительности?

Обновление
Итак, общее мнение таково: "НЕ ДЕЛАЙ ЭТОГО!"

Вот общий обзор приложения

Он получает код,

Код может быть либо 5pt, 10pt, либо еженедельным кодом,

Все три типа кода представлены в отдельных таблицах. в трех таблицах есть код и идентификатор участника

Идентификатор участника ссылается на идентификатор в таблица action_6_members.

Когда утверждается код, данные заполняются в таблице action_6_members.

Идентификатор этого участника затем заполняется в таблице для заявленного кода.

Приведенный выше запрос выбирает первые двадцать участников.

Итак, мой вопрос таков.

Что я могу сделать, чтобы улучшить это?

Поскольку в настоящее время все истекает до запросов завершено.

Участники Action_6_members

CREATE TABLE `action_6_members` (
  `id` int(11) NOT NULL auto_increment,
  `draw_id` int(11) NOT NULL,
  `mobile` varchar(255) NOT NULL,
  `fly_buys` varchar(255) NOT NULL,
  `signup_date` datetime NOT NULL,
  `club` int(11) NOT NULL default '0' COMMENT '1 = yes, 2 = no',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1337 DEFAULT CHARSET=latin1

Действие_6_ 5 и 10 пунктов

CREATE TABLE `action_6_5pts` (
  `code` varchar(255) NOT NULL,
  `member_id` int(11) NOT NULL,
  PRIMARY KEY  (`code`),
  KEY `member_id` (`member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Действие_6_ Еженедельно

CREATE TABLE `action_6_weekly` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(255) NOT NULL,
  `member_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `member_id` (`member_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3250001 DEFAULT CHARSET=latin1

Обновление 2: объясните запрос
id select_type table type possible_keys key       key_len ref  rows   Extra  
1  SIMPLE      m     ALL  \N            \N        \N      \N   1390   Using temporary; Using filesort  
1  SIMPLE      f     ALL  member_id     \N      \N      \N   36000  
1  SIMPLE      t     ALL  member_id     \N      \N      \N   18000  Using where  
1  SIMPLE      w     ref  member_id     member_id 4    m.id 525820 Using where

Только что закончил с этим: Последние данные о загрузке из БД 7.26, 4.60, 2.45

1.0 - это нормальная максимальная нагрузка...Все, что выше, означает, что он должен был "взорваться" и вызвать дополнительные процессы для обработки. т. е. 7,26 означает, что нагрузка в 7 раз превышает максимальную нагрузку на лезвие сервер и был вынужден обратиться к другим, чтобы помочь

Так что в настоящее время этот запрос больше, чем монстр, он ест монстров в качестве закуски...

Author: Hailwood, 2010-08-09
Source

8 answers

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

Эти границы таковы:

  1. В этом случае в MySQL не должно быть странного патологического поведения, скрывающегося в MySQL.
  2. У вас должны быть разумные индексы по всем необходимым столбцам.
  3. Нет (или нет вероятного ) случая который вы можете разумно обнаружить/обработать только в PHP, в котором вы хотели бы прервать запрос на полпути.
  4. Ваш результирующий набор не патологически огромен (например, он помещается в память и не превышает размера max_allowed_packet в my.cnf).

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

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

  • Не используйте MyISAM, если вы не установили, что вам это нужно. InnoDB - ваш друг, особенно если таблицы имеют приличный объем трафика записи. Замки с полным столом MyISAM действительно могут укусить вас. Наличие ВНЕШНИХ КЛЮЧЕЙ для ссылочной целостности также было бы милый.
  • action_6_weekly имеет id в качестве PRIMARY KEY и UNIQUE KEY вкл... id. Это излишне. PRIMARY KEY фактически является надмножеством UNIQUE KEY, вам не нужно создавать отдельный UNIQUE KEY.
  • Вывод EXPLAIN по вашему запросу был бы интересным.
 7
Author: Nicholas Knight, 2010-08-09 04:59:35

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

 1
Author: OMG Ponies, 2010-08-09 04:42:38

Для этого вам не нужно использовать PHP, вы можете сделать это в одном запросе с подзапросами или в хранимой процедуре с несколькими запросами.

Чтобы узнать, какой из них быстрее, сравните их.

 1
Author: Borealid, 2010-08-09 04:43:35

Как ни странно, я собираюсь не согласиться с консенсусом, ну, по крайней мере, частично.

Прежде всего, вы никогда не должны использовать ЛЕВОЕ СОЕДИНЕНИЕ. Это заманчиво, но это почти всегда плохая идея. В вашем случае я предполагаю, что таблицы action_6_5pts, action_6_10pts и action_weekly могут содержать не все идентификаторы участников. (Я предполагаю, что ваши данные, поэтому, если каждая таблица гарантированно содержит все идентификаторы участников, тогда уберите ЛЕВУЮ часть вашего запроса, и все будет в порядке.)

Я подозреваю, что есть вероятно, это лучший способ, которым вы могли бы выложить свои данные в первую очередь. Как правило, рекомендуется объединять одни и те же типы данных в одну таблицу. Я не хочу строить догадки о ваших данных, поэтому приведу вам псевдопример. Я много раз видел, как люди берут похожие данные и разделяют их на несколько таблиц (меньшие таблицы лучше, верно?). Не всегда. Например, если вы создавали систему счетов-фактур, может возникнуть соблазн подумать о разделении счетов-фактур по месяцам на отдельные столы. Таким образом, вы создаете invoice_Jan2010, invoice_Feb2010... и т.д. Но что, если вы захотите поискать? Один и тот же клиент, вероятно, работает не во все месяцы, поэтому трудно составить список только с этим клиентом, не используя ЛЕВОЕ СОЕДИНЕНИЕ. Фу. Нам не нравится ЛЕВОЕ ПРИСОЕДИНЕНИЕ! Это медленно!

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

Теперь в вашем случае, может быть, вы могли бы сделать флаги 5pts и 10pts в одной таблице, а еженедельная дата - это дата? Я делаю предположения, не зная больше, трудно дать вам "правильный" ответ.

Теперь я сказал, что не согласен с консенсусом. Если вы не изменяете свои данные, как правило, если у вас очень большая таблица, как вы говорите, разделение на 4 запроса с использованием операторов IN - лучшая идея, чем ОБЪЕДИНЕНИЕ СЛЕВА. Если вы хотите ускорить его, вы можете объединить все 4 в 1 с помощью UNION. Тот все равно должно быть быстрее, чем ЛЕВОЕ СОЕДИНЕНИЕ.

Вы также можете легко это доказать. Возьмите свой запрос, поставьте перед ним ключевое слово EXPLAIN и выполните его непосредственно в Mysql (используя один из инструментов: командную строку, графический интерфейс Mysql или даже phpmyadmin). Это даст вам представление о том, как он планирует объединить таблицы вместе.

Объяснение вывода слишком длинное для этого ответа, но в целом каждая строка вывода покажет вам, к скольким строкам присоединится запрос. Чем меньше, тем лучше. Оно будет также расскажу вам, как он собирается ПРИСОЕДИНИТЬСЯ. "Использование временного" или "Использование сортировки файлов" - это то, чего вы хотите избежать, если это возможно (хотя, если вы отсортируете, это появится, так что будьте готовы). Там также есть столбец для того, с помощью какого ключа будут соединены строки. Если этот столбец пуст, вам следует попытаться создать индекс, чтобы он работал лучше.

Надеюсь, это поможет! Удачи!

 1
Author: Cfreak, 2010-08-09 05:17:43

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

 0
Author: oezi, 2010-08-09 04:43:01

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

 0
Author: staticsan, 2010-08-09 04:54:47

Вы должны использовать предложение in с соединением, а не использовать Ограничение. Ограничение выполняется после соединения, а не является частью запроса.

 0
Author: Benjamin Anderson, 2010-08-09 04:56:59

Возможно, я схожу с ума, но я не вижу индекса в таблице action_6_members для поля, которое вы фильтруете по draw_id в вашем исходном запросе.

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

Добавление индекса в столбец draw_id, вероятно, поможет здесь.

Вы могли бы создать комбинированный ключ (draw_id,id), но это, вероятно, не принесло бы вам большой пользы, если бы вы не извлекали какие-либо данные из таблицы action_6_members (если это не так, то вместо чтения таблицы данных можно использовать многопольный индекс)

Надеюсь, это поможет...

 0
Author: Dave Rix, 2010-08-09 10:24:43