MySQL查詢，是 join性能好，還是in一下更快呢？

作者：張張 2023-12-01 08:27:53

數據庫 MySQL

當數據量小時，可能一頁數據就夠放的時候，join的成本和速度都更好。數據量大的時候確實分開查的成本更低，但是由于數據量大，造成循環的成本更多，代碼執行的時間也就越長。

先總結：

數據量小的時候，用join更劃算
數據量大的時候，join的成本更高，但相對來說join的速度會更快
數據量過大的時候，in的數據量過多，會有無法執行SQL的問題，待解決

事情是這樣的，在一次代碼review的時候有同學提出說，不要寫join，join耗性能還是慢來著，當時也是真的沒有多想，那就寫in好了，最近發現in的數據量過大的時候會導致sql慢，甚至sql太長，直接報錯了。

這次來淺究一下，到底是in好還是join好，僅目前認知探尋，有不對之處歡迎指正

以下實驗僅在本機電腦試驗。

一、表結構

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、訂單表

圖片

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

二、先來試少量數據的情況

用戶表插一千條隨機生成的數據，訂單表插一百條隨機數據

查下所有的訂單以及訂單對應的用戶

下面從三個維度來看

多表連接查詢成本 = 一次驅動表成本 + 從驅動表查出的記錄數 * 一次被驅動表的成本

1、join

JOIN:

explain format=json select order.id, price, user.`name` from `order` join user on order.user_id = user.id;

子查詢:

select order.id,price,user.`name` from `order`,user where user_id=user.id;

圖片

2、分開查

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、代碼層面

主要用php原生寫了腳本，用ab進行10個同時的請求，看下時間，進行比較

ab -n 100 -c 10

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();

圖片

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();

圖片