乐趣区

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

jiezi

一、什么是 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：很重要然而在其余列不适宜显示的额定信息

退出移动版