为时间字段加索引

最新推荐文章于 2023-12-19 17:07:56 发布
最新推荐文章于 2023-12-19 17:07:56 发布
阅读量1.2w 收藏 7
点赞数 2
分类专栏： # mysql & mybatis 文章标签： mysql 索引 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33934427/article/details/105078236
版权

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

文章目录

一、问题
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树结构

王小希ww
关注
  • 2
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
PHP以指定字段为索引返回数据库所取的数据数组
12-19
本篇文章将介绍如何以指定字段为索引返回数据库查询结果的数据数组，这在处理大量数据时尤其有用，可以优化内存使用并加快后续操作的速度。这里我们将以一个统计网站流量的例子进行说明。 首先，我们有两个相关联的...
如何在SQLITE中使用时间字段作索引
09-11
除了主键索引，还可以为时间字段创建单独的普通索引，这在时间字段不是主键或者需要额外的查询优化时很有用。例如： ```sql sqlite> create index idx_time on timetest(time); ``` 创建了名为 `idx_time` 的索引...
MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法总结
09-09
主要介绍了MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法,总结分析了MySQL建表、编码设置、字段添加、索引操作所涉及的SQL语句,需要的朋友可以参考下
MySQL带时间字段的范围查询不走索引了？
热门推荐
多学多想
07-10 2万+
文章内容输出来源：拉勾教育Java高薪训练营。 今天群里抛出了个乍看之下很迷的问题： 看到问题的第一瞬间，我想这是什么原理，比较方向的修改就能引起索引失效了？于是我自己先试了一下。 环境：MySQL 5.7.25 1. 创建测试表 我先整了个32万行记录的表test_user，表中仅有两个字段：id和创建时间： CREATE TABLE `test_memory` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `create_ti
索引
weixin_30562507的博客
02-25 280
可以建立索引的；至于建立聚集索引或者是非聚集索引，那要看你这个时间字段的具体情况以及使用或变更频繁程度。一般来说，适合建立聚集索引的要求：“既不能绝大多数都相同，又不能只有极少数相同”的规则。先说说一个误区：有人认为：只要建立索引就能显著提高查询速度。这个想法是很错误的。建立非聚集索引，确实，一般情况下可以提高速度，但是一般并不会达到你想要的速度。只有在适当的列建立适当的（聚集）索引，才能达到满意...
时间字段加索引
人生最遗憾的莫过于：轻易地放弃了不该放弃的,固执地坚持了不该坚持的
11-14 2万+
一、深入浅出理解索引结构  　　实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：  　　其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会...
MySql中时间字段增加函数索引
最新发布
fanger0705的博客
12-19 793
使用explain select * from sys_robot_log where DATE(datetime) = DATE(now())查看是否使用到索引。使用explain select * from sys_robot_log where DATE(datetime) = DATE(now())查看是否使用到索引。重新将date函数设为索引：alter table sys_robot_log add key idx_date_index((date(datetime)));
mysql时间字段使用索引
CSDN_LGY_Java的博客
12-23 2万+
索引可以提高查询效率，平时按照时间查询经常遇到，所以就会给时间添加索引，但是添加索引怎么用呢？ 首先创建一张表test_table 然后给时间字段创建索引 导入大量数据，可以使用存储过程 查看一下表中数据条数 一共78W数据，我们用平常的sql查询2019-12-30号当天的数据 SELECT * FROM test_table WHERE DATE_FORMAT(create_time,"%Y-%m-%d") >= '2019-12-30' 一...
mysql unix时间加索引_【mysql】mysql时间字段怎么加索引
weixin_42568512的博客
02-02 3451
查询时需要根据时间字段进行范围查询，timestamp类型。语句没有子查询、关联查询，只查这一张表。查出5万条结果大约需要4.5秒，这个字段已经加了普通索引，但是explain的类型还是ALL。怎么优化？语句是between and。换了>=和<=查询需要的时间也差不多。SELECT id,name,sex,city,headimg FROM user WHERE starttime ...
在OracleE数据库的字段上建立索引的方法
12-16
总的来说，对于在`WHERE`子句中使用函数的情况，我们可以通过对查询语句的适当修改，创建出能够与索引配合的条件，从而在不牺牲查询性能的前提下，实现对数据库字段的高效检索。这种技术在处理含有大量数据的表时...
MySQL分区字段列有必要再单独建索引吗？
09-09
比如，你有一个复合索引(index_date_hour_coun)包含日期(date)、小时(hour)和国家(country)，即使创建时间(create_time)是分区字段，这个索引仍然可以帮助优化涉及这些字段的查询。 2. **索引覆盖**：如果查询仅...
时间字段都要加索引
weixin_34192816的博客
01-04 2105
时间字段都要加索引
给时间字段建立索引优化
forever_nice的博客
11-23 477
分享在开发中遇到的索引优化问题
你踩过这些坑吗？谨慎在时间类型列上创建索引
TiDB 社区干货传送门
06-10 561
作者： Zeratulll 原文来源： https://tidb.net/blog/9468d259 ...
mysql给createtime添加索引_mysql 索引 create_time 加explain关键字是否走索引
weixin_39549899的博客
01-21 1284
SELECT * FROM t_user WHERE email='217@xxg.com'; --1.725 --加email索引之后 0.003SELECT * FROM t_user WHERE email='316@xxg.com' LIMIT 1; 0.001 --加email索引之后 0.002结论：用户数据量很大的情况下 如果查询加了limit 无索引 根据唯一列查询 加索引和不...
mysql给date类型的字段加什么索引合适
weixin_35756690的博客
01-04 2046
MySQL 中的 DATE 类型字段可以使用 BTREE 索引来加速查询。 在命令行或 SQL 语句中，可以这样创建 BTREE 索引： CREATE INDEX index_name ON table_name (column_name); 其中，index_name 是索引的名称，table_name 是表的名称，column_name 是要创建索引的字段名。 你也可以使用 UNIQUE 关...
mysql 时间加索引加速查询
廖圣平
12-16 761
业务请求的日志在数据库中储存，当数据量大的时候排查问题会比较吃力，因为created_at 没有加索引去查会全表扫描。
datetime列是否有必要建索引
xiaoxin_su的博客
09-06 7979
准备用一datetime列存储 登记时间； 1、索引最好建立在重复性较多的列，datetime很明显重复性很少，建立索引有用吗？ 2、重复性较少的记录，如果建立索引，是否索引要耗去很多存储空间？ 3、如果SQL查询一般是按天来查，是否有必要新增一个date冗余列来存储 登记日期？ 4、char(8),date,int都可以存储日期，char和int还有更好的移植性，是这样？ 答： 第一个问题，大原则是，只要有直接比较的查询如=,>,<列名这样的，就需要建索引。 第二个问题，存储空间消耗基本是一样
mysql所有字段加索引
05-14
为所有字段添加索引并不是一个好的做法，因为它会影响数据库的性能。索引是用来加速查询操作的，但是会降低写入操作的性能，并且会占用更多的磁盘空间。因此，只有那些经常用于查询的字段才需要进行索引。 在 MySQL 中，可以使用以下语法来为字段添加索引： ``` ALTER TABLE table_name ADD INDEX index_name (column_name); ``` 其中，`table_name` 是要添加索引的表名，`index_name` 是索引的名称，`column_name` 是要添加索引的字段名。 例如，为 `user` 表的 `username` 字段添加索引，可以使用以下语句： ``` ALTER TABLE user ADD INDEX username_index (username); ``` 需要注意的是，如果一个表中的记录数量很少，或者查询操作很少使用某些字段，那么为这些字段添加索引可能会降低性能，因为 MySQL 需要额外的时间来维护索引。因此，建议在添加索引之前进行仔细的分析和测试，以确保索引的使用可以提高查询性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值