急切询问 这2条语句如何优化

liyihongcug

急切询问 这2条语句如何优化

SELECT bon.id AS id,
       bon.partnerId AS partnerId,
       bon.bonusType AS bonusType,
       bon.countryId AS countryId,
       bon.affilateId AS affilateId,
       bon.startDate AS startDate,
       bon.endDate AS endDate,
       bon.userId AS userId,
       bon.createDate AS createDate,
       bon.isEnable AS isEnable,
       p.partnername AS partnerName,
       u.userName AS userName,
       ifnull (count (bte.userId), 0) AS bonusUserCount,
       ifnull (sum (bte.bonusEUR), 0) AS bonusTotalAmount
  FROM          Bonus bon
             INNER JOIN
                Partner p
             ON bon.partnerId = p.id
          INNER JOIN
             UserEntity u
          ON bon.userId = u.id
       LEFT OUTER JOIN
          BonusTransactionEntity bte
       ON bon.id = bte.bonusId
WHERE     1 = 1
       AND bon.partnerId = 1
       AND((bte.status NOT IN ('5', '8')) OR bte.status IS NULL)
GROUP BY bte.bonusId;
（插入话，  这里左连接 where1=1 是什么意思啦）


特别是下面这条以后将用得很多的
SELECT bon1.id AS id,
       bon1.partnerId AS partnerId,
       bon1.bonusType AS bonusType,
       bon1.countryId AS conuntryId,
       bon1.affilateId AS affilateId,
       bon1.startDate AS startDate,
       bon1.endDate AS endDate,
       bon1.userId AS userId,
       bon1.createDate AS createDate,
       bon1.isEnable AS isEnable,
       p.partnername AS partnername,
       u2.userName AS userName
  FROM Bonus bon1, Partner p, UserEntity u1, UserEntity u2
WHERE     u1.id = ?
       AND u1.partnerId = bon1.partnerId
       AND bon1.isEnable = '1'
       AND u2.id = bon1.userId
       AND bon1.partnerId = p.id
       AND(bon1.id NOT IN
                 (SELECT bonTrans.bonusId
                    FROM BonusTransactionEntity bonTrans
                   WHERE bonTrans.userId = ?
                         AND bonTrans.status NOT IN ('5', '8')));

kider

explain慢慢调