程序员经验分享-hwhale

为什么用了索引，SQL查询还是慢？

2023-05-16

图片

原文链接cnblogs.com/jackyfei/p/12122767.html

经常有同学疑问，为什么有时候一个SQL语句使用了索引，为什么还是会进入到慢查询之中呢？今天我们就从这个问题开始来聊一聊索引和慢查询。

另外插入一个题外话，个人认为团队要合理的使用ORM。合理利用的是ORM在面向对象和写操作方面的优势，避免联合查询上可能产生的坑(当然如果你的Linq查询能力很强另当别论)，因为ORM屏蔽了太多的DB底层的知识内容，对程序员不是件好事，对性能有极致追求，但是ORM理解不透彻的团队更加要谨慎。

案例剖析

言归正传，为了实验，我创建了如下表：

CREATE TABLE `T`(
`id` int(11) NOT NULL,
`a` int(11) DEFAUT NULL,
PRIMARY KEY(`id`),
KEY `a`(`a`)
) ENGINE=InnoDB;

该表有三个字段，其中用id是主键索引，a是普通索引。

首先SQL判断一个语句是不是慢查询语句，用的是语句的执行时间。他把语句执行时间跟long_query_time这个系统参数作比较，如果语句执行时间比它还大，就会把这个语句记录到慢查询日志里面，这个参数的默认值是10秒。当然在生产上，我们不会设置这么大，一般会设置1秒，对于一些比较敏感的业务，可能会设置一个比1秒还小的值。

语句执行过程中有没有用到表的索引，可以通过explain一个语句的输出结果来看KEY的值不是NULL。

我们看下 explain select * from t;的KEY结果是NULL

图片

（图一）

explain select * from t where id=2;的KEY结果是PRIMARY，就是我们常说的使用了主键索引

图片

（图二）

explain select a from t;的KEY结果是a，表示使用了a这个索引。

图片

（图三）

虽然后两个查询的KEY都不是NULL，但是最后一个实际上扫描了整个索引树a。

假设这个表的数据量有100万行，图二的语句还是可以执行很快，但是图三就肯定很慢了。如果是更极端的情况，比如，这个数据库上CPU压力非常的高，那么可能第2个语句的执行时间也会超过long_query_time，会进入到慢查询日志里面。

所以我们可以得出一个结论：是否使用索引和是否进入慢查询之间并没有必然的联系。使用索引只是表示了一个SQL语句的执行过程，而是否进入到慢查询是由它的执行时间决定的，而这个执行时间，可能会受各种外部因素的影响。换句话来说，使用了索引你的语句可能依然会很慢。

全索引扫描的不足

那如果我们在更深层次的看这个问题，其实他还潜藏了一个问题需要澄清，就是什么叫做使用了索引。

我们都知道，InnoDB是索引组织表，所有的数据都是存储在索引树上面的。比如上面的表t，这个表包含了两个索引，一个主键索引和一个普通索引。在InnoDB里，数据是放在主键索引里的。如图所示：

图片

可以看到数据都放在主键索引上，如果从逻辑上说，所有的InnoDB表上的查询，都至少用了一个索引，所以现在我问你一个问题，如果你执行select from t where id>0，你觉得这个语句有用上索引吗？

图片

我们看上面这个语句的explain的输出结果显示的是PRIMARY。其实从数据上你是知道的，这个语句一定是做了全面扫描。但是优化器认为，这个语句的执行过程中，需要根据主键索引，定位到第1个满足ID>0的值，也算用到了索引。

所以即使explain的结果里写的KEY不是NULL，实际上也可能是全表扫描的，因此InnoDB里面只有一种情况叫做没有使用索引，那就是从主键索引的最左边的叶节点开始，向右扫描整个索引树。

也就是说，没有使用索引并不是一个准确的描述。

你可以用全表扫描来表示一个查询遍历了整个主键索引树；

也可以用全索引扫描，来说明像select a from t;这样的查询，他扫描了整个普通索引树；

而select * from t where id=2这样的语句，才是我们平时说的使用了索引。他表示的意思是，我们使用了索引的快速搜索功能，并且有效的减少了扫描行数。

索引的过滤性要足够好

根据以上解剖，我们知道全索引扫描会让查询变慢，接下来就要来谈谈索引的过滤性。

假设你现在维护了一个表，这个表记录了中国14亿人的基本信息，现在要查出所有年龄在10~15岁之间的姓名和基本信息，那么你的语句会这么写，select * from t_people where age between 10 and 15。

你一看这个语句一定要在age字段上开始建立索引了，否则就是个全面扫描，但是你会发现，在你建立索引以后，这个语句还是执行慢，因为满足这个条件的数据可能有超过1亿行。

我们来看看建立索引以后，这个表的组织结构图：

图片

这个语句的执行流程是这样的:

从索引上用树搜索，取到第1个age等于10的记录，得到它的主键id的值，根据id的值去主键索引取整行的信息，作为结果集的一部分返回；

在索引age上向右扫描，取下一个id的值，到主键索引上取整行信息，作为结果集的一部分返回；

重复上面的步骤，直到碰到第1个age大于15的记录；

你看这个语句，虽然他用了索引，但是他扫描超过了1亿行。所以你现在知道了，当我们在讨论有没有使用索引的时候，其实我们关心的是扫描行数。

对于一个大表，不止要有索引，索引的过滤性还要足够好。

像刚才这个例子的age，它的过滤性就不够好，在设计表结构的时候，我们要让所有的过滤性足够好，也就是区分度足够高。

回表的代价

那么过滤性好了，是不是表示查询的扫描行数就一定少呢？

我们再来看一个例子：

如果你的执行语句是 select * from t_people where name='张三' and age=8

t_people表上有一个索引是姓名和年龄的联合索引，那这个联合索引的过滤性应该不错，可以在联合索引上快速找到第1个姓名是张三，并且年龄是8的小朋友，当然这样的小朋友应该不多，因此向右扫描的行数很少，查询效率就很高。

但是查询的过滤性和索引的过滤性可不一定是一样的，如果现在你的需求是查出所有名字的第1个字是张，并且年龄是8岁的所有小朋友，你的语句会怎么写呢？

你的语句要怎么写？很显然你会这么写：select * from t_people where name like '张%' and age=8;

在MySQL5.5和之前的版本中，这个语句的执行流程是这样的:
图片

首先从联合索引上找到第1个年龄字段是张开头的记录，取出主键id，然后到主键索引树上，根据id取出整行的值；

判断年龄字段是否等于8，如果是就作为结果集的一行返回，如果不是就丢弃。

在联合索引上向右遍历，并重复做回表和判断的逻辑，直到碰到联合索引树上名字的第1个字不是张的记录为止。

我们把根据id到主键索引上查找整行数据这个动作，称为回表。你可以看到这个执行过程里面，最耗费时间的步骤就是回表，假设全国名字第1个字是张的人有8000万，那么这个过程就要回表8000万次，在定位第一行记录的时候，只能使用索引和联合索引的最左前缀，最称为最左前缀原则。

你可以看到这个执行过程，它的回表次数特别多，性能不够好，有没有优化的方法呢？

在MySQL5.6版本，引入了index condition pushdown的优化。我们来看看这个优化的执行流程：

图片

首先从联合索引树上，找到第1个年龄字段是张开头的记录，判断这个索引记录里面，年龄的值是不是8，如果是就回表，取出整行数据，作为结果集的一部分返回，如果不是就丢弃；

在联合索引树上，向右遍历，并判断年龄字段后，根据需要做回表，直到碰到联合索引树上名字的第1个字不是张的记录为止；

这个过程跟上面的差别，是在遍历联合索引的过程中，将年龄等于8的条件下推到所有遍历的过程中，减少了回表的次数，假设全国名字第1个字是张的人里面，有100万个是8岁的小朋友，那么这个查询过程中在联合索引里要遍历8000万次，而回表只需要100万次。

虚拟列

可以看到这个优化的效果还是很不错的，但是这个优化还是没有绕开最左前缀原则的限制，因此在联合索引你还是要扫描8000万行，那有没有更进一步的优化方法呢？

我们可以考虑把名字的第一个字和age来做一个联合索引。这里可以使用MySQL5.7引入的虚拟列来实现。对应的修改表结构的SQL语句:

alter table t_people add name_first varchar(2) generated (left(name,1)),add index(name_first,age);

我们来看这个SQL语句的执行效果:

CREATE TABLE `t_people`(
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAUT NULL,
`name_first` varchar(2) GENERATED ALWAYS AS (left(`name`,1)) VIRTUAL,KEY `name_first`(`name_first`,'age')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

首先他在people上创建一个字段叫name_first的虚拟列，然后给name_first和age上创建一个联合索引，并且，让这个虚拟列的值总是等于name字段的前两个字节，虚拟列在插入数据的时候不能指定值，在更新的时候也不能主动修改，它的值会根据定义自动生成，在name字段修改的时候也会自动修改。

有了这个新的联合索引，我们在找名字的第1个字是张，并且年龄为8的小朋友的时候，这个SQL语句就可以这么写：select * from t_people where name_first=‘张’ and age=8。

这样这个语句的执行过程，就只需要扫描联合索引的100万行，并回表100万次，这个优化的本质是我们创建了一个更紧凑的索引，来加速了查询的过程。

总结

本文给你介绍了索引的基本结构和一些查询优化的基本思路，你现在知道了，使用索引的语句也有可能是慢查询，我们的查询优化的过程，往往就是减少扫描行数的过程。

慢查询归纳起来大概有这么几种情况：

  • 全表扫描

  • 全索引扫描

  • 索引过滤性不好

  • 频繁回表的开销

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sql

为什么用了索引

查询还是慢

为什么用了索引，SQL查询还是慢？ 的相关文章

  • MYSQL：如何在同一查询中联接两个表，两次引用同一个表

    我有两张桌子 我正在尝试将下面的示例两个表与表 1 引用表 2 两次结合起来 例如 如果我查看表 1 组 2 和成员 7 它应该查找表 2 中的 ID 并给出输出 Group Members Name Name 2 7 Blue Dog T

  • hive - 在值范围之间将一行拆分为多行

    我在下面有一张表 想按从开始列到结束列的范围拆分行 即 id 和 value 应该对开始和结束之间的每个值重复 包括两者 id value start end 1 5 1 4 2 8 5 9 所需输出 id value current

  • 默认情况下在sql日期时间列中插入null/空值

    如何在 SQL Server 中创建一个表 默认日期时间为空 而不是1900 01 01 00 00 00 000我得到了 我的意思是 如果没有插入值 则默认值应该为 null 空等 如果没有插入值 默认值应该是null empty 在表定

  • CONTAINS 不适用于 Oracle Text

    我在执行此查询时遇到问题 SELECT FROM gob attachment WHERE CONTAINS gob a document java gt 0 它给了我 ORA 29902 error in executing ODCIIn

  • mysql GROUP_CONCAT 重复项

    我从 farmTOanimal 表中进行连接 如下所示 有一个类似的farmTotool表 id FarmID animal 1 1 cat 2 1 dog 当我在视图中加入表时 我得到的结果如下所示 FarmID animal tool

  • SQL Server - 选择满足条件的第一行

    我有 2 个包含 ID 的表 其中一个表中会有重复的 ID 我只想为表 B 中的每个匹配 ID 返回一行 例如 Table A objectIdA objectIdB 1 A 1 B 1 D 5 F Table B objectIdA 1

  • Rails 中 WHERE 子句中的 ALL 运算符

    关联关系如下图所示 InstructorStudent has many fees Fee belongs to instructor student 我想要获得在所有给定数组中具有每月详细信息的指导学生 如果其中任何一个中不存在每月详细信

  • 复杂的sql树行

    表结构 id message reply id 1 help me 0 434 love to 1 852 didn t work 434 0110 try this 852 2200 this wont 0 5465 done 0110

  • 如何关闭与数据库的现有连接

    我想关闭与 MS SQL Server 的现有连接 以便可以通过编程方式对该数据库进行恢复 这应该会断开其他所有人的连接 并使您成为唯一的用户 alter database YourDb set single user with rollb

  • mysql 详细查询字符串，如通配符

    不知道如何标题我的问题 哈哈 下面是我需要的 我的数据库中的值如下所示 test example 1 test example 2 test example TD 1 这些值的长度可以不同 test example 只是一个示例 某些值将具

  • 我的用例可以合并到单个查询中而不影响性能吗？

    我主要着眼于改善表现查询的内容以及是否能够解决单一查询对于我的用例之一 解释如下 涉及到2张表 Table 1 EMPLOYEE column1 column2 email1 email2 column5 column6 Table 2 E

  • 连接 3 三张表

    我有这个图表应该可以解释我的情况 我需要一些关于连接 3 个表的帮助 我不知道如何做这种事情 因此 我可以通过执行以下操作来经历一段检索记录的 while 循环 img src alt Album AlbumID 使用内部联接 http w

  • 使用“AND”表达式构建动态 SQL，而不混淆嵌套条件？

    总的来说 我对 php 和编码相当陌生 我有一系列条件需要测试它们是否已设置 它们是 option1 option2 option3 if isset option1 if isset option2 if isset option3 qu

  • SQL 分隔符上的逗号分隔列

    这是一个 split 函数 它可以应用为dbo Split sf we fs we 当我将字符串更改为列名时 它不起作用 例如dbo Split table columnName Select from dbo Split email pr

  • Android 中读取未提交的事务

    我正在进行大量数据库操作 这会向我的数据库添加大约 10 000 条记录 由于这可能需要很长时间 因此最好使用事务 db startTransaction do write operations db setTransactionSucce

  • 如何为“%abc%”搜索创建文本索引？

    我想对查询进行索引x like abc 如果我有一个如下表 create table t data varchar 100 我想创建一个索引以便能够有效地执行以下操作 select from t where contains abc 和这个

  • 在内连接中重用 mysql 子查询

    我正在尝试优化查询 试图避免重复用 指示的查询 复杂查询 使用两次 结果相同 原始查询 SELECT news FROM news INNER JOIN SELECT myposter FROM SELECT COMPLEX QUERY U

  • 使用显式创建表语句与 select into 创建表

    使用显式创建表语句和加载数据与选择数据之间是否存在性能差异 此示例仅显示 2 列 但问题是针对使用非常大的表 下面的示例也使用临时表 尽管我也想知道使用常规表的效果 我认为无论表格类型如何 它们都是相同的 临时表场景 Explicitly

  • 如何在postgresql中编写有关最大行数的约束？

    我认为这是一个很常见的问题 我有一张桌子user id INT 和一张桌子photo id BIGINT owner INT 所有者是一个参考user id 我想向表照片添加一个约束 以防止每个用户将超过 10 张照片输入数据库 写这个的最

  • 火鸟删除速度很慢

    我正在做这个简单的交易 DELETE FROM ominve01 WHERE CVE OBS IN SELECT CVE OBS FROM minve01 M WHERE M FECHA DOCU lt 31 12 2010 OR FECH

随机推荐

  • 【数据清洗】图像数据清洗之---去除相似度高的图像

    目的 xff1a 人工做数据清洗较为麻烦 xff0c 而且费事费力没成绩 xff0c 还拉拽整个项目的后腿 所以这里根据调研情况 xff0c 分析尝试一下 1 调研分析 1 百度EasyData 参考 xff1a 百度大脑自己的csdn说明

  • pip 命令，向指定的python环境中安装包

    在linux中 xff0c 进入anaconda的虚拟环境之后 xff0c 使用pip并不一定会安装在当前环境下 xff08 和windows不太一样 xff09 xff0c 而是安装在该pip对应的python版本里 xff0c pip对

  • 快速区分主键与外键

    主键与外键的区分 主键用来唯一标识一条记录 xff0c 不允许有重复 xff0c 不允许为空 作用 xff1a 用来保证数据的完整性 个数 xff1a only one 外键 xff0c 表的外键是另一个表的主键 xff0c 外键可以有重复

  • module.exports和exports、export和export default、require和import的详解

    一 分类 commonJS xff1a 导出 xff08 module exports和exports xff09 导入 xff08 require xff09 ES6 xff1a 导出 xff08 export和export defaul

  • 如何查看静态编译的依赖(所链接的库)

    如何查看静态编译的依赖 实际上 静态库不存在依赖 依赖是动态编译下被动态链接的库 可以使用ldd查看 静态链接的话 所有需要的静态库会被添加到文件中 库名在连接的过程中会被剥除 如果文件包含debug 信息 可以通过查看符号的方式 对比静态

  • Hadoop URL读取数据

    URL setURLStreamHandlerFactory 每个虚拟机只能调用一次这个方法 xff0c 因此通常在静态中调用这个方法 xff01 这个限制以为着如果程序其他的组件已经声明一个实例 xff0c 则将无法使用这个方法读取 1

  • 【随记】Mac 取消系统更新的红点

    1 打开 系统偏好设置 点击 软件更新 2 取消选择 自动保持我的Mac最新 3 然后点击 高级 按钮 xff0c 取消所有的勾选 4 通过上面步骤设置后 xff0c 发现底部的小红点还在 xff0c 则需打开终端 xff0c 执行如下2段

  • Android InputChannel事件发送接收系统分析

    本文基于Android12 InputChannel表示其他进程通过文件描述符传递输入事件到View的通道 xff0c 因为需要跨进程传输 xff0c 实现了Parcelable序列化接口 xff0c 所以也能够理解Java层的InputC

  • Homestead for Windows

    Homestead Windows Laravel 致力于让整个 PHP 开发体验变得愉快 xff0c 包括你的本地开发环境 Vagrant 提供了一种简单 xff0c 优雅的方式来管理和配置虚拟机 Laravel Homestead 是一

  • 因为你，我愿意

    偶然成就必然 xff0c 纵一现昙花 xff0c 亦可夺人眼眸 顺水推舟好过机缘巧合 xff0c 谨以此献给程序员未来展望 者 题记 每逢六月初 xff0c 总有人会这般调侃自己 就要高考了 xff0c 还没准备好 xff0c 好紧张 xf

  • 论文阅读笔记《Joint Graph Learning and Matching for Semantic Feature Correspondence》

    核心思想 本文提出一种联合图学习和图匹配的算法 xff08 GLAM xff09 xff0c 将图的构建和匹配过程整合到一个端到端的注意力网络中 相比于其他启发式的建图方法 xff0c 如Delaunay三角法 KNN方法或完全图 xff0

  • 为了安装caffe 安装opencv

    cmake D CMAKE BUILD TYPE 61 RELEASE D CMAKE INSTALL PREFIX 61 home lab248 anaconda2 D INSTALL PYTHON EXAMPLES 61 ON D IN

  • 腾讯轻量云服务器控制台详细介绍及建站操作图文教程

    腾讯轻量应用服务器控制台与腾讯云服务器不同 xff0c 轻量应用服务器主要是在控制台上集成了大部分建站功能 xff0c 通过简单点击几次鼠标就可以轻松建站 xff0c 易学易用 不过对于没接触过的新手来说 xff0c 还是有点陌生的 xff

  • 腾讯云轻量应用服务器快速搭建一个专属网盘

    一 前言 xff1a 云盘我想大家接触的一定不会少 云盘很好地解决了文件存储和共享的问题 xff0c 但随着大量云盘厂商的退出 xff0c 剩余的云盘服务也越来越少 有些云盘虽然上传速度快 xff0c 但是下载速度较慢 xff0c 不开通会

  • 使用腾讯云轻量应用服务器搭建一个简洁漂亮的目录

    前言 作为一个摄影爱好者 xff0c 会经常做一些图片的分享 xff0c 前端时间在网上看到了一个非常好看的目录 xff0c 这里给大家分享一下怎么样通过腾讯轻量应用服务器来搭建 官方介绍 files photo gallery是一款简洁漂

  • 玩转服务器-博客两件套之绝佳的Markdown写作平台CodiMD

    前言 大家都很羡慕博主的高产 xff0c 纷纷问我有什么技巧 我的回复是手熟 xff0c 多写 xff0c 那么多写就需要一个比较好的工具 xff0c 所以我这里给大家介绍一个在线markdown文档平台 xff0c 让大家可以随时书写文档

  • 玩转服务器-博客两件套之开源的一文多发平台ArtiPub

    玩转服务器 博客两件套之开源的一文多发平台ArtiPub 前言 上次给大家介绍了 xff0c 博主在线的markdown文档平台 xff0c 让大家可以随时书写文档和博客 xff0c 那么很多朋友在很多平台都看到了我的文章 xff0c 我是

  • 使用acme.sh申请Let‘s Encrypt免费的SSL证书

    使用acme sh申请Let s Encrypt免费的SSL证书 说明 xff1a Let s Encrypt 是一个由非营利性组织 互联网安全研究小组 xff08 ISRG xff09 提供的免费 自动化和开放的证书颁发机构 xff08

  • win7操作系统下laravel/homestead在SSH auth method: private key卡住提示Warning: Connection reset. Retrying的解决方案

    将VirtualBox兼容模式改为win7 勾选以管理员身份运行 安全里面各组个用户全部编辑好权限并勾选 电脑开机后优先双击VirtualBox启动后在执行git命令行进行启动

  • 为什么用了索引，SQL查询还是慢？

    原文链接cnblogs com jackyfei p 12122767 html 经常有同学疑问 xff0c 为什么有时候一个SQL语句使用了索引 xff0c 为什么还是会进入到慢查询之中呢 xff1f 今天我们就从这个问题开始来聊一聊索引

热门标签