请问mysql加了group by 后其他索引失效了,怎么解决?

Question

EXPLAIN SELECT
  s.id,
  s.name,
  s.price,
  s.unit,
  s.qty,
  s.remark,
  s.image,
  s.cuft,
  s.volume,
  s.status,
  s.vender_id AS venderId,
  s.booth_id AS boothId,
  s.booth_no AS boothNo,
  s.show_booth_no AS showBoothNo,
  s.name_en AS nameEn,
  s.bar_code AS barCode,
  s.product_code AS productCode,
  s.type_one_id AS typeOneId,
  s.type_one_name AS typeOneName,
  s.type_two_id AS typeTwoId,
  s.type_two_name AS typeTwoName,
  s.ep_price AS epPrice,
  s.pack_cn AS packCn,
  s.pack_en AS packEn,
  s.inner_box AS innerBox,
  s.out_chest_long AS outChestLong,
  s.out_chest_width AS outChestWidth,
  s.out_chest_height AS outChestHeight,
  s.pack_long AS packLong,
  s.pack_width AS packWidth,
  s.pack_height AS packHeight,
  s.sample_long AS sampleLong,
  s.sample_width AS sampleWidth,
  s.sample_height AS sampleHeight,
  s.can_supply AS canSupply,
  s.rough_weight AS roughWeight,
  s.net_weight AS netWeight,
  s.is_delete AS isDelete,
  s.create_by AS createBy,
  s.create_date AS createDate,
  s.update_by AS updateBy,
  s.update_date AS updateDate,
  s.credentials AS credentials,
  s.credentials_id AS credentialsId,
  s.vender_name AS venderName,
  s.vender_code AS venderCode,
  s.booth_no AS boothNo,
  (SELECT
    sl.name
  FROM
    sys_login sl
  WHERE id = s.update_by) AS updateName,
  (SELECT
    sl.name
  FROM
    sys_login sl
  WHERE id = s.create_by) AS createName,
  v.contact1 AS contact1,
  v.tel1 AS tel1,
  v.phone1 AS phone1,
  v.meet_phone AS meetPhone,
  v.fax AS fax,
  v.qq AS qq,
  v.sms_code AS smsCode,
  (SELECT
    bh.end_date
  FROM
    booth_hire bh
  WHERE bh.booth_no = s.booth_no
    AND bh.vender_id = s.vender_id
    AND bh.status = 0
  LIMIT 1) AS boothEndDate,
  (SELECT
    sl.name
  FROM
    sys_login sl
  WHERE sl.id = s.create_by) AS createName,
  (SELECT
    sl.name
  FROM
    sys_login sl
  WHERE sl.id = s.update_by) AS updateName,
  s.sold_out_by AS soldOutBy,
  s.sold_out_date AS soldOutDate,
  s.sold_out_type AS soldOutType
FROM
  sample s
  LEFT JOIN vender v
    ON s.vender_id = v.id
WHERE 1 = 1
  AND s.status != 3
  AND s.status != - 1
  AND s.is_delete = 0
group by s.vender_id
LIMIT 0,100

Answer 1

首先，select里面写join看着超级烦，为什么不能放在from里面join呢，怎么看怎么不顺眼，还有createName、updateName要写两遍？
然后，！=是不会走索引的，还有is_delete这种性别字段一样属性，如果过滤结果集基本都是很少的情况下可以用一下索引，否则加了索引本就没多少意义。这时候有个group by字段选择性更好的话，当然就会用group的索引了，因为反正你都要回表查询的。
So。索引失效很正常，如果一定要用的话，加force index好了

Answer 2

测试表qxd_user中number是索引列，reg_dev是普通列，没有加索引

1.group by 没加条件的时候是全表扫描

2.请用where条件带上索引,先后关系是先where再group by

3.group by 索引列，这样可以走索引

Answer 3

你应该来一个 http://sqlfiddle.com/#!2/70129/2