MySQL 时间索引的选择

背景

MySQL 在使用过程中经常会对时间加索引，方便进行时间范围的查询，常见的时间类型有 data、datetime、long、timestamp 等，在此分析下这几种时间类型的索引大小，以找到比较合适的时间类型。

时间类型对比

常用的索引类型是 timestamp、datetime、long、int（秒级），占用的空间大小如下：
在这里插入图片描述
参考：https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
如图所示，timestam 和 datetime、long 类型都占用 8 字节空间，int 类型占用 4 个字节，具体验证下占用空间大小。
分别新建表 date1、timestamp1、long1、int_1，只有 id 和 created 字段，id 是主键，created 是索引，都是 10W 条数据，看下具体占用的索引空间大小。

create table date1 (
 id int (11) unsigned not null comment '主键',
 created datetime not null comment '创建时间',
 primary key(`id`),
 key `idx_created`(created) using btree
) engine = INNODB default charset = utf8 comment = 'date1'
 
 
create table long1 (
 id int (11) unsigned not null comment '主键',
 created bigint(20) not null comment '创建时间',
 primary key(`id`),
 key `idx_created`(created) using btree
) engine = INNODB default charset = utf8 comment = 'long1'
 
create table int_1 (
 id int (11) unsigned not null comment '主键',
 created int(11) not null comment '创建时间',
 primary key(`id`),
 key `idx_created`(created) using btree
) engine = INNODB default charset = utf8 comment = 'long2'
 
create table timestamp1 (
 id int (11) unsigned not null comment '主键',
 created timestamp not null comment '创建时间',
 primary key(`id`),
 key `idx_created`(created) using btree
) engine = INNODB default charset = utf8 comment = 'timestamp1'

批量插入 10W 条数据：

delimiter ;;
create procedure idata()
begin
  declare i int;
 
  set i=1;
  while(i<=100000)do
    insert into date1(id, created) values(i, DATE_ADD('2000-01-01 00:00:00', INTERVAL FLOOR(RAND() * 31536000) SECOND));
        set i=i+1;
  end while;
end;;
delimiter ;
call idata();
 
delimiter ;;
create procedure idata3()
begin
  declare i int;
 
  set i=1;
  while(i<=100000)do
    insert into timestamp1(id, created) values(i, DATE_ADD('2000-01-01 00:00:00', INTERVAL FLOOR(RAND() * 31536000) SECOND));
        set i=i+1;
  end while;
end;;
delimiter ;
call idata3();
 
delimiter ;;
create procedure idata1()
begin
  declare i int;
 
  set i=1;
  while(i<=100000)do
    insert into long1(id, created) values(i, 1697027000000 + i);
        set i=i+1;
  end while;
end;;
delimiter ;
call idata1();
 
delimiter ;;
create procedure idata2()
begin
  declare i int;
 
  set i=1;
  while(i<=100000)do
    insert into int_1(id, created) values(i, 1697000000 + i);
        set i=i+1;
  end while;
end;;
delimiter ;
call idata2();

查看占用的索引空间大小：

select
    table_schema, table_name, table_rows,
    -- round(DATA_LENGTH/1024/1024,2) as data_size_MB,
    round(DATA_LENGTH/1024/1024/1024,2) as data_size_GB,
    round(index_length/1024/1024,2) as index_size_MB,
--     round(index_length/1024/1024/1024,2) as index_size_GB,
    round((DATA_LENGTH + index_length)/1024/1024/1024,2) as data_index_sum_size_GB,
    table_comment
from information_schema.TABLES
    WHERE table_schema = 'test'
--     AND table_name='test'
    order by index_size_MB desc
    limit 20;

在这里插入图片描述
long、timestamp、datetime 占用的索引空间大小一直，int 类型的占用空间小一些，和预想的一致。
类型 TIMESTAMP 最大的优点是可以带有时区属性，因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区，那么类型 TIMESTAMP 是一种不错的选择。比如新闻类的业务，通常用户想知道这篇新闻发布时对应的自己国家时间，那么 TIMESTAMP 是一种选择。
虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数 __tz_convert()，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改，此时性能没有 datetime 好。
常规使用场景中 datetime 和 timestamp 都可。如果仅需要日期查询，也可以考虑建立 date 类型索引，占用空间会更小。