我的mysql关联查询/子查询很慢

时间:2022-09-20 10:39:25
查询一:
SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162

查询二:
SELECT id,user_id,quantity ,(select count(id) FROM coupon where order_id=`order`.id) as sumq FROM `order` where `team_id`=162

这两个查询都很慢
查询结果是120条

表order约有4万条数据,表coupon约有5万条数据

14 个解决方案

#1


在coupon的order_id上创建索引
在order的order_id上创建索引
在order的team_id上创建索引

#2


`order`.team_id,`order`.id 上建立复合索引,要是order.id是主键了,则只需要在order.team_id上建立索引
coupon.order_id 建立索引

#3


为什么慢?

#4


存储引擎用MyISAM需要花1分以上的时间

用InnoDB要5-6秒

#5


explain看执行计划

#6


贴出你的
explain SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162

explain SELECT id,user_id,quantity ,(select count(id) FROM coupon where order_id=`order`.id) as sumq FROM `order` where `team_id`=162

另外用 show index from tabl1 命令把相关表的索引情况贴出来以供分析。

#7


该回复于2011-01-10 14:21:43被版主删除

#8


引用 6 楼 acmain_chm 的回复:
贴出你的
explain SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162

explain SELECT id,user_id,quantity ,(select count(id) FR……


这些是优化查询的基本信息,都要贴出来。

#9


引用楼主 kevinworkroom 的回复:
查询一:
SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162

查询二:
SELECT id,user_id,quantity ,(select count(id) FROM coupon ……


没有索引的,建立team_id、order_id组合做引,然后explain 2个sql语句,看看走没有走索引。

#10


以下3条语句是在InnoDB存储引擎下执行
SELECT
  id,
  user_id,
  quantity,
  (SELECT
     COUNT(id)
   FROM coupon
   WHERE order_id = `order`.id) AS countq
FROM `order`
WHERE `team_id` = 162
    AND `order`.state = 'pay'
    AND `order`.delivery = 'coupon'

用时3.594秒




SELECT
`order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`,
  coupon
WHERE `order`.team_id = 162
    AND `order`.state = 'pay'
    AND `order`.delivery = 'coupon'
    AND `order`.id = coupon.order_id
GROUP BY `order`.id
用时0.296秒




SELECT
  `order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`
  LEFT JOIN coupon
    ON (`order`.id = coupon.order_id)
WHERE `order`.team_id = 162
    AND `order`.state = 'pay'
    AND `order`.delivery = 'coupon'
GROUP BY `order`.id

用时3.406秒

#11


在MyISAM整个没有办法接受

#12


EXPLAIN SELECT
`order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`,
  coupon
WHERE `order`.team_id = 162
    AND `order`.state = 'pay'
    AND `order`.delivery = 'coupon'
    AND `order`.id = coupon.order_id
GROUP BY `order`.id;


id  select_type  table   type    possible_keys  key      key_len  ref                      rows   Extra                            
1   SIMPLE       coupon  ALL                                                               48154  Using temporary; Using filesort  
1   SIMPLE       order   eq_ref  PRIMARY        PRIMARY  8        zuituan.coupon.order_id  1      Using where                                

#13


引用 12 楼 kevinworkroom 的回复:
EXPLAIN SELECT
`order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`,
  coupon
WHERE `order`.team_id = 162
  AND `order`.state = 'pay'
  AND `order`.delivery = 'coupon'
  AND `order……

coupon.order_id 这个字段上建立索引再看看explain;

#14


引用 12 楼 kevinworkroom 的回复:
EXPLAIN SELECT
`order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`,
  coupon
WHERE `order`.team_id = 162
  AND `order`.state = 'pay'
  AND `order`.delivery = 'coupon'
  AND `order……


楼主似乎并不认真看别人的回复!
你的show index 都贴在哪儿了?

从你的EXPLAIN来看,你的表中似乎根本没有创建索引。

#1


在coupon的order_id上创建索引
在order的order_id上创建索引
在order的team_id上创建索引

#2


`order`.team_id,`order`.id 上建立复合索引,要是order.id是主键了,则只需要在order.team_id上建立索引
coupon.order_id 建立索引

#3


为什么慢?

#4


存储引擎用MyISAM需要花1分以上的时间

用InnoDB要5-6秒

#5


explain看执行计划

#6


贴出你的
explain SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162

explain SELECT id,user_id,quantity ,(select count(id) FROM coupon where order_id=`order`.id) as sumq FROM `order` where `team_id`=162

另外用 show index from tabl1 命令把相关表的索引情况贴出来以供分析。

#7


该回复于2011-01-10 14:21:43被版主删除

#8


引用 6 楼 acmain_chm 的回复:
贴出你的
explain SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162

explain SELECT id,user_id,quantity ,(select count(id) FR……


这些是优化查询的基本信息,都要贴出来。

#9


引用楼主 kevinworkroom 的回复:
查询一:
SELECT `order`.id,`order`.quantity
FROM `order` LEFT JOIN `coupon` ON `order`.id = coupon.order_id
WHERE `order`.team_id=162

查询二:
SELECT id,user_id,quantity ,(select count(id) FROM coupon ……


没有索引的,建立team_id、order_id组合做引,然后explain 2个sql语句,看看走没有走索引。

#10


以下3条语句是在InnoDB存储引擎下执行
SELECT
  id,
  user_id,
  quantity,
  (SELECT
     COUNT(id)
   FROM coupon
   WHERE order_id = `order`.id) AS countq
FROM `order`
WHERE `team_id` = 162
    AND `order`.state = 'pay'
    AND `order`.delivery = 'coupon'

用时3.594秒




SELECT
`order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`,
  coupon
WHERE `order`.team_id = 162
    AND `order`.state = 'pay'
    AND `order`.delivery = 'coupon'
    AND `order`.id = coupon.order_id
GROUP BY `order`.id
用时0.296秒




SELECT
  `order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`
  LEFT JOIN coupon
    ON (`order`.id = coupon.order_id)
WHERE `order`.team_id = 162
    AND `order`.state = 'pay'
    AND `order`.delivery = 'coupon'
GROUP BY `order`.id

用时3.406秒

#11


在MyISAM整个没有办法接受

#12


EXPLAIN SELECT
`order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`,
  coupon
WHERE `order`.team_id = 162
    AND `order`.state = 'pay'
    AND `order`.delivery = 'coupon'
    AND `order`.id = coupon.order_id
GROUP BY `order`.id;


id  select_type  table   type    possible_keys  key      key_len  ref                      rows   Extra                            
1   SIMPLE       coupon  ALL                                                               48154  Using temporary; Using filesort  
1   SIMPLE       order   eq_ref  PRIMARY        PRIMARY  8        zuituan.coupon.order_id  1      Using where                                

#13


引用 12 楼 kevinworkroom 的回复:
EXPLAIN SELECT
`order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`,
  coupon
WHERE `order`.team_id = 162
  AND `order`.state = 'pay'
  AND `order`.delivery = 'coupon'
  AND `order……

coupon.order_id 这个字段上建立索引再看看explain;

#14


引用 12 楼 kevinworkroom 的回复:
EXPLAIN SELECT
`order`.id,
  `order`.quantity,
  COUNT(coupon.id)
FROM `order`,
  coupon
WHERE `order`.team_id = 162
  AND `order`.state = 'pay'
  AND `order`.delivery = 'coupon'
  AND `order……


楼主似乎并不认真看别人的回复!
你的show index 都贴在哪儿了?

从你的EXPLAIN来看,你的表中似乎根本没有创建索引。