大家好呀，我是樓仔。

今天發(fā)現(xiàn)一篇很有意思的文章，使用 mysql 查詢時(shí)，是使用 join 好，還是直接 in 更好，這個(gè)大家工作時(shí)經(jīng)常遇到。

為了方便大家查看，文章我重新進(jìn)行了排版。

我沒(méi)有直接用作者的結(jié)論，感覺(jué)可能會(huì)誤導(dǎo)讀者，而是根據(jù)實(shí)驗(yàn)結(jié)果，給出我自己的建議。

不 BB，上目錄：

圖片

01 背景

事情是這樣的，去年入職的新公司，之后在代碼 review 的時(shí)候被提出說(shuō)，不要寫 join，join 耗性能還是慢來(lái)著，當(dāng)時(shí)也是真的沒(méi)有多想，那就寫 in 好了。

最近發(fā)現(xiàn) in 的數(shù)據(jù)量過(guò)大的時(shí)候會(huì)導(dǎo)致 sql 慢，甚至 sql 太長(zhǎng)，直接報(bào)錯(cuò)了。

這次來(lái)淺究一下，到底是 in 好還是 join 好，僅目前認(rèn)知探尋，有不對(duì)之處歡迎指正。

以下實(shí)驗(yàn)僅在本機(jī)電腦試驗(yàn)。

02 表結(jié)構(gòu)

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 '手機(jī)號(hào)',
  `create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
  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 '訂單狀態(tài)',
  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 千條數(shù)據(jù)情況

數(shù)據(jù)量：用戶表插一千條隨機(jī)生成的數(shù)據(jù)，訂單表插一百條隨機(jī)數(shù)據(jù)

要求：查下所有的訂單以及訂單對(duì)應(yīng)的用戶

耗時(shí)衡量指標(biāo)：多表連接查詢成本 = 一次驅(qū)動(dòng)表成本 + 從驅(qū)動(dòng)表查出的記錄數(shù) * 一次被驅(qū)動(dòng)表的成本

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查出來(lái)的所有用戶 id。

圖片

如此看來(lái)，分開(kāi)查和 join 查的成本并沒(méi)有相差許多。

3.3 并發(fā)場(chǎng)景

主要用php原生寫了腳本，用ab進(jìn)行10個(gè)同時(shí)的請(qǐng)求，看下時(shí)間，進(jìn)行比較。

> ab -n 100 -c 10 // 執(zhí)行腳本

下面是 join 查詢的執(zhí)行腳本：

$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 查詢的執(zhí)行腳本：

$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做數(shù)組鍵
 $userRes = [];
 foreach ($users as $user) {
     $userRes[$user['id']] = $user['name'];
 }

 $res = [];
 // 整合數(shù)據(jù)
 foreach ($orders as $order) {
     $current = [];
     $current['id'] = $order['id'];
     $current['price'] = $order['price'];
     $current['name'] = $userRes[$order['user_id']] ?: '';
     $res[] = $current;
 }
 var_dump($res);

 // 關(guān)閉mysql連接

 $mysqli->close();

圖片

看時(shí)間的話，明顯 join 更快一些。

04 萬(wàn)條數(shù)據(jù)情況

user表現(xiàn)在10000條數(shù)據(jù)，order表10000條試下。

4.1 join

圖片

4.2 in

order 耗時(shí)：

圖片

user 耗時(shí)：

圖片

4.3 并發(fā)場(chǎng)景

join 耗時(shí)：

圖片

in 耗時(shí)：

圖片

數(shù)據(jù)量達(dá)到萬(wàn)級(jí)別，非并發(fā)場(chǎng)景，in 更快，并發(fā)場(chǎng)景 join 更快。

05 十萬(wàn)條數(shù)據(jù)情況

隨機(jī)插入后user表十萬(wàn)條數(shù)據(jù)，order表一百萬(wàn)條試下。

5.1 join

圖片

5.2 in

order 耗時(shí)：

圖片

user 耗時(shí)：

order查出來(lái)的結(jié)果過(guò)長(zhǎng)了...

5.3 并發(fā)場(chǎng)景

join 耗時(shí)：

圖片

in 耗時(shí)：

圖片

數(shù)據(jù)量達(dá)到十萬(wàn)/百萬(wàn)級(jí)別，非并發(fā)場(chǎng)景，in 過(guò)長(zhǎng)，并發(fā)場(chǎng)景 join 更快。

06 總結(jié)

實(shí)驗(yàn)結(jié)論：

• 數(shù)據(jù)量不到萬(wàn)級(jí)別，join 和 in 差不多；
• 數(shù)據(jù)量達(dá)到萬(wàn)級(jí)別，非并發(fā)場(chǎng)景，in 更快，并發(fā)場(chǎng)景 join 更快；
• 數(shù)據(jù)量達(dá)到十萬(wàn)/百萬(wàn)級(jí)別，非并發(fā)場(chǎng)景，in 過(guò)長(zhǎng)，并發(fā)場(chǎng)景 join 更快。

下面是樓仔給出的一些建議。

當(dāng)數(shù)據(jù)量比較小時(shí)，建議用 in，雖然兩者的性能差不多，但是 join 會(huì)增加 sql 的復(fù)雜度，后續(xù)再變更，會(huì)非常麻煩。

當(dāng)數(shù)據(jù)量比較大時(shí)，建議用 join，主要還是出于查詢性能的考慮。

不過(guò)使用 join 時(shí)，小表驅(qū)動(dòng)大表，一定要建立索引，join 的表最好不要超過(guò) 3 個(gè)，否則性能會(huì)非常差，還會(huì)大大增加 sql 的復(fù)雜度，非常不利于后續(xù)功能擴(kuò)展。