MySQL到底是join性能好，还是in一下更快呢？

吾爱主题阅读：181 2023-11-16 14:02:00 评论：0

大家好呀，我是楼仔。

今天发现一篇很有意思的文章，使用 mysql 查询时，是使用 join 好，还是直接 in 更好，这个大家工作时经常遇到。

为了方便大家查看，文章我重新进行了排版。

我没有直接用作者的结论，感觉可能会误导读者，而是根据实验结果，给出我自己的建议。

不 BB，上目录：

图片

01 背景

事情是这样的，去年入职的新公司，之后在代码 review 的时候被提出说，不要写 join，join 耗性能还是慢来着，当时也是真的没有多想，那就写 in 好了。

最近发现 in 的数据量过大的时候会导致 sql 慢，甚至 sql 太长，直接报错了。

这次来浅究一下，到底是 in 好还是 join 好，仅目前认知探寻，有不对之处欢迎指正。

以下实验仅在本机电脑试验。

02 表结构

2.1 用户表

图片

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `gender` smallint DEFAULT NULL COMMENT '性别',
  `mobile` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '手机号',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mobile` (`mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

2.2 订单表

图片

CREATE TABLE `order` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `price` decimal(18,2) NOT NULL,
  `user_id` int NOT NULL,
  `product_id` int NOT NULL,
  `status` smallint NOT NULL DEFAULT '0' COMMENT '订单状态',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

03 千条数据情况

数据量：用户表插一千条随机生成的数据，订单表插一百条随机数据

要求：查下所有的订单以及订单对应的用户

耗时衡量指标：多表连接查询成本 = 一次驱动表成本 + 从驱动表查出的记录数 * 一次被驱动表的成本

3.1 join

select order.id, price, user.name from order join user on order.user_id = user.id;

图片

3.2 in

select id,price,user_id from order;

图片

select name from user where id in (8, 11, 20, 32, 49, 58, 64, 67, 97, 105, 113, 118, 129, 173, 179, 181, 210, 213, 215, 216, 224, 243, 244, 251, 280, 309, 319, 321, 336, 342, 344, 349, 353, 358, 363, 367, 374, 377, 380, 417, 418, 420, 435, 447, 449, 452, 454, 459, 461, 472, 480, 487, 498, 499, 515, 525, 525, 531, 564, 566, 580, 584, 586, 592, 595, 610, 633, 635, 640, 652, 658, 668, 674, 685, 687, 701, 718, 720, 733, 739, 745, 751, 758, 770, 771, 780, 806, 834, 841, 856, 856, 857, 858, 882, 934, 942, 983, 989, 994, 995);

其中 in 的是order查出来的所有用户 id。

图片

如此看来，分开查和 join 查的成本并没有相差许多。

3.3 并发场景

主要用php原生写了脚本，用ab进行10个同时的请求，看下时间，进行比较。

> ab -n 100 -c 10 // 执行脚本

下面是 join 查询的执行脚本：

$mysqli = new mysqli('127.0.0.1', 'root', 'root', 'test');
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

$result = $mysqli->query('select order.id, price, user.`name` from `order` join user on order.user_id = user.id;');
$orders = $result->fetch_all(MYSQLI_ASSOC);

var_dump($orders);
$mysqli->close();

图片

下面是 in 查询的执行脚本：

$mysqli = new mysqli('127.0.0.1', 'root', 'root', 'test');
 if ($mysqli->connect_error) {
     die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
 }

 $result = $mysqli->query('select `id`,price,user_id from `order`');
 $orders = $result->fetch_all(MYSQLI_ASSOC);

 $userIds = implode(',', array_column($orders, 'user_id')); // 获取订单中的用户id
 $result = $mysqli->query("select `id`,`name` from `user` where id in ({$userIds})");
 $users = $result->fetch_all(MYSQLI_ASSOC);// 获取这些用户的姓名

 // 将id做数组键
 $userRes = [];
 foreach ($users as $user) {
     $userRes[$user['id']] = $user['name'];
 }

 $res = [];
 // 整合数据
 foreach ($orders as $order) {
     $current = [];
     $current['id'] = $order['id'];
     $current['price'] = $order['price'];
     $current['name'] = $userRes[$order['user_id']] ?: '';
     $res[] = $current;
 }
 var_dump($res);

 // 关闭mysql连接

 $mysqli->close();

图片