MySQL字段类型转换引发的索引失效-阿里云开发者社区

开发者社区> 数据库> 正文

MySQL字段类型转换引发的索引失效

简介: 概述 最近发现一个有趣的SQL优化场景,分享一下 测试数据 表结构 CREATE TABLE `news_webpage` ( `id` int(11) NOT NULL AUTO_INCREMENT, `t1` varchar(100) DEFAULT NULL, `t2` text.

概述

最近发现一个有趣的SQL优化场景,分享一下

测试数据

表结构

CREATE TABLE `dateindex_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1` varchar(100) DEFAULT NULL,
  `t2` text,
  `t2_sub` text,
  `t3` varchar(50) DEFAULT NULL,
  `url` varchar(100) DEFAULT NULL,
  `t4` varchar(255) DEFAULT NULL,
  `t5` longblob,
  `website_name` varchar(50) DEFAULT NULL,
  `t6` varchar(50) DEFAULT NULL,
  `t7` varchar(50) DEFAULT NULL,
  `publish_time` datetime DEFAULT NULL,
  `webpage_uuid` varchar(50) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `webpage_uuid` (`webpage_uuid`) USING BTREE,
  KEY `website_name` (`website_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8317875 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

利用阿里云RDS中的DMS生成随机数据10万行如下

mysql>SELECT count(*) FROM dateindex_test;
+--------------------+
| count(*) |
+--------------------+
| 100000 |
+--------------------+
返回行数:[1],耗时:18 ms.

索引问题

问题SQL:

SELECT
 a.t1 AS t1,
 a.t2 AS t2,
 a.url AS url,
 a.t3 AS t3,
 a.publish_time AS publish_time 
FROM
 dateindex_test a 
WHERE
 date( a.publish_time ) >= date_sub(
  curdate(),
 INTERVAL 1 DAY )

没有索引之前,当然是全表扫。现在加上一个索引

ALTER TABLE dateindex_test ADD INDEX idx_publish_time(publish_time)

我的期望当然是直接走索引过滤是最好的,但是测试下却发现不行,虽然有索引了,但是没有走

mysql>explain SELECT
 a.t1 AS t1,
 a.t2 AS t2,
 a.url AS url,
 a.t3 AS t3,
 a.publish_time AS publish_time 
FROM
 dateindex_test a 
WHERE
 date( a.publish_time ) >= date_sub(
  curdate(),
 INTERVAL 1 DAY )
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| 1 | SIMPLE | a | | ALL | | | | | 99572 | 100 | Using where |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+

我第一时间怀疑的就是date() 函数导致的,将具体时间转换为日期,然后再试图使用具体时间的索引。看上去就感觉是有问题的。

所以就马上去掉 date函数再做测试,发现就使用了索引

mysql>explain SELECT
 a.t1 AS t1,
 a.t2 AS t2,
 a.url AS url,
 a.t3 AS t3,
 a.publish_time AS publish_time 
FROM
 dateindex_test a 
WHERE
 a.publish_time >= date_sub(
  curdate(),
 INTERVAL 1 DAY )
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+------------------+-------------------+---------------+----------------+--------------------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+------------------+-------------------+---------------+----------------+--------------------+-----------------------+
| 1 | SIMPLE | a | | range | idx_publish_time | idx_publish_time | 6 | | 17 | 100 | Using index condition |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+------------------+-------------------+---------------+----------------+--------------------+-----------------------+

到了这里,我所关心的就是:去掉date()函数以后,SQL语句的逻辑和原来的还是一样的吗。

我就再来测试一下:原SQL取出的是,昨天一整天+今天到目前为止的数据

我如果是使用原来的包含具体时间的数据来比较,能有一样的过滤效果吗?

就利用刚建的表小测试一下

第一次测试,发现数据并不一样

mysql>select count(*) from dateindex_test where date(publish_time) > '2019-08-15'
+--------------------+
| count(*) |
+--------------------+
| 64 |
+--------------------+
返回行数:[1],耗时:34 ms.
mysql>select count(*) from dateindex_test where publish_time > '2019-08-15'
+--------------------+
| count(*) |
+--------------------+
| 79 |
+--------------------+

再比较下原SQL,才发现上面不一样的原因所在,当 publish_time 为 2019-08-15 12:31:12,经过date()函数处理后的时间为:2019-08-15。此时与上述SQL的代入发现,第一条语句是不符合的,第二条,却是符合的(这里我猜测是因为在比较时发生隐性类型转换,将2019-08-15 转换为2019-08-19 00:00:00)

此时只要将原SQL中的> 改为'>='就能符合原SQL逻辑

如下:

mysql>select count(*) from dateindex_test where publish_time >= '2019-08-15'
+--------------------+
| count(*) |
+--------------------+
| 79 |
+--------------------+
mysql>select count(*) from dateindex_test where date(publish_time) >= '2019-08-15'
+--------------------+
| count(*) |
+--------------------+
| 79 |
+--------------------+


mysql>select count(*) from dateindex_test where date(publish_time) >= '2019-08-10'
+--------------------+
| count(*) |
+--------------------+
| 154 |
+--------------------+
mysql>select count(*) from dateindex_test where publish_time >= '2019-08-10'
+--------------------+
| count(*) |
+--------------------+
| 154 |
+--------------------+

date与datetime

由上引深再学习一下datetime与date字段类型的区别
1、显示格式的区别
Date显示格式:YYYY-MM-DD;DateTime显示格式:YYYY-MM-DD HH:mm:ss。

2、显示范围的区别
Date显示范围是1601-01-01 到 9999-01-01;DateTime显示范围是1601-01-01 00:00:00 到 9999-12-31 23:59:59。

3、应用场景的区别
当业务需求中只需要精确到天时,可以用Date这个时间格式,当业务需求中需要精确到秒时,可以用DateTime这个时间格式。

4、后台取值的区别
Date后台取值:@JSONField(format=”yyyy-MM-dd”);DateTime后台取值:@JSONField(format=”yyyy-MM-dd HH:mm:ss:SSS”)(这里只会精确到秒)。

字段类型转换导致的索引失效

其实这里最让我想要深入研究一下的主因就是为什么date函数会使该字段的索引无效

  • 函数使索引失效
    在上面的例子中,我们发现这就是引起索引失效的主要原因了,字段类型发生了转换
  • 字符串不加单引号(隐式类型转换;生产重罪)导致索引失效
    (下面测试,在表的t1列添加了索引)
mysql>explain select * from dateindex_test where t1 = 8
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| 1 | SIMPLE | dateindex_test | | ALL | idx_title | | | | 99572 | 10 | Using where |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+

mysql>explain select * from dateindex_test where t1 = '8'
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| 1 | SIMPLE | dateindex_test | | ref | idx_title | idx_title | 303 | const | 123 | 100 | |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+

总结

不要在索引列上做任何操作(计算、函数、(自动或手动)类型转换),会导致索引失效进而转向全表扫描!

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章