MySQL Sub-database sub-table table after the data query (5th)

Objective

After the completion of the table, it is obvious that the query for the data becomes more complex, especially in relation to the table, and in some cases it is not possible to use join at all.

In fact, the individual is more encouraged to split the large join SQL into a few small SQL to query the data. As a result, the overall efficiency may be slightly reduced (if the connection pool is completely ignored), but the query statement becomes simpler, making subsequent maintenance easier. It can also bring more convenient extensions. You can feel that there is a 100 row SQL statement for you to maintain, and give you 10 10 rows and each piece has good annotated SQL to maintain, to help tuning. Which one would you like to choose? Believe it or not, I choose the second kind, and the second one can understand the business well.

It says that to split the join, I don't mean splitting each statement. My guideline is O (n) queries. Taboo that after the detection of data through the program cycle to find out the results of the database to query, that is, O (n*m) this is required. Instant sensory methodology is important ^_^.

Simulation Scenarios

• Scenario 1 : Buyer Place Order

1, when browsing the product can obtain the store ID and the product ID, as for the shopping guide ID here we can be in a random form (need to determine how to obtain the Business Guide ID)

2, through the shopping guide ID to get the user information guide to get shopping guide data should be placed in that table.

3. Deposit the order data into the seller's sub-table, and the purchaser's sub-table.

The following is the pseudo-code (because only the SQL is not good to show the specific business logic), in fact, they are more lazy don't want to write Python. ^_^

--Get the information of the shopping guide, and the store select U.table_flag as Guide_flag,  ug.store_id as Store_idfrom user as U, user_guide as Ugwhere u.use r_id = ug.user_id and  user_guide_id = Shopping Guide ID;  SET autocommit=0; START transaction;--Create sales order sell_order_2 insert INTO sell_order_2values (Order_snowflakeid, Shopping Guide ID, purchaser ID, order total, Order status);----record what items are placed on this order insert into order_goods_2values (Order_goods_snowflakeid, Order_snowflakeid, commodity ID, commodity price, number of items);-- Record purchase order form  buy_order_6 the table where the purchaser is located, the list of the sellers is not confused-the purchaser's order ID and the seller's order ID are the same insert into buy_order_6values (order_ Snowflakeid, User ID, shopping guide ID)  COMMIT; SET autocommit=1;

　　

• Scenario 2 : Buyers View orders

It is troublesome to browse the purchaser's order because the buyer's order information and the product information are not in the same table.

1. Page to find the list of buyers ' orders.

2. After returning the order information to the browser, use Ajax to get the goods for each order.

--Get the user's sub-table information user_id = 66SELECT table_flag from user WHERE user_id=66;+------------+|          Table_flag |+------------+| 9 |+------------+--Get the user order, these information values are returned directly to the browser's select * from Buy_order_9 WHERE user_id=66 LIMIT 0, 1;+---------------------+- --------+---------------+| buy_order_id | user_id | user_guide_id |+---------------------+---------+---------------+|      3792111966815784961 |             66 | 1 |+---------------------+---------+---------------+--get the user_guide_id=1 user's sub-table information 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 |+------------+--Browser to get product information through AJAX SELECT * from order_goods_2 WHERE sell_order_id = 3792111966815784961 and User_gui de_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 |+---------------------+---------------------+---------------------+---------------+---------+------+

　　

From the above experiment we can see that the original in the ' Sub-database table (1)--Basic Table Introduction ' association query can be obtained from the order of the data will now need to be split into multiple parts to query (is inevitable, it may not be a good thing).

Here's why we use Ajax to get and present the data for ' Order items ':

1, we do not know the ' purchase order ' of the shopping Guide is a sub-table, so we need to facilitate the inquiry out of each ' purchase order ', if there are 10 orders need to facilitate 10 times to obtain the corresponding shopping guide is which sub-table.

2, after the completion of the table is also necessary through each sub-table to link ' order merchandise ' to obtain product information.

3, obtain the above information or need to synthesize a list to return to the browser.

The way that data is returned to the browser through a single pass above affects the user experience and makes the user feel very slow. And you need to write complex logic that is difficult to maintain.

We will query the time to enlarge, a check is 1s if 10 orders can be completed one time, it may take more than 11s to return to the browser. If the order of the query is returned to the browser first. It looks like it just needs 1s. The data is returned to the browser.

• Scenario 3 : Guide to view orders

Shopping Guide is also an ordinary user, so a login system will know the shopping guide ID and User ID

--Get the information of the shopping guide user_id = 6, user_guide_id = 5SELECT table_flag from user WHERE user_id=6;+------------+|          Table_flag |+------------+| 6 |+------------+--Query Order information 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 |+---------------------+---------------+---------+---------+--------+--Inquiry Order product Information 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 |+---------------------+---------------------+---------------------+---------------+---------+------+

　　

• Scenario 4 : Guide To modify orders

--Modify the order price Update sell_order_6 SET prices = 1000.00 WHERE sell_order_id = 3792112033412943873;

　　

• Scenario 5 : Shop owner adds merchandise to store

Only store owners have permission to add items. However, the shopkeeper is also an ordinary user.

--Get the table information of the shopkeeper user_id = 1SELECT table_flag from user WHERE user_id=1;+------------+| Table_flag |+------------+|          2 |+------------+--store owner Add item insert INTO Goods_2 VALUES (Snowflakeid, product name, commodity price, store ID);

　　

MySQL Sub-database sub-table table after the data query (5th)