mysqltimestamp和long存储时间效率比较_MySQL

来源：动视网责编：小采时间：2020-11-09 17:00:54

mysqltimestamp和long存储时间效率比较_MySQL

mysqltimestamp和long存储时间效率比较_MySQL: 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-0

推荐度：

点击下载本文 文档为doc格式

导读mysqltimestamp和long存储时间效率比较_MySQL: 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-0

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 :

select COUNT(*) from 20130107date
where c_date BETWEEN DATE('20110101') and DATE('20110102')

这个需要1.54s

2:

select COUNT(*) from 20130107date
where c_date_long BETWEEN UNIX_TIMESTAMP('20110101') and UNIX_TIMESTAMP('20110102')

这个是2.3s

但是可以这样搞 3 :

select UNIX_TIMESTAMP('20110101'),UNIX_TIMESTAMP('20110102');

得到结果1293811200和1293897600

然后

select COUNT(*) from 20130107date
where c_date_long BETWEEN 1293811200 and 1293897600；

发现变成了0.61s 1和2的差距还可以说是比较int和比较timestamp的差距,那么2和3的差距呢?难道多出来的时间是每一条记录都要evaluate UNIX_TIMESTAMP(‘20110102’)?

然后用索引

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'

毫无悬念,两个基本都是瞬时的.

mysqltimestamp和long存储时间效率比较_MySQL

推荐度：

点击下载本文 文档为doc格式

标签：时间效率存储

热门焦点

mysqltimestamp和long存储时间效率比较_MySQL

mysqltimestamp和long存储时间效率比较_MySQL

mysqltimestamp和long存储时间效率比较_MySQL

最新推荐

猜你喜欢

热门推荐