案例12-数据库类型问题(mysql数据类型 索引失效)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 案例12-数据库类型问题(mysql数据类型 索引失效)

1、背景

现状:一个表内有217069条数据,另一个表内有76015条数据,查询起来发现速度特别的慢,推测是索引失效的问题,是不是因为咱们写的sql语句中的is_delete 传值是数字,但是表中的数据类型是字符类型的,所以导致了慢的问题呢?

2、思路&解决方案

复现上述问题:

首先我们摆出结论。where条件里,字符类型的列如过传递数值类型可以查出来数据,但是会索引失效。

1、

对应的索引:

is_delete 是字符型的,

但是下边查询是数值型的。

实际查询:

我们使用EXPLAIN关键字分析可以发现。type是 ALL,也就是全表扫描,并没有走索引。

我们换成字符型的值进行查询。

我们发现 type类型变成了ref,说明是走了我们新建的索引的。

结论:

数值类型的的转换,会使我们的索引失效,所以我们设计实体类的数值类型的时候,一定要和数据库的数据类型进行对应,避免索引失效的情况的出现。

3、扩展(索引失效情况):

索引失效

对索引使用左或者左右模糊匹配

使用 %xx 或者 %xx% 都会造成索引失效问题。但是 xx% 不会造成索引失效。

对索引使用函数

在where条件里,使用函数。比如 select * from user where length(name) = 6

这里进行的就是全表扫描,因为索引里存储的是列的原始值而不是计算后的值

对索引进行表达式计算

例如:select * from user where age + 1 = 10 。

这里也将会进行全表扫描,原因与对索引使用函数一样,都是因为索引内存储的是原始值而不是计算后的值。但是如果将上文的查询修改为 select * from user where age = 10 - 1 的话就没有问题,是会正常走age的索引的

对索引隐式类型转换

我们上文讲到,mysql有三种数据类型,分别是 数值型、日期型、字符型 ,能够隐式转换的就是 数值型和字符型了。

1.如果索引字段是字符型,但是条件查询时,传入的是整型的话,会出现索引失效问题。

2.如果索引是整型,但是条件查询的时候,传入的是字符型,不会出现索引失效问题。

这是因为mysq在遇到字符串和数字比较的时候,会默认将字符串转换为数值类型进行处理

联合索引非最左匹配

假设我们对字段 a,b ,c 建立了一个联合索引(a,b,c)。

如果我们的查询是以下几种,则会正常走联合索引:

where a = 1;

where a = 2 and b = 2;

where a = 2 and b = 2 and c = 2;

如果是如下几种则不会走联合索引:

where b = 2;

where c = 2;

where b = 2 and c = 3;

通过对比我们可以看出,索引的生效是遵循最左匹配原则的。在使用的时候,必须从最左侧的索引开始。

还有一种特殊的情况

where a = 2 and c = 2 ;

这种情况的话,在MySql 5.5的时候,前面的a会走索引,找到对应的主键值后,开始进行回表查询。Mysql 5.6 后,会进行索引下推,速度更快。索引还是会生效。

WHERE 子句中的 OR

如果where 子句里 or前的列是索引列,or后面的条件列不是索引列,索引就会失效。

例如:

select * from user where id = 2 or age = 10 ;

id是主键,默认是主键索引。 age如果没有添加为主键的话,都会进行全表扫描。 因为or表示两个条件满足一个就可以。所以只有一个列有索引的话,没有作用,还是会去全表扫描。

4、总结

防患于未然。胆大心细。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
2月前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
301 80
|
26天前
|
缓存 算法 关系型数据库
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
|
1月前
|
SQL 存储 关系型数据库
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
|
27天前
|
存储 关系型数据库 MySQL
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
|
1月前
|
存储 SQL 关系型数据库
服务器数据恢复—云服务器上mysql数据库数据恢复案例
某ECS网站服务器,linux操作系统+mysql数据库。mysql数据库采用innodb作为默认存储引擎。 在执行数据库版本更新测试时,操作人员误误将在本来应该在测试库执行的sql脚本在生产库上执行,导致生产库上部分表被truncate,还有部分表中少量数据被delete。
66 25
|
19天前
|
SQL 关系型数据库 MySQL
数据库数据恢复——MySQL简介和数据恢复案例
MySQL数据库数据恢复环境&故障: 本地服务器,安装的windows server操作系统。 操作系统上部署MySQL单实例,引擎类型为innodb,表空间类型为独立表空间。该MySQL数据库没有备份,未开启binlog。 人为误操作,在用Delete命令删除数据时未添加where子句进行筛选导致全表数据被删除,删除后未对该表进行任何操作。
|
1月前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
|
2月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
1月前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
167 42

热门文章

最新文章