数据库时间戳到底用timestamp还是int

虚拟机(Virtualbox)配置

CPU: 4
RAM: 4G
DISK: 20G SSD
OS: Ubuntu 18.04.1 LTS
Mariadb: 10.3.13

表相关说明

  • 数据大小390万
  • 库(compare) 引擎:InnoDB 字符集:utf8mb4-general-ci

表字段

id t_int t_timestamp
自增主键 int 的时间戳 timestamp 时间

表列表说明

  • time 最原始的表
  • time_index 分别给t_int和t_timestamp设置了普通索引
  • time_int 只有 t_int 字段
  • time_timestamp 只有 t_timestamp 字段

sql查询

这里就只贴出都能使用到索引极限rows下的查询语句

这里的timestamp时间会晚8小时,因为我这边写入数据的时候php的时区是 UTC

t_int explain

MariaDB [compare]> explain select SQL_NO_CACHE * from time_index WHERE t_int >= 1552735675 and t_int <= 1552737958;
+------+-------------+------------+-------+---------------+-----------+---------+------+--------+-----------------------+
| id   | select_type | table      | type  | possible_keys | key       | key_len | ref  | rows   | Extra                 |
+------+-------------+------------+-------+---------------+-----------+---------+------+--------+-----------------------+
|    1 | SIMPLE      | time_index | range | int_index     | int_index | 4       | NULL | 654620 | Using index condition |
+------+-------------+------------+-------+---------------+-----------+---------+------+--------+-----------------------+

t_timestamp explain

MariaDB [compare]> explain select SQL_NO_CACHE * from time_index WHERE t_timestamp >= '2019-03-16 11:27:55' and t_timestamp <= '2019-03-16 12:05:58';
+------+-------------+------------+-------+-----------------+-----------------+---------+------+--------+-----------------------+
| id   | select_type | table      | type  | possible_keys   | key             | key_len | ref  | rows   | Extra                 |
+------+-------------+------------+-------+-----------------+-----------------+---------+------+--------+-----------------------+
|    1 | SIMPLE      | time_index | range | timestamp_index | timestamp_index | 5       | NULL | 660630 | Using index condition |
+------+-------------+------------+-------+-----------------+-----------------+---------+------+--------+-----------------------+

不适用索引(time) t_int

select SQL_NO_CACHE * from time WHERE t_int >= 1552735675 and t_int <= 1552737958;

342351 rows in set (1.050 sec)
342351 rows in set (1.046 sec)
342351 rows in set (1.092 sec)
342351 rows in set (1.132 sec)

不适用索引(time) t_timestamp

select SQL_NO_CACHE * from time WHERE t_timestamp >= '2019-03-16 11:27:55' and t_timestamp <= '2019-03-16 12:05:58';

342351 rows in set (2.260 sec)
342351 rows in set (2.457 sec)
342351 rows in set (2.471 sec)
342351 rows in set (2.668 sec)

使用索引(time_index) t_int

select SQL_NO_CACHE * from time_index WHERE t_int >= 1552735675 and t_int <= 1552737958;

342351 rows in set (0.662 sec)
342351 rows in set (0.751 sec)
342351 rows in set (0.744 sec)
342351 rows in set (0.783 sec)

使用索引(time_index) t_timestamp

select SQL_NO_CACHE * from time_index WHERE t_timestamp >= '2019-03-16 11:27:55' and t_timestamp <= '2019-03-16 12:05:58';

342351 rows in set (1.118 sec)
342351 rows in set (1.040 sec)
342351 rows in set (1.089 sec)
342351 rows in set (1.048 sec)

compare库中每个表的数据容量对比

use information_schema

select 
table_schema as 'db',
table_name as 'table',
table_rows as 'row',
truncate(data_length/1024/1024, 2) as 'data_cap(MB)',
truncate(index_length/1024/1024, 2) as 'index_cap(MB)'
from information_schema.tables
where table_schema='compare'
order by data_length desc, index_length desc;
+---------+----------------+---------+--------------+---------------+
| db      | table          | row     | data_cap(MB) | index_cap(MB) |
+---------+----------------+---------+--------------+---------------+
| compare | time_index     | 3992406 |       129.64 |          0.00 |
| compare | time_timestamp | 3993168 |       129.62 |          0.00 |
| compare | time_int       | 3993237 |       124.62 |          0.00 |
| compare | time           | 3720558 |       121.64 |          0.00 |
+---------+----------------+---------+--------------+---------------+

总结

  • 这么看 就查询 效率上说. int 比 timestamp 效率高
  • 如果使用 timestamp 要注意 时区的问题