select 加上group by后好慢

Question

2个表数据也不多，但又是加上group by后好慢
SELECT a.goods_id as bb,a.least_num,a.shortInformation,a.short_number,a.advise_num,a.day_sales,a.goods_sn as asn,a.goods_name as aname,a.goods_price,a.goods_cost,a.goods_unit,a.goods_onroad,a.isuse,a.factory,a.goods_suplysn,a.cguser,b.* FROM my_goods AS a JOIN my_onhandle AS b ON a.goods_id = b.goods_id where a.ebay_user='vipyrd' and a.goods_sn = b.goods_sn and b.store_id=18 group by a.goods_sn order by goods_count limit 0,50explain 的结果是：
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: range
possible_keys: goods_sn_2,goods_sn_3,store_id,goods_sn,goods_id,goods_id_2,goods
_id_3,goods_id_4
          key: store_id
      key_len: 4
          ref: NULL
         rows: 21053
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: eq_ref
possible_keys: PRIMARY,ebay_user,goods_sn,goods_sn_2,goods_sn_3,goods_id,goods_s
n_4
          key: PRIMARY
      key_len: 4
          ref: newzehui.b.goods_id
         rows: 1
        Extra: Using whereshow index如下：
mysql> show index from my_goods\G;
*************************** 1. row ***************************
        Table: my_goods
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: goods_id
    Collation: A
  Cardinality: 24125
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: my_goods
   Non_unique: 1
     Key_name: ebay_user
 Seq_in_index: 1
  Column_name: ebay_user
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: my_goods
   Non_unique: 1
     Key_name: goods_sn
 Seq_in_index: 1
  Column_name: goods_sn
    Collation: A
  Cardinality: 24125
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: my_goods
   Non_unique: 1
     Key_name: goods_sn_2
 Seq_in_index: 1
  Column_name: goods_sn
    Collation: A
  Cardinality: 24125
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 5. row ***************************
        Table: my_goods
   Non_unique: 1
     Key_name: goods_sn_2
 Seq_in_index: 2
  Column_name: ebay_user
    Collation: A
  Cardinality: 24125
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 6. row ***************************
        Table: my_goods
   Non_unique: 1
     Key_name: goods_sn_3
 Seq_in_index: 1
  Column_name: goods_sn
    Collation: A
  Cardinality: 24125
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 7. row ***************************
        Table: my_goods
   Non_unique: 1
     Key_name: goods_sn_3
 Seq_in_index: 2
  Column_name: ebay_user
    Collation: A
  Cardinality: 24125
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 8. row ***************************
        Table: my_goods
   Non_unique: 1
     Key_name: goods_id
 Seq_in_index: 1
  Column_name: goods_id
    Collation: A
  Cardinality: 24125
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 9. row ***************************
        Table: my_goods
   Non_unique: 1
     Key_name: goods_sn_4
 Seq_in_index: 1
  Column_name: goods_sn
    Collation: A
  Cardinality: 24125
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
9 rows in set (0.00 sec)mysql> show index from my_onhandle\G;
*************************** 1. row ***************************
        Table: my_onhandle
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: my_onhandle
   Non_unique: 0
     Key_name: goods_sn_2
 Seq_in_index: 1
  Column_name: goods_sn
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: my_onhandle
   Non_unique: 0
     Key_name: goods_sn_2
 Seq_in_index: 2
  Column_name: store_id
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: my_onhandle
   Non_unique: 0
     Key_name: goods_sn_3
 Seq_in_index: 1
  Column_name: goods_sn
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 5. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: ebay_user
 Seq_in_index: 1
  Column_name: ebay_user
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 6. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: store_id
 Seq_in_index: 1
  Column_name: store_id
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 7. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: goods_sn
 Seq_in_index: 1
  Column_name: goods_sn
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 8. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: goods_id
 Seq_in_index: 1
  Column_name: goods_id
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 9. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: goods_id
 Seq_in_index: 2
  Column_name: goods_sn
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 10. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: goods_id
 Seq_in_index: 3
  Column_name: store_id
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 11. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: goods_id_2
 Seq_in_index: 1
  Column_name: goods_id
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 12. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: goods_id_3
 Seq_in_index: 1
  Column_name: goods_id
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 13. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: goods_id_4
 Seq_in_index: 1
  Column_name: goods_id
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 14. row ***************************
        Table: my_onhandle
   Non_unique: 1
     Key_name: goods_id_4
 Seq_in_index: 2
  Column_name: goods_sn
    Collation: A
  Cardinality: 21056
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
14 rows in set (0.00 sec)

Answer 1

好像order by也有原因

Answer 2

my_goods上建个索引(ebay_user,goods_sn)

Answer 3

create index x1 on my_goods(ebay_user,goods_sn);
create index x2 on my_goods(goods_sn,ebay_user);
create index x3 on my_onhandle(goods_sn,store_id);
create index x4 on my_onhandle(store_id,goods_sn);

Answer 4

去掉ORDER　BY看看如何
.goods_id = b.goods_id where a.ebay_user='vipyrd' and a.goods_sn = b.goods_sn and b.store_id=18 group by a.goods_sn order by goods_count limit 0,50
my_goods在goods_id、ebay_user、goods_sn上建立索引
my_onhandle在goods_sn、store_id上建立索引

Answer 5

去掉order by或group by都好快的

Answer 6

重贴你的 explain select ..
show index from ..不要用/G 这个不方便别人分析。

Answer 7

强制使用a.ebay_user索引 和b.store_id索引