Hack笔记

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;

单一索引查询，从结果来看差距是很大的，几乎有一倍了，但是就看这点损耗你可以接受吗？