MySQl中有多种表示日期和时间的数据类型。其中YEAR表示年份，DATE表示日期，TIME表示时间，DATETIME和TIMESTAMP表示日期和实践。它们的对比如下
YEAR ，字节数为1，取值范围为“1901——2155”
DATE，字节数为4，取值范围为“1000-01-01——9999-12-31”
TIME，字节数为3，取值范围为“-838:59:59——838:59:59”
DATETIME，字节数为8，取值范围为“1000-01-01 00:00:00——9999-12-31 23:59:59”
TIMESTAMP，字节数为4，取值范围为“19700101080001——20380119111407”.
以前经常看到有人说时间存储为int类型的时候查询会比timestamp类型快很多，到底有多大的差别哪？我动手测试了一下，给大家分享一下测试结果。
一、表结构
test1表为时间存储为int类型，test2表为存储为timestamp类型。
CREATE TABLE `test1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL DEFAULT '',
`address` varchar(100) NOT NULL DEFAULT '',
`email` varchar(50) NOT NULL DEFAULT '',
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_createdat` (`created_at`),
KEY `idx_name_time` (`name`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `test2` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL DEFAULT '',
`address` varchar(100) NOT NULL DEFAULT '',
`email` varchar(50) NOT NULL DEFAULT '',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_createdat` (`created_at`),
KEY `idx_name_time` (`name`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
二、生成测试数据
我是自己写的python脚本，利用了python的faker库。
import faker
import time
import pymysql
def timestamp_datetime(value):
format = '%Y-%m-%d %H:%M:%S'
# value为传入的值为时间戳(整形)，如：1332888820
value = time.localtime(value)
## 经过localtime转换后变成
## time.struct_time(tm_year=2012, tm_mon=3, tm_mday=28, tm_hour=6, tm_min=53, tm_sec=40, tm_wday=2, tm_yday=88, tm_isdst=0)
# 最后再经过strftime函数转换为正常日期格式。
dt = time.strftime(format, value)
return dt
def gen_sql(max=1000):
sqlData = []
i=0
while i < max :
name = generator.name()
email = generator.email()
unix_time=generator.unix_time()
address = generator.address()
time=timestamp_datetime(unix_time)
sql = "INSERT INTO test1 (`name`,`address`,`email`,`created_at`) VALUE ('%s','%s','%s',%d);" % (name,address,email,unix_time)
sql2 = "INSERT INTO test2 (`name`,`address`,`email`,`created_at`) VALUE ('%s','%s','%s','%s');" % (name,address,email,time)
i=i+1
sqlData.append(sql)
sqlData.append(sql2)
return sqlData
start_time = time.time()
generator = faker.Faker(locale="zh-cn")
total = 1000000*2
one_time=1000
times=total/one_time
print("start generating data")
conn = pymysql.connect(
host = "127.0.0.1",
user = "root",
password = "123456",
database = "sql_test",
charset = 'utf8',
cursorclass = pymysql.cursors.DictCursor)
cursor = conn.cursor()
i=0
while i < times:
sql_data = gen_sql(one_time)
for sql in sql_data:
cursor.execute(sql)
#commit以后更新才会提交到数据库
conn.commit()
i=i+1
print("add records : %d" % (i*one_time))
cursor.close()
conn.close()
use_time=time.time()-start_time
print("done,use time:%f" % (use_time))
三、占用空间
由于innodb的show table status from db中的值不准确，所以这里我们可以当做相等。
四、查询性能
#43ms
select SQL_NO_CACHE * from test1 where created_at > 1025340225 limit 10;
# 82ms
select SQL_NO_CACHE * from test2 where created_at > '2002-06-29 16:43:45' limit 10;
单一索引查询，从结果来看差距是很大的，几乎有一倍了，但是就看这点损耗你可以接受吗？
近期评论