Mysql SQL优化实战之 EXPLAIN篇（两表查询）

清疚 2022-11-03 04:21 6阅读 0赞

**目录**

EXPLAIN

一.两表优化

1.查询语句

2.EXPLAIN解析语句

3.创建索引

4.再次解析

案例二

1.创建表

2.插入语句

3.查询语句

4.创建BOOK索引

5.再次解析SQL

6.删除索引

7再次创建索引

8.解析SQL

9.右连接查询

10.删除索引

11.创建Class表索引

12.解析SQL

--------------------

# EXPLAIN #

**ps:本篇博客记录sql优化实战 explain 相关理论知识在其他文章中总结，一些关键字或者知识点就不一一讲解了。表结构在之前博客中有提到**

[https://blog.csdn.net/LiuY521/article/details/113823612?spm=1001.2014.3001.5501][https_blog.csdn.net_LiuY521_article_details_113823612_spm_1001.2014.3001.5501]   下载表结构即可

## 一.两表优化 ##

### 1.查询语句 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70][]

### **2.EXPLAIN解析语句** ###

![20210226173005516.png][]

**结论：type为ALL 代表全表查 需要从哪个表创建索引优化呢，若不清楚可以一一测试 多测试几次就清楚了**

### 3.创建索引 ###

使用第二种创建索引语句

ALTER TABLE beauty ADD INDEX(boyfriend_id);

查看索引

![20210226174010695.png][]

### 4.再次解析 ###

![20210226174117416.png][]

2.如果把\*更换索引字段再次解析

![20210226174151971.png][]

你品 细细品 这是不是效率更高了  多了Using index 具体好处查考之前博客

--------------------

上面优化sql 不明显 重新找个案例

## 案例二 ##

### 1.创建表 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 1][]

### 2.插入语句 ###

![20210226174430176.png][]

### 3.查询语句 ###

![2021022617464593.png][]

![20210226174738969.png][]

左右连接不确定索引加哪个表，可以一一测试

### 4.创建BOOK索引 ###

**左链接加右表索引**

![2021022617482957.png][]

### 5.再次解析SQL ###

![20210226174941948.png][]

### 6.删除索引 ###

![20210226175124804.png][]

### 7再次创建索引 ###

![2021022617522360.png][]

### 8.解析SQL ###

![20210226175308858.png][]

![20210226175326207.png][]

左连接的特性 是左表数据全部有，左连接加右表索引

### 9.右连接查询 ###

![20210226180233745.png][]

### 10.删除索引 ###

![20210226180307451.png][]

### 11.创建Class表索引 ###

![20210226180355858.png][]

### 12.解析SQL ###

![20210226180431119.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 2][]

![20210226180529239.png][]

总结：左连接索引创建在右表中，右连接索引创建左表中

[https_blog.csdn.net_LiuY521_article_details_113823612_spm_1001.2014.3001.5501]: https://blog.csdn.net/LiuY521/article/details/113823612?spm=1001.2014.3001.5501
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70]: /images/20221024/35b25f1c44da4fbdbe824401c76882fd.png
[20210226173005516.png]: /images/20221024/9ab94b4038984a8bbe4bbe8019f1a075.png
[20210226174010695.png]: /images/20221024/d5a783022cb04862904f6995d5425f74.png
[20210226174117416.png]: /images/20221024/e5e5fec17f814dd2a898b85fa4f22138.png
[20210226174151971.png]: /images/20221024/aba26e376b794d22aef8204f783a3228.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 1]: /images/20221024/211c5f54f2a24ad29bce19fa943b10e9.png
[20210226174430176.png]: /images/20221024/490ba3542e534812a63fdd98840043e4.png
[2021022617464593.png]: /images/20221024/246c5e1236844f8c9f7d3bdd5387ea5b.png
[20210226174738969.png]: /images/20221024/508e146967ab46839a47099a0b132a31.png
[2021022617482957.png]: /images/20221024/6c7b954232774f4b92231be77617f073.png
[20210226174941948.png]: /images/20221024/a14104910aa74c0ca29f6336fae193b8.png
[20210226175124804.png]: /images/20221024/e70512bdc03d4e508f645accabb5e67b.png
[2021022617522360.png]: /images/20221024/6506b687b143429a9fbce7e23bf32cf9.png
[20210226175308858.png]: /images/20221024/f40f5d4c3cb04aae93923c85f2cf1336.png
[20210226175326207.png]: /images/20221024/a11993d66e7b407eb2e3ab259b26f026.png
[20210226180233745.png]: /images/20221024/059ab0896ea749369a1a7b82e2e6a488.png
[20210226180307451.png]: /images/20221024/dbeefb38b4f746a48cb562c48c943766.png
[20210226180355858.png]: /images/20221024/29a20ed753364aa0ad7a0e3d668dcfc6.png
[20210226180431119.png]: /images/20221024/4c24211107da4135814eaabd3ac8f677.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70 2]: /images/20221024/564f3534a18f401ca2423686c59d67c5.png
[20210226180529239.png]: /images/20221024/67793e1d81ca49238435b8fa06b8a6f1.png