mysql之explain 分析sql语句

最新推荐文章于 2025-03-21 23:51:00 发布

肥茹

最新推荐文章于 2025-03-21 23:51:00 发布

阅读量256

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_42592326/article/details/106675915

版权

mysql 专栏收录该内容

15 篇文章

订阅专栏

EXPLAIN SELECT * FROM  表名

rows (估算) =>这个是MYSQL在改优化器的执行方案中扫描的大约数据量与实际的数据有多少
在实际项目中,需要做一个某个统计操作的时候需要的数据不一定正确,这个时候就可以使用explain来获取

select_type:查询类型不是核心
SIMPLE:除了子查询或者union之外的查询显示
partitions:参数的使用，SQL优化本质=》减少=》减少数据扫描的范围
type:类型->当前SQL扫描的类型
all: 全表扫描效率是最低的
index:全索引扫描->就是根据索引去获取结果
const 读常量->主键和唯一字段(主键告诉数据库数据存储的位置)
rang: 索引的范围查询
eq_ref:最多只会有一条结果主要是也是唯一索引
possible key:代表这条SQL语句可能使用到的索引是哪些
key:来决定最终会使用的索引
key len: 索引的大小
extra:这里描述的细节信息
null: 如果key中索引标识那么这里是一个问题"回表"->使用了索引,但是效率不一定很高回表问题在索引去解释
using index覆盖索引->就是直接通过索引获取到了数据(效率是最高)
using where根据条件过,虽然可能使用了索引,但是还是会根据where条件对数据进行过滤
using temporary:意味着产生了一个临时表
using filesort:会对于数据在索引外进行了排序操作

回表：

customers 表 id是主键,a，b字段

SELECT * FROM customers WHERE id=1

这样不会产生回表，因为所有的数据在id的索引树中均能找到

SELECT * FROM customers WHERE a=1;

这样就会产生回表，因为where条件是a字段，那么会去a的索引树里查找数据，但a的索引里面只有a,id两个字段的值，没有b，那么这个查询为了取到b字段，就要取出主键id的值，然后去id的索引树去找b字段的数据。查了两个索引树，这就叫回表。

避免回表，可以建立a和b的联合索引，不用*号，只查询用上的字段

覆盖索引:

覆盖索引就是查这个索引能查到你所需要的所有数据，不需要去另外的数据结构去查。

建立索引

alter table 表名 add index 索引名称 (字段名);

alter table customers add index city_monthsalary(city);

查看表的索引

show indexes from 表名;
show indexes from customers;

删除索引

alter table 表名 drop index 索引名称
alter table salary drop index city_monthsalary

查询女性客户数量和平均月薪

未建索引前所需的时间

select count(*),avg(monthsalary) from customers1 where gender = 0;

[SQL]select count(*),avg(monthsalary) from customers1 where gender = 0;
受影响的行: 0
时间: 12.022s

建立联合索引后，再用explain来看看是否用到联合索引

alter table customers1 add index idex_gender_monthsalary(gender,monthsalary);

explain select count(*),avg(monthsalary) from customers1 where gender = 0;

用上了，再查询一次看看几秒，哇哦，差不多快了12s

建立索引要记住最左匹配原则，就是索引会先匹配最左边的字段，上面的语句gender是最左的，索引建立索引时，gender放在monthsalary前面

___________________________________________________________________

profiling分析

mysql的profiler是一个使用非常方便的查询诊断分析工具，通过该工具可以获取一条查询在整个执行过程中多种资源的消耗情况，例如内存消耗、I/O消耗和CPU消耗等。

profile的语法规则如下：

show profile [type [, type] ...]
[for query n]
[limit row_count [offset offset]]



其中type参数的可选项含义如下：
all：显示所有的信息
block io：限时输入输出操作阻塞的数量
context switches：显示自动或非自动context switches的数量
cpu：显示系统和用户cpu使用的时间
ipc：显示信息发送和接受的数量
memory：内存的信息
page faults：显示主要的page faults数量
source：显示函数的名称，并且系那是函数所在文件的名字和行数
swaps：显示swap数量

开启profiling

set profiling =1;
show profiling;

在开启了query profiler功能之后，mysql就会自动记录所有执行的query的profile信息。然后执行sql

SELECT * FROM customers1;

通过执行 show profiles 命令获取当前系统中保存的多个query的profile的概要信息。

show PROFILES;

找到刚刚执行的sql，记录下query_id

针对单个query获取详细的profile的信息在获取概要信息之后就可以根据概要信息中的query_id来获取某一个query在执行过程中的profile信息了

show PROFILE cpu,block io for query 387;