乐趣区

关于后端:浅谈-MySQL-连表查询

jiezi

浅谈 MySQL 连表查问

连表查问是一把双刃剑, 长处是适应范式, 缩小数据冗余; 毛病是连表查问特地是多张表的连表会减少数据库的累赘, 升高查问效率.

简介

连表查问就是 2 张表或者多张表的联结查问, 联结查问的后果称之为 “ 笛卡尔积 ”, 假如 A 表中有 n 条记录, B 表中有 m 条记录, “ 笛卡尔积 ” 就是 n*m

各种连表查问的实质就是 对笛卡尔积的过滤

  • 全查问: 全量查问笛卡尔积, n*m 种后果, 不加关键字过滤
  • 内连贯: 关键字是 INNER JOIN, JOIN, WHERE, 或者天然匹配(省略连表条件, 不倡议应用!!!)

  • 外连贯:

    • 左外连贯: 关键字是 LEFT JOIN, LEFT OUTER JOIN
    • 右外连贯: 关键字是 RIGHT JOIN, RIGHT OUTER JOIN

这里有一张神图

数据筹备

create table `user_a` (`aid` int(11) NOT NULL AUTO_INCREMENT,
  `a_name` varchar(255) NOT NULL,
  `age` smallint NOT NULL,
  PRIMARY KEY(`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表 A';

create table `user_b` (`bid` int(11) NOT NULL AUTO_INCREMENT,
  `b_name` varchar(255) NOT NULL,
  `age` smallint NOT NULL,
  PRIMARY KEY(`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表 B';

# 插入测试数据
INSERT INTO `user_a`(aid, a_name, age) VALUES(1, 'test1', 1),(2, 'test2', 2),(3, 'test3', 3);
INSERT INTO `user_b`(bid, b_name, age) VALUES(1, 'test2', 2),(2, 'test3', 3),(4, 'test4', 4);

全查问

# 全查问: 上面的后果都是等价的
# [举荐] INNER JOIN
SELECT * FROM user_a INNER JOIN user_b;
SELECT * FROM user_a, user_b;
SELECT * FROM user_a JOIN user_b;
SELECT * FROM user_a CROSS JOIN user_b;

后果: 全量查问 n*m 种可能性

内连贯

对 n*m 的笛卡尔积进行过滤, 用 WHERE 或者 ON 关键字都是等价的(内连贯是等价的, 外连贯不是等价的)

(倡议应用 ON 关键字, 约定俗称)

# 内连贯: 上面的后果都是等价的
# [举荐] INNER JOIN + ON
SELECT * FROM user_a a INNER JOIN user_b b ON a.a_name=b.b_name;
# INNER JOIN + WHERE
SELECT * FROM user_a a JOIN user_b b ON a.a_name=b.b_name;
# JOIN + ON
SELECT * FROM user_a a JOIN user_b b ON a.a_name=b.b_name;
# JOIN + WHERE
SELECT * FROM user_a a JOIN user_b b WHERE a.a_name=b.b_name;
# 多表 + WHERE
SELECT * FROM user_a a, user_b b WHERE a.a_name=b.b_name;

后果: 只有右边和左边同时存在才会返回再后果集外面

外连贯

外连贯包含左连贯和右连贯

左连贯

# 左连贯
# [惟一写法] LEFT JOIN + ON
SELECT * FROM user_a a LEFT JOIN user_b b ON a.a_name=b.b_name;

后果: 保障右边的数据残缺, 如果左边没有该数据, 则左边的数据为 NULL

右连贯

# 右连贯
# [惟一写法] RIGHT JOIN + ON
SELECT * FROM user_a a RIGHT JOIN user_b b ON a.a_name=b.b_name;

后果: 保障左边的数据残缺, 如果右边没有该数据, 则右边的数据为 NULL

ON 和 WHERE 的区别

ON 和 WHERE 在内连表的时候是没有区别的 (举荐应用 INNER JOIN + ON 的标准写法)

外连贯只能用 ON, 外连贯用 WHERE 间接语法错误

为什么要小表驱动大表

因为连贯查问的实质是遍历右边表的记录去匹配左边表的记录, 右边表的复杂度是 O(n), 左边表能走索引 O(log n)

reference

https://juejin.cn/post/704381…

本文由 mdnice 多平台公布

退出移动版