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

作者：樓仔 2023-11-16 12:34:00

數據庫 MySQL

不過使用 join 時，小表驅動大表，一定要建立索引，join 的表最好不要超過 3 個，否則性能會非常差，還會大大增加 sql 的復雜度，非常不利于后續功能擴展。

大家好呀，我是樓仔。

今天發現一篇很有意思的文章，使用 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();

圖片