求救优化SQL 设置索引方法

Question

以下SQL语句查询非常慢，如果不能优化查询语句，从索引方面有没有的救？求赐
ecs_brand 一百条数据
ecs_goods_cart 空表
ecs_goods 七万条数据SELECT b.brand_id, b.brand_name, COUNT(*) AS goods_num FROM `ecs_brand` AS b, `ecs_goods` AS g LEFT JOIN `ecs_goods_cat` AS gc ON g.goods_id = gc.goods_id WHERE g.brand_id = b.brand_id AND (g.cat_id  IN ('10','11','12','13','14','15','16')  OR gc.cat_id  IN ('10','11','12','13','14','15','16') ) AND b.is_show = 1  AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 GROUP BY b.brand_id HAVING goods_num > 0 ORDER BY b.sort_order, b.brand_id ASC;

Answer 1

explain SELECT b.brand_id, b.brand_name, COUNT(*) AS goods_num FROM `ecs_brand` AS b, `ecs_goods` AS g LEFT ...
执行计划如何，贴出.

Answer 2

非常槽糕，如下：
mysql> explain SELECT b.brand_id, b.brand_name, COUNT(*) AS goods_num FROM `ecs_
brand` AS b, `ecs_goods` AS g LEFT JOIN `ecs_goods_cat` AS gc ON g.goods_id = gc
.goods_id WHERE g.brand_id = b.brand_id AND (g.cat_id  IN ('10','11','12','13','
14','15','16')  OR gc.cat_id  IN ('10','11','12','13','14','15','16') ) AND b.is
_show = 1  AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 GROU
P BY b.brand_id HAVING goods_num > 0 ORDER BY b.sort_order, b.brand_id ASC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: gc
         type: system
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 0
        Extra: const row not found
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ALL
possible_keys: PRIMARY,is_show
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 92
        Extra: Using where; Using temporary; Using filesort
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: g
         type: ref
possible_keys: cat_id,brand_id,goods_isd_isa_isr
          key: brand_id
      key_len: 2
          ref: b.brand_id
         rows: 619
        Extra: Using where
3 rows in set (0.00 sec)

Answer 3

无法避免排序，主要是where子句中的列和GROUPBY ORDERBY中的列重复，不知如何设置索引。

Answer 4

不加 ORDER BY b.sort_order, b.brand_id ASC; 看一下速度如何。一段段检查，看加上了哪部分导致速度慢。

Answer 5

一样的效果，先去掉ASC，再去掉b.brand_id,再去掉ORDER BY b.sort_order。依然是奇慢，explain的结果也是一样，没有丝毫变化。
有没有能针对当前这个查询语句结构设置一个索引，随便怎样，我添加上去试试。
或者大家能不能提供一点groupby和orderby同时存在的索引设置教程。THX

Answer 6

索引情况如何
`ecs_goods`：brand_id、cat_id上建立复合索引试试
去掉 HAVING后，速度有无变化

Answer 7

ecs_brand 只有一个brand_id的主键索引和一个is_show的单列索引，在ec_goods上新增了brand_id、cat_id的复合索引，但依然没有效果，还是避免不了文件排序。
mysql> create index cat_brand on ecs_goods(cat_id,brand_id);
Query OK, 70540 rows affected (6 min 12.83 sec)
Records: 70540  Duplicates: 0  Warnings: 0mysql> explain SELECT b.brand_id, b.brand_name, COUNT(*) AS goods_num FROM `ecs_
brand` AS b, `ecs_goods` AS g LEFT JOIN `ecs_goods_cat` AS gc ON g.goods_id = gc
.goods_id WHERE g.brand_id = b.brand_id AND (g.cat_id  IN ('10','11','12','13','
14','15','16')  OR gc.cat_id  IN ('10','11','12','13','14','15','16') ) AND b.is
_show = 1  AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 GROU
P BY b.brand_id > 0 ORDER BY b.sort_order, b.brand_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: gc
         type: system
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 0
        Extra: const row not found
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ALL
possible_keys: PRIMARY,is_show
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 92
        Extra: Using where; Using temporary; Using filesort
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: g
         type: ref
possible_keys: cat_id,brand_id,cat_brand
          key: brand_id
      key_len: 2
          ref: b.brand_id
         rows: 619
        Extra: Using where
3 rows in set (0.03 sec)

Answer 8

select b.brand_id, b.brand_name, COUNT(*) AS goods_num 
from (`ecs_goods` AS g LEFT JOIN `ecs_goods_cat` AS gc ON g.goods_id = gc.goods_id and g.is_on_sale = 1 )
inner join `ecs_brand` AS b on g.brand_id = b.brand_id
where (g.cat_id  IN ('10','11','12','13','14','15','16') OR gc.cat_id  IN ('10','11','12','13','14','15','16'))
 AND b.is_show = 1 
GROUP BY b.brand_id 
HAVING goods_num > 0 
这个语句速度如何？
另外不要用 /G方式贴你的结果，不容易分析。

Answer 9

依然一样，查询奇慢，explain无变化。
mysql> explain select b.brand_id, b.brand_name, COUNT(*) AS goods_num from (`ecs_goods` AS g LEFT JOIN `ecs_goods_cat` AS gc ON g.goods_id = gc.goods_id an
d g.is_on_sale = 1 ) inner join `ecs_brand` AS b on g.brand_id = b.brand_id where (g.cat_id  IN ('10','11','12','13','14','15','16') OR gc.cat_id  IN ('10'
,'11','12','13','14','15','16')) AND b.is_show = 1 GROUP BY b.brand_id HAVING goods_num > 0;
+----+-------------+-------+--------+---------------------------+----------+---------+---------------------+------+----------------------------------------
------+
| id | select_type | table | type   | possible_keys             | key      | key_len | ref                 | rows | Extra
      |
+----+-------------+-------+--------+---------------------------+----------+---------+---------------------+------+----------------------------------------
------+
|  1 | SIMPLE      | gc    | system | PRIMARY                   | NULL     | NULL    | NULL                |    0 | const row not found
      |
|  1 | SIMPLE      | b     | ALL    | PRIMARY,is_show           | NULL     | NULL    | NULL                |   92 | Using where; Using temporary; Using filesort
|
|  1 | SIMPLE      | g     | ref    | cat_id,brand_id,cat_brand | brand_id | 2       | b.brand_id |  619 | Using where
      |
+----+-------------+-------+--------+---------------------------+----------+---------+---------------------+------+----------------------------------------
------+
3 rows in set (0.00 sec)

Answer 10

测试如下语句，如有问题，贴出你的 show index , explain select 以供分析。
select b.brand_id, b.brand_name, COUNT(*) AS goods_num
from `ecs_brand` AS b
where b.is_show = 1 
and (
exists (select 1 from ecs_goods where brand_id = b.brand_id and g.is_on_sale = 1 and cat_id  IN ('10','11','12','13','14','15','16'))
or 
exists (select 1 from `ecs_goods` AS g INNER JOIN `ecs_goods_cat` AS gc ON g.goods_id = gc.goods_id where g.brand_id = b.brand_id adn g.is_on_sale = 1 and  gc.cat_id  IN ('10','11','12','13','14','15','16')
)
GROUP BY b.brand_id 
HAVING goods_num > 0;

Answer 11

去掉 HAVING后，速度有无变化

Answer 12

acmain同学的语句我修了三个地方（增加了一个AS g,一个括号，改adn为and），检索时间确实缩短了一半，但是取出的goods_num值为1，这是错误的。mysql> select SQL_NO_CACHE b.brand_id, b.brand_name, COUNT(*) AS goods_num 
from `ecs_brand` AS b 
where b.is_show = 1 
and (
exists (select 1 from `ecs_goods` AS g where brand_id = b.brand_id and g.is_on_sale = 1 and cat_id IN ('10','11','12','13','14','15','16')) 
or 
exists (select 1 from `ecs_goods` AS g INNER JOIN `ecs_goods_cat` AS gc ON g.goods_id = gc.goods_id where g.brand_id = b.brand_id and g.is_on_sale = 1 and  gc.cat_id  IN ('10','11','12','13','14','15','16'))) 
GROUP BY b.brand_id 
HAVING goods_num > 0;
同时回复aaaa同学，去掉HAVING和建立联合索引后检索时间和索引没有任何变化。