成为作者

MySQL- 技术专题 - 实战技巧

用户头像
李博@Alex
关注
发布于: 2020 年 10 月 10 日
MySQL-技术专题-实战技巧

join用于多表中字段之间的联系，在数据库的DML (数据操作语言，即各种增删改查操作）中有着重要的作用。

合理使用Join语句优化SQL有利于：

  1. 增加数据库的处理效率，减少响应时间；

  2. 减少数据库服务器负载，增加服务器稳定性；

  3. 减少服务器通讯的网络流量；

1.Join的分类：

  • 内连接 Inner Join

  • 全外连接 FULL Outer Join

  • 左外连接 Left Outer Join

  • 右外连接 Right Outer Join

  • 交叉连接 Cross Join

连接的分类

每种连接的区别作为基础内容，这里就不再展开说明，请读者自己参看其他文章了解，比如Mysql Join语法以及性能优化

需要说明的是，目前MySQL不支持全连接，需要使用UNION关键字进行联合。

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

2.Join使用的注意事项

下面进行本文重点，Join的使用注意事项和技巧，首先给出要使用的表结构：

-- auto-generated definition
CREATE TABLE customer
(
  id  INT AUTO_INCREMENT PRIMARY KEY,
  cust_name VARCHAR(50)  NOT NULL CHARSET utf8,
  over      VARCHAR(100) NULL CHARSET utf8,
  CONSTRAINT customer_id_uindex
  UNIQUE (id)
)
ENGINE = InnoDB;

-- auto-generated definition
CREATE TABLE faculty
(
  id        INT AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR(50)  NOT NULL CHARSET utf8,
  over      VARCHAR(200) NULL CHARSET utf8,
  CONSTRAINT faculty_id_uindex
  UNIQUE (id)
)
ENGINE = InnoDB;





2.1 显式连接 VS 隐式连接

所谓显式连接，即如上显示使用inner Join关键字连接两个表，

select * from table a inner join table b on a.id = b.id;

而隐式连接即不显示使用inner Join关键字，如：

select a.*, b.* from table a, table b where a.id = b.id;

二者在功能上没有差别，实现的性能上也几乎一样。只不过隐式连接是SQL92中的标准内容，而在SQL99中显式连接为标准，虽然很多人还在用隐私连接，但是它已经从标准中被移除。

从使用的角度来说，还是推荐使用显示连接，这样可以更清楚的显示出多个表之间的连接关系和连接依赖的属性。

2.2 On VS Where

ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。ON将从匹配阶段产生的数据中检索过滤。

所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。尽可能满足ON的条件，而少用Where的条件，从执行性能来看也更加高效。

3 Join的技巧

3.1 过虑条件更新自身表

假设现在要将是职工中的消费者的“over”属性设置为"优惠"，直接如下更新会报错：

这是由于Mysql不支持这种查询后更新（这其实是标准SQL中一项要求，Oracle、SQL Server中都是可以的）。

为了解决这种更新的过虑条件中包含要更新的表的情况，可以把带过滤条件的查询结果当做一个新表，在新表上，执行更新操作。

UPDATE (faculty f INNER JOIN customer c
    on user_name=cust_name)
set c.over = "优惠";



更新成功

3.2 Join优化子查询

嵌套的子查询是比较低效地，因为每一条记录都要进行匹配，如果记录长度比较大的话，那么我们的查询就有可能非常的耗时。我们应该尽量避免使用子查询，而用表连接。如下面的这个子查询就可以转化为等价的连接查询

SELECT user_name, over ,(SELECT over FROM customer c where user_name=cust_name) as over2
from faculty f;




SELECT user_name, f.over , c.over as over2
from faculty f
  LEFT JOIN customer c ON cust_name=user_name;


3.3 Join优化聚合查询

为了说明这个问题 ，我们在添加一个工作量的表，记录每个职工每天的工作量

-- auto-generated definition
CREATE TABLE tasks
(
  id        SMALLINT(5) UNSIGNED AUTO_INCREMENT
    PRIMARY KEY,
  facult_id SMALLINT(5) UNSIGNED                NULL,
  timestr   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  workload  SMALLINT(5) UNSIGNED                NULL
)
ENGINE = InnoDB CHARSET = utf8;




tasks记录职工的工作量

比如我们想查询每个员工工作量最多是哪一天，通过子查询可以这样实现：

select a.user_name ,b.timestr,b.workload from faculty a join tasks b
on a.id = b.facult_id
where b.workload = (
  select max(c.workload)
  from tasks c
  where c.facult_id = b.facult_id)

查询结果

使用表连接优化之后：

SELECT user_name, t.timestr, t.workload
FROM faculty f
  JOIN tasks t ON f.id = t.facult_id
  JOIN tasks t2 ON t2.facult_id = t.facult_id
GROUP BY user_name,t.timestr，t.workload
HAVING t.workload = max(t2.workload);

这里额外的再连接了一个task表中内容，在这个“额外表”中通过聚合计算出工作量的最大值，然后再过虑（HAVING）出工作量最大的日期。

因为聚合函数通过作用于一组数据而只返回一个单个值，因此，在SELECT语句中出现的元素要么为一个聚合函数的输入值，要么为GROUP BY语句的参数，否则会出错。



但是mysql的group by做过扩展了，select之后的列允许其不出现在group by之后，MySQL在执行这类查询语句时，它会默认理解为，没写到GROUP BY子句的列，其列值是唯一的，如果GROUP BY省略的列值其实并不唯一，将会默认取第一个获得的值，这样就会指代不明，那么最好不要使用这项功能。

3.4 如何实现分组查询

要获取每个员工完成工作量最多的两天。这个也可以通过Join来完成。

select d.user_name,c.timestr,workload
FROM (
       select facult_id,timestr,workload,
         (SELECT COUNT(*)
          FROM tasks b
          WHERE b.facult_id=a.facult_id AND a.workload<=b.workload) AS cnt
       FROM tasks a
       GROUP BY facult_id,timestr,workload) c
JOIN faculty d ON c.facult_id=d.id
WHERE cnt <= 2;

其中，内部的查询结果cnt表示对于tasks表中某个给定记录，相同员工的工作里记录比其大的数量有多少。

内部查询的结果如下：

select facult_id,timestr,workload,
         (SELECT COUNT(*)
          FROM tasks b
          WHERE b.facult_id=a.facult_id AND a.workload<=b.workload) AS cnt
FROM tasks a
GROUP BY facult_id,timestr,workload;



内部查询的结果

即每个工作量记录信息和同一员工的工作量排名。

cnt <= 2

就代表该记录是某位员工的工作量最大两天之一。

每个员工完成工作量最多的两

4. join的实现原理

join的实现是采用Nested Loop Join算法，就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果有多个join，则将前面的结果集作为循环数据，再一次作为循环条件到后一个表中查询数据。

比如我们以如下SQL语句为例：

EXPLAIN 
SELECT C.id, cust_name,T.workload
FROM customer C
  INNER JOIN faculty F
    ON C.cust_name = F.user_name
  INNER JOIN tasks T
    ON T.facult_id = F.id ;





EXPLAIN 连接查询

从explain的输出看出，MySQL选择C作为驱动表，

首先通过Using Where和Using join buffer来匹配F中的内容，然后在其结果的基础上通过主键的索引PRIMARY,faculty_id_uindex匹配到T表中的内容。

其过程类似于三次次嵌套的循环。



需要说明的是，C作为驱动表，通过Using Where和Using join buffer来匹配F，是因为C.cust_name ，F.user_name都没有加索引，要获取具体的内容只能通过对全表的数据进行where过滤才能获取，而Using join buffer是指使用到了Cache(只有当join类型为ALL，index，rang或者是index_merge的时候才会使用join buffer)，记录已经查询的结果，提高效率。



而对于T和F之间通过T的主键T.id连接，所以join类型为eq_ref，也不用使用Using join buffer。

5. join语句的优化原则

  1. 用小结果集驱动大结果集，将筛选结果小的表首先连接，再去连接结果集比较大的表，尽量减少join语句中的Nested Loop的循环总次数；

  2. 优先优化Nested Loop的内层循环（也就是最外层的Join连接），因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；

  3. 对被驱动表的join字段上建立索引；

  4. 当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。

6.join的实现原理

join的实现是采用Nested Loop Join算法，就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果有多个join，则将前面的结果集作为循环数据，再一次作为循环条件到后一个表中查询数据。

接下来通过一个三表join查询来说明mysql的Nested Loop Join的实现方式。

select m.subject msg_subject, c.content msg_contentfrom user_group g,group_message m,group_message_content cwhere g.user_id = 1and m.group_id = g.group_idand c.group_msg_id = m.id



使用explain看看执行计划：

explain select m.subject msg_subject, c.content msg_content from user_group g,group_message m,group_message_content c where g.user_id = 1 and m.group_id = g.group_id and c.group_msg_id = m.id\G;



结果如下：



*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: g
type: ref
possible_keys: user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind
key: user_group_uid_ind
key_len: 4
ref: const
rows: 2
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: g.group_id
rows: 3
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: idx_group_message_content_msg_id
key: idx_group_message_content_msg_id
key_len: 4
ref: m.id
rows: 2
Extra:



从结果可以看出，explain选择user_group作为驱动表，首先通过索引user_group_uid_ind来进行const条件的索引ref查找，然后用user_group表中过滤出来的结果集group_id字段作为查询条件，对group_message循环查询，然后再用过滤出来的结果集中的group_message的id作为条件与group_message_content的group_msg_id进行循环比较查询，获得最终的结果。



这个过程可以通过如下代码来表示：



for each record g_rec in table user_group that g_rec.user_id=1{

     for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{

          for each record c_rec in group_message_content that c_rec.group_msg_id=m_rec.id

                pass the (g_rec.user_id, m_rec.subject, c_rec.content) row

          combination to output;

      }

}



如果去掉group_message_content表上面的group_msg_id字段的索引，执行计划会有所不一样。



drop index idx_group_message_content_msg_id on group_message_content;explain select m.subject msg_subject, c.content msg_content from user_group g,group_message m,group_message_content c where g.user_id = 1 and m.group_id = g.group_id and c.group_msg_id = m.id\G;



得到的执行计划如下：



*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: g
type: ref
possible_keys: user_group_uid_ind
key: user_group_uid_ind
key_len: 4
ref: const
rows: 2
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: g.group_id
rows: 3
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 96
Extra:Using where;Using join buffer



因为删除了索引，所以group_message_content的访问从ref变成了ALL，keys相关的信息也变成了NULL，Extra信息也变成了Using Where和Using join buffer，也就是说需要获取content内容只能通过对全表的数据进行where过滤才能获取。Using join buffer是指使用到了Cache，只有当join类型为ALL，index，rang或者是index_merge的时候才会使用join buffer，它的使用过程可以用下面代码来表示：



for each record g_rec in table user_group{

      for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{

           put (g_rec, m_rec) into the buffer

           if (buffer is full)

                 flush_buffer();

      }

}

flush_buffer(){

      for each record c_rec in group_message_content that c_rec.group_msg_id = c_rec.id{

            for each record in the buffer

                 pass (g_rec.user_id, m_rec.subject, c_rec.content) row combination to output;

      }

      empty the buffer;

}

在实现过程中可以看到把user_group和group_message的结果集放到join buffer中，而不用每次user_group和group_message关联后马上和group_message_content关联，这也是没有必要的；需要注意的是join buffer中只保留查询结果中出现的列值，它的大小不依赖于表的大小，我们在伪代码中看到当join buffer被填满后，mysql将会flush buffer。

join语句的优化

1. 用小结果集驱动大结果集，尽量减少join语句中的Nested Loop的循环总次数；

2. 优先优化Nested Loop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；

3. 对被驱动表的join字段上建立索引；

4. 当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。



参考文章

  1. MySQL数据库对GROUP BY子句的功能扩展(1)

  2. SQL中GROUP BY语句与HAVING语句的使用

  3. Mysql Join语法以及性能优化

  4. mysql join的实现原理及优化思路

  5. Explicit vs implicit SQL joins

Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing



划线
评论
复制
发布于: 2020 年 10 月 10 日 阅读数: 414
用户头像

李博@Alex

关注

我们始于迷惘，终于更高的迷惘. 2020.03.25 加入

一个酷爱计算机技术、健身运动、悬疑推理的极客狂人，大力推荐安利Java官方文档：https://docs.oracle.com/javase/specs/index.html

评论

发布
暂无评论
Copyright © 2020, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 |  京 ICP 备 16027448 号 - 5 京公网安备 京公网安备 11010502039052号
MySQL-技术专题-实战技巧