为什么Mysql 数据库表中有索引还是查询慢

目录
  • 前言：
  • 1、字段类型不匹配导致的索引失效
  • 2、被索引字段使用了表达式计算
  • 3、被索引字段使用了内置函数
  • 4、like 使用了 %X 模糊匹配
  • 5、索引字段不是联合索引字段的最左字段
  • 6、or 分割的条件
  • 7、in、not in 可能会导致索引失效
  • 总结

前言：

问题分析:

在进行数据库查询的时候，我们都知道索引可以加快数据查询的效率。但是在实际的业务场景下，经常会遇到即使在表中增加了索引，但是同样还是会出现数据查询慢的问题。这就需要具体分析数据查询慢的具体原因到底是什么了。

首先需要进行确认的就是 SQL 语句中对应的条件查询中字段有没有建立索引。虽然说表中已经有索引，但是不一定 SQL 语句中的查询字段有建立索引，所以第一步应该进行 SQL 中的字段索引确认。如果没有建立对应的索引可以先尝试下建立索引再进行查询。如果已经有了索引，查询的字段也是索引字段，那么就要考虑下是不是出现了索引失效的情况。下面我们再具体分析下，看看在哪些场景下会出现索引失效的情况。

索引失效场景：

在分析索引失效场景之前，我们必须要清楚索引结构的特点是什么。

我们再来看下 Mysql 数据库索引的结构特点：

本文以 user_info 这张表来作为分析的基础，在 user_info 这张表上，我们分别创建了 idx_name 以及 idx_phone 二级索引以及 idx_age_address 联合索引。

1、字段类型不匹配导致的索引失效

进行 SQL 数据查询的时候，where 条件字段类型与实际表中字段类型不匹配的时候，Mysql 会进行隐式的数据类型转换，而类型转换会使用到内置函数，导致在进行数据查询的时候并没有使用索引。我们可以使用 explain 命令查看 sql 语句。可以看的出来在 key 栏中，对应的值为 null，说明并没有使用索引进行查询。

但是如果在按照 phone_number 字段为字符串类型进行查询的时候，Mysql 没有进行隐式的类型转换，所以最终还是走了索引。

2、被索引字段使用了表达式计算

在 where 中条件使用了条件表达式的时候，数据表中的索引就失效了，实际是因为 Mysql 需要将索引字段取出来之后再进行表达式的条件判断，因而进行了全表扫描，导致索引失效。

3、被索引字段使用了内置函数

索引字段实际上是依赖于整个 B+索引树的遍历，而索引树的遍历又依赖于索引树底层叶子节点的有序性。索引保存的是索引列的原始值，如果经过函数计算，Mysql 的解释器无法判断计算后的索引在原来的索引树上是否可以被索引到，因此它就直接放弃使用索引查询了。

4、like 使用了 %X 模糊匹配

使用左模糊匹配以及左右模糊匹配都会导致索引失效，但是使用右模糊匹配，还是可以走索引查询的。

由于 B+树按照索引值进行排序的，实际是按照最左前缀进行比较，而使用了 %作为最左前缀，Mysql 无法判断其有序性，因此只能进行全表扫描查询。

5、索引字段不是联合索引字段的最左字段

如果数据库表中有联合索引的话，我们在 SQL 查询语句中使用的索引字段又不是联合索引的最左字段，那么就会导致索引失效。

实际上在 Mysql 中的索引检索是遵循最左匹配原则的，同时 B+索引树的叶子节点的有序性也是建立在最左匹配原则之上，而上述的 4、5 两种情况实际违反了最左匹配原则，因此 Mysql 执行器则无法使用对应的索引进行检查查询。

6、or 分割的条件

如果 or 左边的条件存在索引，而右边的条件没有索引，不走索引
因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

7、in、not in 可能会导致索引失效

这里需要说明的是使用 in 以及 not in 走不走索引，实际和 Mysql 的版本以及表中的数据量有关系，在 8.0 之后的版本是走索引的。

注：此处加了地址的索引。

总结

本文总结了几种索引失效的场景，希望在大家平时项目开发时遇到类似的问题可以有对应的问题排查方向。导致索引失效的场景归结起来实际就是在索引使用上面存在瑕疵最终导致了索引失效的情况，这就像我们小时候打拳皇 97 一样，遥感和按钮的组合如果姿势不对，就没办法放出我们希望的大招。总之需要一些经验的积累，同时在写完 SQL 的时候可以进行执行检查，避免在线上出现索引失效的问题。

到此这篇关于为什么Mysql 数据库表中有索引还是查询慢的文章就介绍到这了,更多相关Mysql 查询慢原理内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL数据库查询进阶之多表查询详解

    MySQL数据库查询进阶之多表查询详解

    目录 一.多表查询 1.引出 2.笛卡尔积 3. 笛卡尔积的解决方法 二.多表查询分类 1.等值连接和非等值连接 2.自连接和非自连接 3.内连接和外连接 4.UNION 4.自然连接 5.using连接 三.子查询 1.不相关子查询 2.相关子查询 四.聚合函数 1.聚合函数介绍 1.1 AVG和SUM函数 1.2 MIN和MAX函数 1.3 COUNT函数 2.group by 3.使用having进行分组后的筛选 五.where和having的对比 六.select的执行过程 1.关键字顺

  • MySQL数据库SELECT查询表达式解析

    数据的管理在很大一部分是在进行查找工作,而SELECT占据了很大的一部分 SELECT select_expr [,select_expr...] [ FROM table_reference WHERE [where_condition] [GROUP BY {col_name | position} [ASC| DESC],...] [HAVING where_condition] [ORDER BY {col_name | expr |position} [ASC| DESC],...]

  • Mysql数据库表中为什么有索引却没有提高查询速度

    Mysql数据库表中为什么有索引却没有提高查询速度

    背景 时间过得太快了,春节假期感觉光速般就结束了,转眼间就要继续搬砖上班了.紧接着很快就要进入金三银四的求职面试高峰期,程序猿小枫还没有找到令自己感到满意的工作.就算是在过年放假期间也在拼命的准备技术面试,这不他又梳理了下之前面试过程中面试官经常问到的关于数据库方面的一道面试题,我们来一起帮小枫看看有没有遗漏的地方吧. 面试题目--问题 面试官:看你的简历中有提到过曾经进行过索引优化的工作,那我就问问你,假设数据库表中有索引,但是进行SQL数据查询还是很慢,这种情况下应该怎么分析查询慢的原因?

  • Sql查询MySql数据库中的表名和描述表中字段(列)信息

    下面为大家介绍利用SQL查询语句获取Mysql数据库中表的表名,表描述.字段ID.字段名.数据类型.长度.精度.是否可以为null.默认值.是否自增.是否是主键.列描述 一.查询表信息(表名/表描述) SELECT table_name name,TABLE_COMMENT value FROM INFORMATION_SCHEMA.TABLES WHERE table_type='base table' and table_schema = '数据库名' order by table_name

  • 详解MySQL数据库--多表查询--内连接，外连接，子查询，相关子查询

    多表查询 使用单个select 语句从多个表格中取出相关的查询结果,多表连接通常是建立在有相互关系的父子表上; 1交叉连接 第一个表格的所有行 乘以 第二个表格中的所有行,也就是笛卡尔积 创建一个消费者与顾客的表格: 代码如下: -- create table customers( -- id int primary key auto_increment, -- name VARCHAR(20)not null, -- address VARCHAR(20)not NULL -- ); -- C

  • MySQL数据库高级查询和多表查询

    MySQL多表查询 添加练习表 -- 用户表(user) CREATE TABLE `user`( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户id(主键)', `username` VARCHAR(50) COMMENT '用户姓名', `age` CHAR(3) COMMENT '用户年龄' ); -- 订单表(orders) CREATE TABLE `orders`( `id` INT AUTO_INCREMENT PRIMARY KEY

  • 为什么Mysql 数据库表中有索引还是查询慢

    为什么Mysql 数据库表中有索引还是查询慢

    目录 前言: 1.字段类型不匹配导致的索引失效 2.被索引字段使用了表达式计算 3.被索引字段使用了内置函数 4.like 使用了 %X 模糊匹配 5.索引字段不是联合索引字段的最左字段 6.or 分割的条件 7.in.not in 可能会导致索引失效 总结 前言: 问题分析: 在进行数据库查询的时候,我们都知道索引可以加快数据查询的效率.但是在实际的业务场景下,经常会遇到即使在表中增加了索引,但是同样还是会出现数据查询慢的问题.这就需要具体分析数据查询慢的具体原因到底是什么了. 首先需要进行确

  • MySQL数据库优化之索引实现原理与用法分析

    MySQL数据库优化之索引实现原理与用法分析

    本文实例讲述了MySQL数据库优化之索引实现原理与用法.分享给大家供大家参考,具体如下: 索引 什么是索引 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记录数量越多,这个操作的代价就越高.如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置.如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍.

  • MySQL数据库表中的约束详解

    目录 MySQL表中的约束(constraint) 约束分类 非空约束 唯一性约束 复合约束 主键约束 自增列-AUTO_INCREMENT 外键约束FOREIGN KEY约束 CHECK约束 DEFAULT约束 MySQL表中的约束(constraint) 为了保证数据的完整性,(数据的精确性和可靠性) SQL规范以约束的方式对表数据进行额外的条件限制,可从以下四个方面进行考虑 实体完整性 域完整性 引用完整性 用户自定义完整性 约束?对表中字段的限制. 约束分类 约束作用字段的个数 单列约束

  • MySQL数据库表分区注意事项大全【推荐】

    表分区与数据库分区是不一样的那么碰到表分区使用时我们要注意一些什么事情呢,今天我们来看一篇关于MySQL数据库表分区注意事项的细节. 1.分区列索引约束 若表有primary key或unique key,则分区表的分区列必须包含在primary key或unique key列表里,这是为了确保主键的效率,否则同一主键区的东西一个在A分区,一个在B分区,显然会比较麻烦. 2.各分区类型条件 range 每个分区包含那些分区表达式的值位于一个给定的连续区间内的行.这些区间要连续且不能相互重叠 li

  • 使用canal监控mysql数据库实现elasticsearch索引实时更新

    使用canal监控mysql数据库实现elasticsearch索引实时更新

    业务场景 使用elasticsearch作为全文搜索引擎,对标题.内容等,实现智能搜索.输入提示.拼音搜索等 elasticsearch索引与数据库数据不一致,导致搜索到不应被搜到的结果,或者搜不到已有数据 索引相关业务,影响其他业务操作,如索引删除失败导致数据库删除失败 为了减少对现有业务的侵入,基于数据库层面,对信息表进行监控,但需要索引的字段变动时,更新索引 由于使用的是mysql数据库,故决定采用alibaba的canal中间件 主要是监控信息基表base,监控这一张表的数据变动,mq消

  • 使用canal监控mysql数据库实现elasticsearch索引实时更新问题

    使用canal监控mysql数据库实现elasticsearch索引实时更新问题

    目录 业务场景 安装 下载安装 数据库启用row binlog 使用 修改配置文件canal.properties 配置单个连接 配置多个连接 配置rabbitMQ 程序改动 canal源码 微服务消费mq 业务场景 使用elasticsearch作为全文搜索引擎,对标题.内容等,实现智能搜索.输入提示.拼音搜索等 elasticsearch索引与数据库数据不一致,导致搜索到不应被搜到的结果,或者搜不到已有数据 索引相关业务,影响其他业务操作,如索引删除失败导致数据库删除失败 为了减少对现有业务

  • Mysql 数据库结构及索引类型

    Mysql 数据库结构及索引类型

    目录 前言 数据库索引的数据结构 聚集索引 辅助索引 索引管理 联合索引 覆盖索引 总结 前言 数据库索引是mysql数据库中重要的组成部分,是数据库查询数据速度提升的关键,本文将介绍数据库索引的一些内容. 数据库索引的数据结构 在数据库中的索引方法中,有TREE和HASH两种方法,HASH是经常使用的,本文中主要介绍TREE的数据结构.B+Tree 的高度一般是2-4层,也就是说查找一条数据记录,最多使用 2-4 次 IO,当前一般的机械磁盘每秒至少可以做 100 次 IO, 2-4 词的 I

  • MySQL 回表,覆盖索引,索引下推

    MySQL 回表,覆盖索引,索引下推

    目录 回表 覆盖索引 索引下推 无索引下推: 查看索引下推的状态 有索引下推: 开启索引下推 回表 在研究mysql二级索引的时候,发现Mysql回表这个操作,往下研究了一下 字面意思,找到索引,回到表中找数据 解释一下就是: 先通过索引扫描出数据所在的行,再通过行主键ID 取出数据. 举个例子说明: SELECT * FROM INNODB_USER WHERE AGE = 18 AND USER_NAME LIKE '模糊查%'; 假如age和user_name两个字段是个联合索引,我们通过

  • MySQL单表多关键字模糊查询的实现方法

    在最近的一个项目需要实现在MySQL单表多关键字模糊查询,但这数个关键字并不一定都存在于某个字段.例如现有table表,其中有title,tag,description三个字段,分别记录一条资料的标题,标签和介绍.然后根据用户输入的查询请求,将输入的字串通过空格分割为多个关键字,再在这三个字段中查询包含这些关键字的记录. 可目前遇到的问题是,这些关键字是可能存在于三个字段中的任意一个或者多个,但又要求三个字段必须包含所有的关键词.如果分别对每个字段进行模糊匹配,是没法实现所需的要求,由此想到两种

随机推荐