因为我这个sql是动态的,有可能在type_code上分组,也有可能在其他字段上分组,还有就是实际使用时还有where 条件explain SELECT feet. NAME AS category, COUNT(fec. NAME) AS COUNT_name, COUNT(fec.asset_number) AS COUNT_asset_number, COUNT(fec.type_code) AS COUNT_type_code, COUNT(fec.specification_id) AS COUNT_specification_id FROM fdm_e_common fec, fdm_e_eq_type feet WHERE fec. STATUS < 3 AND fec.type_code = feet.type_code AND feet. STATUS = 1AND ( NOT fec.vte < 20140911114048561 ) AND ( NOT fec.vts > 99990101000000000 ) AND ( NOT fec.tte < 20140911114048561 ) AND ( NOT fec.vts > 99990101000000000 ) AND ( NOT feet.vte < 20140911114048561 ) AND ( NOT feet.vts > 99990101000000000 ) AND ( NOT feet.tte < 20140911114048561 ) AND ( NOT feet.vts > 99990101000000000 ) GROUP BY fec.type_code ORDER BY fec.type_code
1,2个表的type_code字段都加上索引。2,你的很多这种判断 AND ( NOT feet.tte < 20140911114048561 ) AND ( NOT feet.vts > 99990101000000000 ) 看起来很诡异啊,不知道是什么业务逻辑?
因为我这个sql是动态的,有可能在type_code上分组,也有可能在其他字段上分组,还有就是实际使用时还有where 条件explain SELECT
feet. NAME AS category,
COUNT(fec. NAME) AS COUNT_name,
COUNT(fec.asset_number) AS COUNT_asset_number,
COUNT(fec.type_code) AS COUNT_type_code,
COUNT(fec.specification_id) AS COUNT_specification_id
FROM
fdm_e_common fec,
fdm_e_eq_type feet
WHERE
fec. STATUS < 3
AND fec.type_code = feet.type_code
AND feet. STATUS = 1AND (
NOT fec.vte < 20140911114048561
)
AND (
NOT fec.vts > 99990101000000000
)
AND (
NOT fec.tte < 20140911114048561
)
AND (
NOT fec.vts > 99990101000000000
)
AND (
NOT feet.vte < 20140911114048561
)
AND (
NOT feet.vts > 99990101000000000
)
AND (
NOT feet.tte < 20140911114048561
)
AND (
NOT feet.vts > 99990101000000000
)
GROUP BY
fec.type_code
ORDER BY
fec.type_code
AND (
NOT feet.tte < 20140911114048561
)
AND (
NOT feet.vts > 99990101000000000
)
看起来很诡异啊,不知道是什么业务逻辑?
这个时态逻辑,不过你说的给type_code加索引,可是实际使用时SQL是动态的,不一定用到type_code
这个时态逻辑,不过你说的给type_code加索引,可是实际使用时SQL是动态的,不一定用到type_code你把业务逻辑整理一下,都会用到哪些字段,用到的加上合适的索引,你现在索引加的不合适啊。
不加索引前,查询用时十秒左右,刚给type_code加了个索引,用了十六秒
这个执行结果贴出来吧。这是获取详细的表结构信息的sql语句。
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`manufacturer_id` int(11) DEFAULT NULL,
`specification_id` int(11) DEFAULT NULL,
`fid` int(11) DEFAULT NULL,
`type_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`sskx_fid` int(11) DEFAULT NULL,
`smzq` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`publish_time` datetime DEFAULT NULL,
`ssdw` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`group_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`vts` bigint(17) NOT NULL,
`vte` bigint(17) NOT NULL,
`tts` bigint(17) NOT NULL,
`tte` bigint(17) NOT NULL,
`asset_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`asset_state` int(2) DEFAULT NULL,
`asset_belongs` int(2) DEFAULT NULL,
`factory_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`construction_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`re` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`update_user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`status` int(2) DEFAULT '1',
PRIMARY KEY (`id`,`tte`,`tts`,`vts`,`vte`)
) ENGINE=InnoDB AUTO_INCREMENT=1834830 DEFAULT CHARSET=utf8
CREATE TABLE `fdm_e_eq_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`type_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
`table_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`order_id` int(11) DEFAULT NULL,
`vts` bigint(17) NOT NULL,
`vte` bigint(17) NOT NULL,
`tts` bigint(17) NOT NULL,
`tte` bigint(17) NOT NULL,
`update_user_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`status` int(2) DEFAULT '1',
PRIMARY KEY (`id`,`tte`,`vte`,`tts`,`vts`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
关键字段加上索引
AND (
NOT feet.vts > 99990101000000000
)
要统一改成
AND feet.vts <= .....
的形式.
否则索引同样可能用不上.不要出现NOT