速度果然慢爆了~求优化

程序小海 2014-06-24 02:23:35

SELECT a.*,t1.photo_num,t3.fensi_num FROM account a 

					 LEFT JOIN 

					 ( SELECT COUNT(p.user_id) photo_num, p.user_id    FROM account a 

						INNER JOIN photo_table p ON p.user_id=a.user_id  

						WHERE 1   GROUP BY a.user_id ) AS t1

					ON a.`user_id`=t1.user_id 

					  LEFT JOIN 

					( SELECT COUNT(fs.gz_id) AS fensi_num,a.user_id    FROM account a 

						INNER JOIN user_attention fs ON fs.user_id=a.user_id

						WHERE 1   GROUP BY a.user_id

						) AS t3

					 ON a.`user_id`=t3.user_id 	

					 WHERE 1  AND user_type=1  GROUP BY a.user_id  ORDER BY photo_num ASC   LIMIT 0,15

...全文

248 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

botangtest 2014-06-26

打赏
举报

select a.user_id ,count(b.user_id) photo_num ,count(c.user_id) fensi_num from account a left join photo_table b on a.user_id =b.user_id left join user_attention c on a.user_id =c.user_id group by a.user_id

程序小海 2014-06-24

打赏
举报

引用 8 楼 wildwave 的回复:

MySQL啊。。。给你转到mysql版吧

数据库差不多的吧？

<table border=1>
<tr>
<td bgcolor=silver class='medium'>id</td><td bgcolor=silver class='medium'>select_type</td><td bgcolor=silver class='medium'>table</td><td bgcolor=silver class='medium'>type</td><td bgcolor=silver class='medium'>possible_keys</td><td bgcolor=silver class='medium'>key</td><td bgcolor=silver class='medium'>key_len</td><td bgcolor=silver class='medium'>ref</td><td bgcolor=silver class='medium'>rows</td><td bgcolor=silver class='medium'>Extra</td></tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>a</td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>12351</td>
<td class='normal' valign='top'>Using where; Using temporary; Using filesort</td>
</tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'><derived2></td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>11830</td>
<td class='normal' valign='top'> </td>
</tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'><derived3></td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>1522</td>
<td class='normal' valign='top'> </td>
</tr>
<tr>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'><derived4></td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>1294</td>
<td class='normal' valign='top'> </td>
</tr>
<tr>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>fs</td>
<td class='normal' valign='top'>ALL</td>
<td class='normal' valign='top'>user_id</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>3360</td>
<td class='normal' valign='top'>Using temporary; Using filesort</td>
</tr>
<tr>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>a</td>
<td class='normal' valign='top'>eq_ref</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>meet_com.fs.user_id</td>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>Using index</td>
</tr>
<tr>
<td class='normal' valign='top'>3</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>lt</td>
<td class='normal' valign='top'>index</td>
<td class='normal' valign='top'>receive_id</td>
<td class='normal' valign='top'>receive_id</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>9522</td>
<td class='normal' valign='top'>Using index; Using temporary; Using filesort</td>
</tr>
<tr>
<td class='normal' valign='top'>3</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>a</td>
<td class='normal' valign='top'>eq_ref</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>meet_com.lt.receive_id</td>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>Using index</td>
</tr>
<tr>
<td class='normal' valign='top'>2</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>a</td>
<td class='normal' valign='top'>index</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>PRIMARY</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>(NULL)</td>
<td class='normal' valign='top'>12351</td>
<td class='normal' valign='top'>Using index</td>
</tr>
<tr>
<td class='normal' valign='top'>2</td>
<td class='normal' valign='top'>DERIVED</td>
<td class='normal' valign='top'>p</td>
<td class='normal' valign='top'>ref</td>
<td class='normal' valign='top'>user_id</td>
<td class='normal' valign='top'>user_id</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>meet_com.a.user_id</td>
<td class='normal' valign='top'>1</td>
<td class='normal' valign='top'>Using index</td>
</tr>
</table>

小灰狼W 2014-06-24

打赏
举报

MySQL啊。。。给你转到mysql版吧

程序小海 2014-06-24

打赏
举报

引用 3 楼 wildwave 的回复:

不是这个意思。是指涉及到的表的数据量，连接字段的选择性，这样的还有一个很重要的是语句的执行计划

id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL (NULL) (NULL) (NULL) (NULL) 12351 Using where; Using temporary; Using filesort 1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 11830 1 PRIMARY <derived3> ALL (NULL) (NULL) (NULL) (NULL) 1522 1 PRIMARY <derived4> ALL (NULL) (NULL) (NULL) (NULL) 1294 4 DERIVED fs ALL user_id (NULL) (NULL) (NULL) 3360 Using temporary; Using filesort 4 DERIVED a eq_ref PRIMARY PRIMARY 4 meet_com.fs.user_id 1 Using index 3 DERIVED lt index receive_id receive_id 4 (NULL) 9522 Using index; Using temporary; Using filesort 3 DERIVED a eq_ref PRIMARY PRIMARY 4 meet_com.lt.receive_id 1 Using index 2 DERIVED a index PRIMARY PRIMARY 4 (NULL) 12351 Using index 2 DERIVED p ref user_id user_id 4 meet_com.a.user_id 1 Using index

程序小海 2014-06-24

打赏
举报

引用 4 楼 liao_z_j 的回复:

group by +order by 肯定会慢呀

有好方法解决不？

程序小海 2014-06-24

打赏
举报

引用 3 楼 wildwave 的回复:

不是这个意思。是指涉及到的表的数据量，连接字段的选择性，这样的还有一个很重要的是语句的执行计划

哦哦数据量的话也就1w条数据量，他们都有 user_id 作为主外键， mysql 在哪里能看到执行计划哦?

liao_z_j 2014-06-24

打赏
举报

group by +order by 肯定会慢呀

小灰狼W 2014-06-24

打赏
举报

不是这个意思。是指涉及到的表的数据量，连接字段的选择性，这样的还有一个很重要的是语句的执行计划

程序小海 2014-06-24

打赏
举报

引用 1 楼 wildwave 的回复:

贴出执行计划和相关表及涉及字段的说明

（表一）photo_table：（相册表） photo_id int user_id int images_url varchar （表二）user_attention：（关注表） gz_id int user_id int guanzhu_id （表三） account：（用户表） user_id int user_name varchar 效果是：统计每个用户有多少张照片，和多少个关注人。

小灰狼W 2014-06-24