Copying to tmp table 时间很慢,调整了tmp_table_size也没用

时间:2022-09-16 10:08:45
数据库版本,mysql 5.1

表结构如下

DROP TABLE IF EXISTS `goods`;
CREATE TABLE IF NOT EXISTS `goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `in_id` int(6) NOT NULL DEFAULT '0',
  `out_id` int(6) NOT NULL DEFAULT '0',
  `qty` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `in_id` (`in_id`),
  KEY `out_id` (`out_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `goods_out`;
CREATE TABLE IF NOT EXISTS `goods_out` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

DROP TABLE IF EXISTS `goods_in`;
CREATE TABLE IF NOT EXISTS `goods_in` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

INSERT INTO `goods_out` (`id`, `date`) VALUES
(0, '2009-01-01 00:00:00'),
(1, '2010-03-02 00:00:00'),
(2, '2010-03-02 00:00:00');

INSERT INTO `goods_in` (`id`, `date`) VALUES
(0, '2009-01-01 00:00:00'),
(1, '2010-03-01 00:00:00'),
(2, '2010-03-02 00:00:00');

INSERT INTO `goods` (`id`, `in_id`, `out_id`, `qty`) VALUES
(1, 1, 0, 12),
(2, 1, 0, 12),
(3, 1, 1, 12),
(4, 2, 1, 8),
(5, 2, 1, 12),
(6, 2, 2, 9);

ALTER TABLE `goods`
  ADD CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`in_id`) REFERENCES `goods_in` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`out_id`) REFERENCES `goods_out` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;



以前查询不觉得慢,但今天突然发现,用下面的查询语句
SELECT * FROM `goods_out` INNER JOIN `goods` ON `goods_out`.`id` = `goods`.`out_id` WHERE `date` BETWEEN '2010-03-01' AND '2010-03-31' GROUP BY `goods_out`.`id`;

查询既然用了近2秒的时间,而且查询出来的数据量才100条左右而已
但是下面的查询语句却只用了0.01秒
SELECT * FROM `goods_in` INNER JOIN `goods` ON `goods_in`.`id` = `goods`.`out_id` WHERE `date` BETWEEN '2010-03-01' AND '2010-03-31' GROUP BY `goods_in`.`id`;


goods_in 和 goods_out 表可以说是一样的,而且查询出来的数据量也差不多,我试了很多次,只要是涉及到goods_out表的查询速度就很慢,不管数据量多少
goods_out查询
starting 0.000007 
checking query cache for query 0.000057 
Opening tables 0.000034 
System lock 0.000003 
Table lock 0.000036 
init 0.000030 
storing result in query cache 0.000022 
optimizing 0.000009 
statistics 0.000032 
preparing 0.000011 
Creating tmp table 0.000049 
executing 0.000002 
Copying to tmp table 1.710423 
Sorting result 0.000052 
Sending data 0.000051 
end 0.000002 
removing tmp table 0.000007 
end 0.000003 
query end 0.000003 
freeing items 0.000042 
logging slow query 0.000002 
cleaning up 0.000003 

goods_in查询
starting 0.000007 
checking query cache for query 0.000057 
Opening tables 0.000033 
System lock 0.000003 
Table lock 0.000060 
init 0.000033 
storing result in query cache 0.000021 
optimizing 0.000009 
statistics 0.000036 
preparing 0.000016 
Creating tmp table 0.000050 
executing 0.000001 
Copying to tmp table 0.145448 
Sorting result 0.000044 
Sending data 0.000048 
end 0.000001 
removing tmp table 0.000007 
end 0.000002 
query end 0.000003 
freeing items 0.000043 
logging slow query 0.000001 
cleaning up 0.000003 

我通过 set global tmp_table_size=209715200 设置了临时表的大小,但是依然是没用

5 个解决方案

#1


(—_—b

重启mysql后正常了.......
Orz
我中午花了2小时在研究这问题...

#2


explain 看一下。

#3


都重启了  explain也没用了

#4


ORZ, 都重启了. 问题解决了就好。

#5


IMHO,tmp_table_size不宜过大,否则一旦超过此限,copy to disk的过程确实会很长

#1


(—_—b

重启mysql后正常了.......
Orz
我中午花了2小时在研究这问题...

#2


explain 看一下。

#3


都重启了  explain也没用了

#4


ORZ, 都重启了. 问题解决了就好。

#5


IMHO,tmp_table_size不宜过大,否则一旦超过此限,copy to disk的过程确实会很长