Кто-нибудь хочет помочь оптимизировать запрос MySQL?

Вот запрос:

SELECT COUNT(*) AS c, MAX(`followers_count`) AS max_fc, 
       MIN(`followers_count`) AS min_fc, MAX(`following_count`) AS max_fgc,
       MIN(`following_count`) AS min_fgc, SUM(`followers_count`) AS fc,
       SUM(`following_count`) AS fgc, MAX(`updates_count`) AS max_uc,
       MIN(`updates_count`) AS min_uc, SUM(`updates_count`) AS uc
FROM `profiles`
WHERE `twitter_id` IN (SELECT `followed_by` 
                       FROM `relations` 
                       WHERE `twitter_id` = 123);

Две таблицы profiles и relations. Оба имеют более 1 000 000 строк, движок InnoDB. Оба имеют индексы на twitter_id, relations имеют дополнительный индекс на (twitter_id, followed_by). Выполнение запроса занимает более 6 секунд, это меня очень расстраивает. Я знаю, что я могу как-то ПРИСОЕДИНИТЬСЯ к этому, но мои знания MySQL не так круты, поэтому я прошу вашей помощи.

Заранее спасибо всем =)

Привет, К~

Обновлено

Хорошо, мне удалось сократить время до 2,5 секунд. Я использовал INNER JOIN и добавил три пары индексов. Вот результаты EXPLAIN:

id, select_type, table, type, possible_keys, 
    key, key_len, ref, rows, Extra

1, 'SIMPLE', 'r', 'ref', 'relation', 
    'relation', '4', 'const', 252310, 'Using index'

1, 'SIMPLE', 'p', 'ref', 'PRIMARY,twiter_id,id_fc,id_fgc,id_uc', 
    'id_uc', '4', 'follerme.r.followed_by', 1, ''

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

Еще одно обновление

Вот операторы SHOW CREATE TABLE для обеих таблиц:

CREATE TABLE `profiles` (
  `twitter_id` int(10) unsigned NOT NULL,
  `screen_name` varchar(45) NOT NULL default '',
  `followers_count` int(10) unsigned default NULL,
  `following_count` int(10) unsigned default NULL,
  `updates_count` int(10) unsigned default NULL,
  `location` varchar(45) default NULL,
  `bio` varchar(160) default NULL,
  `url` varchar(255) default NULL,
  `image` varchar(255) default NULL,
  `registered` int(10) unsigned default NULL,
  `timestamp` int(10) unsigned default NULL,
  `relations_timestamp` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`twitter_id`,`screen_name`),
  KEY `twiter_id` (`twitter_id`),
  KEY `screen_name` USING BTREE (`screen_name`,`twitter_id`),
  KEY `id_fc` (`twitter_id`,`followers_count`),
  KEY `id_fgc` (`twitter_id`,`following_count`),
  KEY `id_uc` (`twitter_id`,`updates_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `relations` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `twitter_id` int(10) unsigned NOT NULL default '0',
  `followed_by` int(10) unsigned default NULL,
  `timestamp` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`id`,`twitter_id`),
  UNIQUE KEY `relation` (`twitter_id`,`followed_by`)
) ENGINE=InnoDB AUTO_INCREMENT=1209557 DEFAULT CHARSET=utf8

Вау, какой беспорядок =) Извините!


mysql optimization query-optimization
person kovshenin    schedule 09.07.2009    source источник

Ответы (5)


arrow_upward
1
arrow_downward

Создайте следующие составные индексы:

profiles (twitter_id, followers_count)
profiles (twitter_id, following_count)
profiles (twitter_id, updates_count)

и опубликуйте план запроса, ради бога.

Кстати, сколько строк возвращает этот COUNT(*)?

Обновление:

Строки вашей таблицы довольно длинные. Создайте составной индекс для всех выбранных полей:

profiles (twitter_id, followers_count, following_count, updates_count)

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

person Quassnoi    schedule 09.07.2009
comment
Спасибо! Создал пары индексов, id_fc, id_fgc, id_uc. Кажется, сэкономил мне ~ 300 мс. План запроса? Вы имеете в виду результаты EXPLAIN? Посмотрите на обновление вопроса. - person kovshenin; 09.07.2009
comment
COUNT(*) возвращает 195436 строк. Там есть некоторые ошибки, неизвестный столбец p.twitter_id в предложении where (я думаю, для всех). После того, как я добавил p к каждому профилю FROM, он сказал Неизвестный столбец «r.followed_by» в «предложении on». Не уверенны что делать дальше. Большое спасибо за Вашу помощь. - person kovshenin; 09.07.2009
comment
@kovshenin: что касается ошибки, то 3 секунды вполне приличное время для 200 000 строк. Не могли бы вы опубликовать полные заявления CREATE TABLE для вас profiles и relations? - person Quassnoi; 09.07.2009
comment
Привет, исправил предыдущие ошибки. Жаль сам не разобрался. В любом случае, теперь я застрял с этим: смешивание столбцов GROUP (MIN(), MAX(), COUNT(),...) без столбцов GROUP является незаконным, если нет предложения GROUP BY. Должен ли я группировать что-нибудь? - person kovshenin; 09.07.2009
comment
Обновил мой пост с утверждениями CREATE TABLE. 3 секунды прилично, но недостаточно, и я знаю, что есть способ опуститься ниже, и это только вопрос времени, когда я узнаю, ха-ха =) Хотя мне может потребоваться два или три месяца, чтобы прочитать эту книгу о высокой производительности MySQL Я купил несколько недель назад. знак равно - person kovshenin; 09.07.2009
comment
@kovshenin: эммм... извини, я не правильно понял твою логику. Забудьте о последнем запросе :) Просто опубликуйте свои определения таблиц, если хотите. - person Quassnoi; 09.07.2009
comment
Эй, спасибо за всю твою помощь, приятель, у меня осталось ~2 секунды =) - person kovshenin; 10.07.2009

arrow_upward
3
arrow_downward

Соединение будет выглядеть примерно так:

SELECT COUNT(*) AS c,
MAX(p.`followers_count`) AS max_fc,
MIN(p.`followers_count`) AS min_fc,
MAX(p.`following_count`) AS max_fgc,
MIN(p.`following_count`) AS min_fgc,
SUM(p.`followers_count`) AS fc,
SUM(p.`following_count`) AS fgc,
MAX(p.`updates_count`) AS max_uc,
MIN(p.`updates_count`) AS min_uc,
SUM(p.`updates_count`) AS uc
FROM `profiles` AS p
INNER JOIN `relations` AS r ON p.`twitter_id` = r.`followed_by`
WHERE r.`twitter_id` = 123;

Чтобы оптимизировать его, вы должны запустить EXPLAIN SELECT ... для обоих запросов.

person Greg    schedule 09.07.2009
comment
Присоединение замедлит этот путь. - person corymathews; 09.07.2009
comment
Это уменьшило время выполнения с 6 до 3 с. - person kovshenin; 09.07.2009

arrow_upward
1
arrow_downward 

SELECT COUNT(*) AS c,
  MAX(`followers_count`) AS max_fc, MIN(`followers_count`) AS min_fc,
  MAX(`following_count`) AS max_fgc, MIN(`following_count`) AS min_fgc,
  SUM(`followers_count`) AS fc, SUM(`following_count`) AS fgc,
  MAX(`updates_count`) AS max_uc, MIN(`updates_count`) AS min_uc, SUM(`updates_count`) AS uc
FROM `profiles`
JOIN `relations`
  ON (profiles.twitter_id = relations.followed_by)
WHERE relations.twitted_id = 123;

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

person Alex Martelli    schedule 09.07.2009
comment
Я пошел с вышеуказанным методом, но я думаю, что в этих двух нет никакой разницы. - person kovshenin; 09.07.2009
comment
Да, различия в том, как/если вы разбиваете строки, или псевдонимы таблиц и квалификации полей, не имеют значения (за исключением того, что вам нужно будет использовать псевдонимы и/или квалификации, если это необходимо для устранения неоднозначности, но я не вижу следов этого здесь ). - person Alex Martelli; 09.07.2009

arrow_upward
1
arrow_downward

count(*) — очень затратная операция в InnoDB Engine. Пробовали ли вы выполнять этот запрос без этой части? Если это вызывает наибольшее время обработки, возможно, вы могли бы сохранить текущее значение вместо того, чтобы запрашивать его каждый раз.

person Gandalf    schedule 09.07.2009
comment
Это действительно применимо только тогда, когда нет ГДЕ - person Greg; 09.07.2009
comment
Хм, не слышал этого раньше, но думаю, это имеет смысл. - person Gandalf; 09.07.2009
comment
Удаление COUNT(*) ничего не изменило :( - person kovshenin; 09.07.2009

arrow_upward
1
arrow_downward

Я бы подошел к этой проблеме с точки зрения программиста; У меня была бы отдельная таблица (или где-то область хранения), в которой хранились бы значения max, min и sum, связанные с каждым полем в исходном запросе, и обновлялись бы эти значения каждый раз, когда я обновлял и добавлял запись таблицы. (хотя удаление может быть проблематичным, если оно не обрабатывается правильно).

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

person Phill    schedule 09.07.2009
comment
на самом деле так же, как идея гандольфа выше! - person Phill; 09.07.2009
comment
Вы имеете в виду, что TRIGGERS каждый раз обновляет эти значения? Хм, может быть, это хорошая идея. - person kovshenin; 09.07.2009
comment
Да, я давно не использовал mySQL, не был уверен, есть у них триггеры или нет, но да, это общая идея :) - person Phill; 09.07.2009