多表連接配接查詢和多次單表查詢哪個效率高

分享知識 傳遞快樂

在資料量不大的情況下多表連接配接查詢和多次單表查詢的效率差不多。如果資料量足夠大，那肯定是多次單表查詢的效率更高。在一些大的公司裡面，都會禁用多表連接配接查詢，原因就是一旦資料量足夠大的時候多表連接配接查詢效率會很慢，而且不利于分庫分表的查詢優化。

例：

SELECT p.id, sn.name AS nodeName, o.EndTs AS expiryTime, o.PaymentOrder AS consumptionPattern, cus.cusname AS cusName 
FROM cloudlink_port p 
INNER JOIN cloudlink_servernode sn ON sn.id=p.server_node_id AND sn.deleted =0 
INNER JOIN res.res_cusinfcontract o ON o.ContractNo=p.order_no
INNER JOIN res.res_cusinf cus ON cus.id=p.customer_id
WHERE p.order_no='L2021082459016';
           

可以分解成下面這些查詢來代替：

SELECT p.id FROM cloudlink_port p LIMIT 1;
SELECT sn.name FROM cloudlink_servernode sn WHERE sn.deleted =0 LIMIT 1;
SELECT o.EndTs, o.PaymentOrder FROM res.res_cusinfcontract o LIMIT 1;
SELECT cus.cusname FROM res.res_cusinf cus LIMIT 1;
           

需要注意的是，如果查詢隻有1條的情況下，加上 limit 1 效率會更高，

大緻一看，也沒什麼大差别，原本一條查詢，這裡卻變成了多條查詢，傳回結果又是一模一樣。到底為什麼要這樣做？這樣做的優勢是啥？帶着問題往下看。

用分解關聯查詢的方式查詢具有以下優勢：

1. 多次單表查詢，讓緩存的效率更高；許多應用程式可以友善地緩存單表查詢對應的結果對象。對 MYSQL 的查詢緩存來說，如果關聯中的某個表發生了變化，那麼就無法使用查詢緩存了，而拆分後，如果某個表很少改變，那麼基于該表的查詢就可以重複利用查詢緩存結果了。
2. 将查詢分解後，執行單個查詢可以減少鎖的竟争。
3. 在應用層做關聯，可以更容易對資料庫進行拆分，更容易做到高性能和可擴充。很多高性能的應用都會對關聯查詢進行分解。
4. 查詢效率也可能會有所提升；這個例子中，使用 IN() 代替關聯査詢，可以讓 MYSQL 按照 ID 順序進行査詢，這可能比随機的關聯要更高效。
5. 可以減少備援記錄的查詢；在應用層做關聯査詢，意味着對于某條記錄應用隻需要查詢一次，而在資料庫中做關聯查詢，則可能需要重複地通路一部分資料。從這點看，這樣的重構還可能會減少網絡和記憶體的消耗。
6. 這樣做相當于在應用中實作了哈希關聯，而不是使用 MYSQL 的嵌套循環關聯。某些場景哈希關聯的效率要高很多
7. 單表查詢有利于後期資料量大了分庫分表，如果聯合查詢的話，一旦分庫，原來的sql都需要改動。
8. 一些大公司明确規定禁用join，因為資料量大的時候查詢會很慢，是以在資料量不大的情況下，兩種方式的查詢都沒什麼明顯的差别，使用多表連接配接查詢更友善。但是如果在資料量達到幾十萬、幾百萬甚至上億的資料，或者在一些高并發、高性能的應用中，一般建議使用單表查詢。

—————————

如有不足請留言指正

互相學習，共同進步