MySQL查詢，是 join性能好，還是in一下更快呢？
先總結(jié)：
- 數(shù)據(jù)量小的時候，用join更劃算
- 數(shù)據(jù)量大的時候，join的成本更高，但相對來說join的速度會更快
- 數(shù)據(jù)量過大的時候，in的數(shù)據(jù)量過多，會有無法執(zhí)行SQL的問題，待解決
事情是這樣的，在一次代碼review的時候有同學(xué)提出說，不要寫join，join耗性能還是慢來著，當(dāng)時也是真的沒有多想，那就寫in好了，最近發(fā)現(xiàn)in的數(shù)據(jù)量過大的時候會導(dǎo)致sql慢，甚至sql太長，直接報錯了。
這次來淺究一下，到底是in好還是join好，僅目前認(rèn)知探尋，有不對之處歡迎指正
以下實驗僅在本機電腦試驗。
一、表結(jié)構(gòu)
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、訂單表
圖片
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二、先來試少量數(shù)據(jù)的情況
用戶表插一千條隨機生成的數(shù)據(jù)，訂單表插一百條隨機數(shù)據(jù)
查下所有的訂單以及訂單對應(yīng)的用戶
下面從三個維度來看
多表連接查詢成本 = 一次驅(qū)動表成本 + 從驅(qū)動表查出的記錄數(shù) * 一次被驅(qū)動表的成本
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進(jìn)行10個同時的請求，看下時間，進(jìn)行比較
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做數(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
$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更快一些
三、試下多一些數(shù)據(jù)的情況
user表現(xiàn)在10000條數(shù)據(jù)，order表10000條，試下
1、join
圖片
2、分開
order
圖片
user
圖片
3、代碼層面
in
圖片
join
圖片
三、試下多一些數(shù)據(jù)的情況
隨機插入后user表十萬條數(shù)據(jù)，order表一百萬條試下
1、join
圖片
2、分開
order
圖片
user
order查出來的結(jié)果過長了,,,
3、代碼層面
in
圖片
join
圖片
四、到底怎么才能更好
注：對于本機來說100000條數(shù)據(jù)不少了，更大的數(shù)據(jù)量害怕電腦卡死
總的來說，當(dāng)數(shù)據(jù)量小時，可能一頁數(shù)據(jù)就夠放的時候，join的成本和速度都更好。數(shù)據(jù)量大的時候確實分開查的成本更低，但是由于數(shù)據(jù)量大，造成循環(huán)的成本更多，代碼執(zhí)行的時間也就越長。
實驗過程中發(fā)現(xiàn)，當(dāng)in的數(shù)據(jù)量過大的時候，sql過長會無法執(zhí)行，可能還要拆開多條sql進(jìn)行查詢，這樣的查詢成本和時間一定也會更長，而且如果有分頁的需求的話，也無法滿足。。。
感覺這兩個方法都不是太好，各位小伙伴，有沒有更好的方法呢？
