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

先總結(jié)：

1. 數(shù)據(jù)量小的時候，用join更劃算
2. 數(shù)據(jù)量大的時候，join的成本更高，但相對來說join的速度會更快
3. 數(shù)據(jù)量過大的時候，in的數(shù)據(jù)量過多，會有無法執(zhí)行SQL的問題，待解決

事情是這樣的，去年入職的新公司，之后在代碼review的時候被提出說，不要寫join，join耗性能還是慢來著，當(dāng)時也是真的沒有多想，那就寫in好了，最近發(fā)現(xiàn)in的數(shù)據(jù)量過大的時候會導(dǎo)致sql慢，甚至sql太長，直接報錯了。

這次來淺究一下，到底是in好還是join好，僅目前認(rèn)知探尋，有不對之處歡迎指正

以下實驗僅在本機電腦試驗

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

1、用戶表

CREATETABLE`user`(
`id`intNOTNULLAUTO_INCREMENT,
`name`varchar(64)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT'姓名',
`gender`smallintDEFAULTNULLCOMMENT'性別',
`mobile`varchar(11)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT'手機號',
`create_time`datetimeNOTNULLCOMMENT'創(chuàng)建時間',
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 實現(xiàn)的后臺管理系統(tǒng) + 用戶小程序，支持 RBAC 動態(tài)權(quán)限、多租戶、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

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

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

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

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

下面從三個維度來看

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

1、join

JOIN:

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

子查詢:

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

2、分開查

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查出來的所有用戶id]

如此看來，分開查和join查的成本并沒有相差許多

3、代碼層面

主要用php原生寫了腳本，用ab進(jìn)行10個同時的請求，看下時間，進(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'));//獲取訂單中的用戶id
$result=$mysqli->query("select`id`,`name`from`user`whereidin({$userIds})");
$users=$result->fetch_all(MYSQLI_ASSOC);//獲取這些用戶的姓名

//將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();

看時間的話，明顯join更快一些

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

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

三、試下多一些數(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)行查詢，這樣的查詢成本和時間一定也會更長，而且如果有分頁的需求的話，也無法滿足。。。

感覺這兩個方法都不是太好，各位小伙伴，有沒有更好的方法呢？