mysql join查询原理_mysql join查询及原理

最新推荐文章于 2024-07-20 17:30:31 发布
最新推荐文章于 2024-07-20 17:30:31 发布
阅读量75 收藏
点赞数
文章标签： mysql join查询原理
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_29248313/article/details/113220322
版权

CREATE DATABASE mysql_join_demo;

CREATE TABLE users(

id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(100) NOT NULL DEFAULT '',

pass VARCHAR(100) NOT NULL DEFAULT '',

age INT NOT NULL DEFAULT 0,

address VARCHAR(100) NOT NULL DEFAULT '',

PRIMARY KEY(id)

);

CREATE TABLE orders(

id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(100) NOT NULL DEFAULT '',

user_id INT NOT NULL DEFAULT 0,

price FLOAT NOT NULL DEFAULT 0,

PRIMARY KEY(id)

);

INSERT INTO users(name,pass,age,address)

VALUES

('neo','123',23,'Beijing'),

('neo','123',23,'Beijing'),

('neo','123',23,'Beijing'),

('neo','123',23,'Beijing');

INSERT INTO orders(name,user_id,price)

VALUES('order-1',1,12.3),('order-2',1,12.4),('order-3',1,12.4),('order-4',2,12.5);

1.交叉查询

交叉查询就是将多个表做笛卡尔积,也就是一个交叉乘积

mysql> SELECT * FROM users,orders;

+----+------+------+-----+---------+----+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |

+----+------+------+-----+---------+----+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |

| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |

| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |

| 1 | neo | 123 | 22 | Beijing | 4 | order-4 | 2 | 12.5 |

| 2 | neo | 123 | 23 | Beijing | 1 | order-1 | 1 | 12.3 |

| 2 | neo | 123 | 23 | Beijing | 2 | order-2 | 1 | 12.4 |

| 2 | neo | 123 | 23 | Beijing | 3 | order-3 | 1 | 12.4 |

| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |

| 3 | neo | 123 | 23 | Beijing | 1 | order-1 | 1 | 12.3 |

| 3 | neo | 123 | 23 | Beijing | 2 | order-2 | 1 | 12.4 |

| 3 | neo | 123 | 23 | Beijing | 3 | order-3 | 1 | 12.4 |

| 3 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |

| 4 | neo | 123 | 23 | Beijing | 1 | order-1 | 1 | 12.3 |

| 4 | neo | 123 | 23 | Beijing | 2 | order-2 | 1 | 12.4 |

| 4 | neo | 123 | 23 | Beijing | 3 | order-3 | 1 | 12.4 |

| 4 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |

| 5 | neo | 123 | 23 | Beijing | 1 | order-1 | 1 | 12.3 |

| 5 | neo | 123 | 23 | Beijing | 2 | order-2 | 1 | 12.4 |

| 5 | neo | 123 | 23 | Beijing | 3 | order-3 | 1 | 12.4 |

| 5 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |

+

交叉查询+两个表的对等条件＝inner join

mysql> SELECT * FROM users u,orders o WHERE u.id=o.user_id;

+----+------+------+-----+---------+----+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |

+----+------+------+-----+---------+----+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |

| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |

| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |

| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |

+----+------+------+-----+---------+----+---------+---------+-------+

mysql> SELECT * FROM users u INNER JOIN orders o ON u.id=o.user_id;

+----+------+------+-----+---------+----+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |

+----+------+------+-----+---------+----+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |

| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |

| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |

| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |

+

left join

left join主要以from后面的表为基础表,对于join的表没有与其对应的补NULL

mysql> SELECT * FROM users u LEFT JOIN orders o ON u.id=o.user_id;

+----+------+------+-----+---------+------+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |

+----+------+------+-----+---------+------+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |

| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |

| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |

| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |

| 3 | neo | 123 | 23 | Beijing | NULL | NULL | NULL | NULL |

| 4 | neo | 123 | 23 | Beijing | NULL | NULL | NULL | NULL |

| 5 | neo | 123 | 23 | Beijing | NULL | NULL | NULL | NULL |

+

right join不会补齐，而是将不存在的NULL值去掉

mysql> SELECT * FROM users u RIGHT JOIN orders o ON u.id=o.user_id;

+------+------+------+------+---------+----+---------+---------+-------+

| id | name | pass | age | address | id | name | user_id | price |

+------+------+------+------+---------+----+---------+---------+-------+

| 1 | neo | 123 | 22 | Beijing | 1 | order-1 | 1 | 12.3 |

| 1 | neo | 123 | 22 | Beijing | 2 | order-2 | 1 | 12.4 |

| 1 | neo | 123 | 22 | Beijing | 3 | order-3 | 1 | 12.4 |

| 2 | neo | 123 | 23 | Beijing | 4 | order-4 | 2 | 12.5 |

+

mysql的join在添加条件的时候可以使用using关键字，前提是两个关联字段必须一样-_-!

mysql的join原理

mysql join采用的算法就是Nested-Loop Join 传送门(http://dev.mysql.com/doc/refman/5.0/en/nested-loop-joins.html),

例如一个SQL是这样的select * from users join orders,这个东西在mysql内部的实现的伪代码

FOR ORDER IN orders{

FOR USER IN users{

send USER+ORDER

}

}

这里还需要描述一个join_buffer_size,这个值定义当执行join操作的时候系统分配的内存

上面的查询其实已经使用了join_buffer,看执行计划里面Extra这个值Using join buffer

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM users JOIN orders;

+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |

+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 4 | |

| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 5 | USING JOIN buffer |

+

(这里预留一个问题，为什么以orders表做了基表?)

join buffer使用就是将外层的查询结果放置在一片内存里，这块内存就是join buffer,大小就是系统启动时候设置的join_buffer_size

1.join buffer在join的查询为ALL,index,range的时候

2.一个语句可能用到多个join buffer

3.join buffer在语句执行完的时候被释放

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM users JOIN orders JOIN users u;

+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |

+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 5 | |

| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 5 | USING JOIN buffer |

| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 5 | USING JOIN buffer |

+

为什么用orders做基表，因为orders表里面只有4条记录，而users表里面有5条记录，mysql的优化器在优化的时候，会以扫描记录数量的多少来决定以哪个表作为基表

mysql> INSERT INTO orders(name,user_id) VALUES('order-5',2);

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM users JOIN orders;

+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |

+----+-------------+--------+------+---------------+------+---------+------+------+-------------------+

| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 5 | |

| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 5 | USING JOIN buffer |

+

谭康熹
关注
Mysql的join连接查询的原理以及优化
luxuenaliuxin的博客
06-11 490
mysql只支持一种join算法：Nested-Loop Join（嵌套循环连接），但Nested-Loop Join有三种变种：Simple Nested-Loop Join，Index Nested-Loop Join，Block Nested-Loop Join （注：参考公众号：InsideMySQL） 原理： 1.Simple Nested-Loop Join： 如下图，r为驱动表...
浅析 MySQL 中 join 查询
java_beautiful的博客
11-08 917
​复制代码上面这两条 SQL 我们加上了条件 t2.id
Mysql连接join查询原理知识点
12-16
Mysql连接（join）查询 1、基本概念 将两个表的每一行，以“两两横向对接”的方式，所得到的所有行的结果。 假设： 表A有n1行，m1列； 表B有n2行，m2列； 则表A和表B“对接”之后，就会有： n1*n2行； m1+m2列。 2、则他们对接（连接）之后的结果类似这样： 3、连接查询基本形式： from  表1  【连接方式】 join  表2  【on连接条件】连接查询基本形式： from  表1  【连接方式】 join  表2  【on连接条件】 1、连接查询的分类 交叉连接 其实就是两个表之间按连接的基本概念，进行连接之后所得到的“所有数据”，而对此无任何“筛选”的结
MySQL join查询的原理
CaptainCats的博客
01-25 2074
MySQL join查询的原理MySQL用Nested-Loop Join算法实现join查询Nested-Loop Join有三种实现SNLJBNLJINLJ聚集索引非聚集索引NLJ优先级如何优化join查询效率 MySQL用Nested-Loop Join算法实现join查询 区分驱动表和被驱动表，以驱动表的结果集为循环的基础， 访问被驱动表过滤数据，然后合并结果，驱动表在外循环、被驱动表在内循环。 如果还有第三张参与join查询的表， 则以合并的结果为驱动表，第三张表作为被驱动表，以此类推。 left
MySQL：JOIN 多表查询
最新发布
Zachyy的博客
07-20 997
若 table_1 有 N 行，table_2 有 M 行，则交叉连接返回的结果为 N*M 行，当表中的数据较多时，得到的运行结果会非常长，而且得到的运行结果也没太大的意义。，即将左表的每一行与右表的每一行进行组合，返回的结果集将包含左表和右表中所有记录之间的所有可能的组合。通过 JOIN 操作，我们可以将多个表中的数据按照指定的连接条件进行关联，并获取到相关联的数据。外连接是 SQL 中的一种表连接操作，它能够返回连接条件匹配的记录，同时。（右外连接）是 SQL 中的一种表连接操作，是一种单向连接，是。
Mysql join查询 及其原理
m0_62616608的博客
02-26 969
每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配这样周而复始，大大增加了I0的次 数。6、在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数少的表作为驱动表(外表)。
什么是MySQL的 join 查询
Java_LingFeng的博客
11-02 321
相信大家 MySQL 都用了很久了，各种 join 查询天天都在写，但是 join 查询到底是怎么查的，怎么写才是最正确的，今天我就和大家一起学习探讨一下
MySQL JOIN查询原理详细讲解
m0_37449634的博客
02-06 156
mysql join
mysql join 缓存_mysql 服务器参数调优之join_buffer_size
weixin_30631909的博客
02-17 6094
join bufferJoin Buffer 可被用于联接是ALL、index、和range的类型;每次联接使用一个Join Buffer，因此多表的联接可以使用多个Join Buffer；Join Buffer在联接发生之前进行分配，在SQL语句执行完后进行释放；Join Buffer只存储要进行查询操作的相关列数据，而不是整行的记录。1、Using join buffer (Block Nes...
MySQL中（JOIN/ORDER BY）语句的查询过程及优化方法
01-19
在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句，并举了一个慢查询例子： 可以看到上述的查询需要检查1万多记录，并且使用了临时表和filesort排序，这样的查询在用户数快速增长后将成为噩梦...
mysql join子查询_INNER JOIN中的子查询(MySQL)
weixin_30290193的博客
01-18 2775
我似乎在内连接中有一个子查询有问题,但是我没有.>服务器：通过UNIX套接字的Localhost>软件：MySQL>软件版本：5.5.32-nmm2-log – (Ubuntu)>数据库客户端版本：libmysql – 5.5.32有一个m：n表构造,有3个表,其中只有2个与问题相关.>第一个是包含actor的表,标识符：caid.>第二个是跨表连接演员到电影(...
查询语义网SPARQL
weixin_43886056的博客
09-27 463
查询语义网：SPARQL 文章目录查询语义网：SPARQL一、概述二、基础设施三、匹配模式四、过滤器五、其它六、利用SPARQL查询七、总结 一、概述 SPARQL是RDF的查询语言，适合并依赖于万维网上的各种技术。SPARQL能够让我们通过选择、抽取等方式从被表示为RDF的知识中获取特定的部分。 二、基础设施 三元组存储库(triple store)，可以看做是RDF的数据库，也被称作图存储库。每个三元组存储库都提供一个端点(endpoint)，客户端使用HTTP协议向端点发送查询。 三、匹配模式 在SP
Mysql中Join查询使用与原理
任天柳
03-08 3028
在Mysql中我们最终的一个操作就是查询，查询中又要当属join的应用，这里我们在SQL的层面介绍join的7中产寻区别于数据的覆盖范围，但是不同的DB他的join使用可能略有不同。eg：Mysql中是无法使用外连接查询。这个文章中的内容都来自与B站周阳老师，记录这个笔记主要也是为了加深一下自己的认识。 一、概述与建表语句 1、概述 在Mysql中如果对join的类型不说明则默认采用的内连接——inner join。关于join这几种情况的使用通过下面的这张图以及后边的示例语句就没有什么问题了，但是他在.
mysql+join的原理,Mysql连接join查询原理知识点
weixin_30937427的博客
03-17 245
Mysql连接join查询原理知识点Mysql连接(join)查询1、基本概念将两个表的每一行，以“两两横向对接”的方式，所得到的所有行的结果。假设：表A有n1行，m1列；表B有n2行，m2列；则表A和表B“对接”之后，就会有：n1*n2行；m1+m2列。2、则他们对接(连接)之后的结果类似这样：3、连接查询基本形式：from 表1 【连接方式】 join 表2 【on连接条件】连接查询...
MySQL Join查询
架构之路
01-22 410
SQL 执行顺序
MySQL-join查询
weixin_44793021的博客
05-27 371
MySQL-join查询的基本用法
mysql join查询
eagle89的专栏
07-15 704
0.准备 join主要根据两表或多表之间列的关系，从这些表中进行数据的查询。 首先创建两张表：tb_emp(员工表)和tb_dept(部门表)，并插入相关测试数据。 1.tb_emp表。 DROP TABLE IF EXISTS `tb_emp`; CREATE TABLE `tb_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nam...
mysql join查询_MySQL JOIN查询
weixin_36194075的博客
01-27 173
一般而言，如果要设计一个小型数据库(指代码量少)，但又要适应海量数据及访问的性能需求，最有效的方法莫过于针对主要应用场景选择一个或几个性能优异的核心算法作为引擎，然后努力将一些非主要应用场景作为该算法的特例或变种植入到引擎当中。MySQL、PostgreSQL 等就是这么做的。在 MySQL 的 SELECT 查询当中，其核心算法就是 JOIN 查询算法。其他的查询语句都相应向 JOIN 靠拢：...
Mysql----Join查询
qq_50933251的博客
04-26 718
left join左连接查询时，如下： SELECT s.studentno,s.studentname,r.studentno,r.studentresult FROM `student` AS s LEFT JOIN `result` AS r ON s.studentno=r.studentno 结果为： 可以清晰的发现，即使成绩表中的学号为null也能正常显示，所以说这二张表的主导地位是student(学生表),也可以理解为：当查询的数据s.studentno有值时为真，会返回数据。当然，这一
MySQL Join查询详解：内连接与外连接实例
"mysql中各种常见join连表查询实例总结" 在数据库管理中，尤其是使用MySQL进行数据查询时，JOIN操作是不可或缺的一部分，它允许我们从多个表中联接数据以获得所需的结果集。本篇文章主要关注MySQL中的JOIN类型及其...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值