![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521090646368.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
* **SUBQUERY(u2,u3)**:条件中的子查询中的表(包括多重层级)
explain select * from user u1
where u1.name =(
select name from user u2 where u2.name=(select name from user u3 where u3.name=‘zs’)
);
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521090719153.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
* **DEPENDENT SUBQUERY(r1)**: 子查询中的条件依赖于外部的查询(r1的条件是u1表中的数据)
explain select * from user u1 where u1.role_id=(select id from role r1 where u1.id=1);
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521090751489.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
* **DERIVED(u1)**:衍生表的from子表(该子表必须使用union关联其他表)
explain select * from
(select * from user u1 where u1.role_id=1 union select * from user u2 where u2.name=‘zs’) temp;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521090826448.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
### 1.3 table 字段
表示该SQL语句是作用于那张表的,取值为:表名、表别名、衍生表名等。
explain select * from user;
explain select * from user u1;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521090903142.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
### 1.4 partitions 字段
涉及到分区的表
* 准备数据
create table goods_partitions (
id int auto_increment,
name varchar(12),
primary key(id)
)
partition by range(id)(
partition p0 values less than(10000),
partition p1 values less than MAXVALUE
);
* 查看MySQL的物理存储路径:
show variables like ‘%dir%’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521090959554.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
查看物理存储文件,发现多了不同的文件来存储![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521091015548.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
* 查看查询语句所使用到的分区:
explain select * from goods_partitions;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521091101612.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
>
> 整个goods\_partitions使用到了两个分区。
>
>
>
* 查询id<1000的记录(属于p0分区)
explain select * from goods_partitions where id<1000;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521091122200.png)
### 1.5 type字段
反应一段SQL语句性能指标的重要参数,可以通过此参数来判断是否使用到了索引、是否全表扫描、是否范围查询等。
插入测试数据:
insert into role values(1,‘保洁’);
insert into role values(2,‘保安’);
insert into role values(3,‘厨师’);
insert into user values(1,‘zs’,1);
* **null**:代表不访问任何表
explain select 1;
![在这里插入图片描述](https://img-blog.csdnimg.cn/2020052109141434.png)
* **system**:表中只有一条记录,并且此表为系统表(一般很少出现)
use mysql; – 切换到mysql数据库
explain select * from db where host=‘localhost’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521091440889.png)
* **const**:通过唯一索引查询到的数据,只查询一次就查询到了
explain select * from user where id=1;
explain select * from user where name=‘zs’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521091516155.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
>
> 分别根据name和id查询,发现只有id的type为const。
>
>
>
给name字段加上唯一索引(必须要是唯一索引,普通索引不行):
create unique index user_name_unique on user(name);
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521091554424.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
测试完毕删除唯一索引:
drop index user_name_unique on user;
* **eq\_ref**:使用主键的关联查询,并且表中只有一条记录与主表匹配;
explain select * from user u left join role r on u.role_id=r.id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521091715219.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
代表有其他表引用了r表的主键。
**如果主表有多条记录与之匹配那么type将不再是eq\_ref**
* 首先查看两个表的数据:
mysql> select * from user;
±—±-----±--------+
| id | name | role_id |
±—±-----±--------+
| 1 | zs | 1 |
±—±-----±--------+
1 row in set (0.00 sec)
mysql> select * from role;
±—±-------+
| id | name |
±—±-------+
| 1 | 保洁 |
| 2 | 保安 |
| 3 | 厨师 |
±—±-------+
3 rows in set (0.00 sec)
mysql> explain select * from user u left join role r on u.role_id=r.id;
±—±------------±------±-----------±-------±--------------±--------±--------±---------------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-------±--------------±--------±--------±---------------±-----±---------±------+
| 1 | SIMPLE | u | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | r | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.u.role_id | 1 | 100.00 | NULL |
±—±------------±------±-----------±-------±--------------±--------±--------±---------------±-----±---------±------+
2 rows in set, 1 warning (0.00 sec)
mysql>
>
> user表示是1条记录,role是3条记录
>
>
>
* 在user表中新增一条记录,再次查看执行计划:
mysql> select * from user;
±—±-----±--------+
| id | name | role_id |
±—±-----±--------+
| 1 | zs | 1 |
| 2 | ls | 1 |
±—±-----±--------+
2 rows in set (0.00 sec)
mysql> select * from role;
±—±-------+
| id | name |
±—±-------+
| 1 | 保洁 |
| 2 | 保安 |
| 3 | 厨师 |
±—±-------+
3 rows in set (0.00 sec)
mysql> explain select * from user u left join role r on u.role_id=r.id;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+
| 1 | SIMPLE | u | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
| 1 | SIMPLE | r | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql>
>
> 发现`type`字段变为了`ALL`,而不是原来的`eq_ref`。
>
>
>
* **ref**:通过非唯一索引查询到的数据
创建普通索引:
create index user_name_index on user(name);
查询执行计划:
explain select * from user where name=‘zs’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521122524751.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
测试完毕删除索引:
drop index user_name_index on user;
* **range**:使用索引的范围查询(普通列的范围查询不会是range)
我们执行如下两句sql查看执行计划:
explain select * from user u where u.id>20; – 使用索引列进行范围查询
explain select * from user u where u.role_id>20; – 使用普通列进行范围查询
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521122616119.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
给role\_id列添加索引,再次执行sql,查看执行计划:
create index user_role_id_index on user(role_id);
explain select * from user u where u.role_id>20;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521122638788.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
* **index**:查询的是索引列,遍历了索引树
explain select id from user;
![在这里插入图片描述](https://img-blog.csdnimg.cn/202005211227077.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
* **ALL**:效率最低,遍历全表
explain select * from user;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521122730669.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
查询效率从高到底的取值为:
– 所有的type字段取值:
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
– 一般情况下type字段取值:
system > const > eq_ref > ref > range > index > ALL
### 1.6 possible\_keys 字段
查询语句中,可能应用到的索引,并非实际使用到的索引。实际使用到的索引根据key字段来反应。
例如:
– 给name列加索引
create index idx_name on user(name);
explain select * from user where user=‘1’ or user=‘2’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521124355259.png)
>
> 可能用到了`idx_name`索引,但实际没有使用到。
>
>
>
测试完毕删除索引:
drop index idx_name on user;
### 1.7 key 字段
key字段反应sql语句实际使用的索引,为null代表没有使用索引
– 根据id查询
explain select * from user where id=1;
– 根据普通列查询
explain select * from user where name=‘zs’;
– 给name列加上索引
create index idx_name on user(name);
– 根据索引查询
explain select * from user where name=‘zs’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521124010246.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
测试完毕删除索引:
drop index idx_name on user;
### 1.8 key\_len 字段
表示索引中使用的字节数
explain select * from user where id=1;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521123558390.png)
>
> 我的id类型为int类型,因此占用4个字节。
>
>
>
我们把id类型改为bigint(Long),再次查看索引使用字节数:
alter table user modify column id bigint;
explain select * from user where id=1;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521123829225.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
测试完毕更改回来:
alter table user modify column id int;
### 1.9 ref 字段
表示某表的某个字段引用到了本表的索引字段
mysql> select * from user;
±—±-----±--------+
| id | name | role_id |
±—±-----±--------+
| 1 | zs | 1 |
±—±-----±--------+
1 row in set (0.00 sec)
mysql> select * from role;
±—±-------+
| id | name |
±—±-------+
| 1 | 保洁 |
| 2 | 保安 |
| 3 | 厨师 |
±—±-------+
3 rows in set (0.00 sec)
mysql> explain select * from user u left join role r on u.role_id=r.id;
±—±------------±------±-----------±-------±--------------±--------±--------±---------------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-------±--------------±--------±--------±---------------±-----±---------±------+
| 1 | SIMPLE | u | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | r | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.u.role_id | 1 | 100.00 | NULL |
±—±------------±------±-----------±-------±--------------±--------±--------±---------------±-----±---------±------+
2 rows in set, 1 warning (0.00 sec)
mysql>
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521125226122.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
>
> 表示u表的role\_id引用了本表(r表)的索引字段(PRIMARY)
>
>
>
使用其他索引列关联表:
create index role_name_index on role(name); – 给name列加索引。
explain select * from user u left join role r on u.name=r.name; – 使用name列来关联
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521125503433.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
>
> 表示u表的name字段引用了本表(r表)的索引字段(role\_name\_index
>
>
>
测试完毕删除索引:
drop index role_name_index on role;
### 1.10 rows 字段
根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数。
explain select * from user;
explain select * from role;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521131619751.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
>
> user表中有1条记录,role表中有3条记录。
>
>
>
### 1.11 filtered 字段
返回结果与实际结果的差值占总记录数的百分比
insert into user values(2,‘ls’,‘4’);
explain select * from user u inner join role r on u.role_id=r.id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521131912242.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
>
> r表实际记录3条,上述sql语句关联查询出来的结果只能得出一条结果集,因此命中率为33.33%。
>
>
>
查询此SQL语句查询的记录数。
select * from user u inner join role r on u.role_id=r.id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200521131951527.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
>
> 只有一条记录
>
>
>
### 1.12 extra 字段
显示其他扩展信息
* **Using filesort**:排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中。效率低
# 技术学习总结
学习技术一定要制定一个明确的学习路线,这样才能高效的学习,不必要做无效功,既浪费时间又得不到什么效率,大家不妨按照我这份路线来学习。
![](https://img-blog.csdnimg.cn/img_convert/07236ce01ec6a5a67860200f3a7e6ab7.webp?x-oss-process=image/format,png)
![](https://img-blog.csdnimg.cn/img_convert/e1c26514b01ecd4f4b9ab13451a67659.webp?x-oss-process=image/format,png)
![](https://img-blog.csdnimg.cn/img_convert/e154077ccb742c3a9c4e16e61ede4dc8.webp?x-oss-process=image/format,png)
# 最后面试分享
大家不妨直接在牛客和力扣上多刷题,同时,我也拿了一些面试题跟大家分享,也是从一些大佬那里获得的,大家不妨多刷刷题,为金九银十冲一波!
![](https://img-blog.csdnimg.cn/img_convert/8e2005a2c2613e396ee7c98f182e0005.webp?x-oss-process=image/format,png)
![](https://img-blog.csdnimg.cn/img_convert/03f8e394bf8e546648d48e84b7316234.webp?x-oss-process=image/format,png)
img.cn/20200521131951527.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)
>
> 只有一条记录
>
>
>
### 1.12 extra 字段
显示其他扩展信息
* **Using filesort**:排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中。效率低
# 技术学习总结
学习技术一定要制定一个明确的学习路线,这样才能高效的学习,不必要做无效功,既浪费时间又得不到什么效率,大家不妨按照我这份路线来学习。
[外链图片转存中...(img-wGAm9RJy-1718712967386)]
[外链图片转存中...(img-cIzve41k-1718712967387)]
[外链图片转存中...(img-65mUXiW0-1718712967388)]
# 最后面试分享
大家不妨直接在牛客和力扣上多刷题,同时,我也拿了一些面试题跟大家分享,也是从一些大佬那里获得的,大家不妨多刷刷题,为金九银十冲一波!
[外链图片转存中...(img-2vBowNWX-1718712967388)]
[外链图片转存中...(img-aKejKbi1-1718712967389)]