关于cast类型转换后无法使用索引的优化

最新推荐文章于 2023-04-20 19:27:58 发布
最新推荐文章于 2023-04-20 19:27:58 发布
阅读量2k 收藏 2
点赞数 2
分类专栏： MYSQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/miyatang/article/details/79622082
版权 
关于cast类型转换后无法使用索引的优化

因为表设计时是各开发人员建立的表，同是建立日期字段add_time,有int,还有varchar(50)几种数据类型，如下：
（字段有删减）

mys

ql> show create table credit_tender;
+--------------------------+------------------------
| Table                    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+--------------------------+------------------------
| credit_tender | CREATE TABLE `credit_tender` (
  `assign_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户名称',
  `credit_user_id` int(11) DEFAULT NULL COMMENT '出让人id',
  `status` int(2) DEFAULT '0' COMMENT '状态',
  `add_time` varchar(50) DEFAULT NULL COMMENT '添加时间',
  `addip` varchar(50) DEFAULT NULL COMMENT 'ip',
  `client` int(11) DEFAULT NULL COMMENT '客户端',
  PRIMARY KEY (`assign_id`),
  KEY `user_id` (`user_id`),
  KEY `idx_add_time` (`add_time`)
) ENGINE=InnoDB AUTO_INCREMENT=20152 DEFAULT CHARSET=utf8 COMMENT='债权投资表' |

可以看到字段
add_time varchar(50) DEFAULT NULL COMMENT ‘添加时间’,
有索引：
KEY idx_add_time (add_time)

但现在的查询，所有都是使用了以下查询

SELECT * FROM borrow_tender t
WHERE   FROM_UNIXTIME(t.addtime,'%Y-%m-%d %H:%i:%s') >= '2018-03-19 00:00:00'
AND FROM_UNIXTIME(t.addtime,'%Y-%m-%d %H:%i:%s') <= '2018-03-19 23:59:59'

查看执行计划如下：
可以看到，有索引，但并没有走索引，还是全表搜索（type:ALL），

mysql> explain SELECT * FROM borrow_tender t
    WHERE   FROM_UNIXTIME(t.addtime,'%Y-%m-%d %H:%i:%s') >= '2018-03-19 00:00:00'
    AND FROM_UNIXTIME(t.addtime,'%Y-%m-%d %H:%i:%s') <= '2018-03-19 23:59:59';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | tender | ALL  | NULL          | NULL | NULL    | NULL | 664731 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set

mysql>

还看到这个表有以下查询条件的，相对于上面的查询，稍有思考，知道转换成相同数据类型进行对比查询：

mysql> explain 
SELECT  user_id,status,add_time
from credit_tender t 
WHERE  t.add_time  >= cast(unix_timestamp('2018-03-19 00:00:00') as CHAR)
AND  t.add_time <= cast(unix_timestamp('2018-03-19 23:59:59')  as CHAR);
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | hct   | ALL  | NULL          | NULL | NULL    | NULL | 14287 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set

但我们看到，还是没走索引，为什么？
cast(unix_timestamp(‘2018-03-19 00:00:00’) as CHAR) 已转换成字串了呀，为什么。
这里是因为char 是定长，而我们的字段类型为 varchar()
所以，这里只要使用以下函数即可
cast(unix_timestamp(‘2018-03-19 00:00:00’) as NCHAR)

mysql> explain 
SELECT  user_id,status,add_time
from credit_tender hct 
WHERE  hct.add_time  >= cast(unix_timestamp('2018-03-19 00:00:00') as NCHAR)
AND  hct.add_time <= cast(unix_timestamp('2018-03-19 23:59:59')  as NCHAR);
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | hct   | range | idx_add_time  | idx_add_time | 153     | NULL |   45 | Using index condition |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set

或者使用 CONCAT 函数，隐性转换成字串的方法

mysql> explain 
SELECT  user_id,status,add_time
from credit_tender hct 
WHERE  hct.add_time  >= CONCAT(unix_timestamp('2018-03-19 00:00:00'),'')
AND  hct.add_time <= CONCAT(unix_timestamp('2018-03-19 23:59:59')  ,'')
;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | hct   | range | idx_add_time  | idx_add_time | 153     | NULL |   45 | Using index condition |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set

mysql>
边城cn
关注
专栏目录
千呼万唤始出来，MySQL 8.0索引三剑客之函数索引
真水无香，大象无形，做本分的自己，记录技术点滴
04-10 8163
独孤九剑，重剑无锋，大巧不工，通晓剑意，无所施而不可。三剑客之首，函数索引。 好消息是，MySQL 终于在8.0版本引入了这一特性。真的是，千呼万唤始出来，不过好歹还是来了。 普通索引是对列值或列的前缀值进行索引，而MySQL 8.0.13之后支持函数索引，函数索引是对表中的列执行表达式计算后的结构进行索引，而不是对列或列前缀值。使用函数索引可以对未直接存储在表中的数据进行索引。 函数索引为应用程序提供了极大的便利和性能提升。
MySQL-隐式类型转换导致索引失效以及可能的查询数据异常
wuxiaopengnihao1的博客
10-26 697
本文章向大家介绍MySQL-隐式类型转换导致索引失效以及可能的查询数据异常，主要内容包括前言、数据准备、SQL 测试、隐式转换、分析和总结、参考阅读、附录、基本概念、基础应用、原理机制和需要注意的事项等，并结合实例形式分析了其使用技巧，希望通过本文能帮助到大家理解应用这部分内容。
SQL中一些不经意隐式类型转换或者函数使用导致索引失效问题
SQL77的专栏
04-30 2162
--A.函数转换--使用FULL JOIN,以前碰到的环境:有时写视图时将两表合并使用FULL JOIN --示例:DECLARE @A TABLE(NAME VARCHAR(10) NOT NULL PRIMARY KEY,ID INT) INSERT @A SELECT  CASE WHEN NUMBER%2=0 THEN 'A'+LTRIM(NUMBER) ELSE 'B'+LTRIM(NUMBER) END ,  NUMBER FROM  MASTER..SPT_VALUES WHERE TYPE=
Mysql 5.6 “隐式转换”导致的索引失效和数据不准确
weixin_40413961的博客
12-06 2470
背景 在一次进行SQl查询时，我试着对where条件中vachar类型的字段去掉单引号查询，这个时候发现这条本应该很快的语句竟然很慢。这个varchar字段有一个复合索引。其中的总条数有58989，甚至不加单引号查出来的数据不是我们想要的数据。 使用的是mysql 5.6版本，innoDB引擎 实际情况如下 下面我们来看一下执行的结果 在上面的描述中我们还得注意就是，你的where条件的字符串不加单引号必须是全数字。不然就会报错 还有可能查出来的数据不是我们想要的数据。如下图 分析
mysql as用法索引失效_Mysql索引失效情况
weixin_39751391的博客
01-27 313
mysql索引失效有以下几种情况：1、查询条件中有or (普通索引)2、like查询是以'%'开头但是如果想让以‘%’开头仍然使用索引，则需要使用覆盖索引，即只查询带索引字段的列：EXPLAIN SELECT name FROM `user` WHERE name like '%张三'3.对查询的列上有运算或者函数的explain SELECT name,age,address FROM ...
【MySQL】索引不会被使用的7种情况
十一月廿七风雨大作
10-26 4159
子句中，如果索引列所对应的第一个字符由通配符开始，索引将不被采用，然而当通配符出现在字符串其他位置时，优化器就能利用索引。如果对索引字段进行函数、算数运算或其他表达式等操作，那么 MySQL也个便用索引。是一致的,所以，字符类型的字段值应该加上引号。条件中，字段类型为字符串，而其值为数值，那么。对于多列索引，若没有使用前导列，则。估计使用全表扫描要比使用索引快，那么。条件中的所有列都是索引列，否则。[5] 通配符是第一个字符。不会使用索引，这个规则和。[1] 隐式类型转换。[1] 隐式类型转换。
SQLServer性能优化–间接实现函数索引或者Hash索引
12-16
在SQL Server中，虽然没有直接提供函数索引或哈希索引的功能，但可以通过一些技巧间接实现类似的效果，以优化查询性能。以下两种方法分别针对不同的场景提供了解决方案。 1. **计算列上建立索引，实现“函数索引”...
为什么 关联查询 字段类型不一致使用转换函数应用索引
最新发布
09-10
其次，如果转换后的字段数据类型仍然与其他字段类型不匹配，那么仍然无法利用索引进行优化查询。 综上所述，关联查询中字段类型不一致时，可以使用转换函数进行类型转换，以便利用索引来提高查询效率。但需要注意...
MySQL索引失效之隐式转换
虚幻私塾
01-09 787
Python微信订餐小程序课程视频 https://edu.csdn.net/course/detail/36074 Python实战量化交易理财系统 https://edu.csdn.net/course/detail/35475 常见索引失效： 条件索引字段"不干净"：函数操作、运算操作 隐式类型转换：字符串转数值；其他类型转换 隐式字符编码转换：按字符编码数据长度大的方向转换，避免数据截取 一、常见索引失效场景 root@test 10:50 > show create ta
tensorflow 实现数据类型转换
12-20
理解并正确使用这些数据类型转换函数对于优化TensorFlow模型的性能和内存管理至关重要。在处理大规模数据集或进行高精度计算时，选择正确的数据类型能够显著影响模型的运行速度和资源消耗。同时，了解何时以及如何...
索引失效的情况及解决(超详细)
热门推荐
sy_white的博客
12-23 8万+
mysql索引优化
[索引] 索引失效的几种情况
GSON的博客
04-18 4482
一、索引失效的几种情况 建立员工记录表staffs(id,name,age,pos,add_time) 给表中name,age,pos字段添加索引(注意三个字段的顺序) alter table staff add index idx_staffs_nameAgePos(name,age,pos) ①最佳左前缀法则 指的是查询从索引的最左前列开始并且不跳过索引中的列 （带头大哥不能死，中间兄弟不能断) （火车头带着车身跑，不能没有火车头，中间也不能缺少一段火车身） 例： 第一种情况，nam
【SQL】索引失效的11种情况
null
11-24 6973
数据库调优的大致方向：sql查询优化技术有很多，大体分为物理查询优化和逻辑查询优化:数据准备： 索引失效案例 【1】. 全值匹配 【2】. 最佳左前缀法则 在定义表时，让主键auto_increment，否则，插入一条数据时可能会移动大量数据。 如，往 1 5 8 10 15 … 100 中插9，会放在8 10 中间，因为索引默认升序排列。那么10往后的数据都要挪动，页不够时又要放到下一页，每插一条数据都这样挪一次，开销很大 我们自定义的主键列id 拥有AUTO_INCREMENT 属性，在插入记录时存
零碎知识——sql相关
TSzero的博客
03-11 5595
1. in和exists的区别2. count()3. 常用日期函数4. 常用字符串函数5. 常用计算和统计函数6. 其它函数7. 数据倾斜？8. HIVE？9. MySQL？10. 列、行互转11. Union和Union All的区别12. with rollup的用法
mysql字段隐式转换导致索引失效
mameng1988的博客
04-20 926
数据库中int类型的字段，查询的参数值可以是整型或者是字符串类型的；但是字符串类型的数据的字段，查询的参数值必须是字符串，否则会进行全表扫描，不走索引。
SQL语句时间和时间戳的转换
qq_44540071的博客
11-15 1万+
SQL语句时间和时间戳的转换
hive 时间戳的转换方法
Yuli_li的专栏
10-29 2万+
hive 时间戳的转换方法 由于频繁使用，没有一个博文是完整的，所以整理归纳了一下，遇到了sqlServer的不同语法就也写下来了； 时间戳转成日期 hive : select distinct from_unixtime(1441565203,‘yyyy/MM/dd HH:mm:ss’) from test_date; sqlSever: CONVERT ( VARCHAR ( 10 ), DATEADD ...
MySQL 索引优化及失效场景
若明天不见
07-28 616
本文主要介绍MySQL索引的失效场景及索引优化。失效场景包括左侧模糊查询、OR操作、联合索引不满足最左前缀原则、索引列参与运算、不等于比较、NULL值、内置函数、类型隐式转换等。还介绍了MySQL离散度及MR2、ICP优化...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值