文档章节

Mysql Join语法以及性能优化

文文1
 文文1
发布于 06/14 10:31
字数 2229
阅读 3
收藏 0
点赞 0
评论 0

引言

  内外联结的区别内联结去除所有不符合条件的记录,而外联结保留其中部分外左联结外右联结的区别在于如果用A左联结BA中所有记录都会保留在结果中,此时B中只有符合联结条件的记录,而右联结相反,这样也就不会混淆了。

 

一.Join语法概述

join 用于多表中字段之间的联系,语法如下:

代码如下:

FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

table1:左表;table2:右表。

 

JOIN 功能分类

INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。

LEFT JOIN(左连接):取得左表(table1)完全记录,即使右表(table2)并无对应匹配记录。

RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即使左表(table1)并无匹配对应记录。

注意:mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.

接下来给出一个列子用于解释下面几种分类。如下两个表(A,B)

代码如下:

mysql> select A.id,A.name,B.name from A,B where A.id=B.id;//和内连接等价
+----+-----------+-------------+
| id | name | name |
+----+-----------+-------------+
| 1 | Pirate | Rutabaga |
| 2 | Monkey | Pirate |
| 3 | Ninja | Darth Vader |
| 4 | Spaghetti | Ninja |
+----+-----------+-------------+
4 rows in set (0.00 sec)

 

二.Inner join

内连接,也叫等值连接,inner join产生同时符合A和B的一组数据。

代码如下:

mysql> select * from A inner join B on A.name = B.name;
+----+--------+----+--------+
| id | name | id | name |
+----+--------+----+--------+
| 1 | Pirate | 2 | Pirate |
| 3 | Ninja | 4 | Ninja |
+----+--------+----+--------+

 

三.Left join

代码如下:

mysql> select * from A left join B on A.name = B.name;
#或者:select * from A left outer join B on A.name = B.name;
+----+-----------+------+--------+
| id | name | id | name |
+----+-----------+------+--------+
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | NULL | NULL |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL | NULL |
+----+-----------+------+--------+
4 rows in set (0.00 sec)

left join,(或left outer join:在Mysql中两者等价,推荐使用left join.)左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。

 

如果想只从左表(A)中产生一套记录,但不包含右表(B)的记录,可以通过设置where语句来执行,如下:

 代码如下:

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;//where A.id is null可以不写,因为是左连接,那么A.id 肯定是有值的
+----+-----------+------+------+
| id | name | id | name |
+----+-----------+------+------+
| 2 | Monkey | NULL | NULL |
| 4 | Spaghetti | NULL | NULL |
+----+-----------+------+------+
2 rows in set (0.00 sec)

 

 

同理,还可以模拟inner join. 如下:

 代码如下:

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;
+----+--------+------+--------+
| id | name | id | name |
+----+--------+------+--------+
| 1 | Pirate | 2 | Pirate |
| 3 | Ninja | 4 | Ninja |
+----+--------+------+--------+
2 rows in set (0.00 sec)

求差集:

根据上面的例子可以求差集,如下:

 代码如下:

SELECT * FROM A LEFT JOIN B ON A.name = B.name
WHERE B.id IS NULL
union
SELECT * FROM A right JOIN B ON A.name = B.name
WHERE A.id IS NULL;
# 结果
+------+-----------+------+-------------+
| id | name | id | name |
+------+-----------+------+-------------+
| 2 | Monkey | NULL | NULL |
| 4 | Spaghetti | NULL | NULL |
| NULL | NULL | 1 | Rutabaga |
| NULL | NULL | 3 | Darth Vader |
+------+-----------+------+-------------+

 

四.Right join

代码如下:

mysql> select * from A right join B on A.name = B.name;
+------+--------+----+-------------+
| id | name | id | name |
+------+--------+----+-------------+
| NULL | NULL | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| NULL | NULL | 3 | Darth Vader |
| 3 | Ninja | 4 | Ninja |
+------+--------+----+-------------+
4 rows in set (0.00 sec)

同left join。

五.Cross join

cross join:交叉连接,得到的结果是两个表的乘积,即笛卡尔积

笛卡尔(Descartes)乘积又叫直积。假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况

代码如下:

mysql> select * from A cross join B;
+----+-----------+----+-------------+
| id | name | id | name |
+----+-----------+----+-------------+
| 1 | Pirate | 1 | Rutabaga |
| 2 | Monkey | 1 | Rutabaga |
| 3 | Ninja | 1 | Rutabaga |
| 4 | Spaghetti | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | 2 | Pirate |
| 3 | Ninja | 2 | Pirate |
| 4 | Spaghetti | 2 | Pirate |
| 1 | Pirate | 3 | Darth Vader |
| 2 | Monkey | 3 | Darth Vader |
| 3 | Ninja | 3 | Darth Vader |
| 4 | Spaghetti | 3 | Darth Vader |
| 1 | Pirate | 4 | Ninja |
| 2 | Monkey | 4 | Ninja |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | 4 | Ninja |
+----+-----------+----+-------------+
16 rows in set (0.00 sec)
#再执行:mysql> select * from A inner join B; 试一试

#在执行mysql> select * from A cross join B on A.name = B.name; 试一试

实际上,在 MySQL 中(仅限于 MySQL) CROSS JOIN 与 INNER JOIN 的表现是一样的,在不指定 ON 条件得到的结果都是笛卡尔积,反之取得两个表完全匹配的结果。 INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字,因此下面的 SQL 效果是一样的:

代码如下:

... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2

 

六.Full join

代码如下:

mysql> select * from A left join B on B.name = A.name 
-> union 
-> select * from A right join B on B.name = A.name;
+------+-----------+------+-------------+
| id | name | id | name |
+------+-----------+------+-------------+
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | NULL | NULL |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL | NULL |
| NULL | NULL | 1 | Rutabaga |
| NULL | NULL | 3 | Darth Vader |
+------+-----------+------+-------------+
6 rows in set (0.00 sec)

全连接产生的所有记录(双方匹配记录)在表A和表B。如果没有匹配,则对面将包含null

 

七.性能优化

1.显示(explicit) inner join VS 隐式(implicit) inner join

如:

代码如下:

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

VS

代码如下:

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

我在数据库中比较(10w数据)得之,它们用时几乎相同,第一个是显示的inner join,后一个是隐式的inner join。

 

2.left join/right join VS inner join

尽量用inner join.避免 LEFT JOIN 和 NULL.

在使用left join(或right join)时,应该清楚的知道以下几点:

 

2.1 on与 where的执行顺序

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

所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。如:

PASS

代码如下:

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;

Great

代码如下:

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1

从上面例子可以看出,尽可能满足ON的条件,而少用Where的条件。从执行性能来看第二个显然更加省时。

 

2.2 注意ON 子句和 WHERE 子句的不同

如作者举了一个列子:

代码如下:

mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id | weight | exist |
+----+--------+------+--------+-------+
| 1 | 100 | NULL | NULL | NULL |
| 2 | 200 | 2 | 22 | 0 |
| 3 | 300 | NULL | NULL | NULL |
| 4 | 400 | NULL | NULL | NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
 

mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
| 2 | 200 | 2 | 22 | 0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)

从上可知,第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN,然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

 

2.3 尽量避免子查询,而用join

exists和not exists 语法可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否得以保留

往往性能这玩意儿,更多时候体现在数据量比较大的时候,此时,我们应该避免复杂的子查询。如下:

PASS

 代码如下:

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);

Great

代码如下:

insert into t1(a1) 
select b1 from t2 
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id 
where t1.id is null;

 

八.测试题(多表连接join查询)

1. 题目

  现有如下2个保存学校班级比赛的表,一个表保存班级id和班级名,另外一个表保存主场班级id,客场班级id和比赛场数,现在需要连接查询生成主场班级名,客场班级名,比赛场数的表,求具体实现的sql语句.

 

1.1 班级表

mysql> select * from class;
+------+--------+
| c_id | c_name |
+------+--------+
|    1 | 1班    |
|    2 | 2班    |
|    3 | 3班    |
|    4 | 4班    |
+------+--------+
4 rows in set (0.00 sec)

 

1.2 比赛表

mysql> select * from team;
+------+------+------+
| h_id | g_id | num  |
+------+------+------+
|    1 |    2 |   34 |
|    2 |    4 |   37 |
+------+------+------+
2 rows in set (0.00 sec)

 

2. 详解

2.1 分析

  发现要生成2个班级名的表,而一次join连接可以生成一个,则需要2次join连接,由于class表和team表连接内容必须要匹配,所以采用内连接.

2.2 结果

mysql> select ct.h_name,class.c_name as g_name,ct.num from (select class.c_name as h_name,team.* from class join team on class.c_id=team.h_id) as ct join class on ct.g_id=class.c_id;
+--------+--------+------+
| h_name | g_name | num  |
+--------+--------+------+
| 1班    | 2班    |   34 |
| 2班    | 4班    |   37 |
+--------+--------+------+
2 rows in set (0.00 sec)

本文转载自:http://www.jb51.net/article/50427.htm

共有 人打赏支持
文文1
粉丝 19
博文 328
码字总数 113821
作品 0
长沙
程序员
MySQL实战技巧-1:Join的使用技巧和优化

join用于多表中字段之间的联系,在数据库的DML (数据操作语言,即各种增删改查操作)中有着重要的作用。 合理使用Join语句优化SQL有利于: 增加数据库的处理效率,减少响应时间; 减少数据库...

登高且赋 ⋅ 01/29 ⋅ 0

MySQL优化的奇技淫巧之STRAIGHT_JOIN

最近没怎么搞SQL优化,碰巧数据库被慢查询搞挂了,于是拿来练练手。 问题 通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL,如下: SELECT post.*FROM postINNER JOIN posttag ON ...

爱情经纬线 ⋅ 2014/01/15 ⋅ 2

MySQL · 性能优化 · MySQL常见SQL错误用法

前言 MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。但也存在部分客户在使用MySQL数据库的过程中遇...

阿里云RDS-数据库内核组 ⋅ 2017/03/04 ⋅ 0

美团点评 SQL 优化工具--SQLAdvisor

SQLAdvisor 是由美团点评公司北京 DBA 团队开发维护的 SQL 优化工具:输入SQL,输出索引优化建议。 它基于 MySQL 原生词法解析,再结合 SQL 中的 where 条件以及字段选择度、聚合条件、多表 ...

蹩脚的狮子 ⋅ 2017/03/10 ⋅ 9

美团开源 SQL 优化工具 SQLAdvisor,与内部版本保持一致

SQLAdvisor 是由美团点评公司北京 DBA 团队开发维护的 SQL 优化工具:输入SQL,输出索引优化建议,现已开源。 它基于 MySQL 原生词法解析,再结合 SQL 中的 where 条件以及字段选择度、聚合条...

王练 ⋅ 2017/03/10 ⋅ 30

TiDB RC3 发布,提高兼容性和系统稳定性

6 月 16 日,TiDB 正式发布 RC3 版。该版本对 MySQL 兼容性、SQL 优化器、系统稳定性、性能做了大量的工作。性能方面重点优化了负载均衡调度策略和流程。功能方面进一步完善权限管理功能,用...

TiDB ⋅ 2017/06/22 ⋅ 22

SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方 ⋅ 2017/12/25 ⋅ 0

Mysql 和 Postgresql 对比

Mysql 使用太广泛了,以至于我不得不将一些应用从mysql 迁移到postgresql, 很多开源软件都是以Mysql 作为数据库标准,并且以Mysql 作为抽象基础的,但是具体使用过程中,发现Mysql 有很多问题...

宏哥 ⋅ 2010/12/14 ⋅ 63

Mycat - 数据库分库分表中间件,国内最活跃的、性能最好的开源数据库中间件!

Mycat是什么 Mycat - 数据库分库分表中间件,国内最活跃的、性能最好的开源数据库中间件! 一个彻底开源的,面向企业应用开发的大数据库集群 支持事务、ACID、可以替代MySQL的加强版数据库 ...

架构之路 ⋅ 2017/12/05 ⋅ 0

很好的一篇讲解sql优化的文章

【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。 SQL语句如下: SELECT DISTINCT g.*, cp.name AS cp_name, c.nam...

fzxu_05 ⋅ 2014/06/10 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Springboot2 之 Spring Data Redis 实现消息队列——发布/订阅模式

一般来说,消息队列有两种场景,一种是发布者订阅者模式,一种是生产者消费者模式,这里利用redis消息“发布/订阅”来简单实现订阅者模式。 实现之前先过过 redis 发布订阅的一些基础概念和操...

Simonton ⋅ 27分钟前 ⋅ 0

error:Could not find gradle

一.更新Android Studio后打开Project,报如下错误: Error: Could not find com.android.tools.build:gradle:2.2.1. Searched in the following locations: file:/D:/software/android/andro......

Yao--靠自己 ⋅ 昨天 ⋅ 0

Spring boot 项目打包及引入本地jar包

Spring Boot 项目打包以及引入本地Jar包 [TOC] 上篇文章提到 Maven 项目添加本地jar包的三种方式 ,本篇文章记录下在实际项目中的应用。 spring boot 打包方式 我们知道,传统应用可以将程序...

Os_yxguang ⋅ 昨天 ⋅ 0

常见数据结构(二)-树(二叉树,红黑树,B树)

本文介绍数据结构中几种常见的树:二分查找树,2-3树,红黑树,B树 写在前面 本文所有图片均截图自coursera上普林斯顿的课程《Algorithms, Part I》中的Slides 相关命题的证明可参考《算法(第...

浮躁的码农 ⋅ 昨天 ⋅ 0

android -------- 混淆打包报错 (warning - InnerClass ...)

最近做Android混淆打包遇到一些问题,Android Sdutio 3.1 版本打包的 错误如下: Android studio warning - InnerClass annotations are missing corresponding EnclosingMember annotation......

切切歆语 ⋅ 昨天 ⋅ 0

eclipse酷炫大法之设置主题、皮肤

eclipse酷炫大法 目前两款不错的eclipse 1.系统设置 Window->Preferences->General->Appearance 2.Eclipse Marketplace下载【推荐】 Help->Eclipse Marketplace->搜索‘theme’进行安装 比如......

anlve ⋅ 昨天 ⋅ 0

vim编辑模式、vim命令模式、vim实践

vim编辑模式 编辑模式用来输入或修改文本内容,编辑模式除了Esc外其他键几乎都是输入 如何进入编辑模式 一般模式输入以下按键,均可进入编辑模式,左下角提示 insert(中文为插入) 字样 i ...

蛋黄Yolks ⋅ 昨天 ⋅ 0

大数据入门基础:SSH介绍

什么是ssh 简单说,SSH是一种网络协议,用于计算机之间的加密登录。 如果一个用户从本地计算机,使用SSH协议登录另一台远程计算机,我们就可以认为,这种登录是安全的,即使被中途截获,密码...

董黎明 ⋅ 昨天 ⋅ 0

web3j教程

web3j是一个轻量级、高度模块化、响应式、类型安全的Java和Android类库提供丰富API,用于处理以太坊智能合约及与以太坊网络上的客户端(节点)进行集成。 汇智网最新发布的web3j教程,详细讲解...

汇智网教程 ⋅ 昨天 ⋅ 0

谷歌:安全问题机制并不如你想象中安全

腾讯科技讯 5月25日,如今的你或许已经对许多网站所使用的“安全问题机制”习以为常了,但你真的认为包括“你第一个宠物的名字是什么?”这些问题能够保障你的帐户安全吗? 根据谷歌(微博)安...

问题终结者 ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部