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

最新推荐文章于 2024-10-14 09:30:00 发布
最新推荐文章于 2024-10-14 09:30:00 发布
阅读量2.3k 收藏 2
点赞数 1
分类专栏： MySQL 文章标签： msyql 索引失效
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43303221/article/details/99855709
版权

文章目录

概述

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

总结

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

云平_Stephen
关注
专栏目录
MySQL隐式转换，导致索引失效
m0_73311735的博客
09-08 2060
MySQL索引失效的情况有很多，下面主要介绍数据隐式转换导致索引失效。这一点在我们平常书写SQL语句的时候可能是最容易忽略的但是又是经常容易犯的错误。开门见山，下面我们看一下官方文档描述。
MySQL-隐式类型转换导致索引失效以及可能的查询数据异常
wuxiaopengnihao1的博客
10-26 700
本文章向大家介绍MySQL-隐式类型转换导致索引失效以及可能的查询数据异常，主要内容包括前言、数据准备、SQL 测试、隐式转换、分析和总结、参考阅读、附录、基本概念、基础应用、原理机制和需要注意的事项等，并结合实例形式分析了其使用技巧，希望通过本文能帮助到大家理解应用这部分内容。
索引失效原因总结
热门推荐
Colin Lau Oracle
02-28 5万+
今天一个同事突然问我索引为什么失效。说实在的，失效的原因有多种： 但是如果是同样的sql如果在之前能够使用到索引，那么现在使用不到索引，以下几种主要情况: 1. 随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表） 2. 统计信息失效      需要重新搜集统计信息 3. 索引本身失效      需要重建索引 下面是一些不会使用到索
MySQL中什么情况下类型转换会导致索引失效
最新发布
聂可以的博客
10-14 1160
MySQL中什么情况下类型转换会导致索引失效
关于mysql中由于类型转换导致索引无法使用的问题
敖尔其楞的专栏
01-23 1480
mysql> create index idx_create_time on baixyu(create_time); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from baixyu where create_time >’2017-01-
mysql 索引丢失--- 字段类型转换引发的索引失效
qq690452074的专栏
02-14 436
test_2表是bcd三个字段，varchar类型，是三个字段的联合索引，但第一个查询是走的ALL，是因为，字符串不加单引号，隐式类型转换 导致索引失效 函数使索引失效 在上面的例子中，我们发现这就是引起索引失效的主要原因了，字段类型发生了转换 字符串不加单引号（隐式类型转换；生产重罪）导致索引失效 不要在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效进而转向全表扫描！ ...
mysql表更改字段类型未更改sql导致索引失效
学无止境
04-22 436
mysql表更改字段类型未更改sql导致索引失效
mysql字段隐式转换导致索引失效
mameng1988的博客
04-20 930
数据库中int类型的字段，查询的参数值可以是整型或者是字符串类型的；但是字符串类型的数据的字段，查询的参数值必须是字符串，否则会进行全表扫描，不走索引。
Mysql 5.6 隐式转换导致的索引失效和数据不准确的问题
01-21
在一次进行SQl查询时，我试着对where条件中vachar类型的字段去掉单引号查询，这个时候发现这条本应该很快的语句竟然很慢。这个varchar字段有一个复合索引。其中的总条数有58989，甚至不加单引号查出来的数据不是...
MySQL索引失效之隐式转换
虚幻私塾
01-09 789
Python微信订餐小程序课程视频 https://edu.csdn.net/course/detail/36074 Python实战量化交易理财系统 https://edu.csdn.net/course/detail/35475 常见索引失效： 条件索引字段"不干净"：函数操作、运算操作 隐式类型转换：字符串转数值；其他类型转换 隐式字符编码转换：按字符编码数据长度大的方向转换，避免数据截取 一、常见索引失效场景 root@test 10:50 > show create ta
简单了解mysql存储字段类型查询效率
12-14
- 索引NULL列会占用额外空间，且在比较和计算时涉及NULL可能导致索引失效。 - 通常建议避免使用NULL，特别是在索引列上，可以考虑用默认值代替。 4. **时间日期类型**： - DATE用于存储1001年至9999年的日期，...
mysql group 索引失效_介绍mysql索引失效的情况
weixin_42410477的博客
02-17 1310
mysql视频教程栏目索引失效的情况。索引对于MySQL而言，是非常重要的篇章。索引知识点也巨多，要想掌握透彻，需要逐个知识点一一击破，今天来先来聊聊哪些情况下会导致索引失效。图片总结版相关免费学习推荐：mysql视频教程全值匹配(索引最佳)explain select * from user where name = 'zhangsan' and age = 20 and pos = 'cxy'...
mysql 类型转换 索引_经过字段类型转化后的查询不走索引
weixin_29733795的博客
02-07 1096
总结：索引字段上有发生int到varchar、varbinary的类型转换，不会使用到索引而如在索引字段上发生varchar向int的类型转化，仍然会使用到索引表结构如下：mysql> show create table tag_item_list\G*************************** 1. row ***************************Table: ta...
mybatis的sql语句导致索引失效，使得查询超时
weixin_30294295的博客
11-01 661
mybaitis书写sql需要特别注意where条件中的语句，否则将会导致索引失效，使得查询总是超时。如下语句会出现导致索引失效的情况： with test1 as (select count(C_FUNDACCO) val,'a' v from TINF_REQUEST a where a.C_FUNDCODE = #{cFundcode} and a.D_DAT...
MySQL索引失效的情况
smileLs的博客
08-12 1789
mysql的索引
MySQL数据库中的索引失效原因分析和解决
vipfanxu的博客
11-01 1796
我们可以通过避免在索引列上使用函数、使用合适的条件表达式、确保查询条件与索引列类型匹配、使用合适的索引、表结构优化以及使用索引提示等方法来解决索引失效的问题，进而提高数据库查询的性能。如果确实需要使用不等于操作符，可以考虑通过拆分查询，使用UNION操作符来减少不等于操作符的使用次数，提高性能。如果有必要，我们可以将函数操作提前，将结果存入一个新的列中，并在新列上建立索引，以提高查询效率。例如，索引列是字符串类型，但在查询时使用了数值类型，MySQL会放弃使用索引。在这种情况下，索引可能会被认为是无效的。
Mysql DATE_SUB索引问题是否生效?
qq_34445142的博客
11-23 1983
最近做个功能遇到一个问题,在一张大概有一千万数据的表中,查询一个月内的数据,有其他筛选条件. SQL从稍微复杂一点的聚合SQL(包括,Max,Group by,Sum)更改了一个简单的查询SQL.所有的聚合在代码中实现.但是查询结果还是比较慢,并且是在将where条件分片之后. 更改前的SQL就不放了.直接放更改后的: EXPLAIN SELECT a_cloumn AS aId FROM a_table WHERE update_time between DATE_SUB(CURDATE(), INTE
【学习】数据库：mysql查询条件有函数导致索引失效问题，建立函数索引
u010328311的博客
02-12 2002
mysql查询条件有函数导致索引失效问题，建立函数索引
ClickHouse 使用 formatDateTime() 函数 索引失效
aq_money的博客
12-26 1489
在clickhouse中，我们需要将常规的时间格式转换为特定业务场景或者特定格式的时间表示格式，那我们可以使用formatDateTime()函数来实现。在clickhouse中使用这个函数会造成索引失效，会查询全表，降低程序的一个性能。解析:函数根据给定的格式字符串来格式化时间。请注意：格式字符串必须是常量表达式，例如：单个结果列不能有多种格式字符串。语法:formatDateTime(Time, Format [, Timezone])返回值:根据指定格式返回的日期和时间。
mysql类型转换索引失效
07-27
对于MySQL类型转换索引失效的情况，一般是由于查询条件中的数据类型与索引字段的数据类型不匹配导致的。MySQL在比较不同数据类型时会进行隐式类型转换，但这可能导致索引无法使用。 例如，如果索引字段是整数类型，而查询条件中使用了字符串类型的值，那么MySQL会尝试将字符串转换为整数进行比较，但这样可能无法使用索引。 解决这个问题的方法有以下几种： 1. 使用正确的数据类型：确保查询条件中使用的数据类型与索引字段的数据类型匹配。这样可以避免隐式类型转换，使得索引能够有效使用。 2. 使用函数或表达式索引：在某些情况下，可以通过创建函数索引或表达式索引来解决类型转换导致的索引失效问题。例如，如果需要对字符串字段进行数字比较，可以创建一个函数索引将字符串转换为数字类型。 3. 重新设计查询语句：有时候可以通过重新设计查询语句来避免类型转换导致的索引失效。例如，可以将查询条件中的字符串值转换为对应的数字值，或者使用强制类型转换函数（如CAST函数）进行显式类型转换。 需要注意的是，索引失效可能还有其他原因，如查询语句的写法不合理、索引统计信息不准确等。在解决问题时，可以通过分析查询执行计划、查看索引使用情况等方式进行诊断和调优。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值