Question

php mysql优化 这个执行效率太低了 怎么优化一下这个sql呀

$sql = "SELECT count(g.goods_id) as gnum ,sum(g.click_count) as cg,ga.attr_value,u.user_id, u.user_name, sum(
og.goods_number * og.goods_price
) AS sumprice,
sum(
og.goods_number * og.goods_price - (og.sell_fee + og.tosell_fee + og.photo_fee)
) AS rprice,
sum(
og.sell_fee + og.tosell_fee + og.photo_fee
) AS profit,
max(from_unixtime( o.pay_time +28800)) AS paytime,
su.su_name,g.supplier_id,su.site_url,count(*) as sale_num
FROM ecs_order_goods AS og
LEFT JOIN ecs_goods AS g ON og.goods_id = g.goods_id
LEFT JOIN ecs_order_info AS o ON og.order_id = o.order_id
LEFT JOIN ecs_suppliers AS su ON g.supplier_id = su.su_id
LEFT JOIN ecs_users AS u ON su.user_id = u.user_id
LEFT JOIN ecs_pay_records AS pr ON pr.goods_id = og.goods_id

LEFT JOIN ecs_goods_attr AS ga ON og.goods_id=ga.goods_id
LEFT JOIN ecs_attribute AS a ON a.attr_id = ga.attr_id
WHERE 1
AND g.cat_id IN ('225','228','242','349','352','429','646','647','649','668','678','677','676','675','674','673','684','683','682','672','671','670','669','681','680','679','648','664','667','666','665','663','662','661','655','660','659','658','657','654','652','651','650','656','653','192','304','316','317','318','319','320','305','626','627','628','307','336','631','337','338','306','302','301','623','624','625')
AND o.order_status = '1'
AND o.pay_status ='2'
AND pr.action=1
AND a.attr_name='作者姓名'
AND ga.attr_value='".$name."'

GROUP BY ga.attr_value ORDER BY sumprice DESC LIMIT 1";

Answer 1

你的左连接生成中间表时数据太多了，过滤条件在ON中去过滤。
改成下边的语句试试。还不行的话，考虑对表加索引。

SELECT count(g.goods_id) as gnum ,sum(g.click_count) as cg,ga.attr_value,u.user_id, u.user_name, sum(
og.goods_number * og.goods_price
) AS sumprice,
sum(
og.goods_number * og.goods_price - (og.sell_fee + og.tosell_fee + og.photo_fee)
) AS rprice,
sum(
og.sell_fee + og.tosell_fee + og.photo_fee
) AS profit,
max(from_unixtime( o.pay_time +28800)) AS paytime,
su.su_name,g.supplier_id,su.site_url,count(*) as sale_num
FROM ecs_order_goods AS og
LEFT JOIN ecs_goods AS g ON og.goods_id = g.goods_id AND g.cat_id IN ('225','228','242','349','352','429','646','647','649','668','678','677','676','675','674','673','684','683','682','672','671','670','669','681','680','679','648','664','667','666','665','663','662','661','655','660','659','658','657','654','652','651','650','656','653','192','304','316','317','318','319','320','305','626','627','628','307','336','631','337','338','306','302','301','623','624','625')
LEFT JOIN ecs_order_info AS o ON og.order_id = o.order_id AND o.order_status = '1' AND o.pay_status ='2'
LEFT JOIN ecs_suppliers AS su ON g.supplier_id = su.su_id
LEFT JOIN ecs_users AS u ON su.user_id = u.user_id
LEFT JOIN ecs_pay_records AS pr ON pr.goods_id = og.goods_id AND pr.action=1

LEFT JOIN ecs_goods_attr AS ga ON og.goods_id=ga.goods_id AND a.attr_name='作者姓名' AND ga.attr_value='".$name."'
LEFT JOIN ecs_attribute AS a ON a.attr_id = ga.attr_id
GROUP BY ga.attr_value ORDER BY sumprice DESC LIMIT 1

Answer 2

你的应用有问题。有这种功能需求吗？

追问

没有的话 我问干嘛呀 高手！！！ 能解决一下吗？

追答

首先，检查有没有低级错误，索引正确建立了吗。
如果存储过程及各种sql语句优化都解决不了的话，那就要从数据库的结构入手了。把数据库的结构适当地针对性能做一下设计。比如可以按照某个条件分为多个表。也可以添加个sum字段，在insert记录的时候对sum值做一下累计，这样可以把查询时的一次性统计分散为每次insert的累计。对于数据量超大的数据库，还可以依据常用和不常用对数据分开存储，以减少常用数据的量。

追问

怎么分批查询呀 这个

追答

我觉得你的应用，还是有结构问题。不应该这么个情况。

Answer 3

从sql逻辑上优化的空间不大，max(from_unixtime( o.pay_time +28800)) 改为
max(from_unixtime( o.pay_time )) +28800。
另外，检查一下，你关联的那些表，有没有索引。

追问

索引我都 已经加上了 你的sql不好用 谢谢 你再 看看

Answer 4

mysql建议，多查询几次，一个一个查询就不会有这么长了，。另外一种就是写个存储过程。

追问

我想要一个明确的答案 我这里优化不了 怎么做