表结构如下
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小时在研究这问题...
重启mysql后正常了.......
Orz
我中午花了2小时在研究这问题...
#2
explain 看一下。
#3
都重启了 explain也没用了
#4
ORZ, 都重启了. 问题解决了就好。
#5
IMHO，tmp_table_size不宜过大，否则一旦超过此限，copy to disk的过程确实会很长
#1
(—_—b
重启mysql后正常了.......
Orz
我中午花了2小时在研究这问题...
重启mysql后正常了.......
Orz
我中午花了2小时在研究这问题...
#2
explain 看一下。
#3
都重启了 explain也没用了
#4
ORZ, 都重启了. 问题解决了就好。
#5
IMHO，tmp_table_size不宜过大，否则一旦超过此限，copy to disk的过程确实会很长