EC SQL占用大量CPU资源，I/O RAM都不占

2016-07-07 15:18 来源：www.chinab4c.com 作者：ecshop专家



EC SQL占用大量CPU资源，I/O RAM都不占，过滤慢查询语句，发现语句都是一样的，如下，有谁能告诉我怎么优化莫，索引都是官网SQL默认的几个BTREE索引.dbuser是数据库用户，ecshopdb是数据库名。
# User@Host: dbuser[dbuser] @[10.0.0.100] # Query_time: 13Lock_time: 2Rows_sent: 7Rows_examined: 170874
SELECT g.goods_id, g.goods_name, g.shop_price, g.goods_thumb, SUM(og.goods_number) as goods_number FROM `ecshopdb`.`d_goods` AS g, `ecshopdb`.`d_order_info` AS o, `ecshopdb`.`d_order_goods` AS og WHERE g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0AND o.order_sn >= '20100809'AND g.goods_number > 0AND og.order_id = o.order_id AND og.goods_id = g.goods_id AND o.order_status = '1' AND (o.pay_status = '2' OR o.pay_status = '1') AND (o.shipping_status = '1' OR o.shipping_status = '2') GROUP BY g.goods_id ORDER BY goods_number DESC, g.goods_id DESC LIMIT 7;

回答：
进程出现大量copy to tmp table 导致很多Locked，有没有人可以帮看看，如果建立优化索引，谢谢