Mysql查詢調(diào)優(yōu)

現(xiàn)有如下SQL,查詢極慢 求大神調(diào)優(yōu)

SELECT DISTINCT member.loginName,member.memberLevelId,member.regTime,member.multipleChannelsId,
(SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id) AS '下單總數(shù)量',
(SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) AS '付款訂單數(shù)量',
(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;

業(yè)務實際SQL是這樣的...

SELECT DISTINCT member.loginName,member.memberLevelId,member.regTime,member.multipleChannelsId,
(SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id) AS '下單總數(shù)量',
(SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) AS '付款訂單數(shù)量',
(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 < "注冊結(jié)束時間"
AND o.OrderCreateTime > "下單開始時間"
AND o.OrderCreateTime > "下單結(jié)束時間"
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) > "最小下單次數(shù)"
AND (SELECT COUNT(1) FROM cs_order WHERE MemberID = member.id AND OrderStatus >= 3 AND OrderStatus <= 5) < "最大下單次數(shù)"
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) < "下單總金額"