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