MySQL的Explain 及SQL优化的一般步骤_mysql 执行计划 using temporary-CSDN博客

本文链接：https://blog.csdn.net/jsxztshaohaibo/article/details/124661750

本文详细介绍了SQL查询的执行计划分析，包括type、rows、filtered、extra等关键指标的含义及其对查询效率的影响。讲解了如何通过explain分析SQL性能，以及关注点如全表扫描、索引使用、覆盖索引、索引下推等。同时，提到了慢查询日志的配置和使用，以及优化步骤，如分析慢查询、使用showprofile和trace等工具。通过对这些工具和概念的理解，可以有效提升SQL查询性能。

摘要由CSDN通过智能技术生成

Explain

使用 explain select * from tableA 查看SQL语句的执行计划时，需要重点关注type、rows、filtered、extra。

Type

由上至下，效率越来越高

ALL：：全表扫描。从硬盘中读取
index：：全表扫描。跟ALL的区别是：使用索引进行遍历索引树读取
range ：：使用索引范围扫描。就是针对一个有索引的字段，在指定范围中检索数据常用语<,<=,>=,between,in等操作
ref：：使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中。数据查询的时候如果命中的索引是二级索引不是唯一索引，测试查询速度也会很快，但是type是ref。另外如果是多字段的联合索引，那么根据最左匹配原则，从联合索引的最左侧开始连续多个列的字段进行等值比较也是ref的类型。
eq_ref ：：类似ref，区别在于使用的是唯一索引，使用主键的关联查询。在进行数据查询的过程中，如果SQL语句中在表连接情况下可以基于聚簇索引或者非null值的唯一索引记性数据扫描，那么此时type对应的值就会显示为eq_ref
const 如果type是const，说明在进行数据查询的时候，命中了primary key或唯一索引，此类数据查询速度非常快。
system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
null MySQL不访问任何表或索引，直接返回结果
虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为"select * from t where a = 1 and b in (1, 2) order by c";如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2

Extra

Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。【不是使用select * ，而是使用select phone_number，就会用到覆盖索引。】
Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
Using where 查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。
Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

possible_keys

表示哪些索引可以被Mysql的优化器进行选择，也就是索引候选者有哪些。

key

在possible_keys中实际选择的索引

key_len

表示索引的长度，和实际的字段属性以及是否为null都有关系。

rows

显示MySQL认为它执行查询时必须检查的行数。行数越少，效率越高！

filtered

这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下的记录满足条件的记录数量的比例。

一般的优化步骤

1、通过慢查日志等定位那些执行效率较低的SQL语句

查看是否开启慢查询：


show variables like "%slow%";

| slow_launch_time | 2 |  超过2秒定义为慢查询。
| slow_query_log | OFF |  慢查询关闭状态。
| slow_query_log_file | /data/mysql/var/db-Test2-slow.log | 慢查询日志的文件。



开启慢查询：

set global slow_query_log=on;
[mysqld]
log-slow-queries = /data/mysql/var/db-Test2-slow.log #日志目录。 
long_query_time = 1   #记录下查询时间查过1秒。 
log-queries-not-using-indexes  #表示记录下没有使用索引的查询。

2、explain 分析SQL的执行计划

3、show profile 分析

QL执行的线程的状态及消耗的时间。

默认是关闭的，开启语句“set profiling = 1;”

SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

4、trace

trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;