先總結(jié)：
- 數(shù)據(jù)量小的時(shí)候贮尖，用join更劃算
- 數(shù)據(jù)量大的時(shí)候，join的成本更高趁怔，但相對(duì)來說join的速度會(huì)更快
- 數(shù)據(jù)量過大的時(shí)候湿硝，in的數(shù)據(jù)量過多薪前，會(huì)有無法執(zhí)行SQL的問題，待解決
事情是這樣的关斜，去年入職的新公司示括，之后在代碼review的時(shí)候被提出說，不要寫join蚤吹，join耗性能還是慢來著例诀，當(dāng)時(shí)也是真的沒有多想，那就寫in好了裁着，最近發(fā)現(xiàn)in的數(shù)據(jù)量過大的時(shí)候會(huì)導(dǎo)致sql慢，甚至sql太長拱她，直接報(bào)錯(cuò)了二驰。這次來淺究一下，到底是in好還是join好秉沼，僅目前認(rèn)知探尋桶雀，有不對(duì)之處歡迎指正
以下實(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ī)號(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矗积、訂單表
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ù)
查下所有的訂單以及訂單對(duì)應(yīng)的用戶
下面從三個(gè)維度來看
多表連接查詢成本 = 一次驅(qū)動(dòng)表成本 + 從驅(qū)動(dòng)表查出的記錄數(shù) * 一次被驅(qū)動(dòng)表的成本
1棘捣、join
JOIN: explain format=json select order.id, price, user.
namefromorderjoin user on order.user_id = user.id;子查詢: select order.id,price,user.
namefromorder,user where user_id=user.id;
2、分開查
select
id,price,user_id fromorder;
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í)的請(qǐng)求测砂，看下時(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();
復(fù)制代碼
看時(shí)間的話，明顯join更快一些
三砌些、試下多一些數(shù)據(jù)的情況
user表現(xiàn)在10000條數(shù)據(jù)呜投，order表10000條試下
1抡谐、join
2统屈、分開
user
3、代碼層面
in
join
三等限、試下多一些數(shù)據(jù)的情況
隨機(jī)插入后user表十萬條數(shù)據(jù)有巧，order表一百萬條試下
1释漆、join
2、分開
order
user
order查出來的結(jié)果過長了,,,
3篮迎、代碼層面
in
join
四男图、到底怎么才能更好
注：對(duì)于本機(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過長會(huì)無法執(zhí)行症虑，可能還要拆開多條sql進(jìn)行查詢缩歪，這樣的查詢成本和時(shí)間一定也會(huì)更長，而且如果有分頁的需求的話谍憔，也無法滿足匪蝙。。习贫。
感覺這兩個(gè)方法都不是太好逛球，各位小伙伴，有沒有更好的方法呢苫昌？
