mysql as用法索引失效_MySQL字段类型转换引发的索引失效-阿里云开发者社区

最新推荐文章于 2022-05-12 10:10:43 发布
最新推荐文章于 2022-05-12 10:10:43 发布
阅读量155 收藏
点赞数
文章标签： mysql as用法索引失效
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39929635/article/details/113423637
版权

概述

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

+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+

总结

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

weixin_39929635
关注
mysql join 索引 无效_ORACLE MYSQL中join 字段类型不同索引失效的情况-阿里云开发者社区...
weixin_33941859的博客
01-27 240
ORACLE MYSQL中join 字段类型不同索引失效的情况重庆八怪2016-12-29780浏览量简介：关于JOIN使用不同类型的字段类型，数据库可能进行隐士转换，MYSQL ORACLE都是如此，下面使用一个列子来看看，脚本如下：mysql:drop table testjoin1;drop table testjoin2;create tab...关于JOIN使用不同类型的字段类型，数据库...
mysql 一条语句能用到几个索引_mysql索引合并:一条sql可以使用多个索引-阿里云开发者社区...
weixin_28950403的博客
02-03 2001
前言mysql的索引合并并不是什么新特性。早在mysql5.0版本就已经实现。之所以还写这篇博文，是因为好多人还一直保留着一条sql语句只能使用一个索引的错误观念。本文会通过一些示例来说明如何使用索引合并。什么是索引合并下面我们看下mysql文档中对索引合并的说明：The Index Merge method is used to retrieve rows with several range ...
mysql 索引丢失--- 字段类型转换引发的索引失效
qq690452074的专栏
02-14 419
test_2表是bcd三个字段，varchar类型，是三个字段的联合索引，但第一个查询是走的ALL，是因为，字符串不加单引号，隐式类型转换 导致索引失效 函数使索引失效 在上面的例子中，我们发现这就是引起索引失效的主要原因了，字段类型发生了转换 字符串不加单引号（隐式类型转换；生产重罪）导致索引失效 不要在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效进而转向全表扫描！ ...
mysql as用法索引失效_Mysql索引失效情况
weixin_39751391的博客
01-27 305
mysql索引失效有以下几种情况：1、查询条件中有or (普通索引)2、like查询是以'%'开头但是如果想让以‘%’开头仍然使用索引，则需要使用覆盖索引，即只查询带索引字段的列：EXPLAIN SELECT name FROM `user` WHERE name like '%张三'3.对查询的列上有运算或者函数的explain SELECT name,age,address FROM ...
as无效 mysql_mysql服务器无效怎么解决
weixin_39595487的博客
01-13 277
匿名用户1级2016-01-13 回答1.遇到这个问题先不要重新安装MySQL数据库，解决方法需要清理下WIndows的缓存目录就可以了。2.按“windows键+R”打开运行对话框，输入命令“cmd”，回车打开DOS窗口。3.输入“del c:windowstemp*.* /s /q”，等待文件删除完，MySQL自然会恢复正常。二，配置文件配置错误(mysql启动错误1067的解决 )问题一删除...
mysql in or索引失效_mysql 查询使用 in 索引失效的问题？
weixin_29658015的博客
01-18 142
表中的数据是这样的：mysql> select * from tbl_student;+----+----------+----------+---------------------+| id | name | class_id | create_time |+----+----------+----------+---------------------+| 1...
with as 固化的结果集是否可以使用原表的索引？
handsomer_everyday的博客
09-20 3446
结论with as 固化的结果集再添加条件再次使用的时候是可以使用原表的索引的。 测试步骤如下： 1.构建测试表 create table  dbmgr.TEST_APPEND  as select * from dba_objects ;  insert into dbmgr.TEST_APPEND   select * from dbmgr.TEST_APPEND   ;
mysql.zip_MYSQL_mysql vc_mysql-vc_vc mysql
09-24
在本压缩包文件"mysql.zip"中，包含了一个名为"mysql.cpp"的源代码文件，这显然与使用Visual C++（VC）编程环境连接并操作MySQL数据库有关。MySQL是一种广泛使用的开源关系型数据库管理系统（RDBMS），而VC是...
MySQL优化之索引选择性-并不是所有字段都适合加索引
最新发布
05-19
目标是帮助开发者理解，在MySQL性能调优中，除了常规的索引应用外，深入探讨索引选择性的重要性，以便更精准地识别并解决查询效率低下问题，同时提升系统整体性能和响应速度。此知识有助于面试时展现更全面的优化...
mysql 除号_MySql的运算符-阿里云开发者社区
weixin_42467445的博客
01-19 1893
数据库中的表结构确立后，表中的数据代表的意义就已经确定。而通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。例如，学生表中存在一个birth字段，这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。这就是MySQL的运算符，所以熟悉并掌握运算符的应用，对于操作MySQL数据库中的数据是非常有用的。下面就来熟悉一...
mysql索引语法as_Mysql中索引和约束的示例语句
weixin_42227736的博客
01-19 102
外键查询一个表的主键是哪些表的外键SELECTTABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAMEFROMINFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERETABLE_SCHEMA = 'mydbname'AND REFERENCED_TABLE_NAME...
mysql select 查询，使用列别名无效问题，用了as关键字不行
hery186的专栏
05-28 8854
加中括号也不行！ select s_username as username from userinfo 结果别名没生效，查询结果还是s_username，而不是username 解决办法： jdbc 5.1.x版本驱动，请在数据库连接url指定 useOldAliasMetadataBehavior=true 即可 如：jdbc:mysql://localhost:3306/...
as无效 mysql_as连接mysql异常
weixin_39603995的博客
12-22 335
/*这是我写的一个java类，照着网上敲的代码，代码内容也理解，用as连接mysql在学习当中，希望大神指点一下，感激不尽*/publicclassrenWu{publicstaticvoidmain(String[]args){Connectionconn=null;Stringdriver="com.mysql.jdbc.Driver";//MySQL驱动String...
MySQL字段类型转换引发的索引失效
LiZhen314的博客
05-14 1392
概述 最近发现一个有趣的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` varcha
mysql起别名会影响索引不_mysql – 允许在HAVING子句中使用别名的性能影响
weixin_33957089的博客
01-19 723
仅关注该特定查询,并在下面加载示例数据.这确实解决了其他一些查询,例如其他人提到的count(distinct …).HAVING中的别名似乎略微超出其替代品(取决于查询).这使用一个预先存在的表,其中大约有500万行通过这个answer快速创建,需要3到5分钟.结果结构：CREATE TABLE `ratings` (`id` int(11) NOT NULL AUTO_INCREMENT,`t...
MySQL 索引详解
漫长学习路
08-12 4786
什么是索引？1、索引索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。2、索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度索引的优点是可以提高检索数据的速度索引的缺点是创建和
坚持的力量：mysql学习第19天-为什么用了索引字段却不走索引
epitomizelu的专栏
05-12 1153
有三种情况，即使你用了索引字段，mysql在执行sql的时候也不会走sql。 一，索引字段用函数 比如，create_time上使用了索引，但如下sql却不会走索引： select * from t where month(t) = 7 原因是month(t)破坏了索引字段在索引树上的有序性，索引之所以快，就是因为索引在各层上是有序的。 二，隐式字段类型转换 如果索引字段my_id是varchar类型，如下sql不走索引： select * from t where t.my_id = 1 因为在mys
mysql DATE_FORMAT导致索引失效
RoyRaoHR的博客
11-18 7113
最近在优化一个统计的接口，在几十万的数据统计下，接口处理的响应时间达到了20s。看了下代码逻辑，发现其中主要有三个主要的统计方法，在优化了其中一个方法的统计逻辑后。接口的响应时间下降到了3s内。还是没有达到期望的响应时间（1s内）。看了下另外两个方法的SQL语句，其中一个查询时间达到了两秒多，如下： SELECT FLOW_TO AS flowTo, COUNT( DISTINCT RELATED_ID ) AS count FROM or_flow_schedule WHERE DATE_FO
记一次关于 Mysql 中 text 类型和索引问题引起的慢查询的定位及优化
热门推荐
zdplife的专栏
07-04 1万+
最近有用户反馈产品有些页面加载比较慢，刚好我在学习 Mysql 相关知识，所以先从 Mysql 慢查询日志开始定位： step1：通过慢查询日志定位具体 SQL 首先通过 SHOW VARIABLES like 查看当前 Mysql 服务器关于慢查询的具体配置信息： slow_query_log = ON # 慢查询日志处于开启状态，所以可以直接查询 slow_q...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值