mysql中创建了一张双主键的表 插入数据90W条 SELECT COUNT(*) FROM TyumonDetail 竟然耗时6.7S 请各位高手指定一二。表结构如下
CREATE TABLE `TyumonDetail` (
`AtNo` int(4) unsigned NOT NULL,
`DetailNo` int(4) unsigned NOT NULL,
`Kubun` char(1) NOT NULL,
`Azukari` char(1) NOT NULL,
`SyohinCd` char(13) NOT NULL,
`SyohinNm` varchar(40) NOT NULL,
`SokoCd` varchar(4) NOT NULL,
`Ttnnyr` decimal(16,3) NOT NULL,
`Hako` decimal(16,3) NOT NULL,
`Ko` decimal(16,3) NOT NULL,
`Tani` varchar(4) NOT NULL,
`Tanka0` decimal(16,2) NOT NULL,
`Tanka1` decimal(16,2) NOT NULL,
`KinGaku0` decimal(16,0) NOT NULL,
`KinGaku1` decimal(16,0) NOT NULL,
`SyoHizei` decimal(16,0) NOT NULL,
`TtnnyrSKta` char(1) NOT NULL,
`HakoSKta` char(1) NOT NULL,
`KoSKta` char(1) NOT NULL,
`TankaSKta` char(1) NOT NULL,
`UZeiKb` char(1) NOT NULL,
`UZeikomi` char(1) NOT NULL,
`BiKo` varchar(40) NOT NULL,
`DelFlag` char(1) NOT NULL,
PRIMARY KEY (`AtNo`,`DetailNo`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `TyumonDetail` (
`AtNo` int(4) unsigned NOT NULL,
`DetailNo` int(4) unsigned NOT NULL,
`Kubun` char(1) NOT NULL,
`Azukari` char(1) NOT NULL,
`SyohinCd` char(13) NOT NULL,
`SyohinNm` varchar(40) NOT NULL,
`SokoCd` varchar(4) NOT NULL,
`Ttnnyr` decimal(16,3) NOT NULL,
`Hako` decimal(16,3) NOT NULL,
`Ko` decimal(16,3) NOT NULL,
`Tani` varchar(4) NOT NULL,
`Tanka0` decimal(16,2) NOT NULL,
`Tanka1` decimal(16,2) NOT NULL,
`KinGaku0` decimal(16,0) NOT NULL,
`KinGaku1` decimal(16,0) NOT NULL,
`SyoHizei` decimal(16,0) NOT NULL,
`TtnnyrSKta` char(1) NOT NULL,
`HakoSKta` char(1) NOT NULL,
`KoSKta` char(1) NOT NULL,
`TankaSKta` char(1) NOT NULL,
`UZeiKb` char(1) NOT NULL,
`UZeikomi` char(1) NOT NULL,
`BiKo` varchar(40) NOT NULL,
`DelFlag` char(1) NOT NULL,
PRIMARY KEY (`AtNo`,`DetailNo`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
你的表是不是innodb表呢?如果是的话,有这种可能,因为你没有建主键。如果是myisam的话,很快就出来了count(*)的结果。
你的问题,我也碰到过,确实是这样的,count(*)这个一般我用的很少,都是直接用count(1)或者count(主键字段)来着。
ANALYZE TABLE TyumonDetail; OPTIMIZE TABLE TyumonDetail;
然后再试试,会快么?