为时间字段加索引

mysql 专栏收录该内容
10 篇文章 0 订阅

为时间字段加索引(待更进)

一、问题

1、描述:日期不一致
  • 在centos7中运行docker,docker中运行mysql,在IDEA中将日期数据写入dates表中

  • dates表
    在这里插入图片描述

  • 插入日期数据

@Test
void contextLoads() {
  Date date = new Date(System.currentTimeMillis());
  System.out.println(date);   //Tue Mar 24 13:08:49 CST 2020
  questionMapper.insertdateTime(date);
}
  • mysql中dates数据

在这里插入图片描述

2、原因:时区不同

​ 发现后台输入和数据库中的数据相差了8个小时,因为系统时区和docker中的mysql时区相差8个时区

[root@localhost ~]# docker exec -it mysql01 /bin/bash
root@1c202aea2496:/# mysql -uroot -p  
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2604
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

备注:

  • 在连接mysql时记得有"-u"参数,否则会出现Ignoring query to other database错误
3、解决方法: 时区修改
mysql> set global time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.02 sec)
  • 再次插入日期数据
Tue Mar 24 13:32:39 CST 2020
  • 数据查看
    在这里插入图片描述

二、datetime和varchar类型效率比较

​ 我记得之前我是通过修改了my.conf文件,进而修改了docker中mysql的日期格式,起效果后,但是下次重启mysql时,启动不容器。打开错误日志后,告诉我my.conf里出现错误,我迫不得已将日期格式改了回来,将datetime改成了varchar类型,来存储时间。

​ 至于如何让docker中的mysql重启的,详见这里

​ 那么datetime和varchar在查找和内存消耗方面有什么区别吗?

以下测试摘录于这里

1、背景

大家都知道数据库表字段设计得是否合理,对查询速度的快慢至关重要,下面做个简单的测试,看下差距有多大

2、开始

1、在数据库中新建两个表 test1(不合理的表)test2(合理表),两张表的 send_time 字段的类型不一样

CREATE TABLE `test1` (
	 `id` int(11) NOT NULL AUTO_INCREMENT,
	`send_time` varchar(20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `test2` (
	 `id` int(11) NOT NULL AUTO_INCREMENT,
	`send_time` datetime DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2、新建存储过程,导入10 000 000 条测试数据(运行过程可能需要几分钟),运行结束后再将数据导入表test2保证两张表的数据一致

BEGIN
DECLARE i INT DEFAULT 0;
START TRANSACTION;
	WHILE i<10000000 DO
INSERT INTO test1(send_time) VALUES(from_unixtime(1541302365+FLOOR(rand()*154130236),"%Y-%m-%d %H:%i:%s"));
SET i=i+1;
END WHILE;
COMMIT;
END

3、查看表信息可得:
在这里插入图片描述
在这里插入图片描述

结论:varchar 表字段占用存储空间 是 datetime 表的三倍左右

4、查询速度比较

	SELECT * FROM test1 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
	SELECT * FROM test2 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
	
	结果比较
	test1			test2
	2.653s			1.833s
	2.650s			1.866s

5、给表添加索引(执行过程需要几分钟),并查询速度比较

	-- 添加索引		
	ALTER TABLE `test1` ADD INDEX `n_sendtime` (`send_time`) ;
	ALTER TABLE `test2` ADD INDEX `n_sendtime` (`send_time`) ;

	-- 查询速度比较:
	SELECT * FROM test1 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
	SELECT * FROM test2 WHERE send_time>'2019-03-17' and send_time<'2019-03-18';
	
	结果比较
	test1			test2
	0.084s			0.048s
	0.062s			0.046s
	0.062s			0.048s
	0.066s			0.047s

4、结论

  • 合理的字段类型不论对 查询速度 或是 数据存储 都至关重要
  • 时间字段用dateTime等时间类型,不要用varchar类型

三、时间字段加索引

1、聚集索引
  • 表记录的排列顺序和与索引排列顺序一致

  • 一个表中只能拥有一个聚集索引

  • 修改慢。为了保证表中记录的物理和索引顺序一致,在记录插入的时候,会对数据页重新排序

  • 聚集索引的叶节点就是最终的数据节点

2、非聚集索引
  • 逻辑顺序与磁盘上行的物理存储顺序不同
  • 一个表中可以拥有多个非聚集索引
  • 非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针
动作描述使用聚集索引使用非聚集索引
列经常被分组排序
返回某范围内的数据不应
一个或极少不同值不应不应
小数目的不同值不应
大数目的不同值不应
频繁更新的列不应
外键列
主键列
频繁修改索引列不应
3、时间字段是否适合加索引

以下摘自此处

  • 可以建立索引的;至于建立聚集索引或者是非聚集索引,那要看你这个时间字段的具体情况以及使用或变更频繁程度。

  • 一般来说,适合建立聚集索引的要求:“既不能绝大多数都相同,又不能只有极少数相同”的规则。

  • 先说说一个误区:有人认为:只要建立索引就能显著提高查询速度。这个想法是很错误的。建立非聚集索引,确实,一般情况下可以提高速度,但是一般并不会达到你想要的速度。只有在适当的列建立适当的(聚集)索引,才能达到满意的效果

  • 考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

聚集索引和非聚集索引根本区别以及使用方式

​ 这就得看看项目中对该时间字段的具体操作了。

---- 待更新

四、参考文档

1、聚集索引和非聚集索引根本区别以及使用方式

2、mysql索引的应用

3、聚集索引和非聚集索引 简析与对比

4、索引深入浅出(3/10):聚集索引的B树结构

  • 1
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值