AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2019-08-20 2123
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 概述 最近发现一个有趣的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 | |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+

总结

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

文章标签：
云数据库 RDS MySQL 版
索引
SQL
关系型数据库
MySQL
关键词：
云数据库 RDS MySQL 版字段类型
云数据库 RDS MySQL 版索引
云数据库 RDS MySQL 版字段类型索引
云数据库 RDS MySQL 版索引失效
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
云平_Stephen
目录
相关文章
最好zzz
|
27天前
|
存储 关系型数据库 MySQL
MySQL 字段类型探究：深入理解 Varchar(50) 与 Varchar(500)
在MySQL数据库中，`VARCHAR`类型是一种常用的字符串存储类型，它允许定义一个可变长度的字符串。然而，`VARCHAR(50)`和`VARCHAR(500)`之间的差异不仅仅是长度的不同，它们在存储和性能方面也有显著的区别。本文将深入探讨这两种字段类型的区别，以及它们在实际应用中的选择。
最好zzz
80 3
德国都芳最好
|
22天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中，需要根据具体的业务需求和查询模式，综合运用索引策略和查询性能调优方法，不断地测试和优化，以提高MySQL数据库的查询性能。
德国都芳最好
58 9
技术自由圈/原疯狂创客圈
|
2月前
|
存储 关系型数据库 MySQL
阿里面试：为什么要索引？什么是MySQL索引？底层结构是什么？
尼恩是一位资深架构师，他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构，主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型，如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树，它能够有效支持范围查询和顺序遍历，同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点，并提供了多个面试题及其解答，帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
技术自由圈/原疯狂创客圈
95 6
潇洒洒
|
27天前
|
存储 关系型数据库 MySQL
MySQL 字段类型深度解析：VARCHAR(50) 与 VARCHAR(500) 的差异
在MySQL数据库中，`VARCHAR`类型是一种非常灵活的字符串存储类型，它允许存储可变长度的字符串。然而，`VARCHAR(50)`和`VARCHAR(500)`之间的差异不仅仅是长度的不同，它们在存储效率、性能和使用场景上也有所不同。本文将深入探讨这两种字段类型的区别及其对数据库设计的影响。
潇洒洒
39 2
土木林森
|
1月前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型，以及如何创建和维护索引，结合实战案例分析查询执行计划，帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
土木林森
192 1
跃@sir
|
2月前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理？
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理？
跃@sir
79 1
土木林森
|
1月前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件，其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引（如B-Tree、复合索引）和优化查询语句（如使用EXPLAIN、优化分页查询），可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志，持续优化性能。
土木林森
78 0
蓝易云
|
2月前
|
监控 关系型数据库 MySQL
mysql8索引优化
综上所述，深入理解和有效实施这些索引优化策略，是解锁MySQL 8.0数据库高性能查询的关键。
蓝易云
51 0
Apache_Tomcat_Nignx
|
6天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因，包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题，并提供了详细的解决步骤，如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等，确保导入过程顺利。
Apache_Tomcat_Nignx
30 3
路卿老师
|
26天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法，包括安装并连接MySQL服务器、选择数据库、执行SQL语句（如插入、更新、删除和查询），以及将结果集返回到数组。通过具体示例代码，详细介绍了每一步的操作流程，帮助读者快速入门PHP与MySQL的交互。
路卿老师
34 1

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    PostgreSQL SQL HINT的使用
  • 3
    mysql互为主从的环境，更新一条语句同时提交，为什么会出现数据不一致？
  • 4
    更改mysql表引擎(shell)
  • 5
    【MySQL数据库开发之三】MySQL 获得数据库和表操作！
  • 6
    MySQL各种日期类型与整型（转）
  • 7
    Mysql数据库备份、还原、管理工具--MysqlDumper
  • 8
    logstash-input-jdbc实现mysql 与elasticsearch实时同步深入详解
  • 9
    mysql 协议的server状态标识
  • 10
    破解并重置mysql密码
  • 1
    【MySQL系列笔记】分库分表
    196
  • 2
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    157
  • 3
    服务器Linux系统配置mysql数据库主从自动备份
    212
  • 4
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    417
  • 5
    golang使用gorm操作mysql3，数据查询
    96
  • 6
    golang使用gorm操作mysql2
    57
  • 7
    golang使用gorm操作mysql1
    106
  • 8
    Hbase与MySQL对比，区别是什么？
    291
  • 9
    解决 mysql8.0 ERROR 1045 (28000): Access denied for user ‘ODBC‘@‘localhost‘ (using password: NO)用户访问拒绝
    2297
  • 10
    navicat过期了，直接用idea连接mysql
    64

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 使用SQL语句管理索引
  • 云端MySQL的安装及应用
  • SQL进阶之约束、索引

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月