來源：https://juejin.cn/post/7169567387527282701 先總結： 數據量小的時候，用join更划算 數據量大的時候，join的成本更高，但相對來說join的速度會更快 數據量過大的時候，in的數據量過多，會有無法執行SQL的問題，待解決 事情是這樣的，去年入職的新 ...
來源：https://juejin.cn/post/7169567387527282701
先總結：
- 數據量小的時候，用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
推薦一個開源免費的 Spring Boot 實戰項目：
二、先來試少量數據的情況
用戶表插一千條隨機生成的數據，訂單表插一百條隨機數據
查下所有的訂單以及訂單對應的用戶
下麵從三個維度來看
多表連接查詢成本 = 一次驅動表成本 + 從驅動表查出的記錄數 * 一次被驅動表的成本
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();
看時間的話，明顯join更快一些
三、試下多一些數據的情況
user表現在10000條數據，order表10000條試下
1、join
2、分開
order
user
3、代碼層面
in
join
三、試下多一些數據的情況
隨機插入後user表十萬條數據，order表一百萬條試下
1、join
2、分開
order
user
order查出來的結果過長了,,,
3、代碼層面
in
join
四、到底怎麼才能更好
註：對於本機來說100000條數據不少了，更大的數據量害怕電腦卡死
總的來說，當數據量小時，可能一頁數據就夠放的時候，join的成本和速度都更好。數據量大的時候確實分開查的成本更低，但是由於數據量大，造成迴圈的成本更多，代碼執行的時間也就越長。
實驗過程中發現，當in的數據量過大的時候，sql過長會無法執行，可能還要拆開多條sql進行查詢，這樣的查詢成本和時間一定也會更長，而且如果有分頁的需求的話，也無法滿足。。。
感覺這兩個方法都不是太好，各位小伙伴，有沒有更好的方法呢？
參考資料：
https://www.cnblogs.com/zhuwenjoyce/p/14968183.html
https://blog.csdn.net/xjk201/article/details/122051216
https://juejin.cn/book/6844733769996304392/section/6844733770055024647
近期熱文推薦：
1.1,000+ 道 Java面試題及答案整理(2022最新版)
4.別再寫滿屏的爆爆爆炸類了，試試裝飾器模式，這才是優雅的方式！！
覺得不錯，別忘了隨手點贊+轉發哦！
