乐趣区

关于mysql:面试八股文四Explain语句中各个字段分别表示什么

一、什么是 Explain?

1. 应用 explain 能够模仿优化器执行 SQL 查问语句,从而晓得 MySQL 怎么解决你的 SQL 语句的,剖析你的查问语句和表构造的性能瓶颈。

二、Explain 能做什么?

  • 读取表的程序
  • 哪些索引可能被应用
  • 数据读取操作的操作类型
  • 哪些索引可能被理论应用
  • 表之间的援用
  • 每张表有多少行被物理查问

三、Explain 应用的表

1.blog_blog、blog_blogtype、user 三个表,其中 blog 表应用外键一对一关联另外两个表,具体如下

四、Explain 应用举例

1. 执行语句 1(查问博客类型为随笔且作者名称为老胡的博客)

EXPLAIN 
SELECT * FROM blog_blog blog WHERE blog.blog_type_id IN (SELECT id FROM blog_blogtype blogtype WHERE type_name = '随笔')
AND blog.author_id = (SELECT id FROM USER WHERE user_name = '老胡')

2. 执行后果(局部截图)

3.id、table 字段:通过这两个字段咱们能够判断出你的每一条 SQL 语句的执行程序和表的查问程序。在截图中,id 优先级更高,因而第一个查问的是 user 表;当 id 雷同时,怎么看程序呢?自上而下,如这里的 id 都是 1,则自上而下查问的第二个查问的表是 blog,第三个表是 blogtype

4.type 字段(画重点):上面的代码从左到右,越凑近右边越优良

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

①NULL:MySQL 可能在优化阶段合成查问语句,在执行阶段用不着再拜访表或索引,比方咱们晓得 MySQL 底层是 B + 树,叶子节点的第一个就是最大的 id 值,那么咱们很容易查问到最大的 id(当 id 为主键时会默认创立主键索引),所以不必拜访表或索引

// 因为存在主键索引,则 type 为 NULL
EXPLAIN SELECT MAX(id) FROM blog_blog 
// 因为该字段不存在索引,则 type 为 All
EXPLAIN SELECT MAX(title) FROM blog_blog

因而咱们得出结论:NULL 的前提是曾经建设了索引

②SYSTEM:只有一行记录(等于零碎表),这是 const 类型的特列,平时不大会呈现,能够疏忽。

③const:示意通过索引一次就找到了,const 用于比拟 primary key 或 uique 索引,因为只匹配一行数据,所以很快,如主键置于 where 列表中,MySQL 就能将该查问转换为一个常量。

// 依据索引查问一次失去构造,type 类型为 const
//TODO:为啥 id= 1 和 id= 2 的时候 type 的类型为 NULL?EXPLAIN SELECT * FROM blog_blog WHERE id = 3

④eq_ref:用于联表查问的状况,按联表的主键或惟一键联结查问。
多表 join 时,对于来自后面表的每一行,在 以后表中只能找到一行 。这可能是除了 system 和 const 之外最好的类型。当主键或惟一 非 NULL索引的所有字段都被用作 join 联接时会应用此类型。

//blogtype 中的 id 值是主键(不可反复),所以满足 blogtype 的表的类型为 eq_ref
EXPLAIN SELECT * FROM blog_blog  blog LEFT JOIN blog_blogtype blogtype ON blog.blog_type_id = blogtype.id 

⑤ref:能够用于单表扫描或者连贯。如果是连贯的话,驱动表的一条记录可能在被驱动表中通过 非惟一(主键)属性所在索引中 匹配多行数据,或者是在单表查问的时候通过非惟一(主键)属性所在索引中查到一行数据。

//1. 在连贯中,不懂的同学能够和下面的 eq_ref 比照着看
//blogtype 中的 id 值不是主键(可反复),一个 blog_type_id 可能对应着好几个 blogtype.id
EXPLAIN SELECT * FROM blog_blog  blog LEFT JOIN blog_blogtype blogtype ON blog.blog_type_id = blogtype.id 
//2. 在单表查问中,咱们对 blog 中的 title 属性建设一般索引
CREATE INDEX index_title ON blog_blog (title(50))
// 留神,这里的 title 是有索引的,且不是主键,且不惟一
EXPLAIN SELECT * FROM blog_blog WHERE title = "随笔 2"

⑥ref_or_null 相似 ref,然而能够搜寻值为 NULL 的行

EXPLAIN SELECT * FROM blog_blog WHERE title = "随笔 2" OR title IS NULL

⑦index_merge:示意查问应用了两个以上的索引,最初取交加或者并集,常见 and,or 的条件应用了不同的索引,官网排序这个在 ref_or_null 之后,然而实际上因为要读取多个索引,性能可能大部分工夫都不如 range。

// 这里同时应用了主键索引 id 和一般索引 title
EXPLAIN SELECT * FROM blog_blog  WHERE title = "随笔 2" OR id = 2

⑧range:索引范畴查问,常见于应用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者 like 等运算符的查问中。

// 有索引且是范畴查问
EXPLAIN SELECT * FROM blog_blog  WHERE blog_type_id > 1

⑨index:index 只遍历索引树,通常比 All 快。因为,索引文件通常比数据文件小,也就是尽管 all 和 index 都是读全表,但 index 是从索引中读取的,而 all 是从硬盘读的

// 有索引但须要遍历索引树
EXPLAIN SELECT id FROM blog_blog 

⑩ALL:如果一个查问的 type 是 All, 并且表的数据量很大,那么请解决它!

5.possible_keys:可能应用的索引

6.key:理论应用的索引

7.ref:显示哪些列被应用了

8.rows 和 filter:

  • rows 是依据表的统计信息和索引的选用状况,优化器大略帮你估算出你执行这行函数所须要查问的行数。
  • Filter 是查问的行数与总行数的比值。其实作用与 rows 差不多,都是数值越小,效率越高。

9.extra:很重要然而在其余列不适宜显示的额定信息

退出移动版