为时间字段加索引（待更进）

一、问题

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树结构