Mysql查询调优

Question

现有如下SQL,查询极慢 求大神调优

SELECT DISTINCT member.loginName,member.memberLevelId,member.regTime,member.multipleChannelsId,
(SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id) AS '下单总数量',
(SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) AS '付款订单数量',
(SELECT SUM(GoodsAmount) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) AS '付款订单总金额',
(SELECT MAX(OrderCreateTime) FROM cs_order WHERE MemberID = member.id) AS '最后下单时间'
FROM cs_member member JOIN cs_order o ON member.id = o.MemberId 
JOIN cs_order_promotion_log op ON o.id = op.orderId
JOIN cs_coupon_definition cd ON op.PromotionID = cd.PromotionId
JOIN cs_coupon_batch cb ON cd.id = cb.CouponDefinitionId
JOIN cs_order_item oi ON o.id = oi.OrderId
JOIN cs_product p ON oi.ProductId = p.id
WHERE TRUE
AND (SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) > "10"
AND (SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) < "50"
AND (SELECT COUNT(GoodsAmount) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) > "1000"
AND (SELECT COUNT(GoodsAmount) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) < "10000"
LIMIT 20;

业务实际SQL是这样的...

SELECT DISTINCT member.loginName,member.memberLevelId,member.regTime,member.multipleChannelsId,
(SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id) AS '下单总数量',
(SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) AS '付款订单数量',
(SELECT SUM(GoodsAmount) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) AS '付款订单总金额',
(SELECT MAX(OrderCreateTime) FROM cs_order WHERE MemberID = member.id) AS '最后下单时间'
FROM cs_member member JOIN cs_order o ON member.id = o.MemberId 
JOIN cs_order_promotion_log op ON o.id = op.orderId
JOIN cs_coupon_definition cd ON op.PromotionID = cd.PromotionId
JOIN cs_coupon_batch cb ON cd.id = cb.CouponDefinitionId
JOIN cs_order_item oi ON o.id = oi.OrderId
JOIN cs_product p ON oi.ProductId = p.id
WHERE member.loginName LIKE "%loginName%" 
OR member.mobile LIKE "%mobile%" 
OR member.email LIKE "%email%" 
AND member.memberLevelId = "会员等级"
AND member.cityId = "所属城市"
AND member.multipleChannelsId = "渠道"
AND member.regLanguageType = "语言类型"
AND member.regTime > "注册开始时间"
AND member.regTime < "注册结束时间"
AND o.OrderCreateTime > "下单开始时间"
AND o.OrderCreateTime > "下单结束时间"
AND p.CategoryId IN ("标准分类Id")
AND p.code IN ("购买过其一的商品")
AND p.code = "都购买过的商品"
AND cb.ShortNum = "短码"
AND (SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) > "最小下单次数"
AND (SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) < "最大下单次数"
AND (SELECT COUNT(GoodsAmount) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) > "下单总金额"
AND (SELECT COUNT(GoodsAmount) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) < "下单总金额"

Answer 1

光看到几个子查询我就知道慢是正常的

Answer 2

1. 你最后几个and查询，“最小下单次数、最大下单次数、下单总金额”，如果你的需求真的是这样的话，那么可以考虑在cs_member里加几个字段进行存储，数据变化的时候，再更新这几个字段，这样，查询速度会快不少
2. 查一下表关联字段的索引，是否建立了，如果没有的话，建立相关的索引
3. 查询字段“下单总数量、付款订单数量、付款订单总金额、最后下单时间”，不要在这里查询，等待需要的指定条数的数据查出后，再遍历查询，这样也会快很多。