Mysql & Explain使用

1,262 阅读5分钟

持续创作,加速成长!这是我参与「掘金日新计划 · 6 月更文挑战」的第12天,点击查看活动详情

Explain 是什么

EXPLAIN语句提供有关 MySQL 如何执行语句的信息。与 SELECT,DELETE,INSERT,REPLACE,UPDATE语句一起使用。 EXPLAIN,SELECT为语句中使用的每个表返回一行信息 。它按照 MySQL 在处理语句时读取它们的顺序列出输出中的表。MySQL 使用嵌套循环连接方法解析所有连接。这意味着 MySQL 从第一个表中读取一行,然后在第二个表、第三个表中找到匹配的行,依此类推。处理完所有表后,MySQL 会输出选定的列并通过表列表回溯,直到找到匹配行较多的表。从此表中读取下一行,并继续处理下一个表。
使用 Explain 关键字可以模拟优化器执行sql查询语句,分析查询语句或者是表结构的性能瓶颈。 image.png 官方地址:dev.mysql.com/doc/refman/…

Explain 如何使用

语法:Explain + sql
测试:

EXPLAIN select * from sys_order

输出结果:

image.png

返回的结果列所代表的含义

id: select 查询的序列号,包含一组数字表示查询中执行select子句或者操作表的顺序
  1. id相同,执行顺序由上至下
例如:

EXPLAIN select t1.* from sys_order as t1 ,sys_shop as t2,sys_user as t3  
where  t1.order_shop_id = t2.shop_id and t1.order_entering_userid = t3.xk_user_id

输出结果:

image.png
  2. id不同,如果子查询id的序号会递增,id值越大优先级越高,越先被执行
例如:

EXPLAIN select t1.* from sys_order as t1 where 
order_shop_id =(select t2.shop_id from sys_shop as t2 where t1.order_shop_id = t2.shop_id)

输出结果:

image.png


  3. id相同不同同时存在,id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

select_type: 查询类型,主要用于区别 普通查询丶联合查询丶子查询等复杂查询。
 1.SIMPLE: 简单的查询,查询中不包含子查询或者UNION
 2.PRIMARY: 查询中若包含复杂的子部分,最外层查询则被标记
 3.PRIMARY: 在SELECT或WHERE列表中包含子查询
 4.DERIVED: 在FROM列表中包含的子查询被标记为DERIVED,MYSQL会递归执行这些子查询,把结果放在临时表。
 5.UNION:若第二个SELECT出现在UNION之后,则被标记为UNION 若UNION包含在FROM子语句的子查询中,外层SELECT将被标记为:DERIVED
 6.UNION RESULT: 从UNION表获取结果的SELECT
table加载的表名
type: 访问类型 显示查询使用了何种类型。
   从最好到最差依次是:system > const > eq_ref > ref > range > index > All(常见的,一般来说的保证查询至少达到range)

  1. system : 表中只有一行记录这是const类型的特列,平时不会出现。可以忽略
  2. const:表示通过索引一次就找到了,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快就主键置于where 列表中,mysql就能讲该查询转换为一个常量

image.png

  1. eq_ref: 唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或者唯一索引扫描

image.png 4. ref: 非唯一性索引扫描,返回匹配摸个单独值的所有行,本质上也是一种索引访问,他返回的匹配某个单独值的行,可能返回多个符合条件的行。所以它属于查找和扫描的混合体

image.png 5. range: 只检索给定范围的行,使用一个索引来选择行,比全表扫描要好。因为它只需要开始于索引的某一点,而结束语另一点。不用扫描全部索引。即就是where语句中出现between < > in等范围条件查询

image.png 6. Index:Full Index Scan inde与ALL区别为index类型只遍历索引树。这通常比All快,因为索引文件通常比数据文件小。(ALL是从硬盘中读取数据,index是从索引中读取的)

image.png 7. ALL:Full Table Scan,将遍历全表以找到匹配的行

image.png possible_keys: 显示可能用到的索引,一个或者多个。但不一定被查询实际使用
测试:理论上 PRIMARY,uq_order_id 实际上使用 的索引 uq_order_id image.png

测试:理论上用不到索引但是实际使用了索引 uq_order_id image.png key: 实际使用的索引,如果为NULL ,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中
key_len: 表示索引中使用的字节数,在不损失精确性的情况下,长度越短越好,key_len 显示的值为索引字段的最大可能长度,并非实际使用长度。即key_len是根据表设计定义即算而得,不是通过表内检索出的。
ref: 显示索引的那一列被使用。如果可能的话是一个常数,用于查找索引列上的值

image.png rows: 索引信息统计及引用情况,可以计算出记录所需读取的行数。(也可以在创建索引前后进行对比获取到优化查询条数),此数值越小越好。
extra: 显示不在前列的额外信息:

  1. Using filesort: 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MYSQL中无法利用索引完成的顺序操作称为“文件排序”。(出现Using filesort sql性能需要优化)
  2. Using temporary: 使用临时表保存了中间结果。常见于排序 order by和分组查询 group by。(出现Using temporary sql性能需要优化比 Using filesort更严重)
  3. USING index:表示使用了覆盖索引(Covering Index) 避免访问表的数据行,效率不错!如果同时出现using where 表明索引被用来执行索引键值的查找,如果没有同时出现 using where 表明索引用来读取数据而非执行查找动作。
  4. suing where:使用了where过滤
  5. using join buffer:使用了链接缓存
  6. impossible where: where自居的值总是false 不成立也就是获取不到任何数据
  7. select tables optimized away: 在没有 groupby子句 的情况下对于索引的一个优化。
  8. distinct: 优化dictinct操作 ,在找到第一匹配元组后即停止找同样值的动作(可以理解下不重复)

实践是检验真理的唯一方法! 明天见🥰🥰🥰