查询所有有商品的栏目:
explain select ecs_category.cat_id,ecs_category.cat_name from ecs_goods inner join ecs_category on ecs_category.cat_id=ecs_goods.cat_id group by ecs_category.cat_name \G
group by cat_name显然不如group by cat_id速度快,cat_id是×××,cat_name是字符串。
explain select ecs_category.cat_id,ecs_category.cat_name from ecs_goods inner join ecs_category on ecs_category.cat_id=ecs_goods.cat_id group by ecs_category.cat_id \G
用到了Using temporary; Using filesort,性能自然成问题
然而,仅仅是为了查找有商品的category,就把goods和category连接(inner join)并且group by,显然是不划算的!
这时候就要用到exists了:
explain select ecs_category.cat_id,ecs_category.cat_name from ecs_category where exists (select * from ecs_goods where ecs_goods.cat_id=ecs_category.cat_id) \G
exists子查询
题: 查询有商品的栏目.
按上面的理解,我们用join来操作,如下:
mysql> select c.cat_id,cat_name from ecs_category as c inner join goods as g
on c.cat_id=g.cat_id group by cat_name; (见36)
优化1: 在group时, 用带有索引的列来group, 速度会稍快一些,另外,
用int型 比 char型 分组,也要快一些.(见37)
优化2: 在group时, 我们假设只取了A表的内容,group by 的列,尽量用A表的列,
会比B表的列要快.(见38)
优化3: 从语义上去优化
select cat_id,cat_name from ecs_category where exists(select *from goods where goods.cat_id=ecs_category.cat_id) (见40)
| 36 | 0.00039075 | select c.cat_id,cat_name from ecs_category as c inner
join goods as g on c.cat_id=g.cat_id group by cat_name
|
| 37 | 0.00038675 | select c.cat_id,cat_name from ecs_category as c inner
join goods as g on c.cat_id=g.cat_id group by cat_id
|
| 38 | 0.00035650 | select c.cat_id,cat_name from ecs_category as c inner
join goods as g on c.cat_id=g.cat_id group by c.cat_id
|
| 40 | 0.00033500 | select cat_id,cat_name from ecs_category where exists
(select * from goods where goods.cat_id=ecs_category.cat_id)
|
from 型子查询:
注意::内层from语句查到的临时表, 是没有索引的.
所以: from的返回内容要尽量少.
转载于:https://blog.51cto.com/5660061/2375781