关于java:用MySQL-执行计划分析-DATEFORMAT-函数对索引的影响

前言

最近公司在代码评审时，在应用 DATE_FORMAT 函数的问题上有了点不同的观点。具体 DATE_FORMAT 对索引会不会产生影响？哪种状况下会产生影响呢？周末无事，通过 mysql 的执行打算测试一波。

应用 explain 剖析

执行打算就是展现 Mysql 如何执行一条 Sql 语句, 应用 EXPLAIN。输入包含 Sql 查问的程序、是否应用索引、以及应用的索引信息等内容, 展现如图

id : 示意查问中 select 操作表的程序, 按程序从大到顺次执行

select_type : 该示意抉择的类型, 可选值有: SIMPLE(简略的)

type : 该属性示意拜访类型, 有很多种拜访类型。最常见的其中包含以下几种: ALL(全表扫描), index(索引扫描),range(范畴扫描),ref (非惟一索引扫描),eq_ref(惟一索引扫描,),(const) 常数援用, 访问速度顺次由慢到快。其中 : range(范畴) 常见与 between and …, 大于 and 小于这种状况。提醒 : 慢 SQL 是否走索引, 走了什么索引, 也就能够通过该属性查看了。

table : 示意该语句查问的表

possible_keys : 顾名思义, 该属性给出了, 该查问语句, 可能走的索引,(如某些字段上索引的名字) 这里提供的只是参考, 而不是理论走的索引, 也就导致会有 possible_Keys 不为 null,key 为空的景象。

key : 显示 MySQL 理论应用的索引, 其中就包含主键索引 (PRIMARY), 或者自建索引的名字。

key_len : 示意索引所应用的字节数，

ref : 连贯匹配条件, 如果走主键索引的话, 该值为: const, 全表扫描的话, 为 null 值

rows : 扫描行数, 也就是说，须要扫描多少行, 能力获取指标行数, 个别状况下会大于返回行数。通常状况下,rows 越小, 效率越高, 也就有大部分 SQL 优化，都是在缩小这个值的大小。留神: 现实状况下扫描的行数与理论返回行数实践上是统一的, 但这种状况及其少, 如关联查问, 扫描的行数就会比返回行数大大增加 )

Extra：这个属性十分重要, 该属性中包含执行 SQL 时的真实情况信息, 如下面所属, 应用到的是”using where”，示意应用 where 筛选失去的值, 罕用的有:“Using temporary”: 应用长期表“using filesort”: 应用文件排序

表语句及数据插入 sql 如图：

如果有搭档须要验证，也附上 SQL，和下面图片内容统一：

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (`id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `birth_date` date NULL DEFAULT NULL,
  `is_delete` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `name`(`name`) USING BTREE,
  INDEX `age`(`age`) USING BTREE,
  INDEX `birth_date`(`birth_date`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '小明 1', 1, '2000-01-01', 0);
INSERT INTO `user` VALUES (2, '小明 2', 2, '2001-01-02', 0);
INSERT INTO `user` VALUES (3, '小明 3', 3, '2002-01-03', 0);
INSERT INTO `user` VALUES (4, '小明 4', 4, '2003-01-04', 0);
INSERT INTO `user` VALUES (5, '小明 5', 5, '2004-01-05', 0);
INSERT INTO `user` VALUES (6, '小明 6', 6, '2005-01-06', 0);
INSERT INTO `user` VALUES (7, '小明 7', 7, '2006-01-07', 0);
INSERT INTO `user` VALUES (8, '小明 8', 8, '2007-01-08', 0);
INSERT INTO `user` VALUES (9, '小明 9', 9, '2008-01-09', 0);
INSERT INTO `user` VALUES (10, '小明 1', 10, '2009-01-10', 0);
INSERT INTO `user` VALUES (11, '小明 11', 11, '2010-01-11', 0);
INSERT INTO `user` VALUES (12, '小明 12', 12, '2011-01-12', 0);
INSERT INTO `user` VALUES (13, '小明 13', 13, '2012-01-13', 0);
INSERT INTO `user` VALUES (14, '小明 14', 14, '2013-01-14', 0);
INSERT INTO `user` VALUES (15, '小明 15', 15, '2014-01-15', 0);
INSERT INTO `user` VALUES (16, '小明 16', 16, '2015-01-16', 0);
INSERT INTO `user` VALUES (17, '小明 17', 17, '2016-01-17', 0);
INSERT INTO `user` VALUES (18, '小明 18', 18, '2017-01-18', 0);
INSERT INTO `user` VALUES (19, '小明 19', 19, '2018-01-19', 0);
INSERT INTO `user` VALUES (20, '小明 20', 20, '2019-01-20', 0);
INSERT INTO `user` VALUES (21, '小明 21', 21, '2020-01-21', 0);

验证过程

留神：本文中采纳的数据库为 mysql：5.7.24。

1、不必 DATE_FORMAT 函数

第一种形式：

explain select * from user where birth_date <= ‘2009-10-10’;

如上图所示可能用到 key：birth_date。

第二种形式：

EXPLAIN SELECT * FROM USER WHERE birth_date <= ‘2009-10-10’ and birth_date >= ‘2009-10-10’;

如上图所示用到 key：birth_date。

2、应用 DATE_FORMAT 函数

第一种形式：

EXPLAIN
SELECT
 *
FROM
 USER
WHERE
 birth_date >= DATE_FORMAT('2019-10-10', '%Y-%m-%d');
  
EXPLAIN
SELECT
 *
FROM
 USER
WHERE
 birth_date >= DATE_FORMAT('2019-10-10', '%Y-%m-%d')
AND birth_date <= DATE_FORMAT('2020-12-10', '%Y-%m-%d');

第二种形式：

EXPLAIN
SELECT
 *
FROM
 USER
WHERE
 DATE_FORMAT(birth_date, '%Y-%m-%d') >= '2019-10-10';

EXPLAIN
SELECT
 *
FROM
 USER
WHERE
 DATE_FORMAT(birth_date, '%Y-%m-%d') >= '2019-10-10'
AND DATE_FORMAT(birth_date, '%Y-%m-%d') <= '2020-12-10';

以上两组 SQL 别离在字段和参数上加了 DATE_FORMAT 进行测试，后果如图，birth_date 的索引均未失效， 次要看 字段 key。

所以，在开发过程中，应防止应用该类函数，否则可能会导致索引生效，全表扫描。

总结

依据理论状况进行抉择 ：

①应用 DATE_FORMAT 中的第一种形式，在查问参数应用 DATE_FORMAT，而不是查问字段应用 。

EXPLAIN
SELECT
 *
FROM
 USER
WHERE
 birth_date >= DATE_FORMAT('2019-10-10', '%Y-%m-%d')
AND birth_date <= DATE_FORMAT('2020-12-10', '%Y-%m-%d');

②不必 DATE_FORMAT 中的第二种形式，依照范畴进行查问，如查问一

-- 举例：EXPLAIN SELECT
 *
FROM
 USER
WHERE
 create_time >= '2026-10-24 00:00:00'
AND create_time <= '2026-10-24 23:59:59';

写在最初

欢送大家关注我的公众号【 惊涛骇浪如码 】，海量 Java 相干文章，学习材料都会在外面更新，整顿的材料也会放在外面。

感觉写的还不错的就点个赞，加个关注呗！点关注，不迷路，继续更新！！！