Explain详解与索引优化最佳实践
Explain工具介绍
使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈
在select语句之前增加explain关键字,MySQL会在查询前设置一个标记,执行查询会返回执行计划的信息,而不是执行这条SQL
注意: 如果from中包含子查询,仍会执行该子查询,将结果放入临时表中
Explain分析示例
explain官方文档
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (`id` int(11) NOT NULL,`name` varchar(45) DEFAULT NULL,`update_time` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2024-02-22 15:27:18'), (2,'b','2024-02-22 15:27:18'), (3,'c','2024-02-22 15:27:18');DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (`id` int(11) NOT NULL,`film_id` int(11) NOT NULL,`actor_id` int(11) NOT NULL,`remark` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
explain select * from actor
explain两个变种
-
explain extended: 会在explain的基础上额外提供一些查询优化信息,紧随其后通过show warnings命令可以得到优化后的查询语句,从而看出优化器优化了什么.额外还有filtered列,是一个百分比的值,rows*filtered/100可以估算出将要和explain中前一个表进行连接的行数(前一个表指explain中的id值比当前表id值小的表)
explain extended select * from film where id =1; show warnings
-
explain partitions: 相比explain多了个partitions字段,如果查询是基于分区表的话,会查询将访问的分区
以上两个变种新增的filtered列和partitions列在5.7以后使用explain都会直接显示出来,不需要再加其他的关键字
explain中的列
id
id列的编号是select的序列号,有几个select就有几个id,并且id的顺序是按select出现的顺序增长的
id列越大执行优先级越高,id相同则从上往下执行,id为null最后执行
select_type
select_type表示对应行是简单还是复杂的查询
-
simple: 简单查询,查询不包含子查询和union
explain select * from film where id = 2
-
primary: 复杂查询中最外层的select
-
subquery: 包含在select中的子查询(不在from子句中)
-
derived: 包含在from子句中的子查询.MySQL会将结果存放在一个临时表中,也成为派生表(derived的英文含义)
set session optimizer_switch='derived_merge=off'; explain select(select 1 from actor where id = 1) from (select * from film where id = 1)der;
set session optimizer_switch='derived_merge=on';
-
union: 在union中的第二个和随后的select
explain select 1 union all select 1
table
这一列表示explain的一行正在访问那个表.
当from子句中有子查询时,table列是格式,表示当前查询依赖id=N的查询,于是先执行id=N的查询
当有union时,union result的table列的值为<union1,2>,1和2表示参与union的select行id.
type
这一列表示关联类型或访问类型, 即MySQL决定如何查找表中的行,查找数据行记录的大概范围.
依次最优到最差分别为:system>const>eq_ref>ref>range>index>ALL
关于SQL性能优化目标,Java开发手册(嵩山版):
Null: MySQL能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引.例如:再索引列中选取最小值,可以单独查找索引来完成(B+树叶子节点最左节点就是最小值).不需要在执行时访问表
explain select min(id) from film
system,const: MySQL能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings的结果).用于primary key或unique key的所有列与常数比较时,所有表最多有一个匹配行,读取1次速度比较快. system时const的特例, 表中只有一条元素匹配时为system
explain select * from (select * from film where id = 1)tmp;
show warnings
eq_ref: primary key或unique key索引的所有部分被连接使用,最多只会返回一条符合条件的记录.这可能是再const之外最好的连接类型了,简单的select查询不会出现这种type
ref: 相比eq_ref,不适用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的记录
-
简单的select查询,name是普通索引(非唯一索引)
explain select * from film where name = 'film1'
-
关联表查询,idx_film_actor_id是film_id和actor_id的联合索引,这里使用到了film_actor的左边前缀film_id部分
explain select film_id from film left join film_actor on film.id = film_actor.film_id
range: 范围扫描通常出现在in(),between,<,>,>=等操作中.使用一个索引来检索给定范围的记录
explain select * from actor where id > 1
index: 扫描全索引就能拿到结果,一般是扫描某个二级索引,这种扫描不会从索引树根节点开始快速查找,而是直接对二级索引的叶子节点遍历和扫描,速度还是比较慢的,这种查询一般会使用覆盖索引,二级索引一般比较小,所以这种秦广比ALL快一些
explain select * from film
ALL: 即全表扫描,扫描你的聚簇索引的所有叶子节点,通常情况下这需要增加索引来优化了.
explain select * from actor
possible_keys
这一列显示查询可能使用那些索引来查找
explain时可能踹向那possible_keys有值,而key显示为Null的情况,这种情况是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表扫描
如果改列是Null,则没有相关的索引.在这种情况下,可以通过检查where子句看是否可以创建一个适当的索引来提高查询性能,然后用explain查看效果
key
这一列显示mysql实际采用那个索引来优化对该表的访问.
如果没有使用索引,则该列是Null.如果想强制mysql使用或护士possible_keys列中的索引,在查询中使用force index,ignore index
key_len
这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的那些列
举例来说,film_actor的联合索引idx_film_actor_id由film_id和actor_id两个int列组成,并且每个int都是4字节.通过结果中的key_len=4可以推断查询使用了第一个列:film_id来执行索引查找
explain select * from film_actor where film_id = 2
key_len计算规则如下:
- 字符串:char(n)和varchar(n),5.0.3以后的版本中,n代表字符数,而不是字节数, 如果是utf-8,一个数字或字母1个字节,一个汉字占3个字节
- char(n):如果存汉字长度就是3n字节
- varchar(n):如果存汉字长度就是3n+2字节,加的2字节用来存储字符串长度,因为varchar是可变长度字符串
- 数值类型
- tinyint:1字节
- smallint:2字节
- int:4字节
- bigint:8字节
- 时间类型
- date:3字节
- timestamp:4字节
- datetime:8字节
- 如果字段允许为NULL,需要1字节记录是否为NULL
索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引
ref
这一列显示在key列记录的索引中, 表查找值所使用到的列或常量,常见的由:const;字段名
rows
这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数
filtered
该列是一个百分比的值,rows*filtered/100可以估算出将要和explain中前一个表进行连接的行数(前一个表指explain中的id值比当前表id值小的表)
Extra
这一列展示的是额外信息.常见的重要值如下:
Using index:使用覆盖索引
覆盖索引定义:mysql执行计划explain结果里的key有使用索引,如果select后面查询的字段都可以从这个索引的树中获取,这种情况下就避免了回表.一般可以说用到了覆盖索引.extra里一般都有using index;覆盖索引一般针对的是辅助索引:整个查询结果只通过覆盖索引就能拿到结果,不需要通过辅助索引树找到主键,再通过主键去主键索引树获取其他字段的值
explain select film_id from film_actor where film_id = 1
Using where: 使用where语句来处理结果,并且查询的列未被索引覆盖
explain select * from actor where name = 'a'
Using index condition: 查询的列不完全被索引覆盖,where条件中是一个前导列范围
explain select * from film_actor WHERE film_id > 1
Using temporary: mysql需要创建一张临时表来处理查询.出现这种情况一般是要进行优化的.首先想到用索引来优化
-
actor.name没有索引,此时创建了张临时表来distinct
explain select distinct name from actor
-
film.name建立了idx_name索引,此时查询extra是using index,没有用临时表
explain select distinct name from film
Using filesort: 将用外部排序而不是索引排序,数据较小时从内存排序,否则需要再磁盘完成排序.这种情况下一般也是要考虑使用索引来优化的.
-
actor.name未创建索引,会浏览actor整张表,保存排序关键字name和对应的id,然后排序name并检索行记录
explain select * from actor order by name
-
film.name建立了idx_name索引.此时查询时extra时using index
explain select * from film order by name
Select tables optimized away: 使用某些聚合函数(比如max,min)来访问存在索引的某个字段时
explain select min(id) from film
索引最佳实战
示例表:
CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',PRIMARY KEY (`id`),KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
-
全值匹配
explain select * from employees where name = 'LiLei'
explain select * from employees where name = 'LiLei' and age = 22
-
最左前缀法则
如果联合索引,要遵守最左前缀法则.指的是查询从索引的最左列开始并且不跳过索引中的列.
explain select * from employees where name = 'LiLei' and age = 22; explain select * from employees where age = 30 and position = 'dev'; explain select * from employees where position = 'dev'
-
不在索引列上做任何操作(计算,函数,自动/手动类型转换),会导致索引失效而转向全表扫描
explain select * from employees where name = 'LiLei'; explain select * from employees where left(name,3) = 'LiLei'
给hire_time增加一个普通索引:
alter table employees add index idx_hire_time(hire_time)using btree explain select * from employees where date(hire_time) = '2024-03-13'
转化为日期范围查询,有可能会走索引
explain select * from employees where hire_time >= '2024-03-13 00:00:00' and hire_time <= '2024-03-13 23:59:59'
还原最初索引状态
alter table employees drop index idx_hire_time
-
存储引擎不能使用索引中范围条件右边的列
explain select * from employees where name = 'LiLei' and age = 22 and position = 'dev'; explain select * from employees where name = 'LiLei' and age > 22 and position = 'dev';
-
尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句
explain select name,age,position from employees where name = 'LiLei' and age = 22 and position = 'dev'; explain select * from employees where name = 'LiLei' and age = 22 and position = 'dev';
-
mysql在使用不等于(!=/<>),not int,not exists的时候无法使用索引会导致全表扫描.小于,大于,小于等于,大于等于这些,MySQL内部优化器会根据检索比例,表大小等多个因素整体评估是否使用索引
explain select * from employees where name != 'LiLei'
-
is null,is not null一般情况下也无法使用索引
explain select * from employees where name is null
-
like以通配符开头(‘%xxx’)mysql索引失效会变成全表扫描操作
explain select * from employees where name like '%Li'; explain select * from employees where name like 'Li%'
问题:解决以通配符开头索引不被使用的办法
-
使用覆盖索引,查询字段必须是建立覆盖索引字段
explain select name, age, position from employees where name like '%Li';
-
如果不能使用覆盖索引则可能需要借助搜索引擎
-
-
字符串不加单引号索引失效
explain select * from employees where name = '1000'; explain select * from employees where name = 1000;
-
少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例,表大小等多个因素整体评估是否使用索引,详见范围查询优化
explain select * from employees where name = 'LiLei' or name = 'HanMeiMei'
-
范围查询优化
给age添加单值索引
alter table employees add index idx_age(age) using btree
explain select * from employees where age >= 1 and age <= 2000
没走索引的原因:mysql内部优化器会根据检索比例,表大小等多个因素整体评估是否使用索引.比如,可能是由于单次数据量查询过大导致优化器最终选择不走索引;优化方法:可以将大的范围拆分成多个小范围
explain select * from employees where age >= 1 and age <= 1000; explain select * from employees where age >= 1000 and age <= 2000;
还原最初索引状态
alter table employees drop index idx_age
索引使用总结
like ‘kk%’ 相当于=常量,‘%kk’ 和’%kk%'相当于范围
相关文章:

Explain详解与索引优化最佳实践
Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈 在select语句之前增加explain关键字,MySQL会在查询前设置一个标记,执行查询会返回执行计划的信息,而不是执行这条SQL 注意: 如果from中包含子查询,仍会执行该子查询,将结果…...

Spring Boot轻松整合Minio实现文件上传下载功能【建议收藏】
一、Linux 安装Minio 安装 在/root/xxkfz/soft目录下面创建文件minio文件夹,进入minio文件夹,并创建data目录; [rootxxkfz soft]# mkdir minio [rootxxkfz soft]# cd minio [rootxxkfz minio]# mkdir data执行如下命令进行下载 [rootxxkf…...

MySql入门教程--MySQL数据库基础操作
꒰˃͈꒵˂͈꒱ write in front ꒰˃͈꒵˂͈꒱ ʕ̯•͡˔•̯᷅ʔ大家好,我是xiaoxie.希望你看完之后,有不足之处请多多谅解,让我们一起共同进步૮₍❀ᴗ͈ . ᴗ͈ აxiaoxieʕ̯•͡˔•̯᷅ʔ—CSDN博客 本文由xiaoxieʕ̯•͡˔•̯᷅ʔ 原创 CSDN …...

鸿蒙Harmony应用开发—ArkTS声明式开发(基础手势:Slider)
滑动条组件,通常用于快速调节设置值,如音量调节、亮度调节等应用场景。 说明: 该组件从API Version 7开始支持。后续版本如有新增内容,则采用上角标单独标记该内容的起始版本。 子组件 无 接口 Slider(options?: SliderOption…...

第五十六回 徐宁教使钩镰枪 宋江大破连环马-飞桨图像分类套件PaddleClas初探
宋江等人学会了钩镰枪,大胜呼延灼。呼延灼损失了很多人马,不敢回京,一个人去青州找慕容知府。一天在路上住店,马被桃花山的人偷走了,于是到了青州,带领官兵去打莲花山。 莲花山的周通打不过呼延灼…...
springboot/ssm企业内部人员绩效量化管理系统Java员工绩效管理系统web
springboot/ssm企业内部人员绩效量化管理系统Java员工绩效管理系统web 基于springboot(可改ssm)vue项目 开发语言:Java 框架:springboot/可改ssm vue JDK版本:JDK1.8(或11) 服务器:tomcat 数据库&…...
XML语言的学习记录2-XMLHttpRequest
学习笔记:XMLHttpRequest 特点: 在不重新加载页面的情况下更新网页在页面已加载后从服务器请求数据在页面已加载后从服务器接收数据在后台向服务器发送数据创建XMLHttpRequest对象 xmlhttpnew XMLHttpRequest();IE5 和 IE6,使用 …...
力扣爆刷第95天之hot100五连刷61-65
力扣爆刷第95天之hot100五连刷61-65 文章目录 力扣爆刷第95天之hot100五连刷61-65一、131. 分割回文串二、51. N 皇后三、35. 搜索插入位置四、74. 搜索二维矩阵五、34. 在排序数组中查找元素的第一个和最后一个位置 一、131. 分割回文串 题目链接:https://leetcod…...
聊聊powerjob的执行机器地址
序 本文主要研究一下powerjob的执行机器地址(designatedWorkers) SaveJobInfoRequest powerjob-common/src/main/java/tech/powerjob/common/request/http/SaveJobInfoRequest.java Data public class SaveJobInfoRequest {/*** id of the job. set null to create or non-…...

Android Kotlin知识汇总(三)Kotlin 协程
Kotlin的重要优势及特点之——结构化并发 Kotlin 协程让异步代码像阻塞代码一样易于使用。协程可大幅简化后台任务管理,例如网络调用、本地数据访问等任务的管理。本主题介绍如何使用 Kotlin 协程解决以下问题,从而让您能够编写出更清晰、更简洁的应用代…...

JVM垃圾收集器-serial.parNew,parallelScavnge,serialOld,parallelOld,CMS,G1
垃圾收集器 分代模型 适用于新生代: serial parNew parallel Scaavenge 适用于老年代: CMS serial Old(msc) paraller Old 分区模型 适用于超大容量: G1 分代模型 serial /serial Old收集器 1.单线程收集器 2.收集时会暂停其他线程&…...
docker搭建upload-labs
Upload-labs 是一个专门设计用于学习和练习文件上传安全的开源工具。它提供了各种场景的文件上传漏洞,供用户通过实践来学习如何发现和利用这些漏洞,同时也能学习到防御措施。使用 Docker 来搭建 upload-labs 环境是一种快速、简便的方法,它可…...
超详细外贸单证汇总!
今天给大家汇总了外贸单证的种类与使用相关知识,东西齐全,赶紧码住! 1、合同 CONTRACT是统称,买卖双方均可出具。如系卖方制作,可称为销售确认书,买方出具则可称为采购。 买卖双方均可出具合同。卖方出具的,可称为销售确认书(Sales Confir…...

Docker部署ChatGLM3、One API、FastGPT
创建并运行chatglm3容器 docker run --name chatglm3 -p 8000:8000 registry.cn-hangzhou.aliyuncs.com/ryyan/chatglm.cpp:chatglm3-q5_1 创建并运行one-api容器 (其中挂载路径 D:\one-api 可以选择你自己喜欢的目录) docker run --name oneapi -d -p 3000:3000 -e TZAsia…...

【Linux-网络编程】
Linux-网络编程 ■ 网络结构■ C/S结构■ B/S结构 ■ 网络模型■ OSI七层模型■ TCP/IP四层模型 ■ TCP■ TCP通信流程■ TCP三次握手■ TCP四次挥手 ■ 套接字:socket 主机IP 主机上的进程(端口号)■ TCP传输文件 ■ 网络结构 ■ C/S结构…...

win10虚拟机安装驱动教程
在虚拟机菜单栏中选择安装VMware Tools: 安装好后,在虚拟机中打开此电脑,双击DVD驱动器进行安装: 一直点击下一步: 安装完成: 此时重启虚拟机,发面小屏幕页面的虚拟机自动占满了全部屏幕&#x…...

SpringBoot实战项目——博客笔记项目
提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 文章目录 前言一、项目介绍二、项目的整体框架 2.1 数据库模块 2.2 前端模块 2.3 后端模块三、项目图片展示四、项目的实现 4.1 准备工作 4.…...

【海贼王的数据航海】排序——直接选择排序|堆排序
目录 1 -> 选择排序 1.1 -> 基本思想 1.2 -> 直接选择排序 1.2.1 -> 代码实现 1.3 -> 堆排序 1.3.1 -> 代码实现 1 -> 选择排序 1.1 -> 基本思想 每一次从待排序的数据元素中选出最小(或最大)的一个元素,存放在序列的起始位置&…...
Flutter 的 switch 语句补遗
我的 App 里,一个消息气泡变成空白了,非常奇怪,此前一直是没问题的,经过调试定位我发现: static TextSpan _buildRootSpan(BuildContext ctx, List<LinkifyElement> parts, TextStyle? style) {List<InlineS…...
Linux动态库*.so函数名修改
在某些学习或者特殊需求的情况下要对linux下动态库*.so文件内部的函数名进行修改。 比如一个函数ADD(int a,int b);修改为Add(int a,int b); 通过这篇文章你将了解到在linux下动态库函数名寻址的规则,截止2024年3月linux动态库的寻址规则已经出现多种,这…...

TDengine 快速体验(Docker 镜像方式)
简介 TDengine 可以通过安装包、Docker 镜像 及云服务快速体验 TDengine 的功能,本节首先介绍如何通过 Docker 快速体验 TDengine,然后介绍如何在 Docker 环境下体验 TDengine 的写入和查询功能。如果你不熟悉 Docker,请使用 安装包的方式快…...
k8s从入门到放弃之Ingress七层负载
k8s从入门到放弃之Ingress七层负载 在Kubernetes(简称K8s)中,Ingress是一个API对象,它允许你定义如何从集群外部访问集群内部的服务。Ingress可以提供负载均衡、SSL终结和基于名称的虚拟主机等功能。通过Ingress,你可…...
Qt Http Server模块功能及架构
Qt Http Server 是 Qt 6.0 中引入的一个新模块,它提供了一个轻量级的 HTTP 服务器实现,主要用于构建基于 HTTP 的应用程序和服务。 功能介绍: 主要功能 HTTP服务器功能: 支持 HTTP/1.1 协议 简单的请求/响应处理模型 支持 GET…...
【python异步多线程】异步多线程爬虫代码示例
claude生成的python多线程、异步代码示例,模拟20个网页的爬取,每个网页假设要0.5-2秒完成。 代码 Python多线程爬虫教程 核心概念 多线程:允许程序同时执行多个任务,提高IO密集型任务(如网络请求)的效率…...
Xen Server服务器释放磁盘空间
disk.sh #!/bin/bashcd /run/sr-mount/e54f0646-ae11-0457-b64f-eba4673b824c # 全部虚拟机物理磁盘文件存储 a$(ls -l | awk {print $NF} | cut -d. -f1) # 使用中的虚拟机物理磁盘文件 b$(xe vm-disk-list --multiple | grep uuid | awk {print $NF})printf "%s\n"…...
A2A JS SDK 完整教程:快速入门指南
目录 什么是 A2A JS SDK?A2A JS 安装与设置A2A JS 核心概念创建你的第一个 A2A JS 代理A2A JS 服务端开发A2A JS 客户端使用A2A JS 高级特性A2A JS 最佳实践A2A JS 故障排除 什么是 A2A JS SDK? A2A JS SDK 是一个专为 JavaScript/TypeScript 开发者设计的强大库ÿ…...

人机融合智能 | “人智交互”跨学科新领域
本文系统地提出基于“以人为中心AI(HCAI)”理念的人-人工智能交互(人智交互)这一跨学科新领域及框架,定义人智交互领域的理念、基本理论和关键问题、方法、开发流程和参与团队等,阐述提出人智交互新领域的意义。然后,提出人智交互研究的三种新范式取向以及它们的意义。最后,总结…...

处理vxe-table 表尾数据是单独一个接口,表格tableData数据更新后,需要点击两下,表尾才是正确的
修改bug思路: 分别把 tabledata 和 表尾相关数据 console.log() 发现 更新数据先后顺序不对 settimeout延迟查询表格接口 ——测试可行 升级↑:async await 等接口返回后再开始下一个接口查询 ________________________________________________________…...

代码规范和架构【立芯理论一】(2025.06.08)
1、代码规范的目标 代码简洁精炼、美观,可持续性好高效率高复用,可移植性好高内聚,低耦合没有冗余规范性,代码有规可循,可以看出自己当时的思考过程特殊排版,特殊语法,特殊指令,必须…...
【学习笔记】erase 删除顺序迭代器后迭代器失效的解决方案
目录 使用 erase 返回值继续迭代使用索引进行遍历 我们知道类似 vector 的顺序迭代器被删除后,迭代器会失效,因为顺序迭代器在内存中是连续存储的,元素删除后,后续元素会前移。 但一些场景中,我们又需要在执行删除操作…...