MySQL分庫分表分表後資料的查詢(5th)

標籤：nbsp   get   store   準則   sharp   如何擷取   效率   ajax   避免   

前言

在分表完之後顯然對於資料的查詢會變的比較的複雜，特別是在表的關聯方面，在有些情況下根本就不能使用JOIN。

其實個人是比較鼓勵將那些大的JOIN SQL拆分成幾個小的SQL來查詢資料。這樣雖然總體的效率可能會稍稍下降（如果使用了串連池完全可以忽略），但是查詢的語句變簡單了，使得後續的維護帶來的方便。同時也能帶來比較便利的擴充。你可以感受一下有一個100行的SQL語句給你維護，和給你10個10行並且每一塊都有很好的注釋的SQL去維護，去協助調優。你願意選哪個。不管你們信不信，反正我是選第二種，而且第二種可以很好的理解業務。

上面說到要拆分JOIN，我的意思不是將每個語句都拆分。我的準則是 O(n) 次的查詢。忌諱那種查出資料後通過程式迴圈查出結果再去資料庫中查詢，也就是需要 O(n*M)這種。 瞬間感覺方法論很重要有木有 ^_^。

類比情境

• 情境1：購買者下訂單

1、在瀏覽商品的時候能獲得商品的 門店ID 和 商品ID，至於導購ID這裡我們能以隨機的形式得到（需要根據業務來確定如何擷取導購ID）

2、通過導購ID獲得導購的使用者資訊從而得到導購的資料應該放在那張分表。

3、將下單資料存入出售者的分表，和購買者的分表。

下面展示的是虛擬碼(因為只用SQL不好展示具體商務邏輯)，其實是自己比較懶不想寫Python了。^_^

-- 獲得導購分表資訊，和所在門店SELECT u.table_flag AS guide_flag,  ug.store_id AS store_idFROM user AS u, user_guide AS ugWHERE u.user_id = ug.user_id  AND user_guide_id = 導購ID;  SET autocommit=0;START TRANSACTION;-- 建立銷售訂單 sell_order_2 通過程式拼湊出來的INSERT INTO sell_order_2VALUES(order_SnowflakeID, 導購ID, 購買者ID, 訂單總額, 訂單狀態);-- 記錄此訂單有哪些商品INSERT INTO order_goods_2VALUES(order_goods_SnowflakeID, order_SnowflakeID, 商品ID, 商品價格, 商品個數);-- 記錄購買訂單表  buy_order_6 購買者所在的分表，上面的是出售者所在的分表別弄混了-- 購買者訂單ID 和 出售者訂單ID是一樣的INSERT INTO buy_order_6VALUES(order_SnowflakeID, 使用者ID, 導購ID)  COMMIT;SET autocommit=1;

　　

• 情況2：購買者瀏覽訂單

瀏覽購買者訂單就是比較麻煩的，因為購買者訂單資訊和商品資訊不是在同一分表中。

1、分頁尋找出購買者的訂單列表。

2、將訂單資訊返回給瀏覽器後，使用ajax擷取每個訂單的商品。

-- 獲得使用者的分表資訊 user_id = 66SELECT table_flag FROM user WHERE user_id=66;+------------+| table_flag |+------------+|          9 |+------------+-- 擷取使用者訂單, 這些資訊值直接先返回給瀏覽器的SELECT * FROM buy_order_9 WHERE user_id=66 LIMIT 0, 1;+---------------------+---------+---------------+| buy_order_id        | user_id | user_guide_id |+---------------------+---------+---------------+| 3792111966815784961 |      66 |             1 |+---------------------+---------+---------------+-- 擷取 user_guide_id=1 使用者的分表資訊SELECT u.table_flag AS guide_flagFROM user AS u, user_guide AS ugWHERE u.user_id = ug.user_id  AND user_guide_id = 1;+------------+| guide_flag |+------------+|          2 |+------------+-- 瀏覽器通過ajax擷取商品資訊進行展現SELECT * FROM order_goods_2 WHERE sell_order_id = 3792111966815784961  AND user_guide_id = 1;+---------------------+---------------------+---------------------+---------------+---------+------+| order_goods_id      | sell_order_id       | goods_id            | user_guide_id | price   | num  |+---------------------+---------------------+---------------------+---------------+---------+------+| 3792112143781859329 | 3792111966815784961 | 3792111950445416449 |             1 | 3100.00 |    2 || 3792112160789762049 | 3792111966815784961 | 3792111951305248769 |             1 | 5810.00 |    1 |+---------------------+---------------------+---------------------+---------------+---------+------+

　　

從上面的實驗我們可以看到原本在 ‘分庫分表(1)--基礎資料表介紹‘ 中的關聯查詢就能獲得出訂單的資料現在需要被拆為多個部分來查詢(是不可避免的, 這樣做也未必不是好事)。

 

這裡說一下我們為什麼要使用ajax來擷取並展現 ‘訂單商品‘ 的資料：

1、我們不知道 ‘購買訂單‘ 的導購的分表是哪一個，因此我們需要便利查詢出的每一條 ‘購買訂單‘，如果有10個訂單就需要便利10次去擷取對應導購是哪個分表。

2、獲得分表完之後還需要通過每個分表去關聯 ‘訂單商品‘ 獲得商品資訊。

3、獲得到以上資訊或需要整合成一個列表返回給瀏覽器。

通過上面一次性把說有資料返回給瀏覽器的方法，會影響到使用者體驗，讓使用者覺得很慢的感覺。並且需要寫複雜的邏輯，難以維護。

我們將查詢時間放大，一個查是 1s 如果有10個訂單 一次性完成就可能需要 11s 以上的時間才返回給瀏覽器。如果先將查詢的訂單返回給瀏覽器。看上去就只需要 1s就吧資料返回給瀏覽器了。

• 情況3：導購查看訂單

導購也是一個普通使用者, 因此一登陸系統就知道 導購ID 和 使用者ID

-- 獲得導購的分表資訊 user_id = 6, user_guide_id = 5SELECT table_flag FROM user WHERE user_id=6;+------------+| table_flag |+------------+|          6 |+------------+-- 查詢訂單資訊SELECT * FROM sell_order_6 WHERE user_guide_id = 5 LIMIT 0, 3;+---------------------+---------------+---------+---------+--------+| sell_order_id       | user_guide_id | user_id | price   | status |+---------------------+---------------+---------+---------+--------+| 3792112033412943873 |             5 |      10 | 5197.00 |      1 || 3792112033429721089 |             5 |      10 | 6826.00 |      1 || 3792112033446498305 |             5 |      10 | 5765.00 |      1 |+---------------------+---------------+---------+---------+--------+-- 查詢訂單商品資訊SELECT * FROM order_goods_6WHERE sell_order_id IN(  3792112033412943873,  3792112033429721089,  3792112033446498305);+---------------------+---------------------+---------------------+---------------+---------+------+| order_goods_id      | sell_order_id       | goods_id            | user_guide_id | price   | num  |+---------------------+---------------------+---------------------+---------------+---------+------+| 3792112273532653569 | 3792112033412943873 | 3792111951800176641 |             5 | 7826.00 |    1 || 3792112292964864001 | 3792112033412943873 | 3792111952559345665 |             5 | 3057.00 |    2 || 3792112273545236481 | 3792112033429721089 | 3792111952660008961 |             5 | 8540.00 |    1 || 3792112292981641217 | 3792112033429721089 | 3792111951863091201 |             5 | 8545.00 |    1 || 3792112273566208001 | 3792112033446498305 | 3792111952110555137 |             5 | 8383.00 |    2 || 3792112292998418433 | 3792112033446498305 | 3792111952966193153 |             5 | 3282.00 |    2 |+---------------------+---------------------+---------------------+---------------+---------+------+

　　

• 情況4：導購修改訂單

-- 修改訂單價格UPDATE sell_order_6 SET price = 1000.00 WHERE sell_order_id = 3792112033412943873;

　　

• 情況5：店主為店鋪添加商品

添加商品只有店鋪的店主有許可權。然而店主也是一個普通使用者。

-- 獲得店主的分表資訊 user_id = 1SELECT table_flag FROM user WHERE user_id=1;+------------+| table_flag |+------------+|          2 |+------------+-- 店主添加商品INSERT INTO goods_2 VALUES(SnowflakeID, 商品名稱, 商品價格, 門店ID);

　　

MySQL分庫分表分表後資料的查詢(5th)