一條LEFT JOIN+ORDER BY的sql語句優化問題?

實習在做一個項目，需要查兩張表然後進行排序，結果可能數據太大查詢超時很嚴重...想了很多辦法都不知道怎麼優化，希望有前輩指導一下方向

---

你這個是用Mysql嗎？那基本無解，因為Mysql的Join想快只能是Index Look-up Join。

可以試試TiDB，非常適合你這個場景，而且兼容MySQL語法和協議。

對這個語句可以對produce time建立索引，這樣Join的方式是Hash Join，而且沒有排序的代價。

---

left join的key和order by的key不一樣，確實會造成其中一個索引失去作用。這沒有什麼好辦法，你要換一種方法來寫。你這個sql無非就是想找到所有ams_cost_type_fomular_system裡面存在的ams_finance_account_data，你可以嘗試一下【再】給ams添加一個(producetime, feeType)的索引，理論上如果MySQL夠聰明的話，可以不用遍歷表/中間結果。雖然我對此不抱希望，畢竟是MySQL。

---

18行之後出現的所有欄位名都加上索引，資料庫存入ssd，基本上只有這些優化途徑了。

---

where換成and試試？

---

單純就這個query來說，語句級別的優化餘地有限。不過我還是想就你的提問方式給一些建議。

你在問問題的時候，應該給出DDL和更多的統計數據，比如關鍵column的cardinality，分布是否均勻，predicate的ff等，這些都有助於熱心人更好的了解你的數據，給出更好的建議。

以前我在IBM CDL DB2/z團隊工作過3年多，我們當時做的是一個專門幫助DBA做查詢優化的工具。我在其中負責一個模塊，叫Query Annotation，作用就是格式化一個query，並且把相關的統計數據放在表和列的旁邊，讓DBA一目了然。

下面就是一張效果圖，還是bing出來的，基本意思到了，供參考。

---

我接觸的項目 剛剛好 就有一個Left Join查詢

每次查詢 5%-9%的CPU使用

生產環境下 這個表又沒辦法改

我最後的辦法是 把一次查詢 分解成幾次查詢

原來的結果是同時展現幾個表的內容

新的設計是引導用戶去點擊詳情 獲取其他表的內容

可能不是最好的方案 但是實用

---

You can write different SQL to get same results:

1 make sure index are created and used

2 try another way to get the result

such as:

SELECT *

FROM

( SELECT ams* FROM ams WHERE ams.define1 = "3") t LEFT JOIN s

ON t.type = s.type

ORDER BY t.time DESC

3 When writing SQL, keep time complexity, space complexity, data set, divide and conquer in mind.

My pleasure.

---

這類事情要看場景，如果數據量大，如果幾秒內返回，客戶可以接受也行，（各位不要懷疑，做內網應用經常的事，不稀奇），但要在0.0幾秒返回結果，可以評估下，你的數據變動頻繁嗎，如果不頻繁變化少，就直接在數據變動後，後台起任務查緩存結果，用的時候直接查結果。如果變動多，則將需要用到的數據做成小數據量表。最後實在不行，那就不要考慮資料庫抗了。最後提醒一點，問問需求方，他們真的要這麼查嗎，有的時候他們會說，我們基本不查。

---

看語句我覺得你加個索引會有改善，但對此並不樂觀。

看別人已經說得很多了，我說些不直接從語句上下手的。

一、如果行數多查詢頻繁，我認為最徹底的解決辦法是從設計上下刀把這個規避過去。比如加開一個表（看欄位名什麼的，感覺這個表增刪改應該不是很頻繁，查詢的多）存這些數據，把時間分攤到每次操作里去，這樣子直接從表裡查數據即可，當然這會引起每次增刪改多操作一個表。

二、至於define那個能不能也換個設計？比如用二進位位或者乾脆直接合併到一個？這樣子就可以不or了。如果每次都是查詢3 or 3的乾脆空間換時間，開表，把這個or化整為零放到增刪改裡面那就什麼都不用干直接調取就行了。

三、另外可以建一個緩存，這樣子壓力就乾脆不在庫上了。

就這些吧，希望能夠幫到你。

---

用in型子查詢而不是連接查詢來做，子查詢中利用索引覆蓋查出一個臨時表，外層利用索引用in查找。(臨時表數據不多的情況下效果會不錯)

---

1、從設計和需求上面分析，

1.1為什麼defind1和defind2和存儲兩個欄位，不能合併到一個欄位嗎，

1.2是否可以把鏈表的查詢取消，先查詢一張的數據，然後再查詢另一張表的數據，（第一張表返回的數據交少的時候，這樣就可以先排序，然後再去第二張表的數據）

2.1可以先查詢出第一張表，加排序，然後外層加子查詢關聯第二張表（第一張表的數據不多）

---

如果ams.define1="3" or ams.define2="3"數量不多的話，可以先對ams表做一個篩選查詢，即改成：

SELECT
ams.id,
......
ams.define2,
s.cost_type_name
FROM
(SELECT
id,
......
define2
FROM
ams_finance_account_data
WHERE
define1="3"
OR
define2="3") ams
LEFT JOIN
ams_cost_type_formula_system s
ON
ams.feeType=s.cost_type_id
ORDER BY
ams.producetime DESC


希望能對你有幫助！

---

defined1 or defined2 這裡可以優化，用or是無法使用索引的，建議改成union all兩句，union all出結果後再left join

---

不知道ams和s兩張表的數據量有多少

有沒有可能先把s表讀到內存做成字典，再查ams單表，在內存中拼name欄位

---

事實上你只用到s表的一個欄位，如果feetype就只有幾種的話就不用關聯了，直接用case when單表查詢好了；如果s表這個關聯ID是唯一值的話在謂語前面用子查詢查name試一下速度如何。因為這個SQL本來不複雜，主要的cost在join和order by，業務上沒有特殊要求的話把order by去掉也可。

---

能不能加一列冗餘一下?金融行業的軟體都這麼做.

---

拆分成多個查詢

添加索引

終極解決方案，分庫分表或者把數據放搜索引擎

---

類似的問題我半年多前遇到過

這類問題的通用解決方案是

1 查看語句執行計劃

2 根據計劃添加索引

3 遇到多個索引的情況，嘗試使用強制索引試試(mysql只能單索引)

實在無法解決，緩存吧...

---

推薦閱讀：