MySQL 多对多表只关联一条记录 mysql的多表关联查询

转载

网络小墨舞风 2024-06-26 08:42:27

文章标签 MySQL 多对多表只关联一条记录数据字段关联查询 文章分类 MySQL 数据库

一.单表优化

建表

create table article(
    id int unsigned not null primary key auto_increment,
    -- int类型、无符号、非空、主键、自增
    author_id int unsigned not null,
    -- int类型、无符号、非空
    category_id int unsigned not null,
    -- int类型、无符号、非空
    views int unsigned not null,
    -- int类型、无符号、非空
    comments int unsigned not null,
    -- int类型、无符号、非空
    title varchar(255) not null,
    -- varchar类型保留、非空
    content text not null
    -- text可变长度类型、非空
);

插入数据

insert into article(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) values 
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

需求：查询category_id为1且comments大于1的情况下,views最多的author_id

select author_id from article where category_id=1 and comments>1 order by views desc limit 1;
-- 也就是说他需要的只是author_id，那么select后面就只跟这个author_id，我们完成了category_id为1且comments大于1这个条件后，将views逆排序，那么第一条就是最大的，我们再选取第一条。

二.双表优化

建表

-- 商品类别表
create table class(
    id int unsigned not null primary key auto_increment,
    card int unsigned not null
);

-- 图书表
create table book(
    bookid int unsigned not null auto_increment primary key,
    card int unsigned not null
);

驱动表概念

什么是驱动表？当我们进行表连接的时候，驱动表会驱动另一张表进行查询。

举个例子：

SELECT * FROM class a LEFT JOIN book b ON a.id=b.bookid HAVING a.id=1

这条SQL语句是以class表为驱动表，那么他会在我们的class表中先查询出主键索引 id 然后再去通过这个索引去book表中查找。
那么如果我们的 class 表中只有两条数据，那么我们是不是直接就找到了，再通过这条数据去 book 表中查找，是不是就很快。
那如果book表中有一万条数据，那我们要扫描完这一万条数据，再去class那里扫描那两条数据，就会慢很多。
所以驱动表一定要是小表。驱动表可以是人为指定，也可以是优化器自己选择。

人为指定驱动表

驱动表除了优化器自动选择之外也可以人为的去选择，通过以下指令：

straight_join

比如说以下代码，我们就把 t1 表指定为了驱动表

select * from t1 straight_join t2 on ti.id=t2.id;

三.join的NLJ算法

在我们的数据库使用中，一张表可能满足不了我们的日常需求，那么就会用到表连接，今天我们就来讲一下join的一些优化，为了方便理解我们先建一个表：

CREATE TABLE `test_join` ( /* 创建表test_join */
`id` int(11) NOT NULL auto_increment,
-- id int类型、非空、自增
`a` int(11) DEFAULT NULL,
-- a int类型、默认为空
`b` int(11) DEFAULT NULL,
-- b int类型、默认为空
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
-- create_time datetime类型、非空、自动记录创始时间、注释为记录出创建时间
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
COMMENT '记录更新时间',
-- update_time datetime类型、非空、自动记录创始时间、自创始开始就不断更新时间戳、注释为记录更新时间
PRIMARY KEY (`id`),
-- 指定 id 为主键
KEY `idx_a` (`a`)
-- 创建一个名为 idx_a 的索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 指定引擎为InnoDB、指定字符集为utf8mb4

1.关联查询的算法

关联查询有两种算法

Nested-Loop Join 算法(简称NLJ)
Block Nested-Loop Join 算法(简称BNL)

2.Nested-Loop Join 算法

我们在关联查询的时候，一个简单的 Nested-Loop Join(NLJ)算法是从驱动表中取出一条数据，然后用这一条数据在被驱动表中扫描一遍，不停重复。

那么如果我们的驱动表中没有索引呢？那驱动表中有 n 条数据，被驱动表中有 m 条数据，那岂不是要扫描 n*m 条数据，那数据量大的时候直接原地爆炸

不过好在 MySQL 只有在有索引的情况下才会使用 NLJ 算法，没有索引就会使用 Block Nested-Loop Join 算法，这个只能有MySQL决定，不能进行人为干预。

举例

那么首先我们创建两张表

CREATE TABLE t1(
	id INT(10) PRIMARY KEY auto_increment,
	name VARCHAR(5)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
CREATE TABLE t2(
	id INT(10) PRIMARY KEY auto_increment,
	name VARCHAR(5)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

那么我们现在 name 字段是没有索引的

执行关联查询的SQL语句

explain select * from t1 inner join t2 where t1.name=t2.name;

结果

mysql> explain select * from t1 inner join t2 where t1.name=t2.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

那么从这个执行计划中我们可以看出 t2 为驱动表，因为explain在分析 SQL 语句时第一行的就是驱动表。选择 t2 做驱动表的原因：如果没固定连接方式优化器会优先选择小表做驱动表
但这里并没有使用到 NLJ ，因为我们并没有创建索引，我们如何分辨呢？在分析结果中 Extra 中出现了 Using join buffer (Block Nested Loop)

mysql> explain select * from t1 inner join t2 where t1.name=t2.name;
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL     | NULL    | NULL             |    1 |   100.00 | Using where |
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_name      | idx_name | 23      | demo0128.t2.name |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

大致流程

先从 t2 表中取出一行数据
从第一步的数据中取出要关联的字段，放到 t1 表中筛选
取出 t1 表中满足条件的行，与 t2 表中满足条件的行合并，返回给客户端
不停重复上面三步

假如 t2 有一百条记录， t1 有十万条记录

在这个过程中会读取 t2 表的所有数据，因此这里扫描了 100 行，然后遍历这 100 行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行，这里也扫描了 100 行。因此整个过程扫描了 200 行。

前面我们也说了被驱动表里的关联字段没有索引就会自动选择 BNL 算法，至于为什么呢？下面我们也会说

3.Block Nested-Loop Join 算法

Block Nested-Loop Join(BNL) 算法的思想是：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，如果满足 join 条件，则返回结果给客户端。

回到刚才那条 SQL 语句，被驱动表中并没有索引

explain select * from t1 inner join t2 where t1.name=t2.name;

运行结果如下

mysql> explain select * from t1 inner join t2 where t1.name=t2.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

我们在 Extra 中发现了 Using join buffer (Block Nested Loop) ，那么也就是说这条关联查询使用了 BNL 算法。

大致流程

把 t2 的所有数据放到 join_buffer 中
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据

在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。如果数据量一多，听着都吓人。

4.这两种算法为何这样选择

如果被驱动表的关联字段没有索引,两种算法的区别是这样的：

如果使用 NLJ 算法那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
如果使用 BNL 算法那么磁盘扫描是 100 + 10000=10100 次，在内存中判断 100 * 10000 = 100万次。
那么磁盘扫描是 100 + 10000=10100 次，在内存中判断 100 * 10000 = 100万次。

四.优化关联查询

我们只需要在被驱动表的关联字段添加索引就可以了，当然最好驱动表的关联字段也添加索引。

小表做驱动表

上面我们也讲过 NLJ 算法会扫描驱动表所有的数据，假设驱动表的总行数为 n ，然后遍历这 n 行数据中所有的关联字段的值，根据驱动表中关联字段的值索引扫描被驱动表中的对应行，这里又会扫描 n 行，因此整个过程扫描了 2n 行。当使用 Index Nested-Loop Join 算法时，扫描行数跟驱动表的数据量成正比。那么我们用小表来做驱动表的话扫描的总行数是不是就变小了呢？所以能用小表做为驱动表的，就尽量用小表。

我们可以用 straight_join 来指定驱动表，在指令前面的就是驱动表

select * from t2 straight_join t1 on t2.a = t1.a;

五.临时表

如果有的时候不方便在被驱动表上添加索引，我们可以创建一个临时表

insert into '临时表' select * from t1;

通过这条 SQL 语句把 t1 表的数据传进去在创建索引，切记临时表的表结构要和被驱动表一致。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：android TelephonyManager 获取isDataEnabled android获取当前手机号

下一篇：grafana echarts知乎 echarts graph symbol

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯