Mysql基础知识之通过EXPLAIN分析SQL的执行计划及分页优化示例

作者: JONE 分类: 编程 发布时间: 2020-08-29 23:39

ID列

ID列中的数据为一组数字,表示执行 SELECT语句的顺序
ID值相同时,执行顺序由上至下
ID值越大优先级越高,越先被执行
ID值为null,

SELECT TYPE列

SIMPLE:不包含子查询或是UNION操作的查询
PRIMARY:查询中如果包含任何子查询,那么最外层的查询则被标记为PRIMARY
SUBQUERY SELECT:列表中的子查询
DEPENDENT SUBQUERY:依赖外部结果的子查询
UNION:Union操作的第二个或是之后的查询的值为 union
DEPENDENT UNION:当UNON做为孑查询时,第二或是第二个后的查询的select type值
UNION RESULT:UNoN产生的结果集
DERIVED:出现在FROM子句中的子查询

TABLE列

输出数据行所在的表的名称
< unionM,N>由ID为M,N查询union产生的结果集
< derivedN>/< subqueryN>由ID为N的查询产生的结果

PARTITIONS列

对于分区表,显示查询的分区|D
对于非分区表,显示为NULL

TYPE列

system:这是 const联接类型的一个特例当查询的表只有一行时使用
const:表中有且只有一个匹配的行时使用,如对主键或是唯一索引的查询,这是效率最高的联接方式
eg_ref:唯一索或主键引查找,对于每个索引键,表中只有一条记录与之匹配
ref:非唯一索引查找,返回匹配某个单独值的所有行
ref_or_null:类似于ref类型的查询,但是附加了对NUL值列的查询
Index_ mer/ge:该联接类型表示使用了索引合并优化方法;
ranae:索引范围扫描,常见于 between、>、<这样的查询条件
index5:FUbL index Scan全索引扫描,同AL的区别是,遍历的是索引树
ALL:FULL  TALBLE  Scan全表扫描,这是效率最差的联接方式

Extra列

Distinct:优化 distinct操作,在找到第一匹配的元组后即停,止找同样值的动作
Not exists:使用 not exists来优化查询
Using filesort:使用额外操作进行排序,通常会出现在 order by或 group by查询中
Using index:使用了覆盖索引进行查询
Using temporary:MySQL需要使用临时表来处理查询,常见于排i序,子查询,和分组查询
Using where:需要在 MySQL服务器层使用 WHERE条件来过滤数据
select tables/optimized away:直接通过索引来获得数据,不用访问表

KEY列

查询优化器优化查询实际所使用的索引
如果没有可用的索引,则显示为NULL
如查询使用了覆盖索引,则该索引仅出现在Key列中

执行计划的限制

无法展示存储过程,触发器,UDF对查询的影响
无法使用 EXPLAIN对存储过程进行分析
早期版本的 MySQL只支持对 SELECT语句进行分析

评论分页数据优化

customer_id为主键,覆盖索引 product_id、audit_status(区分度最高的列放在联合索引的最左边),这钟优化的话是通过索引

当查询的的条件和查询的结果都是索引中的字段的时候,这个索引我们可以称之为覆盖索引

优化说明,通过联合索引取出分页后的5条数据的主键id,再通过关联查询获取主键id对应的数据信息。这样的因为数据都是先从索引中获取,节省IO,优化之后每次翻页消耗的时间和资源都是相同的,优化之前的sql语句翻页效果随着页码的增加会越来越慢效率越来越差

#优化前示例
SELECT customer_id, title, content FROM product_comment WHERE audit_status =1 AND product_id=199726 LIMIT 0,5:
#优化后示例
SELECT t.customer_id, t.title, t.content
FROM(
    SELECT comment_id
    FROM product_comment
    WHERE product_id=199727 AND audit_status=1 LIMIT 0,15
) a JOIN product_comment t
oN a.comment_id= t.comment_id;