AI 助理
备案控制台
开发者社区 数据库 文章 正文

[MySQL优化案例]系列 -- 用TIMESTAMP类型取代INT和DATETIME

2017-11-08 998
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介：
引言：在以前，我总是习惯用 INT UNSIGNED 来存储一个转换成Unix时间戳的时间值，认为这样做从索引，比较等角度来讲，都会比较高效。现在我们来对比下 TIMESTAMP 和 INT UNSIGNED 以及 DATETIME 这3种类型到底谁更好。

1. 准备

创建一个测试表： 
mysql> CREATE TABLE `t` (
`d1` int(10) unsigned NOT NULL default '0',
`d2` timestamp NOT NULL default CURRENT_TIMESTAMP,
`d3` datetime NOT NULL,
KEY `d2` (`d2`),
KEY `d1` (`d1`),
KEY `d3` (`d3`)
);
然后创建一个存储过程填充数据： 
mysql> DELIMITER //
CREATE PROCEDURE INS_T()
BEGIN
SET @i=1;
WHILE 0<1
DO
SET @i=@i+1;
INSERT INTO i VALUES (1199116800+@i, FROM_UNIXTIME(1199116800+@i), FROM_UNIXTIME(1199116800+@i));
END WHILE;
END;//
DELIMITER ;
时间戳  1199116800 表示  2008-01-01 这个时间点。然后运行存储过程，大概填充几十万条记录后，中止执行，因为上面的存储过程是个死循环，所以需要人工中止。
来看看到底有多少条记录了，以及索引情况： 
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|   924707 |
+----------+
mysql> analyze table t;
+--------+---------+----------+-----------------------------+
| Table  | Op      | Msg_type | Msg_text                    |
+--------+---------+----------+-----------------------------+
| test.t | analyze | status   | Table is already up to date |
+--------+---------+----------+-----------------------------+
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t     |          1 | d2       |            1 | d2          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |
| t     |          1 | d1       |            1 | d1          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |
| t     |          1 | d3       |            1 | d3          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2. 对比

2.1 只检索一条记录

mysql> explain select * from t where d1 = 1199579155;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | d1            | d1   | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d2 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | d2            | d2   | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d3 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | d3            | d3   | 8       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+

2.2 范围检索

mysql> explain select * from t where d1 >= 1199894400;
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | d1            | d1   | 4       | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d2 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | d2            | d2   | 4       | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d3 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | d3            | d3   | 8       | NULL | 120625 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
小结：从上面的2次对比中可以看到，对  d1 或  d2 字段检索时的索引长度都是  4，因为  TIMESTAMP 实际上是  4字节的  INT 值。因此，实际应用中，基本上完全可以采用  TIMESTAMP 来代替另外2种类型了，并且  TIMESTAMP 还能支持自动更新成当前最新时间，何乐而不为呢？
作/译者：叶金荣（Email: ），来源：http://imysql.cn，转载请注明作/译者和出处，并且不能用于商业用途，违者必究。
引言：在以前，我总是习惯用 INT UNSIGNED 来存储一个转换成Unix时间戳的时间值，认为这样做从索引，比较等角度来讲，都会比较高效。现在我们来对比下 TIMESTAMP 和 INT UNSIGNED 以及 DATETIME 这3种类型到底谁更好。

1. 准备

创建一个测试表： 
mysql> CREATE TABLE `t` (
`d1` int(10) unsigned NOT NULL default '0',
`d2` timestamp NOT NULL default CURRENT_TIMESTAMP,
`d3` datetime NOT NULL,
KEY `d2` (`d2`),
KEY `d1` (`d1`),
KEY `d3` (`d3`)
);
然后创建一个存储过程填充数据： 
mysql> DELIMITER //
CREATE PROCEDURE INS_T()
BEGIN
SET @i=1;
WHILE 0<1
DO
SET @i=@i+1;
INSERT INTO i VALUES (1199116800+@i, FROM_UNIXTIME(1199116800+@i), FROM_UNIXTIME(1199116800+@i));
END WHILE;
END;//
DELIMITER ;
时间戳  1199116800 表示  2008-01-01 这个时间点。然后运行存储过程，大概填充几十万条记录后，中止执行，因为上面的存储过程是个死循环，所以需要人工中止。
来看看到底有多少条记录了，以及索引情况： 
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|   924707 |
+----------+
mysql> analyze table t;
+--------+---------+----------+-----------------------------+
| Table  | Op      | Msg_type | Msg_text                    |
+--------+---------+----------+-----------------------------+
| test.t | analyze | status   | Table is already up to date |
+--------+---------+----------+-----------------------------+
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t     |          1 | d2       |            1 | d2          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |
| t     |          1 | d1       |            1 | d1          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |
| t     |          1 | d3       |            1 | d3          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2. 对比

2.1 只检索一条记录

mysql> explain select * from t where d1 = 1199579155;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | d1            | d1   | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d2 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | d2            | d2   | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d3 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | d3            | d3   | 8       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+

2.2 范围检索

mysql> explain select * from t where d1 >= 1199894400;
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | d1            | d1   | 4       | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d2 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | d2            | d2   | 4       | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d3 >= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | d3            | d3   | 8       | NULL | 120625 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
小结：从上面的2次对比中可以看到，对  d1 或  d2 字段检索时的索引长度都是  4，因为  TIMESTAMP 实际上是  4字节的  INT 值。因此，实际应用中，基本上完全可以采用  TIMESTAMP 来代替另外2种类型了，并且  TIMESTAMP 还能支持自动更新成当前最新时间，何乐而不为呢？


本文转自叶金荣51CTO博客，原文链接：http://blog.51cto.com/imysql/308818，如需转载请自行联系原作者
文章标签：
云数据库 RDS MySQL 版
索引
关系型数据库
存储
MySQL
Unix
关键词：
云数据库 RDS MySQL 版优化
云数据库 RDS MySQL 版datetime
类型int
云数据库 RDS MySQL 版类型
云数据库 RDS MySQL 版案例
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
科技小先锋
目录
相关文章
nanshaws
|
17天前
|
关系型数据库 MySQL
用dbeaver创建一个enum类型，并讲述一部分，mysql的enum类型的知识
这篇文章介绍了如何在DBeaver中创建MySQL表的枚举（ENUM）字段，并探讨了MySQL中ENUM类型的一些行为特点，例如ENUM值的默认排序和在插入重复值时的表现。
nanshaws
29 1
用dbeaver创建一个enum类型，并讲述一部分，mysql的enum类型的知识
蓝易云
|
1月前
|
关系型数据库 MySQL 数据库
Python MySQL查询返回字典类型数据的方法
通过使用 `mysql-connector-python`库并选择 `MySQLCursorDict`作为游标类型，您可以轻松地将MySQL查询结果以字典类型返回。这种方式提高了代码的可读性，使得数据操作更加直观和方便。上述步骤和示例代码展示了如何实现这一功能，希望对您的项目开发有所帮助。
蓝易云
83 4
yuanzhengme
|
1月前
|
自然语言处理 算法 Java
Java如何判断两句话的相似度类型MySQL的match
【9月更文挑战第1天】Java如何判断两句话的相似度类型MySQL的match
yuanzhengme
23 2
wljslmz
|
2月前
|
关系型数据库 MySQL 数据管理
深入解析 MySQL 中的关系类型
【8月更文挑战第31天】
wljslmz
38 0
zzy的aly
|
9天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二)：数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结，包括登录、退出、查看时间与版本、数据库和数据表的基本操作（如创建、删除、查看）、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询，以及如何进行表结构的修改。这些内容对于初学者来说非常实用，是学习MySQL数据库管理的基础。
zzy的aly
43 6
m1a6
|
7天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构，类似于书籍中的索引。它允许用户快速找到数据，而无需扫描整个表。MySQL中的索引可以显著提升查询速度，使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
m1a6
39 3
Mysql(4)—数据库索引
zzy的aly
|
9天前
|
SQL Ubuntu 关系型数据库
Mysql学习笔记(一)：数据库详细介绍以及Navicat简单使用
本文为MySQL学习笔记，介绍了数据库的基本概念，包括行、列、主键等，并解释了C/S和B/S架构以及SQL语言的分类。接着，指导如何在Windows和Ubuntu系统上安装MySQL，并提供了启动、停止和重启服务的命令。文章还涵盖了Navicat的使用，包括安装、登录和新建表格等步骤。最后，介绍了MySQL中的数据类型和字段约束，如主键、外键、非空和唯一等。
zzy的aly
27 3
Mysql学习笔记(一)：数据库详细介绍以及Navicat简单使用
m1a6
|
14天前
|
缓存 算法 关系型数据库
Mysql(3)—数据库相关概念及工作原理
数据库是一个以某种有组织的方式存储的数据集合。它通常包括一个或多个不同的主题领域或用途的数据表。
m1a6
38 5
Mysql(3)—数据库相关概念及工作原理
代码bug生产队
|
1天前
|
存储 关系型数据库 MySQL
如何在MySQL中创建数据库？
【10月更文挑战第16天】如何在MySQL中创建数据库？
代码bug生产队
10 1
小Tomkk
|
5天前
|
SQL Oracle 关系型数据库
安装最新 MySQL 8.0 数据库（教学用）
安装最新 MySQL 8.0 数据库（教学用）
小Tomkk
34 4

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    PostgreSQL flashback(闪回) 功能实现与介绍
  • 3
    MySQL数据表生成ER图
  • 4
    阿里JAVA手册之MySQL数据库 （建表规约、索引规约、SQL语句、ORM映射）
  • 5
    centos8使用yum源安装mysql8
  • 6
    MySQL8.0大表秒加字段，是真的吗？
  • 7
    mysql引擎查看和修改
  • 8
    xtrabackup备份方式搭建一个mysql slave
  • 9
    MYSQL用SOURCE命令时导入乱码的问题解决
  • 10
    MySQL · 源码阅读 · Secondary Engine
  • 1
    Java时间转换为MySQL中的INT类型时间戳
    58
  • 2
    C++_int负数转unsigned
    56
  • 3
    java 数组转字符串 和字符串转int
    86
  • 4
    在Python中，整型（int）
    63
  • 5
    Java中Long转Int转字符串Int转Long以及Int超出长度判断
    58
  • 6
    Dataphin常见问题之与指定类型int不兼容如何解决
    89
  • 7
    Go语言中的int和int32：同一个概念吗？
    513
  • 8
    OceanBase CDC从热OB库采集过来的Tinyint（1）类型会默认转换成Boolean,请教一下，如果想转换成int类型，有什方法么？
    138
  • 9
    C# 字节数组与INT16,float,double之间相互转换，字符数组与字符串相互转换，
    148
  • 10
    Python系列（15）—— int类型转string类型
    123

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • PolarDB MySQL HTAP：实时数据分析加速
  • MySQL数据库DTS迁移上云

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    AI助理直击要害，从繁复中提炼精华——使用CDN加速访问OSS存储的图片