MySQL索引——Explain

已于 2022-02-21 22:43:43 修改
阅读量671 收藏 3
点赞数
文章标签： mysql
于 2021-06-27 23:37:54 首次发布
原文链接：https://www.bilibili.com/video/BV1KW411u7vy?p=31&spm_id_from=pageDriver
版权

MySQL索引——Explain

1.1 Explain是什么？

  • 主键自动建立唯一索引。
  • 频繁作为查询条件的字段应该创建索引。
  • 查询中与其他表关联的字段，外键关系建立索引。
  • 单键/组合索引的选择问题，组合索引性价比更高。
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
  • 查询中统计或者分组字段。

1.2 Explain能干什么？

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引可以被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

1.2.1 怎么用

  • explain + SQL
  • 执行计划包含的信息
+----+-------------+-------+-------+---------------+-----+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+-----+---------+------+------+-------+

1.3 Explain结果分类(id、type、key为关键指标)

1.3.1 Explain-id(区别顺序)

  1. Explain-id相同，执行顺序由上至下
  2. Explain-id不相同，若事子查询，id的需要会递增，id值越大优先级越高，越先被执行

1.3.2 Explain-select_type(区别查询类型)

  1. SIMPLE - 简单的select查询，查询中不包含子查询或者UNION
  2. PRIMARY - 查询中若包含任何复杂的子部分，最外层查询被标记为
  3. SUBQUERY - 在SELECT或WHERE列表中包含了子查询
  4. DERIVED - 在FORM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
  5. UNION - 若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FORM子句的子查询中，外层SELECT将被标记为：DERIVED
  6. UNION RESULT - 从UNION表获取结果的SELECT

1.3.3 Explain - table (区分表名)

1.3.4 Explain - type(访问类型排列)

  • 显示查询使用了何种类型
  • 最好到最差依次是 
    • system > const > eq_ref > ref > range > index > All
  1. system : 表中只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计 
    • select * from t1
  2. const : 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于Where列表中，MySQL就将该查询转换为一个常量 
    • select * from (select * from t1 where id = 1) d1
  3. eq_ref : 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 
    • select * from t1, t2 wgere t1.id = t2.id
  4. ref : 非唯一性索引扫描，返回匹配某个单独值直的所有行本质上也是一种索引访问，它返回所有四配某心单独值的行，然而，它可能会找到多个符合条件的行，所以他他应该属于查找和扫描的混合体
  5. range : 只检索给定范围的行,使用一个索引来选 择行。 key列显示使用了哪个索引一般就是在你的where语句中出现了betweeen、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好， 因为它只需要开始于索引的某一点，而结束语另一点,不用扫描全部索引。
  6. index : Full Index Scan， index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表， 但index是从索引中读取的，而all是从硬盘中读的)
  7. all : Full Table Scan，将遍历全表以找到匹配的行

一般来说，要保证查询至少达到range级别，最好能达到ref

1.3.5 Explain - possible_keys(可能用到的索引)

  • 查询设计到的字段上若存在索引，则该索引被列出，但不一定被查询实际使用

1.3.6 Explain - key(实际用到的索引)

1.3.7 Explain - key_len(索引中使用的字节数)

  • 可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。
  • key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是更具表定义计算而得，并不是通过表内检索出的。

1.3.8 Explain - ref(显示索引的哪一列被使用了)

  • 如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

1.3.6 Explain - rows(根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。)

  • 建完索引后

1.3.6 Explain - Extra(包含不适合在其他列中显示，但十分重要的额外信息)

  1. Using filesort : 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取 MySQL中无法利用索引完成的排序操作称为"文件排序"
  2. Using temporary :使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by和分组查询group by。
  3. Using index :
  • 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的娄收据行，效率不错!
  • 如果同时出现usingwhere，表明索引被用来执行索引键值的查找;
  • 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
  1. Using where : 使用了where过滤
  2. Using join buffer : 使用了连接缓存(配置中的join buffer可以调大？)
  3. impossible where : where子句的值总是false，不能用来获取任何元组
  4. select tables optimized away : 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*操作，不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。
  5. distinct : 优化distinct操作，在找到第一匹配的元组后即停止找同样

1.4 例题热身

  • 第一行(执行顺序4): id列为1，表示是un on里的第一个select，select_type列的prima ry表示该查询为外层查询，table列被标记为<derived3>，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。
    • 【select d1.name....】
  • 第二行(执行顺序2): id为3，是整个查询 中第三个select的一部分。因查询包含在from 中，所以为derived。
    • 【select idname from t1 where other column="】
  • 第三行(执行顺序3): select列表中的子查 询select_type为subquery，为整个查询中的 第二个select。
    • 【select id from t3】
  • 第四行 (执行顺序1): select_type为union 说明第四个select是union里的第二个selec t，最先执行
    • 【select name,id from t2】
  • 第五行(执行顺序5): 代表从union的临时表中读取行的阶段，table列的<union14>表示示用第一个和第四个select的结果进行
    • 【两个结果union集合】

参考尚硅谷视频

陈賝
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL——索引与EXPLAIN
等一杯咖啡的博客
12-05 1297
前言 本文内容主要参考自《高性能MySQL》第5章以及《MySQL DBA 修炼之道》书中的第三章，算是原书的实践与补充。 上次主要讲了MySQL的基本操作，这次来谈谈索引与EXPLAIN。 I. 什么是索引？ 想要深入的学习MySQL相关技术，而不仅仅停留在简单CURD，能够写出百万数据中分分钟查出需要数据的SQL，首先就需要掌握索引技术。那么什么是索引呢？ 要理解MySQL中索引是如何工作的，...
mysql索引和explain的详解
fajing_feiyue的博客
04-19 404
索引原理分析 索引存储结构 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使使用不同的索引 MyISAM和InnoDB存储引擎：只支持B+ TREE索引， 也不能够更换 MEMORY/HEAP存储引擎：支持HASH和BTREE索引 B树图示 B树是为了磁盘或其它存储设备设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。 多叉平衡。 B树和B+树的区别：...
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
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介绍
冒旭的博客
03-27 423
    explain描述MySQL如何执行查询操作、执行顺序，使用到的索引，以及MySQL成功返回结果集需要执行的行数。    explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们的查询，让查询优化器能够更好的工作。    explain关键字放在select查询语句的前面。    语法：explain select  *  from t1;     ...
MySQL索引优化Explain详解
01-21
找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL...
Mysql实验之使用explain分析索引的走向
09-09
索引是mysql的必须要掌握的技能，同时也是提供mysql查询效率的手段。通过以下的一个实验可以理解？mysql的索引规则，同时也可以不断的来优化sql语句
Explain详解与索引最佳实践
weixin_39585051的博客
07-19 404
Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是 执行这条SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 Explain分析示例 参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html 1 示例表： 2 D
Mysql索引与Explain结合优化SQL
NingYunYuYi的博客
07-18 840
目录 1、索引 2、借助Explain 分析 sql 语句 3、案例分析: 4、总结 1、索引   官方定义：索引（Index） 是帮助 MySQL 高效获取数据的数据结构。 索引为什么是一种数据结构，它又是怎么提高查询的速度？我们拿最常用的二叉树来分析索引的工作原理。如下图： 创建索引的优势： 1. 提高数据的检索速度，降低数据库 IO 成本：使用索引的意义就是通过缩小表...
MySQL索引-EXPLAIN
qq_34819372的博客
08-13 174
一、Explain 使用 EXPLAIN + SQL语句 EXPLAIN SELECT * FROM mk_***_ad_day_main WHERE `ad_id` = '1694**26445' and `stat_datetime` = '2021-08-13 00:00:00'; 二、参数解释 1、id 优先级和先后顺序 id越大的优先级高，优先级高的先执行 id相同，执行顺序由上到下 2、select_type 查询的类型 普通查询：simple 子查询： ...
mysql explain 索引_mysql之explain详解（分析索引的最佳使用）
weixin_29358723的博客
02-17 259
在这里对explain的各个字段进行详细的分析，来帮助大家分析自己所写的sql是否最佳的使用了索引。首先是select_type:将select查询分为简单(simple)和复杂两种类型复杂类型又分为子查询(subquery)和from列表中包含子查询(drived)simple:drived:就type进行详细的介绍：System,const,eq_ref,ref,range,index,all...
Mysql索引及explain
TigerwolfC的博客
03-12 658
索引：是一种数据结构，以增加存储开销和减慢DML(增、删、改)操作来提高查询速度。 常见的索引结构:btree索引（myisam，innodb，memory，heap），hash索引（memory，heap）。 索引本身也很大，不可能全部存储在内存中，所以索引往往以索引文件的形式存储在磁盘上。 常见的索引包括：主键索引、唯一索引、普通索引、全文索引 注意：查询一次后会将这次的查询结果缓存起...
mysql中索引利用情况（explain用法）
热门推荐
Jackson Zhang
06-06 1万+
使用explain查看，如下 1、首先创建表test，语句如下 create table test(a int,b varchar(10),c varchar(10)); 2、在表中的a，b都创建索引，先后顺序是a,b create index idx_a_b on test(a,b); 3、分别往里面插入三条数据，插入后结果如下： 4、使用explain查询，查看索引使用情况（这
MySQL索引及Explain及常见优化
dengmian2109的博客
05-26 116
MySQL索引设计的原则 1. 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE 子句中的列，或连接子句中指定的列，而不是出现在SELECT 关键字后的选择列表中的列。 2. 使用惟一索引。考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区...
mysql explain 索引执行分析
仅做个人笔记
06-17 209
转自：https://www.cnblogs.com/tufujie/p/9413852.html 仅作个人备份，浏览请看原文 在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及.
mysql索引优化explain详解和实战
Twist丶Fate JA158106
03-18 150
mysql索引优化案例和解析提示：以下是本篇文章正文内容，下面案例可供参考。
mysql查询中利用EXPLAIN优化索引（简单易懂小白向）让你快速上手，适用于江湖救急！！！
最新发布
qq_61395554的博客
08-08 347
一定要按照顺序加进去，不然查询速度会慢的超乎你想象，同时不要跳过索引中的字段，也就是说如果索引有a.b.c三个字段，如果你查询的条件里没有a，那索引就不生效，或者中间跳过了b也不生效，其他的ab，abc，ac，a都会生效。与一些博客说的不同，select * 不一定会导致索引失效，但是仅仅只是查询需要的字段就会减少很多时间，而且如果需要的字段少，走覆盖索引速度会非常快。里面记录的就是正在使用的索引，如果你有多个索引，那要看看你的查询语句是否走的是你希望的那个索引。
mysql索引优化 explain
05-26
在MySQL中，优化查询的一个重要手段就是使用索引。EXPLAIN是用来分析查询语句的工具，可以通过分析EXPLAIN输出结果来进行索引优化。下面是一些常用的优化技巧： 1. 索引覆盖：通过使用覆盖索引，可以避免MySQL访问表格数据，从而提高查询速度。如下所示： ``` EXPLAIN SELECT id FROM table WHERE col1 = 'value'; ``` 在这个查询中，只需要访问索引就可以返回结果，不用访问表格数据。 2. 索引选择：使用合适的索引可以提高查询的效率。一般来说，选择最左前缀列的索引可以让MySQL选择更加有效的索引。如下所示： ``` EXPLAIN SELECT * FROM table WHERE col1 = 'value' AND col2 = 'value2'; ``` 在这个查询中，如果存在(col1, col2)的复合索引，则MySQL会选择它来执行查询，这样可以避免全表扫描。 3. 避免使用函数：在查询中，使用函数会导致MySQL无法使用索引，从而降低查询效率。如下所示： ``` EXPLAIN SELECT * FROM table WHERE YEAR(date_col) = '2021'; ``` 在这个查询中，使用YEAR函数会导致MySQL无法使用(date_col)的索引来执行查询，可以考虑将查询条件改为： ``` EXPLAIN SELECT * FROM table WHERE date_col >= '2021-01-01' AND date_col < '2022-01-01'; ``` 4. 避免使用OR：在查询中，使用OR会导致MySQL无法使用索引，从而降低查询效率。如下所示： ``` EXPLAIN SELECT * FROM table WHERE col1 = 'value1' OR col2 = 'value2'; ``` 在这个查询中，可以考虑将查询条件改为： ``` EXPLAIN SELECT * FROM table WHERE col1 = 'value1' UNION SELECT * FROM table WHERE col2 = 'value2'; ``` 5. 避免全表扫描：全表扫描是一种效率比较低下的查询方式，可以通过优化查询条件或者添加合适的索引来避免全表扫描。如下所示： ``` EXPLAIN SELECT * FROM table WHERE col LIKE '%value%'; ``` 在这个查询中，可以考虑将查询条件改为： ``` EXPLAIN SELECT * FROM table WHERE col LIKE 'value%'; ``` 这样可以使用(col)的索引来执行查询，避免全表扫描。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值