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

背景

时间过得太快了，春节假期感觉光速般就结束了，转眼间就要继续搬砖上班了。紧接着很快就要进入金三银四的求职面试高峰期，程序猿小枫还没有找到令自己感到满意的工作。就算是在过年放假期间也在拼命的准备技术面试，这不他又梳理了下之前面试过程中面试官经常问到的关于数据库方面的一道面试题，我们来一起帮小枫看看有没有遗漏的地方吧。

面试题目——问题

面试官:看你的简历中有提到过曾经进行过索引优化的工作，那我就问问你，假设数据库表中有索引，但是进行SQL数据查询还是很慢，这种情况下应该怎么分析查询慢的原因？

分析

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

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

索引失效场景

在分析索引失效场景之前，我们必须要清楚索引结构的特点是什么。关于Mysql的数据库索引结构在之前的文章中已经进行了详细的分析，可以参见之前的文章。

Mysql数据库索引面试题(程序员基础技能)

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

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

CREATE TABLE IF NOT EXISTS `user_info` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) NOT NULL,
 `gender` int(2) NOT NULL,
 `age` int(10) NOT NULL,
 `phone_number` VARCHAR(20) NOT NULL,
 `address` VARCHAR(40) NOT NULL,
  PRIMARY KEY ( `id` ),
  KEY `idx_name`(`name`),
  KEY `idx_phone`(`phone_number`),
  KEY `idx_age_address`(`age`,`address`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 1、字段类型不匹配导致的索引失效

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

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

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

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

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

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

  • 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一样，遥感和按钮的组合如果姿势不对，就没办法放出我们希望的大招。小枫的求职面试之路还在继续，我们一起期待下次小枫还会遇到怎样的面试问题吧。

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

赞 (0)

相关推荐

  • mysql数据库之索引详细介绍

    mysql数据库之索引详细介绍

    目录 思维导图 简单理解 索引模型的演变 二叉查找树 自平衡二叉树 B树 B+树 聚集索引与二级索引 总结 如果你想深入了解为什么mysql可以快速的进行检索数据,那么你一定要来了解一下mysql的索引原理 思维导图 简单理解 你可以把索引理解为一本书的目录,我们可以通过索引快速的找到我们需要的数据,大概就像下面这个图,索引就像是右边的二叉树,每个节点指向具体的数据的物理地址,先通过二叉树找到数据的位置,然后再去物理磁盘中获取数据. 但是不同的二叉树的特性不同,我们还要选择合适的树来作为索引,所

  • MySQL数据库的事务和索引详解

    MySQL数据库的事务和索引详解

    目录 一.事务: 事务四大特性: 并发事务带来哪些问题?(隔离所导致的一些问题) 事务隔离级别有哪些? MySQL的默认隔离级别: 二.索引: 索引的作用: 索引的分类: 索引准则: 索引的数据结构: 总结 一.事务: 事务是逻辑上的一组操作,要么都成功,要么都失败! ---------------------------------- 1.SQL执行        A:1000元     -->转账200元        B:200元 2.SQL执行        A:800元       -

  • MySQL数据库索引的最左匹配原则

    MySQL数据库索引的最左匹配原则

    目录 一. 联合索引说明 二. 那ac是否能用到索引呢? 三. 思考 四. 最左匹配原则的成因 一. 联合索引说明 建立三个字段的联合索引 联合索引(a,b,c)相当于建立了索引:(a),(a,b),(a,b,c) 二. 那ac是否能用到索引呢? 先给出结论:a可以命中联合索引(a,b,c),c无法命中,所以ac组合无法命中联合索引. 1.建立abc联合索引(province,city,district) ac索引查询 SELECT * FROM user_address WHERE provi

  • MySQL 数据库 索引和事务

    MySQL 数据库 索引和事务

    目录 1. 索引 1.1 概念 1.2 作用 1.3 索引的原理 1.3.1 减少磁盘的访问次数是构建索引的核心思想 1.3.2 B+ 树适用实现索引的底层 1.4 适用场景 1.5 使用语句 1.5.1 查看索引 1.5.2 创建索引 1.5.3 删除索引 2. 事务 2.1 概念 2.2 为什么使用事务 2.3 四大属性 2.3.1 原子性 2.3.2 一致性 2.3.3 持久性 2.3.4 隔离性 2.4 使用方法 1. 索引 1.1 概念 索引是为了加速对表中数据行的检索而创建的一种分散

  • MySQL数据库索引以及失效场景详解

    MySQL数据库索引以及失效场景详解

    目录 1. MySQL索引概述 1.1 索引的概念 1.2 索引的特点 1.3 索引的分类 1.4 索引的使用场景 2. 索引失效场景 2.1 索引失效9种场景 2.2 索引失效场景总结 3. 索引失效验证 3.1 全值匹配 3.2 最佳左前缀 3.3 索引计算 3.4 索引范围:索引列上不能有范围查询 3.5 索引覆盖:尽量使用覆盖索引 3.6 不等: 使用不等于(!= 或者 <>)的时候 3.7 null:字段的is not null 与is null 3.8 like:like的前后模糊

  • MySQL数据库之索引详解

    MySQL数据库之索引详解

    目录 一.MySQL索引简介 二.MySQL五种类型索引详解 (一)普通索引 (二)唯一性索引 (三)主键索引 (四)复合索引 (五)全文索引 三.MySQL索引使用原则 总结 今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL索引相关内容. 一.MySQL索引简介 索引是MySQL数据库为了加快数据查询的速度,给表中的某一个或者是某几个列添加的一种"目录".MySQL的索引是一个特殊的文件,但是InnoDB类型引擎(关于MySQL的引擎我们会在今后的文章中进行讲解)的表的

  • MySQL数据库索引的弊端及合理使用

    MySQL数据库索引的弊端及合理使用

    目录 合理利用索引 1.普通索引的弊端 2.主键索引的陷阱 3.联合索引的矛与盾 4.前缀索引的短小精悍 5.唯一索引的快与慢 6.不要盲目加索引 7.索引失效那些事 索引优化 1.change buffer 2.索引下推 3.刷新邻接页 4.MRR 最后 一个好的索引对数据库系统尤其重要,索引可以说是数据库中的一个大心脏了,如果说一个数据库少了索引,那么数据库本身存在的意义就不大了,和普通的文件没什么两样.今天来说说MySQL索引,从细节和实际业务的角度看看在MySQL中B+树索引好处,以及我

  • Mysql数据库索引面试题(程序员基础技能)

    Mysql数据库索引面试题(程序员基础技能)

    引言 索引是Mysql的一块硬骨头,但是对于程序猿来说又是十分重要的基础技能.在平常的项目开发中,它是重要的SQL优化手段.在求职面试中,它是面试官常常用来考察求职者数据库性能优化方面的重要考量.因此透彻的掌握索引原理,并能够将其运用到数据库查询实战是每个程序猿必备的能力.本文将从索引原理.索引设计原则方面阐述Mysql索引.相信阅读完本文之后,在Mysql索引查询数据理解这块完全可以征服阿里面试官.准备好了吗?我们发车了. 索引原理 在进行索引设计以及优化之前,我们先深入理解下索引的原理.因为

  • MySQL数据库索引order by排序精讲

    MySQL数据库索引order by排序精讲

    排序这个词,我的第一感觉是几乎所有App都有排序的地方,淘宝商品有按照购买时间的排序.B站的评论有按照热度排序的... 对于MySQL,一说到排序,你第一时间想到的是什么?关键字order by?order by的字段最好有索引?叶子结点已经是顺序的?还是说尽量不要在MySQL内部排序? 事情的起因 现在假设有一张用户的朋友表: CREATE TABLE `user` ( `id` int(10) AUTO_INCREMENT, `user_id` int(10), `friend_addr`

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

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

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

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

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

  • Python实现将MySQL数据库表中的数据导出生成csv格式文件的方法

    本文实例讲述了Python实现将MySQL数据库表中的数据导出生成csv格式文件的方法.分享给大家供大家参考,具体如下: #!/usr/bin/env python # -*- coding:utf-8 -*- """ Purpose: 生成日汇总对账文件 Created: 2015/4/27 Modified:2015/5/1 @author: guoyJoe """ #导入模块 import MySQLdb import time impor

  • 从MySQL数据库表中取出随机数据的代码

    MySQL 如何从表中取出随机数据  以前在群里讨论过这个问题,比较的有意思.mysql的语法真好玩. 他们原来都想用PHP的实现随机,但取出多条好像要进行两次以上查询. 翻了手册,找到了下面这个语句,可以完成任务了 SELECT * FROM table_name ORDER BY rand() LIMIT 5; rand在手册里是这么说的: RAND()  RAND(N)  返回在范围0到1.0内的随机浮点值.如果一个整数参数N被指定,它被用作种子值.  mysql> select RAND

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

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

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

  • Mysql如何删除数据库表中的某一列

    目录 删除数据库表中的某一列 Mysql删除列,添加列的sql语句 删除数据库表中的某一列 删除某一字段的值 update table_name SET field_name = ''; 删除某一列 ALTER TABLE table_name DROP COLUMN field_name; Mysql删除列,添加列的sql语句 已有表actor,且包含列last_name -- 删除列, 以下两种方式都可以 alter table actor drop  column last_name; a

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

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

  • MySQL数据库表空间回收的解决

    目录 1. MySQL表空间回收 2. MySQL表空间设置 3. MySQL删除数据流程 4. MySQL数据页空洞问题 1. MySQL表空间回收 我们经常会发现一个问题,就是把表数据删除以后发现,数据文件大小并没有变化,这就是标题中所说的MySQL数据库表空间回收问题. 这里,我们还是针对MySQL中应用最广泛的InnoDB引擎展开讨论.一个InnoDB表包含两部分,即:表结构定义和数据.在MySQL 8.0版本以前,表结构是存在以.frm为后缀的文件里.而MySQL 8.0版本,则已经允

  • php使用PDO从数据库表中读取数据的实现方法(必看)

    创建了PDO对象后,就可以通过创建的对象进行数据检索了.查询数据我们可以使用PDO::query()方法,具体代码如下: try{ $pdo=new PDO('mysql:host=localhost;dbname=alpha','root','password'); }catch(PDOException $e){ echo "数据库连接失败,原因是:".$e->getMessage(); } //从数据库中选择数据,并将结果赋予一个变量,testtable为数据库表 $res

  • Mysql数据库表定期备份的实现详解

    Mysql数据库表定期备份的实现 0.背景 实际开发环境中,前端程序需要在给定时间段内,将数据更新/插入到MySQL指定的库表中.随着数据量的增多,基础库表基数的增长,每更新一次都会有5s左右的卡顿. 改进方案一:批量更新,累计数10条或者100条进行一次更新入库操作: 改进方案二:将当前日期前1个月之前的数据进行备份操作,并删除当前库表中1个月前的数据.经测定,该方法一定程度提高了访问效率.根因:基础表基数少,查询效率相对提高. 1.库表的定时备份总结 步骤1:备份Mysql指定数据库中的制定

随机推荐