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

2021-10-08 05:20:54

概述

最近发现一个有趣的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 | |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+

总结

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

上一篇：PostgreSQL的设置开机自启动


下一篇：PostgreSQL外部数据插件：mysql_fdw