Turn from: Http://blog.itpub.net/29254281/viewspace-2086198/MySQL Order sub-database sub-table multi-dimensional query
MySQL Sub-database sub-table, generally can only follow one dimension of the query.
Take the order form example and divide it into 64 databases according to the user ID mod 64.
Query the user's dimensions quickly, because the final query falls on a single server.
However, if you query the dimensions of the merchant, the cost is very high.
All 64 servers need to be queried.
In the case of paging, it gets worse.
For example, a merchant queries the data on page 10th (according to the order creation time). The first 100 data needs to be queried on each database server, the program receives the 64*100 data, and then sorts the order creation time, and intercepts the 10 records of rank 90-100. Then discard the remaining 6,390 records. If the query is 100th page, 1000th page, then the database IO, network, middleware CPU, is not a small pressure.
After the sub-table, in order to deal with multi-dimensional query, in many cases will introduce redundancy.
For example, two clusters, one according to the user ID sub-database sub-table, the other one according to the merchant ID sub-database table.
In such a multidimensional query, each check.
But there are a few problems that are just as bad to solve.
Like what
Each extension of a dimension requires the introduction of a cluster.
The data between clusters, how to ensure consistency.
Redundancy consumes large amounts of disk space.
The order table structure that you see from your friends. Doing redundancy consumes a lot of disk space.
Create TableTs_order (order_id Number(8) not NULL, SNVARCHAR2( -), member_id Number(8), STATUS Number(2), Pay_status Number(2), Ship_status Number(2), shipping_id Number(8), Shipping_typeVARCHAR2(255), Shipping_areaVARCHAR2(255), payment_id Number(8), Payment_nameVARCHAR2( -), Payment_typeVARCHAR2( -), Paymoney Number( -,2), Create_time Number( -) not NULL, Ship_nameVARCHAR2(255), Ship_addrVARCHAR2(255), Ship_zipVARCHAR2( -), Ship_emailVARCHAR2( -), Ship_mobileVARCHAR2( -), Ship_telVARCHAR2( -), Ship_dayVARCHAR2(255), Ship_timeVARCHAR2(255), Is_protectVARCHAR2(1), Protect_price Number( -,2), Goods_amount Number( -,2), Shipping_amount Number( -,2), Order_amount Number( -,2), WEIGHT Number( -,2), Goods_num Number(8), Gainedpoint Number( One)default 0, Consumepoint Number( One)default 0, DISABLEDVARCHAR2(1), DISCOUNT Number( -,2), imported Number(2)default 0, pimported Number(2)default 0, Complete_time Number( One)default 0, Cancel_reasonVARCHAR2(255), Signing_time Number( One), The_signVARCHAR2(255), Allocation_time Number( One), Ship_provinceid Number( One), Ship_cityid Number( One), Ship_regionid Number( One), Sale_cmpl Number(2), Sale_cmpl_time Number( One), Depotid Number( One), Admin_remarkVARCHAR2( +), Company_codeVARCHAR2( +), Parent_snVARCHAR2( -), REMARKVARCHAR2( -), GOODS CLOB, Original_amount Number( -,2), Is_onlineCHAR(1), is_commentedCHAR(1)default 0, Order_flagCHAR(1)default 1 )
you can try a table instead of an indexed method.
1. Sub-database sub-table
2. Eventual consistency
3. The ability to replace the index with a table
First, it is based on the sub-database. The order form is divided into different servers according to the user ID mod 64 (according to the dimensions of the most queried).
Database server 1 has a database name of db_1
Database server 2 has a database name of db_2
...
Take db_1 as an example to create the following table
1. Order Form
ts_order_1 partition table, one partition per month.
2. Transaction table
CREATE TABLE Tran_log_1 (
tran_id bigint PRIMARY Key,
param varchar (2000)
);
Partition table, one partition per month.
3. Message table
CREATE TABLE Msg_log_1 (
tran_id bigint,
Shardkey varchar () NOT NULL,
Primary KEY (Tran_id,shardkey)
);
Partition table, one partition per month.
4. Dimension Index Table
CREATE TABLE Shard_shop_1 (
ID bigint primary KEY auto_increment,
Shopid int,
TS Timestamp,
State int,
dbid int,
OrderID bigint,
Index (shopid,ts,state)
);
Partition table, one partition per month.
For the final consistency of the table of the database using the Transaction table and the message table, please refer to
http://blog.itpub.net/29254281/viewspace-1819422/
For the cluster primary key generation service, please refer to
http://blog.itpub.net/29254281/viewspace-1811711/
Process for order creation
The Web server receives the user order and first obtains a transaction ID (tran_id) through RPC.
Locate the database server with transaction ID mod 64.
Writes the transaction ID, parameters to the Tran_log table,
The transaction ID and parameters are then written to the message queue.
If the write message queue succeeds, the transaction is committed. Otherwise, the transaction is rolled back.
You are now ready to return to the user interface.
The back-end processing service receives Message Queuing information, first querying the Tran_log table, whether the transaction ID exists, and not processing if it does not exist.
The queue message is then divided into two dimensions, one for the user dimension and one for the merchant dimension.
As a user dimension,
First based on the user ID mod 64 to find the final landing database, query the database of the message table Msg_log, in the user dimension, whether the transaction ID exists, if present, will not be processed.
(SELECT COUNT (*) from msg_log_xx where shardkey= ' order creation: User dimension ' and tran_id=?)
If it does not exist, a transaction is opened
Insert order form, I think can use tran_id directly as the ID of the order,
and insert the message table Insert Msg_log_xx (Tran_id,shardkey) VALUES (?, ' order creation: User dimension ');
Commit a transaction, commit.
As a merchant dimension,
The final database is found based on the merchant ID MoD 64, and the database for the user dimension may not be the same server.
In the same way, the message table of the landing database is queried first.
(SELECT COUNT (*) from msg_log_xxx where shardkey= ' order creation: Merchant dimension ' and tran_id=?)
If no record exists, the transaction is turned on,
Insert Dimension Index Table,
Insert into shard_shop_xxx (Shopid,ts,state,dbid,orderid) VALUES (...)
Shopid,ts,state Merchant ID, order time, order status are based on the original information of the order.
Dbid refers to the database ID of the order data, based on the user dimension (main dimension),
OrderID refers to the primary key of the order table in the user dimension (the primary dimension).
Insert Message table, insert Msg_log_xx (Tran_id,shardkey) VALUES (?, ' order creation: Merchant dimension ');
Final submission.
In this way, as a merchant dimension query, first find the dimension index table according to the Merchant's ID mod 64, get the order information of the merchant
SELECT * from Shard_shop_1 where shopid=? and state=2 order by TS limit 300, 10;
The information obtained may be as follows
You can see the eligible order information from the server 1,2,16,32,64
This part of the information, you can directly go to these servers to fetch data, and is the primary key query, fast.
Every once in a while, by the daemon, view Tran_log and Msg_log, if there are missing data, then the transaction is compensated.
Extension, you can add a new dimension index table.
Because all the tables are partitioned by month, the cold data from the past can be stored in a single server, and this instance holds 64 databases at the same time. After all, it is cold data, the traffic is very small.
Can be divided and to be able to close. For example:
MySQL Order sub-table multi-dimensional query
