网站seo优化发布高质量外链,物流软件开发工具,十几万 建设网站,宝安区建设局网站连接简介
在实际工作中#xff0c;我们需要查询的数据很可能不是放在一张表中#xff0c;而是需要同时从多张表中获取。下面我们以简单的两张表为例来进行说明。
连接的本质
为方便测试说明#xff0c;#xff0c;先创建两个简单的表并给它们填充一点数据#xff1a;
…连接简介
在实际工作中我们需要查询的数据很可能不是放在一张表中而是需要同时从多张表中获取。下面我们以简单的两张表为例来进行说明。
连接的本质
为方便测试说明先创建两个简单的表并给它们填充一点数据
mysql CREATE TABLE t1 (m1 int, n1 char(1));
Query OK, 0 rows affected (0.12 sec)
mysql CREATE TABLE t2 (m2 int, n2 char(1));
Query OK, 0 rows affected (0.13 sec)
mysql INSERT INTO t1 VALUES(1, a), (2, b), (3, c);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql INSERT INTO t2 VALUES(2, b), (3, c), (4, d);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0以上是建立的两个表 t1 和 t2 两个表都有两个列一个是 INT 类型的一个是 CHAR(1) 类型的填充的表数据如下
mysql SELECT * FROM t1;
------------
| m1 | n1 |
------------
| 1 | a |
| 2 | b |
| 3 | c |
------------
3 rows in set (0.00 sec)
mysql SELECT * FROM t2;
------------
| m2 | n2 |
------------
| 2 | b |
| 3 | c |
| 4 | d |
------------
3 rows in set (0.00 sec)连接的本质就是把各个连接表中的记录都取出来并将依次匹配的组合加入结果集并返回给用户。所以我们把 t1 和t2 两个表连接起来的过程如下图所示 这个过程看起来就是把 t1 表的记录和 t2 的记录连起来组成新的更大的记录所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合这样的组合集就可以称之为笛卡尔积 。因为表 t1 中有3条记录表 t2 中也有3条记录所以这两个表连接之后的笛卡尔积就有 3×39 行记录。在 MySQL 中连接查询的语法也很随意只要在 FROM 语句后边跟多个表名就好了比如我们把 t1 表和 t2 表连接起来的查询语句可以写成这样
mysql SELECT * FROM t1, t2;
------------------------
| m1 | n1 | m2 | n2 |
------------------------
| 1 | a | 2 | b |
| 2 | b | 2 | b |
| 3 | c | 2 | b |
| 1 | a | 3 | c |
| 2 | b | 3 | c |
| 3 | c | 3 | c |
| 1 | a | 4 | d |
| 2 | b | 4 | d |
| 3 | c | 4 | d |
------------------------
9 rows in set (0.00 sec)连接过程简介
理论上我们可以连接任意数量张表但是如果没有任何限制条件的话这些表连接起来产生的 笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产生的 笛卡尔积就有 100×100×1001000000 行数据所以在连接的时候过滤掉特定记录组合是有必要的在连接查询中的过滤条件可以分成两种
涉及单表的条件 这种只设计单表的过滤条件我们之前都提到过一万遍了我们之前也一直称为搜索条件 比如 t1.m1 1是只针对 t1 表的过滤条件 t2.n2 ‘d’ 是只针对 t2 表的过滤条件。涉及两表的条件 比如 t1.m1 t2.m2 、 t1.n1 t2.n2 等这些条件中涉及到了两个表我们稍后会分析这种过滤条件是如何使用的. 下边我们就要看一下携带过滤条件的连接查询的大致执行过程了比方说下边这个查询语句 SELECT * FROM t1, t2 WHERE t1.m1 1 AND t1.m1 t2.m2 AND t2.n2 ‘d’; 在这个查询中我们指明了这三个过滤条件t1.m1 1t1.m1 t2.m2t2.n2 ‘d’ 这个连接查询的大致执行过程如下 1 . 首先确定第一个需要查询的表这个表称之为驱动表 。单表中执行查询语句按照前一章节的方式进行只需要选取代价最小的那种访问方法去执行单表查询语句就好了依次是const、ref、ref_or_null、range、index、all这些执行方法中选取代价最小的去执行查询。此处假设使用 t1 作为驱动表那么就需要到 t1 表中找满足 t1.m1 1 的记录因为表中的数据太少我们也没在表上建立二级索引所以此处查询 t1 表的访问方法应该为all也就是采用全表扫描的方式执行单表查询。关于如何提升连接查询的性能我们之后再说现在先把基本概念搞清楚。所以查询过程就如下图所示 我们可以看到 t1 表中符合 t1.m1 1 的记录有两条。 2 . 针对上一步骤中从驱动表产生的结果集中的每一条记录分别需要到 t2 表中查找匹配的记录所谓匹配的记录 指的是符合过滤条件的记录。因为是根据 t1 表中的记录去找 t2 表中的记录所以 t2 表也可以被称之为被驱动表 。上一步骤从驱动表中得到了2条记录所以需要查询2次 t2 表。此时涉及两个表的列的过滤条件 t1.m1 t2.m2 就派上用场了当 t1.m1 2 时过滤条件 t1.m1 t2.m2 就相当于 t2.m2 2 所以此时 t2 表相当于有了 t2.m2 2 、 t2.n2 ‘d’ 这两个过滤条件然后到 t2 表中执行单表查询。当 t1.m1 3 时过滤条件 t1.m1 t2.m2 就相当于 t2.m2 3 所以此时 t2 表相当于有了 t2.m2 3 、 t2.n2 ‘d’ 这两个过滤条件然后到 t2 表中执行单表查询。 所以整个连接查询的执行过程就如下图所示 也就是说整个连接查询最后的结果只有两条符合过滤条件的记录
m1n1m2n22b2b3c3c从上边两个步骤可以看出来我们上边唠叨的这个两表连接查询共需要查询1次 t1 表2次 t2 表。当然这是在特定的过滤条件下的结果如果我们把 t1.m1 1 这个条件去掉那么从 t1 表中查出的记录就有3条就需要查询3次 t2 表了。也就是说在两表连接查询中驱动表只需要访问一次被驱动表可能被访问多次。
内连接和外连接
为方便大家理解我们先创建两个比较实用的表
CREATE TABLE student (
number INT NOT NULL AUTO_INCREMENT COMMENT 学号,
name VARCHAR(5) COMMENT 姓名,
major VARCHAR(30) COMMENT 专业,
PRIMARY KEY (number)
) EngineInnoDB CHARSETutf8 COMMENT 学生信息表;
Query OK, 0 rows affected (0.12 sec)CREATE TABLE score (
number INT COMMENT 学号,
subject VARCHAR(30) COMMENT 科目,
score TINYINT COMMENT 成绩,
PRIMARY KEY (number, score)
) EngineInnoDB CHARSETutf8 COMMENT 学生成绩表;
Query OK, 0 rows affected (0.18 sec)我们新建了一个学生信息表和一个学生成绩表然后我们向上述两个表中插入一些数据插入后两表中的数据如下
mysql insert into student values(20230101,张三,石油工程),(20230102,李四,测控技术),(20230103,王五,通信工程);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql insert into score values(20230101,高等数学,76),(20230102,模拟电路,92),(20230102,模拟电路,86),(20230102,高等数学,95);
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql select * from student;
--------------------------------
| number | name | major |
--------------------------------
| 20230101 | 张三 | 石油工程 |
| 20230102 | 李四 | 测控技术 |
| 20230103 | 王五 | 通信工程 |
--------------------------------
3 rows in set (0.00 sec)mysql select * from score;
-------------------------------
| number | subject | score |
-------------------------------
| 20230101 | 高等数学 | 76 |
| 20230102 | 模拟电路 | 86 |
| 20230102 | 模拟电路 | 92 |
| 20230102 | 高等数学 | 95 |
-------------------------------
4 rows in set (0.00 sec)现在我们想把每个学生的考试成绩都查询出来就需要进行两表连接了因为 score 中没有姓名信息所以不能单纯只查询 score 表。连接过程就是从 student 表中取出记录在 score 表中查找 number 相同的成绩记录所以过滤条件就是 student.number socre.number 整个查询语句就是这样
mysql SELECT * FROM student, score WHERE student.number score.number;
---------------------------------------------------------------
| number | name | major | number | subject | score |
---------------------------------------------------------------
| 20230101 | 张三 | 石油工程 | 20230101 | 高等数学 | 76 |
| 20230102 | 李四 | 测控技术 | 20230102 | 模拟电路 | 86 |
| 20230102 | 李四 | 测控技术 | 20230102 | 模拟电路 | 92 |
| 20230102 | 李四 | 测控技术 | 20230102 | 高等数学 | 95 |
---------------------------------------------------------------
4 rows in set (0.00 sec)字段有点重复我们精简一下
mysql SELECT s1.number, s1.name, s1.major , s2.subject, s2.score FROM student AS s1, score AS s2 where s1.number s2.number;
-----------------------------------------------------
| number | name | major | subject | score |
-----------------------------------------------------
| 20230101 | 张三 | 石油工程 | 高等数学 | 76 |
| 20230102 | 李四 | 测控技术 | 模拟电路 | 86 |
| 20230102 | 李四 | 测控技术 | 模拟电路 | 92 |
| 20230102 | 李四 | 测控技术 | 高等数学 | 95 |
-----------------------------------------------------
4 rows in set (0.00 sec)从上述查询结果中我们可以看到各个同学对应的各科成绩就都被查出来了可是有个问题 王五同学也就是学号为 20230103的同学因为某些原因没有参加考试所以在 score 表中没有对应的成绩记录。那如果老师想查看所有同学的考试成绩即使是缺考的同学也应该展示出来但是到目前为止我们介绍的连接查询是无法完成这样的需求的。我们稍微思考一下这个需求其本质是想驱动表中的记录即使在被驱动表中没有匹配的记录也仍然需要加入到结果集。为了解决这个问题就有了内连接和外连接的概念
对于内连接的两个表驱动表中的记录在被驱动表中找不到匹配的记录该记录不会加入到最后的结果集我们上边提到的连接都是所谓的内连接 。交集对于外连接的两个表驱动表中的记录即使在被驱动表中没有匹配的记录也仍然需要加入到结果集。 在 MySQL 中根据选取驱动表的不同外连接仍然可以细分为2种 左外连接 选取左侧的表为驱动表。右外连接 选取右侧的表为驱动表。 可是这样仍然存在问题即使对于外连接来说有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。这就犯难了有时候匹配失败要加入结果集有时候又不要加入结果集怎么解决呢其实把过滤条件分为两种这个问题就解决了放在不同地方的过滤条件是有不同语义的 WHERE 子句中的过滤条件 WHERE 子句中的过滤条件就是我们平时见的那种不论是内连接还是外连接凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。ON 子句中的过滤条件 对于外连接的驱动表的记录来说如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录那么该记录仍然会被加入到结果集中对应的被驱动表记录的各个字段使用 NULL 值填充。 需要注意的是这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的所以如果把 ON 子句放到内连接中 MySQL 会把它和 WHERE 子句一样对待也就是说内连接中的WHERE子句和ON子句是等价的。
一般情况下我们都把只涉及单表的过滤条件放到 WHERE 子句中把涉及两表的过滤条件都放到 ON 子句中我们也一般把放到 ON 子句中的过滤条件也称之为连接条件 。 注意左外连接和右外连接简称左连接和右连接。
左外连接的语法
左外连接的语法还是挺简单的比如我们要把 t1 表和 t2 表进行左外连接查询可以这么写 SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件]; 其中中括号里的 OUTER 单词是可以省略的。对于 LEFT JOIN 类型的连接来说我们把放在左边的表称之为外表或者驱动表右边的表称之为内表或者被驱动表。所以上述例子中 t1 就是外表或者驱动表 t2 就是内表或者被驱动表。需要注意的是对于左外连接和右外连接来说必须使用 ON 子句来指出连接条件。了解了左外连接的基本语法之后再次回到我们上边那个现实问题中来看看怎样写查询语句才能把所有的学生的成绩信息都查询出来即使是缺考的考生也应该被放到结果集中
mysql SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number s2.number;
---------------------------------------
| number | name | subject | score |
---------------------------------------
| 20230101 | 张三 | 高等数学 | 76 |
| 20230102 | 李四 | 模拟电路 | 86 |
| 20230102 | 李四 | 模拟电路 | 92 |
| 20230102 | 李四 | 高等数学 | 95 |
| 20230103 | 王五 | NULL | NULL |
---------------------------------------
5 rows in set (0.00 sec)从结果集中可以看出来虽然 王五并没有对应的成绩记录但是由于采用的是连接类型为左外连接所以仍然把她放到了结果集中只不过在对应的成绩记录的各列使用 NULL 值填充而已。
右外连接的语法
右外连接和左外连接的原理是一样一样的语法也只是把 LEFT 换成 RIGHT 而已 SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件]; 只不过驱动表是右边的表被驱动表是左边的表。
mysql SELECT s1.number, s1.name, s2.subject, s2.score FROM score AS s2 right JOIN student AS s1 ON s1.number s2.number;内连接的语法
内连接和外连接的根本区别就是在驱动表中的记录不符合 ON 子句中的连接条件时不会把该记录加入到最后的结果集我们最开始说的那些连接查询的类型都是内连接。不过之前仅仅提到了一种最简单的内连接语法就是直接把需要连接的多个表都放到 FROM 子句后边。其实针对内连接MySQL提供了好多不同的语法我们以 t1和 t2 表为例 SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件]; 也就是说在 MySQL 中下边这几种内连接的写法都是等价的
SELECT * FROM t1,t2;SELECT * FROM t1 JOIN t2;SELECT * FROM t1 INNER JOIN t2;SELECT * FROM t1 CROSS JOIN t2;
现在我们虽然介绍了很多种内连接 的书写方式不过熟悉一种就好了我们推荐 INNER JOIN 的形式书写内连接因为 INNER JOIN 语义很明确可以和 LEFT JOIN 和 RIGHT JOIN 很轻松的区分开。这里需要注意的是由于在内连接中ON子句和WHERE子句是等价的所以内连接中不要求强制写明ON子句。
我们前边说过连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表两表连接产生的笛卡尔积肯定是一样的。而对于内连接来说由于凡是不符合 ON 子句或WHERE 子句中的条件的记录都会被过滤掉其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去所以对于内连接来说驱动表和被驱动表是可以互换的并不会影响最后的查询结果。但是对于外连接来说由于驱动表中的记录即使在被驱动表中找不到符合 ON 子句连接条件的记录所以此时驱动表和被驱动表 的关系就很重要了也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。
连接的原理
嵌套循环连接Nested-Loop Join
我们前边说过对于两表连接来说驱动表只会被访问一遍但被驱动表却要被访问到好遍具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。对于内连接来说选取哪个表为驱动表都没关系而外连接的驱动表是固定的也就是说左外连接的驱动表就是左边的那个表右外连接的驱动表就是右边的那个表。我们上边已经大致介绍过 t1 表和 t2 表执行内连接查询的大致过程大致过程如下
步骤1选取驱动表使用与驱动表相关的过滤条件选取代价最低的单表访问方法来执行对驱动表的单表查询。步骤2对上一步骤中查询驱动表得到的结果集中每一条记录都分别到被驱动表中查找匹配的记录。
通用的两表连接过程如下图所示 如果有3个表进行连接的话那么 步骤2 中得到的结果集就像是新的驱动表然后第三个表就成为了被驱动表 重复上边过程也就是 步骤2 中得到的结果集中的每一条记录都需要到 t3 表中找一找有没有匹配的记录用伪 代码表示一下这个过程就是这样 for each row in t1 { #此处表示遍历满足对t1单表查询结果集中的每一条记录 for each row in t2 { #此处表示对于某条t1表的记录来说遍历满足对t2单表查询结果集中的 每一条记录 for each row in t3 { #此处表示对于某条t1和t2表的记录组合来说对t3表进行单表查询 if row satisfies join conditions, send to client } } } 这个过程就像是一个嵌套的循环所以这种驱动表只访问一次但被驱动表却可能被多次访问访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为 嵌套循环连接 Nested-Loop Join 这是最简单也是最笨拙的一种连接查询算法。
使用索引加快连接速度
我们知道在嵌套循环连接的步骤2 中可能需要访问多次被驱动表如果访问被驱动表的方式都是全表扫描的话那得要扫描很多次了。 但是别忘了查询 t2 表其实就相当于一次单表扫描我们可以利用索引来加快查询速度。回顾一下最开始介绍的 t1 表和 t2 表进行内连接的例子 SELECT * FROM t1, t2 WHERE t1.m1 1 AND t1.m1 t2.m2 AND t2.n2 ‘d’; 我们使用的其实是 嵌套循环连接 算法执行的连接查询再把上边那个查询执行过程表拉下来给大家看一下 查询驱动表 t1 后的结果集中有两条记录 嵌套循环连接 算法需要对被驱动表查询2次
当 t1.m1 2 时去查询一遍 t2 表对 t2 表的查询语句相当于 SELECT * FROM t2 WHERE t2.m2 2 AND t2.n2 ‘d’;当 t1.m1 3 时再去查询一遍 t2 表此时对 t2 表的查询语句相当于 SELECT * FROM t2 WHERE t2.m2 3 AND t2.n2 ‘d’;
可以看到原来的 t1.m1 t2.m2 这个涉及两个表的过滤条件在针对 t2 表做查询时关于 t1 表的条件就已经确定了所以我们只需要单单优化对 t2 表的查询了上述两个对 t2 表的查询语句中利用到的列是 m2 和 n2 列我们可以
在 m2 列上建立索引因为对 m2 列的条件是等值查找比如 t2.m2 2 、 t2.m2 3 等所以可能使用到ref refs复数多个的访问方法假设使用 ref 的访问方法去执行对 t2 表的查询的话需要回表之后再判断 t2.n2 d 这个条件是否成立。 这里有一个比较特殊的情况就是假设 m2 列是 t2 表的主键或者唯一二级索引列那么使用 t2.m2 常数值这样的条件从 t2 表中查找记录的过程的代价就是常数级别的。我们知道在单表中使用主键值或者唯一二级索引列的值进行等值查找的方式称之为 const 而设计 MySQL 的人把在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为 eq_ref 。在 n2 列上建立索引涉及到的条件是 t2.n2 ‘d’ 可能用到 range 的访问方法假设使用 range 的访问 方法对 t2 表的查询的话需要回表之后再判断在 m2 列上的条件是否成立。
假设 m2 和 n2 列上都存在索引的话那么就需要从这两个里边儿挑一个代价更低的去执行对 t2 表的查询。当然建立了索引不一定使用索引只有在 二级索引 回表 的代价比全表扫描的代价更低时才会使用索引。
另外有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列而这些列都是某个索引的一部分这种情况下即使不能使用 eq_ref 、 ref 、 ref_or_null 或者 range 这些访问方法执行对被驱动表的查询的话也可以使用索引扫描也就是 index 的访问方法来查询被驱动表。所以我们建议在真实工作中最好不要使用 * 作为查询列表最好把实际需要用到的列作为查询列表。
基于块的嵌套循环连接Block Nested-Loop Join
扫描一个表的过程其实是先把这个表从磁盘上加载到内存中然后从内存中比较匹配条件是否满足。现实生活中的表可不像 t1 、t2 这种只有3条记录成千上万条记录都是少的几百万、几千万甚至几亿条记录的表到处都是。内存里可能并不能完全存放的下表中所有的记录所以在扫描表前边记录的时候后边的记录可能还在磁盘上等扫描到后边记录的时候可能内存不足所以需要把前边的记录从内存中释放掉。我们前边又说过采用嵌套循环连接算法的两表连接过程中被驱动表可是要被访问好多次的如果这个被驱动表中的数据特别多而且不能使用索引进行访问那就相当于要从磁盘上读好几次这个表这个 I/O 代价就非常大了所以我们得想办法尽量减少访问被驱动表的次数。
当被驱动表中的数据非常多时每次访问被驱动表被驱动表的记录会被加载到内存中在内存中的每一条记录只会和驱动表结果集的一条记录做匹配之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录再一次把被驱动表的记录加载到内存中一遍周而复始驱动表结果集中有多少条记录就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候一次性和多条驱动表中的记录做匹配这样就可以大大减少重复从磁盘上加载被驱动表的代价了。所以设计 MySQL 的人提出了一个 join buffer 的概念 join buffer 就是执行连接查询前申请的一块固定大小的内存先把若干条驱动表结果集中的记录装在这个 join buffer 中然后开始扫描被驱动表每一条被驱动表的记录一次性和 join buffer 中的多条驱动表记录做匹配因为匹配的过程都是在内存中完成的所以这样可以显著减少被驱动表的 I/O 代价。使用 join buffer 的过程如下图所示 最好的情况是 join buffer 足够大能容纳驱动表结果集中的所有记录这样只需要访问一次被驱动表就可以完成连接操作了。设计 MySQL 的人把这种加入了 join buffer 的嵌套循环连接算 法称之为 基于块的嵌套连接Block Nested-Loop Join算法。
这个 join buffer 的大小是可以通过启动参数或者系统变量 join_buffer_size 进行配置默认大小为 262144字节 也就是 256KB 最小可以设置为128字节 。当然对于优化被驱动表的查询来说最好是为被驱动表加上效率高的索引如果实在不能使用索引并且自己的机器的内存也比较大可以尝试调大 join_buffer_size 的值来对连接查询进行优化。
另外需要注意的是驱动表的记录并不是所有列都会被放到 join buffer 中只有满足查询列表中的列和过滤条件中的列才会被放到 join buffer 中最后强调说明最好不要把 * 作为查询列表只需要把我们实际需要使用到的列放到查询列表就好了这样就可以在 join buffer 中一次性放置更多的记录。
更多关于mysql的知识分享请前往博客主页。编写过程中难免出现差错敬请指出
