您现在的位置是：网站首页> 编程资料编程资料

一文带你看懂MySQL执行计划_Mysql_

2023-05-26 410人已围观

简介一文带你看懂MySQL执行计划_Mysql_

一、id ，select 查询序列号
二、select_type，查询语句类型
三、table，查询涉及的表或衍生表
四、partitions查询涉及到的分区
五、type提供了判断查询是否高效的重要依据依据
六、possible_keys：指示MySQL可以从中选择查找此表中的行的索引。
七、key：MySQL查询实际使用到的索引。
八、key_len：表示索引中使用的字节数（只计算利用索引作为index key的索引长度，不包括用于group by/order by的索引长度）
九、ref：显示该表的索引字段关联了哪张表的哪个字段
十、rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
十一、filtered：返回结果的行数占读取行数的百分比，值越大越好
十二、extra：包含不适合在其他列中显示但十分重要的额外信息。常见的值如下

小结：

总结

前言

项目开发中，性能是我们比较关注的问题，特别是数据库的性能；作为一个开发，经常和SQL语句打交道，想要写出合格的SQL语句，我们需要了解SQL语句在数据库中是如何扫描表、如何使用索引的；

MySQL提供explain/desc命令输出执行计划，我们通过执行计划优化SQL语句。

下面我们以MySQL5.7为例了解一下执行计划：

注：文中涉及到的表结构、sql语句只是为了理解explain/desc执行计划，有不合理之处勿喷

explain/desc 用法

只需要在我们的查询语句前加explain/desc即可

准备数据表

-- 创建user表 create table user( id int, name varchar(20), role_id int, primary key(id) )engine=innodb default charset=utf8; -- 创建role表 create table role( id int, name varchar(20), primary key(id) )engine=innodb default charset=utf8;

查询，执行计划

 explain select * from user;

执行计划输出有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra，这些内容有什么意义，下面简单介绍一下

explain/desc 输出详解

一、id ，select 查询序列号

1 id相同，从上往下一次执行；

-- 左关联 explain select * from user a left join user b on a.id=b.id; -- 右关联 explain select * from user a right join user b on a.id=b.id;

通过left join 和 right join 验证；id一样（注意执行计划的table列），left join 先扫描a表，再扫描b表；rightjoin 先扫描b表，再扫描a表

2 id不同，id越大优先级越高，越先被执行

desc select * from user where role_id=(select id from role where name='开发');

我们编写查询角色为开发的用户；可以知道先查询角色name为开发角色id，查询序列号为2；再根据角色id查询用户，查询序列号为1；

二、select_type，查询语句类型

(1)SIMPLE(简单SELECT,不使用UNION或子查询等)

explain select * from user;

(2)PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

 desc select * from user where role_id=(select id from role where name='开发');

(3)UNION(UNION中的第二个或后面的SELECT语句)

desc select * from user where name='Java' union select * from user where role_id=1;

(4)DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

desc select * from user a where id in ( select b.id from user b where b.id=a.id union select c.id from role c where c.id=a.role_id );

(5)UNION RESULT(UNION的结果)

desc select * from user where name='Java' union select * from user where role_id=1;

(6)SUBQUERY(子查询中的第一个SELECT)

 desc select * from user where role_id=(select id from role where name='开发');

(7)DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

desc select * from user where role_id = ( select id from role where id=user.id );

(8)DERIVED(派生/衍生表的SELECT, FROM子句的子查询)

desc select * from ( select * from user where name='Java' union select * from user where role_id=1 ) a;

(9) MATERIALIZED(物化子查询) 在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表，后续对子查询结果集的访问将直接通过临时表获得。

(10)UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

(11)UNCACHEABLE UNION(UNION查询的结果不能被缓存)

三、table，查询涉及的表或衍生表

table分别user、role表

四、partitions查询涉及到的分区

创建分区表，

-- 创建分区表， -- 按照id分区，id<100 p0分区，其他p1分区 create table user_partitions (id int auto_increment, name varchar(12),primary key(id)) partition by range(id)( partition p0 values less than(100), partition p1 values less than maxvalue );

desc select * from user_partitions where id>200;

查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则

五、type提供了判断查询是否高效的重要依据依据

通过type字段, 我们判断此次查询是全表扫描还是索引扫描等，下面简单介绍一下常用的type；

（1）system: 表中只有一条数据，相当于系统表；这个类型是特殊的const类型；

（2）const:主键或者唯一索引的常量查询，表格最多只有1行记录符合查询，通常const使用到主键或者唯一索引进行定值查询。

主键

-- 创建user表 create table user(id int primary key, name varchar(20), role_id int ); -- 插入一条记录 insert into user values (1, 'a', 1 ); -- 按id查询 desc select * from user where id=1; -- 按role_id查询 desc select * from user where role_id=1;

分别查看按id和按role_id查询的执行计划；发现按主键id查询，执行计划type为const

将主键设置为id和role_id

-- 删除主键 alter table user drop primary key; -- 设置主键id,role_id alter table user add primary key(id,role_id); -- 按照部分主键查询 desc select * from user where id=1; -- 按照部分主键查询 desc select * from user where role_id=1; -- 按照全部主键查询 desc select * from user where id=1 and role_id=1;

发现只有按照全部主键查询，执行计划type为const

唯一索引

-- 删除主键 alter table user drop primary key; -- 设置主键 alter table user add primary key(id); -- 设置role_id为唯一索引 alter table user add unique key uk_role(role_id); -- 按照唯一索引查询 desc select * from user where role_id=1;

发现按role_id唯一索引查询；执行计划type为const

普通索引

-- 将role_id设置成普通索引 -- 删除唯一索引 alter table user drop index uk_role; -- 设置普通索引 alter table user add index index_role(role_id); -- 按照普通索引查询 desc select * from user where role_id=1;

发现按role_id普通索引查询；执行计划type为ref

const用于主键或唯一索引查询；将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时使用；与索引类型有关。

（3）eq_ref: 除了system和const类型之外,效率最高的连接类型；唯一索引扫描，对于每个索引键，表中只有一条记录与之对应；常用于主键或唯一索引扫描

准备数据

-- 创建teacher表 create table teacher( id int primary key, name varchar(20), tc_id int ); -- 插入3条数据 insert into teacher values (1,'a',1),(2,'b',2),(3,'c',3); -- 创建teacher_card表 create table teacher_card( id int primary key, remark varchar(20) ); -- 插入2条数据 insert into teacher_card values (1,'aa'),(2,'bb'); -- 关联查询，执行计划 desc select * from teacher t join teacher_card tc on t.tc_id=tc.id where t.name='a';

执行计划