网络系统设计,wordpress 链接优化插件,什么视频直播网站做挣钱,网站图片优化搞数据库⼀个避不开的概念就是Join#xff0c;翻译成中⽂就是连接。
相信很多⼩伙伴在初学连接的时候有些⼀脸懵逼#xff0c;理解了连接的语义之后⼜可能不明⽩各个表中的记 录到底是怎么连起来的#xff0c;以⾄于在使⽤的时候常常陷⼊下边两种误区#xff1a;
误区⼀翻译成中⽂就是连接。
相信很多⼩伙伴在初学连接的时候有些⼀脸懵逼理解了连接的语义之后⼜可能不明⽩各个表中的记 录到底是怎么连起来的以⾄于在使⽤的时候常常陷⼊下边两种误区
误区⼀业务⾄上管他三七⼆⼗⼀再复杂的查询也⽤在⼀个连接语句中搞定。
误区⼆敬⽽远之上次 DBA 那给报过来的慢查询就是因为使⽤了连接导致的以后再也不敢⽤了。
连接简介 连接的本质 为了故事的顺利发展我们先建⽴两个简单的表并给它们填充⼀点数据
mysql CREATE TABLE t1 (m1 int, n1 char(1));
Query OK, 0 rows affected (0.02 sec)
mysql CREATE TABLE t2 (m2 int, n2 char(1));
Query OK, 0 rows affected (0.02 sec)
mysql INSERT INTO t1 VALUES(1, a), (2, b), (3, c);
Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql INSERT INTO t2 VALUES(2, b), (3, c), (4, d);
Query OK, 3 rows affected (0.00 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 inset (0.00 sec)
mysql SELECT * FROM t2;
------------
| m2 | n2 |
------------
| 2 | b |
| 3 | c |
| 4 | d |
------------
3 rows inset (0.00 sec)
连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加⼊结果集并返回给⽤户。所以我们把t1和t2两个表连接起来的过程如下图所示 这个过程看起来就是把t1表的记录和t2的记录连起来组成新的更⼤的记录所以这个查询过程称之为连接查询。连接查询的结果集中包含⼀个表中的每⼀条记录 与另⼀个表中的每⼀条记录相互匹配的组合像这样的结果集就可以称之为笛卡尔积。因为表t1中有3条记录表t2中也有3条记录所以这两个表连接之后的笛卡 尔积就有3×39⾏记录。在MySQL中连接查询的语法也很随意只要在FROM语句后边跟多个表名就好了⽐如我们把t1表和t2表连接起来的查询语句可以写成这 样 连接过程简介
如果我们乐意我们可以连接任意数量张表但是如果没有任何限制条件的话这些表连接起来产⽣的笛卡尔积可能是⾮常巨⼤的。⽐⽅说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 1
t1.m1 t2.m2
t2.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表中 执⾏单表查询。
所以整个连接查询的执⾏过程就如下图所示 从上边两个步骤可以看出来我们上边唠叨的这个两表连接查询共需要查询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 学⽣信息表;
CREATE TABLE score (
number INT COMMENT 学号,
subject VARCHAR(30) COMMENT 科⽬,
score TINYINT COMMENT 成绩,
PRIMARY KEY (number, score) ) EngineInnoDB CHARSETutf8 COMMENT 学⽣成绩表;
我们新建了⼀个学⽣信息表⼀个学⽣成绩表然后我们向上述两个表中插⼊⼀些数据为节省篇幅具体插⼊过程就不唠叨了插⼊后两表中的数据如下 现在我们想把每个学⽣的考试成绩都查询出来就需要进⾏两表连接了因为score中没有姓名信息所以不能单纯只查询score表。连接过程就是从student 表中取出记录在score表中查找number相同的成绩记录所以过滤条件就是student.number socre.number整个查询语句就是这样
mysql SELECT * FROM student, score WHERE student.number score.number; 字段有点多哦我们少查询⼏个字段 从上述查询结果中我们可以看到各个同学对应的各科成绩就都被查出来了可是有个问题史珍⾹同学也就是学号为20180103的同学因为某些原因没有参 加考试所以在score表中没有对应的成绩记录。那如果⽼师想查看所有同学的考试成绩即使是缺考的同学也应该展示出来但是到⽬前为⽌我们介绍的连 接查询是⽆法完成这样的需求的。我们稍微思考⼀下这个需求其本质是想驱动表中的记录即使在被驱动表中没有匹配的记录也仍然需要加⼊到结果集。 为了解决这个问题就有了内连接和外连接的概念
对于内连接的两个表驱动表中的记录在被驱动表中找不到匹配的记录该记录不会加⼊到最后的结果集我们上边提到的连接都是所谓的内连接。
对于外连接的两个表驱动表中的记录即使在被驱动表中没有匹配的记录也仍然需要加⼊到结果集。
在MySQL中根据选取驱动表的不同外连接仍然可以细分为2种
左外连接
选取左侧的表为驱动表。
右外连接
选取右侧的表为驱动表。
可是这样仍然存在问题即使对于外连接来说有时候我们也并不想把驱动表的全部记录都加⼊到最后的结果集。这就犯难了有时候匹配失败要加⼊结果 集有时候⼜不要加⼊结果集这咋办有点⼉愁啊。。。噫把过滤条件分为两种不就解决了这个问题了么所以放在不同地⽅的过滤条件是有不同语义 的
WHERE⼦句中的过滤条件
WHERE⼦句中的过滤条件就是我们平时⻅的那种不论是内连接还是外连接凡是不符合WHERE⼦句中的过滤条件的记录都不会被加⼊最后的结果集。
ON⼦句中的过滤条件
对于外连接的驱动表的记录来说如果⽆法在被驱动表中找到匹配ON⼦句中的过滤条件的记录那么该记录仍然会被加⼊到结果集中对应的被驱动表记 录的各个字段使⽤NULL值填充。
需要注意的是这个ON⼦句是专⻔为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加⼊结果集这个场景下提出的所以如果把ON ⼦句放到内连接中MySQL会把它和WHERE⼦句⼀样对待也就是说内连接中的WHERE⼦句和ON⼦句是等价的。
⼀般情况下我们都把只涉及单表的过滤条件放到WHERE⼦句中把涉及两表的过滤条件都放到ON⼦句中我们也⼀般把放到ON⼦句中的过滤条件也称之为连 接条件。
