商務邏輯中常有這樣的情況:
得到某一個無限分類下面的全部文章。
統計某一帳號下麵店鋪的全部訂單。
經常會用到article: in (cid,……),
order: in (shop_id,……)
如果某個分類下面有很多很多子分類,總之很多很多,可能無限,某一個帳號下面也有n個店鋪,那麼in ()不撐死了啊,sql長度不能很大吧
不知道對於這種情況有沒有什麼好的方法,項目中很多地方都是這樣用in (),從來沒有考慮過這個問題,今天突然想到了,意識到了這個問題,不知道有沒有好的最佳化方式。
線上等大神,謝謝了!
回複內容:
商務邏輯中常有這樣的情況:
得到某一個無限分類下面的全部文章。
統計某一帳號下麵店鋪的全部訂單。
經常會用到article: in (cid,……),
order: in (shop_id,……)
如果某個分類下面有很多很多子分類,總之很多很多,可能無限,某一個帳號下面也有n個店鋪,那麼in ()不撐死了啊,sql長度不能很大吧
不知道對於這種情況有沒有什麼好的方法,項目中很多地方都是這樣用in (),從來沒有考慮過這個問題,今天突然想到了,意識到了這個問題,不知道有沒有好的最佳化方式。
線上等大神,謝謝了!
不記得有代替in的語句,可能也是因為我不太關注sql導致水平較低哈。
這種問題通常是由於無限分類表本身的設計問題導致的,例如只有id, parent_id兩個欄位,所以只要in了,如果無限分類表包含 id, parent_id, level(樹高), path這個欄位的時候就可以避免使用in了。
我個人使用的一個無限分級表包括以下的欄位
id 唯一索引
parent_id 父級的id
number 數軸投影序號
leve 樹高
left_number 已當前節點為樹根時,其下的最左端的子節點的數軸投影序號
一個簡單的例子演變:
[id:1, level:1 number: 1, left_number:1]
插入一個子節點
[id:1, level:1 number: 2, left_number:1]
[id:2, level:2 number: 1, left_number:1]
於level2再插入一個子節點
[id:1, level:1 number: 3, left_number:1]
[id:2, level:2 number: 1, left_number:1] [id:2, level:2 number: 2, left_number:2]
這種樹形的資料維護很麻煩,但是搜尋要快的多,例如任意節點下的全部子節點就是left_number到number-1。用這個做過一個Chuan啊銷系統的人員結構樹,效果很不錯。
核心思路就是把一顆樹的分支轉換為數軸上的點/線段,從而在數軸上得到完整的樹的投影。
忘了一個top_id,表示節點對應的根節點,否則表裡面有多顆樹的話就出問題了。
採用Memory引擎表,在拼接in裡面的欄位時,把這些值全部寫到這個表中,然後使用這個表關聯查詢;in裡面最好是索引欄位,這樣的話,可以避免in長度限制。
不想用 in 的話,可以考慮冗餘一個欄位。比如所有的商鋪訂單加上帳號 id。冗餘欄位帶來新問題就是關係變動的維護:比如商鋪轉移給其他帳號(但曆史訂單應該還是屬於原來的帳號,冗餘應該問題不大)
分類的話可能就不適合冗餘了,隨時可能查詢不同層級下的所有分類,還是用 in 比較好,畢竟不是真的無限分類。
mysql in 本身 沒有長度限制,而整個SQL 是有長度限制的,明知道 in 的法子有一定的缺陷或者 存在隱患,不妨 換一種思路,一個SQL 很難搞定,實在不濟, 動態拼湊其多個union all ,也可以搞定的
IN沒有限制,不過整個SQL長度在my.cnf可以配置限制的max_allowed_packet=2M。SQL太長肯定會影響傳輸和查詢分析器分析的效率,越精簡越好。
IN的這種做法,本質上底層IN(1,2) 和... where id = 1 union all ..where id = 2差不多的。
從業務上要歸避這種垃圾SQL。
1、業務上如果可以,把IN(id1,id2) 把這些id放到一個表中管理。然後JOIN這個表。
2、通常你的id1,id2是由某個查詢產生的,可以用子查詢來做。
3、產品上妥協,改善
mysql中的In操作,只要欄位上建立好有效索引,效率完全不用擔心。
不過id In('1','2', ....,'1000' )這種語句,最好數量控制在一千以內,再多的話,sql效率就會下降。如果有需求超過一千以上,說明樓主需要冗餘欄位了,比如冗餘你業務情境中的使用者帳號ID。
至於樓上建議join聯表的,我覺得如果表數量級在10萬層級還可以;一旦達到百萬級,聯表就是災難。
in 實在顯示不了,試試left join on 串連一下吧