explain详解

最新推荐文章于 2024-02-08 17:36:10 发布
最新推荐文章于 2024-02-08 17:36:10 发布
阅读量2.7k 收藏 2
点赞数 2
分类专栏： 数据库 文章标签： explain mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gogokongyin/article/details/51889814
版权
    explain（Execution plan cost）就是优化器做的事情。执行计划，很重要！！
    只有明白sql是如何执行的，才能更好的优化，先了解它，然后才能优化它，explain就是和mysql优化对话的大门和打开优化器门的钥匙～
问题导读：
  • MySQL一定是按照我们输入的sql执行的吗？
  • 如果不是，那么是如何优化和选择索引的呢？
  • 如何知道MySQL使用了我们创建的那个索引，扫描的行数及是否排序情况？

1、认识explain

认识explain：可以模拟mysql优化器来执行sql语句，从而让我们知道db是如何执行sql的。分析sql和表结果的性能瓶颈。
实操， 语法： explain +sql

1.1、Id：表的读取顺序

Id ：查询的序列号，一组数字，表示查询中执行的select字句或操作表的顺序
  • 相同：自上而下
  • 不相同：id号越大优先级越高，先执行；
  •  相同又不同的子查询顺序满足1-2， 小表驱动大表；
 

1.2、Select_type: sql的操作类型

Select type ： 查询类型，主要用于区别 普通查询／联合查询／子查询等的复杂查询
  • Simple：简单的select查询，查询中不包含子查询或者UNION
  • Primary: 查询中包含任何复杂的字部分，最外层查询被标记为：
  • SUBQUERY:  select或where中包含的子查询
  • Deriverd: 在from中包含的子查询被标记为driverd（衍生），不是真实的表；
  • Union:第二个selet出现union后将标记为nuion；
  • Union result：是对两个sql语句的结果的一个结果合并；

1.3、table：操作的表名称

table：显示这一行的数据属于那张表

1.4、type：索引类型 system > const > eq_ref >( ref >  range )> index > all 

type ：system > const > eq_ref >(  ref >  range  )> index > all 
System: 表只有一行记录，例如系统表；比如lock等待的时间query_lock_wait/long_query_time
const:   指常量查询比如 id = 1 ，  使用主键或者唯一索引等值查询一次命中，因为只匹配一行；
eq_ref:  唯一索引命中，常见于主键索引和唯一索引扫描本表或者关联表， 非常量查询；通过唯一的索引值来匹配，表中只有一条记录与之匹配；
Ref： 查询索引，使用了条件索引，非唯一性索引。 返回一条或多条纪录， 非唯一性索引扫描，返回匹配某个单独值的所有行；本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以它属于 查找和扫描的混合体。
 
range:   对索引使用了范围查询 ，只检索给定范围的行，使用一个索引来选择行，key列显示使用那个索引。一般就是在你的where语句中出现 between/</>/in等查询的时候，这种 范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束另一点，不用扫描全部索引。
Ps:如果id不是表的 in 范围内的，就退化为all了。
 
Index：查询了所有索引的值 ，只是不去扫描整个数据文件； 覆盖索引
 
all：标上没有索引，扫描所有的数据

1.5、 possable key: 可能用到的索引-key

案例：possible key为空，但是key使用了索引，实际上是用到了覆盖索引。

1.6、key：实际用到的key；如果为null，则没有使用索引

Ps: 注意一下possable_key没有值，key有值的情况；

1.7、Ken_len:  索引使用的字节数

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好；
  • 显示的 值为索引字段的最大可能长度，并非实际使用长度，即  key_len 是根据表初定义计算而得，不是通过表内检索内容得出。 
  • 可以用来判断复合索引是否实效的关键指标。我们可以通过该索引的长度大小来看使用了那些索引。
  • char 和 vchar类型：latin 占用1个字节 ，gbk 占用2个字节， utf8 占用 3 个字节
字节大小的计算：
  • char(10)   null  30 + 1;
  • varchar(10) +  null 30 + 2 (字段长度) +1
Utf-8和utf8mb4
  •  为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 
  •  对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR  替代 CHAR。
小结：
  • 复合索引有最左前缀的特性，如果复合索引能全部用上，则是复合索引字段的长度之和，这也可以用来判断复合索引是否部分使用，还是全部使用
  • Null都需要一个字节的额外空间来标识该字段是否为空，所以索引字段最好不要为null，因为null让统计更加复杂，并且需要额外的内存空间。
  • 变长varchar字段需要额外的两个字段来存储是否为空和长度；
整数／浮点数／时间类型的索引长度：
  • Not null = 字段本身的字段长度
  • Null = 字段本身的字段长度 + 1 (因为需要有 是否为空的标记，这个标记需要占用一个字节 )
Datetime 在 5.6 中占用 5 个字节， datetime 在 5.5 中字段长度为 8 个字节；

1.8、Ref:表之间的引用

ref：显示索引的 具体那一列被使用了。
⚠️： 和type=ref区分开

1.9、rows：扫描的记录行数

rows：根据表统计情况及索引选用情况，大致估算出找到所需的记录所需要扫描的行数； 尽量少。
filtered：得到的结果数据和全部扫描的数据的一个百分比，大约不精确， 越大越好：
  • 例如：70%表示扫描了100行共得到70行有效数据；

2.0、extra：额外信息

  1. using filesort： 排序可以优化。mysql无法利用索引完成数据的排序；不是按照表内索引顺序读取数据， 无法利用索引的排序称为：文件排序 ，可能在 次磁盘-(外排序) 或者 内存(内排序)上完成排序，应尽量避免此种情况；
  2. using temporary：mysql 使用了临时表来保存中间结果，常见于排序order by和组查询group by，distinct；
  3. Using index:    使用了索引的排序， 使用了覆盖索引，避免了 回表 ；
  4. using where：表明server使用了 where过滤；
  5. using join buffer：使用了连接缓存； select price from order o   INNER JOIN coupon c on o.id = c.orderid;
  6. impossible where：where子句的值总为false，select * from goods where 1=2；
  7. use index condition（ push down） 使用了 索引下推；
extra实例操作演示：
临时表：
排序：
添加索引：
ALTER TABLE `kinginfo`.`goods` ADD UNIQUE INDEX `create_time_UNIQUE` (`create_time` ASC);
 

2、结束

    认识优化器后，才能更好的掌握sql的执行过程，从而找到优化的点。
 
 
水滴石穿，积少成多。学习笔记，内容简单，用于复习，梳理巩固。
 
##参考资料，
《Innodb存储引擎》
《MySql实战详解》
戎·码一生
关注
专栏目录
mysql use index_MySQL USE INDEX
weixin_39608526的博客
01-19 915
MySQL USE INDEX简介：在本教程中，您将学习如何使用MySQLUSE INDEX提示指示查询优化器仅使用查询的命名索引列表。MySQL USE INDEX提示简介在MySQL中，当您提交SQL查询时，查询优化器将尝试制定最佳查询执行计划。为了确定最佳可能的计划，查询优化器使用了许多参数。选择使用哪个索引的最重要参数之一是存储密钥分发，也称为基数。但是，基数可能不准确，例如，如果表已经...
mysql派生表（Derived Table）简单用法实例解析
09-08
主要介绍了mysql派生表（Derived Table）简单用法,结合实例形式分析了mysql派生表的原理、简单使用方法及操作注意事项,需要的朋友可以参考下
mysql索引
weixin_42906466的博客
09-07 1262
为什么要使用索引 1.可以加快数据的检索速度（大大减少检索的数据量，这也是使用索引的主要原因）。 2.帮助服务器避免排序和临时表 3.将随机IO变为顺序IO 使用索引要注意的事项 1.在经常需要搜索的列上创建，可以加快搜索速度。 2.在经常使用where子句中的列上面创建，加快条件的判断速度。 3.在经常需要排序的列上面创建，因为索引已经排序，这样可以直接使用索引的排序，加快排序查询时间。 4.对于中到大型表创建索引都是非常有效的，但对于超大型表创建索引成本较大，不建议使用。 5.在经常用在连接的列上创建索
MySQL之use index()查询
加油
10-30 5248
语法： SELECT ... FROM TABLE [ {USE|IGNORE|FORCE} INDEX (key_list)] WHERE ... USE INDEX 用来提供你希望MySQL去参考的索引列表，就可以让MySQL不再考虑使用其他可用的索引。 INGNORE INDEX 让MySQL忽略一个或者多个索引。 FORCE INDEX 强制MySQL使用一个特定的索引。 ...
mysql use index() 优化查询
经验之谈，不做搬运工
07-03 171
FORCE INDEX/IGNORE INDEX 的语法： SELECT *** FROM TABLE [{USE|IGNORE|FORCE} INDEX (key_list)] WHERE ……   USE INDEX 在你查询语句中表名的后面，添加 USE INDEX 来提供你希望 MySQ 去参考的索引列 表，就可以让 MySQL 不再考虑其他可用的索引。 Eg:SE...
Explain详解与索引最佳实践.zip
06-18
"Explain详解与索引最佳实践"的主题主要聚焦于如何利用MySQL的`EXPLAIN`命令来理解并优化SQL查询，以及如何有效地设计和利用索引来提升查询效率。 `EXPLAIN`是MySQL提供的一种强大工具，用于分析查询执行计划。当你...
MySQL-Explain 详解
08-07
MySQL Explain 详解 MySQL Explain 是 MySQL 中的一个重要工具，用于解释和优化 SQL 查询语句。通过 Explain，可以了解 MySQL 是如何执行查询语句的，并且可以根据 Explain 的结果来优化查询语句。 一、用法 ...
mysql use index_MYSQL 慎用USE INDEX
weixin_30952319的博客
01-27 524
1. 创建测试表， 并加入记录，创建索引。(testing)root@localhost [test]> create table testa(id int, name varchar(9));Query OK, 0 rows affected (0.04 sec)(testing)root@localhost [test]> insert into testa values(1,'x...
mysql use index用法_MySQL数据库之mysql use index() 优化查询
weixin_39745345的博客
02-01 594
本文主要向大家介绍了MySQL数据库之mysql use index() 优化查询 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。mysqluseindex()优化查询FORCEINDEX/IGNOREINDEX的语法：SELECT***FROMTABLE[{USE|IGNORE|FORCE}INDEX(key_list)]WHERE……USEI...
Mysql中explain作用详解
pengzhisen123的博客
06-24 2073
一、MYSQL的索引索引（Index）:帮助Mysql高效获取数据的一种数据结构。用于提高查找效率，可以比作字典。可以简单理解为排好序的快速查找的数据结构。索引的作用：便于查询和排序（所以添加索引会影响where 语句与 order by 排序语句）。在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据。这样就可以在这些数据结构上实现高级查找算法。这些数据结构就是索...
mysql use index用法_MySQL中USE INDEX 和 FORCE INDEX
weixin_36083434的博客
02-27 1685
问题在一次生产环境排查性能问题时, 发现有个请求在一些用户的数据量比较大的情况下, 最高耗时差不多要3s. 而且还是一个轮询的请求.原因在排查问题时, 定位到是执行某条SQL时在用户的数据比较大的情况下, SQL执行耗时要1.5s.mysql> SELECT count(1)-> FROM-> cc_session cs-> LEFT JOIN users_plat...
MySQL-索引（INDEX）
最新发布
m0_53222084的博客
02-08 2051
索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。
MySQL INDEX
guanjie930423的博客
05-31 420
-- test index with 'and &amp; or' - begin -- type: ref explain select * from student where name = '' and class_id = ''; -- type: all, extra: using where explain select * from student where name = ''...
mysql use index_mysql use index （） 优化查询的例子
weixin_39769740的博客
01-19 108
USE INDEX在你查询语句中表名的后面，添加 USE INDEX 来提供你希望 MySQ 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。Eg:SELECT * FROM mytable USE INDEX (mod_time, name) ...�IGNORE INDEX如果你只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作为 Hint...
关于mysql index
02-15 808
一、参考：What is an index in SQL? ，内容如下：An index is used to speed up searching in the database. MySQL have some good documentation on the subject (which is relevant for other SQL servers as well): http://d
mysql中explain详解
08-19
在MySQL中，EXPLAIN是一个关键字，用于查询SQL语句的执行计划和索引使用情况。使用EXPLAIN可以帮助我们了解MySQL如何处理我们的查询语句，并对查询进行优化。 EXPLAIN命令会返回一个包含12列信息的结果集。这些列包括：id，select_type，table，partitions，type，possible_keys，key，key_len，ref，rows，filtered，Extra。每一列都提供了有关查询执行的详细信息。 - id列表示查询的标识符，常用来区分查询的不同部分。 - select_type列表示查询的类型，例如简单查询、子查询、联合查询等。 - table列表示与查询相关的表名。 - partitions列表示查询涉及的分区信息。 - type列表示查询的访问类型，包括全表扫描、索引扫描、范围扫描等。 - possible_keys列表示可能用到的索引。 - key列表示实际使用的索引。 - key_len列表示索引的长度。 - ref列表示查询中使用的索引引用。 - rows列表示查询返回的行数估计值。 - filtered列表示查询结果的过滤率。 - Extra列提供了额外的信息，如是否使用了临时表、是否使用了文件排序等。 通过分析EXPLAIN的结果，我们可以判断查询是否使用了索引，是否存在全表扫描等问题，从而进行查询性能的优化。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [Mysql的explain详解](https://blog.csdn.net/weixin_44143114/article/details/118526637)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* *3* [MySQL数据库：第十七章：Explain详解](https://blog.csdn.net/java_wxid/article/details/111881486)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值