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