分组查询出现sending data,三个查询语句cpu跑到90%,希望大家帮我看看。mysql> explain SELECT g.id,g.city,g.end_time,
-> g.small_img,g.et_price,g.shop_price,
-> g.site_id,g.is_best,g.bought,g.name,g.sort,
-> s.name as site_name,g.bussiess_id,(g.shop_price / g.et_price) as disc
ount,
-> g.click_count,g.url,s.code_position,
-> s.cps_url,(SELECT count(*) from xjs_goods
-> where site_id=g.site_id and id>g.id
-> and site_id>0 and end_time >= 1335899153 )
-> as group_id1 FROM xjs_goods as g
-> left join xjs_site as s on s.id = g.site_id
-> where g.status = 1 and g.end_time >= 1335899153 and g.site_id>0
-> and (g.city = '' or g.city = '全国' or g.city like '%北京%') group by g.
id
-> order by group_id1 asc,
-> s.sort, g.is_best desc,g.add_time desc,g.sort asc,g.id desc limit 0,60
-> ;
+----+--------------------+-----------------+--------+--------------------------
---+---------+---------+------------------+------+------------------------------
----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| 1 | PRIMARY | g | index | end_time,city,site_id | PRIMARY | 4 | NULL | 792 | Using where; Using temporary;
Using filesort |
| 1 | PRIMARY | s | eq_ref | PRIMARY,id| PRIMARY | 4 | xjsdata.g.site_id | 1 |
|
| 2 | DEPENDENT SUBQUERY | xjs_goods | ref | PRIMARY,end_time,id,site_id | site_id | 4 | func| 209 | Using where
|
+----+--------------------+-----------------+--------+--------------------------
---+---------+---------+------------------+------+------------------------------
----------------+
3 rows in set, 2 warnings (0.00 sec)对数据库不是很清楚,求教各位。多谢多谢。
-> g.small_img,g.et_price,g.shop_price,
-> g.site_id,g.is_best,g.bought,g.name,g.sort,
-> s.name as site_name,g.bussiess_id,(g.shop_price / g.et_price) as disc
ount,
-> g.click_count,g.url,s.code_position,
-> s.cps_url,(SELECT count(*) from xjs_goods
-> where site_id=g.site_id and id>g.id
-> and site_id>0 and end_time >= 1335899153 )
-> as group_id1 FROM xjs_goods as g
-> left join xjs_site as s on s.id = g.site_id
-> where g.status = 1 and g.end_time >= 1335899153 and g.site_id>0
-> and (g.city = '' or g.city = '全国' or g.city like '%北京%') group by g.
id
-> order by group_id1 asc,
-> s.sort, g.is_best desc,g.add_time desc,g.sort asc,g.id desc limit 0,60
-> ;
+----+--------------------+-----------------+--------+--------------------------
---+---------+---------+------------------+------+------------------------------
----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| 1 | PRIMARY | g | index | end_time,city,site_id | PRIMARY | 4 | NULL | 792 | Using where; Using temporary;
Using filesort |
| 1 | PRIMARY | s | eq_ref | PRIMARY,id| PRIMARY | 4 | xjsdata.g.site_id | 1 |
|
| 2 | DEPENDENT SUBQUERY | xjs_goods | ref | PRIMARY,end_time,id,site_id | site_id | 4 | func| 209 | Using where
|
+----+--------------------+-----------------+--------+--------------------------
---+---------+---------+------------------+------+------------------------------
----------------+
3 rows in set, 2 warnings (0.00 sec)对数据库不是很清楚,求教各位。多谢多谢。
我只用一个 order by group_id1 asc 照旧 这和order by 没太大关系吧?
由于没有测试环境,不太确定,你去掉这句看看是否解决。如果是这个问题,你将g.city like '%北京%'看是否能改为g.city like '北京%',并且建立索引。
-> where site_id=g.site_id and id>g.id
-> and site_id>0 and end_time >= 1335899153 )
-> as group_id1 这一部分分组,就能很快得出查询结果。
我看这个时间1335899153 是五月2号 end_time>5.2的记录必然很少
所以强制使用end_time索引 ->,(SELECT count(*) from xjs_goods force index(end_time)
-> where site_id=g.site_id and id>g.id
-> and site_id>0 and end_time >= 1335899153 )
-> as group_id1
不知道是怎么搞的,我试这个语句mysql卡死了好几次 一直没查到内容
这是我查的一个东东 网上找的方法 但是不是很懂 求指教
mysql> show profile for query 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000051 |
| checking query cache for query | 0.000140 |
| Opening tables | 0.000012 |
| System lock | 0.000005 |
| Table lock | 0.000041 |
| init | 0.000071 |
| optimizing | 0.000013 |
| statistics | 0.000026 |
| storing result in query cache | 0.000103 |
| preparing | 0.000015 |
| Creating tmp table | 0.000126 |
| executing | 0.000001 |
| Copying to tmp table | 0.000022 |
| Sorting result | 0.000008 |
| Sending data | 0.000015 |
| end | 0.000002 |
| removing tmp table | 0.000004 |
| end | 0.000002 |
| query end | 0.000002 |
| freeing items | 0.000031 |
| logging slow query | 0.000001 |
| cleaning up | 0.000003 |
+--------------------------------+----------+
22 rows in set (0.00 sec)
仔细看了下你的语句,你直接运行
SELECT g.id,count(*) from xjs_goods s
join xjs_goods as g
on s.site_id=g.site_id and s.id>g.id
where s.site_id>0 and s.end_time >= 1335899153
and g.site_id>0 and g.end_time >= 1335899153 and g.status=1
group by g.id
试试。
| 1 | PRIMARY | g | index | end_time,city,site_id | PRIMARY | 4 | NULL | 792 | Using where; Using temporary;
Using filesort |这里。