使用groupby和orderby的MySQL查询中的性能问题

Question

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)

Answer 1

尝试将查询改写为：

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替换,并且由于join...ON a.id=b.rid谓词，因此与oryginal查询100%等效，但使MySql略有改进，并且如@Dennis Leon所建议的，a.status='1'和b.download='1'与字符串相比较，而不是与数字相比较。还尝试将Order by sum(B.download)desc替换为Order by count(*)desc-由于查询只检索b.download='1'的行，最后创建两个索引：

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

然后尝试以上更改后的查询速度。