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

Question

急切询问 这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')));

Answer 1

（插入话，  这里左连接 where1=1 是什么意思啦） 这个应该是你的程序生成的，为了怕直接AND，所以在前面默认加了个 1=1 ,之后，你的程序就直接加 swhere = swhere + "and ...."这个对SQL执行效率没有影响。

Answer 2

第1条在SQL语句方面应该是最快的，在连接字段上建立索引
第2条在SQL语句与第1条基本相似
将NOT　IN　修改为LEFT　JOIN，
1＝1：基本没有用，因为结果永远是TRUE

Answer 3

楼上可以接着讨论吗？（mysql的） 
我的有优化如下   
第1条  (status 是int)
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 >0) 
GROUP BY bte.bonusId; 第2条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,
       u1.userName AS userName
  FROM Bonus bon1, Partner p, UserEntity u1WHERE     u1.id = ?
       AND u1.partnerId = bon1.partnerId
       AND bon1.isEnable = '1'
       AND u1.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')));

Answer 4

第2条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, 
      u1.userName AS userName 
  FROM Bonus bon1, Partner p, UserEntity u1WHERE    u1.id = ? 
      AND u1.partnerId = bon1.partnerId 
      AND bon1.isEnable = 1 
      AND u1.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))); 

Answer 5

这个优化，仅从SQL语句上很难做出正确的决策，需要看你的表的数据分布，然后根据情况建立相关的索引。你可以先 explain select ...一下这句，看一下MySQL是如何制定执行计划的。
关于查询优化，下面的贴子中讨论得比较多，你可以先参考一下。
http://topic.csdn.net/u/20090520/16/a96a2e90-a935-4460-837e-e52b4557c519.html?11066
http://topic.csdn.net/u/20090526/17/639d78ec-e299-40d0-9c8e-8d5b21229405.html?49111
http://topic.csdn.net/u/20090604/15/b4120d6f-fd95-4a60-be4b-fbd5accbea73.html?94607

Answer 6

第2条:
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, 
      u1.userName AS userName 
  FROM Bonus bon1 INNER JOIN Partner p ON bon1.partnerId = p.id 
INNER JOIN UserEntity u1 ON  u1.id = bon1.userId 
LEFT JOIN BonusTransactionEntity bonTrans D ON D. bonTrans.bonusId =bon1.id
WHERE    u1.id = ? AND bon1.isEnable = '1' AND bonTrans.userId = ? 
AND bonTrans.status NOT IN ('5', '8') AND D.bonusId IS NULL

Answer 7

特别是下面这条以后将用得很多的 
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, 
  ....... id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
1 SIMPLE bte ALL 1 Using where
1 SIMPLE bon eq_ref PRIMARY PRIMARY 4 oddsmatrixdb.bte.bonusId 1 Using where
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 oddsmatrixdb.bon.userId 1 Using where

Answer 8

建议楼上以以这种格式贴出你的explain 结果，容易对比分析。mysql> explain select * from ta;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | ta    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.19 sec)mysql>

Answer 9

explain  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 >0) 
GROUP BY bte.bonusId; 
+----+-------------+-------+--------+----------------------------+--------------+---------+--------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys              | key          | key_len | ref                      | rows | Extra                           |
+----+-------------+-------+--------+----------------------------+--------------+---------+--------------------------+------+---------------------------------+
|  1 | SIMPLE      | p     | const  | PRIMARY,index_partnerid    | PRIMARY      | 4       | const                    |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | bte   | range  | index_status,Index_bonusid | index_status | 2       | NULL                     |    1 | Using where                     |
|  1 | SIMPLE      | bon   | eq_ref | PRIMARY                    | PRIMARY      | 4       | bte.bonusId              |    1 | Using where                     |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                    | PRIMARY      | 4       | bon.userId               |    1 | Using where                     |
+----+-------------+-------+--------+----------------------------+--------------+---------+--------------------------+------+---------------------------------+
4 rows in set

Answer 10

根据前3楼的 
1 SIMPLE p const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort 
1 SIMPLE bte ALL 1 Using where 
1 SIMPLE bon eq_ref PRIMARY PRIMARY 4 oddsmatrixdb.bte.bonusId 1 Using where 
1 SI、PLE u eq_ref PRIMARY PRIMARY 4 oddsmatrixdb.bon.userId 1 Using where
我加bte 的2个索引（参照字段），结果变为楼上
+----+-------------+-------+--------+----------------------------+--------------+---------+--------------------------+------+---------------------------------+ 
| id | select_type | table | type  | possible_keys              | key          | key_len | ref                      | rows | Extra                          | 
+----+-------------+-------+--------+----------------------------+--------------+---------+--------------------------+------+---------------------------------+ 
|  1 | SIMPLE      | p    | const  | PRIMARY,index_partnerid    | PRIMARY      | 4      | const                    |    1 | Using temporary; Using filesort | 
|  1 | SIMPLE      | bte  | range  | index_status,Index_bonusid | index_status | 2      | NULL                    |    1 | Using where                    | 
现在头疼，我加p的id的索引 ，但是执行计划仍然是那样。
所有能加的索引都加了 ，  根据官方文档 Using temporary; Using filesort  这是肯定要做优化的 ，问题是如何做 

Answer 11

从你目前的执行计划上没有问题啊。没有具体的数据分布，无法进行人为地优化。
+----+-------------+-------+--------+----------------------------+--------------+---------+-------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys              | key          | key_len | ref         | rows | Extra                           |
+----+-------------+-------+--------+----------------------------+--------------+---------+-------------+------+---------------------------------+
|  1 | SIMPLE      | p     | const  | PRIMARY,index_partnerid    | PRIMARY      | 4       | const       |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | bte   | range  | index_status,Index_bonusid | index_status | 2       | NULL        |    1 | Using where                     |
|  1 | SIMPLE      | bon   | eq_ref | PRIMARY                    | PRIMARY      | 4       | bte.bonusId |    1 | Using where                     |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                    | PRIMARY      | 4       | bon.userId  |    1 | Using where                     |
+----+-------------+-------+--------+----------------------------+--------------+---------+-------------+------+---------------------------------+建议你先看一下 五楼 的那几个贴子。

Answer 12

但是第1行 有 这个  Using temporary; Using filesort 
官方文档提示说 这个是应该优化 ？  （但我记得有时不能完全相信这些提示的 
他走了索引，应该是正常的哦）

Answer 13

想起来 GROUP BY bte.bonusId  这个必须要用到 Using temporary; Using filesort 的

Answer 14

按照那几个贴子的方法，把你的表结构，索引情况都列出来。desc table xxx
show index from xxx;

Answer 15

用LEFT OUTER JOIN where  is null 代替not in 效果好吗？