简单分析Mysql不同方式联表查询的效率问题

- 日理万妓 2022-11-04 02:03 291阅读 0赞

前言：在项目中看别人写的后台代码发现了多种连表查询的方式，所以来调查一下哪种连表查询方式效率较高，以及如何优化，因为初入门，所以可能有些知识不准确，理解大意即可，对细节感兴趣的请自行查找。

参考：

[https://www.cnblogs.com/wyq178/p/11576065.html][https_www.cnblogs.com_wyq178_p_11576065.html]

[https://blog.csdn.net/hzz532968708/article/details/77370497][https_blog.csdn.net_hzz532968708_article_details_77370497]

[https://www.cnblogs.com/JonaLin/p/11392613.html][https_www.cnblogs.com_JonaLin_p_11392613.html]

[https://blog.csdn.net/qq\_39885372/article/details/104173121][https_blog.csdn.net_qq_39885372_article_details_104173121]

[https://www.runoob.com/mysql/mysql-insert-query.html][https_www.runoob.com_mysql_mysql-insert-query.html]

[https://blog.csdn.net/qq\_20891495/article/details/93744495][https_blog.csdn.net_qq_20891495_article_details_93744495]

[https://www.cnblogs.com/xupengjun/p/14265806.html][https_www.cnblogs.com_xupengjun_p_14265806.html]

**目录**

第一章 Mysql基础知识

1.1 mysql执行顺序

1.2 explain id表示的顺序

第二章 连表查询

2.1 例子表介绍

2.2 两种查询方式对比

2.3 联表原理分析

2.4 优化方式

第三章 子查询

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

# 第一章 Mysql基础知识 #

来源：[https://www.cnblogs.com/wyq178/p/11576065.html][https_www.cnblogs.com_wyq178_p_11576065.html]

先了解下sql查询的基础

对于select distinct s.id  from T t join  S s on t.id=s.id where t.name="Yrion" group by t.mobile having count(\*)>2  order by s.create\_time limit 5;

## 1.1 mysql执行顺序 ##

1. from

第一步就是选择出from关键词后面跟的表,这也是sql执行的第一步:表示要从数据库中执行哪张表。

实例说明:在这个例子中就是首先从数据库中找到表T

2 join on

join是表示要关联的表，on是连接的条件。通过from和join on选择出需要执行的数据库表T和S,产生笛卡尔积,生成T和S合并的临时中间表Temp1。on:确定表的绑定关系,通过on产生临时中间表Temp2.

实例说明：找到表S,生成临时中间表Temp1,然后找到表T的id和S的id相同的部分组成成表Temp2,Temp2里面包含着T和Sid相等的所有数据

3 where

where表示筛选,根据where后面的条件进行过滤,按照指定的字段的值(如果有and连接符会进行联合筛选)从临时中间表Temp2中筛选需要的数据,注意如果在此阶段找不到数据，会直接返回客户端,不会往下进行.这个过程会生成一个临时中间表Temp3。**注意在where中不可以使用聚合函数，聚合函数主要是(min\\max\\count\\sum等函数)**

实例说明:在temp2临时表集合中找到T表的name="Yrion"的数据,找到数据后会成临时中间表Temp3,temp3里包含name列为"Yrion"的所有表数据

4 group by

group by是进行分组，对where条件过滤后的临时表Temp3按照固定的字段进行分组,产生临时中间表Temp4，这个过程只是数据的顺序发生改变,而数据总量不会变化,表中的数据以组的形式存在

实例说明:在temp3表数据中对mobile进行分组,查找出mobile一样的数据,然后放到一起，产生temp4临时表。

5 Having

对临时中间表Temp4进行聚合,这里可以为count等计数，然后产生中间表Temp5，在此阶段可以使用select中的别名

实例说明:在temp4临时表中找出条数大于2的数据,如果小于2直接被舍弃掉，然后生成临时中间表temp5

1.1.6 select

对分组聚合完的表挑选出需要查询的数据,如果为\*会解析为所有数据,此时会产生中间表Temp6

实例说明：在此阶段就是对temp5临时聚合表中S表中的id进行筛选产生Temp6,此时temp6就只包含有s表的id列数据,并且name="Yrion"，通过mobile分组数量大于2的数据

7 Distinct

distinct对所有的数据进行去重,此时如果有min、max函数会执行字段函数计算，然后产生临时表Temp7

实例说明:此阶段对temp5中的数据进行去重,引擎API会调用去重函数进行数据的过滤,最终只保留id第一次出现的那条数据,然后产生临时中间表temp7

8 order by

会根据Temp7进行顺序排列或者逆序排列，然后插入临时中间表Temp8，这个过程比较耗费资源

**实例说明：这段会将所有temp7临时表中的数据按照创建时间(create\_time)进行排序,这个过程也不会有列或者行损失**

**9 limit**

limit对中间表Temp8进行分页,产生临时中间表Temp9,返回给客户端。

**实例说明:在temp7中排好序的数据,然后取前五条插入到Temp9这个临时表中，最终返回给客户端**

**ps:实际上这个过程也并不是绝对这样的，中间mysql会有部分的优化以达到最佳的优化效果，比如在select筛选出找到的数据集**

## 1.2 explain id表示的顺序 ##

参考：[https://blog.csdn.net/qq\_39885372/article/details/104173121][https_blog.csdn.net_qq_39885372_article_details_104173121]（挺重要的）

比如下面这个是id=2先执行，再执行id=1上面那个，再执行id=1下面那个

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70][]

# 第二章 连表查询 #

## 2.1 例子表介绍 ##

为了更有效地说明例子，我新建了两个表，一个表叫zhang，一个表叫chen

两个表的结构都一样，如下图所示：

![20210302215622653.png][]

其中zhang表有4万条数据，chen表有5千条数据

## 2.2 两种查询方式对比 ##

一般网上的连表查询有两种，一种是

explain select zhang.name from zhang join chen on zhang.name = chen.name

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 1][]

另一种是

explain select zhang.name from zhang,chen where zhang.\`name\`=chen.\`name\`

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 2][]

测试了几次发现效果是一样的，join和逗号可以随意换，on和where可以随意换，其实可以参考 [https://www.runoob.com/mysql/mysql-insert-query.html][https_www.runoob.com_mysql_mysql-insert-query.html]，这里也说明了可以换。

## 2.3 联表原理分析 ##

表面上看是求了两表的笛卡尔积，那么这个笛卡尔积怎么分析出来的呢？

如对于explain select zhang.name from zhang join chen on zhang.name = chen.name

当进行联表查询时，Mysql会从chen表读出一条，选出所有与on匹配的zhang表纪录进行连接，形成n条纪录，然后继续读下一条，直到读完，如果还有第三个参与Join，则再通过前两个表的Join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

一般是让小表驱动大表，因为每循环一次都要连接一次，小表在外面连接的次数更少，这种情况下小表叫驱动表，大表叫被驱动表，如上就是chen是驱动表，zhang是被驱动表，体现在explain上就是驱动表在上面，可以看上面的图chen表一直在上面（这个个人没做技术验证，感觉应该靠谱）。参考：[https://blog.csdn.net/qq\_20891495/article/details/93744495][https_blog.csdn.net_qq_20891495_article_details_93744495]

for(5k条)\{  
    for(4万条)\{

\}  
\}

当使用join或者逗号时，会自动挑选小表当驱动表，left join时，左边的表是驱动表，同理right join时右边的表是驱动表。

## 2.4 优化方式 ##

最直接的想法是减少笛卡尔积的个数，所以感觉如果能提前筛出来一些可以提前筛出来一些。

比如：

explain select zhang.name from zhang join chen on zhang.\`name\`=chen.\`name\` where zhang.id>20000 and chen.id>4500

explain select zhang.name from zhang,chen where zhang.\`name\`=chen.\`name\`and zhang.id>20000 and chen.id>4500

可以看到经过where的过滤后两个做笛卡尔积的表数量少了不少，话说我没想到mysql能把where自动优化在join的笛卡尔积之前，我一开始以为得写成select from ( select xxx from a where xxxx) a join (select xxx from b where xxxx) b on  a.xxx=b.xxx这种形式才能把where自动优化在join的笛卡尔积之前。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 4][]

可以看到在提前筛出来的过程中会使用索引之类的，如上面是使用了主键索引，所以在筛选时增加适当的索引也能提高速度。个人认为增加的速度主要来源于小表的循环次数，如小表5千条，要查大表5千次，因为没有索引每次都要遍历大表，增加了索引之后，5千次大表的查询过程可以每次都用索引，这样就极大的优化了查询速度。

explain select zhang.name from zhang,chen where zhang.\`name\`=chen.\`name\`and zhang.clothes<'sport12000' and chen.clothes<'sport10515'

可以看到每次都要遍历

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 5][]

给clothes字段增加索引

![20210303111636572.png][]

![20210303111743981.png][]

再次查询发现走了索引，rows少了很多

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 6][]

经过索引的增加后我们要查询的列由 4793\*40532 变成了 513\*2225，按理说应该是速度变得很快，但是。。。本人发现速度没变快多少，居然两次的查询速度都是0.24s左右，这个后面本人再做做调查。。。。

总结：优化的话，小表要当驱动表，要让参与笛卡尔积的两表尽量小，通过where减少参与笛卡尔积两表的量级（这个应该跟实际情景的相关，没有对应的需求也不能筛选表，只是说有能够筛选的尽量提前放进来筛选），也可以增加索引来增加筛选时的速度（因为被驱动表会被查询很多次，这个是推测的）

# 第三章 子查询 #

有时子查询也能代替某些需要联表的查询，感觉子查询应该不会效率更高，如在select后的子查询，应该也是要循环两轮

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 7][]

随便百度下网上说子查询比较好写，但是效率不高，感觉应该没问题，等以后有实际需要调优的场景了再来看这个问题吧，先这样了。参考：[https://www.cnblogs.com/xupengjun/p/14265806.html][https_www.cnblogs.com_xupengjun_p_14265806.html]

[https_www.cnblogs.com_wyq178_p_11576065.html]: https://www.cnblogs.com/wyq178/p/11576065.html
[https_blog.csdn.net_hzz532968708_article_details_77370497]: https://blog.csdn.net/hzz532968708/article/details/77370497
[https_www.cnblogs.com_JonaLin_p_11392613.html]: https://www.cnblogs.com/JonaLin/p/11392613.html
[https_blog.csdn.net_qq_39885372_article_details_104173121]: https://blog.csdn.net/qq_39885372/article/details/104173121
[https_www.runoob.com_mysql_mysql-insert-query.html]: https://www.runoob.com/mysql/mysql-insert-query.html
[https_blog.csdn.net_qq_20891495_article_details_93744495]: https://blog.csdn.net/qq_20891495/article/details/93744495
[https_www.cnblogs.com_xupengjun_p_14265806.html]: https://www.cnblogs.com/xupengjun/p/14265806.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70]: /images/20221023/ecb31cc92cb6414a878b771f98a48edd.png
[20210302215622653.png]: /images/20221023/2ea5134da0044f80af23d9b1ab87cbd2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 1]: /images/20221023/c64fca1484de48e4a97062dd2e8b8b85.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 2]: /images/20221023/e4455a3b719549098b4f5f2e2fa78b65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 3]: /images/20221023/3e391ac6045049d286e959f5241900be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 4]: /images/20221023/38e940c1294b4dbd82a7ea653671d3c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 5]: /images/20221023/3a9f808da8404313b802621fe923fdde.png
[20210303111636572.png]: /images/20221023/8edbc730806b40719346ff13446279e7.png
[20210303111743981.png]: /images/20221023/2a3f76b6282447f7971abe4bde18a35d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 6]: /images/20221023/7739364297d14304b173512cdbd6b17f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3E2MTAzNzY2ODE_size_16_color_FFFFFF_t_70 7]: /images/20221023/6dc036d876e5450a85dac92bab864aed.png