bitsCN.com mysql timestamp和int存储时间Sql代码show create table 20130107date;CREATE TABLE `20130107date` (`id` int(11) NOT NULL AUTO_INCREMENT,`c_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`c_date_long` int(20) NOT NULL,`idx_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`idx_date_long` int(11) NOT NULL,PRIMARY KEY (`id`),KEY `20130107date_idx_date` (`idx_date`),KEY `20130107date_idx_long` (`idx_date_long`)) ENGINE=InnoDB里面有90w数据,都是随机的时间.
先看没有索引的全表扫描1 :Sql代码select COUNT(*) from 20130107date where c_date BETWEEN DATE('20110101') and DATE('20110102')这个需要1.54s 2:Sql代码select COUNT(*) from 20130107date where c_date_long BETWEEN UNIX_TIMESTAMP('20110101') and UNIX_TIMESTAMP('20110102')这个是2.3s但是可以这样搞3 :Sql代码select UNIX_TIMESTAMP('20110101') ,UNIX_TIMESTAMP('20110102');得到结果1293811200和1293897600然后Sql代码select COUNT(*) from 20130107date where c_date_long BETWEEN 1293811200 and 1293897600;发现变成了0.61s 1和2的差距还可以说是比较int和比较timestamp的差距,那么2和3的差距呢?难道多出来的时间是每一条记录都要evaluate UNIX_TIMESTAMP('20110102')?然后用索引Sql代码select COUNT(*) from 20130107date where idx_date_long BETWEEN UNIX_TIMESTAMP('20110101') and UNIX_TIMESTAMP('20110102');select COUNT(*) from 20130107date where idx_date BETWEEN '20110101' and '20110102'毫无悬念,两个基本都是瞬时的.
bitsCN.com