• 全部
  • 问答

版主们,求show processlist疑问

剑心永远OK 2011-10-15 02:35:14
利用mytop监测mysql运行状态,发现有几句经常会出现在里面,时间不长,1秒的时候就会消失了。

我随后拿着这几条SQL来单测试,速度都在0点几秒时间内。

之后我发现show processlist,状态是处于preparing状态,然后消失。。

网上搜索了很多都很少关于preparing状态的介绍,只是在MYSQL手册里面简单描述了,在运行中进行优化。

这几条SQL一直导致CPU占用100%以上。。找不到解决思路,

问preparing是怎么回事,各大虾有什么思路不?
...全文
82 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
剑心永远OK 2011-10-17
这是一条经常出现在show processlist里面的SQL

SELECT COUNT(*) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_special ps ON (p.product_id = ps.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '107' OR p2c.category_id = '77' OR p2c.category_id = '75' OR p2c.category_id = '74' OR p2c.category_id = '71' OR p2c.category_id = '70' OR p2c.category_id = '69' OR p2c.category_id = '7');


explain

+----+--------------------+-------+----------------+--------------------------------+------------+---------+----------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+----------------+--------------------------------+------------+---------+----------------------------------+------+--------------------------+
| 1 | PRIMARY | p2s | index | PRIMARY,store_id | PRIMARY | 8 | NULL | 7360 | Using where; Using index |
| 1 | PRIMARY | p | eq_ref | PRIMARY,date_available | PRIMARY | 4 | fashione_db.p2s.product_id | 1 | Using where |
| 1 | PRIMARY | ps | ref | product_id | product_id | 4 | fashione_db.p.product_id | 1 | Using index |
| 1 | PRIMARY | pd | eq_ref | PRIMARY | PRIMARY | 8 | fashione_db.p2s.product_id,const | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p2c | index_subquery | PRIMARY,category_id,product_id | product_id | 4 | func | 1 | Using where |
+----+--------------------+-------+----------------+--------------------------------+------------+---------+----------------------------------+------+--------------------------+
5 rows in set (0.00 sec)


检索有7000多条。。感觉不太好,但应该不会导致CPU那么高啊。求解释。
回复
剑心永远OK 2011-10-16
[Quote=引用 2 楼 zuoxingyu 的回复:]

引用楼主 dada20042030 的回复:

这几条SQL一直导致CPU占用100%以上。。找不到解决思路,



单独分析这几条语句。
[/Quote]
分析了,速度都在0点几秒以内。
不过有样东西就是explain出来的东西,我觉得有点奇怪


explain select count(*) total from product p left join product_description pd on (p.product_id = pd.product_id) where pd.language_id = 1 and p.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '218' or p2c.category_id = '220');



+----+--------------------+-------+----------------+--------------------------------+------------+---------+--------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+----------------+--------------------------------+------------+---------+--------------------------------+------+--------------------------+
| 1 | PRIMARY | p | index | PRIMARY | PRIMARY | 4 | NULL | 9771 | Using where; Using index |
| 1 | PRIMARY | pd | eq_ref | PRIMARY | PRIMARY | 8 | fashione_db.p.product_id,const | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p2c | index_subquery | PRIMARY,category_id,product_id | product_id | 4 | func | 1 | Using where |
+----+--------------------+-------+----------------+--------------------------------+------------+---------+--------------------------------+------+--------------------------+



explain 出来的结果里面有个表product的type类型是index,这叫正常吗?而且检索的行也很多。。我感觉不正常。
回复
zuoxingyu 2011-10-16
[Quote=引用楼主 dada20042030 的回复:]

这几条SQL一直导致CPU占用100%以上。。找不到解决思路,

[/Quote]

单独分析这几条语句。
回复
rucypli 2011-10-16
先找找这几个paeparing得是否正常语句
回复
相关推荐
发帖
MySQL
创建于2007-09-28

5.5w+

社区成员

MySQL相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2011-10-15 02:35
社区公告
暂无公告