MySQL到底是join性能好，還是in一下更快呢？
大家好呀，我是樓仔。
今天發(fā)現(xiàn)一篇很有意思的文章，使用 mysql 查詢時，是使用 join 好，還是直接 in 更好，這個大家工作時經(jīng)常遇到。
為了方便大家查看，文章我重新進行了排版。
我沒有直接用作者的結(jié)論，感覺可能會誤導讀者，而是根據(jù)實驗結(jié)果，給出我自己的建議。
不 BB，上目錄：
圖片
01 背景
事情是這樣的，去年入職的新公司，之后在代碼 review 的時候被提出說，不要寫 join，join 耗性能還是慢來著，當時也是真的沒有多想，那就寫 in 好了。
最近發(fā)現(xiàn) in 的數(shù)據(jù)量過大的時候會導致 sql 慢，甚至 sql 太長，直接報錯了。
這次來淺究一下，到底是 in 好還是 join 好，僅目前認知探尋，有不對之處歡迎指正。
以下實驗僅在本機電腦試驗。
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 '手機號',
`create_time` datetime NOT NULL COMMENT '創(chuàng)建時間',
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 '訂單狀態(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_ci03 千條數(shù)據(jù)情況
數(shù)據(jù)量：用戶表插一千條隨機生成的數(shù)據(jù)，訂單表插一百條隨機數(shù)據(jù)
要求：查下所有的訂單以及訂單對應的用戶
耗時衡量指標：多表連接查詢成本 = 一次驅(qū)動表成本 + 從驅(qū)動表查出的記錄數(shù) * 一次被驅(qū)動表的成本
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 并發(fā)場景
主要用php原生寫了腳本，用ab進行10個同時的請求，看下時間，進行比較。
> 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();
圖片
看時間的話，明顯 join 更快一些。
04 萬條數(shù)據(jù)情況
user表現(xiàn)在10000條數(shù)據(jù)，order表10000條試下。
4.1 join
圖片
4.2 in
order 耗時：
圖片
user 耗時：
圖片
4.3 并發(fā)場景
join 耗時：
圖片
in 耗時：
圖片
數(shù)據(jù)量達到萬級別，非并發(fā)場景，in 更快，并發(fā)場景 join 更快。
05 十萬條數(shù)據(jù)情況
隨機插入后user表十萬條數(shù)據(jù)，order表一百萬條試下。
5.1 join
圖片
5.2 in
order 耗時：
圖片
user 耗時：
order查出來的結(jié)果過長了...
5.3 并發(fā)場景
join 耗時：
圖片
in 耗時：
圖片
數(shù)據(jù)量達到十萬/百萬級別，非并發(fā)場景，in 過長，并發(fā)場景 join 更快。
06 總結(jié)
實驗結(jié)論：
- 數(shù)據(jù)量不到萬級別，join 和 in 差不多；
- 數(shù)據(jù)量達到萬級別，非并發(fā)場景，in 更快，并發(fā)場景 join 更快；
- 數(shù)據(jù)量達到十萬/百萬級別，非并發(fā)場景，in 過長，并發(fā)場景 join 更快。
下面是樓仔給出的一些建議。
當數(shù)據(jù)量比較小時，建議用 in，雖然兩者的性能差不多，但是 join 會增加 sql 的復雜度，后續(xù)再變更，會非常麻煩。
當數(shù)據(jù)量比較大時，建議用 join，主要還是出于查詢性能的考慮。
不過使用 join 時，小表驅(qū)動大表，一定要建立索引，join 的表最好不要超過 3 個，否則性能會非常差，還會大大增加 sql 的復雜度，非常不利于后續(xù)功能擴展。
