Mysq数据量不大查询速度却很慢，记录一次left join查询优化

雨点打透心脏的1/2处 2023-10-11 01:27 56阅读 0赞

#### 背景 ####

> 趁着周末闲来无事，想起来之前看过的SQL优化，试一试效果。

#### 效果 ####

*为了体现查询慢，所以...无视这烂SQL吧。*

**优化前**

![优化前][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70] 优化前效果

> 可以清楚的看到上面短短5W余条数据，居然要一分钟，这能忍？

**优化后**

![优化后][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 1] 优化后效果

> 神奇吧，这就是用好索引的效果，同样一条烂SQL，效果千差万别。

看完了效果，接下来我们说一下优化过程。

首先用explain这个命令来查看一下SQL语句的执行计划，看看该SQL语句有没有使用上了索引，有没有做全表扫描，这些都可以通过explain命令来查看。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 2][]

通过explain命令，可以很清楚的看到我们SQL所关联的表执行计划。

下面说一下图中都有什么信息，代表什么意思。

expain出来的信息有10列，分别是id、select\_type、table、partitions、type、possible\_keys、key、key\_len、ref、rows、filtered、Extra

1.  id:选择标识符，也就是查询的先后顺序排名，值越大，优先执行
2.  select\_type:表示查询的类型。
3.  table:输出结果集的表，也可能是别名
4.  partitions:匹配的分区
5.  type:表示表的连接类型（常用的类型有： **ALL、index、range、 ref、eq\_ref、const、system、****NULL（从左到右，性能从差到好）**）
6.  possible\_keys:表示查询时，可能使用的索引
7.  key:表示实际使用的索引
8.  key\_len:索引字段的长度
9.  ref:列与索引的比较
10. rows:扫描出的行数(估算的行数)
11. filtered:按表条件过滤的行百分比
12. Extra:执行情况的描述和说明

说完了expain命令，接下来开始进入正文。

**一说到SQL优化，都是什么**

注意通配符中Like的使用

避免在where子句中对字段进行函数操作

在子查询当中，尽量用exists代替in

where子句中尽量不要使用is null 或 is not null对字段进行判断

![173f915b20bac3b975295a30eacd1dc0.jpg][]

也确实都没毛病，但是这些都是在我们写SQL的时候尽量避免的，但是我们编码是为业务服务的，有时候业务就是变态，你不得不用。

其实优化最明显的就是使用**索引**了。

索引为何物？

答：参考我们平常读的书，书本前是不是都有一个叫**目录**的东西？那玩意就是索引，能够准确的定位到我们想要看的知识。

![4b823fa67e8e2a51eb6e350a6ca73c8d.jpg][]

### 索引在哪创建？ ###

以Navicat为例，找到目标表右键设计表就能进入编辑表结构的页面。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 3][]

我们最常用的就是字段这个tab栏了，旁边就是索引栏。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 4][]

切换到索引栏，就可以自己创建了，首先是命名，然后就是我们要使用的字段（重点），然后是索引类型和索引方法。

**命名**

*  唯一索引使用uni\_\[字段名\]来命名
 *  非唯一索引使用idx\_\[字段名\]来命名

**索引类型**

*  full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
 *  normal：表示普通索引
 *  spatial：空间索引，空间索引是对空间数据类型的字段建立的索引
 *  unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
 *  总之，索引的类别由建立索引的字段内容特性来决定，通常normal最常见。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 5][]

**索引方法**

*  BTREE（B树（也叫多叉树）） （主流使用）
 *  HASH（key,value） 哈希这种方式对范围查询支持得不是很好

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 6][]

一般索引字段，都是我们需要对外进行关联的字段，例：on p.result\_id = pr.result\_id

![20201213113012700.png][]

两个表都建立是效果最佳的。（多张表，依次建立索引）

### **注意：字段的字符集一定要相同，不然建立索引也无法生效。** ###

比如A表result\_id是utf8mb4，那对应的B表也应该是utf8mb4，如果是其它，那么索引是无效的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 7][]

索引建立完成后，我们就可以看看效果了。

**索引前计划效果**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 8][]

![20201213113736457.png][]

**索引后计划效果**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 9][]

![2020121311385381.png][]

可以很清楚的看到计划中的改变和查询的时间效率。

### 优化总结 ###

> 1.  建立表时遵循数据库的三大范式。
> 2.  多表查询时建立索引
> 3.  注意字段字符集，避免索引失效
> 4.  写SQL时尽量避免特殊写法，避免索引失效

### 最后 ###

> SQL优化涉及的东西挺多的，以上只是我提到的一小点。如果想要了解更多，可以看看MySql的数据结构。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201213104520731.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2020121310523247.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201213105724607.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[173f915b20bac3b975295a30eacd1dc0.jpg]: https://img-blog.csdnimg.cn/img_convert/173f915b20bac3b975295a30eacd1dc0.jpg
[4b823fa67e8e2a51eb6e350a6ca73c8d.jpg]: https://img-blog.csdnimg.cn/img_convert/4b823fa67e8e2a51eb6e350a6ca73c8d.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201213111553478.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20201213111645211.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20201213112218520.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20201213112808360.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[20201213113012700.png]: https://img-blog.csdnimg.cn/20201213113012700.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20201213113127102.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20201213113503517.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[20201213113736457.png]: https://img-blog.csdnimg.cn/20201213113736457.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/2020121311361484.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzczNTMz,size_16,color_FFFFFF,t_70
[2020121311385381.png]: https://img-blog.csdnimg.cn/2020121311385381.png