Проблемы с производительностью в запросе MySQL с использованием groupby и orderby

1) Использован первый запрос ... который занял около 23 секунд

select a.id from mza_movie_upload a,mza_movie_statics b 
where a.status=1 and b.download=1 and a.id=b.rid 
group by b.rid order by sum(b.download) desc

В настоящее время я изменил запрос .., который занимает около 9 секунд

select a.id from mza_movie_upload a 
INNER JOIN mza_movie_statics b 
ON a.id=b.rid WHERE a.status=1 and b.download=1 
group by b.rid order by sum(b.download) desc

explain select a.id from mza_movie_upload a  INNER JOIN mza_movie_statics b  ON     a.id=b.rid WHERE a.status=1 and b.download=1  group by b.rid order by sum(b.download) desc;

+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows    | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL                 | 1603089 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | mmdfurni_dev11.b.rid |       1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------------------------------------+
2 rows in set (0.03 sec)

Я не уверен, что делать? Я хочу, чтобы этот запрос был быстрым ... Я попытался проиндексировать rid и id, что по-прежнему ухудшало запрос.

Вот подробности таблицы

mza_movie_upload

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| userid        | varchar(200) | NO   |     | NULL    |                |
| email         | varchar(200) | NO   |     | NULL    |                |
| up_date       | datetime     | NO   |     | NULL    |                |
| file_size     | varchar(200) | NO   |     | NULL    |                |
| temp_filename | varchar(200) | NO   |     | NULL    |                |
| fileneame     | varchar(200) | NO   | MUL | NULL    |                |
| filepath      | varchar(255) | NO   |     | NULL    |                |
| status        | varchar(20)  | NO   |     | NULL    |                |
| ip            | varchar(200) | NO   |     | NULL    |                |
| category      | varchar(200) | NO   |     | NULL    |                |
| mcode         | bigint(20)   | NO   |     | NULL    |                |
| movie_name    | varchar(200) | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

mza_movie_statics

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| rid       | int(11) | NO   |     | NULL    |                |
| uid       | int(11) | NO   |     | NULL    |                |
| save      | int(11) | NO   |     | NULL    |                |
| download  | int(11) | NO   |     | NULL    |                |
| enterdate | date    | NO   |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql query-optimization query-performance slowdown
person Vijax    schedule 29.09.2013    source источник

Ответы (3)


arrow_upward
0
arrow_downward

Попробуйте переписать запрос на:

SELECT b.rid 
FROM mza_movie_upload a 
INNER JOIN mza_movie_statics b 
ON a.id=b.rid 
WHERE a.status= '1'  and b.download= '1'  
-- group by b.rid order by sum(b.download) desc;
GROUP BY b.rid ORDER BY count(*) DESC;

В этом запросе SELECT a.id заменяется на SELECT b.rid и на 100% эквивалентен исходному запросу из-за предиката JOIN ... ON a.id=b.rid, но приводит к несколько лучшему планированию MySql

И, как предположил @Dennis Leon, a.status= '1' and b.download= '1' сравниваются со строками, а не числа.

Попробуйте также заменить order by sum(b.download) desc на order by count(*) desc - поскольку запрос извлекает только строки с b.download = '1', тогда sum( b.download ) эквивалентно count(*) - это изменение позволяет сэкономить несколько сотен миллисекунд на преобразовании от строк к числам в пределах SUM( .. ).

В итоге создадим два индекса:

create index bbbb on mza_movie_statics( download, rid );
create index aaaaa on mza_movie_upload( status );

затем попробуйте скорость запроса после указанных выше изменений.

person krokodilko    schedule 29.09.2013

arrow_upward
0
arrow_downward

Я бы рекомендовал применить индекс к a.status и / или b.download, если вы хотите увидеть дальнейший прирост производительности. Имейте в виду, что создание дополнительных индексов сопряжено с дополнительными накладными расходами в отношении вставки / обновления / удаления записей - в этом случае, возможно, это необходимо.

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

наконец, я заметил, что в вашем запросе в предложении where указано: a.status = 1, однако столбец статуса - это varchar. Чтобы избежать преобразования между двумя разными типами данных (что замедляет время выполнения запроса) и потенциально нарушает ваш будущий индекс, я бы рекомендовал изменить его на: a.status = '1' (обратите внимание на кавычки)

person Dennis Leon    schedule 29.09.2013
comment
Привет, Деннис, я немного меняю запрос, но по-прежнему требуется около 6 секунд для выполнения select a.id из mza_movie_upload a INNER JOIN mza_movie_statics b ON a.id = b.rid ГДЕ a.status = 1 и b.download = 1 group в порядке b.rid по количеству (a.id); - person Vijax; 29.09.2013

arrow_upward
0
arrow_downward

Ваш запрос можно лучше оптимизировать, если у вас есть индекс ПОКРЫТИЯ. То есть ... в индексе есть столбцы, связанные с тем, что вы ищете, включая критерии. Таким образом, движку не нужно обращаться к необработанным данным, чтобы фактически проверить соответствующий статус и загрузить части.

Итак, на mza_movie_upload есть индекс на (id, status), на mza_movie_statics есть индекс на (rid, download)

Затем группа по будет лучше всего работать с индексом, который управляет запросом, и, поскольку a.id = b.rid, но a.id может быть управляющим индексом, пусть IT будет группой по значению.

select
      mu.id
   from
      mza_movie_upload mu
         JOIN mza_movie_statics ms
            on mu.id = ms.rid
           AND ms.download > 0
   group by
      b.rid
   order by
      sum( b.download ) DESC

А теперь комментарий к загрузке. Он выглядит числовым, поэтому вы, вероятно, не хотите явно сравнивать с «1», поскольку кажется, что этот столбец является счетчиком количества раз, когда что-то было загружено. И вы ищете то, что скачивали чаще всего. Если это всегда значение 1, тогда да, оставьте = 1 вместо> 0.

person DRapp    schedule 30.09.2013