mysql索引（explain 执行计划）

已于 2024-03-22 17:43:26 修改
阅读量1.9k 收藏 41
点赞数 42
分类专栏： mysql原理 文章标签： mysql 开发语言
于 2024-03-18 16:16:48 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/king_zzzzz/article/details/136801003
版权

关键词

执行计划

EXPLAIN + 语句

在这里插入图片描述

查看mysql 优化后的语句

show warnings;

在这里插入图片描述

EXPLAIN 执行后，各列的含义

要点：

  1. select_type 如何查询 表
  2. type 如何查询 行
  3. key 如何使用 索引
  4. key_len 索引 使用多少
  5. rows 行 预计使用多少
  6. extra 表 的额外信息

1.id

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。
在这里插入图片描述

2.select_type

MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。
select_type 表示对应行是是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种。
注意：mysql 5.7 做了衍生表的优化，先将 衍生表 合并 优化关闭，

set session optimizer_switch='derived_merge=off'; #关闭mysq15.7新特性对衍生表的合并优化
set session optimizer_switch='derived_merge=on';#还原默认配置

1）simple：简单查询。查询不包含子查询和union

 explain  select * from actor;

在这里插入图片描述

2）primary：复杂查询中最外层的 select
3）subquery：包含在 select 中的子查询（不在 from 子句中）
4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

在这里插入图片描述

5）union：在 union 中的第二个和随后的 select

explain select 1 union all select 1;

在这里插入图片描述

3.table

访问哪个表。
当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为 <union1,2>，1和2表示参与 union 的 select 行id。

4.type

  • 这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。
  • 效率依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
  • 常用的7种是：system > const > eq_ref > ref > range > index > ALL
  • 优化一般到 range 级别，最好是 ref 级别
  • 注意：NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表。

例如：在索引列中选取最小值，可以直接查找索引树来完成，不需要在执行时访问表

explain select min(id) from film;

在这里插入图片描述

1.const, system（where :主键/唯一键）

const：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。
用于 主键 或 唯一键 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。
system：system是const的特例，表里只有一条元组匹配时为system
例如：

explain  select * from (select * from film where id = 1) tmp;

在这里插入图片描述
在这里插入图片描述
上表中的dual 就是个 mysql 默认的空表，为了满足sql语句的基本写法

2.eq_ref（关联时 被关联的表使用主键/唯一键）

被关联的表 使用了主键 或 唯一键 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。

explain select * from film_actor left join film on film_actor.film_id = film.id;

在这里插入图片描述

注意：若是被关联的表中的 关联字段不是主键 或 唯一键，type 就是 ALL

explain SELECT * from film left join film_actor on film_actor.film_id = film.id;
或
explain select * from film_actor right join film on film_actor.film_id = film.id;

在这里插入图片描述

3.ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

例如:
(1) 使用了普通索引

 explain select * from film where name = "film1";

在这里插入图片描述

（2）关联表查询，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;

在这里插入图片描述
注意： 当 select 后面没有指定 字段，为 * 的时候，Type 就是 ALL

explain select * from film left join film_actor on film.id = film_actor.film_id;

在这里插入图片描述

4.range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

explain select * from actor where id > 1;

在这里插入图片描述

5.index（使用了覆盖索引：即索引树中有全部要查询的字段）
explain select * from film;

扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些

若我们表中只有两个字段
在这里插入图片描述
那么在使用select * 的时候，就是覆盖索引

在这里插入图片描述
当表中有其他字段时
在这里插入图片描述
select * 就使用了全表 扫描
在这里插入图片描述
当我们指定查询的字段 在索引树中时 依旧是 index

explain select id from film;
explain select name from film;
explain select id,name from film;

在这里插入图片描述

当我们指定查询的字段 不在索引树中时 ,就会是 ALL

explain select age from film;
explain select id, age from film;

在这里插入图片描述
这个就是覆盖索引
即 不管是主键索引还是普通索引，组合索引，查询的字段 在索引树中 时，所以尽量不要用 * ，否则就会造成回表

6.ALL

即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

explain select * from actor;

在这里插入图片描述

5. possible_keys

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，
这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。
在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

6. key

这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。
如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

7. 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=8可推断出查询使用了第一个列：film_id 和 actor_id列来执行索引查找。
explain select * from film_actor where film_id = 2 and actor_id =3;

在这里插入图片描述

key_len计算规则如下：

  • 字符串
    • char(n)：n字节长度
    • varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
  • 数值类型
    • tinyint：1字节
    • smallint：2字节
    • int：4字节
    • bigint：8字节
  • 时间类型
    • date：3字节
    • timestamp：4字节
    • datetime：8字节
  • 如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节
当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

8. ref（key中用到的列表或常量）

这一列显示了在key列记录的索引中，表查找值所用到的列或常量.
常见的有：const（常量），func，NULL，字段名（例：film.id）

9. rows

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

10. Extra

这一列展示的是额外信息。常见的重要值如下：

Using index（使用了覆盖索引，不会回表）

这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。

explain select id from film ;

在这里插入图片描述

Using where （未被索引覆盖）

使用where语句来处理结果，并且 查询的列 未被索引覆盖

explain select * from actor where name ='a';

在这里插入图片描述

Using index condition （不完全被索引覆盖）

查询的列不完全被索引覆盖

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索引，直接使用索引树
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;

在这里插入图片描述

建表sql

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','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-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,
  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);

官方文档

官方文档 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

LeBron.JG
关注
  • 42
    点赞
  • 踩
  • 41
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql索引及explain
TigerwolfC的博客
03-12 659
索引：是一种数据结构，以增加存储开销和减慢DML(增、删、改)操作来提高查询速度。 常见的索引结构:btree索引（myisam，innodb，memory，heap），hash索引（memory，heap）。 索引本身也很大，不可能全部存储在内存中，所以索引往往以索引文件的形式存储在磁盘上。 常见的索引包括：主键索引、唯一索引、普通索引、全文索引 注意：查询一次后会将这次的查询结果缓存起...
mysql explain 索引_mysql之explain详解（分析索引的最佳使用）
weixin_29358723的博客
02-17 261
在这里对explain的各个字段进行详细的分析，来帮助大家分析自己所写的sql是否最佳的使用了索引。首先是select_type:将select查询分为简单(simple)和复杂两种类型复杂类型又分为子查询(subquery)和from列表中包含子查询(drived)simple:drived:就type进行详细的介绍：System,const,eq_ref,ref,range,index,all...
掌握MySQL执行计划分析【Explain】
最新发布
weixin_40381772的博客
05-11 885
掌握MySQL执行计划分析是数据库性能优化的关键步骤。通过使用Explain命令，我们可以获得查询的详细信息，从而对SQL语句进行针对性的优化。这不仅能够提高查询效率，还能提升整个应用的性能。因此，对于数据库管理员和开发人员来说，深入理解执行计划并能够有效地利用这些信息是非常重要的。
mysql之explain
fz13768884254的博客
03-05 460
explain:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈 建表语句： CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id)); CREATE TABL...
MySQL索引——Explain
Chen_chenjiasheng的博客
06-27 682
数据类型 描述 DATE() 日期: 格式：YYYY-MM-DD 注释: 支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’ DATETIME() 日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ TIMESTAMP() 时间戳: TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式：YY
MYSQL索引 EXPLAIN
qq_42682991的博客
05-19 181
MySQL聚集索引和非聚集索引 - 爱写bug的程序员 - 博客园MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类，理解聚集索引和非聚集索引可通过对比汉语字典的索引。汉语字典提供了两类检索汉字的方式，第一类是拼音检索（前提是知道该汉字读音），比如https://www.cnblogs.com/zsmzsm/p/15596877.html数据库常见知识点总结-最左前缀匹配原则、EXPLAIN 命令详解 - xuwc - 博客园参考： https://blog.csdn.net/kqqkqq
MYSQL explain 执行计划
09-11
MySQL的`EXPLAIN`命令是数据库管理员和开发者用于分析SQL查询执行计划的重要工具。通过`EXPLAIN`，我们可以深入理解MySQL如何处理`SELECT`语句，优化查询性能，特别是涉及多表连接的情况。以下是`EXPLAIN`输出的各列...
MySql中如何使用 explain 查询 SQL 的执行计划
09-09
在MySQL中，`EXPLAIN`命令是一个强大的工具，用于分析和理解SQL查询的执行计划。通过使用`EXPLAIN`，我们可以深入了解查询优化器如何处理SQL语句，从而优化查询性能。下面我们将详细介绍如何使用`EXPLAIN`以及如何...
mysql索引和explain的详解
12-14
索引原理分析 索引存储结构 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使使用不同的索引 MyISAM和InnoDB存储引擎：只支持B+ TREE索引， 也不能够更换 MEMORY/HEAP存储引擎：支持HASH和BTREE索引 B树图示...
mysql执行计划介绍
09-10
MySQL执行计划是数据库管理系统在处理SQL查询时的预估策略，它是分析和优化SQL语句性能的关键工具。通过查看执行计划，我们可以了解MySQL如何解析、优化以及执行SQL语句，从而找出潜在的性能瓶颈，提升数据库性能。 ...
mysql explain索引_mysql explain与索引
weixin_39674293的博客
02-01 38
mysql explain与索引person表结构如下:+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | ...
mysql explain索引_mysql之explain使用详解(分析索引)
weixin_33293848的博客
01-19 158
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain就可以了，如：explain select * from statuses_status where id=11;explain列的解释table：显示这一行的数据是关于哪张表的type：这是重要的列，显示连接使用了何种类型。从最...
mysql explain 索引执行分析
仅做个人笔记
06-17 211
转自：https://www.cnblogs.com/tufujie/p/9413852.html 仅作个人备份，浏览请看原文 在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及.
Mysql-Explain与索引详解
qq1071643762的博客
04-20 156
Mysql系列文章目录 提示：使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 文章目录Mysql系列文章目录前言一、Explain分析explain中的列二、索引实践1.全值匹配2.最左前缀法则3.不在索引列上做任何操作（计算、函数、（自动or手动）
MySQL 之explain(分析索引最佳使用)
程序员学习圈
01-28 436
Mysql中有专门负责优化SELECT语句的优化器模块，主要通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间）. 1.explain是什么     查看执行计划，使用 explain 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，
Mysql索引与Explain结合优化SQL
NingYunYuYi的博客
07-18 845
目录 1、索引 2、借助Explain 分析 sql 语句 3、案例分析: 4、总结 1、索引   官方定义：索引（Index） 是帮助 MySQL 高效获取数据的数据结构。 索引为什么是一种数据结构，它又是怎么提高查询的速度？我们拿最常用的二叉树来分析索引的工作原理。如下图： 创建索引的优势： 1. 提高数据的检索速度，降低数据库 IO 成本：使用索引的意义就是通过缩小表...
mysql索引实例-explain-type类型分析
qq_38331606的博客
08-09 851
知识概括 a. all 全表扫描 b. index 按照索引顺序的全表扫描 c. range 有范围的索引扫描 ,between，and以及'>','<'外，in和or均是索引范围扫描 d. ref 使用了非主键或者非唯一索引的普通索引(即索引可以重复情况下的) e....
mysql的sql执行计划详解（非常有用）
热门推荐
Gavin's Blog
05-11 2万+
引言： 实际项目开发中，由于我们不知道实际查询的时候数据库里发生了什么事情，数据库软件是怎样扫描表、怎样使用索引的，因此，我们能感知到的就只有 sql语句运行的时间，在数据规模不大时，查询是瞬间的，因此，在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大，如千万、亿的时候，我们运 行同样的sql语句时却发现迟迟没有结果，这个时候才知道数据规模已经限制了我们查询的速度。所以，查询优
mysql的explain执行计划
08-12
MySQL的EXPLAIN是用于分析查询语句的执行计划的工具。它可以帮助开发人员优化查询语句，提高查询性能。 要使用EXPLAIN，只需在查询语句前加上关键字EXPLAIN。例如： ``` EXPLAIN SELECT * FROM table_name WHERE ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值