mysql中datetime字段建立索引并比较大小详解

目录
  • 1、问题背景
  • 2、优化过程
  • 补充：Mysql 时间Datetime 索引不生效问题
  • 总结

1、问题背景

最近测试库查询一个表的数据，需要用到唯一的一个日期类型字段作为 where 的子查询（查询当天的数据），就正常写了个这样的 SQL，具体的表名我就不写了：

# create_time 是 datetime 类型
select * from ${tablename} where date(create_time)='20220919' limit 20;

其中字段的值样本如下：

我知道我写的这条 SQL 即使在 create_time 这个列有索引的情况下也不会走索引，但是执行了以后就 wc 了：

NM！20条记录你查询需要 8 s！

这种问题要不优化都不敢说自己是农民工！

2、优化过程

1） 操作索引

首先我看了下这个表的索引：

show index from ${tablename};

一看，有一个列对应了 2 个索引字段，也就是 create_time 没有索引，我就干掉其中一个重复的并在 create_time 上建了一个（表名我就不写了）：

#删除索引
drop index index_create_time on ${tablename};
#添加索引
create index index_create_time on {tablename}(create_time);

2）是否走索引判断

有索引了关键是得让它走索引啊，先验证一下，我用执行计划继续执行了上面问题背景中的那个sql：

explain select * from ${tablename} where date(create_time)='20220919' limit 20;

结果如下，一点都不意外，照样全表扫描：

3）datetime使用索引查询

既然不能操作索引列，我又想查询某一天的数据，就只能用范围了，范围的就那个几个，between…and 、> 、<
于是，我写了下面查询 SQL：

select * from ${tablename} where create_time between '2022-09-19 00:00:00' and '2022-09-19 23:59:59';

果然不辜负我的小操作，查询结果如下：

查询 1850 条数据平均在 0.5 s，这不用说，绝壁走了索引，一个查询 20 条花费 8s，一个小 2 千记录花费半秒，没有可比性。

下面验证这个查询，同样在 sql 前加上 explain 后执行：

没毛病，索引类型 range，between…and 走的就是这种范围索引，这种范围类型的索引结构开销是有点大的，大到一定程度就不走索引了，比如在性别字段上建索引！

补充：Mysql 时间Datetime 索引不生效问题

今天发现之前在使用日期索引时，通过explain发现一直不走日期索引，在网上查询了下，发现使用过程中要注意以下情况：

1、在查询数据条数约占总条数五分之一以下时能够使用到索引，但超过五分之一时，则使用全表扫描了。

2、查询条件有日期索引和其他条件的话，只有所有条件都有索引的情况下，才会走日期索引，

例如：

我建立了复合索引car_date_index: date_time,car_plate_no.

当我是用查询条件： WHERE car_plate_no=‘冀E8888’ and date_time<=‘2019-05-01 00:00:00’，如果想走car_date_index的索引，表中必须有car_plate_no字段的索引才能走，暂时没发现为啥会出现此问题，有知道可以分享下哦

总结

到此这篇关于mysql中datetime字段建立索引并比较大小的文章就介绍到这了,更多相关mysql datetime字段建立索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL时间字段究竟使用INT还是DateTime的说明

    今天解析DEDECMS时发现deder的MYSQL时间字段,都是用 `senddata` int(10) unsigned NOT NULL DEFAULT '0'; 随后又在网上找到这篇文章,看来如果时间字段有参与运算,用int更好,一来检索时不用在字段上转换运算,直接用于时间比较!二来如下所述效率也更高. 归根结底:用int来代替data类型,更高效. 环境: Windows XP PHP Version 5.2.9 MySQL Server 5.1 第一步.创建一个表date_test(非

  • MySQL中datetime时间字段的四舍五入操作

    MySQL中datetime时间字段的四舍五入操作

    目录 前言 1.背景 2.模拟测试 3.结论 总结 前言 如果不是踩到坑,我估计到现在还不知道时间字段会四舍五入. 1.背景 通过 Java 代码获取当日最大时间,然后存入数据库,数据库表字段格式 datetime 保留 0 位. now.with(LocalTime.MAX) 小小的一行代码,获取今天的最大日期. 到数据库一看,好家伙,竟然存了第二天的时间. 看着样子是四舍五入了! 2.模拟测试 执行之后,看一下日志: 使用的是 2021-09-28T23:59:59.999999999,但是

  • MYSQL 数据库时间字段 INT,TIMESTAMP,DATETIME 性能效率的比较介绍

    目录 一.准备工作 1.1 建表 1.2 插入100万条测试数据 二.MyISAM引擎 2.1 MyISAM 引擎无索引下的 dint/dtimestamp/d_datetime 2.1.1 int 类型是否调用 UNIX_TIMESTAMP 优化对比 2.1.2 timestamp 类型是否调用 UNIX_TIMESTAMP 优化对比 2.1.3 datetime 类型是否调用 UNIX_TIMESTAMP 优化对比 2.2 MyISAM 引擎有索引下的 dint/dtimestamp/d_d

  • MySql用DATE_FORMAT截取DateTime字段的日期值

    用 DATE_FORMAT 来格式化日期字段 SELECT DATE_FORMAT(crt_time,'%Y-%m-%d') FROM ad_n_advertise_t

  • mysql中datetime字段建立索引并比较大小详解

    目录 1.问题背景 2.优化过程 补充:Mysql 时间Datetime 索引不生效问题 总结 1.问题背景 最近测试库查询一个表的数据,需要用到唯一的一个日期类型字段作为 where 的子查询(查询当天的数据),就正常写了个这样的 SQL,具体的表名我就不写了: # create_time 是 datetime 类型 select * from ${tablename} where date(create_time)='20220919' limit 20; 其中字段的值样本如下: 我知道我写

  • MySQL中datetime和timestamp的区别及使用详解

    一.MySQL中如何表示当前时间? 其实,表达方式还是蛮多的,汇总如下: CURRENT_TIMESTAMP CURRENT_TIMESTAMP() NOW() LOCALTIME LOCALTIME() LOCALTIMESTAMP LOCALTIMESTAMP() 二.关于TIMESTAMP和DATETIME的比较 一个完整的日期格式如下:YYYY-MM-DD HH:MM:SS[.fraction],它可分为两部分:date部分和time部分,其中,date部分对应格式中的"YYYY-MM-

  • MySQL中增删改查操作与常见陷阱详解

    MySQL中增删改查操作与常见陷阱详解

    目录 本文导读 一.MySQL的增删改查 1.insert语句 2.delete语句 3.update语句原理 4.select 二.15种MySQL数据操作语句 1.REPLACE语句 2.CALL语句 3.TABLE语句 4.WITH语句 三.MySQL查询陷阱 总结 本文导读 本文作为MySQL系列第二篇文章,详细讲解了MySQL的增删改查的语句.语义和一些我们经常在开发工作中暴露的问题,MySQL的增删改查又叫数据操作语句,本文有讲些了一些常用的数据操作语句,select语句后续将作为一

  • 浅谈MySQL中授权(grant)和撤销授权(revoke)用法详解

    MySQL 赋予用户权限命令的简单格式可概括为: grant 权限 on 数据库对象 to 用户 一.grant 普通数据用户,查询.插入.更新.删除 数据库中所有表数据的权利 grant select on testdb.* to common_user@'%' grant insert on testdb.* to common_user@'%' grant update on testdb.* to common_user@'%' grant delete on testdb.* to c

  • MySQL中int(10)和int(11)的区别详解

    目录 一.背景 二.MySQL整数类型 总结: 一.背景 在创建数据库表的时候,我们经常会用到int(x)来定义一个字段的类型,一直误以为这里的x表示存储数字的长度. 其实大错特错,这里的 x 指的是 最大显示宽度(最大有效显示宽度是255),且显示宽度与存储大小或类型包含的值的范围无关. 二.MySQL整数类型 类型 字节 取值范围 显示宽度 tinyint 1 -128 ~ 127 4 smallint 2 -32768 ~ 32767 6 mediumint 3 -8388608 ~ 83

  • MySQL中Decimal类型和Float Double的区别(详解)

    MySQL中存在float,double等非标准数据类型,也有decimal这种标准数据类型. 其区别在于,float,double等非标准类型,在DB中保存的是近似值,而Decimal则以字符串的形式保存数值. float,double类型是可以存浮点数(即小数类型),但是float有个坏处,当你给定的数据是整数的时候,那么它就以整数给你处理.这样我们在存取货币值的时候自然遇到问题,我的default值为:0.00而实际存储是0,同样我存取货币为12.00,实际存储是12. 幸好mysql提供

  • MySql中的IFNULL、NULLIF和ISNULL用法详解

    今天用到了MySql里的isnull才发现他和MSSQL里的还是有点区别,现在简单总结一下: mysql中isnull,ifnull,nullif的用法如下: isnull(expr) 的用法: 如expr 为null,那么isnull() 的返回值为 1,否则返回值为 0. mysql> select isnull(1+1); -> 0 mysql> select isnull(1/0); -> 1 使用= 的null 值对比通常是错误的. isnull() 函数同 is nul

  • MySQL中参数sql_safe_updates在生产环境的使用详解

    前言 在应用 BUG或者 DBA误操作的情况下,会发生对全表进行更新:update delete 的情况.MySQL提供 sql_safe_updates 来限制次操作. set sql_safe_updates = 1; 设置之后,会限制update delete 中不带 where 条件的SQL 执行,较严格.会对已有线上环境带来不利影响.对新系统.应用做严格审核,可以确保不会发生全表更新的问题. CREATE TABLE working.test01 (id INT NOT NULL AU

  • MySQL中几种插入和批量语句实例详解

    目录 前言 1.insert ignore into 2.on duplicate key update 3.replace into 4.insert if not exists 5.批量插入数据 6.批量更新 总结 前言 最常见的方式就是为字段设置主键或唯一索引,当插入重复数据时,抛出错误,程序终止,但这会给后续处理带来麻烦,因此需要对插入语句做特殊处理,尽量避开或忽略异常,下面我简单介绍一下,感兴趣的朋友可以尝试一下: 这里为了方便演示,我新建了一个user测试表,主要有id,userna

  • mysql中find_in_set()函数的使用及in()用法详解

    MySQL手册中find_in_set函数的语法解释: FIND_IN_SET(str,strlist) str 要查询的字符串 strlist 字段名 参数以","分隔 如 (1,2,6,8,10,22) 查询字段(strlist)中包含(str)的结果,返回结果为null或记录 假如字符串str在由N个子链组成的字符串列表strlist 中,则返回值的范围在 1 到 N 之间. 一个字符串列表就是一个由一些被 ',' 符号分开的子链组成的字符串.如果第一个参数是一个常数字符串,而第

随机推荐