mysql性能优化之explain

～快乐小青蛙～

于 2024-09-02 16:02:45 发布

阅读量478

点赞数 12

分类专栏： mysql 文章标签： mysql 性能优化数据库

本文链接：https://blog.csdn.net/qq_19887735/article/details/141822146

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

我不是一个资深高手，只想描述普通人在项目中真正常见的问题，以及我的一些经验！

EXPLAIN作为MySQL的性能分析神器，读懂其结果是很有必要的,针对此工具单独开篇聊一下就具备了很大的必要性。

explain是什么
explain是用来分析SQL的执行计划的工具。现实工作中，我们遇到查询缓慢，定位到sql执行缓慢时，认为的经验判断虽然很重要，但是最依赖的肯定是explain工具，因为此工具会帮我们把实际的sql执行计划结果展示出来，让我们快速定位，整体哪里最慢，为什么慢。

explain能做什么
1.展示读取表（包含sql中临时表）顺序

2.命中索引情况：哪些索引命中，命中的情况

3.每张表有行数检索情况

如何使用explain
直接在运行sql 前加上explain关键字即可

explain返回结果格式

explain返回结果解读
id: 该语句的唯一标识。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。
select_type: 查询类型，有如下几种取值

    SIMPLE:简单查询（未使用UNION或子查询）
    PRIMARY:最外层的查询
    UNION: 在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含，那么它的第一个          SELECT会被标记为DERIVED。
     DEPENDENT UNION: UNION中的第二个或后面的查询，依赖了外面的查询
     UNION RESULT: UNION的结果
     SUBQUERY: 子查询中的第一个 SELECT
     DEPENDENT SUBQUERY: 子查询中的第一个 SELECT，依赖了外面的查询
     DERIVED: 用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table（派生表），因为该临时表是从子查询派生出来的
     DEPENDENT DERIVED: 派生表，依赖了其他的表
     UNCACHEABLE SUBQUERY: 子查询，结果无法缓存，必须针对外部查询的每一行重新评估
     UNCACHEABLE UNION: UNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询

table：表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名
partitions：当前查询匹配记录的分区。对于未分区的表，返回null
type：连接类型，有如下几种取值，性能从好到坏排序如下：

 system：该表只有一行（相当于系统表），system是const类型的特例
 const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
 eq_ref：当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const。
 ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
     最左前缀原则：指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)，那么，如果查询条件是：WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用该索引；WHERE column1 = 2、WHERE column1 = 1 AND column3 = 3就无法匹配该索引。
 fulltext：全文索引
 ref_or_null：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询 （ps：此处也是我们需要在建表时强调为什么非null的原因）
 index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引

 unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引

 index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引

 range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。

 index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：

  如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。

 按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。

  ALL：全表扫描，性能最差。

possible_keys：展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。
key：表示MySQL实际选择的索引
key_len:索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。
ref: 表示将哪个字段或常量和key列所使用的字段进行比较
rows: MySQL估算会扫描的行数，数值越小越好。
filtered:表示符合查询条件的数据百分比，最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。