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

146次阅读
没有评论

<span style=”color: #ff0000;”><strong>ID 列 </strong></span>

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

<span style=”color: #ff0000;”><strong>SELECT TYPE 列 </strong></span>

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

<span style=”color: #ff0000;”><strong>TABLE 列 </strong></span>

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

<span style=”color: #ff0000;”><strong>PARTITIONS 列 </strong></span>

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

<span style=”color: #ff0000;”><strong>TYPE 列 </strong></span>

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

<span style=”color: #ff0000;”><strong>Extra 列 </strong></span>

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:直接通过索引来获得数据, 不用访问表

<strong>KEY 列 </strong>

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

<span style=”color: #ff0000;”><strong> 执行计划的限制 </strong></span>

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

<span style=”color: #ff0000;”><strong> 评论分页数据优化 </strong></span>

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>

正文完
有偿技术支持加微信
post-qrcode
 
评论(没有评论)
验证码