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 '最后下單時(shí)間'
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è)務(wù)實(shí)際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 '最后下單時(shí)間'
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 = "會(huì)員等級(jí)"
AND member.cityId = "所屬城市"
AND member.multipleChannelsId = "渠道"
AND member.regLanguageType = "語(yǔ)言類型"
AND member.regTime > "注冊(cè)開(kāi)始時(shí)間"
AND member.regTime < "注冊(cè)結(jié)束時(shí)間"
AND o.OrderCreateTime > "下單開(kāi)始時(shí)間"
AND o.OrderCreateTime > "下單結(jié)束時(shí)間"
AND p.CategoryId IN ("標(biāo)準(zhǔn)分類Id")
AND p.code IN ("購(gòu)買過(guò)其一的商品")
AND p.code = "都購(gòu)買過(guò)的商品"
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) < "下單總金額"