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

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

2019-08-20 2038

版权

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

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介： 概述最近发现一个有趣的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 | |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+

总结

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

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

概述

测试数据

索引问题

date与datetime

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

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像