MySQL到底是join性能好，還是in一下更快呢？
大家好呀，我是樓仔。
今天發現一篇很有意思的文章，使用 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_ci2.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_ci03 千條數據情況
數據量：用戶表插一千條隨機生成的數據，訂單表插一百條隨機數據
要求：查下所有的訂單以及訂單對應的用戶
耗時衡量指標：多表連接查詢成本 = 一次驅動表成本 + 從驅動表查出的記錄數 * 一次被驅動表的成本
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();
圖片
看時間的話，明顯 join 更快一些。
04 萬條數據情況
user表現在10000條數據，order表10000條試下。
4.1 join
圖片
4.2 in
order 耗時：
圖片
user 耗時：
圖片
4.3 并發場景
join 耗時：
圖片
in 耗時：
圖片
數據量達到萬級別，非并發場景，in 更快，并發場景 join 更快。
05 十萬條數據情況
隨機插入后user表十萬條數據，order表一百萬條試下。
5.1 join
圖片
5.2 in
order 耗時：
圖片
user 耗時：
order查出來的結果過長了...
5.3 并發場景
join 耗時：
圖片
in 耗時：
圖片
數據量達到十萬/百萬級別，非并發場景，in 過長，并發場景 join 更快。
06 總結
實驗結論：
- 數據量不到萬級別，join 和 in 差不多；
- 數據量達到萬級別，非并發場景，in 更快，并發場景 join 更快；
- 數據量達到十萬/百萬級別，非并發場景，in 過長，并發場景 join 更快。
下面是樓仔給出的一些建議。
當數據量比較小時，建議用 in，雖然兩者的性能差不多，但是 join 會增加 sql 的復雜度，后續再變更，會非常麻煩。
當數據量比較大時，建議用 join，主要還是出于查詢性能的考慮。
不過使用 join 時，小表驅動大表，一定要建立索引，join 的表最好不要超過 3 個，否則性能會非常差，還會大大增加 sql 的復雜度，非常不利于后續功能擴展。
