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

151次阅读

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 语句翻页效果随着页码的增加会越来越慢效率越来越差
<pre># 优化前示例
SELECT customer_id, title, content FROM product_comment WHERE audit_status =1 AND product_id=199726 LIMIT 0,5:</pre>
<pre># 优化后示例
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;</pre>

正文完

有偿技术支持加微信