MySQL到底是join性能好，還是in一下更快呢？

先總結(jié)：

1. 數(shù)據(jù)量小的時(shí)候，用join更劃算
2. 數(shù)據(jù)量大的時(shí)候，join的成本更高，但相對(duì)來(lái)說(shuō)join的速度會(huì)更快
3. 數(shù)據(jù)量過(guò)大的時(shí)候，in的數(shù)據(jù)量過(guò)多，會(huì)有無(wú)法執(zhí)行SQL的問(wèn)題，待解決

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

一、表結(jié)構(gòu)

1、用戶(hù)表

CREATETABLE`user`(
`id`intNOTNULLAUTO_INCREMENT,
`name`varchar(64)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT'姓名',
`gender`smallintDEFAULTNULLCOMMENT'性別',
`mobile`varchar(11)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT'手機(jī)號(hào)',
`create_time`datetimeNOTNULLCOMMENT'創(chuàng)建時(shí)間',
PRIMARYKEY(`id`),
UNIQUEKEY`mobile`(`mobile`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=1005DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_general_ci

2、訂單表

CREATETABLE`order`(
`id`intunsignedNOTNULLAUTO_INCREMENT,
`price`decimal(18,2)NOTNULL,
`user_id`intNOTNULL,
`product_id`intNOTNULL,
`status`smallintNOTNULLDEFAULT'0'COMMENT'訂單狀態(tài)',
PRIMARYKEY(`id`),
KEY`user_id`(`user_id`),
KEY`product_id`(`product_id`)
)ENGINE=InnoDBAUTO_INCREMENT=202DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_general_ci

基于 Spring Boot + MyBatis Plus + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶(hù)小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶(hù)、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

• 項(xiàng)目地址：https://github.com/YunaiV/ruoyi-vue-pro
• 視頻教程：https://doc.iocoder.cn/video/

二、先來(lái)試少量數(shù)據(jù)的情況

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

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

下面從三個(gè)維度來(lái)看

多表連接查詢(xún)成本 = 一次驅(qū)動(dòng)表成本 + 從驅(qū)動(dòng)表查出的記錄數(shù) * 一次被驅(qū)動(dòng)表的成本

1、join

JOIN:

explainformat=jsonselectorder.id,price,user.`name`from`order`joinuseronorder.user_id=user.id;

子查詢(xún):

selectorder.id,price,user.`name`from`order`,userwhereuser_id=user.id;

2、分開(kāi)查

select`id`,price,user_idfrom`order`;

selectnamefromuserwhereidin(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)的所有用戶(hù)id]

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

3、代碼層面

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

ab -n 100 -c 10

in

$mysqli=newmysqli('127.0.0.1','root','root','test');
if($mysqli->connect_error){
die('ConnectError('.$mysqli->connect_errno.')'.$mysqli->connect_error);
}

$result=$mysqli->query('select`id`,price,user_idfrom`order`');
$orders=$result->fetch_all(MYSQLI_ASSOC);

$userIds=implode(',',array_column($orders,'user_id'));//獲取訂單中的用戶(hù)id
$result=$mysqli->query("select`id`,`name`from`user`whereidin({$userIds})");
$users=$result->fetch_all(MYSQLI_ASSOC);//獲取這些用戶(hù)的姓名

//將id做數(shù)組鍵
$userRes=[];
foreach($usersas$user){
$userRes[$user['id']]=$user['name'];
}

$res=[];
//整合數(shù)據(jù)
foreach($ordersas$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=newmysqli('127.0.0.1','root','root','test');
if($mysqli->connect_error){
die('ConnectError('.$mysqli->connect_errno.')'.$mysqli->connect_error);
}

$result=$mysqli->query('selectorder.id,price,user.`name`from`order`joinuseronorder.user_id=user.id;');
$orders=$result->fetch_all(MYSQLI_ASSOC);

var_dump($orders);
$mysqli->close();

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

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶(hù)小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶(hù)、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

• 項(xiàng)目地址：https://github.com/YunaiV/yudao-cloud
• 視頻教程：https://doc.iocoder.cn/video/

三、試下多一些數(shù)據(jù)的情況

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

1、join

2、分開(kāi)

order

user

3、代碼層面

in

join

三、試下多一些數(shù)據(jù)的情況

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

1、join

2、分開(kāi)

order

user

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

3、代碼層面

in

join

四、到底怎么才能更好

注：對(duì)于本機(jī)來(lái)說(shuō)100000條數(shù)據(jù)不少了，更大的數(shù)據(jù)量害怕電腦卡死

總的來(lái)說(shuō)，當(dāng)數(shù)據(jù)量小時(shí)，可能一頁(yè)數(shù)據(jù)就夠放的時(shí)候，join的成本和速度都更好。數(shù)據(jù)量大的時(shí)候確實(shí)分開(kāi)查的成本更低，但是由于數(shù)據(jù)量大，造成循環(huán)的成本更多，代碼執(zhí)行的時(shí)間也就越長(zhǎng)。

實(shí)驗(yàn)過(guò)程中發(fā)現(xiàn)，當(dāng)in的數(shù)據(jù)量過(guò)大的時(shí)候，sql過(guò)長(zhǎng)會(huì)無(wú)法執(zhí)行，可能還要拆開(kāi)多條sql進(jìn)行查詢(xún)，這樣的查詢(xún)成本和時(shí)間一定也會(huì)更長(zhǎng)，而且如果有分頁(yè)的需求的話，也無(wú)法滿足。。。

感覺(jué)這兩個(gè)方法都不是太好，各位小伙伴，有沒(méi)有更好的方法呢？